Views:

In this tutorial we'll see how to use LUSID to perform the following task:

 

“As a portfolio manager with a mix of equities and bonds in different regions, I want to load market data from multiple providers and track the performance of my fund over time as prices and rates change.”

 

We'll see how to:

  • Purchase assets in multiple currencies while maintaining the cost basis of the portfolio in a single currency.
  • Load equity and bond prices and FX rates from different financial data vendors into the LUSID quote store.
  • Create a recipe that retrieves this market data, specifies a pricing model, and handles missing quotes.
  • Decide which of the many LUSID valuation metrics are useful to include in our report.
  • Value instrument positions individually in the portfolio each day, using FX rates to normalise foreign currency positions to the portfolio currency.
  • Sum daily instrument positions to calculate a valuation for the portfolio as a whole each day, and calculate PnL (ie. performance) over a week.

You can examine and run the complete code sample for this exercise from this Jupyter Notebook, providing you have suitable access control permissions. This can most easily be achieved by assigning your LUSID user the built-in lusid-administrator role, which should already be the case if you are the domain owner. If you are told you do not have a license to perform a particular operation, please contact support.

   

Examining the source files

Let's assume we have a set of transactions to load into our portfolio on Monday 7 March:

We have closing market prices for equities from EDI (keyed by FIGI identifiers) for each of the five days between Monday 7 March and Friday 11 March:

We have closing market prices for bonds from Refinitiv DataScope (keyed by ISIN identifiers) for the same 5 days:

And we have closing USD/GBP spot rates for most of those days:

We can see that:

  • The transaction source file injects initial GBP and USD cash amounts into the portfolio using the built-in FundsIn transaction type.
  • Equities and bonds are purchased in both GBP and USD using the built-in Buy transaction type, so we can expect the cost of each transaction to be automatically deducted from the appropriate cash balance.
  • The cost of equities and cash is calculated as the number of units bought multiplied by the purchase price in the transaction currency. The cost of bonds is calculated as a percentage of par, which is purchase price divided by par multiplied by the number of units bought. 
  • There's a USD/GBP spot rate missing, on Thursday 10 March.

Note that bond instruments must be mastered in a particular way in order for LUSID to value them correctly:

  • Bond instrument definitions should be unitised (the principal set to 1) and quantity purchased specified in transactions.
  • Coupon rates are defined as numbers rather than percentages, so a bond paying:
    • 10% should have a couponRate  of 0.1
    • 2.5% should have a couponRate of 0.025
    • 0.375% should have a couponRate of 0.00375

Creating a suitable portfolio

The first task is to call the LUSID CreatePortfolio API, specifying a suitable scope, code and name, and setting the base currency of the portfolio to GBP. For a general introduction to portfolios in LUSID, read our portfolio documentation.

Step 3 of the accompanying Jupyter Notebook demonstrates how to do this using the LUSID Python SDK. You can execute the Notebook in Jupyterhub for your LUSID domain and then sign in to the web app to see the portfolio on the Portfolios dashboard, available from the left-hand Data Management menu:

Loading transactions into the portfolio and establishing positions

We can call the LUSID UpsertTransactions API to load the transactions in our source file into the portfolio.

For each transaction, we record the number of units bought, the purchase price, and the total consideration (cost) in the transaction currency.

Note we purchase a US equity and bond in USD, but the base currency of the portfolio is GBP. To maintain the cost basis of the portfolio, we need to record the USD/GBP spot rate for USD transactions, which we can do by adding the built-in TradeToPortfolioRate system property to each. LUSID then automatically calculates the cost in GBP.

Step 4 of the accompanying Jupyter Notebook demonstrates how to do this using the LUSID Python SDK. You can execute the Notebook in Jupyterhub for your LUSID domain and then sign in to the web app to observe LUSID's holdings calculations on the Dashboard > Holdings. Note that:

  • The Cost column shows the cost of each instrument position in the Holding Currency.
  • The Cost (Portfolio Currency) column shows the cost in GBP for the USD transactions calculated using the trade to portfolio rate.

Loading market data into the LUSID quote store

LUSID has a quote store designed to hold simple market data such as equity and bond prices and FX rates. Note that for more complex market data, other stores are available.

We can call the LUSID UpsertQuotes API to load up to 2000 quotes at a time, specifying for each:

  • A scope, or namespace, into which to load the data.
  • A provider representing a financial data vendor. At the time of writing, valid providers are Bloomberg, DataScope, SIX, FactSet, TraderMade, Edi, Lusid and Client; if your provider isn't listed, you can represent it using Lusid or Client.
  • For equity and bond prices, an instrument identifier such as Figi or Isin with a value to link the quote to an instrument mastered in LUSID.
  • For FX rates, an identifier type of CurrencyPair and a value consisting of the buy and sell currencies separated by a forward slash, for example USD/GBP.
  • The type, for example Price or Rate.
  • A field, for example mid or bid.
  • The actual price or rate amount.

Note that for each bond we also need to record the scale factor (reflecting par) to enable LUSID to value the bond properly.

Steps 5.1 and 5.2 of the accompanying Jupyter Notebook demonstrate how to load prices and rates using the LUSID Python SDK. You can execute the Notebook in Jupyterhub for your LUSID domain and then sign in to the web app to access the Data Management > Quotes dashboard. Use the Scope dropdown (highlighted top left) and the Effective At column (highlighted top right) to choose one of the days between Monday 7 and Friday 11 March to see the quotes loaded for that day:

Creating a valuation recipe

To value a portfolio we must first create a recipe that tells LUSID how to retrieve market data from the quote store, and which pricing model and methodology to use.

Retrieving market data

For each of the three types of quote we need to retrieve (equity prices, bond prices and FX rates), we must specify a MarketDataKeyRules section in the recipe that tell LUSID:

  • The key to use to look up market data. This is a dot-separated string whose precise syntax depends on the type of data; see these examples. In our case, the keys are:
    • A key of Quote.Figi.* to look up equity prices loaded into the quote store using FIGI identifiers.
    • A key of Quote.Isin.* to look up bond prices loaded using ISIN identifiers.
    • A key of Fx.CurrencyPair.* to look up FX rates.

      Note that if our bonds had been loaded into the quote store with FIGI identifiers as well, then equities and bonds could have shared a MarketDataKeyRules section with a key of Quote.Figi.*. As things stand, in fact, they could share a MarketDataKeyRules section with a key of Quote.*.* to look up quotes using any identifier.
  • The scope into which market data is loaded in the quote store. In our example, this is FBNUniversity (see the Scope dropdown in the picture above).
  • The supplier, which must match the provider in the quote store. In our example, this is Edi for equity prices, DataScope for bond prices and Lusid for FX rates (see the Provider column in the picture above).
  • Whether the quote is a Price or a Rate (see the Quote Type column in the picture above).
  • The field, for example mid or bid (this is case sensitive and must match the equivalent quote field exactly; see the Field column in the picture above).
  • An interval determining how long the quote is valid for (if more than 24 hours). This must be a dot-separated string nominating a start and end date, for example 5D.0D to look back 5 days from today (0 days ago); other mneumonics are W(eek), M(onth) and Y(ear). By setting this to 2D.0D (two days from today), we can accommodate our missing FX rate on Thursday 10 March by causing LUSID to automatically fall back to using the rate from Wednesday 9 March.

Changing the default pricing model

For each of the instrument types we want to value (equities and bonds), we need to tell LUSID which built-in pricing model to use. LUSID supports a number of different pricing models, each suited to a particular asset class and set of circumstances. Note that more complex scenarios typically require complex market data such as discount curves, interest curves or volatility surfaces.

The default pricing model model for equities is SimpleStatic, which calculates value for a particular date simply by multipling the number of units held on that day by the quote price. However, the default pricing model for bonds is Discounting, which requires a discount curve. For the purpose of this exercise, we'll create a PricingContext section in the recipe that tells LUSID to use SimpleStatic for bonds as well.

Step 6 of the accompanying Jupyter Notebook demonstrates how to create a suitable recipe using the LUSID Python SDK. You can execute the Notebook in Jupyterhub for your LUSID domain and then sign in to the web app to examine the recipe in JSON form on the Data Management > Recipes dashboard (alternatively, the whole recipe, with default options, is printed in the Notebook):

Deciding which metrics to report

LUSID's valuation engine is complex and can report many metrics.

We want to calculate:

  1. The PV of each instrument position in our portfolio in the transaction currency each day.
  2. The PV of each USD instrument position normalised to GBP, the portfolio base currency.
  3. The PV of the portfolio as a whole each day, normalised to GBP.
  4. The PnL for the portfolio as a whole each day, normalised to GBP.

We can call the LUSID GetQueryableKeys API to list all the metrics LUSID is able to report. Each metric has an addressable key in the same way as a property, and we can either request the metric Value itself, or ask LUSID to perform a Sum or Proportion operation on a range of metric values.

To create our valuation report, we'll request the following metrics:

  • Holding/default/Cost to calculate cost in the transaction currency.
  • Valuation/PV to calculate PV in the transaction currency.
  • Valuation/PnL/Unrealised to calculate PnL in the transaction currency (PV minus cost).
  • Holding/Cost/Pfolio to calculate cost in GBP.
  • Valuation/PvInPortfolioCcy to calculate PV in GBP.
  • Valuation/PnL/Unrealised/PfolioCcy to calculate PnL in GBP. We can further split this by using Valuation/PnL/UnrealisedMarket/PfolioCcy and Valuation/PnL/UnrealisedFX/PfolioCcy to calculate how much of the PnL is due to changes to market prices, and how much is due to fluctuating FX rates respectively.

In addition, we'll request other metrics to help us create a meaningful report, such as Instrument/default/Name and Analytic/default/ValuationDate.

We're going to value the portfolio in two ways: each instrument per day, and the whole portfolio each day. Step 7 of the accompanying Jupyter Notebook demonstrates how to assemble metrics appropriately using the LUSID Python SDK:

  • To value each instrument per day, we'll request each metric Value (see Step 7.1 in the Notebook).
  • To value the whole portfolio each day, we'll request LUSID to (see Step 7.2 in the Notebook):
    • Group by Analytic/default/ValuationDate in order to create one entry for the portfolio each day rather than the default of one entry per instrument each day.
    • Sum certain instrument metrics to calculate totals for the portfolio each day, notably Holding/Cost/Pfolio, Valuation/PvInPortfolioCcy and Valuation/PnL/Unrealised/PfolioCcy. Note it doesn't make sense to sum string metrics such as Instrument/default/Name, nor numeric metrics such as Valuation/PV where values might be in different currencies.

Valuing the portfolio

We can now call the LUSID GetValuation API, passing in an appropriate valuation request.

Valuing each instrument per day

Step 7.1 of the accompanying Jupyter Notebook demonstrates how to pass in a valuation request using the LUSID Python SDK that:

  • Identifies the portfolio to value and the recipe to use.
  • Specifies the metrics to report and requests LUSID return a  Value for each.
  • Declares a schedule encompassing our dates of Monday 7 March to Friday 11 March inclusive, sorted in date order.

You can execute the Notebook in Jupyterhub for your LUSID domain and then sign in to the web app to examine the Data Management > Valuation dashboard. Choose the portfolio and recipe (highlighted top left) and a particular day using the Effective datetime picker (highlighted top right). 

For example, if we look at Thursday 10 March and examine the Fx column we can see that LUSID has automatically fallen back to using the USD/GBP rate for Wednesday 9 March (which was 0.71):

Valuing the portfolio as a whole each day, and tracking performance over the week

Step 7.2 of the accompanying Jupyter Notebook demonstrates how to pass in a valuation request that:

  • Identifies the portfolio to value and the recipe to use.
  • Specifies the metrics to report and requests LUSID Sum a range of metric values.
  • Groups by valuation date to request LUSID sum instrument positions and calculate a total for the portfolio each day.
  • Declares a schedule encompassing our dates of Monday 7 March to Friday 11 March inclusive, sorted in date order.

You can execute the Notebook in Jupyterhub for your LUSID domain, but unfortunately it's not possible to see a valuation for a range of dates in the LUSID web app at the current time. This picture is taken from the Notebook output:

Summary

In this tutorial, we've seen how to:

  • Set up bond instrument definitions and load bond quotes in a way that enables LUSID to value them correctly.
  • Maintain the cost basis in the portfolio currency by normalising foreign currency transactions.
  • Match a valuation recipe to quote definitions in order that the former can retrieve the latter at valuation time.
  • Return common BI metrics such as PV and PnL on a per instrument and per portfolio basis.