|Data provider||Write||Finbourne||Provided with LUSID|
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: The LUSID user running the query must have sufficient access control permissions to both use the provider and administer reference portfolio data in LUSID. This should automatically be the case if you are the domain owner.
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
EffectiveFromdatetime. For floating weights, this determines the prices of initial weights.
- Each constituent can have a different
Weightrepresenting 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
@table_of_data = <select-statement>; select * from Lusid.Portfolio.Constituent.Writer where ToWrite = @table_of_data;
Lusid.Portfolio.Constituent.Writer has parameters to help you construct a valid table of data to write.
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';
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|
At least one instrument identifier, for example
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';
We recommend examining the results of every write query using one or more of the
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
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