Saxo Bank Developer Portal

2019-06-17, Gid van der Ven

Deprecation of OpenAPI Trade UDFs

With version 1.3.0, all UDFs in the "OpenAPI Trading" category have been removed from the add-in. To comply with requirements for algo trading tools, users on the Excel platform will have to construct orders 'from scratch' using the provided HTTP request functions in Excel:

OpenAPIPost() to create a new order, either to open a position or close an existing position.
OpenAPIPatch() to modify the details of an existing order.
OpenAPIDelete() to remove an existing order.

Except for OpenAPIDelete(), these functions require a body to be provided in the request, which contains the details of the order. This body has to be constructed separately in VBA and should include the required fields as specified by the /trade/v2/orders endpoint. The body is a string containing JSON-structured data.

The below guide draws on Tutorial 8, which implements the new order functionality and can be used as pointer for creating more complex order interactions.

The following UFDs will no longer be supported:

OpenAPIPlaceOrder()
OpenAPIEditOrder()
OpenAPICancelOrder()
OpenAPIAddRelatedOrder()
OpenAPIClosePosition()

Clients on earlier versions of the add-in will not directly be blocked from using these functions. It is advised to migrate timely, as new requirements for order placement will not be implemented for deprecated functions. These UDFs will stop working altogether once the requirements for order placement are enforced (planned Q3 2019).

Creating a new order

The following VBA code can be used to create a new order. It assumes the variables that are passed into the string that contains the order body are defined beforehand. Instead of the OpenAPIPlaceOrder() formula, the input is now directly passed into OpenAPIPost(). Input variables can be either string or integer (long) types.

Notice how the "double quotes" are used to chain the string types together in VBA, while the 'single quotes' are used within the body to identify the individual items in the JSON structure.

body = "{" & _
	"'AccountKey':'" & accountkey & "', " & _
	"'Amount':" & amount & ", " & _
	"'AssetType':'" & assettype & "', " & _
	"'BuySell':'" & buysell & "', " & _
	"'Uic':" & uic & ", " & _
	"'OrderType':'" & ordertype & "', " & _
	"'OrderPrice':" & orderprice & ", " & _
	"'OrderDuration':{'DurationType':'" & duration & "'}, " & _
	"'ManualOrder':false" & _
"}"

Debug.Print (body)

order = Application.Run("OpenAPIPost", "trade/v2/orders", body)

Debug.Print (order)

For a simple EURUSD (UIC 21) Market DayOrder to Buy 15K that was generated automatically, the following output is printed by the Debug statements:

{'AccountKey':'youraccountkey', 'Amount':15000, 'AssetType':'FxSpot', 'BuySell':'Buy', 'Uic':21,
'OrderType':'Market', 'OrderPrice':, 'OrderDuration':{'DurationType':'DayOrder'}, 'ManualOrder':false}

{"OrderId":"77894810"}

Notice how this procedure is similar compared to providing the required input for the deprecated OpenAPIPlaceOrder() function. Because the input has to be converted to a JSON text string, the formatting required to add the special characters makes the code less readable. For this reason, it is advised to always print out the actual request body (and of course the response from the OpenAPI contained in the 'order' variable), to validate the structure. The OpenAPI will always reject incorrectly formatted JSON strings. In addition, sending a request directly to the OpenAPI significantly improves performance, as the deprecated formulas dependent on multiple interactions to achieve a similar result.

Modifying an existing order

The below VBA code shows how an existing resting order can be converted to a market order (as an example). To achieve this, the OpenAPIPatch() function is used. Again, the details of the order are assumed to be available in the associated variables.

body = "{" & _
	"'AccountKey':'" & accountkey & "', " & _
	"'Amount':" & amount & ", " & _
	"'OrderId':'" & orderid & "', " & _
	"'AssetType':'" & assettype & "', " & _
	"'OrderType':'Market', " & _
	"'OrderDuration':{'DurationType':'DayOrder'}" & _
"}"

Debug.Print (body)

marketorder = Application.Run("OpenAPIPatch", "trade/v2/orders", body)

Debug.Print (marketorder)

Which prints the following when run:

{'AccountKey':'youraccountkey', 'Amount':15000, 'OrderId':'77894811',
'AssetType':'FxSpot', 'OrderType':'Market', 'OrderDuration':{'DurationType':'DayOrder'}}

{"OrderId":"77894811"}

This procedure is similar to the OpenAPIEditOrder() function. Comparable performance benefits are expected as above. Notice how the OpenAPI echos the OrderId to confirm the change was made successfully.

Deleting an existing order

This is arguably the easiest procedure, as it only requires the accountkey and orderid variables:

uri = "trade/v2/orders/" & orderid & "/?AccountKey=" & accountkey

Debug.Print (uri)

cancelorder = Application.Run("OpenAPIDelete", uri)

Debug.Print (cancelorder)

Which prints the following when executed:

trade/v2/orders/77894813/?AccountKey=youraccountkey
{"Orders":[{"OrderId":"77894813"}]}

As before, the OpenAPI echos the OrderId to confirm the order was removed correctly.

Closing a position

Closing a position can be achieved using a POST request similar to the one above (used to open a position). The main difference is the addition of the "PositionId" field in the body of the request, which signals to the OpenAPI that this order should offset an existing position and change its state to "Closed". For this to work properly, the order details should match the existing position. The "BuySell" field is the only field that directly relies on the existing position (long positions are closed with a SELL order, and vice versa).

body = "{" & _
    "Orders:[{'AccountKey':'" & accountkey & "', " & _
    "'Amount':" & amount & ", " & _
    "'AssetType':'" & assettype & "', " & _
    "'BuySell':'" & buysell & "', " & _
    "'Uic':" & uic & ", " & _
    "'OrderType':'Market', " & _
    "'OrderDuration':{'DurationType':'DayOrder'}, " & _
    "'ManualOrder':false}], " & _
    "'PositionId':'" & positionid & "'" & _
"}"

closeorder = Application.Run("OpenAPIPost", "trade/v2/orders", body)

As before, the OpenAPI echos the OrderId, which is assigned to the "closeorder" variable, to confirm order was received.

Automation