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
Loop
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"
Else
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
Loop
' 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"
Else
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.
