Lusid.PortfolioGroup · Customer Self-Service

Views:

Related resources:

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

The Lusid.PortfolioGroup provider enables you to write a Luminesce SQL query that finds some or all of the portfolios in a particular portfolio group, including those in nested groups (that is, in sub-groups). In conjunction with other providers, you can see transactions, or calculate holdings or valuations, for the portfolio group as a whole.

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

Lusid.PortfolioGroup considers the portfolio group you search for to be the 'top group'. Since a portfolio group can contain up to five levels of nested portfolio groups, 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.Portfolio, 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 contain at least one 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 start select ^ from Lusid.PortfolioGroup...

Examples

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

These examples 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, querying 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 CAN portfolio group is omitted from the results.

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:

TopGroupCode	BottomGroupCode	GroupCodePath	PortfolioCode
Global	US	Global/US	US-1
Global	US	Global/US	US-2
Global	Europe	Global/Europe	EU-1
Global	UK	Global/Europe/UK	UK-1
Global	UK	Global/Europe/UK	UK-FR-1
Global	FR	Global/Europe/FR	UK-FR-1
Global	FR	Global/Europe/FR	FR-1

Example 2: Retrieve just non-nested portfolios

Setting Expand to False returns only those portfolios directly contained in the specified groups.

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

This query returns:

TopGroupCode	BottomGroupCode	PortfolioCode
Europe		EU-1

Example 3: Retrieve portfolio groups even if they are empty

Setting IncludeEmptySubgroups to True includes the empty CAN portfolio group.

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

This query returns:

TopGroupCode	BottomGroupCode	PortfolioCode
North America	US	US-1
North America	US	US-2
North America	CAN

Example 4: Calculate holdings for all the 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'

Keywords: luminesce, provider, portfolio group