excel - 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?

question from:https://stackoverflow.com/questions/65644595/parsing-json-in-vba

Answer 1

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: