Extract JSON in Excel VBA

Question

I want to parse stock quotes from the Robin Hood API via Excel VBA.

Say I want Amazon, which is https://api.robinhood.com/quotes/?symbols=AMZN.

Which produces:

{  
   "results":[  
      {  
         "ask_price":"1592.3900",
         "ask_size":100,
         "bid_price":"1591.0000",
         "bid_size":500,
         "last_trade_price":"1592.3900",
         "last_extended_hours_trade_price":"1592.0000",
         "previous_close":"1600.1400",
         "adjusted_previous_close":"1600.1400",
         "previous_close_date":"2018-05-07",
         "symbol":"AMZN",
         "trading_halted":false,
         "has_traded":true,
         "last_trade_price_source":"consolidated",
         "updated_at":"2018-05-08T23:58:44Z",
         "instrument":"https://api.robinhood.com/instruments/c0bb3aec-bd1e-471e-a4f0-ca011cbec711/"
      }
   ]
}

Using an example like this answer, I have installed VBA-JSON and turned on Microsoft Scripting Runtime.

My code:

Public Sub STOCKQUOTE()

 Dim http As Object
 Set http = CreateObject("MSXML2.XMLHTTP")

 Const sURL As String = "https://api.robinhood.com/quotes/?symbols=AMZN"
 http.Open "GET", sURL, False
 http.send

 Dim jsonResponse As Dictionary
 Set jsonResponse = JsonConverter.ParseJson(http.responseText)

 Dim results As String
 Set results = jsonResponse("results")

 MsgBox results
End Sub

But this doesn't work, instead I get Compiler Error: Object Required for the line Set results = jsonResponse("results").

If I add Debug.Print http.responseText I see the correct JSON, but any idea what I'm doing wrong?

VBA-JSON is installed correctly, because if I use their example, it works fine:

Dim Json As Object
Set Json = JsonConverter.ParseJson("{""a"":123,""b"":[1,2,3,4],""c"":{""d"":456}}")

But if I try changing Dictionary to Object, I get Run-time error '450': Wrong number of arguments or invalid property assignment.

Answer 1

Your json has an object called results. There could be, but isn't, multiple result objects. You have only one, so I think it's leading to confusion. Each result is going to get it's own entry in your jsonResponse dictionary. The ITEM in that dictionary will, itself, be a dictionary.

The best way to deal with iterating through the dictionary in a dictionary is to declare a new dictionary, I'm calling att for "Attributes" and then fill that dictionary with each iteration through the jsonResponse dictionary. It will only iterate once though as you only have one result:

Public Sub STOCKQUOTE()

    Dim http As Object
    Set http = CreateObject("MSXML2.XMLHTTP")

    Const sURL As String = "https://api.robinhood.com/quotes/?symbols=AMZN"
    http.Open "GET", sURL, False
    http.send

    Dim jsonResponse As Dictionary
    Set jsonResponse = JsonConverter.ParseJson(http.responseText)

    Dim att As Dictionary

    For Each att In jsonResponse("results")
     Debug.Print att("last_trade_price")
    Next att
End Sub

Alternatively, because you have only a single result, you could just refer to that result by it's index in the jsonResponse dictionary and then it's attribute you are after. This makes the code smaller, but if you ever get more than one result from your REST query it will be lost forever. No biggie though since you don't expect that to happen:

Public Sub STOCKQUOTE()

    Dim http As Object
    Set http = CreateObject("MSXML2.XMLHTTP")

    Const sURL As String = "https://api.robinhood.com/quotes/?symbols=AMZN"
    http.Open "GET", sURL, False
    http.send

    Dim jsonResponse As Dictionary
    Set jsonResponse = JsonConverter.ParseJson(http.responseText)

    MsgBox (jsonResponse("results")(1)("last_trade_price"))

End Sub