TD Ameritrade API JSON

[cyclone-isu2001]

Trying to apply this with a TD Ameritrade API call. When the JSON gets returned and the script runs, its response is "JSON has no rows". Any input or advice would be greatly appreciated... The JSON seems to parse using an online parser such as: http://json.parser.online.fr/
return string.txt

[omegastripes]

@cyclone-isu2001 remove the code after JSON.Parse line in the example, and create code, which depends on your needs.

[omegastripes]

To make things clear here is the part of the repsonse:

So, there are lets say Sections, Categories, Subcategories and Properties with some values in the source data. How to show it as a table? Which entities should be converted to rows and which to columns in the resulting table?

[omegastripes]

As for me I would split it something like

It depends on the code you create, but not on parser anyway) And JSON.ToArray facilitate to build result.

[cyclone-isu2001]

Man - that helps a ton... so there is a put expiration map and a call expiration map (sections)... then there are expirations dates (sub-categories), then strike prices (categories), then properties for each strike price (bid, ask last, mark, etc...

a representative table would look like the attached:

[omegastripes]

Source JSON data and representative table have quite different structure, so you should extensively rebuild parts of data to accomplish that. One more to be noted is that actually you displayed not the single table but a list of tables, one table for each date, in this case better to make a single table first on one worksheet, and the create a pivot table to split the data to sections for each date on another worksheet.

[omegastripes]

Try something like this

Sub test()
    Dim resp As String
    Dim data
    Dim state As String
    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", "<TD Ameritrade API call URL here>", True
        .Send
        Do Until .ReadyState = 4: DoEvents: Loop
        resp = .ResponseText
    End With
    JSON.Parse resp, data, state
    Dim putEdm
    Set putEdm = data("putExpDateMap")
    Dim callEdm
    Set callEdm = data("callExpDateMap")
    Dim resultRows
    Set resultRows = New Dictionary
    Dim keyDate
    For Each keyDate In putEdm
        Debug.Print "Date " & keyDate
        Dim eltDate
        Set eltDate = putEdm(keyDate)
        Dim keyStrike
        For Each keyStrike In eltDate
            Debug.Print "Strike " & keyStrike
            Dim putProps
            Set putProps = eltDate(keyStrike)(0)
            Dim callProps
            Set callProps = callEdm(keyDate)(keyStrike)(0)
            Dim resultRow
            Set resultRow = New Dictionary
            resultRow("exp") = keyDate
            resultRow("strike") = keyStrike
            Set resultRow("put") = putProps
            Set resultRow("call") = callProps
            Set resultRows(resultRows.Count) = resultRow
        Next
    Next
    Dim tableData()
    Dim tableHeader()
    JSON.ToArray resultRows, tableData, tableHeader
    With ThisWorkbook.Sheets(1)
        .Activate
        .Cells.Delete
        With .Cells(1, 1)
            .Resize(1, UBound(tableHeader) - LBound(tableHeader) + 1).Value = tableHeader
            .Offset(1, 0).Resize( _
                    UBound(tableData, 1) - LBound(tableData, 1) + 1, _
                    UBound(tableData, 2) - LBound(tableData, 2) + 1 _
                ).Value = tableData
        End With
        .Columns.AutoFit
    End With
    MsgBox "Completed"
End Sub