Lusid.Instrument.<Type>.Writer providers

Views:

Related resources:

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.

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`		`CapFloorType` `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`
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 WriteError, WriteErrorCode 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;

Keywords: luminesce, provider, upsert, instrument