Lusid.Portfolio.Holding

  • Updated on Oct 22, 2024
  • Published on Aug 21, 2024

Type

Read/write

Author

Availability

Data provider

Read

Finbourne

Provided with LUSID

Providing you have sufficient access control permissions, the Lusid.Portfolio.Holding provider enables you to write a Luminesce SQL query that generates holdings from a history of all the transactions and other economic activity in one or more LUSID transaction portfolios.

Note: By default, Lusid.Portfolio.Holding cannot retrieve properties. To do this, you must first configure Lusid.Portfolio.Holding to 'inline' properties. See how to do this.

See also: Lusid.Portfolio.Holding.Writer

select * from Lusid.Portfolio.Holding where <filter-expression>;
SQL

Lusid.Portfolio.Holding 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.Portfolio.Holding' and FieldType = 'Parameter';
SQL

By default, Lusid.Portfolio.Holding returns a table of data populated with particular fields (columns). You can return a subset of these fields.

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.Portfolio.Holding' and FieldType = 'Column';
SQL

Note: Fields marked 'main' are returned by queries that start select ^ from Lusid.Portfolio.Holding...

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

select * from Lusid.Portfolio.Holding
SQL

It's typically useful to join to the Lusid.Instrument provider in order to retrieve the friendly name of underlying instruments. Note that since both providers have a DisplayName field, only that of Lusid.Instrument is returned in order to avoid a duplicate column clash:

select 
    h.^, 
    i.DisplayName 
from Lusid.Portfolio.Holding h
    left outer join Lusid.Instrument i
    on h.LusidInstrumentId = i.LusidInstrumentId
where PortfolioScope = 'Finbourne-Examples';
SQL

To retrieve properties for holdings, Lusid.Portfolio.Holding must have been configured to 'inline' the chosen properties (in this case CountryOfOrigin) into the standard set of holdings fields. Note it is possible to retrieve properties from the Instrument domain as well as the native Holding domain.

select * from Lusid.Portfolio.Holding 
where PortfolioScope = 'Finbourne-Examples' and PortfolioCode = 'UK-Equities' and CountryOfOrigin <> 'UK'
SQL

By default, Lusid.Portfolio.Holding generates holdings effective today.

  • To nominate a different date, specify the EffectiveAt parameter.

  • To nominate a range of dates and generate holdings on each day in that range, specify the EffectiveFrom parameter as the inception date and the EffectiveAt parameter as the end date. Note the time component is taken from EffectiveAt for every day in the range (see the example below).

  • To roll back LUSID's bitemporal as-at timeline, specify the AsAt parameter.

--Generate holdings for the last seven days at 12pm
@@x = select Date('now', '-7 day');
@@y = select DateTime(Date('now'), Time('12:00'));
select * from Lusid.Portfolio.Holding 
where PortfolioScope = 'Finbourne-Examples' and PortfolioCode = 'UK-Equities'
and EffectiveFrom = @@x
and EffectiveAt = @@y;
SQL

Note: Time-variant instrument and holding properties retrieved as part of the query are evaluated each day in a range.

In this example, holdings are not generated for portfolios containing transactions that have errors, such as unrecognised transaction types.

select * from Lusid.Portfolio.Holding where PortfolioScope = 'Finbourne-Examples'
and PortfolioCode in ('UK-Equities', 'Portfolio-With-Undefined-Txn-Types')
and Error is null
SQL