Views:
TypeRead/writeAuthorAvailability
Data providerReadFinbourneProvided 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

Basic usage

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

Query parameters

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

Data fields

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

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

Examples

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

Example 1: Generate holdings in every portfolio

select * from Lusid.Portfolio.Holding

Example 2: Generate holdings in all portfolios in a particular scope, and show instrument friendly names

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

Example 3: Generate holdings in a particular portfolio, and retrieve properties

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'

Example 4: Generate holdings in a particular portfolio every day between two dates

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 (see the example below).
  • To roll back LUSID's bitemporal as-at timeline, specify the AsAt parameter.
select * from Lusid.Portfolio.Holding where PortfolioScope = 'Finbourne-Examples' and PortfolioCode = 'UK-Equities'
and EffectiveFrom = #2022-06-06#
and EffectiveAt = #2022-06-10#

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

Example 5: Generate holdings in all portfolios in a particular scope, ignoring those with errors

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
Keywords: luminesce, provider, holdings