Views:

A derived property is a special kind of property that you can add to most types of LUSID entity to extend the quality and quantity of information stored about those entities.

Rather than having property values assigned explicitly, LUSID calculates derived property values on-the-fly using one or more mathematical or related operations. 

Consider the following example, of instruments loaded into LUSID from two different sources. Data relating to domicile from each source has been mapped to two (standard) properties, represented by the country_issue and origin columns. We can see that the same countries are represented using different values, and that some values are missing:

Because it seems likely this information has the same meaning, we can create a Domicile derived property that automatically maps and coalesces country_issue and origin values to a new, normalised set. This derived property can subsequently be used for reporting purposes or for consumption by a downstream system, without having altered the original data:

The derivation formula (see below) that controls how LUSID calculates Domicile values might look like this:

map(coalesce(Properties[Instrument/VendorA/country_issue], Properties[Instrument/VendorB/origin], 'Unknown'):
 'United Kingdom'='UK', 'united_kingdom'='UK',
 'Great Britain'='UK', 'GB'='UK', 'DE'='Germany',
 'United States'='USA', 'usa'='USA', default='Unknown')

Creating a derived property definition

The first step is to create a derived property definition, which must consist of:

  • A display name.
  • A data type, for example string or number.
  • A 3-stage property key that uniquely identifies the derived property. The first stage is the ‘domain’ (entity) to which the derived property belongs, for example Instrument or Transaction. The second stage is a scope, or namespace, which can be any intuitive string. The third stage is a code, which must be unique within the scope, and again can be any intuitive string. So for example, the Domicile derived property in the picture above might have the property key Instrument/Derived/Domicile.
  • A derivation formula that specifies how LUSID should calculate derived property values on-the-fly.

Note the following: 

  • A derived property definition, like a property definition, is one of the few data structures in LUSID that is monotemporal. That is, the definition can be updated, and you can retrieve previous definitions by rolling back the as at timeline. However, for a given as at datetime, the prevailing definition will apply to all effective at datetimes.
  • It is not possible to alter the derivation formula once a derived property is created. You would need to create a new derived property, or delete and recreate the existing one if you want to use the same scope and code.
  • A derived property cannot derive from another derived property.

Using the REST API

  1. Obtain an API access token.
  2. Call the LUSID CreateDerivedPropertyDefinition API for your LUSID domain, passing in your API access token and a suitable definition, for example:
    curl -X POST "https://<your-domain>.lusid.com/api/api/propertydefinitions/derived"
      -H "Authorization: Bearer <your-API-access-token>"
      -H "Content-Type: application/json"
      -d '{
        "domain": "Instrument",
        "scope": "Derived",
        "code": "Domicile",
        "displayName": "Domicile",
        "dataTypeId": {
          "scope": "system",
          "code": "string"
        },
        "propertyDescription": "Normalising domicile-related data",
        "derivationFormula": "map(coalesce(Properties[Instrument/VendorA/country_issue], Properties[Instrument/VendorB/origin], 'Unknown'):
          'United Kingdom'='UK', 'united_kingdom'='UK',
          'Great Britain'='UK', 'GB'='UK', 'DE'='Germany',
          'United States'='USA', 'usa'='USA', default='Unknown')"
    }'

Using the LUSID web app

  1. Sign in to the LUSID web app using the credentials of a LUSID administrator.
  2. From the left-hand menu, select Data Management > Properties.
  3. On the Properties dashboard, click the Create property button (top right).
  4. Enter Basic data (domain, scope and code) and then choose Derived property.
  5. Choose a Data type and then enter the Derivation formula, for example:

Constructing a derivation formula

A derivation formula consists of one or more operations on one or more values. Note the following:

  • Operations can be nested. So for example a Mathematical operation can add a number to the result of another, nested Mathematical operation.
  • The term ‘entity object’ in the table below is used to refer to all the fields, properties, identifiers and other data structures belonging to entities that are available to be operated on in the formula.
Operation Syntax Allowed values
Mathematical <value> <operator> <value>

<value> can be:

  • A number
  • An entity object
  • The following nested operations:
    • Mathematical
    • Numeric mapping
    • Average
    • Coalesce

<operator> can be:

  • +
  • -
  • *
  • /
  • ^ (to the power of)

Examples:

Properties[Instrument/default/SharesOutstanding] * Properties[Instrument/default/Price]
(Properties[Instrument/default/Cost] + Properties[Instrument/default/Price]) / Properties[Instrument/default/SharesOutstanding]
(Properties[Instrument/default/Price] - Properties[Instrument/default/Cost]) ^ 2

Concatenation concat(<value>, <value>…)

<value> can be (there must be at least two, each separated by a comma):

  • A number
  • A string
  • An entity object
  • Any other nested operation, including another Concatenation
concat(Properties[Instrument/default/Name], ' - ', Properties[Instrument/default/Currency], '(', Properties[Instrument/default/Country], ')')
concat(Name, ' - ', Luid)
Numeric mapping map(<value>: <expression>)

<value> can be:

  • A number
  • An entity object
  • The following nested operations:
    • Mathematical
    • Numeric mapping
    • Average
    • Coalesce

<expression> is a comma-separated list of one or more of:

  • <string> = <number>
  • <number> = <number>
  • default = <number>
map(Properties[Instrument/default/S_and_P]: 'AA'=1, 'BB'=2,'CC'=3, default=0)
map(Properties[Instrument/default/Duration]: '3 months'=3, '9 months'=9)
String mapping map(<value>: <expression>)

<value> can be:

  • A string
  • An entity object
  • The following nested operations:
    • Concatenation
    • String mapping
    • Coalesce

<expression> is a comma-separated list of one or more of:

  • <string> = <string>
  • <number> = <string>
  • default = <string>
map(Properties[Instrument/default/Duration]: '3 months'='3m', '9 months'='9m')
map(Properties[Instrument/default/Duration]: '3 months'='3m', '9 months'='9m', Default = '0m')
Average average(<value>, <value>…)

<value> can be (there must be at least two, each separated by a comma):

  • A number
  • An entity object
  • The following nested operations:
    • Mathematical
    • Numeric mapping
    • Average
    • Coalesce
average(Properties[Instrument/default/Price], Properties[Instrument/default/Cost])
average(map(Properties[Instrument/default/S_and_P]: 'AA'=1, 'BB'=2,'CC'=3), map(Properties[Instrument/default/Moodys]: 'A'=1, 'B+'=2, 'C'=3), map(Properties[Instrument/default/Fitch]: '1'=1, '2'=2, '3'=3))
Coalesce coalesce(<value>, <value>…)

<value> can be (there must be at least two, each separated by a comma):

  • A number
  • A string
  • An entity object
  • Any other nested operation, including another Coalesce

Note Coalesce prefers property values in the order they are specified. So in the first example below, the derived property takes its description value from Commbank if that property exists, from Monzo if not, and finally from AmericanExpress if neither. If none of these properties exist, the derived property has the value Unknown.

coalesce(Properties[Transaction/Commbank/description], Properties[Transaction/Monzo/Description], Properties[Transaction/AmericanExpress/Description], 'Unknown')
coalesce(Properties[Transaction/Commbank/Price], Properties[Transaction/Monzo/Price], Properties[Transaction/AmericanExpress/Price], -1)

Replace replace(<value>: <string> = <string>)

<value> can be:

  • A string
  • An entity object
  • The following nested operations:
    • Concatenation
    • String mapping
    • Coalesce
Replace(Name: 'instr'='instrument')
If if(<value> <operator> <value>) 
then <trueValue> 
else <falseValue>

<value> can be:

  • A number
  • A string
  • An entity object
  • Any other nested operation, including another If

<operator> can be any of these filter operators, for example:

  • eq
  • neq
  • gt
  • startswith

<trueValue> and <falseValue> can be:

  • A number
  • A string
  • An entity object
  • Any other nested operation, including another If
if(Properties[Transaction/Commbank/Price] gt Properties[Transaction/Commbank/Cost]) then 'true' else 'false'
if(Name eq 'InstrumentCommbank') then 'Commbank' else Name
if(Properties[Instrument/default/Country] neq 'UK') then 'Others' else Properties[Instrument/default/Country]
Convert to string toString(<value>)

<value> can be:

  • A number, with other data types untested but likely to work
  • An entity object
  • Any other nested operation, including another Convert to string
Convert to number toNumber(<value>)

<value> can be:

  • A string representing a number, for example "1" or "1.123" (converting other data types to numbers should be supported soon)
  • An entity object that returns a number
  • Any other nested operation providing it returns a number

If <value> cannot be parsed (for example toNumber("hello")) the operation returns 0.

Using entity objects in derivation formulas

The data structures belonging to entities that can be used in a derivation formula differ depending on the type of entity the derived property belongs to.

For example, if the derived property belongs to holdings (that is, the first part of the 3-stage property key signals it is in the Holding domain), then values can derive from:

  • Any other (standard) property in the Holding domain, and/or
  • Certain fields on holdings themselves, and/or
  • Certain fields, identifiers, sub-holding keys and properties on other entities.

Note: A derived property cannot derive from another derived property.

Consider the following example, of a derivation formula for a Holding derived property that consists of a series of If operations and a nested Concat operation, all conditional on the HoldingType entity field:

if( HoldingType eq 'P' ) then 'Market Value of Investments' 
else if ( HoldingType eq 'R' ) then 'Payable/Receivable' 
else if ( HoldingType eq 'B' ) then 'Cash' 
else concat('Unknown holding type ', HoldingType)
 
Entity (derived property domain)
  Instrument Portfolio Transaction Holding
Fields on this type of entity that can be used in a formula LusidInstrumentId
Name
State
LookthroughPortfolio.Scope
LookthroughPortfolio.Code
Id.Scope
Id.Code
Type
DisplayName
Description
Created
IsDerived
BaseCurrency
ParentPortfolioId.Scope
ParentPortfolioId.Code
TransactionId
CounterpartyId
TransactionDate
SettlementDate
EntryDateTime
Units
ExchangeRate
Source
TransactionCurrency
InstrumentUid
Type
TransactionPrice.Price
TransactionPrice.Type
TotalConsideration.Amount
TotalConsideration.Currency
InstrumentUid
HoldingType
SettledUnits
CostPortfolioCcy.Amount
Cost.Amount
Cost.Currency
Identifiers on this type of entity For example:
Identifiers[ClientInternal]
Identifiers[Figi]
Identifiers[LusidInstrumentId]
n/a For example:
InstrumentIdentifiers[Instrument/default/ClientInternal]
InstrumentIdentifiers[Instrument/default/Figi]
InstrumentIdentifiers[Instrument/default/LusidInstrumentId]
n/a
Properties on this type of entity For example:
Properties[Instrument/X/Y]
Properties[Instrument/Y/X]
For example:
Properties[Portfolio/X/Y]
Properties[Portfolio/Y/X]
For example:
Properties[Transaction/X/Y]
Properties[Transaction/Y/X]
For example:
Properties[Holding/X/Y]
Properties[Holding/Y/X]
Objects on other entity types n/a n/a

Instrument properties (note you must also decorate the instrument property when decorating the derived property onto retrieved transactions). For example:
Properties[Instrument/X/Y]
Properties[Instrument/Y/X]

Transaction fields:
Transaction.TransactionId
Transaction.CounterpartyId
Transaction.TransactionDate
Transaction.SettlementDate
Transaction.EntryDateTime
Transaction.Units
Transaction.ExchangeRate
Transaction.Source
Transaction.TransactionCurrency
Transaction.InstrumentUid
Transaction.Type
Transaction.TransactionPrice.Price
Transaction.TransactionPrice.Amount
Transaction.TotalConsideration.Amount
Transaction.TotalConsideration.Currency

Transaction instrument identifiers (for example):
Transaction.InstrumentIdentifiers[Instrument/default/Figi]

Transaction sub-holding keys (for example):
SubHoldingKeys[Transaction/X/Y]

Transaction properties (for example):
Transaction.Properties[Transaction/X/Y]
Transaction.Properties[Transaction/Y/X]


Instrument properties (note you must also decorate the instrument property when decorating the derived property onto retrieved holdings). For example:
Properties[Instrument/X/Y]
Properties[Instrument/Y/X]

Calculating derived property values on-the-fly

There's no equivalent step for explicitly adding a derived property to entities of a particular type, as there is with standard properties. LUSID calculates values on-the-fly when asked to decorate a derived property onto retrieved entities of that type.

For example, to retrieve the Domicile derived property for the BP instrument (LUID_00003D5D), call the LUSID GetInstrument API for your LUSID domain, passing in your API access token and the Instrument/Derived/Domicile 3-stage property key:

curl -X GET "https://<your-domain>.lusid.com/api/api/instruments/LusidInstrumentId/LUID_00003D5D?propertyKeys=Instrument%2FDerived%2FDomicile&scope=default"
  -H "Authorization: Bearer <your-API-access-token>"
 

The derived property value is automatically calculated and returned, in this case UK:

{
  "href": "https://<your-domain>.lusid.com/api/api/instruments/LusidInstrumentId/LUID_00003D5D?scope=default",
  "scope": "default",
  "lusidInstrumentId": "LUID_00003D5D",
  "version": {
    "effectiveFrom": "0001-01-01T00:00:00.0000000+00:00",
    "asAtDate": "2022-03-02T11:18:11.5634600+00:00"
  },
  "name": "BP",
  "identifiers": {
    "ClientInternal": "imd_43535553",
    "Ticker": "BP/LN",
    "ShareClassFigi": "BBG001S61CN0",
    "LusidInstrumentId": "LUID_00003D5D",
    "Figi": "BBG000C05BD1",
    "CompositeFigi": "BBG000C059M6",
    "Isin": "GB0007980591"
  },
  "properties": [
    {
      "key": "Instrument/Derived/Domicile",
      "value": {
        "labelValue": "UK"
      },
      "effectiveFrom": "0001-01-01T00:00:00.0000000+00:00"
    }
  ],
  "instrumentDefinition": {
    "identifiers": {},
    "domCcy": "GBP",
    "instrumentType": "Equity"
  },
  "state": "Active",
  "assetClass": "Equities",
  "domCcy": "GBP",
  ...
}