Views:
TypeRead/writeAuthorAvailability
Data providerWriteFinbourneProvided with LUSID

Luminesce has a dedicated Lusid.Instrument.*.Writer provider per asset class that enables you to write a Luminesce SQL query to master instruments in LUSID. For example, the Lusid.Instrument.Bond.Writer provider creates or updates 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.*.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.

If a record in your table contains a value for at least one unique identifier (such as a FIGI) that already exists in LUSID, then an existing instrument is updated. If it doesn't, then a new instrument is created. Note if you are creating an instrument, make sure to omit a value for the LusidInstrumentId field, since a unique LUID will be automatically created for you.

See also: Lusid.Instrument.* reader providers

Basic usage

@table_of_data = <select-statement>;
select * from Lusid.Instrument.*.Writer where toWrite = @table_of_data;

Query parameters

All Lusid.Instrument.*.Writer providers have the same set of parameters that help you construct a valid table of data to write.

Note: The 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';

Data fields

Each Lusid.Instrument.*.Writer provider lists the fields you can or must populate in your table of data to write:

To create or update an...Use the Lusid.Instrument.* providerMandatory fields for all providersMandatory fields for this providerIf creating, do not populate...
EquityEquity.WriterDisplayName
At least one unique identifier, for example 'BBG00WGHTKZ0' as Figi
DomCcyLusidInstrumentId
Type
BondBond.WriterDomCcy
All the FlowConventions* fields
Principal
MaturityDate
FutureFuture.WriterAll the ContractDetails* fields
FxForwardFxForward.WriterDomCcy
FgnCcy
DomAmount
FgnAmount
MaturityDate
FX optionFxOption.WriterDomCcy
FgnCcy
Strike
OptionMaturityDate
OptionSettlementDate
Equity optionEquityOption.WriterDomCcy
Code
Strike
OptionMaturityDate
OptionSettlementDate
Exchange-traded optionExchangeTradedOption.WriterAll the ContractDetails* fields
Funding legFundingLeg.WriterStartDate
MaturityDate

All the LegDefinition* fields
Simple instrumentSimpleInstrument.WriterDomCcy
AssetClass
SimpleInstrumentType

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';

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, a Lusid.Instrument.*.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 a simple instrument (such as an equity) must have at least one unique identifier and a domestic currency (each of these errors is revealed in turn).

Examples

In the following examples, a single instrument is created or updated for purposes of clarity, but each Lusid.Instrument.*.Writer provider is 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.

Returning the 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.

Returning the 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;