|Data provider||Read||Finbourne||Provided with LUSID|
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: The LUSID user running the query must have sufficient access control permissions to both use the provider and read holding data in LUSID. This should automatically be the case if you are the domain owner.
See also: Lusid.Portfolio.Holding.Writer
select * from Lusid.Portfolio.Holding where <filter-expression>;
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';
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...
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
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
Lusid.Portfolio.Holding generates holdings effective today.
- To nominate a different date, specify the
- To nominate a range of dates and generate holdings on each day in that range, specify the
EffectiveFromparameter as the inception date and the
EffectiveAtparameter as the end date (see the example below).
- To roll back LUSID's bitemporal as-at timeline, specify the
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