json API to excel (array)

66 views
Skip to first unread message

Az

unread,
Sep 9, 2014, 4:05:06 AM9/9/14
to excel-r...@googlegroups.com
Hello,

I'm quite new to json and VBA..

At the moment, I'm trying to obtain the data from this API (http://api.guildwars2.com/v2/commerce/listings/19721), where the number is the ID.
I'm currently only interested in the first data on the list.

ID  |  Buy listings | Buy Price | Buy quantity | Sell listings | Sell Price | Sell Quantity
x
y
z

I've tried looking into arrays and rest to excel library but I'm lost on what to do.

Thank you,
Az






Bruce Mcpherson

unread,
Sep 9, 2014, 4:18:20 AM9/9/14
to excel-r...@googlegroups.com
what have you tried ? are you having general problems getting started or specific problem lining up the buys and sells on the same table?

--
You received this message because you are subscribed to the Google Groups "Excel Liberation" group.
To unsubscribe from this group and stop receiving emails from it, send an email to excel-ramblin...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Message has been deleted

Az

unread,
Sep 9, 2014, 8:52:05 PM9/9/14
to excel-r...@googlegroups.com
I'm having general problems getting started.

I've been using the downloaded cDataSet.xlsm as an example. From what I understand, this is what convert json into a readable format by excel. I used the examples to understand generally on how it works (isbn, imdb by title).

However, I'm currently unable to figure out how to populate excel by skipping the "id: xx", selecting the first data on the array from buys and sell and print them out on separate column. 

Thanks,
Az

Bruce Mcpherson

unread,
Sep 10, 2014, 4:42:48 PM9/10/14
to excel-r...@googlegroups.com
ok .. i'll post something tomorrow to show you how..

Bruce Mcpherson

unread,
Sep 15, 2014, 7:49:26 AM9/15/14
to excel-r...@googlegroups.com
Create a sheet called guild with these headings .. you can see the beginning of the result below. note that you must have a blank column between the buys and sells - this is because they both need to have the same column heading which is not allowed in the same table

id listings unit_price quantity listings unit_price quantity
19721 1056 1 8990 1 4214 12
19721 1 2 25 1 4230 2
19721 1 4 25 1 4397 11
19721 3 5 521 2 4398 5
19721 1 7 100 3 4414 348
19721 1 8 1 7 4415 781


The code is very straughtforward .. just a few lines. If you need to append other queries for other ids, you can just set the append argument to true

Private Function guild()
    
    With restQuery("guild", , "19721", , "https://api.guildwars2.com/v2/commerce/listings/", "buys", , , , , , , , , , "id")
        With restQuery("guild!f1", , , , "foo", "sells", , , , , True, .response)
            .tearDown
        End With
        .tearDown
    End With

End Function

Az

unread,
Sep 18, 2014, 5:49:13 AM9/18/14
to excel-r...@googlegroups.com
That worked very well. Thank you very much!
Reply all
Reply to author
Forward
0 new messages