The OpenAPI for Excel add-in's User-Defined Functions (UDFs) return either a String or Variant() depending on the function and whether the operation resulted in an error. Some general guidelines:
- A GET request (OpenAPIGet) will normally return data transformed into a Variant() data type (see below). Errors return a string.
- All other requests (POST, PUT, PATCH, DELETE) return a string if any information is returned. An order sent via a POST request to /trade/v1/orders will for instance return a string, which either contains the OrderId (if the order was successfully placed), or an error description.
When working with VBA, this behavior should be taken into account. For instance, if your VBA declares Option Explicit (which is highly recommended and implemented in all tutorial series downloads), make sure to always set the data type for GET requests to Variant, which can take both the returned data and an error, because it will change to a String data type when the operation fails. The below example illustrates this behavior. In order to check if a GET request succeeded, best practice is to check its data type using the TypeName function.
The Debug.Print() statements print the error to the immediate window. Alternatively, if the operation was successful, the returned data type will be a Variant().
For all other HTTP methods (POST, PUT, PATCH, DELETE), the operation is slightly different, as the function will only return a String type (both in cases of errors and when the operation was successful). To find errors, parse out the string. An example of this can be found in Tutorial 5, the automated FX trader. When an order is sent, the OpenAPI confirms successful placement by sending back the OrderId. If this OrderId is not present in the returned String type, an error has occurred and the auto trader is stopped immediately:
Handling GET requests
Data fields returned through a GET request are encoded in the response body (in JSON format) and take the form of a preformatted Variant() array data type with two dimensions in VBA. OpenAPIGetAutoResize() will automatically adjust the output array on a worksheet depending on the amount of columns and rows returned by the API (do not use this in VBA).
The below example showcases how data is returned in worksheet cells through AutoResize, and what the corresponding request in VBA looks like. Three fields are pulled from the /netpositions/ endpoint: the position's ID, exposure in base currency and the description of the position:
Performing a similar request in VBA:
Which prints the following output to the debug console:
Accessing nested list structures
Excel VBA is mainly oriented around 2D data structures (tables and sheets). which makes it slightly complicated to pull out information from nested lists in JSON, which is the data model returned by the OpenAPI. Multiple endpoints return data in this format, such as the account history example below. In order to access nested lists (such as the date range in the below example), two square brackets "" are added to the field names:
Which results in the below 4 days of data printed to the immediate window: