OpenAPI for Excel
Open API Learn Tutorial Reference Docs Explorer Apps Updates Get 24 Hour Token Sign in
Other Products FIX Trading API Client Management Systems API End-of-Day File Transfers Trade Event Notification System OpenAPI for Excel

10 - Market data verification tool

Download the Excel snippet here

This tutorial present a quick and easy way to verify market data in 3rd party apps (like Excel). It uses only limited VBA, and is intended to be used for clients that are looking to work with live market data in their Excel spreadsheets.

In order to use this tool, the following is required:

  1. A live account with Saxo Bank (market data is not available in SIM as of now - this tool will not work).
  2. Market data should be enabled for the user, see these quick steps to activate it.
  3. (Optional) A subscription to live price data from any exchange for the instrument you are looking to work with (we take equity listed on NASDAQ as an example here).

As we are not going to do any trading, we can log in with LIVE read-only, which will still allow us to pull out undelayed market data.

The below screenshot shows functionality of this tool. For the selected instrument, it pulls out the symbol, name, and listed exchange. Additionally, it sets up a price subscription to trade/v1/infoprices and returns some key fields that indicate whether the provided data is real-time.

Note: because of licencing restrictions for retail clients, LIVE market data can only flow to a single endpoint/app, which means that activating the primary session (which enables live market data) will log you out of other platforms such as GO and PRO.

Once you log into Excel, your session is by default secondary ("OrdersOnly"), which means you will receive delayed market data for all instruments. To confirm this is working, log in with your LIVE account and refresh the sheet. If market data is correctly enabled (see step 2 above), you should be able to see a delayed price for Cisco Systems Inc. at the bottom. Note how the delay is 15 minutes and the price type is "OldIndicative".

Next, select an instrument on an exchange to which you have subscribed for live data. In this case, we are using NASDAQ, which is part of the BATS feed, but you could of course use any listed product/exchange combination. Without changing the session ("Trade Level"), the prices will remain delayed.

Next, change the session to primary ("FullTradingAndChat"). After a short moment, the price subscription at the bottom updates and shows that the delay is now 0 minutes, and the price type is "Indicative". In addition, the "LastUpdated" column will show the time stamp of each price message as they come in, which should be close to the current time (in an active market).

VBA implementation

Only minimal VBA is required for this tool to work, as we only automate trade level settings. The below code takes care of this by patching the sessions endpoint. It also refreshes the formula in cell A4, which updates the Trade Level GET request that is placed here.

Sub TradeLevelPrimary()

    TradeLevel = "{'TradeLevel':'FullTradingAndChat'}"
    Result = Application.Run("OpenApiPatch", "root/v1/sessions/capabilities", TradeLevel)
    Range("A4").Formula = Range("A4").Formula

End Sub

Sub TradeLevelSecondary()

    TradeLevel = "{'TradeLevel':'OrdersOnly'}"
    Result = Application.Run("OpenApiPatch", "root/v1/sessions/capabilities", TradeLevel)
    Range("A4").Formula = Range("A4").Formula

End Sub