Lusid.Portfolio.AggregatedReturn

The Lusid.Portfolio.AggregatedReturn provider enables you to write a Luminesce SQL query that retrieves aggregated performance returns for one or more LUSID portfolios.

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

See also: Lusid.Portfolio.Return, Lusid.Portfolio.Return.Writer

Basic usage

@lookup_table = <select-statement>;
@performance_metrics = <select-statement>;
select * from Lusid.Portfolio.AggregatedReturn where toLookUp = @lookup_table and performanceReturnMetrics = @performance_metrics

Query parameters

Lusid.Portfolio.AggregatedReturn has parameters that help you construct a valid table of data to look up. For example:

• ToLookUp. Mandatory. Use the following data fields in the select statement to identify the portfolios and returns to look up:

  Status	Data field	Explanation
  Required	PortfolioScope	The scope of the portfolio to look up.
  	PortfolioCode	The code of the portfolio to look up.
  	ReturnScope	The scope of the returns to look up for a portfolio.
  	ReturnCode	The code of the returns to look up for a portfolio.
  Optional	Period CompositeMethod OutputFrequency AlternativeIncDate HolidayCalendars	For more information on these fields, see this table.

• PerformanceReturnMetrics. Mandatory. Use the following data fields in the select statement to control how returns are aggregated:

  Status	Data field	Explanation
  Required	WindowMetric	For more information on these fields, see this table.
  Optional	Type AllowPartial Annualised WithFee SeedAmount Alias

• Recipe. Optional. Specify the scope and code of a recipe if you want to provide a source of FX rates to normalise constituents in different currencies to the composite currency.

To list all 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.AggregatedReturn' and FieldType = 'Parameter';

Data fields

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

Note: Some of these fields are mandatory to specify in your query; see the section above.

To list all 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.AggregatedReturn' and FieldType = 'Column';

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

Errors

We recommend examining the results of every query using the Error field.

This field is populated if Lusid.Portfolio.AggregatedReturn cannot retrieve an entity for some reason. For example, the Error field reveals that this query:

@lookup_table = select 'Finbourne-Examples' as PortfolioScope, 'UK-Equities' as PortfolioCode, 'Performance' as ReturnCode;
@performance_metrics = select '1D' as WindowMetric;
select Error from Lusid.Portfolio.AggregatedReturn where ToLookUp = @lookup_table and PerformanceReturnMetrics = @performance_metrics;

...fails because a ReturnScope has not been provided in the lookup table.

Examples

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

Example 1: Aggregate one set of returns daily and over a month

@returns = select 'Finbourne-Examples' as PortfolioScope,
                  'UK-Equities' as PortfolioCode,
                  'Production' as ReturnScope,
                 'Performance-1' as ReturnCode,
                  'Daily' as Period, 
                  'Asset' as CompositeMethod,
                  'Daily' as OutputFrequency,
                  '2020-01-01T00:00:00.0000Z' as AlternativeIncDate;
@perfMetrics = select '1M' as WindowMetric, false as AllowPartial, false as ithFees, false as Annualised, '1 Month' as Alias
               union all
               select '1D' as WindowMetric, false as AllowPartial, false as WithFees, false as Annualised, '1 Day' as Alias;
select * from Lusid.Portfolio.AggregatedReturn 
                   where ToLookUp = @returns
                   and FromEffectiveAt = #2020-03-01#
                   and UntilEffectiveAt = #2020-03-28#
                   and PerformanceReturnMetrics = @perfMetrics;

Example 2: Aggregate two sets of returns daily, monthly, bi-monthly and since portfolio inception

In this example, the scope and code of a recipe is passed in using the Recipe parameter.

@@recipe = select 'ShadowIBOR/BloombergMarketValue';
@returns = select 'Finbourne-Examples' as PortfolioScope,
                  'UK-Equities' as PortfolioCode,
                  'Production' as ReturnScope,
                  'Performance-1' as ReturnCode
           union all
           select 'Finbourne-Examples' as PortfolioScope,
                  'UK-Equities' as PortfolioCode,
                  'Production' as ReturnScope,
                  'Performance-2' as ReturnCode;
@perfMetrics = select '1M' as WindowMetric, false as AllowPartial
               union all
               select '1D' as WindowMetric, false as AllowPartial
               union all
               select 'Inc' as WindowMetric, false as AllowPartial
               union all
               select '2M' as WindowMetric, false as AllowPartial;
select * from Lusid.Portfolio.AggregatedReturn 
                   where ToLookUp = @returns
                   and FromEffectiveAt = #2020-03-01#
                   and UntilEffectiveAt = #2020-03-28#
                   and PerformanceReturnMetrics = @perfMetrics
                   and Recipe = @@recipe;