(Solved) – Parse JSON data with VBA – don’t get data as JSON string


Am trying to parse data as JSON with the following code, but am just getting xml data structure:

Public Sub vbajson()

Dim http As Object
Dim sht As Worksheet
Dim authKey As String
Dim accNr As String

Set sht = Worksheets("Account")
authKey = "abc"
accNr = "123"

Set http = CreateObject("MSXML2.XMLHTTP")
With http
.Open "GET", "https://api.tradier.com/v1/accounts/" & accNr & "https://stackoverflow.com/balances", False
.setRequestHeader "Content-type", "application/json"
.setRequestHeader "Accept", "application/json"
.setRequestHeader "Authorization", "Bearer " & authKey
End With

MsgBox http.responsetext

End Sub

msgbox output:
msgbox data structure output tradier

instead of using

.Open "GET", "https://api.tradier.com/v1/accounts/" & accNr & "https://stackoverflow.com/balances", False

I tested the following link, leaving the rest of the VBA code unchanged:

.Open "GET", "http://jsonplaceholder.typicode.com/users", False`

and get the following result, which is the data structure I want:
msgbox data structure output jsonplaceholder

Am also getting data in JSON structure using the following Python code (code borrowed from here: https://documentation.tradier.com/brokerage-api/accounts/get-account-balance)

import requests

response = requests.get('https://api.tradier.com/v1/accounts/123/positions',
    headers={'Authorization': 'Bearer abc', 'Accept': 'application/json'}
json_response = response.json()

Any idea how I need to update my VBA code to get the data in JSON structure? What is the issue?
I want to get JSON, as xml will no longer be supported once the API gets updated.

Leave a Reply

Your email address will not be published. Required fields are marked *