Using what we learned in Tutorial 1, we can now start looking into slightly more complex interactions with the OpenAPI. In this tutorial, we will investigate how to pull historic FX pricing data from the OpenAPI and build a simple price graph. To facilitate this process and automate the data, checks, and updates to the graph, a bit of VBA is included with the spreadsheet.
Real-time pricing data is available for FX instruments across Saxo Bank's products, including OpenAPI for Excel. This tutorial restricts the AssetType to FX, as market data for other instruments has to be enabled separately.
Two endpoints will be explored in this tutorial:
- /ref/v1/instruments/details/, in order to obtain the symbol for any valid UIC/AssetType combination.
- /chart/v1/charts/, to pull historic pricing data.
This charting tool will pull the most recently available data from the OpenAPI and automatically update the chart accordingly. The /chart/v1/charts/ endpoint is limited to 1200 datapoints per request (if available) and requires the following parameters:
To add additional functionality, the following parameters will be passed in addition to the above:
- Count, to limit the graph to a certain number of datapoints.
- Mode, to instruct the API to return only the most recent values.
- Time, which indicates the starting point of the time series and will be automatically updated using the Now function VBA.
A simple API call is added to pull the symbol of the UIC/AssetType combination entered by the user, which sits in cell A5:
Instruments other than FX can be tested in this framework as well, although results may vary depending on whether market data is enabled on the user's account. Error handling in the VBA code below takes care of edge cases where the returned message from the API is not in the expected format. Make sure to add AssetType options through Data Validation when testing this on a broader range of instruments.
The below cells are named in this spreadsheet, which allows for easy referencing in VBA. See Formulas → Name Manager for more information.
Now that all the inputs are in place, with the correct names, the below VBA is assigned to the Update Chart button to automatically pull the most recent data. See the comments added to the code below to get a better understanding of the intended workflow. The key points in this script are:
- The query URL string and parameters are automatically constructed based on the provided inputs, combining the values of the provided fields with the relevant request parameters such as AssetType, Uic and Horizon.
- The value for &Time= is automatically set to "Now", which returns the current date and time in VBA. To prevent localization settings from sending incorrect dates to the API, this string is converted to yyyy/mm/dd hh:mm.
- The &Mode= parameter is set to "UpTo", instructing the OpenAPI to return data up until the date provided in &Time=.
- Errors are handled by checking data types, returned values, and the value from the above formula that pulls the symbol of the UIC/AssetType combination.
- The chart is pointed at the newly obtained data, taking into account that the size of the data can change depending on user settings and availability.
- The chart title is automatically updated using the value returned by the API call above (cell A5) and the instructions passed the /chart/v1/charts/ endpoint.