Call API to get info via Excel VBA

[DJot]

Hi,
I am currently trying to get some info from a website into an excel sheet, using their API as described here:
http s://bittrex.com/home/api

Specifically I am interested in the returned info for the "/public/getmarketsummaries" call.
I would then like to declare variables to some of the info returned to further process it.

It is important that all libraries used can be bound to the excel file itself so the workbook can be distributed to different users and operating systems without the user having to do setup work.

How would I start tackling this?
Thanks for your help in advance.

BR
DJot

[Jan Karel Pieterse]

Different OS-es will be a problem if you want to do this in VBA entirely as it requires an external library to communicate with the web. Perhaps you can use the new web functions Excel 2016 has to offer, or perhaps even Get & transform?

[DJot]

I found a VBA-JSON converter and imported its .bas file as a module into the worksheet. Is this sufficient to use it on different computers or would the .bas file need to be downloaded and linked for every computer trying to use the macro in the workbook?

Btw, here is my code so far which I found as example on a different website:


Public Sub exceljson()

Dim http As Object, JSON As Object, i As Integer
Set http = CreateObject("MSXML2.XMLHTTP")
http.Open "GET", "http s://bittrex.com/api/v1.1/public/getmarketsummaries", False
http.Send
Set JSON = ParseJson(http.responseText)
i = 2
For Each Item In JSON
Sheets(1).Cells(i, 1).Value = Item("MarketName")
Sheets(1).Cells(i, 2).Value = Item("Last")
Sheets(1).Cells(i, 3).Value = Item("Volume")
Sheets(1).Cells(i, 4).Value = Item("BaseVolume")

i = i + 1

Next

MsgBox ("complete")

End Sub


However, I am currently running into the "runtime error 13 type mismatch" error when I try to export the data to the worksheet. Is this because my JSON file is nested?

Thanks,
DJot

[Jan Karel Pieterse]

I seriously doubt the msxml library this code uses is available on an Apple Mac for one.

[DJot]

The JSON / VBA converter library? It actually is available for both Win and Mac on github.

If a library is imported to the module of the workbook, will it be attached to it so that other users don't have to download it separately?

[Jan Karel Pieterse]

No, I refer to your call to MSXML2.XMLHTTP. That is an external library on Windows systems.

[DJot]

Ah okay. Sorry, tbh I don't fully understand that bit of the example code.
I am currently stuck with getting type mismatch error.

Running the code with a simple example JSON works, but when I try to use the real one it doesn't. I assume this is due to the formatting of my response (see below)

{
"success" : true,
"message" : "",
"result" : [{
"MarketName" : "BTC-888",
"High" : 0.00000919,
"Low" : 0.00000820,
"Volume" : 74339.61396015,
"Last" : 0.00000820,
"BaseVolume" : 0.64966963,
"TimeStamp" : "2014-07-09T07:19:30.15",
"Bid" : 0.00000820,
"Ask" : 0.00000831,
"OpenBuyOrders" : 15,
"OpenSellOrders" : 15,
"PrevDay" : 0.00000821,
"Created" : "2014-03-20T06:00:00",
"DisplayMarketName" : null
}, {
"MarketName" : "BTC-A3C",
"High" : 0.00000072,
"Low" : 0.00000001,
"Volume" : 166340678.42280999,
"Last" : 0.00000005,
"BaseVolume" : 17.59720424,
"TimeStamp" : "2014-07-09T07:21:40.51",
"Bid" : 0.00000004,
"Ask" : 0.00000005,
"OpenBuyOrders" : 18,
"OpenSellOrders" : 18,
"PrevDay" : 0.00000002,
"Created" : "2014-05-30T07:57:49.637",
"DisplayMarketName" : null
}
]
}

Any ideas how I should adjust my code to be able to read this?