3 - Analyzing FX exposure

In this third tutorial, we will take a closer look at the /port/v1/positions/ endpoint to analyse currency exposure across instrument types for all accounts sitting under the logged in user. The questions we will answer are:

• Which currencies are open positions on each account exposed to, per AssetType?
• What is the total exposure of the portfolio in the client's base currency?
• What is the total exposure to each currency?

The /port/v1/positions/ endpoint returns positions of the current user (for all accounts) and supports the following fields, which will be pulled in for this analysis:

• Exposure, the amount of exposure in the currency that the instrument is denominated in.
• ExposureCurrency, which returns the currency code of the instrument.
• ExposureInBaseCurrency, which translates the instrument's specific currency exposure to the base currency of the client.

The only required parameter for the positions endpoint is the client key, which is obtained in cell B3 using the built-in "=OpenApiGetClientKey()" formula. The data returned by the endpoint is stored on a separate sheet labelled Data. and is subsequently loaded into the pivot table on the Exposures tab.

Basic framework

The objective is to create a dynamic pivot table, which automatically updates to the latest data from the OpenAPI with a single click. The data can contain any number of accounts, currencies, AssetTypes and positions. The user can adjust the fields in the pivot table and build their own reports accordingly. This is a helpful tool to identify potential foreign exchange risk in a portfolio and trade FX accordingly to cover exposure.

VBA solution

The functionality for this tool is broken down into two steps:

1. Obtain exposure data on all positions through the /port/v1/positions/ endpoint. This is similar to the data procedure implemented in the second tutorial.
2. Refresh the pivot table to ingest the new data, keeping the structure and breakdowns intact. Users can adjust the pivot table's design afterwards without breaking the update functionality.

Because the endpoint returns all positions, including closed ones (which by definition do not have exposure), a filter is added to the pivot table to clean this up.

The below VBA snippet takes care of these tasks. Key points in this script are:

• The fields variable is defined separately and sent into the OpenApiGet function. Organizing the parameters of this function this way is helpful for readability and making adjustments at a later stage.
• The data is returned without overriding the headers on the Data tab.
• When the pivot table is refreshed, the headers are included in the Range.

Sub updateExposures()

    Dim query As String
    Dim fields As String
    Dim data As Variant
    Dim DataArea As Range
    Dim dmax As Integer

    'refresh all formulas on spreadsheet
    Application.Run ("OpenApiRefreshFormula")

    'set clientkey variable
    ckey = Application.Run("OpenApiGetClientKey")

    'build query and define fields to be returned from OpenAPI
    query = "/openapi/port/v1/positions/?FieldGroups=positionbase," & _
    "positionview&ClientKey=" & ckey

    fields = "PositionBase.AccountId,PositionBase.Status,PositionBase.AssetType," & _
    "PositionView.Exposure,PositionView.ExposureCurrency,PositionView.ExposureInBaseCurrency"

    'perform API call
    data = Application.Run("OpenApiGet", query, fields)
    
    'capture if error is returned by API (which is string type)
    If TypeName(data) <> "Variant()" Then GoTo unexperror
    
    dmax = UBound(data)

    Range("Data!a2:f10000").ClearContents 'clear range of cells that contain the data
    Range("Data!a2:f" & 1 + dmax).Value = data 'load data onto spreadsheet

    'capture errors where no open (net) posititons exist
    If Application.Sum(Range("Data!F2:F" & 1 + dmax)) = 0 Then GoTo nodataerror

    Set DataArea = Range("Data!a1:f" & 1 + dmax) 'define range of data
    
    'update the pivot table on the main sheet by refreshing its cash
    ActiveSheet.PivotTables("ExposurePivot").ChangePivotCache ActiveWorkbook. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:=DataArea)
    Exit Sub
    
nodataerror:
    MsgBox "An error occured." & vbNewLine & _
    "It looks like you have no open (net) positions on your accounts." & vbNewLine & _
    "The exposure pivot table has not been updated."
    Exit Sub

unexperror:     'capture unexpected error
    MsgBox "An unexpected error occurred." & vbNewLine & _
    "This could be caused by data access rights." & vbNewLine & _
    "Returned error: " & data
    Exit Sub

End Sub

With the above VBA implemented, we now have a single button that updates the exposure data and refreshes the pivot table. Using the above example as a starting point, we can rearrange the pivot table to identify a net GBP position of almost 6 million:

Which can be reduced significantly by entering in an offsetting GBP short position: