Leather Conversions Spreadsheet & Bags

Leather Conversion

I promised you more spreadsheets so today we have a Leatherworker's spreadsheet. I wrote a post way back near the beginning of Cataclysm about converting leather scraps up to better qualities as a way to make some easy gold. It's nice to see that the old tips & tricks still work now that Mists of Pandaria is live & I've included some of the older conversions in this spreadsheet as there is still gold to be made there.

Now there are two ways to use this spreadsheet - you can download a copy of my Leather Conversion spreadsheet & just manually input prices from your Auction House or you can use The Undermine Journal .csv file as I explained in last weeks post Easy Data from TUJ.

If you decide to manually input the prices, you will need to overwrite the VLookup formulas in column B but the rest of the spreadsheet will still work. (If you want to try your own VLookup formulas, keep reading, instructions at end of post)

If you want to try the TUJ data file, follow the instructions in the post to get a .csv file onto your PC. Open it in Excel or Open Office & just copy/paste the whole sheet into tab 2 of my Leather Conversion spreadsheet. It should pull the data through to the main sheet automatically.

Leather Conversions Spreadsheet

So what are we looking at? Well, I've tried to keep it fairly simple but it took me a while to get there so I hope you can follow what I've done! Converting up from the various leather scraps is a bit more time-consuming but can be very profitable - just look at the price of Savage Leather Scraps on my server today (red dot line). Converting up from those scraps means that my Savage Leather would only cost me 33 silver - a big saving over the 9 gold or more that someone is trying to sell their Savage Leather at! If there are enough Savage Leather Scraps to convert to a Pristine Hide (250 scraps, in case you were wondering), that's only 17 gold 50 silver for a Pristine Hide - big profit there or even use the ultra cheap hide to make the scribe or mining bags for even more profits!

Now I've only included the bags here but you can easily add rows for leg armours or anything else you like - add a row for the leg armor & a row for each type of material it uses besides the leather then use a VLookup formula to get the prices from the TUJ data file. A quick a-b formula later & you should see if it would be profitable or not.

Ok so I promised help with the VLookup formula for those of you not sure about spreadsheets. It's really very easy but sometimes the help files make it confusing. This is what Open Office says :-

"Syntax

=VLOOKUP(SearchCriterion; Array; Index; SortOrder)

SearchCriterion is the value searched for in the first column of the array.

Array is the reference, which is to comprise at least two columns.

Index is the number of the column in the array that contains the value to be returned. The first column has the number 1.

SortOrder is an optional parameter that indicates whether the first column in the array is sorted in ascending order. Enter the Boolean value FALSE or zero if the first column is not sorted in ascending order. Sorted columns can be searched much faster and the function always returns a value, even if the search value was not matched exactly, if it is between the lowest and highest value of the sorted list. In unsorted lists, the search value must be matched exactly. Otherwise the function will return this message: Error: Value Not Available."

OK - so the Search Criterion is what you are looking for in that huge table of data - in our case Savage leather, Pristine Hide etc. For this formula to work, your description in the main spreadsheet must be identical to that in the data file so watch for spelling mistakes & random apostrophes.

The Array part of the formula is merely the parts of the huge data file that the formula is going to look at. The formula doesn't need to look at the whole file, it always starts with the item name column so you have to tell the formula which columns to look at. In our case, it only needs columns C & D so I click C1, hold down the shift button & page down to D12000+.

The Index part of the formula is which column contains the data you want to use in the formula - for us that would be column D or 2 (it's the 2nd column in our array). If we wanted to pull data from other columns, we would just increase the Array columns (say columns C to H) & put a 3,4,5 or 6 in the formula to pull data from that column.

Sort Order is important to us here - the data file is so big, we really don't want to sort it in ascending order before we do anything so the last part of the Vlookup formula for us is a 0 (zero).

So for this file, our Vlookup formula looks like this 

=VLOOKUP(A5;'TUJ Data'.$C$2:$D$12668;2;0)/10000

The Undermine Journal data prices are expressed in coppers so I have divided the Vlookup results by 10,000 to get to a gold/silver price.

So there we go! I hope it's clearer than mud to those of you who need the help but if you are having problems, do feel free to comment, tweet me or email me & I will try to help further. 

As for leather converting - don't forget, you can convert the lower leathers up to higher ones too - it's rarely profitable but if you just need that 1 or 2 more pieces to get a skill point, it can be a time saver.

Image © Austin Appraiser under Creative Commons licence

More Spreadsheet Stuff - Easy Data From TUJ

One of the comments that kept cropping  up on my Ironpaw Token Shuffle posts was easy access to The Undermine Journal data. If your desk is a mass of post-it notes or scraps of scruffy paper, then maybe this post will help sort that out - at least as far as Auction House stuff goes! I didn't know about TUJ data being available myself until Molsan pointed me to his recent post on how to download the data using IQY but as I use Open Office not Excel, I couldn't get it to work (that may just be me messing up though)! Instead I used the .csv file facility so I thought I'd show you how to get it for yourself.

It's a huge file though - almost 13000 lines for my server but as we're not going to wade through it manually that's not really a problem. This data file only needs to be downloaded each time you want to update your spreadsheets with a limit of 10 times per 24 hour period. The great thing about it though is that you can use the same file for all your WoW related spreadsheets. I have most of my spreadsheets in one file, each one on a different tab or sheet & I just copy/paste the fresh data file over the old one in the relevant tab. This will then update all my sheets at the same time.

OK, so enough chit chat, how do you get to this file? Go to The Undermine Journal for your region & login. If you don't have a TUJ account yet, just follow the instructions on the site (I linked it to my Twitter account for easy access). Once you have your account set up, you should log in & it will take you to your account page - look for the 'Market Data XML & CSV' & choose your realm from the drop down box. It will give you two URL options & a IQY link. Just copy/paste the .csv link into a browser window & it should download the file automatically for you.

Next you will need to open the file in a spreadsheet program of your choice. If you are given options, this file is comma separated so choose that option & it should open nicely laid out, with columns already set up similar to the screenshot below.

Sample .csv File from TUJ

Once I have my data file opened like this, all I do is copy/paste the whole thing into my master spreadsheet file & the formulas I have there will pull all the data to the various sheets. I use the VLOOKUP function so you could have a look how to use that or if you're patient, I'll be doing some more spreadsheet posts over the next week or two which will use the VLOOKUP function so you can just wait & use my spreadsheets! lol

I hope this helps - I know there are one or two readers who will love this & probably race ahead & sort out some glorious spreadsheets for themselves but for those of you who haven't used spreadsheets before, bear with me & we'll get you some nice sheets to use.


Image © pjohnkeane under Creative Commons licence

Ironpaw Tokens & Cooking Materials Shuffle - Revisited

Ironpaw Cooking Materials Shuffle

After some comments, email feedback & a conversation with a fellow gold blogger, I've added some more stuff to the Ironpaw Tokens spreadsheet I uploaded a few days ago. It seems that as long as it took me to get my head around what I was trying to do, I still didn't quite manage to communicate it very well in my Ironpaw Tokens & Cooking Materials Shuffling post!

So version 2 has a whole bunch of extra columns which should show you at a glance which cooking material will make you some profit if you convert it to another via the Ironpaw Tokens trade-in. I've uploaded this as a second file called Ironpaw Conversion and Profits which you can download & edit freely. Please though - make sure you download before you edit - it just makes it easier for others if the original file is left untouched.

Ironpaw Token Profit Shuffle Spreadsheet

I took the original spreadsheet and extended the columns - one for each cooking material. Now all you have to do is type in the prices (in the yellow boxes) for your Auction House & it should calculate where any profitable conversions are. To use it, just choose the cooking material you have from column A & read across that row to find which other cooking material to convert it into for profit (or savings if you're leveling your cooking of course).

You don't need to worry whether you are converting vegetables to meat/fish or vice versa - I have adjusted the profit calculations accordingly within the sheet.

Also, you may want to sort each section (vegetable rows, fish rows, meat rows etc) by Column B once you've entered your AH prices - make sure you sort the whole section though otherwise the formulas will mess up big time! lol Sorting is optional but it does make it easier to read if your cheapest price is at the top of each section.

So an example might help I guess. If I have plenty of Wildfowl Breast & want to use those to convert to vegetables - I can see by reading along row 29 (wildfowl breast, column A) that converting to Juicycrunch Carrots, Green Cabbages or White Turnips will give me a loss, converting to Witchberries will give me profit of 1g63s per item but converting to Jade squash will give me a very nice profit of 6g 67s per item!

Please remember though - cooking materials are very volatile right now & prices are likely to vary widely over a few days - don't sink loads of gold in today then find out your 'profitable' material from the list above is the cheapest on the Auction House tomorrow. Spread your risk too - don't invest loads in just one cooking material - if you are going to convert a lot, convert to a range of different profitable items so that you are covered as prices change.

So there you go! Thank you to all the commenters, emailers & others who gave suggestions on the earlier version. I hope this one is closer to what you were hoping for from the earlier version.

Good luck & have fun

****************

Check out some of my Squidoo lenses! Gift Guide - Horde Edition | Gift Guide - Alliance Edition

Image © AW Sheffield under Creative Commons licence

Ironpaw Tokens & Cooking Materials Shuffling

Ironpaw Token Shuffle - Veggies, Meat & Fish

Over the last few weeks, I've been talking about making gold with cooking materials & fishing quite a bit here. That's because right now, it's about the only thing I'm really doing in game alongside dailies & some leveling! About 2 weeks ago, I did a post about how to make easy gold with Pandaren Cooking & in the comments it was pointed out to me that I missed an obvious trick! I edited the post at the time but it also got me thinking a little clearer about this whole Ironpaw Token Shuffle.

I went back to The Undermine Journal, to the Cooking section under the Gathered category & just stared at it a while. I realised that for me, at least, it didn't go far enough so I built another little spreadsheet for that extra step. This screenshot (from The Undermine Journal) shows how much it would cost to make a container of meats/fish or vegetables from each material available on the Auction House. In effect, it's the cost of an Ironpaw Token, dependant on which raw material you use. I've included the 100 Year Soy Sauce & Black pepper section because those are still selling well on my server & from this screenshot, you can easily see that there is profit to be made just by converting the cheapest raw materials to Ironpaw Tokens.

The Undermine Journal, Cooking page, Ironpaw Token Calcs

But what if you don't want to sell 100 Year Soy Sauce or Black Pepper? What if you need cheap materials to level your cooking Ways? Out comes the calculator to work out whether those 20 cheap Redbelly Mandarin are actually cheaper than 5 Raw Crocolisk Belly via an Ironpaw Token or should you just buy the Raw Crocolisk Bellies? (Just in case you don't know, 20 x  a meat or fish or 100 x a vegetable can be converted to an Ironpaw Token which in turn can be exchanged for 5 x a meat/fish bundle or 25 x a vegetable bundle)

That's where my spreadsheet comes in. I wanted to put some clever gizmos in there to make it even easier but my spreadsheet skills are extremely rusty & I just couldn't get my head around it! So I took the basic Undermine Journal data (columns C & D) to start with - this is just the same data as the screenshot earlier. Then I took the Container Cost & divided it by either 5 or 25 (meat/fish or vegetables) for columns E & F.

Calculate Ironpaw Token Shuffle Costs

So what does this actually do for me that I can't do quickly in game? For me, it quickly identifies which raw materials to buy to shuffle to the other materials I require. Currently I need Giant Mantis Shrimp but at 34g80s each, I don't want to buy them directly. My spreadsheet shows me that even if I use 100 Pink Turnips to convert to an Ironpaw Token & then buy 5 Giant Mantis Shrimp, I will save 42 gold! (purple dots).

It's going to be fairly rare for 100 vegetables of any sort to be a cost effective way of buying just 5 meat or fish - Giant Mantis Shrimp are just exceptionally expensive on my server today but using a single 20 stack of meat or fish can easily be cost effective to buy either 5 meat/fish bundle or a 25 vegetable bundle.

You can see that the Raw Turtle Meat & the Reef Octopus (red dots) are both really cheap today & in column F, it shows them at a converted price of 1g66s & 1g59s respectively - that means using either of these to buy a vegetable bundle (25's) will be the cheapest way to buy any of the vegetables except Pink Turnips & Mogu Pumpkins. The Reef Octopus is so cheap today that it's also the cheapest way to buy Wildfowl Breast, Raw Crocolisk Meat & Raw Crab Meat as well as all the fish below Jewel Danio in the fish section!

I've added this spreadsheet onto Google Docs & fingers crossed, in an editable, downloadable form. Just click Ironpaw Token Conversion spreadsheet and you should go straight to it. Feel free to edit it to suit yourself but please, only edit a downloaded copy. I tried to put a macro in for easy sorting but it didn't work (or I couldn't make it work!) so don't worry if you get a 'contains a macro' warning - I couldn't find it to get rid of it!

I hope this helps make the Ironpaw Token Shuffle a little easier & quicker for you. If you have any problems downloading or editing it, let me know either in comments, by email (top right under profile) or on Twitter @NevAHAddict & I'll do my best to help.

Edited: I had to change to Google Docs link above to return the file to it's original format. Please, please, download a copy before you edit anything. Thank you :)

Edited yet again! Following some feedback, I've extended this spreadsheet & uploaded a version 2 to google docs - I've also added some more analysis in a second post Ironpaw Tokens & Cooking Materials Shuffling - Revisited so to make sure you've got the most up to date version, pop over to the Revisited post & click the google docs link there :)


Image © USDAGov under Creative Commons licence