Get Started with Saxo’s OpenAPI for Excel 

OpenAPI is the gateway to Saxo's IT infrastructure. The API allows clients and partners to build customized trading applications that communicate directly with Saxo's trading systems. All of Saxo’s next generation trading platforms are built on top of the OpenAPI.

OpenAPI for Excel exposes all the power of OpenAPI directly in Excel. However for advanced use cases, clients and partners can also use the OpenAPI directly. For more information on OpenAPI, please contact your local sales representative.

• Offers a flexible Excel based trading and account view solution for proficient Excel users

• Gives access to all the info you need to trade and manage your portfolios (positions, orders, trades, prices) directly in Excel

• Enables you to create standalone sheets and adjust the sheets to your own unique use cases

• Enables the sending of trades and orders to Saxo in a non-manual way

• Allows you to automate all the tasks you would usually perform in a stand-alone platform

OpenAPI for Excel will easily integrate with other external plugins and is easy to implement into your existing worksheets and models. The Plugin will work in any sheet and will not require macros to be enabled in order to use. However, it should be stressed that in order to leverage the true power of the plugin it is advised to create additional logic via VBA. It is furthermore possible to be logged in to OpenAPI for Excel while you are logged in to other Saxo trading platforms such as SaxoTraderGO.

Some of the possible use cases for OpenAPI for Excel are:

• Trading on Algo output – Excel output from trading models can be executed efficiently

• Trading on specific times or trading based on dynamic data from external systems or from the API

• Full-fledged robo-trading (non low latency)

• FX hedging - sheets can be configured to automatically hedge other positions or transactions once these are committed

• Bulk trading on behalf of multiple clients or accounts

• Portfolio rebalancing and monitoring across clients for smaller wealth managers or hedge funds

• Back testing FX based models

• Access to real time data on positions, orders and balances directly in Excel for reconciliation purposes or risk monitoring

All functionality in OpenAPI for Excel is exposed via worksheets functions. This means that everything from committing trades to viewing positions can be done by typing a formula in to a worksheet cell.

The syntax for the formulas mirrors the syntax of OpenAPI which means that everything you can do in OpenAPI can be done in Excel. This flexibility comes at the cost of ease of use as the syntax can be somewhat complicated.

The plugin, however, includes an “examples button” which allows users to generate a wide range of predefined calls with the columns of their choosing in any worksheet. These examples can be modified once the user has gained familiarity with the API. (This functionality is currently being developed).

If you want to create advanced queries please reference the OpenAPI developer portal for all available functions and parameters. For access to the OpenAPI developer portal, please contact your account representative.

Most API functions will work in VBA and can be called via the Application.Run() syntax, however, real-time (RTD) functions cannot at this time be called via VBA. This means that OpenAPISubscribe() will not work in VBA.

OpenAPI for Excel allows you to automate your trading strategies in order to reduce manual workflows. Furthermore combining OpenAPI with the flexibility of Microsoft Excel VBA allows you to create sophisticated trading models or automate orders based on rules or trading signals.

If you are enabled for trading via OpenAPI for Excel, your normal commission scheme will and you will be able to trade most of the instruments that are currently offered in SaxoTraderGO.

Saxo exposes all the information you need in order to manage your trading as either real-time updating views, or as static datasets. In other words, all of the information that is visible today in our traditional trading platforms can now be exposed via Excel.

It should be noted that Saxo will not expose market data for non FX instruments.

To get started with OpenAPI for Excel, please download the plugin from the OpenAPI for Excel page.

If you need a demo user this can be created at our homepage. This user will work for one month.

The plugin comes in a 32 or 64 bit version.

Internet explorer 9 or higher is required alongside .Net Framework 4.6.1

There are two ways to load the OpenAPI .xll plugin in Excel:

You can launch it directly and then open any excel file to perform operations using formulas.

Alternatively you can add it using the 'Developer' tab from the top ribbon. Both are described in detail below.

Double click on the 'OpenAPI.xll' file to launch the plugin with excel. It will launch excel without any workbooks open. So you can open any existing or new workbook from 'File' menu.

Then click on the 'Saxo Bank' tab in ribbon to proceed further.

Launch a new instance of Excel.
          If you don't see 'Developer' tab in top ribbon, then enable it by going to File -> Options -> Customize Ribbon -> (Check this option as shown)

Go to 'Developer' tab in top ribbon and click on button.  button.

Browse to the plugin file OpenAPI.xll to load it.

Then click on the 'Open Api’ tab in ribbon to proceed further.

 

From The 'Open Api' ribbon it is possible to login with enabled Saxo credentials: Client Id and Password.

Choose the environment (LIVE/LIVE-Read/SIM) and press login:




The credentials are the same used for logging in to any other Saxo platform.

Saxo has produced a range of example sheets which can be found at the OpenAPI for Excel landing page. Additional samples will be released over time.

Saxo is in no way liable for any shortcoming in the sheets.

The Example sheets shows a limited sample of simple use cases that can be covered by the Excel API, VBA code is not hidden and can be used or modified at the client’s initiative.

All functions in OpenAPI for Excel are interacting directly with OpenAPI. The Excel plugin thereby functions as a wrapper handling interactions between Excel and OpenAPI.

For functionality to be available through the plugin it must be exposed for this purpose in OpenAPI. Furthermore certain public endpoints in OpenAPI can be restricted for use with the plugin.

An endpoint is used for getting or submitting data from or to OpenAPI. The OpenAPI currently powers SaxoTraderGO which means that all functionality currently in SaxoTraderGO can potentially be exposed through the public version of OpenAPI and to OpenAPI for Excel.

The Functions in the plugin are written in Excel cells to retrieve or submit data. Most functions apart from OpenAPISubscribe also work for implementation in Excel VBA.

This is done by using application.run ("FunctionName","Parameters"). Examples of VBA code can be found in the supplied example sheet.

The formula Builder allows easy access to all available data in OpenAPI.

The Formula Builder can be found in the OpenAPI for Excel toolbar.

The tool allows the user to browse all available Subscribe () and Get () endpoints.
The syntax generated via the tool can afterwards be evaluated, changed and reused across Excel using the functions found below.

The user can choose Formula type and endpoint in the dropdown dialogs.

The user can actively choose which columns to display in the Excel sheet by picking from a list of all available columns. A column is picked by selecting 1 or more columns and pressing the arrow to the right. If any input is needed in order to populate the data, input fields or dropdowns will dynamically be shown at the bottom of the form.

Once the user is satisfied with the columns and input fields the “Generate Formula” button can be pressed.

This will populate the excel sheet with the selected data at the position of the currently selected cell.

Avoid populating close to important data as the formula might override the existing cells.

Utility functions - frequently used functions used for exposing things such as clientkeys (unique client identifier), Accountkeys (unique account identifier) or a list of all accounts under the currently logged in user.

Trading functions - used for placing orders and related orders, updating orders and cancelling orders

Generic Functions - These functions are used to communicate directly with OpenAPI. Users will specify the endpoint and the input parameters as well as the columns which should be populated in the sheet (get and subscribe functions).

OpenAPIGet - Used for getting a snapshot of data. The user needs to specify the size of the output area and commit the formula using “CTRL-SHIFT-ENTER” to get the array in the sheet. This data can be exposed to Excel VBA as well.

OpenAPISubscribe - Used for getting a streamed response ex. realtime updating net positions

OpenApiPost - Used for sending data such as trades through OpenAPI

OpenAPIUpdate - Used for updating data such as orders

OpenAPIDelete - Used for deleting data such as orders

OPENAPI GENERIC FUNCTIONS

OpenAPI for Excel allows the user to interact with all functions (endpoints) in OpenAPI.

All endpoints that have been made available by Saxo will be callable directly from Excel using the following generic functions.

OPENAPIGET / OPENAPIGETASYNC

Description: Get Static data from an OpenAPI Endpoint either synchronously or asynchronously.

Parameters: 

Uri (string) - The path of the endpoint e.g "port/v1/netpositions/ME"

ParameterList (string) - The columns to return as they are written in OpenAPI. In case of nested objects such NetPositionView.AverageOpenPrice as the user will need to use "." to get the nested object (in this case AverageOpenPrice). The syntax is case sensitive.

As an example if we want to get NetpositionId, Average Open Price and Amount for the logged in client, then OpenAPI is called as follows:

=OpenAPIGet("port/v1/netpositions/ME","NetPositionId,NetPositionView.AverageOpenPrice,NetPositionBase.Amount")

Where “port/v1/netpositions/ME” is the name of the endpoint

and

“NetPositionId,NetPositionView.AverageOpenPrice,NetPositionBase.Amount” are the columns that are to be returned.

Note that AvererageOpenPrice is nested in the NetPositionView Object and Amount is Nested in the NetPositionBase object hence we use the ”.” syntax.

OPENAPIGETAUTORESIZE 

OpenAPIGetAutoResize has the same syntax as OpenAPIget.

However, this function automatically sizes the returned data to the size of the source dataset.

The function furthermore accommodates headers for the returned data. This is controlled by an optional Boolean

(TRUE/FALSE) value.
The syntax is as follows:
=OpenApiGetAutoResize("/openapi/ref/v1/currencies","CurrencyCode,Name,Decimals",TRUE) For returning a sized list(Array) of all currencies.

OPENAPISUBSCRIBE

OpenApiSubscribe will return a line with headers as well as a dynamic array below which contains the data. The data is controlled by the cell into which the formula was initially input. Deleting or changing this cell will alter or delete the entire dataset and unsubscribe from the data. If OpenApiSubscribe is input in to formula "A1" then column headers will appear in cell A1, B1, C1 etc. and return data will be shown beginning in row 2 and will dynamically resize depending on the amount of data. Make sure that no important data is residing immediately below the subscribe function as it might be deleted. A polling rate of once per second has been set for all subscriptions in OpenAPI for Excel.

Description: Get Streaming data from an OpenAPI Endpoint

Parameters: 

Uri (string) - The path of the endpoint e.g "port/v1/netpositions/subscriptions/active"

Arguments (string) - The arguments used to populate the connection e.g "Clientkey:N02PuZi3szhnEbc4qTXk1g==" these can be found in the developer portal. ClientKey and AccountKey is needed for most endpoints. These can be retrieved by using the functions OpenApiGetClientKey and OpenApiGetAccountKey.

FieldGroups (string) - The fieldgroups to return e.g NetpositionBase, these groups are used to specify the type of data you are querying for. This helps limit the amount of data that has to be downloaded and thereby reduces latency for large datasets. The fieldgroups are input as a comma separated string.

ParameterList (string) - The columns to return as they are written in OpenAPI. In case of nested object we use "." . the syntax is case sensitive.

As an example if we want to get NetpositionId, AverageopenPrice and Amount from the below return data then OpenAPI is called as follows:

=OpenApiSubscribe("port/v1/netpositions/subscriptions/active","ClientKey:9DxQ|LlQHC5QoU2lvfghJQ
==","NetPositionBase,NetPositionView,DisplayAndFormat","NetPositionId,NetPositionView.AverageOpenPrice,
NetPositionBase.Amount")

OPENAPIPOST

OpenAPI post is used to commit data through OpenAPI.

This can be used to commit data that is not supported with a specific function. Even though trades can be placed via OpenAPIPost it is recommended to use the designated trading functions described later in this document.

Description: Commit data to OpenAPI.

Parameters: 
Uri (string) - The path of the endpoint e.g "trade/v1/orders"
Body (string) - The arguments used to send through OpenAPI

A range of dedicated trading functions have been implemented in the plugin to make committing trades easier. All of the below functions can be replicated using OpenApiPost.

OPENAPIPLACEORDER

Description: This function is used to place a new order. It does not work for all asset types.

Parameters:

AccountKey (string) - Account key for the account from which order is to be placed.

Instrument (string) - Symbol of instrument for which order is to be placed, for e.g. "EURUSD", "EURJPY" etc.

AssetType (string) - Asset type of the instrument, for e.g. "FxSpot" etc.

Amount (Decimal) - Size of the order i.e. quantity of the instrument to be ordered.

Buy/Sell (string) - The direction of the order; buy or sell.

Duration (string) - A string describing different order duration types. Note that not all order types are possible for all asset types. Possible valid values are given in the reference documentation on the OpenAPI developer portal.

OrderType (string) - Specifies they order type, possible valid types are given in the reference documentation on the OpenAPI developer portal.

OrderPrice (Decimal) - Price at which order is to be placed. Optional for market orders.

RelatedLimitPrice (Decimal) - Price at which Limit order is to be placed (Optional)

RelatedStopPrice (Decimal) - Price at which Stop order is to be placed (Optional)

RelatedStopType (string) - specifies supported Stop order types (Optional), possible valid types are given in the reference documentation of Place Order endpoint.

Return Value: Order Id if order is placed successfully. If order could not be placed successfully, the respective error message is displayed to the user.

OPENAPIPLACEORDERASYNC

Description: Description: This function is used to place an order asynchronously and return immediately. Once the order is placed (successfully or unsuccessfully) the result is again sent to excel. If multiple asynchronous orders are placed in short sequence they might not be committed in the order they were input.

Placing Asynchronous orders does not work for all asset Types and should not be used in Excel VBA.
The inputs for this function are the same as for OpenAPIPlaceOrder.

OPENAPIADDRELATEDORDER

Description: This function is used to place a new related 'slave' order to an existing 'master' order.
Parameters:
AccountKey (string) - Account key for the account from which order is to be placed.
Amount (Decimal) - Size of the order i.e. quantity of the instrument to be ordered.
Buy/Sell (string) - The direction of the order; buy or sell.
Duration (string) - A string describing different order duration types. Note that not all order types are possible for all asset types. Possible valid values are given in the reference documentation of Add Related Orders endpoint.
OrderPrice (Decimal) - Price at which order is to be placed.
OrderType (string) - Specifies supported order types. Possible valid types are given in the reference documentation of Add Related Orders endpoint.
MainOrderId (string) - Order id of the master order.
OrderRelation (string) - Relation with parent/related order. Possible valid types are given in the reference documentation of Add Related Orders endpoint.
Return Value: Order id if order successfully placed. On unsuccessful order, returns the related error message.

OPENAPICANCELORDER

Description: This function is use to cancel any existing open order.

Parameters: 
OrderId (string) - Order id of the order to be cancelled.
AccountKey (string) - Account key for the account from which order was placed.
Return Value: Status of cancellation, success or error message.

OPENAPIEDITORDER

Description: This function is used to edit an existing open order.

Parameters:
AccountKey (string) - Account key for the account from which order is placed.

Amount (Decimal) - Size of the order i.e. quantity of the instrument to be ordered.

Duration (string) - A string describing different order duration types. Note that not all order types are possible for all asset types. Possible Order duration are given in the reference documentation of Update Order endpoint.

OrderPrice (Decimal) - Price at which the order is to be placed.

OrderType (string) - Specifies supported order types placeable in the orders ticket.Possible Order types are given in the reference documentation of Update Order endpoint.

OrderId (string) - Id of the order to be edited.

Return Value: Status of order edit. Shows success / error message.

UTILITY FUNCTIONS

Apart from the above functions a small number of helper functions have been implemented.

OpenApiGetAccountKey

Description: Get Account key using account id as an input parameter.
Parameters: 

AccountId (string, optional) - Id of the account for which accountkey is to be fetched. This is an optional parameter.

Return Value: Account key for account whose id is given as input parameter. If no account id is given as input, then Account key of the default account is returned.

OPENAPIGETCLIENTKEY

Description: Gets the Clientkey for a specific Client or if input left blank for the logged in user.

Parameters: 

ClientID(string, optional) - Id of the Client for which Clienttkey is to be fetched. This is an optional parameter.

Return Value: Clientkey for account whose id is given as input parameter. If no Client id is given as input, then Client key of the default Client is returned.

OPENAPIGETACCOUNTLIST

Description: Display a list of all accounts, Account Ids and Account Keys below the client, in a table.