OpenAPI Functions

OpenAPI for Excel is entirely built around worksheet functions. Every interaction with Saxo's OpenAPI begins with a formula, which follows Excel's standard structure and the following naming convention:

=OpenAPIFunction([argument1, argument2, argument3, ...])

in worksheet cells, or:

Response = Application.Run([OpenAPIFunction],[argument1, argument2, argument3, ...]))

in VBA.

All functions operate within Excel worksheets, although the OpenAPIGet() function should be avoided when more than 1 data field is requested (use OpenAPIGetAutoResize() for this purpose). The following functions do not work in VBA currently and should be avoided:

• OpenAPIGetAutoResize() - use OpenApiGet instead.
• OpenAPISubscribe() - this function sets up a streaming service that VBA cannot interpret.
• OpenAPISubscribeRtd() - same as above, this is a utility function that is used only on worksheets.
• OpenAPIGetAsync() - this function sends asynchronous requests to the API and cannot be used in VBA.

Understanding HTTP requests in Excel

Excel connects to Saxo Bank's OpenAPI by sending HTTP requests through the web, a procedure which can be replicated from scratch using Excel's built-in web data retrieval tool. In the below steps, we will reproduce a single HTTP GET request to the /port/v1/balances/ endpoint and return the data in a worksheet table. This section can be skipped in case the reader is familiar with HTTP requests.

Request a 24-hour token

Excel authenticates automatically with Saxo's authentication servers when the user logs in through the usual flow, and the returned token can only be used against Saxo's gateway through OpenAPI functions. The Developer Portal however allows a client to request a stand-alone token that is valid for 24 hours for testing purposes. Follow this link and accept the disclaimer to obtain a temporary token.

Setting up the HTTP request

Navigate to Data → From Web, and select Advanced on the popup that appears.

Fill out the following URL under URL parts:

https://gateway.saxobank.com/sim/openapi/port/v1/balances/me

And add a HTTP request header of type Authorization, with the value "Bearer " + the token from the Developer Portal copied in, and click OK:

"Bearer eyJhbGciOiJFUzI1NiIsIng1dCI6IkQ2QzA2MDAwMD..."

On the next screen, select the entire path to apply the settings to in order to access the web content and click Connect.

The power query editor

The following screen that appears shows the Power Query Editor, which features a preview of the data that is loaded from the API endpoint. Under the Convert tab, click Into Table to pull the data into an empty Excel spreadsheet. On the next screen, click Close & Load, after which the window closes and the data is loaded onto a new sheet, which (for a plain simulation account) should look something like this:

The HTTP request is now complete! Fiddler shows the details of this interaction as per below. Every time an OpenAPI function is used in Excel, a similar request is sent to Saxo's gateway, which in turn responds with either a response code and confirmation message, or data in the form of a JSON file.