OpenAPI for Excel

In this tutorial, we will create an automated trading tool that sends orders completely autonomously. Similar to Tutorial 5, we will use a fair bit of VBA to accomplish this task. The idea is to enable the user to send orders in bulk, instead of keying them in one-by-one through any of Saxo's platforms (or through a similar trading interface we developed in Tutorial 7.1). This functionality comes in handy when paired with an algorithm that creates a list of orders. In this tutorial, we assume the client has such as algorithm built already for FX trades, and is looking for the most efficient way to send these orders into their Saxo account. Moreover, because we want to prevent conversion costs, the tool automatically sends each order into the matching currency account.

The following functionality is included in the above spreadsheet:

  • Bulk-order placement (on the "Trade" sheet), which:
    • Pulls down all of the client's accounts (and their currencies)
    • Takes in a list of orders which can be Market, Limit, or Stop with DayOrder or GoodtillCancel durations
    • Runs a precheck to verify symbols and assign the correct accounts to each order
    • Sends the orders off through the OpenAPI
  • Order management (on the "Orders" sheet), which allows the user to:
    • Cancel existing working orders or convert them to market orders
    • Cancel all orders across all accounts automatically
  • Position management (on the "Positions" sheet), which:
    • Shows some basic info on each of the client's positions
    • Allows to close individual positions manually
    • Directly close all positions automatically

The final result looks as below. Notice how the "Trade Account" is assigned for each currency pair (USD-denominated crosses are sent into the USD account, etc). The tool assumes the client can trade every instrument on each of these accounts.

VBA solution

Automating placement of orders, canceling orders, and closing positions all depend on similarly structured VBA code, which cycles through a list of items and measures the elapsed time for each individual series of operations to determine whether the throttling limit is being hit:

Dim curline As Integer
curline = 4

' while there is content on this line
Do While Range("A" & curline) <> ""
	start = GetTickCount()
	' perform operations here
	elapsed = GetTickCount() - start
	If elapsed < 500 Then ' this is the throttling threshold
        Sleep 500 - (elapsed / 2 )
    End If
	curline = curline + 1


The curline integer depends on where in the sheet the list of orders/positions starts. As long as there is content in column A on curline, the operations will be executed for that particular line of data. The Sleep command is important here because the OpenAPI throttles requests when too many are received (1/s on the refdata endpoint, and 2/s on the orders endpoint). To allow sleep times under 1 second (which are not natively supported in VBA), we have to invoke a System32 subroutine and function at the top of each VBA module to get two pieces of functionality:

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Private Declare Function GetTickCount Lib "kernel32" () As Long

The first line declares what Sleep does, which takes a millisecond input and halts VBA for the specified amount of time. The GetTickCount function returns the the millisecond counter from the operating system, which in turn is used to estimate the elapsed time for each operation. This is a straightforward way to measure short time intervals and although it is not 100% accurate, it works reliably enough pace the calls to the OpenAPI.

Using this predefined structure, the CloseAllOrders subroutine is fairly straightforward to implement:

Sub CloseAllOrders()
    Dim curline As Integer
    Dim start As Long
    Dim elapsed As Long
    Dim uri As String
    Dim cancelorder As String
    curline = 4

    Do While Range("A" & curline) <> ""
        start = GetTickCount()
        uri = "/openapi/trade/v2/orders/" & Range("C" & curline).Text & "/?AccountKey=" & Range("B" & curline).Text
        cancelorder = Application.Run("OpenAPIDelete", uri)
        elapsed = GetTickCount() - start
        If InStr(14, cancelorder, "OrderId") Then
            Range("N" & curline) = "Order cancelled"
            Range("N" & curline).Style = "Good"
            Range("N" & curline) = "Error occured: " & cancelorder
            Range("N" & curline).Style = "Bad"
        End If
        curline = curline + 1
        ' pause for 0.333 seconds to prevent rate limiting
        If elapsed < 333 Then
            Sleep 333 - (elapsed / 2)
        End If
    ' refresh list to update open orders and remove messages
    Call RefreshOrderList
End Sub

Notice how the curline is used throughout to keep track of the details of each order and elapsed is used to balance the requests right at the edge of what the throttling allows.

For the single-click manual operations such as converting an order to market from the "Orders" sheet, a similar structure is used as in the previous 2 tutorials:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim uri As String
    Dim body As String
    Dim cancelorder As String
    Dim marketorder As String
    If Target.CountLarge = 1 Then ' if the selection is a single cell
        If Target.Text = "Market" Then ' when Market is clicked
            ' confirm message box for changing to market order
            If MsgBox("Please confirm you want to change this order to Market:" & _
                vbCrLf & vbCrLf & _
                Target.Offset(0, -8) & " " & _
                Target.Offset(0, -6) & " " & _
                Target.Offset(0, -5) & " @ " & _
                Target.Offset(0, -3) & " " & _
                Target.Offset(0, -2), _
                vbYesNo, "Change Order to Market") = vbYes Then
                    ' send edit order request to change to market
                    body = "{" & _
                        "'AccountKey':'" & Target.Offset(0, -10).Text & "', " & _
                        "'Amount':" & Target.Offset(0, -5).Value & ", " & _
                        "'OrderId':'" & Target.Offset(0, -9).Text & "', " & _
                        "'AssetType':'" & Target.Offset(0, -7).Text & "', " & _
                        "'OrderType':'Market', " & _
                        "'OrderDuration':{'DurationType':'DayOrder'}" & _
                    marketorder = Application.Run("OpenAPIPatch", "trade/v2/orders", body)
                    If InStr(3, marketorder, "OrderId") Then
                        Target.Offset(0, 2) = "Converted to Market"
                        Target.Offset(0, 2).Style = "Good"
                        Target.Offset(0, 2) = "Error occured: " & marketorder
                        Target.Offset(0, 2).Style = "Bad"
                    End If
                    Target.Offset(0, 2) = marketorder
                    Sleep 500
                    ' refresh list to update open orders and remove message
                    Call RefreshOrderList
                End If

Using this combination of VBA structure, the overall result is a easy-to-use order placement and management tool.