Data Model

Data types

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.

Option Explicit

Sub InvalidGETRequest()
    
    Dim Query As String
    Dim Fields As String
    Dim Data As Variant 'declare data as Variant
    
    Query = "some bad query"
    Fields = "some invalid fields"
    Data = Application.Run("OpenAPIGet", Query, Fields) 'this request will fail
    
    Debug.Print (TypeName(Data)) 'data type has been changed to String
    
    If TypeName(Data) = "String" Then 'check if operation result in error (String)
        Debug.Print ("An error occurred: " & Data)
    End If
    
End Sub

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().

String
An error occurred: Not Found

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:

'if trade did not go through correctly, stop trader
If Not (trade Like "*OrderId*") Then
    StopTrader
    MsgBox "An error occurred when attempting your trade. The AutoTrader was stopped", , "Error!"
Else
    CurrentPosition = "Long"
    AddLog
End If

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:

Sub PrintTypeShapeData()

	Response = Application.Run("OpenApiGet", "/openapi/port/v1/netpositions/me/?" & _
	"FieldGroups=netpositionview,displayandformat", _
	"NetPositionId,NetPositionView.ExposureInBaseCurrency,DisplayAndFormat.Description")

	Debug.Print TypeName(Response)
	Debug.Print UBound(Response, 1) - LBound(Response, 1) + 1; "rows"
	Debug.Print UBound(Response, 2) - LBound(Response, 2) + 1; "columns"
	Debug.Print "Contents:"
	For Each Item In Response
    	Debug.Print Item
	Next Item

End Sub

Which prints the following output to the debug console:

Variant()
 2 rows
 3 columns
Contents:
GBPDKK__FxSpot
GBPUSD__FxSpot
-55361.25 
 1660.84 
British Pound/Danish Krone
British Pound/US Dollar

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:

Sub AccessNestedList()

    query = "/hist/v3/perf/your_client_key/?FieldGroups=BalancePerformance&FromDate=2019-01-01&ToDate=2019-01-07"
    fields = "BalancePerformance.AccountValueTimeSeries[].Date,BalancePerformance.AccountValueTimeSeries[].Value"
    Response = Application.Run("OpenApiGet", query, fields)

    Debug.Print TypeName(Response)

    If TypeName(Response) = "String" Then ' If the request failed, print the error
        Debug.Print (Response)
    End If

    Debug.Print UBound(Response, 1) - LBound(Response, 1) + 1; "rows"
    Debug.Print UBound(Response, 2) - LBound(Response, 2) + 1; "columns"
    Debug.Print "Contents:"
    For Each Item In Response
        Debug.Print Item
    Next Item

End Sub

Which results in the below 4 days of data printed to the immediate window:

Variant()
 4 rows
 2 columns
Contents:
2019-01-02
2019-01-03
2019-01-04
2019-01-07
390.48
405.44
384.24
688.51