Views:

Related resources:

Explanation

Tutorials

How-to guides

A derived property must have a derivation formula that instructs LUSID how to automatically calculate values from one or more data fields or properties, including other derived properties.

Note: A derivation formula is stored as part of a derived property type.

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.
OperationSyntaxAllowed 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

Concatenationconcat(<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 mappingmap(<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 mappingmap(<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')
Averageaverage(<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))
Coalescecoalesce(<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)

Replacereplace(<value>: <string> = <string>)

<value> can be:

  • A string
  • An entity object
  • The following nested operations:
    • Concatenation
    • String mapping
    • Coalesce
Replace(Name: 'instr'='instrument')
Ifif(<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 stringtoString(<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 numbertoNumber(<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 formulae

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 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.

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)
 InstrumentPortfolioTransactionHolding
Fields on this type of entity that can be used in a formulaLusidInstrumentId
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
Units
CostPortfolioCcy.Amount
Cost.Amount
Cost.Currency

Identifiers on this type of entityFor example:
Identifiers[ClientInternal]
Identifiers[Figi]
Identifiers[LusidInstrumentId]		n/aFor example:
InstrumentIdentifiers[Instrument/default/ClientInternal]
InstrumentIdentifiers[Instrument/default/Figi]
InstrumentIdentifiers[Instrument/default/LusidInstrumentId]		n/a

Properties on this type of entity

Custom properties, for example:
Properties[Instrument/X/Y]
Properties[Instrument/Y/X]

 

System properties, for example:
Properties[Instrument/default/Name]
Properties[Instrument/default/Scope]

Custom properties, for example:
Properties[Portfolio/X/Y]
Properties[Portfolio/Y/X]
 

System properties, for example:
Properties[Portfolio/default/FundFamily]
Properties[Portfolio/default/ModelFees]

Custom properties, for example:
Properties[Transaction/X/Y]
Properties[Transaction/Y/X]
 

System properties, for example:
Properties[Transaction/default/TradeToPortfolioRate]
Properties[Transaction/default/BondInterest]
 

Custom properties, for example:
Properties[Holding/X/Y]
Properties[Holding/Y/X]
 

System properties, for example:
Properties[Holding/default/SourcePortfolioId]
Properties[Holding/default/SourcePortfolioScope]

Objects on other entity typesn/an/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]

Keywords: derived property, derivation formula, mathematical operation