In this first tutorial we will explore how to build basic search functionality in Excel. This is a useful tool to have as we explore more challenging topics because the OpenAPI does not take instrument names or symbols as identifiers. Instead, instruments are identified by a unique combination of UIC (Universal Instrument Code) and AssetType. Pulling this information for the specific instrument you are interested in is key to continue into other operations such as sending orders, building charts, creating reports, etc.
No VBA is required for this functionality as we are using a single API call in cell A11 to return a predefined number of results from the Reference Data service group.
The documentation for the /ref/v1/instruments/ endpoint can be found here.
Dynamically updating OpenAPI formulas with cell references
In order to update the the search results every time the search keyword is changed, we use a formula that references the input fields. An API call will automatically be executed and update the search results every time the reference cells are updated. The key to this spreadsheet is the formula in cell A11, which is displayed below. It takes the keyword from cell B3, the AssetType variable from B4, the amount of results to return from B5, and pulls the data fields specified in range A10-F10 using the TEXTJOIN function. The field names in row 10 can be adjusted to pull out different data, such as PrimaryListing (useful for Stock and CFD traders). This way, the search is entirely customizable.
=OpenAPIGetAutoResize("/openapi/ref/v1/instruments/?$top="&top&"&Keywords=
"&Search!B3&"&AssetTypes="&Search!B4&"",TEXTJOIN(",",FALSE,A10:F10))
This formula performs the following steps every time it is called:
- It resizes the output array automatically depending on the number of search results that are returned.
- Sends a request to /openapi/ref/v1/instruments/, returning only a limited number of results (top=), for a specific keyword (Keywords=) and optional asset types (AssetTypes=).
- From the returned result, output the selected fields A10-F10, which contain the field names for each of the properties that we want to show. These are case-sensitive.
- Symbol
- AssetType
- Identifier
- Description
- CurrencyCode
- PrimaryListing
The combination of Identifier (Uic), AssetType and PrimaryListing (for stocks) can be used to identify the instrument of interest. In the example above, the search keyword "Apple" returns five results, the first of which is the primary listed stock on the New York Stock Exchange.
Limiting input values to prevent errors
Because the OpenAPI sets some specific requirements for the value of each of the query string parameters, it is advised automatically to restrict the input fields to a limited set of values. This can be done using the Data Validation tool and a predefined list of AssetType and search result values, which are located on the config tab in the sample sheet. This functionality will be used throughout the tutorial series to prevent erroneous input and is advised for all applications in Excel.
The above setting creates a handy drop-down for both the AssetType and the max result parameters:
Using conditional formatting to improve user experience
When the user types a keyword that is not found on the endpoint, the formula used in cell A11 will return the error "No Content Found". Because this error is delivered as a string instead tabular data, the output range will not be resized. This means that every field that was active before will show the same error, which is not exactly user friendly. An easy workaround is to set two conditional formatting rules, which blank out all cells that contain error messages, except for cell A11:
Which works nicely to inform the user whether the query did not return results, or the user is not authorized:
