Register
Welcome to the Stormspire.net WoW Gold Forums!

Greetings, guest! Welcome to Stormspire.net, your #1 resource for WoW and Diablo 3 Gold, Professions, and everything in between!


Enjoy what the site offers? Consider Premium Membership if you wish to get the most out of the site. Premium Membership benefits include the acclaimed Goblineer's World of Warcraft Gold Guide, private forum access, a complimentary high-quality avatar, and much more!


If this is your first visit, make sure to check out the Stormspire.net Survival Guide for a quick site orientation. You may also register to unlock all standard forum features, and to get rid of these lovely ads!


We proudly host the Official forums for TradeSkillMaster, The Undermine Journal, and WoWuction.

Page 1 of 3 123 Last
Results 1 to 10 of 30
  1. #1
    Nordrassil(A)-Winterhoof(A)
    Wind Trader
    TheExile's Avatar
    Posts
    330
    Reputation
    47
    Tagged in
    134 Posts
    Add to this user's reputation

    Importing WoWUction Data on Excel

    Hey guys, so lately I've been working on building my own Profession Leveling Kit Spreadsheets. I'll attach them so you can view and get a better idea of what I'm talking about, but they basically all look like this:

    LvlKitEx.png

    If you don't want to download it, how it works is pretty simple (pretty basic, just something for ease). It's partially for WHILE I'm building, and partially for being used PRIOR to building. Once you get a feel for the market, you can look at what you have in stock and the prices of the mats beforehand, and can usually give a decent price quote to somebody before building the kit just by looking at this. Before, I was inputting prices manually. That was awful. After seeing Stede & Mithrildar's MoP Shuffling Spreadsheet though, I decided I wanted to try and do something like this. Granted, it doesn't need to be as neat or simple as that one, where anybody can get their server info with minimal effort, but just something to replace manually entering the price ranges. Anyway, it's a basic layout, where you enter the amount of stock you have in the "Have" column, the "Needed" automatically updates. Later today I'm adding two other options, one will be the "Total Projected Cost (Minimum)", where it will just calculate how much the kit will cost at a minimum, if you were to buy all pieces at the Lowest Buyout. This would be for the ENTIRE kit. The second will be "Total Projected Cost Remaining (Minimum)", which will be the same as before, but will only calculate the minimum price on what you still have to buy to complete the kit. More details will come as I build it, for easier access, but to be honest, I'm not overly experienced with Excel - I'm teaching myself as I go, using multiple online sources.

    Now, if you look at the top right, you will see where I have a section *Reserved* for Importing the WoWU data for my realm (or eventually, of any realm you decide to select, such as in @Stede 's Shuffler. However, that is for later. Now, I just want to know how to import data from WoWU (I read Fetching WoW API AH data into Excel / Google Spreadsheet and Imported the data, but wasn't sure of what to do with it) then draw the prices for the items I want from that imported data to the specific areas on the spreadsheets. I would love to get the price as the 15th centile, but I would also settle for Lowest Buyout if that's easier, or the only option available. Now, if someone helps me out and just does it for me, to get it done, great. But, I would really like to learn HOW to do it, so I'd like to actually have someone help me through the process of doing it. Then, after it's all finished, I can put the entirety of my finished project out for anyone to use! I can also use the information to build more spreadsheets later. So, thanks for any help in advance, and I'll definitely +rep anyone who can help me out!
    Attached Files Attached Files
    MoP Profession Kit Spreadsheet | No Supply Custom Transmog Market
    Follow me @WoWTheExile
    Remember to +Rep someone if one of their posts help you!

  2. #2
    US-Shattered Halls(H)
    Wind Trader
    PhatLewts's Avatar
    Posts
    833
    Reputation
    81
    Blog Entries
    2
    Tagged in
    446 Posts
    Add to this user's reputation
    I'd say the easiest way would be to set up a V-Lookup with a reference to the sheet that has the data on it. I personally use Theunderminejournal's XML to import data, here's an example:

    Data:

    Name Market
    Design: Jeweler's Amber Monocle 3578427
    Primal Robe 39000000
    Initiate's Pants 2200
    Hypnotic Dust 121749
    Enchant Gloves - Mastery 1259900
    Lesser Celestial Essence 49418
    Greater Celestial Essence 200000
    Small Heavenly Shard 33367
    Heavenly Shard 23256
    Maelstrom Crystal 19800

    These are columns F and G of a spreadsheet called XML containing the current data from my realm, to pull a number onto a seperate sheet, I could use a Vlookup function like so:

    =VLOOKUP("Hypnotic Dust", 'XML'!F1:G13291, 2, FALSE)/10000

    This would return 12.1749, the gold Market Value for Hypnotic Dust (I use the TUJ XML)

    just to show what each part does, the first entry in quotes is the item you want to look up, the second entry what entires you're looking at, in this case all the data in columns F and G, you can do more or less columns with ease. Now the function will use the first 2 search parameter to look for "Hypnotic Dust" in the FIRST COLUMNof my selected Data range. Once it finds it, it will look to argument number three, which in this case is 2, and pull the number from the SECOND (2) COLUMN. The last column is do you want to approximate the search number? Keep it always as FALSE!!


    Here's another example set of data:
    Name Market Quantity ReagentPrice LastSeen MarketAvg Market
    Primal Robe 39000000 0 2012-01-01T02:33:04-08:00
    Initiate's Pants 2200 0 2012-06-20T14:09:52-07:00
    Hypnotic Dust 121749 50 2013-07-19T13:49:55-07:00 126295 20065
    Enchant Gloves - Mastery 1259900 0 171167 2013-07-06T12:29:55-07:00
    Lesser Celestial Essence 49418 139 2013-07-19T13:49:55-07:00 29752 17535
    Greater Celestial Essence 200000 10 2013-07-19T13:49:55-07:00 198989 99406

    These are Columns F through L of my spreadsheet. If I reference 'XML'!F1:L20000, Name is column 1, market is column 2, quantity is column 3, etc.

    So to look up the Quantity of Greater Celestial Essence on my AH with a VLOOKUP in this case:
    =VLOOKUP("Greater Celestial Essence", 'XML'!F1:L20000, 3, FALSE)

    Market Avg of Hypnotic Dust?
    =VLOOKUP("Hypnotic Dust, 'XML'!F1:L20000, 6, FALSE)

    For your spreadsheet:

    okay it looks like Rough stone is in A3 from the screenie, but whatever it is, put this in the lowest buyout column
    =VLOOKUP(A3, YOURSHEETNAME&REFCOLUMNS, (MARKET VALUE COLUMN #), FALSE)

    It will pull the name from A3 and return the market value, then copy this formula select the rows below and paste it, and excel should automatically copy the formatting to make the 2nd reference A4, 3rd reference A5 etc.

    Let me know if you have any questions, I know I'm rambley sometimes lol

  3. #3
    Nordrassil(A)-Winterhoof(A)
    Wind Trader
    TheExile's Avatar
    Posts
    330
    Reputation
    47
    Tagged in
    134 Posts
    Add to this user's reputation
    @PhatLewts Thanks for the reply, this was exactly what I needed! I just have two questions for you, and I think I'll be great and will apply this right away. First, looking at the reference cells you gave me "F1:L20000", did you choose any arbitrary number? From what I understand, a formula containing anything like "D1:D10" is just selecting everything in between those two values, and applying the same operation to them (in my case, I used "=SUM(D4:D31)" when totaling how many mats I had left to buy, under the "Remaining Mats" portion). Or is there a reason you chose the number you did? I only ask because in your first example, =VLOOKUP("Hypnotic Dust", 'XML'!F1:G13291, 2, FALSE)/10000, you used a very specific number (...F1:G13291...), compared to an even number in the "...F1:L20000..." example for my SS.

    Second, is there a way I can specify which realms to draw the data from, instead of just my own? I want to eventually (once I complete this) expand my SS to everyone, and want to provide the minimal work necessary. I was thinking of having two cells asking for the realm and faction, then using those to generate the URL to draw the data from. I'm not overly sure of how to do this, although I do think I have an idea. I'm gonna try it out now, thanks!

    Just thought of another thing - how do you draw in your data from TUJ? I use the macro from Fetching WoW API AH data into Excel / Google Spreadsheet , which only provides item numbers, not item names.
    Last edited by TheExile; July 21st, 2013 at 02:59 PM. Reason: Disable Smilies
    MoP Profession Kit Spreadsheet | No Supply Custom Transmog Market
    Follow me @WoWTheExile
    Remember to +Rep someone if one of their posts help you!

  4. #4
    US-Shattered Halls(H)
    Wind Trader
    PhatLewts's Avatar
    Posts
    833
    Reputation
    81
    Blog Entries
    2
    Tagged in
    446 Posts
    Add to this user's reputation
    The number I used in the first example was the actual size of my data, but you can use an arbitrarily large number to include everything (say if in the future there are more BoE items added to the game). What F1:L20000 will do is select a rectangle box of data, where F1 and L20000 are opposite corners.

    Second question, I'd just name the data page something arbitrary like XML in my case and have them imported.

    To import the XML data from TUJ log in and go to Site -> Market API and it's under "Market Data XML and CSV".

  5. #5
    Nordrassil(A)-Winterhoof(A)
    Wind Trader
    TheExile's Avatar
    Posts
    330
    Reputation
    47
    Tagged in
    134 Posts
    Add to this user's reputation
    Okay so I did everything you said, as far as I can tell. I tried creating a new doc, downloaded the XML data, and put it on the first tab, which I named XML.

    ImpEx.png


    On the second tab, I made cell A1 say "Guild Tabard", which you can see is in the table above (the cell that is selected). In A3 of the second page, I posted the following:

    =VLOOKUP(A1, XML!F1:H20000, 3, FALSE)

    Using this, my result was "#N/A". However, if I replace it with:

    =VLOOKUP(A1, XML!G1:H20000, 2, FALSE)

    It will actually work, to an extent. The only problem I have, is that it imports the number as a whole. . . instead of saying it's worth 95.997, it says 95997. Cosmetic, but could make a world of difference to someone who doesn't know better. Anyway, is there a specific reason the difference between these two formulas causes it to mess up? I can't see much of a difference, as I altered the Market Value Column accordingly with the change of cell ranges.
    MoP Profession Kit Spreadsheet | No Supply Custom Transmog Market
    Follow me @WoWTheExile
    Remember to +Rep someone if one of their posts help you!

  6. #6
    US-Shattered Halls(H)
    Wind Trader
    PhatLewts's Avatar
    Posts
    833
    Reputation
    81
    Blog Entries
    2
    Tagged in
    446 Posts
    Add to this user's reputation
    have your formula divide by 10000, it's just how TUJ stores the data (the price is in Copper).

    =VLOOKUP(A1, XML!G1:H20000, 2, FALSE)/10000
    Last edited by PhatLewts; July 21st, 2013 at 04:00 PM.

  7. #7
    US-Emerald Dream(Both)
    Your Favorite Goblin
    Kathroman's Avatar
    Posts
    5,079
    Reputation
    269
    Blog Entries
    15
    Tagged in
    1975 Posts
    Add to this user's reputation
    Quote Originally Posted by TheExile View Post
    @PhatLewts Thanks for the reply, this was exactly what I needed! I just have two questions for you, and I think I'll be great and will apply this right away. First, looking at the reference cells you gave me "F1:L20000", did you choose any arbitrary number? From what I understand, a formula containing anything like "D110" is just selecting everything in between those two values, and applying the same operation to them (in my case, I used "=SUM(D431)" when totaling how many mats I had left to buy, under the "Remaining Mats" portion). Or is there a reason you chose the number you did? I only ask because in your first example, =VLOOKUP("Hypnotic Dust", 'XML'!F1:G13291, 2, FALSE)/10000, you used a very specific number (...F1:G13291...), compared to an even number in the "...F1:L20000..." example for my SS.

    Second, is there a way I can specify which realms to draw the data from, instead of just my own? I want to eventually (once I complete this) expand my SS to everyone, and want to provide the minimal work necessary. I was thinking of having two cells asking for the realm and faction, then using those to generate the URL to draw the data from. I'm not overly sure of how to do this, although I do think I have an idea. I'm gonna try it out now, thanks!

    Just thought of another thing - how do you draw in your data from TUJ? I use the macro from Fetching WoW API AH data into Excel / Google Spreadsheet , which only provides item numbers, not item names.
    For the realm selection - have them enter their export token from the site (found under resources > data export). Then have the call build the URL dynamically. Juts make sure that you either release it with a dummy token or a blank cell, so they don't end up spamming yours and getting throttled.
    Like what you see? Become an Ethereal Contributor to help support the site and gain access to additional perks.

        

    Guides: Thunder Bluff Vendor Supply Route | Entry Level JC Strategy | Alternative DMF Guide

    TSM 2.0: TSM 2.0 Groups & Operations GUIDE | TSM 2.0 Basic JC Shuffler | TSM 2.0 ADVANCED JC Shuffler

  8. #8
    Nordrassil(A)-Winterhoof(A)
    Wind Trader
    TheExile's Avatar
    Posts
    330
    Reputation
    47
    Tagged in
    134 Posts
    Add to this user's reputation
    @Kathroman That's a great idea, and I understand how to do it, I just don't know how to do it. Again, I'm new to this and learning as I go. Also, there's something else I need to know, that you might be able to help me with. The only way I can find to update the realm info is to re download the XML file and replace the one I was previously using, my formula gets messed up. To update the info, I delete the old XML tab, install the new one. When that happens, the code I was using changes from:

    =VLOOKUP(A4, XML!G1:I20000, 2, FALSE)/10000

    to this:

    =VLOOKUP(A4, #REF!G1:I20000, 2, FALSE)/10000



    Now this is a quick fix after the problem, as I just run a Replace All of "#REF" to "XML". It just sucks to have to do that every time. Any ideas from anyone out there?
    MoP Profession Kit Spreadsheet | No Supply Custom Transmog Market
    Follow me @WoWTheExile
    Remember to +Rep someone if one of their posts help you!

  9. #9
    US-Emerald Dream(Both)
    Your Favorite Goblin
    Kathroman's Avatar
    Posts
    5,079
    Reputation
    269
    Blog Entries
    15
    Tagged in
    1975 Posts
    Add to this user's reputation
    Quote Originally Posted by TheExile View Post
    @Kathroman That's a great idea, and I understand how to do it, I just don't know how to do it. Again, I'm new to this and learning as I go. Also, there's something else I need to know, that you might be able to help me with. The only way I can find to update the realm info is to re download the XML file and replace the one I was previously using, my formula gets messed up. To update the info, I delete the old XML tab, install the new one. When that happens, the code I was using changes from:

    =VLOOKUP(A4, XML!G1:I20000, 2, FALSE)/10000

    to this:

    =VLOOKUP(A4, #REF!G1:I20000, 2, FALSE)/10000



    Now this is a quick fix after the problem, as I just run a Replace All of "#REF" to "XML". It just sucks to have to do that every time. Any ideas from anyone out there?
    Yes - don't replace the tab, just paste OVER the contents of the tab.
    Like what you see? Become an Ethereal Contributor to help support the site and gain access to additional perks.

        

    Guides: Thunder Bluff Vendor Supply Route | Entry Level JC Strategy | Alternative DMF Guide

    TSM 2.0: TSM 2.0 Groups & Operations GUIDE | TSM 2.0 Basic JC Shuffler | TSM 2.0 ADVANCED JC Shuffler

  10. #10
    Nordrassil(A)-Winterhoof(A)
    Wind Trader
    TheExile's Avatar
    Posts
    330
    Reputation
    47
    Tagged in
    134 Posts
    Add to this user's reputation
    Quote Originally Posted by Kathroman View Post
    Yes - don't replace the tab, just paste OVER the contents of the tab.

    The only problem is, when I drag/drop the file onto the Excel tab to replace it, it gives me the options to open the file:

    As an XML Table
    As a read-only workbook.
    Use the XML Source task pane

    No matter which one I choose, it opens the information in an entirely new Excel file. I can't get it to just replace the old information.
    MoP Profession Kit Spreadsheet | No Supply Custom Transmog Market
    Follow me @WoWTheExile
    Remember to +Rep someone if one of their posts help you!

 

 
Page 1 of 3 123 Last

Similar Threads

  1. Replies: 21
    Last Post: September 22nd, 2012, 05:52 AM
  2. Excel SS to grab data from WoWuction...
    By hitmanwa in forum Addons and Macros
    Replies: 0
    Last Post: August 11th, 2012, 12:23 PM
  3. Need help importing data
    By infect3d in forum General Discussion
    Replies: 2
    Last Post: March 15th, 2012, 11:52 AM
  4. Importing MySales Data
    By thomasp1013 in forum General TradeSkillMaster Discussion
    Replies: 5
    Last Post: August 29th, 2011, 09:08 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •