Link Derivatives to Underlyings Best Practices

Owned by Jawann Swislow
Last updated: Aug 03, 2023 by Jawann Swislow
4 min read

Overview

Eagle’s data model supports linking derivatives to one (or more) underlying security. This document describes how to link derivatives to their underlying, link swap legs together, link legs to their underlying(s), and identify all in Data Management. It contains best practices for both Suite and non-Accounting clients. It is focused on the specific details of storing and retrieving derivative data, and is intended for a technical audience. The first several sections are specific to linking swap legs, while the last section contains information about Option contract underlying.

Identify Contract Using Primary Asset ID

These are the processing security types of different swap contacts:

  • SWCOCR: Cross Currency Swap Contract

  • SWCOIR: Interest Rate Swap Contract

  • SWCOTR: Total Return Swap Contract

Processing security type is stored in securitydbo.security_master.process_sec_type field of the contract security.

Sample Query

Primary Asset ID of Interest Rate Swap: 99SWAP2AC

select security_alias from securitydbo.security_master where primary_asset_id = '99SWAP2AC' and process_sec_type = 'SWCOIR'

This query will return the security alias of the interest rate swap contract.

Identify Legs Using Primary Asset ID

These are the processing security types of different swap legs:

Processing Security Type

Swap Contract type

Can Be Attached To

SWLEAC

Swap Leg Interest Accrual

Interest Rate, Currency, Total Return Swap Contract

SWLXEQ

Swap Leg Total Rate Return on Equity

Total Return Swap Contract

SWLEDB

Swap Leg Total Rate Return on Fixed Income

Total Return Swap Contract

Swap legs and the contract share the same primary asset id. One way to identify all the three is to query by security_master.primary_asset_id. This will bring back the contract and all the legs attached to the contract.

Sample Query

Primary asset id of Interest Rate Swap: 99SWAP2AC

select security_alias from securitydbo.security_master where primary_asset_id = '99SWAP2AC'

This query will return the security aliases of the interest rate swap pay and receive legs.

Link Legs of a Swap to the Contract

The swap contract is also linked to its legs in the securitydbo.underlying_security table. This is another way to identify all the legs of a swap.

Sample Query

Security_alias of Swap contract: 374527

select underlying_sec_alias from  securitydbo.underlying_security where security_alias = 374527

This query will return all the legs of the interest rate swap contract.

Identify Pay/Receive Information

Legs of the swaps can either be Pay or Receive. This data is stored in the securitydbo.swaps.pay_receive_ind column. Possible data in pay_receive_ind column:

Data

Represents

C

Contract

P

Pay

R

Receive

Sample Query

Primary asset id of Interest Rate Swap: 99SWAP2AC

select sm.security_alias, issue_name, pay_receive_ind from securitydbo.security_master sm, securitydbo.swaps s where primary_asset_id = '99SWAP2AC' and process_sec_type = 'SWLEAC' and sm.security_alias = s.security_alias

This query will return the security_alias, issue_name and pay receive indicator of the accrual legs of interest rate swap.

Link TRS Return Leg to Underlying

The return leg of a total return swap is linked its underlying Equity/Index/Basket security in securitydbo.derivatives table. The column representing the security alias of the underlying is securitydbo.derivatives.underlying_sec_alias

Sample Query

Security_alias of Return leg of TRS contract: 374527

select underlying_security, underlying_sec_alias from securitydbo.derivatives where security_alias = 374527

This query will return the issue name and security alias of the underlying equity linked to the return leg

Link Swap Leg to Underlying

In V10.X releases, Eagle uses the securitydbo.derivatives table to link the underlying reference entity or asset to a swap leg. An enhancement was made in V11.0 and above to store this relationship in the securitydbo.underlying_security table in addition to the derivatives table.

  • It is recommended that clients with custom processes to load swap data use the underlying_security table (in addition to the derivatives table) to store this relationship. For example, consider this total return swap with a custom index on the following page:

Storing TRS Index in Eagle Warehouse

Security Alias

Underlying Security Alias

Comments

100

101

Links TRS Contract to Finance Leg

100

102

Links TRS Contract to Return Leg

101

103

Links Finance Leg to Reference Rate Security (3M LIBOR)

102

104

Links Return leg to Underlying Portfolio

104

105

Links Equity Basket to constituents (see comments below)

104

106

Links Equity Basket to constituents (see comments below)

104

107

Links Equity Basket to constituents (see comments below)

104

108

Links Equity Basket to constituents (see comments below)

104

109

Links Equity Basket to constituents (see comments below)

Using underlying_security table to link the constituents is a simple way to do it IF:

  • No need to track the weight of each constituent in the Index

  • This is a custom basket and not a market Index like S&P 500

Otherwise, IF:

  • Underlying basket is a market Index like S&P 500

  • There is a need to track weight and performance of the index

THEN:

  • Create an entity to represent the basket/index/portfolio with Entity Type (entity_type) = Portfolio

  • Load the daily positions, market value, weight, etc. into the position/position details table for this entity; there will be one set of position for this entity each day

  • For the swap leg security (alias 104 in above example), set Look Thru Value (look_thru_value) = Entity ID of underlying basket/index/portfolio and Look Thru Indicator (look_thur_ind) = P (Portfolio)

  • This relationship can be used to retrieve the custom index constituents

Link Option/Future to Its Underlying

In V10.X releases, Eagle uses the securitydbo.derivatives table to link option contracts to their underlying securities. An enhancement was made in V11.0 and above to store this relationship in the securitydbo.underlying_security table in addition to the derivatives table. This provides the flexibility to store multiple underlyings for a single security, as occurs with some Option contracts when the underlying stock goes through a spin off. In V10.1 and above, the underlying_security table also has a start_date (part of primary key) column that provides the ability to track changes in underlyings over time.

For clients who do not use Eagle Accounting and have a custom process to load data, the best practice is to use the securitydbo.underlying_security table to link an option contracts to its underlying(s). Security Reference Manager (SRM) has an underlying security module that will allow users store data in the underlying_security table.

Primary Key in underlying_security Table

  • Pre V10.1

    • security_alias: security alias of the Option contract.

    • underlying_sec_alias: security alias of the underlying asset.

  • V10.1 and Later

    • security_alias: security alias of the Option contract.

    • underlying_sec_alias: security alias of the underlying asset.

    • start_date: issue date during initial setup, or the new effective date if the underlying asset changes.

If there are multiple underlying assets, as in the case of a spin off, there will be more than one row in underlying_security table.