Providing you have sufficient access control permissions, the Lusid.PortfolioGroup provider enables you to write a Luminesce SQL query that searches for and retrieves one or more portfolio groups, including portfolios in nested groups (that is, in sub-groups). In conjunction with other providers, you can list transactions, generate holdings and perform valuations for the portfolio group as a whole.

Note: By default, Lusid.PortfolioGroup cannot retrieve properties. To do this, you must first configure Lusid.PortfolioGroup to 'inline' properties. See how to do this.

Lusid.PortfolioGroup considers the portfolio group you search for to be the 'top group'. Since a portfolio group can contain portfolio groups nested up to five levels deep, Lusid.PortfolioGroup considers the lowest group found in the hierarchy to be the 'bottom group'. Any portfolio groups in the middle are considered to be part of the 'group path'.

See also: Lusid.PortfolioGroup.Writer

Basic usage

select * from Lusid.PortfolioGroup where TopGroupScope = <scope> and TopGroupCode = <code>;

Query parameters

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

Note: By default, Lusid.PortfolioGroup returns all nested portfolio groups providing they are not empty (that is, do not directly contain a portfolio). You can change both these settings; see below for examples.

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

Data fields

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

Note: You must specify the TopGroupScope and TopGroupCode fields as part of your query.

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

Note: Fields marked 'main' are returned by queries that select a caret character, for example select ^ from Lusid.PortfolioGroup.

Examples

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

The examples in this section refer to the following portfolio group structure. For simplicity, all the portfolio groups and portfolios are in the same Groups-Example scope; the label in each box represents the code:

Example 1: Retrieve all the portfolios, including nested

Because the Expand parameter is set to True by default, searching the top Global portfolio group returns all the portfolios in the nested groups.

Equally, because the IncludeEmptySubgroups parameter is set to False by default, the NorthAmerica and CAN portfolio groups are omitted from the results. Although NorthAmerica contains sub-groups, it does not directly contain a portfolio itself.

Note the UK-FR-1 portfolio is returned twice, because it is a member of two groups.

select TopGroupCode, BottomGroupCode, GroupCodePath, PortfolioCode
from Lusid.PortfolioGroup
where TopGroupScope = 'Groups-Example' and TopGroupCode = 'Global'

This query returns:

Example 2: Retrieve just non-nested portfolios

Setting Expand to False returns the only portfolio (EU-1) that is directly contained when searching the NorthAmerica and Europe groups.

select TopGroupCode, BottomGroupCode, PortfolioCode
from Lusid.PortfolioGroup
where TopGroupScope = 'Groups-Example' and TopGroupCode in ('NorthAmerica', 'Europe')
and Expand = False

This query returns:

Example 3: Retrieve portfolio groups even if they are empty

Setting IncludeEmptySubgroups to True returns the empty CAN portfolio group when searching the NorthAmerica group.

select TopGroupCode, BottomGroupCode, PortfolioCode
from Lusid.PortfolioGroup
where TopGroupScope = 'Groups-Example' and TopGroupCode = 'NorthAmerica'
and IncludeEmptySubgroups = True

This query returns:

Example 4: Calculate holdings for all portfolios

Holdings can be calculated in conjunction with the Lusid.Portfolio.Holding provider by joining on the portfolio scope and code.

select grp.GroupCodePath, hold.*
from Lusid.PortfolioGroup grp
left join Lusid.Portfolio.Holding hold
    on grp.PortfolioScope = hold.PortfolioScope
    and grp.PortfolioCode = hold.PortfolioCode
where grp.TopGroupScope = 'Groups-Example' and grp.TopGroupCode = 'Global'