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

The Formula Builder

The flexibility of OpenAPI for Excel comes at a cost, as the syntax can be tricky and rapidly becomes unreadable for complex statements with dependencies etc. In general, most formulas require at least the endpoint to be specified, and, depending on the endpoint, a number of subsequent arguments containing parameters and fields. The easiest way to construct a formula from scratch is by using the Formula Builder, a simple graphical interface which adapts automatically depending on which functionality is requested. As each endpoint has its own requirements and options for Request Parameters and Fields, a lot of back-and-forth is required between the OpenAPI Reference Documentation and Excel when typing out formulas straight into the formula box. The Formula Builder overcomes this issue and loads a predefined list of options for each method/endpoint combination, which makes this process exponentially easier. The usual procedure for building a new formula consists of three steps:

  1. Select required endpoint.
  2. Specify request parameters.
  3. Select fields to be returned.

For an overview of all the available OpenAPI endpoints and the requirements for each endpoint, see the OpenAPI Reference Documentation. For more information on how the OpenAPI formulas translate the required inputs for requests, please review the OpenAPI Functions section.

Example

Suppose we are looking to load the Symbol, Description, and IsTradable information for 3 UICs (unique instrument identifiers), 21, 31 and 41. Using the Formula Builder, this information can be retrieved by following these steps:

  1. Select the /instruments/details endpoint under the ReferenceData service group.
  2. Enable Include Headers to automatically add a header row to the output array (this should be enabled by default).
  3. Enter the three UICs in the appropriate parameter field.
  4. Specify which information should be returned (in this case, the Uic, Symbol, Description and IsTradable fields).
  5. Click Insert Formula.

Which delivers the following data into the target cell on the spreadsheet:

The Excel array returned above can subsequently be fed into other formulas or used in VBA. See OpenAPI Functions for more details on the format of the returned data. The formula that is returned by the Formula Builder looks as follows:

=OpenApiGetAutoResize("/openapi/ref/v1/instruments/details/?Uics=21,31,41&
FieldGroups="Uic,Symbol,Description,IsTradable",TRUE)

This formula is automatically constructed based on the input in the Formula Builder. Note how the structure is very similar to a HTTP request: the path is combined with the request parameters, and from the returned output the Uic, Symbol, Description, and IsTradable fields are converted to columns in Excel. The formula includes the 'AutoResize' suffix in order to expand the output range to the shape of the data array that is returned. See the next section on OpenAPI Excel functions for more information on these formulas and their usage in VBA.