So far, this tutorial series has explored numerous applications of OpenAPI for Excel, and this tutorial will add trading functionality to the line-up. The goal is to create an automated trader (the "AutoTrader"), that will perform a FX trade based on predefined (static) trading rules. The below screenshot provides an example situation:
- The traded instrument concerns the EUR/USD cross, which will be traded either long or short for a position of 100,000 EUR if the trading rules are met.
- Each 5 seconds, the AutoTrader sends a request to the OpenAPI to get the latest price update.
- For demonstration purposes, the chosen trading rules are simplified: the AutoTrader goes long if the price hits a low boundary (1.13950), and vice versa (at a price of 1.14). This amounts to a very rudimentary mean-reversal strategy, which relies on the assumption that the EURUSD price is expected to float between these two boundaries, and any divergence is corrected in the short-term. By picking long/short positions accordingly, the AutoTrader can profit off this behavior (if the assumption holds true).
The static trading rules can of course be replaced by more intricate measures, such as the ones discussed in Tutorial 2.1 and Tutorial 2.2. A simple rule could be: if RSI hits a value of 70 or higher, trade short at market (and vice versa at a value of 30). The benefit of this approach lies mainly in the fact that a static rule like the one demonstrated on this spreadsheet becomes obsolete quickly if the FX price shifts beyond one of these thresholds and does not return to the other boundary for a long time. Indicators such as RSI take changes in the current price into account.
Choosing an account
Before discussing the VBA code that automatically performs trades, it is important to consider which account the trade is sent to and whether this account has enough balance available to service the trade. This is one of the required parameters for OpenAPI's trading functions. To allow a user to pick one of their accounts, the second tab on this spreadsheet automatically pulls all accounts for the logged in user with the OpenApiGetAccountList() function. The drop-down on the Trader sheet takes up to 5 values from this list, and whenever one is selected, the ClientKey, AccountKey and calculation for available balance are automatically updated on the account sheet:
Framework design
This implementation of OpenAPI for Excel relies on VBA to perform the recurring checks and send orders to the OpenAPI when trading conditions are met. The recurrent process is designed according to the following steps, which are included in the below VBA code:
- Check if the TraderActive flag is set to True. This flag can be reversed by processes such as failed price updates or unconfirmed orders.
- If any of the two trading conditions is True and the current position is not already in line with this decision, the AutoTrader sends a trade. This is a crucial step to prevent the AutoTrader from sending in multiple orders in repeatedly.
- Re-run the same subroutine, taking the value from Interval to time the next call.
Private Sub AutoTrader()
If TraderActive Then 'only run loop if flag still set to True
UpdatePrice 'get latest price
'condition for long posititon
If CurrentPosition <> "Long" And CurrentPrice <= [LowP] Then
TradeLong
[CurPos] = CurrentPosition
[CurValue] = [TradeAmount]
End If
'condition for short position
If CurrentPosition <> "Short" And CurrentPrice >= [HighP] Then
TradeShort
[CurPos] = CurrentPosition
[CurValue] = -[TradeAmount]
End If
'loop AutoTrader
IntervalTime = TimeValue("00:00:0" & [Interval])
Application.OnTime Now() + IntervalTime, "ThisWorkbook.AutoTrader"
End If
End Sub
In order to facilitate orders, the OpenApiPlaceOrder() function is used within Application.Run(), taking the arguments from the main sheet and CurrentPosition flag. If the position is non-existent, trades are sized to 100,000. If a position already exists, the trade size is set to 200,000 to offset the initial position. This way, whenever the trading rules are met, the AutoTrader will always change the open position to exactly 100,000 EUR long or 100,000 EUR short.
The OpenApiPlaceOrder() function returns a text string with either an error or confirmation. If the order is not correctly received, which is checked by the InStr() function, the AutoTrader is automatically halted to prevent it from re-sending the same order. Both trading functions are private subs and cannot be called directly from the spreadsheet. A log entry is added after each trade completes correctly.
Private Sub TradeLong()
If CurrentPosition = "Short" Then
Amount = 2 * [TradeAmount]
Else
Amount = [TradeAmount]
End If
body = "{" & _
"'AccountKey':'" & [accountkey] & "', " & _
"'Amount':" & Amount & ", " & _
"'AssetType':'FxSpot', " & _
"'BuySell':'Buy', " & _
"'Uic':" & [Uic] & ", " & _
"'OrderType':'Market', " & _
"'OrderDuration':{'DurationType':'DayOrder'}, " & _
"'ManualOrder':false" & _
"}"
trade = Application.Run("OpenAPIPost", "trade/v2/orders", body)
'if trade did not go through correctly, stop trader
If InStr(3, trade, "OrderId") <> 3 Then
StopTrader
MsgBox "An error occurred when attempting your trade. The AutoTrader was stopped", , "Error!"
Else
CurrentPosition = "Long"
AddLog
End If
End Sub
Log entries are automatically added to the respective fields using a counter to make sure each new entry is added into the correct line:
Private Sub AddLog()
'add log entries for each trade made
Range("A" & 27 + TradeCounter).value = Time
Range("B" & 27 + TradeCounter).value = "Traded " & CurrentPosition
Range("C" & 27 + TradeCounter).value = TradeAmount
'increment counter
TradeCounter = TradeCounter + 1
End Sub