Populating the LUSID instrument master from different data sources

In this tutorial we'll introduce instruments by using LUSID to perform the following task:

“As a data manager, I want to populate the LUSID instrument master with a mix of Equities, Bonds, Futures and FxForwards. The data is sourced from two systems, each with its own taxonomy and market identifiers. I want to a see a consolidated view for each instrument in LUSID, but retain the original format and lineage of the data.”

We'll see how to:

• Use instrument identifiers to ensure records from different systems representing the same financial asset are merged into a single LUSID instrument.

• Model the different asset classes using instrument templates to create appropriate economic definitions.

• Load instruments into a custom instrument scope, to segregate them from currencies and other instruments in the default scope.

• Add properties to instruments to store extra information while maintaining the original format and lineage of the data.

• Use derived properties to normalise data from different systems with the same meaning, for reporting purposes or for consumption by downstream systems.

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 informed you do not have a license to perform a particular operation, contact support.

Examining the source files

Let's assume we have security information from two different systems, VendorA and VendorB.

Vendor A

Vendor B

We can see that:

• Each source file has a record for an Equity, a Bond, a Future and an FxForward.

• Equivalent records represent the same financial asset; that is, the Equity record from VendorA has the same FIGI as the Equity record from VendorB, and so on.

• Each source file has a column relating to domicile. For VendorA, this column is called country_issue; for VendorB, it is origin.

• These domicile columns have missing and non-uniform values; for example, VendorB contains both GB and Great Britain, while VendorA has no value for the Future.

• The bond has a coupon rate expressed as a percentage.

Mastering instruments in LUSID

The first task is to model the assets as instruments in LUSID. Each instrument must have:

• A name. This is for display purposes and need not be unique.

• At least one unique market identifier, such as a FIGI. An instrument can have more than one unique identifier, and it can have non-unique identifiers too. The more identifiers an instrument has, the more successful LUSID will be at automatically resolving transactions from different systems and calculating holdings (positions) correctly.

• An economic definition appropriate to the asset class.

Note that an instrument successfully mastered in LUSID is automatically assigned a LUID, a unique identifier that is guaranteed to be unique and never change. Subsequently, you can address an instrument by any of its unique identifiers, including the LUID.

Optionally we can master the instruments in a custom instrument scope, perhaps to set entitlement boundaries, or simply to segregate the data.

Using unique identifiers to merge records to the same instrument

For each instrument, we need to:

• Map the instrument_name column to the instrument display name.

• Map appropriate columns to unique market identifiers in LUSID. For example:

  • The figi column to the Figi unique identifier type (note LUSID identifier type names are case sensitive).

  • The internal_id column to the ClientInternal unique identifier.

  LUSID requires only one unique identifier in order to merge equivalent records to the same instrument. However, in our example, the FxForward does not have a FIGI, since it is not exchange-traded, and so we must also rely on the internal ID. If source files from different systems do not have data that can be mapped to at least one unique identifier, you will need to pre-process the data.

• Map the isin column from VendorA to the Isin non-unique identifier.

• Map the ticker column from VendorB to the Ticker non-unique identifier.

Step 2 of the accompanying Jupyter Notebook demonstrates how to do this using the LUSID Python SDK.

Modelling asset classes using instrument templates

LUSID has an instrument template for each asset class to help you create a valid economic definition.

Step 2 of the accompanying Jupyter Notebook demonstrates how to construct economic definitions using the LUSID Python SDK. Note that bond coupon rates must be expressed as a number rather than a percentage, so our bond paying 0.375% must be defined as 0.00375.

Upserting instruments to LUSID

Once identifiers are mapped and economic definitions in place, we can call the LUSID UpsertInstruments API for each file in turn.

For each record in the first file, an instrument is created. For each record in the second file, the existing instrument is updated. In our example, this means the display name of the instrument inherits the value from the second file. 

Step 2 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 results on the Instruments dashboard, available from the left-hand Data Management menu. Since we mastered instruments in a custom scope, we can specify that Scope (highlighted) to retrieve our segregated instruments:

Adding properties to record extra information

Each system provides extra information relating to domicile that we cannot include as part of the standard economic definition for an asset class. We want to store this data in LUSID without changing it in any way, so it remains meaningful to the originating system.

We need to:

• Create a property definition representing the country_issue column from VendorA, and then add a property of this type to each instrument.

• Create a property definition representing the origin column from VendorB, and then add a property of this type to each instrument.

Note: It's possible to create property definitions in advance and add properties at the same time as upserting instruments. However, in this tutorial we'll update instruments with new properties afterwards, to separate these operations more clearly.

Creating property definitions

The first step is to call the LUSID CreatePropertyDefinition API to create each property definition in turn. This must consist of:

• A display name.

• A data type, for example string or number.

• A 3-stage property key. The first stage is the ‘domain’ (entity) to which the property belongs, in this case Instrument. The second stage is a scope for the property, which we can use to record the originating system. The third stage is a code for the property, which must be unique within the scope, and which we can use to record the column name. So for example:

  • Instrument/VendorA/country_issue might be a suitable property key for the country_issue column from VendorA.

  • Instrument/VendorB/origin might be a suitable property key for the origin column from VendorB.

Step 3.1 of the accompanying Jupyter Notebook demonstrates how to do this using the LUSID Python SDK.

Adding properties to instruments

We can then call the LUSID UpsertInstrumentsProperties API to add properties of these types to existing instruments, taking care to specify the custom instrument scope.

Step 3.2 of the accompanying Jupyter Notebook demonstrates how to do this using the LUSID Python SDK.

Confirming properties have been added

By default, retrieving an instrument does not retrieve properties for that instrument. To confirm our properties have been added, we must ask LUSID to explicitly  ‘decorate’ properties onto instruments as part of retrieving them from the custom instrument scope.

Step 3.3 of the accompanying Jupyter Notebook demonstrates how to do this using the LUSID GetInstruments API. To show properties in the LUSID web app, click the cog icon (highlighted top right in the Data Management > Instruments dashboard below), choose Add column, and then select the properties to display from the Property screen. For example:

Using a derived property to normalise data

It seems clear the Instrument/VendorA/country_issue and Instrument/VendorB/origin properties contain data intended to convey the same meaning. 

We can add a third property to each instrument that automatically derives its values from these existing properties, amalgamating and normalising data from different systems for reporting or other purposes. 

Creating a derived property definition

The first step is to call the LUSID CreateDerivedPropertyDefinition API to create a derived property definition, which must consist of:

• A display name.

• A 3-stage property key in the same way as a standard property, consisting of a domain, scope and code. For example, a suitable key might be Instrument/Derived/Domicile.

• A derivation formula, to instruct LUSID how to coalesce, map or otherwise transform existing properties. Our objective is to first coalesce the two properties in order to account for missing values, and then map the plethora of country names and abbreviations to a standard set. See the list of available derivation functions.

Steps 4.1 and 4.2 of the accompanying Jupyter Notebook demonstrates how to do this using the LUSID Python SDK.

Confirming the derived property can be calculated

There's no equivalent step for adding a derived property to instruments. LUSID calculates values on-the-fly when asked to decorate a derived property onto instruments retrieved from the custom instrument scope.

Step 4.3 of the accompanying Jupyter Notebook demonstrates how to do this using the LUSID GetInstruments API. To show the derived property in the LUSID web app, follow the same steps above to display the appropriate column on the Data Management > Instruments dashboard. Note the change in background color signifies a derived property column:

Summary

In this tutorial, we've seen how to:

• Use instrument identifiers to merge data representing the same financial asset into a single LUSID instrument.

• Create appropriate economic definitions for Equities, Bonds, Futures and FxForwards.

• Add properties to instruments to store extra information.

• Use derived properties to add business value.