Lusid.Instrument.Property

  • Published on Aug 21, 2024

Type

Read/write

Author

Availability

Data provider

Read

Finbourne

Provided with LUSID

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.

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

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

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';
SQL

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';
SQL

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

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

In this example, the instrument is identified by LUID.

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

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';
SQL
select * from Lusid.Instrument.Property where PropertyScope = 'Ibor' and PropertyCode = 'GICSSector';
SQL

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';
SQL

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;
SQL
select * from Lusid.Instrument.Property where InstrumentIdType = 'LusidInstrumentId' 
and InstrumentId in (select LusidInstrumentId from Lusid.Instrument.Equity);
SQL

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'
SQL