json API to excel (array)

[Az]

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]

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.

[Az]

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]

ok .. i'll post something tomorrow to show you how..

[Bruce Mcpherson]

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]

That worked very well. Thank you very much!