Lusid.Instrument.Property

The Lusid.Instrument.Property provider enables you to write a Luminesce SQL query that retrieves custom properties for instruments mastered in LUSID.

Note: The LUSID user running the query must have sufficient access control permissions to both use the provider and read instrument and property data stored in LUSID. This should automatically be the case if you are the domain owner.

Note that, by default, the Lusid.Instrument entity provider does not retrieve properties. You can use Lusid.Instrument.Property to do so in conjunction with other providers (including Lusid.Instrument) to analyse all the data associated with instruments.

Basic usage

select * from Lusid.Instrument.Property where <instrument-or-property>;

You must specify either:

• The InstrumentIdType and InstrumentId fields to identify an instrument whose properties to return. The InstrumentIdType field refers to a unique instrument identifier, and the InstrumentId field to a valid value for that identifier.

• The PropertyScope and PropertyCode fields to identify the scope and code of a property whose instruments to return.

Specify the InstrumentScope field as well if instruments are mastered in a custom instrument scope (otherwise only the built-in default scope is searched).

Query parameters

Lusid.Instrument.Property has parameters that enable you to filter or refine a query.

To list available parameters, their data types, default values, and an explanation for each, run the following query using a suitable tool:

select FieldName, DataType, ParamDefaultValue, Description from Sys.Field where TableName = 'Lusid.Instrument.Property' and FieldType = 'Parameter';

Data fields

By default, Lusid.Instrument.Property returns a table of data populated with particular fields (columns). You can return just a subset of these fields if you wish.

To list fields available to return, their data types, whether fields are considered 'main', and an explanation for each, run the following query using a suitable tool:

select FieldName, DataType, IsMain, IsPrimaryKey, SampleValues, Description from Sys.Field where TableName = 'Lusid.Instrument.Property' and FieldType = 'Column';

Note: Fields marked 'main' are returned by queries that use a caret character, for example select ^ from Lusid.Instrument.Property.

Examples

Note: For more example Luminesce SQL queries, visit our Github repo.

Example 1: Retrieve all the properties for a particular instrument

In this example, the instrument is identified by LUID.

select * from Lusid.Instrument.Property where InstrumentIdType = 'LusidInstrumentId' and InstrumentId = 'LUID_6OHFNCI7';

In this example, the instrument is identified by FIGI and mastered in a custom instrument scope.

select * from Lusid.Instrument.Property where InstrumentScope = 'my-custom-instrument-scope'
and InstrumentIdType = 'Figi' and InstrumentId = 'BBG000BDZGH6';

Example 2: Retrieve all the instruments that have a particular property

select * from Lusid.Instrument.Property where PropertyScope = 'Ibor' and PropertyCode = 'GICSSector';

Example 3: Retrieve all the multi-value properties for a particular instrument

You can join Lusid.Instrument.Property to Lusid.Property.Definition to retrieve information about underlying property types:

select * from Lusid.Instrument.Property prop
inner join Lusid.Property.Definition def
    on def.PropertyScope = prop.PropertyScope
    and def.PropertyCode = prop.PropertyCode
    where prop.InstrumentIdType = 'LusidInstrumentId' and prop.InstrumentId = 'LUID_6OHFNCI7'
    and def.ConstraintStyle = 'Collection';

Example 4: Retrieve a time-variant property as a time-series for a particular instrument

Specify the GetHistorical query parameter to retrieve a time-variant property.

select * from Lusid.Property where Domain = 'Instrument'
and EntityIdType = 'LusidInstrumentId' and EntityId = 'LUID_G67H99J6'
and PropertyScope = 'Ibor' and PropertyCode = 'AnalystRating' and GetHistorical = True;

Example 5: Retrieve all the properties for all the equity instruments

select * from Lusid.Instrument.Property where InstrumentIdType = 'LusidInstrumentId' 
and InstrumentId in (select LusidInstrumentId from Lusid.Instrument.Equity);

Example 6: Decorate particular instrument properties onto holdings

You can join Lusid.Instrument.Property to an entity provider such as Lusid.Portfolio.Holding to decorate holdings with particular instrument properties:

select
   h.*,
   pC.Value as Coupon,
   pI.Value as Industry
from
   lusid.portfolio.holding h
   left outer join Lusid.Instrument.Property pC
      on pC.InstrumentIdType = 'LusidInstrumentId'
      and pC.InstrumentId = h.LusidInstrumentId
      and pC.PropertyCode = 'Coupon'
   left outer join Lusid.Instrument.Property pI
      on pI.InstrumentIdType = 'LusidInstrumentId'
      and pI.InstrumentId = h.LusidInstrumentId
      and pI.PropertyCode = 'Industry'
where
   h.PortfolioScope = 'Finbourne-Examples'