Type | Read/write | Author | Availability |
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 configureLusid.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 theEffectiveAt
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