Lusid.Portfolio.Constituent.Writer

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

Providing you have sufficient access control permissions, the Lusid.Portfolio.Constituent.Writer provider enables you to write a Luminesce SQL query that loads weighted instrument constituents into a LUSID reference portfolio, replacing existing constituents.

Note: By default, Lusid.Portfolio.Constituent.Writer cannot add reference holding properties to constituents. To do this, you must first configure Lusid.Portfolio.Constituent.Writer to 'inline' properties. See how to do this.

You must construct a valid table of data to write, one constituent per record. Lusid.Portfolio.Constituent.Writer lists the fields (columns) available to populate with values for each record, and has parameters to help you construct a valid table. 

Note the following:

• All constituents must share the same WeightType. This determines whether initial weights are static or rather float according to real-time pricing information, thereby causing portfolio asset allocations to automatically change.
• All constituents must share the same EffectiveFrom datetime. For floating weights, this determines the prices of initial weights.
• Each constituent can have a different Weight representing the proportion of the index initially allocated to that asset. Note this need not be a percentage (that is, the weights of all constituents need not add up to 100), though floating weight calculations are easier to interpret if they do.

For more information on constituents, examining asset allocations over time, and rebalancing a reference portfolio, see this article.

See also: Lusid.Portfolio.Constituent

Basic usage

@table_of_data = <select-statement>;
select * from Lusid.Portfolio.Constituent.Writer where ToWrite = @table_of_data;

Query parameters

Lusid.Portfolio.Constituent.Writer has parameters to help you construct a valid table of data to write.

Note: The ToWrite parameter is mandatory and used to actually write data to LUSID.

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

Data fields

Lusid.Portfolio.Constituent.Writer lists the fields you can populate in your table of data to write.

Mandatory fields in table of data to write	Mandatory fields when WeightType is Periodical
PortfolioScope PortfolioCode EffectiveFrom (this must be the same for all constituents) WeightType (this must be the same for all constituents; valid values are Static, Floating, Periodical) Weight Currency At least one instrument identifier, for example 'BBG00WGHTKZ0' as Figi	PeriodType (valid values are Daily, Weekly, Monthly, Quarterly, Annually) PeriodCount (for more information, see this table)

To list all available fields, 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.Constituent.Writer' and FieldType = 'Column';

Write errors

We recommend examining the results of every write query using one or more of the WriteError, WriteErrorCode and WriteErrorDetail fields.

For each record in the table of data to write, Lusid.Portfolio.Constituent.Writer returns an error code. If the operation is successful, the error code is 0. If unsuccessful, a positive error code and explanation help you discover why LUSID considers the operation invalid.

For example, the query:

@table_of_data = select 'Finbourne-Examples' as PortfolioScope, 'UK-Benchmark' as PortfolioCode, 12 as Weight, 'Static' as WeightType, 'GBP' as Currency;
select WriteErrorCode, WriteError, WriteErrorDetail from Lusid.Portfolio.Constituent.Writer where ToWrite = @table_of_data;

...fails because there is no EffectiveFrom date.

Examples

Lusid.Portfolio.Constituent.Writer does not perform an upsert operation but rather replaces the entire contents of a reference portfolio each time. Any existing constituents not included in the new dataset are removed.

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

Example 1: Load equally-weighted static constituents into a reference portfolio

@vals = values
('LUID_00003DEY'),
('LUID_00003DEX');

@table_of_data = select 'Finbourne-Examples' as PortfolioScope, 'UK-Benchmark' as PortfolioCode, #2022-03-01# as EffectiveFrom,
'Static' as WeightType, 50 as Weight, 'GBP' as Currency, column1 as LusidInstrumentId from @vals;

select * from Lusid.Portfolio.Constituent.Writer where ToWrite = @table_of_data

Example 2: Load periodic floating constituents and automatically rebalance every 5 days

In this example, the reset period of Daily 5 causes LUSID to automatically rebalance the portfolio (that is, reset floating weights to the initial weights) every five days after the EffectiveFrom date of 1 March 2022, so for example on Sunday 6 March, Friday 11 March and so on:

@vals = values
('LUID_00003DEY', 30, 'GBP'),
('LUID_00003DEX', 70, 'USD');

@table_of_data = select 'Finbourne-Examples' as PortfolioScope, 'UK-Benchmark' as PortfolioCode, #2022-03-01# as EffectiveFrom, column3 as Currency,
'Periodical' as WeightType, 'Daily' as PeriodType, 5 as PeriodCount, column2 as Weight, column1 as LusidInstrumentId from @vals;

select * from Lusid.Portfolio.Constituent.Writer where ToWrite = @table_of_data