Can anybody help me to get this also work in VBA.
I have successfully used the Python script and it also works in Postman.
But no idea why this doesnt work in VBA.
’ -----------------------------------------------------------------------------------------
’ GetAccountSummary Evaluates the actual Account Summary
’ -----------------------------------------------------------------------------------------
Private Function GetAccountSummary() As T212_AccountSummary
Dim endpoint, url As String
Dim element As Object
Dim AccountSummary As T212_AccountSummary
endpoint = “/api/v0/equity/account/summary”
url = baseURLBP & endpoint
Set oRequest = CreateObject(“MSXML2.serverXMLHTTP”)
With oRequest
.Open “GET”, url, False, apiKey, apiSecret
authHeader = "Basic " & Base64Encode(apiKey & “:” & apiSecret)
.setRequestHeader “Authorization”, authHeader
'.setRequestHeader “Accept”, “application/json”
.send
If (.Status = 200) Then
Set json = JsonConverter.ParseJson(.responseText)
If Not json Is Nothing Then
'code comes here
End If
End If
End With
End Function
Looking at this again:
Your function (GetAccountSummary) is not returning the generated value to the caller. You do not show the definition of T212_AccountSummary but I guess it must be a JSON string. If that is so, you need GetAccountSummary = json where it says ‘code comes here’
You also have apiKey and apiSecret specified twice: in the ‘Open’ and in the authHeader creation. I know nothing about the ‘Open’ as it is obviously a method of the MSXML2.serverXMLHTTP object but my instinct tells me that only the latter one is correct.
this function is not completed.
i still struggle with the send snd authorzation.
yes as this is required in the python example which is provided in the docu.
i also tried the version with base64 conversion only but in vba i only get 401.
my code works fine for bitpanda. so the vba stuff is basically correct.
so either the docu is grap or the vba objects do something different then the python version.
I’ll put it together tomorrow and post it.
What I did was get Claude to write the API code for me and then using my VBA experience to adjust the Excel parts of the code. I can thoroughly recommend the free plan on Claude to write the API code. Generally Claud’s code worked first time.
Here is the VB.Net code I promised:
Imports System.IO
Imports System.Net
Imports System.Text
Public Class Form1
Dim URLAccountSummary As String = "https://live.trading212.com/api/v0/equity/account/summary"
Public Function T212PostReceive(url As String, APIKey As String, APISecret As String) As String
Dim Response As String
'The $ before the quote indicates use of interpolation causing the variable names between braces (squiggly brackets) to be replaced by the variable values
Dim credentials_string As String = $"{APIKey}:{APISecret}"
Dim encoded_credentials As String = Convert.ToBase64String(Encoding.UTF8.GetBytes(credentials_string))
Dim auth_header As String
auth_header = $"Basic {encoded_credentials}"
auth_header = "Authorization: " & auth_header
Response = PostReceive(url,,, auth_header)
Return Response
End Function
'Optional ByVal attributes specify that the parameter as optional and if omitted, the specified value will be used
Public Function PostReceive(URL As String, Optional ByVal ContentType As String = "application/x-www-form-urlencoded", Optional ByVal DataFormat As String = "application/json",
Optional ByVal Headers As String = "", Optional ByVal UserAgent As String = "") As String
'If URL.Contains("/order") Then AppendDiagLog($"URL={URL}")
Dim dataStream As Stream
Dim Response As String = ""
Try
Dim request As HttpWebRequest = HttpWebRequest.Create(URL)
request.Method = "GET"
request.ContentLength = 0
request.ContentType = ContentType 'Set the ContentType property of the WebRequest.
request.Accept = DataFormat 'This requests a formatted response see https://developer.mozilla.org/en-US/docs/Web/HTTP/Basics_of_HTTP/MIME_types
request.UserAgent = UserAgent
If Headers <> "" Then
Dim Hdrs() As String = Headers.Split(",")
For i As Int16 = 0 To Hdrs.GetUpperBound(0)
request.Headers.Add(Hdrs(i))
Next
End If
Try
Dim WebResponse As HttpWebResponse = request.GetResponse() ' Get the response.
dataStream = WebResponse.GetResponseStream() ' Get the stream containing content returned by the server.
If WebResponse.StatusDescription <> "OK" Then
Response = "!API Call to '" & URL & "' Failed. Response = " & WebResponse.StatusDescription
Exit Try
End If
Dim reader As New StreamReader(dataStream) ' Open the stream using a StreamReader for easy access.
Response = reader.ReadToEnd() ' Read the content.
reader.Close() ' Clean up the streams.
dataStream.Close()
WebResponse.Close()
Catch ex As Exception
End Try
Catch ex As Exception
Response = "!API Call to '" & URL & "' Failed" & vbCrLf & ex.Message
End Try
PostReceive = Response
End Function
End Class
Thnx i still cant get this work.
i have all the python examples from the documentation running fine but they dont follow the documentation for how this authentification should be done and also differs with your sample code.
Thats why i am so confused that the documentation allways talks about having the api-key in authorisation header and then it uses apikey and secret as user and password.
i transposed now you code to VBA but the only difference was the UTF8 encoding of the string before encoding base64 which leads to a different string as i get it in this python example where you only can generate the code without a call.
import base64
1. Your credentials
api_key = “<YOUR_API_KEY>”
api_secret = “<YOUR_API_SECRET>”
2. Combine them into a single string
credentials_string = f"{api_key}:{api_secret}"
3. Encode the string to bytes, then Base64 encode it
encoded_credentials =
base64.b64encode(credentials_string.encode(‘utf-8’)).decode(‘utf-8’)
4. The final header value
auth_header = f"Basic {encoded_credentials}"
print(auth_header)
in this script the encoding and decoding utf8 seams a bit sensless.
as all the python examples dont use the string at all i cannot validate whats the issue with the generated string
This is the python example found in all calls and this doesnt use the encodeing at all.
import requests
url = “https://demo.trading212.com/api/v0/equity/account/summary”
headers = {“Authorization”: “YOUR_API_KEY_HERE”}
response = requests.get(url, headers=headers, auth=(‘’,‘’))
data = response.json()
print(data)
at the beginning i used the python example for getting the code and compared it with the string i produced in VBA. it was the same but also this didnt work (using this in Authorization header).
also the curl example works with -u apiKey:apisecret as username and password and not with the encoded string of this two.
as next i will try your code 1:1 in VS and see if it works or not. i first tried to transpose it 1:1 into VBA as you had some additional headers used and the encoding utf8 before base64 encoding.
so this documentation is realy a big pile of mess for me and no clear documentation.
bitpanda api i had running in seconds.
this is a mess.
your code is working excellent and i also see the base64 encoded string which is the same as the one from the python program.
so i need to find out whats the issue with the VBA call as the base64 conversion works in VBA.
thnx for the working code !
OK i solved it - the Base64Encoding function (which i got from the stupid AI) hat a bug and exposed a blank in between the string on a place.
I now found a better version on my own for this Base64Encoding and now i get the same string as your code.
And … it works
So for anyone who needs this in VBA … this works now
' Source - https://stackoverflow.com/a/66055477
' Posted by Steven, modified by community. See post 'Timeline' for change history
' Retrieved 2026-08-22, License - CC BY-SA 4.0
Function EncodeBase64(text As String) As String
Dim arrData() As Byte
arrData = StrConv(text, vbFromUnicode)
Dim objXML As Variant
Dim objNode As Variant
Set objXML = CreateObject("MSXML2.DOMDocument")
Set objNode = objXML.createElement("b64")
objNode.DataType = "bin.base64"
objNode.nodeTypedValue = arrData
EncodeBase64 = Replace(objNode.text, vbLf, "")
Set objNode = Nothing
Set objXML = Nothing
End Function
' -----------------------------------------------------------------------------------------
' GetAccountSummary Evaluates the actual Account Summary
' -----------------------------------------------------------------------------------------
Private Function GetAccountSummary() As T212_AccountSummary
Dim endpoint, url As String
Dim element As Object
Dim AccountSummary As T212_AccountSummary
Dim authHeader As String
endpoint = "/api/v0/equity/account/summary"
url = baseURLBP & endpoint
Set oRequest = CreateObject("MSXML2.serverXMLHTTP.6.0")
With oRequest
.Open "GET", url, False
authHeader = "Basic " & EncodeBase64(apiKey & ":" & apiSecret)
.setRequestHeader "Authorization", authHeader
'.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
.setRequestHeader "Accept", "application/json"
'.setRequestHeader "UserAgent", ""
.send
If (.Status = 200) Then
Set json = JsonConverter.ParseJson(.responseText)
If Not json Is Nothing Then
' here you build your typedata and return it
If json("id") <> "" Then
' GetAccountSummary.id =
End If
If json("currency") <> "" Then
End If
If json("totalValue") <> "" Then
End If
If Not json("cash") Is Nothing Then
If json("cash")("availableToTrade") Then
End If
If json("cash")("reservedForOrders") Then
End If
If json("cash")("inPies") Then
End If
End If
If Not json("investments") Is Nothing Then
If json("investments")("currentValue") Then
End If
If json("investments")("totalCosts") Then
End If
If json("investments")("realizedProfitLoss") Then
End If
If json("investments")("unrealizedProfitLoss") Then
End If
End If
End If
End If
End With
End Function