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.