Lusid.Portfolio.Return

The Lusid.Portfolio.Return provider enables you to write a Luminesce SQL query that retrieves simple 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.AggregatedReturn, Lusid.Portfolio.Return.Writer

Basic usage

@lookup_table = <select-statement>;
select * from Lusid.Portfolio.Return where toLookUp = @lookup_table and <filter-expression>

Query parameters

Lusid.Portfolio.Return has parameters that help you construct a valid table of data to look up.

Note: The toLookUp parameter is mandatory. Use the following data fields in your select statement to identify the portfolios and returns to look up:

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

Data fields

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

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

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

Errors

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

This field is populated if Lusid.Portfolio.Return 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;
select Error from Lusid.Portfolio.Return where toLookUp = @lookup_table;

...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: Get all daily performance returns for a particular portfolio

@lookup_table = select 'Finbourne-Examples' as PortfolioScope, 'UK-Equities' as PortfolioCode, 'Production' as ReturnScope, 'Performance' as ReturnCode;
select Error from Lusid.Portfolio.Return where toLookUp = @lookup_table;

Example 2: Retrieve monthly loss-making portfolios from last year

In this example:

• The Lusid.Portfolio provider is used to populate values for PortfolioScope and PortfolioCode in the lookup table, and its where clause selects the portfolio scope to retrieve portfolio codes for.

• @@today and @@oneYearAgo demonstrate the use of scalar variables.

@@today = select date('now');
@@oneYearAgo = select date('now', '-1 year');

@table_of_data = select PortfolioScope, PortfolioCode, 'Production' as ReturnScope, 'Performance' as ReturnCode, 'Monthly' as Period
from Lusid.Portfolio where PortfolioScope = 'Finbourne-Examples';

select * from Lusid.Portfolio.Return where toLookUp = @table_of_data 
and FromEffectiveAt = @@oneYearAgo and UntilEffectiveAt = @@today
and RateOfReturn < 0