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.

Identifying Legs Using Primary Asset ID

These are the processing security types of different swap legs:

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.

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

Identifying 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:

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.

Linking 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

Linking 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

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

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