VBA Parse JSON Data

Question

I am trying to parse the JSON data from a website. I successfully get the JSON string, but I can't parse it. An exception is thrown in the code below:

Runtime Error 424. Object Required

This is my code:

' Download string from URL Public Function DownloadDataFromURL(url As String) As String Set Myrequest = CreateObject("WinHttp.WinHttpRequest.5.1") Myrequest.Open "GET", url Myrequest.send Dim WebResponse As String WebResponse = Myrequest.responseText Cells(1, 1).Value = WebResponse DownloadDataFromURL = WebResponse End Function ' Download all cryptocoins in Public Sub LoadCryptocoins() Dim Path As String Dim Data As String Dim json As Object Path = "https://api.coinmarketcap.com/v1/ticker/" Data = DownloadDataFromURL(Path) Set jp = New JsonParser Set jo = jp.Decode(Data) For Each Item In jp.EnumKeys(jo) MsgBox (Item.GetValue(jo, "id")) 'The exception is thrown here Next End Sub 

I'm using the JSON Parser from here: Parsing JSON in Excel VBA

The raw JSON data I a processing can be found here: https://api.coinmarketcap.com/v1/ticker/

How can I get every coin name and the price in USD?

Answer 1

Since Excel 2010 version you can download the add-in "Power Query" or if you have 2016 version it comes by default in Excel as "Get & Transform". you can extract data easily, in your case it will be:

1. Go Data
2. Select "From Web"

3. Add the JSON url

It will open the ETL excel tools (Query Editor):

4. Go "Transform"

5. Select "To Table"

6. I didn't change any parameter > OK

7.Click in the corner with two arrows and select the fields what you want.

8. There is a lot of tools to play with the data. Then Go "Home" and select "close and load" it will create a new sheet with your data.

This is the "advance editor" code if you want just to copy and paste:

let Source = Json.Document(Web.Contents("https://api.coinmarketcap.com/v1/ticker/")), #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "name", "symbol", "rank", "price_usd", "price_btc", "24h_volume_usd", "market_cap_usd", "available_supply", "total_supply", "max_supply", "percent_change_1h", "percent_change_24h", "percent_change_7d", "last_updated"}, {"Column1.id", "Column1.name", "Column1.symbol", "Column1.rank", "Column1.price_usd", "Column1.price_btc", "Column1.24h_volume_usd", "Column1.market_cap_usd", "Column1.available_supply", "Column1.total_supply", "Column1.max_supply", "Column1.percent_change_1h", "Column1.percent_change_24h", "Column1.percent_change_7d", "Column1.last_updated"}) in #"Expanded Column1" 

Answer 2

Try the below script. It will fetch you all the data you have mentioned. No external parser is required to achieve this:

Sub coinmarketcap_data() Dim http As New XMLHTTP60, res As Variant With http .Open "GET", "https://api.coinmarketcap.com/v1/ticker/", False .send res = .responseText End With For r = 1 To UBound(Split(res, "id"": """)) Cells(r, 1) = Split(Split(Split(res, "name"": """)(r), """symbol")(0), """")(0) Cells(r, 2) = Split(Split(Split(res, "price_usd"": """)(r), """price_btc")(0), """")(0) Next r End Sub 

Reference to add to the library:

Microsoft XML, v6.0 

Answer 3

The exception that you get is because your parsed JSON object jo is a collection, so you have to loop through the collection items first before you can access each item's keys and values.

I don't know what parser you're using, so I will answer using VBA-JSON:

Public Sub LoadCryptocoins() Dim Path As String Dim Data As String Dim json As Object Path = "https://api.coinmarketcap.com/v1/ticker/" Data = DownloadDataFromURL(Path) Set json = JsonConverter.ParseJson(Data) For Each Item In json MsgBox (Item("name") & " $" & Item("price_usd")) Next End Sub 

Now, using your parser, I imagine it should be something like this:

Public Sub LoadCryptocoins() Dim Path As String Dim Data As String Dim json As Object Path = "https://api.coinmarketcap.com/v1/ticker/" Data = DownloadDataFromURL(Path) Set jp = New JsonParser Set jo = jp.Decode(Data) For Each Item In jo MsgBox (Item.GetValue(jo, "name") & " $" & Item.GetValue(jo, "price_usd")) Next End Sub 

But this is a complete blind shot in the dark. Once you post a link to the parser that you're using, I can update my answer.

Answer 4

Simple example using https://github.com/VBA-tools/VBA-JSON

Public Sub LoadCryptocoins() Dim Path As String Dim Data As String Dim jo As Object, o Dim i As Long Path = "https://api.coinmarketcap.com/v1/ticker/" Data = DownloadDataFromURL(Path) Set jo = json.parse(Data) For i = 1 To jo.Count Set o = jo(i) Debug.Print o("id") Next i End Sub 

The return value from json.parse will be a Collection if the JSON response represents an array, or a Scripting Dictionary if it's an object.