Views:
TypeRead/writeAuthorAvailability
Data providerWriteFinbourneProvided with LUSID

Providing you have sufficient access control permissions, you can use a Lusid.Instrument.<Type>.Writer provider to write a Luminesce SQL query that either creates, updates or deletes instruments of that type (that is, asset class) in LUSID. For example, the Lusid.Instrument.Bond.Writer provider creates, updates or deletes bond instruments.

Note: By default, a Lusid.Instrument.<Type>.Writer provider cannot add properties to instruments of that type. To do this, you must first configure the provider to 'inline' properties. See how to do this.

You must construct a valid table of data to write, one instrument per record. A 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 LusidInstrumentId field, 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

Basic usage

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

Query parameters

All Lusid.Instrument.<Type>.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.<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> providerMandatory fields for all providersMandatory fields for this providerIf creating, do NOT populate...
EquityEquity.Writer
(see an example)
DisplayName

At least one unique identifier, for example 'BBG00WGHTKZ0' as Figi
DomCcyLusidInstrumentId
Type
BondBond.WriterDomCcy
Principal
MaturityDate

The mandatory flow convention fields:
FlowConventionsCurrency
FlowConventionsPaymentFrequency
FlowConventionsDayCountConvention
FlowConventionsRollConvention
FlowConventionsPaymentCalendars
FlowConventionsResetCalendars
Complex bondComplexBond.Writer
(see an example)
SchedulesJson
Inflation-linked bondInflationLinkedBond.WriterStartDate
MaturityDate

The mandatory flow convention fields:
FlowConventionsCurrency
FlowConventionsPaymentFrequency
FlowConventionsDayCountConvention
FlowConventionsRollConvention
FlowConventionsPaymentCalendars
FlowConventionsResetCalendars

The mandatory inflation index convention fields:
InflationIndexConventionInflationIndexName
InflationIndexConventionsCurrency
InflationIndexConventionsObservationLag
Amortising loanSimpleCashFlowLoan.Writer
(see an example)
DomCcy
StartDate
MaturityDate

PeriodsJson
CapFloorCapFloor.WriterCapFloorType
CapStrike
FloorStrike
UnderlyingFloatingLegLegDefinitionNotionalExchangeType UnderlyingFloatingLegLegDefinitionPayReceive UnderlyingFloatingLegLegDefinitionStubType UnderlyingFloatingLegStartDate
UnderlyingFloatingLegMaturityDate
UnderlyingFloatingLegNotional
The mandatory flow convention fields:
UnderlyingFloatingLegLegDefinitionConventionsCurrency
UnderlyingFloatingLegLegDefinitionConventionsPaymentFrequency
UnderlyingFloatingLegLegDefinitionConventionsDayCountConvention
UnderlyingFloatingLegLegDefinitionConventionsRollConvention
UnderlyingFloatingLegLegDefinitionConventionsPaymentCalendars
UnderlyingFloatingLegLegDefinitionConventionsResetCalendars

The mandatory index convention fields: UnderlyingFloatingLegLegDefinitionIndexConventionFixingReference
UnderlyingFloatingLegLegDefinitionIndexConventionPublicationDayLag
UnderlyingFloatingLegLegDefinitionIndexConventionPaymentTenor
UnderlyingFloatingLegLegDefinitionIndexConventionDayCountConvention
UnderlyingFloatingLegLegDefinitionIndexConventionCurrency
CFDContractForDifference.WriterCode
UnderlyingIdentifier
ContractSize
ContractForDifferenceType
StartDate
PayCcy
UnderlyingCcy
CDSCreditDefaultSwap.WriterDomCcy
CreditDefaultSwapTicker
CouponRate
StartDate
MaturityDate
ProtectionDetailSpecificationRestructuringType
ProtectionDetailSpecificationSeniority

The mandatory CDS flow convention fields:
FlowConventionsCurrency
FlowConventionsPaymentFrequency
(must be 3M or 6M)
FlowConventionsDayCountConvention
FlowConventionsRollConvention
FlowConventionsPaymentCalendars
FlowConventionsResetCalendars
FlowConventionsSettleDays
FlowConventionsResetDay
Equity optionEquityOption.WriterDomCcy
Code
Strike
OptionMaturityDate
OptionSettlementDate
Equity swapEquitySwap.WriterCode
UnderlyingIdentifier
StartDate
MaturityDate
InitialPrice
Quantity
FundingLegLegDefinitionNotionalExchangeType
FundingLegLegDefinitionPayReceive
FundingLegLegDefinitionStubType
FundingLegStartDate
FundingLegMaturityDate
FundingLegNotional

The mandatory flow convention fields for the cashflow leg:
FundingLegLegDefinitionConventionsCurrency
FundingLegLegDefinitionConventionsPaymentFrequency
FundingLegLegDefinitionConventionsDayCountConvention
FundingLegLegDefinitionConventionsRollConvention
FundingLegLegDefinitionConventionsPaymentCalendars
FundingLegLegDefinitionConventionsResetCalendars

The mandatory flow convention fields for the equity leg:
EquityFlowConventionsCurrency
EquityFlowConventionsPaymentFrequency
EquityFlowConventionsDayCountConvention
EquityFlowConventionsRollConvention
EquityFlowConventionsPaymentCalendars
EquityFlowConventionsResetCalendars
Exchange-traded optionExchangeTradedOption.WriterAll the ContractDetails* fields
Forward rate agreementForwardRateAgreement.WriterDomCcy
Notional
StartDate
MaturityDate
FixingDate
FraRate
Funding legFundingLeg.WriterStartDate
MaturityDate
LegDefinitionNotionalExchangeType
LegDefinitionPayReceive
LegDefinitionStubType

The mandatory flow convention fields:
LegDefinitionConventionsCurrency
LegDefinitionConventionsPaymentFrequency
LegDefinitionConventionsDayCountConvention
LegDefinitionConventionsRollConvention
LegDefinitionConventionsPaymentCalendars
LegDefinitionConventionsResetCalendars
Fund share classFundShareClass.Writer
(see an example)
ShortCode
FundShareClassType
DistributionPaymentType
Hedging
DomCcy
FutureFuture.WriterAll the ContractDetails* fields
FX forwardFxForward.WriterDomCcy
FgnCcy
DomAmount
FgnAmount
MaturityDate
StartDate
FX optionFxOption.WriterDomCcy
FgnCcy
Strike
OptionMaturityDate
OptionSettlementDate
FX swapFxSwap.WriterNearFxForwardStartDate
NearFxForwardMaturityDate
NearFxForwardDomCcy
NearFxForwardDomAmount
NearFxForwardFgnCcy
NearFxForwardFgnAmount
FarFxForwardStartDate
FarFxForwardMaturityDate
FarFxForwardDomCcy
FarFxForwardDomAmount
FarFxForwardFgnCcy
FarFxForwardFgnAmount
Interest rate swapInterestRateSwap.WriterStartDate
MaturityDate

First leg:
Legs1LegsDefinitionNotionalExchangeType
Legs1LegDefinitionPayReceive
Legs1LegDefinitionStubType
Legs1StartDate
Legs1MaturityDate
Legs1Notional

The mandatory flow convention fields for the first leg:
Legs1LegDefinitionConventionsCurrency
Legs1LegDefinitionConventionsPaymentFrequency
Legs1LegDefinitionConventionsDayCountConvention
Legs1LegDefinitionConventionsRollConvention
Legs1LegDefinitionConventionsPaymentCalendars
Legs1LegDefinitionConventionsResetCalendars

Second leg:
Legs2LegsDefinitionNotionalExchangeType
Legs2LegDefinitionPayReceive
Legs2LegDefinitionStubType
Legs2StartDate
Legs2MaturityDate
Legs2Notional

The mandatory flow convention fields for the second leg:
Legs2LegDefinitionConventionsCurrency
Legs2LegDefinitionConventionsPaymentFrequency
Legs2LegDefinitionConventionsDayCountConvention
Legs2LegDefinitionConventionsRollConvention
Legs2LegDefinitionConventionsPaymentCalendars
Legs2LegDefinitionConventionsResetCalendars
Interest rate swaptionInterestRateSwaption.WriterStartDate
SwapStartDate
SwapMaturityDate
DeliveryMethod
PayOrReceiveFixed
(either Pay or Receive)
First leg (must be fixed leg if Pay, floating leg if Receive):
SwapLegs1InstrumentLegType (either FixedLeg or FloatingLeg)
SwapLegs1LegsDefinitionNotionalExchangeType
SwapLegs1LegDefinitionPayReceive
SwapLegs1LegDefinitionStubType
SwapLegs1StartDate
SwapLegs1MaturityDate
SwapLegs1Notional

The mandatory flow convention fields for the first leg:
SwapLegs1LegDefinitionConventionsCurrency
SwapLegs1LegDefinitionConventionsPaymentFrequency
SwapLegs1LegDefinitionConventionsDayCountConvention
SwapLegs1LegDefinitionConventionsRollConvention
SwapLegs1LegDefinitionConventionsPaymentCalendars
SwapLegs1LegDefinitionConventionsResetCalendars

Second leg (must be floating leg if Pay, fixed leg if Receive):
SwapLegs2InstrumentLegType (either FixedLeg or FloatingLeg)
SwapLegs2LegsDefinitionNotionalExchangeType
SwapLegs2LegDefinitionPayReceive
SwapLegs2LegDefinitionStubType
SwapLegs2StartDate
SwapLegs2MaturityDate
SwapLegs2Notional

The mandatory flow convention fields for the second leg:
SwapLegs2LegDefinitionConventionsCurrency
SwapLegs2LegDefinitionConventionsPaymentFrequency
SwapLegs2LegDefinitionConventionsDayCountConvention
SwapLegs2LegDefinitionConventionsRollConvention
SwapLegs2LegDefinitionConventionsPaymentCalendars
SwapLegs2LegDefinitionConventionsResetCalendars

The mandatory index convention fields for whichever is the floating leg:
SwapLegs<1-or-2>LegDefinitionIndexConventionFixingReference
SwapLegs<1-or-2>LegDefinitionIndexConventionPaymentTenor
SwapLegs<1-or-2>LegDefinitionIndexConventionDayCountConvention
SwapLegs<1-or-2>LegDefinitionIndexConventionCurrency
RepoRepo.WriterStartDate
MaturityDate
DomCcy
AccrualBasis

One of PurchasePrice, Haircut, Margin
One of RepurchasePrice, RepoRate
Term depositTermDeposit.WriterStartDate
MaturityDate
ContractSize

The mandatory flow convention fields:
FlowConventionCurrency
FlowConventionPaymentFrequency
FlowConventionDayCountConvention
FlowConventionRollConvention
FlowConventionPaymentCalendars
FlowConventionResetCalendars
Simple instrument (no full economic definition)SimpleInstrument.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 WriteErrorWriteErrorCode and WriteErrorDetail fields.

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).

Examples

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.

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;

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 a fund share class

The Lusid.Instrument.FundShareClass.Writer provider creates a share class as an instrument in the LUSID Security Master. Note that if you omit the Scope field, the instrument is domiciled in the default (system) instrument scope.

@table_of_data = select 'ShareClassA' as DisplayName, 'ShareClassA-Id' as ClientInternal, 'Share class A' as ShortCode,
'Income' as FundShareClassType, 'Net' as DistributionPaymentType, 'None' as Hedging, 'GBP' as DomCcy, 
'MyShareClassCustomInstrumentScope' as Scope;

select * from Lusid.Instrument.FundShareClass.Writer where toWrite = @table_of_data;

Providing the query is successful, the result reveals the LUID of the mastered instrument so you can add it to a fund.

Example 6: Create an amortising loan instrument using the PeriodsJson field

The Lusid.Instrument.SimpleCashFlowLoan.Writer provider has a mandatory PeriodsJson field that expects an array of JSON objects as a value, each representing a loan period with PaymentDate, Notional and InterestAmount fields.

To specify a single loan period, it might be simplest to encapsulate raw JSON in '[ and ]' characters, for example:

@table_of_data = select 'MySimpleCashFlowLoan' as DisplayName, 'SimpleCashFlowLoan' as ClientInternal,
#2022-01-01# as StartDate, #2023-01-01# as MaturityDate, 'GBP' as DomCcy, 
'[
  {
    "PaymentDate": "2022-03-01",
    "Notional": 100,
    "InterestAmount": 5
  }
]' as PeriodsJson;

select * from Lusid.Instrument.SimpleCashFlowLoan.Writer where ToWrite = @table_of_data;

To specify multiple loan periods, it might be more efficient to use the SQLite json_group_array and json_object functions to convert a table of data objects into JSON, for example:

@loanPeriods = values
(#2022-03-01#, 100, 5),
(#2022-06-01#, 90, 4),
(#2022-09-01#, 50, 4.5),
(#2022-12-01#, 30, 2.0);

@@loanPeriodsArray = select
    json_group_array(
        json_object(
            'PaymentDate', column1,
            'Notional', column2,
            'InterestAmount', column3
        )
    ) from @loanPeriods;

@table_of_data = select 'MySimpleCashFlowLoan' as DisplayName, 'SimpleCashFlowLoan' as ClientInternal,
#2022-01-01# as StartDate, #2023-01-01# as MaturityDate, 'GBP' as DomCcy, @@loanPeriodsArray as PeriodsJson;

select * from Lusid.Instrument.SimpleCashFlowLoan.Writer where ToWrite = @table_of_data;

Example 7: Create a complex bond instrument using the SchedulesJson field

The Lusid.Instrument.ComplexBond.Writer provider has a mandatory SchedulesJson field that expects an array of JSON objects as a value, each representing a schedule of a type such as fixed or float.

To specify a single schedule, it might be simplest to encapsulate raw JSON in '[ and ]' characters, for example:

@table_of_data = select 'MyComplexBond' as DisplayName, 'MyComplexBond' as ClientInternal,
'[
  {
    "ScheduleType": "FixedSchedule",
    "StartDate": "2021-09-15T00:00:00+00:00",
    "MaturityDate": "2028-09-15T00:00:00+00:00",
    "Notional": 1000000.0,
    "PaymentCurrency": "GBP",
    "FlowConventions": {
      "Currency": "GBP",
      "PaymentFrequency": "12M",
      "DayCountConvention": "ActActIsma",
      "RollConvention": "None",
      "PaymentCalendars": ["GBP"],
      "ResetCalendars": ["GBP"]
    }
  }
]' as SchedulesJson;

select * from Lusid.Instrument.ComplexBond.Writer where toWrite = @table_of_data;

To specify multiple schedules (or multiple bonds), it might be more efficient to use the SQLite json_group_array and json_object functions to convert a table of data objects into JSON, for example:

@schedules = values
('FixedSchedule', #2021-09-15#, #2028-09-15#, 'GBP', 1000000.0, 0.015, 'None'),
('FloatSchedule', #2028-09-15#, #2030-03-01#, 'GBP', 1000000.0, 0.0, 'ShortFront');

@@schedulesArray = select
    json_group_array(
        json_object(
            'ScheduleType', column1,
            'StartDate', column2,
            'MaturityDate', column3,
            'PaymentCurrency', column4,
            'Notional', column5,
            'CouponRate', column6,
            'StubType', column7,
            'FlowConventions', json_object(
                'Currency', 'GBP',
                'PaymentFrequency', '12M',
                'DayCountConvention', 'ActActIsma',
                'RollConvention', 'Following',
                'PaymentCalendars', json_array('GBP'),
                'ResetCalendars', json_array('GBP')
            ),
            'IndexConventions', json_object(
                'FixingReference', 'GBP1YBMK',
                'PublicationDayLag', 0,
                'PaymentTenor', '12M',
                'DayCountConvention', 'ActActIsma',
                'Currency', 'GBP'
            )
        )
    )
    from @schedules;

@table_of_data = select 'MyComplexBond' as DisplayName, 'MyComplexBond' as ClientInternal, @@schedulesArray as SchedulesJson;

select * from Lusid.Instrument.ComplexBond.Writer where toWrite = @table_of_data;