Lusid.Instrument.<Type>.Writer providers

Type

Read/write

Author

Availability

Data provider

Write

Finbourne

Provided 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> provider

Mandatory fields for all providers

Mandatory fields for this provider

If creating, do NOT populate...

Equity

Equity.Writer
(see an example)

DisplayName

At least one unique identifier, for example 'BBG00WGHTKZ0' as Figi

DomCcy

LusidInstrumentId


Type

Bond

Bond.Writer

DomCcy

Principal

MaturityDate
The mandatory flow convention fields:
FlowConventionsCurrency

FlowConventionsPaymentFrequency

FlowConventionsDayCountConvention

FlowConventionsRollConvention

FlowConventionsPaymentCalendars

FlowConventionsResetCalendars

Complex bond

ComplexBond.Writer
(see an example)

SchedulesJson

Inflation-linked bond

InflationLinkedBond.Writer

StartDate

MaturityDate
The mandatory flow convention fields:
FlowConventionsCurrency

FlowConventionsPaymentFrequency

FlowConventionsDayCountConvention

FlowConventionsRollConvention

FlowConventionsPaymentCalendars

FlowConventionsResetCalendars
The mandatory inflation index convention fields:
InflationIndexConventionInflationIndexName

InflationIndexConventionsCurrency

InflationIndexConventionsObservationLag

Amortising loan

SimpleCashFlowLoan.Writer
(see an example)

DomCcy

StartDate

MaturityDate
PeriodsJson

CapFloor

CapFloor.Writer

CapFloorTypeCapStrikeFloorStrike
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

CFD

ContractForDifference.Writer

Code

UnderlyingIdentifier

ContractSize

ContractForDifferenceType

StartDate

PayCcy

UnderlyingCcy

CDS

CreditDefaultSwap.Writer

DomCcy

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 option

EquityOption.Writer

DomCcy

Code

Strike

OptionMaturityDate

OptionSettlementDate

Equity swap

EquitySwap.Writer

Code

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 option

ExchangeTradedOption.Writer

All the ContractDetails* fields

Forward rate agreement

ForwardRateAgreement.Writer

DomCcy

Notional

StartDate

MaturityDate

FixingDate

FraRate

Funding leg

FundingLeg.Writer

StartDate

MaturityDate

LegDefinitionNotionalExchangeType

LegDefinitionPayReceive

LegDefinitionStubType
The mandatory flow convention fields:
LegDefinitionConventionsCurrency

LegDefinitionConventionsPaymentFrequency

LegDefinitionConventionsDayCountConvention

LegDefinitionConventionsRollConvention

LegDefinitionConventionsPaymentCalendars

LegDefinitionConventionsResetCalendars

Fund share class

FundShareClass.Writer
(see an example)

ShortCode
FundShareClassType
DistributionPaymentType
Hedging
DomCcy

Future

Future.Writer

All the ContractDetails* fields

FX forward

FxForward.Writer

DomCcy

FgnCcy

DomAmount

FgnAmount

MaturityDate

StartDate

FX option

FxOption.Writer

DomCcy

FgnCcy

Strike

OptionMaturityDate

OptionSettlementDate

FX swap

FxSwap.Writer

NearFxForwardStartDate

NearFxForwardMaturityDate

NearFxForwardDomCcy

NearFxForwardDomAmount

NearFxForwardFgnCcy

NearFxForwardFgnAmount

FarFxForwardStartDate

FarFxForwardMaturityDate

FarFxForwardDomCcy

FarFxForwardDomAmount

FarFxForwardFgnCcy

FarFxForwardFgnAmount

Interest rate swap

InterestRateSwap.Writer

StartDate

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 swaption

InterestRateSwaption.Writer

StartDate

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

Repo

Repo.Writer

StartDate

MaturityDate

DomCcy

AccrualBasis
One of PurchasePrice, Haircut, Margin
One of RepurchasePrice, RepoRate

Term deposit

TermDeposit.Writer

StartDate

MaturityDate

ContractSize
The mandatory flow convention fields:
FlowConventionCurrency

FlowConventionPaymentFrequency

FlowConventionDayCountConvention

FlowConventionRollConvention

FlowConventionPaymentCalendars

FlowConventionResetCalendars

Simple instrument (no full economic definition)

SimpleInstrument.Writer

DomCcy

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;