As much as I have a love-hate relationship with Microsoft, I have to admit the Excel has won me over as a power-user tool, particularly in the world of finance, where it may be used to access powerful back-end analytics servers to provide real-time yield curves and such like.
REST APIs such as IG's Web Trading API make this even easier, as this Excel VBA code snippet to get a list of open positions will hopefully demonstrate. The sample assumes that a login /session request has already been executed and the client and account session tokens have been obtained. The API key is the unique authorisation token required to access the API.
Public Function positions() As Collection
' Set up the HTTP access framework
Set oXMLHTTP = CreateObject("MSXML2.XMLHTTP.6.0")
' Set the API URL
Call oXMLHTTP.Open("GET", IG_API_HOST + "/positions", False)
' Set the HTTP request headers:
' Set the account session token
Call oXMLHTTP.SetRequestHeader("X-SECURITY-TOKEN", m_accountToken)
' Set the client session token
Call oXMLHTTP.SetRequestHeader("CST", m_clientToken)
' Set the API key
Call oXMLHTTP.SetRequestHeader("X-IG-API-KEY", m_apiKey)
' Set the content type to JSON
Call oXMLHTTP.SetRequestHeader("Content-Type", "application/json; chartset=utf-8")
' Set the requested response type to JSON
Call oXMLHTTP.SetRequestHeader("Accept", "application/json; chartset=utf-8")
' Execute the request
Call oXMLHTTP.send
If oXMLHTTP.Status = 200 Then
' Successful. A list of zero or more positions will have been returned
' Extract the response as a JSON object
Dim data As Dictionary
Set data = JSON.parse(oXMLHTTP.responseText)
' Get the list of positions from the response object
Dim positions as Collection
Set positions = data.Item("positions")
' If positions exist, iterate over the list
If Not positions Is Nothing Then
Dim aPosition As Object
' Do something with the position, e.g. extract the instrument name of the underlying market
For Each aPosition In positions
Dim instrumentName as String
Set instrumentName = aPosition.Item("market").Item("instrumentName")
' etc
Next
Else
' An error occurred
MsgBox oXMLHTTP.responseText
Set positions = Nothing
End If
End Function