Lusid.Portfolio.Reconciliation.Generic

Views:

Related resources:

Type	Read/write	Author	Availability
Data provider	Read	Finbourne	Provided with LUSID

The Lusid.Portfolio.Reconciliation.Generic provider enables you to write a Luminesce SQL query that reconciles either holdings or valuations in one or more transaction portfolios.

You can reconcile two different portfolios, the same portfolio with different recipes, the same portfolio at different times, or multiple portfolios in portfolio groups.

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

By default, Lusid.Portfolio.Reconciliation.Generic reconciles valuations by reporting the following metrics:

Valuation/PV/Amount to report the present value of each holding in its transaction currency.
Valuation/PV/Ccy to report each transaction currency.

To reconcile holdings, choose one or more non-valuation metrics instead, such as Holding/default/Units; see Example 1 for more information.

Also by default, the Lusid.Portfolio.Reconciliation.Generic provider:

Groups by the Instrument/default/LusidInstrumentId metric to report holdings individually by LUID. You can change this by setting the UseDefaultGroupKeys parameter to False and choosing a different metric; see Example 3.
Has a default set of comparison rules that match numbers exactly, without any tolerances. You can change this to allow non-exact matches by specifying tolerances for one or more metrics; see Example 5. Note that support for strings tolerances should be added soon.

Basic usage

@lookup_table = <select-statement>;
select * from Lusid.Portfolio.Reconciliation.Generic where toLookUp = @lookup_table;

Query parameters

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

The ToLookUp parameter is mandatory. Include the following data fields in the select statement for ToLookUp to identify the portfolio(s) to reconcile:

Data field	Status	Explanation
`LeftPortfolioScope`	Mandatory	The scope of the portfolio on the 'left hand side' of the reconciliation.
`LeftPortfolioCode`	Mandatory	The code of the portfolio on the left.
`LeftValuationDate`	Optional	Defaults to now. Specify a different datetime at which to generate a holdings report or perform a valuation operation for the portfolio on the left.
`LeftRecipeId`	Optional	Defaults to the built-in `default` recipe for the portfolio scope. For a valuation reconciliation, specify a different recipe for the portfolio on the left using the format `<recipe-scope>/<recipe-code>`; this recipe must be able to retrieve market data from a suitable LUSID store. For a holdings reconciliation, however, market data is typically not required, so the simplest thing to do is to omit this field and use the `default` recipe (see Example 1).
`LeftPortfolioType`	Optional	Defaults to `SinglePortfolio`. Specify `GroupPortfolio` to specify that the portfolio on the left is a portfolio group containing multiple portfolios.
`LeftReportCurrency`	Optional	Defaults to the base currency of the portfolio on the left. Specify an ISO 4217 currency code to set a different report currency.
`RightPortfolioScope`	Mandatory	The scope of the portfolio on the 'right hand side' of the reconciliation.
`RightPortfolioCode`	Mandatory	The code of the portfolio on the right.
`RightValuationDate`	Optional	As per `LeftValuationDate`, but for the portfolio on the right.
`RightRecipeId`	Optional	As per `LeftRecipeId`, but for the portfolio on the right.
`RightPortfolioType`	Optional	As per `LeftPortfolioType`, but for the portfolio on the right.
`RightReportCurrency`	Optional	As per `LeftReportCurrency`, but for the portfolio on the right.
`ReconciliationKey`	Optional	It is possible to have multiple portfolios on the left and on the right that are not in portfolio groups, in which case by default reconciliation results are not aggregated. Specify any string value to aggregate results so that each instrument is reported once rather than once per portfolio. For more information, contact Support.

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

Data fields

By default, Lusid.Portfolio.Reconciliation.Generic 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 supply in your query; see the section above.

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

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

Errors

We recommend examining the results of every query using the Error field. This field is populated if Lusid.Portfolio.Reconciliation.Generic fails to return data for some reason.

Examples

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

Example 1: Reconcile holdings in the same portfolio at two different times

In order to reconcile holdings, it's necessary to change the default metrics to remove those related to Valuation/PV/*.

In this example, we specify the Holding/default/Units metric instead to reconcile the number of units in each holding, and also the Instrument/default/Name metric to reveal the friendly name of each underlying instrument. Note new metrics must be passed to the MeasuresToReturn parameter as separate rows in a table of data with three ordered columns, as follows:

Column order	Column name	Expected value	Example value
1	`MeasureName`	A metric	`'Instrument/default/name'`
2	`Operation`	Either `Sum`, `Proportion`, `Average`, `Count`, `Min`, `Max`, `Value`, `SumOfPositiveValues`, `SumOfNegativeValues`, `SumOfAbsoluteValues`, `ProportionOfAbsoluteValues`	`'Sum'`
3	`ReconciliationSide`	Either `Left`, `Right` or specify `null` for `Both`	`null`

Note also the following:

The built-in default recipe (available in every portfolio scope) is used since it is not necessary to retrieve market data for a holdings reconciliation.
The same portfolio is specified for the left and right sides, but with holdings reports generated at different datetimes.

For more information on this scenario, see this tutorial.

@lookup_table = select 
   'FBNUniversity' as LeftPortfolioScope, 
   'T01004' as LeftPortfolioCode, 
   #2022-03-07 16:29:00# as LeftValuationDate, 
   'FBNUniversity' as RightPortfolioScope, 
   'T01004' as RightPortfolioCode, 
   #2022-03-07 16:30:00# as RightValuationDate
;

@metrics = values
   ('Holding/default/Units', 'Sum', null),
   ('Instrument/default/Name', 'Value', null)
;
@formatted_metrics = select
   column1 as 'MeasureName',
   column2 as 'Operation',
   column3 as 'ReconciliationSide'
from @metrics
;

select ^ from Lusid.Portfolio.Reconciliation.Generic 
   where ToLookUp = @lookup_table 
   and MeasuresToReturn = @formatted_metrics
;

For the purpose of comparison with the output of the same reconciliation operation in LUSID, Luminesce returns a table of data like this, with one row per metric (so two per holding):

Example 2: Pivot a reconciliation response to show metrics in separate columns

You can run a Lusid.Portfolio.Reconciliation.Generic response through the Tools.Pivot provider to return one row per holding instead of one per metric, with each metric shown in duplicated columns rather than in the same columns in different rows.

For example, pivoting the query in Example 1:

@lookup_table = select 
   'FBNUniversity' as LeftPortfolioScope, 
   'T01004' as LeftPortfolioCode, 
   #2022-03-07 16:29:00# as LeftValuationDate, 
   'FBNUniversity' as RightPortfolioScope, 
   'T01004' as RightPortfolioCode, 
   #2022-03-07 16:30:00# as RightValuationDate 
;

@metrics = values
   ('Holding/default/Units', 'Sum', null),
   ('Instrument/default/Name', 'Value', null)
;
@formatted_metrics = select
   column1 as 'MeasureName',
   column2 as 'Operation',
   column3 as 'ReconciliationSide'
from @metrics
;

@response = select ^ from Lusid.Portfolio.Reconciliation.Generic
   where ToLookUp = @lookup_table
   and MeasuresToReturn = @formatted_metrics
;

@pivot = use Tools.Pivot with @response
   --key=Measure
   --aggregateColumns=LeftMeasureValue,RightMeasureValue,Difference,ResultComparison
enduse
;

select * from @pivot;

...returns a table of data like this, with one row per holding instead of two, and the Instrument/default/Name and Holding/default/Units metrics shown in separate columns:

Example 3: Reconcile two portfolio valuations

You can perform a simple valuation reconciliation by providing a recipe for each portfolio able to retrieve market data (prices and FX rates) for all the underlying instruments in that portfolio. This can be the same recipe or two different ones.

Since by default the Lusid.Portfolio.Reconciliation.Generic provider:

Reports the Valuation/PV/Amount and Valuation/PV/Ccy metrics, this query returns two rows per holding (one for each metric), with PV reconciled in the transaction currency.
Groups by the Instrument/default/LusidInstrumentId metric, so underlying non-currency instruments in both portfolios must have the same LUID (that is, be mastered in the same instrument scope):

@lookup_table = select 
   'Growth' as LeftPortfolioScope, 
   'Internal' as LeftPortfolioCode, 
   #2022-03-11# as LeftValuationDate,
   'Growth/InternalRecipe' as LeftRecipeId,
   'Growth' as RightPortfolioScope, 
   'Custodian' as RightPortfolioCode, 
   #2022-03-11# as RightValuationDate,
   'Growth/CustodianRecipe' as RightRecipeId
;

select ^ from Lusid.Portfolio.Reconciliation.Generic
   where ToLookUp = @lookup_table
;

Example 4: Change the default valuation reconciliation metrics and group by settings

In this example, we take the valuation reconciliation in Example 3 and:

Change the default report metrics to reconcile PV in the portfolio currency instead of the transaction currency. As per Example 1, new metrics must be passed as rows in a table of data with three columns.
Group by instrument name rather than LUID. This might be useful if the portfolios contain the same real-world securities but are mastered in different instrument scopes (and so have different LUIDs). To do this, set the UseDefaultGroupKeys parameter to False and specify the KeysToGroupBy parameter (this can be a comma-separated list to group by multiple keys). Note the key you group by must also be reported as a metric in order to appear in the table of results:

@lookup_table = select 
   'Growth' as LeftPortfolioScope, 
   'Internal' as LeftPortfolioCode, 
   #2022-03-11# as LeftValuationDate,
   'Growth/InternalRecipe' as LeftRecipeId,
   'Growth' as RightPortfolioScope, 
   'Custodian' as RightPortfolioCode, 
   #2022-03-11# as RightValuationDate,
   'Growth/CustodianRecipe' as RightRecipeId
;

@metrics = values
   ('Valuation/PvInPortfolioCcy', 'Value', null),
   ('Instrument/default/Name', 'Value', null)
;
@formatted_metrics = select
   column1 as 'MeasureName',
   column2 as 'Operation',
   column3 as 'ReconciliationSide'
from @metrics
;

select ^ from Lusid.Portfolio.Reconciliation.Generic
   where ToLookUp = @lookup_table
   and MeasuresToReturn = @formatted_metrics
   and UseDefaultGroupKeys = false
   and KeysToGroupBy = 'Instrument/default/Name'
;

Example 5: Change the default tolerances

By default, a reconciliation break is reported if numbers do not match exactly. You can change this to allow non-exact matches.

To do this, pass new comparison rules to the ComparisonRules parameter as separate rows in a table of data with six ordered columns, as follows:

Column order	Column name	Expected value	Example
1	`RuleType`	Either `ReconcileNumericRule`, `ReconcileDateTimeRule`, `ReconcileStringRule`, `ReconcileExact`. Note that only `ReconcileNumericRule` is supported at the moment.	`'ReconcileNumericRule'`
2	`ComparisonType`	Either `Exact` or `AbsoluteDifference`	`'AbsoluteDifference'`
3	`Tolerance`	A number	`25`
4	`AppliesToKey`	A metric	`'Valuation/PV/Amount'`
5	`AppliesToOp`	Either `Sum`, `Proportion`, `Average`, `Count`, `Min`, `Max`, `Value`, `SumOfPositiveValues`, `SumOfNegativeValues`, `SumOfAbsoluteValues`, `ProportionOfAbsoluteValues`	`'Value'`

@lookup_table = select 
   'Growth' as LeftPortfolioScope, 
   'Internal' as LeftPortfolioCode, 
   #2022-03-11# as LeftValuationDate,
   'Growth/InternalRecipe' as LeftRecipeId,
   'Growth' as RightPortfolioScope, 
   'Custodian' as RightPortfolioCode, 
   #2022-03-11# as RightValuationDate,
   'Growth/CustodianRecipe' as RightRecipeId
;

@comparisons = values
   ('ReconcileNumericRule', 'AbsoluteDifference', 25, 'Valuation/PV/Amount', 'Value')
;
@comparison_rules = select
    column1 as 'RuleType',
    column2 as 'ComparisonType',
    column3 as 'Tolerance',
    column4 as 'AppliesToKey',
    column5 as 'AppliesToOp'
from @comparisons
;

select ^ from Lusid.Portfolio.Reconciliation.Generic
   where ToLookUp = @lookup_table
   and ComparisonRules = @comparison_rules
;

Keywords: luminesce, provider, reconciliation, reconcile holdings, reconcile valuations