Cant figure out how access an array within an object when parsing data in excel vba

Question

I am trying to pull data from the website 'https://api.iextrading.com/1.0/stock/aapl/financials' onto an excel sheet (originally from https://iextrading.com/developer/docs/#financials). I have been able to pull data from 'https://api.iextrading.com/1.0/stock/aapl/chart/1y' using my code. I tried to alter it for the financials page, but I am getting stuck because I can't figure out how to access the array within the object, my array currently returns a length of 2, ie 'symbol' and 'financials'.

Here is my code:

'write to ws
Dim ws As Worksheet
Set ws = Sheets("Ratios")

Dim ticker As String
ticker = ws.Range("P7").Value

Dim lastrow As Integer
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).row

'clear range
ws.Range("A1:L" & lastrow).Clear

'array col headers
Dim myarray As Variant
myarray = Array("reportDate", "grossProfit", "costOfRevenue", "operatingRevenue", "totalRevenue", "operatingIncome", "netIncome", "researchAndDevelopment", "operatingExpense", "currentAssets", "totalAssets", "totalLiabilities", "currentCash", "currentDebt", "totalCash", "totalDebt", "shareholderEquity", "cashChange", "cashFlow", "operatingGainsLosses")

arrsize = UBound(myarray) - LBound(myarray) + 1

Dim rngTarget As Range
Set rngTarget = ws.Range(Cells(1, 1), Cells(1, arrsize))
rngTarget = myarray

'send web requests for API data
u = "https://api.iextrading.com/1.0/stock/aapl/financials"

Set MyRequest = CreateObject("WinHttp.WinHttpRequest.5.1")
MyRequest.Open "Get", u
MyRequest.Send

'parse Json
Dim json As Object
Set json = JsonConverter.ParseJson(MyRequest.ResponseText)

'get # of objects in Array
Dim arraylen As Integer
arraylen = json.Count

MsgBox (arraylen)

'loop through elements
Dim elements As Variant

Dim x, y, r As Integer

r = 2
y = 1 
x = 1

While x < arraylen + 1

    For Each element In myarray

        ws.Cells(r, y).Value = json(x)(element)

        y = y + 1

    Next element

    y = 1
    x = x + 1
    r = r + 1

Wend

End Sub

I also get a type mismatch regarding the json(x)(element).

What can I add to my code so that I can access the array within the object 'financials'?

QHarr · Accepted Answer · 2019-05-07 17:57:43Z

You need a double For Loop. [] are collections accessed by index and {} are dictionaries accessed by key. The returned object is a dictionary and you need the key financials to return the collection of dictionaries within.

Option Explicit
Public Sub GetData()
    Dim json As Object, results(), item As Object, headers()
    Dim key As Variant, ws As Worksheet, r As Long, c As Long
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", "https://api.iextrading.com/1.0/stock/aapl/financials", False
        .send
        Set json = JsonConverter.ParseJson(.responseText)("financials")
        ReDim results(1 To json.Count, 1 To json.item(1).Count)
        headers = json.item(1).keys
        For Each item In json
            r = r + 1: c = 1
            For Each key In item.keys
                results(r, c) = item(key)
                c = c + 1
            Next
        Next
    End With
    With ws
        .Cells(1, 1).Resize(1, UBound(headers) + 1) = headers
        .Cells(2, 1).Resize(UBound(results, 1), UBound(results, 2)) = results
    End With
End Sub

Collectives™ on Stack Overflow

Cant figure out how access an array within an object when parsing data in excel vba

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related