|Data provider||Write||Finbourne||Provided with LUSID|
Luminesce has a dedicated
Lusid.Instrument.<Type>.Writer provider per asset class that enables you to write a Luminesce SQL query to create, update or delete instruments of that type in LUSID. For example, the
Lusid.Instrument.Bond.Writer provider creates, updates or deletes bond instruments.
Note: The LUSID user running the query must have sufficient access control permissions to both use the provider and upsert instruments into LUSID. This should automatically be the case if you are the domain owner.
You must construct a valid table of data to write, one instrument per record. Each
Lusid.Instrument.<Type>.Writer provider lists the fields (columns) available to populate with values for each record, and has a set of parameters to help you construct a valid table.
Your query can use the
WriteAction field to perform one of the following operations on the table of data to write:
- Upsert an instrument; that is, update an existing instrument if a value for at least one unique identifier (such as a FIGI) already exists in LUSID, and create a new instrument if not. This is the default operation if you omit
WriteAction. Note if you are creating an instrument, make sure to omit a value for the
LusidInstrumentIdfield, since a unique LUID will be automatically created for you.
- Hard or soft delete an instrument. For more information on the difference, see this article.
See also: Lusid.Instrument.<Type> reader providers
@table_of_data = <select-statement>; select * from Lusid.Instrument.<Type>.Writer where toWrite = @table_of_data;
Lusid.Instrument.<Type>.Writer providers have the same set of parameters that help you construct a valid table of data to write.
toWrite parameter is mandatory and used to actually write the table of data into LUSID.
To list available parameters, their data types, default values, and an explanation for each, run the following query using a suitable tool (this example lists parameters for bonds, but they are the same for all providers):
select FieldName, DataType, ParamDefaultValue, Description from Sys.Field where TableName = 'Lusid.Instrument.Bond.Writer' and FieldType = 'Parameter';
Lusid.Instrument.<Type>.Writer provider lists the fields you can or must populate in your table of data to write:
|To create or update a...||Use the Lusid.Instrument.<Type> provider||Mandatory fields for all providers||Mandatory fields for this provider||If creating, do NOT populate...|
At least one unique identifier, for example
|Exchange-traded option||All the |
|Future||All the |
|Simple instrument (no full economic definition)|
To list all available fields for a particular provider, their data types, whether fields are considered 'main', and an explanation for each, run the following query using a suitable tool (this example lists fields for bonds):
select FieldName, DataType, IsMain, IsPrimaryKey, SampleValues, Description from Sys.Field where TableName = 'Lusid.Instrument.Bond.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, a
Lusid.Instrument.<Type>.Writer provider returns an error code. If an 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 'AstraZeneca' as DisplayName; select WriteErrorCode, WriteError, WriteErrorDetail from Lusid.Instrument.Equity.Writer where toWrite = @table_of_data;
...fails because an equity instrument must have at least one unique identifier and a domestic currency (each of these errors is revealed in turn).
In the following examples, a single instrument is created or updated for purposes of clarity, but the
Lusid.Instrument.<Type>.Writer providers are designed to handle writing potentially unlimited numbers of instruments simultaneously.
Note: For more example Luminesce SQL queries, visit our Github repo.
Example 1: Create a new equity instrument
In this example, LUSID creates a new instrument representing an equity providing a FIGI of
BBG00WGHTKZ0 does not already exist in the instrument master.
LusidInstrumentId field is useful to reveal the unique LUID automatically generated for the new instrument.
@table_of_data = select 'AstraZeneca' as DisplayName, 'BBG00WGHTKZ0' as Figi, 'GBP' as DomCcy; select LusidInstrumentId, Figi, DisplayName, WriteErrorCode from Lusid.Instrument.Equity.Writer where toWrite = @table_of_data;
Example 2: Update an existing equity instrument
Following on from Example 1, since a FIGI of
BBG00WGHTKZ0 now exists, the existing instrument is updated with a new display name.
LusidInstrumentId field again reveals the instrument has the same LUID, confirming no new instrument has been created.
@table_of_data = select 'AstraZeneca PLC' as DisplayName, 'BBG00WGHTKZ0' as Figi, 'GBP' as DomCcy; select LusidInstrumentId, Figi, DisplayName, WriteErrorCode from Lusid.Instrument.Equity.Writer where toWrite = @table_of_data;
Example 3: Delete an instrument in a particular scope
To delete an instrument of any type, specify its LUID and either
'HardDelete' as WriteAction or
'SoftDelete' as WriteAction, for example:
@table_of_data = select 'LUID_00003DFA' as LusidInstrumentId, 'my-custom-instr-scope' as Scope, 'HardDelete' as WriteAction; select * from Lusid.Instrument.Equity.Writer where toWrite = @table_of_data;
Example 4: Create a term deposit instrument with a bespoke flow convention
For more information on flow conventions, see this article.
@table_of_data = select 'DepositAccount' as DisplayName, 'DepositAccABC' as ClientInternal, #2022-01-01# as StartDate, #2032-01-01# as MaturityDate, 1 as ContractSize, 'GBP' as FlowConventionCurrency, '3M' as FlowConventionPaymentFrequency, 'Actual360' as FlowConventionDayCountConvention, 'P' as FlowConventionRollConvention, 'GBP,USD' as FlowConventionPaymentCalendars, '' as FlowConventionResetCalendars ; select * from Lusid.Instrument.TermDeposit.Writer where toWrite = @table_of_data;
Example 5: Create an amortising loan instrument with an array of JSON objects
PeriodsJson field is a text field but requires a flattened array of JSON objects as a value, each representing a
LoanPeriod object with
InterestAmount fields. See an example.