Thursday, 10 July 2014

Excel REST Trading API Example

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
        ' An error occurred
        MsgBox oXMLHTTP.responseText
        Set positions = Nothing
    End If
End Function