Views:
TypeRead/writeAuthorAvailability
Data providerReadFinbourneProvided with LUSID

The Lusid.Portfolio.Valuation provider enables you to write a Luminesce SQL query that performs a valuation of a portfolio either on a particular day or over a range of days.

Note: The LUSID user running the query must have sufficient access control permissions to both use the provider and perform valuation operations in LUSID. This should automatically be the case if you are the domain owner.

By default, Lusid.Portfolio.Valuation reports the following metrics for each holding in the portfolio:

  • Valuation/PV/Amount reports the present value of the holding in the transaction currency.
  • Valuation/PV/Ccy reports the transaction currency.

You can report many more metrics; retrieve the full list using the Lusid.Portfolio.Valuation.Measure provider.

Also by default Lusid.Portfolio.Valuation groups by the following metrics to value each holding in a portfolio separately each day:

  • Instrument/default/LusidInstrumentId
  • Analytic/default/ValuationDate

You can set the UseDefaultGroupKeys parameter to False to override the default settings and choose different metrics to group by, for example just by date alone to sum holding values and report a daily total for the portfolio (see Example 4).

See also: Lusid.Valuation.Recipe, Lusid.Logs.Valuation.Manifest

Basic usage

select * from Lusid.Portfolio.Valuation 
where PortfolioScope = <scope> 
and PortfolioCode = <code> 
and Recipe = <recipe-scope>/<recipe-code> 
and EffectiveAt = <date>;

Query parameters

Lusid.Portfolio.Valuation has parameters that enable you to filter or refine a query.

Note: The Recipe and EffectiveAt parameters are mandatory.

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.Valuation' and FieldType = 'Parameter';

Data fields

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

Note: The PortfolioScope and PortfolioCode fields are mandatory to specify in your query; see the Basic usage section.

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.Valuation' and FieldType = 'Column';

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

Examples

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

Example 1: Value each holding in the transaction currency on a particular day

select *
from Lusid.Portfolio.Valuation
where Recipe = 'FBNUniversity/Module-4-1Recipe'
and PortfolioCode = 'Module-4-1'
and PortfolioScope = 'FBNUniversity'
and EffectiveAt = #2022-03-07#

Example 2: Value each holding per day for a week

Specify the EffectiveFrom parameter as the inception date and the EffectiveAt parameter as the end date.

select *
from Lusid.Portfolio.Valuation
where Recipe = 'FBNUniversity/Module-4-1Recipe'
and PortfolioCode = 'Module-4-1'
and PortfolioScope = 'FBNUniversity'
and EffectiveFrom = #2022-03-07#
and EffectiveAt = #2022-03-11#

Example 3: Report metrics to value each holding and measure P&L in the portfolio currency

Metrics other than the defaults must be specified as a comma-separated list of values, each with a multi-stage metric key and an operation such as Sum, Value, Proportion and so on. The metrics must then be formatted into a suitable table to pass to the MeasuresToReturn parameter. Note if you specify non-default metrics you must explicitly specify the default metrics (Valuation/PV/Amount and Valuation/PV/Ccy) in order to report them as well.

In this example:

  • Instrument/default/Name reports the user-friendly name of a holding.
  • Valuation/PvInPortfolioCcy reports the present value of a holding in the portfolio currency.
  • ProfitAndLoss/Unrealised/Market/PortfolioCcy(Window="YTD") reports the P&L of a holding in the portfolio currency since the beginning of the year. Note some metrics (including all P&L metrics) mandate at least one option that must be specified as shown.

For more examples of useful metrics to report, see this tutorial.

@metrics = values
('Valuation/PV/Amount', 'Sum'),
('Valuation/PV/Ccy', 'Value'),
('Instrument/default/Name', 'Value'),
('Valuation/PvInPortfolioCcy', 'Sum'),
('ProfitAndLoss/Unrealised/Market/PortfolioCcy(Window="YTD")', 'Sum');

@metrics_formatted = select column1 as MeasureName, column2 as Operation from @metrics;

select *
from Lusid.Portfolio.Valuation
where Recipe = 'FBNUniversity/Module-4-1Recipe' 
and PortfolioCode = 'Module-4-1'
and PortfolioScope = 'FBNUniversity'
and EffectiveAt = #2022-03-07#
and MeasuresToReturn = @metrics_formatted;

Example 4: Group by valuation date to sum holdings and value the portfolio as a whole each day

In this example, the KeysToGroupBy parameter groups by the Analytic/default/ValuationDate metric in order to sum individual holdings and calculate a total for the portfolio on a particular date. At the same time it is necessary to set the UseDefaultGroupKeys parameter to False to disable the default group by settings (see top).

Note for a multi-currency portfolio it only makes sense to specify the Sum operation for metrics that normalise monetary values to the portfolio currency (hence the Valuation/PV/Amount and Valuation/PV/Ccy metrics are omitted below). The Instrument/default/Name metric is also omitted as individual holding information is meaningless in an aggregated portfolio valuation:

@metrics = values
('Valuation/PvInPortfolioCcy', 'Sum'),
('ProfitAndLoss/Unrealised/Market/PortfolioCcy(Window="YTD")', 'Sum');

@metrics_formatted = select column1 as MeasureName, column2 as Operation from @metrics;

select *
from Lusid.Portfolio.Valuation
where Recipe = 'FBNUniversity/Module-4-1Recipe' 
and PortfolioCode = 'Module-4-1'
and PortfolioScope = 'FBNUniversity'
and EffectiveAt = #2022-03-07#
and MeasuresToReturn = @metrics_formatted
and KeysToGroupBy = 'Analytic/default/ValuationDate'
and UseDefaultGroupKeys = False;

 

Keywords: luminesce, provider, valuation