OpenAPI for Excel

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.

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 ouput 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