Lusid.Instrument.<Type> reader providers

Luminesce has a generic Lusid.Instrument provider and a number of Lusid.Instrument.<Type> providers that enable you to write a Luminesce SQL query to retrieve 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 data in LUSID. This should automatically be the case if you are the domain owner.

The generic Lusid.Instrument provider retrieves basic information about instruments of any asset class (it does not retrieve full economic definitions). Dedicated providers retrieve instruments of a particular asset class, for example Lusid.Instrument.Bond retrieves the full economic definition of bond instruments.

Note the following:

• By default, providers retrieve instruments from the default scope and all custom instrument scopes. You can narrow the search using the Scope field.

• By default, providers do not retrieve properties decorated onto instruments. More information.

• You can create, update or delete instruments using equivalent Lusid.Instrument.<Type>.Writer providers. More information.

Basic usage

select * from Lusid.Instrument.<Type> where <filter-expression>

Query parameters

All Lusid.Instrument.<Type> providers have the same set of parameters that enable you to filter or refine a query.

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

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

Data fields

Each Lusid.Instrument.<Type> provider returns a table of data populated with relevant fields (columns). You can return a subset of these fields.

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

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

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

Examples

Note: For more examples, try the Luminesce Github repo.

Example 1: Retrieve all non-currency instruments of any asset class

Note that Lusid.Instrument does not retrieve full economic definitions.

select ^ from Lusid.Instrument where LusidInstrumentId not like 'CCY_%';

Example 2: Retrieve bonds that mature after a particular date

select * from Lusid.Instrument.Bond where MaturityDate > #2023-01-01#;>

Example 3: Reveal which portfolios have FxForward positions and how long until expiry

select 
    f.LusidInstrumentId,
    f.DisplayName as InstrumentName,
    p.DisplayName as PortfolioName,
    t.TxnId as TransactionId,
    t.Type as TransactionType,
    f.DomCcy as BuyCcy,
    f.DomAmount as BuyAmount,
    f.FgnCcy as SellCcy,
    f.FgnAmount as SellAmount,
    f.RefSpotRate as Rate,
    f.StartDate,
    f.MaturityDate as ExpiryDate,
    cast(julianday(f.MaturityDate) - julianday('now', 'start of day') as Integer) as DaysToExpiry
from
    Lusid.Instrument.FxForward f
    left outer join Lusid.Portfolio.Holding h
        on f.LusidInstrumentId = h.LusidInstrumentId
    left outer join Lusid.Portfolio.Txn t
        on h.LusidInstrumentId = t.LusidInstrumentId
        and h.PortfolioScope = t.PortfolioScope
        and h.PortfolioCode = t.PortfolioCode
    left outer join Lusid.Portfolio p
        on h.PortfolioScope = p.PortfolioScope
        and h.PortfolioCode = p.PortfolioCode
order by f.StartDate asc

Example 4: Retrieve version information for instruments created by a particular user

select ClientInternal, 
  AsAtCreated, 
  RequestIdCreated, 
  AsAtModified, 
  UserIdModified, 
  RequestIdModified, 
  AsAtVersionNumber 
from lusid.instrument.equity 
where UserIdCreated = '00uji4twb4jDcHGjN2p7'