Lusid.Portfolio.AtoB

The Lusid.Portfolio.AtoB provider enables you to write a Luminesce SQL query that generates an A2B report for a LUSID transaction portfolio to understand how it has changed in value between two points in time.

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

See also: Lusid.Portfolio.Valuation

Basic usage

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

Query parameters

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

Note: The Recipe, FromDate and ToDate 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.AtoB' and FieldType = 'Parameter';

Data fields

By default, Lusid.Portfolio.AtoB 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.AtoB' and FieldType = 'Column';

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

Examples

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

Example 1: Generate an A2B report to understand changes in value to a portfolio during a week

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

For the purpose of comparison with the output of the same A2B operation in LUSID where each holding has one row which can be expanded, Luminesce returns a table of data like this, with two rows per holding:

Example 2: Group holdings and aggregate columns to show one row per holding

You can group the holdings by LusidInstrumentId and then Sum() (SQLite aggregate function) the grouped holdings in the StartMarketValue, EndMarketValue, CapGain and Carry columns to return one row per holding instead of two.

For example, aggregating and grouping the query in Example 1. The Sum() function parses each of the groups and returns the sum of all non-null values in the group. It then replaces the two values for each holding (one a non-null value, the other null) with the single value returned from Sum():

select
    LusidInstrumentId,
    Currency,
    Sum(StartMarketValue) as StartMarketValue,
    Sum(EndMarketValue) as EndMarketValue,
    Sum(CapGain) as CapGain,
    Sum(Carry) as Carry
from Lusid.Portfolio.AtoB
    where PortfolioScope = 'FBNUniversity'
    and PortfolioCode = 'Module-4-1'
    and Recipe = 'FBNUniversity/Module-4-1Recipe'
    and FromDate = #2022-03-07#
    and ToDate = #2022-03-11#
group by LusidInstrumentId
;

...and returns a table of data like this, with one row per holding instead of two:

Example 3: Show the instrument name and type for each holding

We can join Lusid.Portfolio.AtoB with other providers to retrieve and include additional useful pieces of data in the A2B report.

For example, joining our query from Example 2 with the Lusid.Instrument provider:   

select 
    a.LusidInstrumentId,
    i.DisplayName as InstrumentName,
    i.Type as InstrumentType,
    a.Currency,
    Sum(a.StartMarketValue) as StartMarketValue,
    Sum(a.EndMarketValue) as EndMarketValue,
    Sum(a.CapGain) as CapGain,
    Sum(a.Carry) as Carry
from
    Lusid.Portfolio.AtoB a
    inner join Lusid.Instrument i
        on a.LusidInstrumentId = i.LusidInstrumentId
where
    Recipe = 'FBNUniversity/Module-4-1Recipe'
    and PortfolioCode = 'Module-4-1'
    and PortfolioScope = 'FBNUniversity'
    and FromDate = #2022-03-07#
    and ToDate = #2022-03-11#
group by a.LusidInstrumentId
;

...returns a table of data like this, with an instrument name and type to provide more context for each LUID.