Sunday 25 November 2012

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

6 comments:

  1. Awesome! Thank you sooooo much! You're the best!

    ReplyDelete
  2. See that's my hesitation with making another spreadsheet or two, it's a pain to have to keep multiple spreadsheets up to date with what should be the same data, but at the same time I don't want to just have one giant ass spreadsheet. Thinking of splitting the Prices, XML, CSV, and IQY tabs of my shuffler into a base price collecting unit, but then it becomes tricky because I don't know if relative file paths will work when other spreadsheets want the data.

    ReplyDelete
    Replies
    1. And then there's another issue, what I do in Excel probably won't work in open office, and definitely won't work in Google Docs

      Delete
    2. hmmm, yeah I can see your problem. I just have lots of small spreadsheets but all in one file. I keep a back up copy too though, just in case of stupid errors or PC meltdown!

      Delete
  3. Ive got this part done and cant wait for your other post about spreadsheets. Im very new to this, but looking forward to seeing if i can learn!!

    Thanks Nev

    ReplyDelete
  4. Another great post! I love my "daddy efficient" way of learning which simply is:
    1 Hold Lil' Grayz
    2 Have a small goal to achieve thru what I learn
    3 Go!
    4 Show support!
    5 Practice (or use Audio/Video for more learning, if my hands are tied)
    6 Rince repeat

    ReplyDelete

Your comment is awaiting moderation - I hate to do this but so many spammers around these days :(