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

Understanding OpenAPI Fields

OpenAPIGet functions require instructions on which FieldGroups and Fields to returned in Excel/VBA, the later of which is encoded in the "Fields" argument:

This list is a simple case-sensitive, comma-separated string with full stops identifying nested sub-elements. The FieldGroups query string parameter controls which data structures are returned in the response body. For instance, suppose we are interested in pulling out the NetPositionView FieldGroup for the current client's account, and from this FieldGroup we select the ExposureInBaseCurrency field, alongside the default NetPositionId field. Using AutoResize functionality, which parses the returned data automatically into a correctly-sized Excel array, the corresponding Excel formula is structured as follows:

=OpenApiGetAutoResize("/openapi/port/v1/netpositions/me/?FieldGroups=netpositionview",
"NetPositionId,NetPositionView.ExposureInBaseCurrency",TRUE)

Which returns:

Fields under FieldGroups that are not included in the query string parameters will return blanks. Suppose the field DisplayAndFormat.Description is added to the Fields argument, without calling the corresponding FieldGroup:

=OpenApiGetAutoResize("/openapi/port/v1/netpositions/me/?FieldGroups=netpositionview",
"NetPositionId,NetPositionView.ExposureInBaseCurrency,DisplayAndFormat.Description",TRUE)

Which fails to pull the required data because the Description field is not found in any of the returned data.

Adding the DisplayAndFormat FieldGroup fixes this issue:

=OpenApiGetAutoResize("/openapi/port/v1/netpositions/me/?FieldGroups=netpositionview,displayandformat",
"NetPositionId,NetPositionView.ExposureInBaseCurrency,
DisplayAndFormat.Description",TRUE)

The relationship between FieldGroups and their corresponding Fields is important to take into consideration when querying data through the API. To enable lightweight and quick responses, the API does not by default return all FieldGroups (this would be a lot of unnecessary data for each request). Instead, the API sends only the required data as specified by the FieldGroups query string parameter. The Formula Builder does this automatically.