Register
Page 1 of 9 123 ... Last
Results 1 to 10 of 84
  1. #1
    Pliaksi's Avatar
    Posts
    173
    Reputation
    64
    Tagged in
    86 Posts
    Add to this user's reputation

    Fetching WoW API AH data into Excel / Google Spreadsheet

    Hey folks. I will be showing you how you can query the WoW AH API (applicable to other-than-AH also) with both Excel and Google Spreadsheets. First I will give you a brief of the Pros and Cons of both approaches and some general remarks. The intention of this thread is not not provide a full data analysis solution, rather to help you out if you are developing one of your own.

    General remarks:

    - You are limited to 3000 requests per day (which should be more than enough).
    - You get the data in JSON format, although I will be providing a very very basic parser for it.
    - I still have to test the exact data refresh time, but I believe it is somewhere about 1 hour. Important: currently the macros do not keep track if you have already downloaded a certain file or not. It would be a safe bet to fetch AH data at least at an hour intervals. If however you need means to determine if a file has already been downloaded - ask for it, it should be pretty straightforward.
    - The following code is 100% written by me, so you don't need to worry about any misdoings (if you trust me of course )
    - If you are serious about this, you might want to check out Blizzard Community Platform API Documentation.
    - The Google Spreadsheets example is made simpler on purpose, while the Excel one is more "programatically correct" and modular. I just feel such approach fits the pros and cons of both applications.
    - If you take on the Google Spreadsheet approach, I suggest you just skim trough the Excel one as well, as it is more detailed and elaborate.


    Pros and Cons:


    Comparison chart
    Excel Google Spreadsheets
    Speed Runs mainly on your own PC. Benefits from good PC setup. Runs on Google servers, does not use your own resources. Can be potentially run from a mobile device.
    Accessibility Local file, poor accessibility and protection options. In the cloud, access form anywhere, quite a bit more sophisticated protection options.
    Delayed/Triggered start Can be automated, requires Excel to be running on a machine, not really Excels main strength. Can be easily automated, runs on Google servers, it's fire and forget.
    Spreadsheet functionality Very flexible, great charts, pivots... everything. A bit limited, has everything for your basic needs, but nothing sophisticated.
    Spreadsheet limit Quite large limits if you are using 2007 and above. Quite limited with some odd limitations as well. Why the hell is it 400 000 Cell limit?
    Additional integration Technically can be integrated with Outlook, but I doubt a lot of people use that. Native gmail integration, coupled with triggers this has great potential.
    Request limits. You send requests from your IP, limited to 3000 a day. That one is interesting... it's actually Google sending the request via their IP(s). Technically this could mean breaching the 3000 limit.
    User friendliness It isn't really hard per se... just a lot more so that the alternative. Simple and easy.
    Licences You need to own an Office Package, I don't think this will run with the trial versions. Completely free.
    Portability There were some issues reported, when running this on MacOS (but who likes Apple anyway ). Luckily there is also a solution later in this topic. No known issues. Also could potentially be run on mobile... I just need to figure out how.


    Google spreadsheets:

    All you need to know is how to create a script. Then all you need to do is paste the following code:

    Code:
    function FetchBlizzApi() {
      var response = UrlFetchApp.fetch("http://eu.battle.net/api/wow/auction/data/Defias%20Brotherhood");
      var apiString = response.getContentText().match("http.*json");
      var date = new Date(response.getContentText().match("[0-9]{10,15}")*1);
      var response2 = UrlFetchApp.fetch(apiString);
      var object = Utilities.jsonParse(response2.getContentText());
    
      var ssheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Test1");
      
      if (ssheet.getMaxColumns() > 3) {
        ssheet.deleteColumns(3,ssheet.getMaxColumns()-3);
      }
      if (ssheet.getMaxRows() > 10000) {
        ssheet.deleteRows(2,ssheet.getMaxRows()/3-2);
        ssheet.deleteRows(2,ssheet.getMaxRows()/2-2);
        ssheet.deleteRows(2,ssheet.getMaxRows()/2-2);
      }
      if (ssheet.getMaxRows() > 4) {
        ssheet.deleteRows(2,ssheet.getMaxRows()-2);
      }
      ssheet.clear();
      i = 2;
    
      ssheet.getRange(1,1,1,3).setValues([["Item","Buyout","Quantity"]]);
      
     
      for (var side in object) {
       if (side == "alliance") { 
          for (var auc in object[side]) {
            for (var item in object[side][auc]) {
              ssheet.getRange(i ,1).setValue(object[side][auc][item].item);
              ssheet.getRange(i ,2).setValue(object[side][auc][item].buyout);
              ssheet.getRange(i ,3).setValue(object[side][auc][item].quantity);
              i++;
            }
          }
        }
      }
      Browser.msgBox("Fetched " + i + " auctions for date " + date);
    }
    Naturally you will need to change your realm URL which is in the first line of code (also explained in the Excel example). You also need to change the faction you get data for at this point:

    Code:
    if (side == "alliance") {
    You will also need to have a sheet named "Test1" in your current active spreadsheet, alternatively you can change this in the following line:
    Code:
     var ssheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Test1");
    And you are pretty much done.

    Misc:

    Sending a notification e-mail on a event is pretty easy... the hard part is capturing the event you need. In any case if for instance you want to receive an e-mail, each time a data fetch completes fetch add this before your last closing bracket:

    Code:
    MailApp.sendEmail("someone@something.smt", "Scan completed","Fetched " + i + " auctions for date " + date);


    Excel


    What is required?
    - If you would just like to play around and query your realm's auction house at work or whatever, you would be fine with just copy/paste. The only thing you need to know is where to open VBA script editor, create a module and paste code in it.
    - If you want to develop a spreadsheet on top of this or plug it into your own existing spreadsheet, some basic VBA knowledge will be required (of course you can always ask for help here).


    So lets get started:

    1. First you would need a function to fetch the address and time stamp of your realm's AH data. We do this the following way.
    Code:
    Function getWebData(webAddress As String) As String
        Dim oXHTTP As Object
        Set oXHTTP = CreateObject("MSXML2.XMLHTTP")
        oXHTTP.Open "GET", webAddress, False
        oXHTTP.send
        getWebData = oXHTTP.responsetext()
    End Function
    Basically this is a function that will accept a web address (which I will show you later) and get an HTTP response from it. The response is in JSON format and looks like this:
    Code:
    {
      "files":[
        {
          "url":"http://us.battle.net/auction-data/medivh/auctions.json",
          "lastModified":1314338580000
        }
      ]
    }
    2. Now that we have this we need to extract the address that is after the URL tag and ends in .json. We do this with the following function:
    Code:
    Function getAddress(addrString As String) As String
        firstPos = WorksheetFunction.Find("url", addrString, 1)
        secondPos = WorksheetFunction.Find(".json", addrString, 1)
        getAddress = VBA.Mid(addrString, firstPos + 6, secondPos - firstPos - 1)
    End Function
    3. We would also need the time stamp in format that Excel understands:
    Code:
    Function getDate(addrString As String) As Double
        firstPos = WorksheetFunction.Find("lastModified", addrString, 1)
        secondPos = WorksheetFunction.Find("}", addrString, 1)
        getDate = Val(VBA.Mid(addrString, firstPos + 14, secondPos - firstPos - 20)) / 1000 / 60 / 60 / 24 + 25569
    End Function
    4. After we extract the address we can query the full auction data, using the function in point 1. This would return all of the auctions in JSON format.

    5. A very very basic parser: JSON format might not be the most simple to work with. There are numerous VBA/JSON parsers on the net and there are numerous better ways that I could have done the parsing (RegEx for example) but I decided to keep it as simple and understandable as I can, so here goes:

    Code:
    Sub parseDataToSheet(dataStr As String, dateNum As Double)
        Dim counter As Integer, rowCouner As Integer
        Dim ws As Worksheet
        Dim wsName As String
        
        'Split the data by factions
        splitData = Split(dataStr, "[")
        counter = 0
        
        'Loop through each faction
        For Each Stri In splitData
            counter = counter + 1
            
            'Determine the faction and set the name for the worksheet
            Select Case counter
            Case 2
                wsName = "Alliance " & Format(dateNum, "dd.mm.yyyy hh.mm")
            Case 3
                wsName = "Horde " & Format(dateNum, "dd.mm.yyyy hh.mm")
            Case 4
                wsName = "Neutral " & Format(dateNum, "dd.mm.yyyy hh.mm")
            Case Else
                GoTo Next1
            End Select
            
            'If a sheet with the same name exists - repace it with a new one, if not create a new one
            Application.DisplayAlerts = False
            On Error Resume Next
            ThisWorkbook.Sheets(wsName).Delete
            Application.DisplayAlerts = True
            On Error GoTo 0
            Set ws = ThisWorkbook.Sheets.Add
            ws.Name = wsName
            
            'Set the headers
            ws.Cells(1, 1) = "Auction ID"
            ws.Cells(1, 2) = "Item"
            ws.Cells(1, 3) = "Seller"
            ws.Cells(1, 4) = "Bid"
            ws.Cells(1, 5) = "Buyout"
            ws.Cells(1, 6) = "Quantity"
            ws.Cells(1, 7) = "Timeleft"
            
            'Split by auction
            splitAuc = Split(Stri, "{")
            rowcounter = 1
            
            On Error Resume Next
            'Loop trough auctions
            For Each auc In splitAuc
                'Split data by fields and polulate them
                splitfield = Split(auc, """")
                ws.Cells(rowcounter, 1) = Val(Mid(splitfield(2), 2, Len(splitfield(2)) - 2))
                ws.Cells(rowcounter, 2) = Val(Mid(splitfield(4), 2, Len(splitfield(4)) - 2))
                ws.Cells(rowcounter, 3) = splitfield(7)
                ws.Cells(rowcounter, 4) = Val(Mid(splitfield(10), 2, Len(splitfield(10)) - 2)) / 100 / 100
                ws.Cells(rowcounter, 5) = Val(Mid(splitfield(12), 2, Len(splitfield(12)) - 2)) / 100 / 100
                ws.Cells(rowcounter, 6) = Val(Mid(splitfield(14), 2, Len(splitfield(14)) - 2))
                ws.Cells(rowcounter, 7) = splitfield(17)
                rowcounter = rowcounter + 1
            Next auc
            On Error GoTo 0
    Next1:
        Next Stri
    
    End Sub
    This is going to parse the JSON string into separate sheets for each faction and add an Europen-style time stamp to their name.

    6. Calling all the functions. The following code calls all the functions in the needed order:
    Code:
    Sub ParseMe()
        Dim mystr As String, firstAddr As String, aucData As String
        Dim aucDate As Double
        
        mystr = getWebData("http://us.battle.net/api/wow/auction/data/medivh") 'Get the auction data address and time
        firstAddr = getAddress(mystr)                                                              'Extract the address
        aucDate = getDate(mystr)                                                                   'Extract the date
        aucData = getWebData(firstAddr)                                                         'Get the AH data itself
        Call parseDataToSheet(aucData, aucDate)                                            'Parse the data
    End Sub
    The important part here is the following line:
    Code:
    mystr = getWebData("http://us.battle.net/api/wow/auction/data/medivh")
    This is the single point where you define your realm name and location.

    7. The big COPY/PASTE. In case you would just like to copy/paste it all in a single go, here you are:
    Code:
    Function getWebData(webAddress As String) As String
        Dim oXHTTP As Object
        Set oXHTTP = CreateObject("MSXML2.XMLHTTP")
        oXHTTP.Open "GET", webAddress, False
        oXHTTP.send
        getWebData = oXHTTP.responsetext()
    End Function
    Function getAddress(addrString As String) As String
        firstPos = WorksheetFunction.Find("url", addrString, 1)
        secondPos = WorksheetFunction.Find(".json", addrString, 1)
        getAddress = VBA.Mid(addrString, firstPos + 6, secondPos - firstPos - 1)
    End Function
    Function getDate(addrString As String) As Double
        firstPos = WorksheetFunction.Find("lastModified", addrString, 1)
        secondPos = WorksheetFunction.Find("}", addrString, 1)
        getDate = Val(VBA.Mid(addrString, firstPos + 14, secondPos - firstPos - 20)) / 1000 / 60 / 60 / 24 + 25569
    End Function
    Sub parseDataToSheet(dataStr As String, dateNum As Double)
        Dim counter As Integer, rowCouner As Integer
        Dim ws As Worksheet
        Dim wsName As String
        
        'Split the data by factions
        splitData = Split(dataStr, "[")
        counter = 0
        
        'Loop through each faction
        For Each Stri In splitData
            counter = counter + 1
            
            'Determine the faction and set the name for the worksheet
            Select Case counter
            Case 2
                wsName = "Alliance " & Format(dateNum, "dd.mm.yyyy hh.mm")
            Case 3
                wsName = "Horde " & Format(dateNum, "dd.mm.yyyy hh.mm")
            Case 4
                wsName = "Neutral " & Format(dateNum, "dd.mm.yyyy hh.mm")
            Case Else
                GoTo Next1
            End Select
            
            'If a sheet with the same name exists - repace it with a new one, if not create a new one
            Application.DisplayAlerts = False
            On Error Resume Next
            ThisWorkbook.Sheets(wsName).Delete
            Application.DisplayAlerts = True
            On Error GoTo 0
            Set ws = ThisWorkbook.Sheets.Add
            ws.Name = wsName
            
            'Set the headers
            ws.Cells(1, 1) = "Auction ID"
            ws.Cells(1, 2) = "Item"
            ws.Cells(1, 3) = "Seller"
            ws.Cells(1, 4) = "Bid"
            ws.Cells(1, 5) = "Buyout"
            ws.Cells(1, 6) = "Quantity"
            ws.Cells(1, 7) = "Timeleft"
            
            'Split by auction
            splitAuc = Split(Stri, "{")
            rowcounter = 1
            
            On Error Resume Next
            'Loop trough auctions
            For Each auc In splitAuc
                'Split data by fields and polulate them
                splitfield = Split(auc, """")
                ws.Cells(rowcounter, 1) = Val(Mid(splitfield(2), 2, Len(splitfield(2)) - 2))
                ws.Cells(rowcounter, 2) = Val(Mid(splitfield(4), 2, Len(splitfield(4)) - 2))
                ws.Cells(rowcounter, 3) = splitfield(7)
                ws.Cells(rowcounter, 4) = Val(Mid(splitfield(10), 2, Len(splitfield(10)) - 2)) / 100 / 100
                ws.Cells(rowcounter, 5) = Val(Mid(splitfield(12), 2, Len(splitfield(12)) - 2)) / 100 / 100
                ws.Cells(rowcounter, 6) = Val(Mid(splitfield(14), 2, Len(splitfield(14)) - 2))
                ws.Cells(rowcounter, 7) = splitfield(17)
                rowcounter = rowcounter + 1
            Next auc
            On Error GoTo 0
    Next1:
        Next Stri
    
    End Sub
    Sub ParseMe()
        Dim mystr As String, firstAddr As String, aucData As String
        Dim aucDate As Double
        
        mystr = getWebData("http://us.battle.net/api/wow/auction/data/medivh") 'Get the auction data address and time
        firstAddr = getAddress(mystr)                                                              'Extract the address
        aucDate = getDate(mystr)                                                                   'Extract the date
        aucData = getWebData(firstAddr)                                                         'Get the AH data itself
        Call parseDataToSheet(aucData, aucDate)                                            'Parse the data
    End Sub
    8.Finally just run the Macro "ParseMe".


    Some additional stuff:

    - If your realm has a space in it's name use "%20"... for example:
    Code:
    getWebData("http://eu.battle.net/api/wow/auction/data/Defias%20Brotherhood")
    - As you might have noticed, the data you get consists of item IDs rather than item names. There is a way to get detailed item data, but I won't include ready-to-use scripts to do so. The reason behind this is the 3000 request limit, and that generally you will be doing more harm if you just mass spam info requests multiple times. However I will provide general idea of what you are supposed to do:
    1. You need to build a string for the data you want to fetch. It should look like this:
    Code:
    http://us.battle.net/api/wow//item/38268
    2. You can then pass this string to the getWebData function listed earlier.
    3. The response you will receive will look like this:
    Code:
    {
      "id":38268,
      "description":"Give to a Friend",
      "name":"Spare Hand",
      "icon":"inv_gauntlets_09",
      "stackable":1,
      "itemBind":0,
      "bonusStats":[],
      "itemSpells":[],
      "buyPrice":12,
      "itemClass":2,
      "itemSubClass":14,
      "containerSlots":0,
      "weaponInfo":{
        "damage":[
          {
            "minDamage":1,
            "maxDamage":2
          }
        ],
        "weaponSpeed":2.5,
        "dps":0.6
      },
      "inventoryType":13,
      "equippable":true,
      "itemLevel":1,
      "maxCount":0,
      "maxDurability":16,
      "minFactionId":0,
      "minReputation":0,
      "quality":0,
      "sellPrice":2,
      "requiredSkill":0,
      "requiredLevel":70,
      "requiredSkillRank":0,
      "itemSource":{
        "sourceId":0,
        "sourceType":"NONE"
      },
      "baseArmor":0,
      "hasSockets":false,
      "isAuctionable":true
    }
    4. Then finally you have to parse out the data you need. The parseDataToSheet function that I provided will not work properly for the purpose of parsing this string.

    Misc:

    Testing refresh time - either I fail terribly hard at searching, or there is absolutely no info on the web at what times Bliz refresh the AH data. Below is the procedure I use to determine the refresh time. What it does is to request the time stamp each 5 minutes up to 100 min and compare it to the time stamp when the script is loaded. Note that your current instance of Excel will likely become unresponsive for the period. The output will be in your VBA debugging window.

    Code:
    Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
    Sub getResponseTime()
    Dim str As String, str2 As String
    Dim time As Double, time2 As Double
    
    nowT = VBA.now
    str = getWebData("http://eu.battle.net/api/wow/auction/data/Defias%20Brotherhood")
    time = getDate(str)
    
    For i = 1 To 20
        str2 = getWebData("http://eu.battle.net/api/wow/auction/data/Defias%20Brotherhood")
        If StrComp(str, str2, vbTextCompare) Then
            time2 = getDate(str2)
            Debug.Print Format(time2 - time, "dd.mm.yyyy hh:mm:ss")
            Exit Sub
        End If
        Sleep 300000
    Next i
    End Sub
    Also if you manage to have the nerves to wait for a change, please do post the results


    If you have any questions, feel free to post here and I will answer if I can.
    Last edited by Pliaksi; December 22nd, 2011 at 02:34 AM. Reason: adding some stuff
    It's not a bug, it's a feature !

  2. #2
    Brilliant, I just tested this and it works beautifully. Thanks very much, Pliaksi!

    As many goblins, I have developed my own Excel-based All-In-One AH Tool over time. I have used MarketWatcher to extract the AH data via lua parsing for a very long time, but now I will switch to this instead. This will save precious minutes every day doing scans in game as well. Now, the only daily lua-based imports remaining are wealth (gold count per character) and stock (itemID count per character's bank, mail, auctions and inventory). I doubt this will every become available through the API, but this still reduces the effort by 80%. Thanks again!

  3. #3
    OK, everything works fine on Excel 2010 but when I try it on Excel 2011 on my Mac (which I prefer to play WoW on) I get an error when executing the macro above. Since MSXML isn't supported on Macs, is it possible to build another getWebdata function that does not rely on MSXML? I guess it should be possible to open a connection based on QueryTables.Add somehow, but since I haven't worked much with web queries I'm at a roadblock at the moment.

  4. #4
    Pliaksi's Avatar
    Posts
    173
    Reputation
    64
    Tagged in
    86 Posts
    Add to this user's reputation
    You might try "WinHttp.WinHttpRequest.5.1" object instead of MSXML, but the "Win" in the name tells me it would not work out very well. I initially tried to fetch the data with QueryTables but did not succeed. Will try again once I get back to work on monday.

    Edit: Scratch that, I found the following, but can't test it right now:
    Code:
    With ActiveSheet.QueryTables.Add(Connection:="URL;http://carbon.brighterplanet.com/flights.txt", Destination:=Range("A2"))
        .PostText = "origin_airport=MSN&destination_airport=ORD"
        .RefreshStyle = xlOverwriteCells
        .SaveData = True
        .Refresh
    End With
    Try to give it a go. It is supposed to work specifficaly on Mac.
    Last edited by Pliaksi; August 26th, 2011 at 11:05 AM.
    It's not a bug, it's a feature !

  5. #5
    It gives me a nice import and enough to work with. I had tried something similar but forgotten to add the URL; bit before the http address. Thanks for the tip.

  6. #6
    A side note on fetching item data and the request limit: The limit is per region, so you can do 3k requests on US, EU, TW, ... each.

    So what I do (I store my servers auction history in my own SQL DB and run analysis on it, since it is not supported on TUJ) is:
    Fetch item info from tw.battle.net and kr.battle.net (don't forget to add ?locale=en_US if you want english item names).
    So that even if I shoot over my limit on TW and KR regions I can still fetch my auctions from my EU realm (plus I insert all item data into my DB as well and once a day fetch the items I don't have).

  7. #7
    Pliaksi's Avatar
    Posts
    173
    Reputation
    64
    Tagged in
    86 Posts
    Add to this user's reputation
    Edit: Moving this to OP and formatting it a bit.
    Last edited by Pliaksi; December 22nd, 2011 at 01:22 AM.
    It's not a bug, it's a feature !

  8. #8
    Sterling's Avatar
    Posts
    3,856
    Reputation
    378
    Blog Entries
    1
    Tagged in
    2134 Posts
    Add to this user's reputation
    I somehow missed this when it was first posted, good job +rep.

    Moving to the add-on forum and sticky as well.
    Must-See: Stormspire.net Survival Guide | How-to: Tooltips in your posts | Consortium Key Sender (CKS) | Phat Lewts' Learn TSM Series
    Legion Mastery Series: Sniper | Cross-Faction Trading | Cooking | Blacksmithing, Leatherworking, Tailoring Master
    Old World: Smelting | Garrisons Part 1 - Buildings | Garrisons Part 2 - Followers | Profitable Alchemy Crafts
    Archived: Enchanting | Jewelcrafting | Toy Box Guide | Blacksmithing Transmog Guide
    Want to get the most out of the site and support the community? Consider Premium Membership
    Find a post useful? Give the author rep! And above all, be respectful and friendly to everyone.

  9. #9
    Pliaksi's Avatar
    Posts
    173
    Reputation
    64
    Tagged in
    86 Posts
    Add to this user's reputation
    @Sterling I changed the topic to reflect that it now includes Google Spreadsheets as well. Unfortunately this seems to have no effect outside of the thread (when viewing all threads in the forum). Can you help with this one please ?
    It's not a bug, it's a feature !

  10. #10
    Are there any sample Excel files to download? I know, I'm being lazy. The instructions above look good but I have not used VBA.
    Never mind. I followed the VBA tutorial and have it working.
    thanks.
    Last edited by fireymerlin; December 24th, 2011 at 11:43 PM.

 

 

Similar Threads

  1. Exporting UMJ data to Excel 2010 error
    By Blinky in forum Official Forum of The Undermine Journal
    Replies: 11
    Last Post: November 8th, 2011, 12:35 PM