Parsing JSON in VBA

Question

I'm querying a stock market API and parsing JSON in Excel VBA:

Sub GetCompanyInfo()

Dim hReq As Object, json As Dictionary
Dim i As Long
Dim var As Variant
Dim ws As Worksheet

Set ws = Sheet1

Dim strUrl As String
    strUrl = "https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&symbol=" & ws.Cells(1, 2).Value & "&apikey=x"
    
    
Set hReq = CreateObject("MSXML2.XMLHTTP")
    With hReq
        .Open "GET", strUrl, False
        .Send
    End With
    
Dim response As String
    response = hReq.ResponseText
    ws.Cells(1, 4).Value = response

Set json = JsonConverter.ParseJSON(response)
    i = 0
    For Each Value In json("Time Series (Daily)")
        ws.Cells(i, 1).Value = Value("1. open")
        i = i + 1
    Next Value

End Sub

The response is being written to cell D1, so API call is working:

{
    "Meta Data": {
        "1. Information": "Daily Prices (open, high, low, close) and Volumes",
        "2. Symbol": "AMZN",
        "3. Last Refreshed": "2021-01-08",
        "4. Output Size": "Compact",
        "5. Time Zone": "US/Eastern"
    },
    "Time Series (Daily)": {
        "2021-01-08": {
            "1. open": "3180.0000",
            "2. high": "3190.6400",
            "3. low": "3142.2000",
            "4. close": "3182.7000",
            "5. volume": "3537744"
        },
        "2021-01-07": {
            "1. open": "3157.0000",
            ...

But I'm getting an error Object Required on the set json = JsonConverter.ParseJSON(response) line. Why isn't the JSON response being parsed?

Just checking - you have imported the VBA-json module? – Tim Williams Jan 09 '21 at 22:53 — Tim Williams, Jan 09 '21 at 22:53

Ron Rosenfeld · Answer 1 · 2021-01-10T12:23:32.260

I see several issues with your code:

Set json = JsonConverter.ParseJSON(response)

will return the Object Required error if you have not imported the JsonConverter (or made it accessible to your project).

Once you get past that, you will receive more errors because the subsequent code is incorrect. Should read:

    i = 1
    For Each var In json("Time Series (Daily)")
        ws.Cells(i, 1).Value = json("Time Series (Daily)")(var)("1. open")
        i = i + 1
    Next var

i must start at 1 since cells(0,1) is not valid. (Rows start at row 1)
You need to use var for the loop
each var will be a string which is the key into the json("Time Series (Daily)") dictionary object. So you must rewrite the ws.Cells... line as I have shown to properly access the open price in the dictionary.

After fixing those issues your code works here as you would expect with the following at the beginning of Column A:

FaneDuru · Answer 2 · 2021-01-10T14:00:19.310

0

Please, try changing the json variable declaration. It is enough to be:

Dim json As Object

And use Var instead of Value in the iteration loop

But I will better post the working code (based on what I imagine you want extracting...):

Sub GetCompanyInfo()
 Dim hReq As Object, json  As Object, strUrl As String
 Dim i As Long, var, ws As Worksheet, response As String

 Set ws = ActiveSheet
 strUrl = "https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&symbol=" & _
                                                ws.cells(1, 2).Value & "&apikey=x"
    
 Set hReq = CreateObject("MSXML2.XMLHTTP")
 With hReq
    .Open "GET", strUrl, False: .send
    response = .responseText
 End With
    
 Set json = JsonConverter.ParseJSON(response)("Time Series (Daily)")
 i = 2
 For Each var In json
    ws.cells(i, 1).Value = var
    ws.cells(i, 2).Value = json(var)("1. open")
    i = i + 1
 Next var
 MsgBox "Ready..."
End Sub
The above code works with the assumption that you have the module `JsonConverter` accessible to the project.

edited Jan 10 '21 at 14:00

answered Jan 09 '21 at 16:56

FaneDuru

38,298
4
19
27

Why must JSON be declared as Object? – Ron Rosenfeld Jan 10 '21 at 12:28
@Ron Rosenfeld: Due to the fact that this is what JsonConverter.ParseJSON(...) returns... In JsonConverter module Public Function ParseJSON(ByVal JsonString As String) As Object returns a Object variable. Is there something wrong with this declaration? Of course, using a code referring JsonConverter I assumed that he has the referenced module accessible to the project... A `Scripting.Dictionary` object declaration my work, but not necessary in the context of the above code, since simple `Object` does the job, I think. – FaneDuru Jan 10 '21 at 13:14
It works fine here declaring JSON as a dictionary. So I guess it's the word **must** that I don't understand. And if declaring it as a dictionary were the problem, his error message would be different. No question that **Object** works fine. I just don't think that that variable **must** be declared as an object. – Ron Rosenfeld Jan 10 '21 at 13:33
@Ron Rosenfeld: Agree it. I will change that **must** with something else, more appropriate... – FaneDuru Jan 10 '21 at 13:57

Parsing JSON in VBA

2 Answers2