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.
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.
The functionality for this tool is broken down into two steps:
- Obtain exposure data on all positions through the /port/v1/positions/ endpoint. This is similar to the data procedure implemented in the second tutorial.
- 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.
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: