☰
Navigation :

TRADE_SENSITIVITIES

The TRADE_SENSITIVITIES table contains some of the attributes of the Sensitivity data. The ladder vectors can be found in the TRADE_SENSITIVITIES_VECTOR table.

Column Name Type Not Null Default Value1 Cube Field Description
AS_OF_DATE DATE Y Timestamp (at close of business) for the data.
TRADE_KEY STRING Y ‘N/A’ The field contains the tradeID for full data or Book#VaR Inclusion for summary data.
TRADE_ID STRING Y ‘DATAMEMBER’ [Booking].[Trades] If TRADE_ID comes from multiple systems, you may need to prepend source system to the ID for uniqueness.

note

In certain cases, the TRADE_ID could be for adjustment purposes. In such cases we might only have one PnL vector per Book or desk.

The TRADE_ID should contain this information clearly (ADDON or ADJ). Example: “IR_IRSWAP_LIBOR3M”, “EQ_12345677”, etc.
SENSITIVITY_NAME STRING Y ‘N/A’ [Sensitivities].[Sensitivity] The name of the sensitivity (cube measure)..
RISK_CLASS STRING Y ‘N/A’ [Risk].[Risk Classes] Risk factor’s asset class: “Interest rate”, “Credit spread”, “Foreign exchange”, “Equity”, “Commodity”, “Hybrid”.
RISK_FACTOR_ID STRING Y ‘N/A’ [Risk].[Risk Factors] Internal risk factor/bucket identifier: instrument, curve, vol surface/cube identifier.
RISK_FACTOR_ID2 STRING Y ‘N/A’ [Risk].[Risk Factors Secondary]

note

This field is only present in the Vanna input file. It does not exist for Delta, Gamma, Vega, or Volga inputs.

Second risk factor for the Vanna sensitivity.
Example: UniCredit_Spot price
TENOR_LABELS STRING Y ‘N/A’ [Risk].[Tenors] The list of tenor labels, corresponding to the vertex of the risk factor, such as 3M, 5Y, and so on.
TENOR_DATES STRING Y ‘N/A’ A list of explicit tenor dates, which are used to sort tenors and to re-bucket sensitivities (if supported).
Example: 2019-03-16; 2019-04-27; 2019-10-27; 2020-10-27
MATURITY_LABELS STRING Y ‘N/A’ [Risk].[Maturities] Name for the bucketed group.
MATURITY_DATES STRING Y ‘N/A’ A list of explicit maturity dates, which are used to sort tenors and to re-bucket sensitivities (if supported).
Example: 2019-03-16; 2019-04-27; 2019-10-27; 2020-10-27
MONEYNESS STRING Y ‘ATM’ [Risk].[Moneyness] A list of labels corresponding to different ways of stating moneyness.
Supported formats:
moneyness in percent, e.g. 80;100;120;
delta-moneyness,e.g. 25p;ATM ;25c
VALUES DOUBLE Y 0.0 Sensitivity value.
CCY STRING Y ‘N/A’ [Currencies].[Currencies] The currency of the sensitivity.
HAS_LADDER STRING Y ‘N’ [Risk].[Ladder Availability] Flag set to “Y” if the Ladder field is not null. Null values are interpreted as “N”.

Unique Key

Columns
AS_OF_DATE
TRADE_KEY
SENSITIVITY_NAME
RISK_FACTOR_ID
RISK_FACTOR_ID2
TENOR_LABELS
TENOR_DATES
MATURITY_LABELS
MATURITY_DATES
MONEYNESS

Incoming Joins

Target Table Source Columns Target Columns
TRADE_SENSITIVITIES_VECTOR AS_OF_DATE
TRADE_KEY
SENSITIVITY_NAME
RISK_FACTOR_ID
RISK_FACTOR_ID2
TENOR_LABELS
TENOR_DATES
MATURITY_LABELS
MATURITY_DATES
MONEYNESS		 AS_OF_DATE
TRADE_KEY
SENSITIVITY_NAME
RISK_FACTOR_ID
RISK_FACTOR_ID2
TENOR_LABELS
TENOR_DATES
MATURITY_LABELS
MATURITY_DATES
MONEYNESS

Outgoing Joins

Target Table Source Columns Target Columns
TRADE_ATTRIBUTES AS_OF_DATE
TRADE_KEY		 AS_OF_DATE
TRADE_KEY
RISK_FACTORS_CATALOGUE AS_OF_DATE
RISK_FACTOR_ID		 AS_OF_DATE
RISK_FACTOR_ID
RISK_FACTORS_CATALOGUE AS_OF_DATE
RISK_FACTOR_ID2		 AS_OF_DATE
RISK_FACTOR_ID

Table creation script

Snowflake

Table creation

create OR REPLACE table TRADE_SENSITIVITIES(
  AS_OF_DATE DATE NOT NULL,
  TRADE_KEY STRING NOT NULL DEFAULT 'N/A',
  TRADE_ID STRING NOT NULL DEFAULT '_DATAMEMBER_',
  SENSITIVITY_NAME STRING NOT NULL DEFAULT 'N/A',
  RISK_CLASS STRING NOT NULL DEFAULT 'N/A',
  RISK_FACTOR_ID STRING NOT NULL DEFAULT 'N/A',
  RISK_FACTOR_ID2 STRING NOT NULL DEFAULT 'N/A',
  TENOR_LABELS STRING NOT NULL DEFAULT 'N/A',
  TENOR_DATES STRING NOT NULL DEFAULT 'N/A',
  MATURITY_LABELS STRING NOT NULL DEFAULT 'N/A',
  MATURITY_DATES STRING NOT NULL DEFAULT 'N/A',
  MONEYNESS STRING NOT NULL DEFAULT 'ATM',
  "VALUES" DOUBLE NOT NULL DEFAULT 0.0,
  CCY STRING NOT NULL DEFAULT 'N/A',
  HAS_LADDER STRING NOT NULL DEFAULT 'N',
  primary key (AS_OF_DATE, TRADE_KEY, SENSITIVITY_NAME, RISK_FACTOR_ID, RISK_FACTOR_ID2, TENOR_LABELS, TENOR_DATES, MATURITY_LABELS, MATURITY_DATES,MONEYNESS),
  foreign key (AS_OF_DATE, TRADE_KEY) references TRADE_ATTRIBUTES(AS_OF_DATE,TRADE_KEY),
  foreign key (AS_OF_DATE, RISK_FACTOR_ID) references RISK_FACTORS_CATALOGUE(AS_OF_DATE,RISK_FACTOR_ID),
  foreign key (AS_OF_DATE, RISK_FACTOR_ID2) references RISK_FACTORS_CATALOGUE(AS_OF_DATE,RISK_FACTOR_ID)
);

Table population from files (optional)

If you want to populate the table from CSV files, you can run the following scripts (this example assumes that you want to load the files *Sensitivities.csv present in the folders 2023-09-26, 2023-09-27 and 2023-09-28):

create OR REPLACE stage MR_INPUT_RAW;

create OR REPLACE file format CSV_GZIP
TYPE = CSV
COMPRESSION = GZIP
SKIP_HEADER = 1
comment = 'File format to import MR data';


create OR REPLACE file format CSV_FORMAT
TYPE = CSV
COMPRESSION = AUTO
SKIP_HEADER = 1
comment = 'File format to import MR data';


PUT 'file://<full path to you data folder>/2023-09-26/CashSensitivities.csv' @MR_INPUT_RAW/2023-09-26;
PUT 'file://<full path to you data folder>/2023-09-26/GammaSensitivities.csv' @MR_INPUT_RAW/2023-09-26;
PUT 'file://<full path to you data folder>/2023-09-26/DeltaSensitivities.csv' @MR_INPUT_RAW/2023-09-26;
PUT 'file://<full path to you data folder>/2023-09-26/CrossGammaSensitivities.csv' @MR_INPUT_RAW/2023-09-26;
PUT 'file://<full path to you data folder>/2023-09-26/GammaSensitivities_CSR non-Sec.csv' @MR_INPUT_RAW/2023-09-26;
PUT 'file://<full path to you data folder>/2023-09-26/GammaSensitivities_Equity.csv' @MR_INPUT_RAW/2023-09-26;
PUT 'file://<full path to you data folder>/2023-09-26/VannaSensitivities.csv' @MR_INPUT_RAW/2023-09-26;
PUT 'file://<full path to you data folder>/2023-09-26/VegaSensitivities.csv' @MR_INPUT_RAW/2023-09-26;
PUT 'file://<full path to you data folder>/2023-09-26/VolgaSensitivities.csv' @MR_INPUT_RAW/2023-09-26;
PUT 'file://<full path to you data folder>/2023-09-26/ThetaSensitivities.csv' @MR_INPUT_RAW/2023-09-26;
PUT 'file://<full path to you data folder>/2023-09-26/DeltaSensitivities_CSR non-Sec.csv' @MR_INPUT_RAW/2023-09-26;
PUT 'file://<full path to you data folder>/2023-09-26/DeltaSensitivities_Equity.csv' @MR_INPUT_RAW/2023-09-26;
PUT 'file://<full path to you data folder>/2023-09-27/CashSensitivities.csv' @MR_INPUT_RAW/2023-09-27;
PUT 'file://<full path to you data folder>/2023-09-27/GammaSensitivities.csv' @MR_INPUT_RAW/2023-09-27;
PUT 'file://<full path to you data folder>/2023-09-27/DeltaSensitivities.csv' @MR_INPUT_RAW/2023-09-27;
PUT 'file://<full path to you data folder>/2023-09-27/CrossGammaSensitivities.csv' @MR_INPUT_RAW/2023-09-27;
PUT 'file://<full path to you data folder>/2023-09-27/GammaSensitivities_CSR non-Sec.csv' @MR_INPUT_RAW/2023-09-27;
PUT 'file://<full path to you data folder>/2023-09-27/GammaSensitivities_Equity.csv' @MR_INPUT_RAW/2023-09-27;
PUT 'file://<full path to you data folder>/2023-09-27/VannaSensitivities.csv' @MR_INPUT_RAW/2023-09-27;
PUT 'file://<full path to you data folder>/2023-09-27/VegaSensitivities.csv' @MR_INPUT_RAW/2023-09-27;
PUT 'file://<full path to you data folder>/2023-09-27/VolgaSensitivities.csv' @MR_INPUT_RAW/2023-09-27;
PUT 'file://<full path to you data folder>/2023-09-27/ThetaSensitivities.csv' @MR_INPUT_RAW/2023-09-27;
PUT 'file://<full path to you data folder>/2023-09-27/DeltaSensitivities_CSR non-Sec.csv' @MR_INPUT_RAW/2023-09-27;
PUT 'file://<full path to you data folder>/2023-09-27/DeltaSensitivities_Equity.csv' @MR_INPUT_RAW/2023-09-27;
PUT 'file://<full path to you data folder>/2023-09-28/CashSensitivities.csv' @MR_INPUT_RAW/2023-09-28;
PUT 'file://<full path to you data folder>/2023-09-28/GammaSensitivities.csv' @MR_INPUT_RAW/2023-09-28;
PUT 'file://<full path to you data folder>/2023-09-28/DeltaSensitivities.csv' @MR_INPUT_RAW/2023-09-28;
PUT 'file://<full path to you data folder>/2023-09-28/CrossGammaSensitivities.csv' @MR_INPUT_RAW/2023-09-28;
PUT 'file://<full path to you data folder>/2023-09-28/GammaSensitivities_CSR non-Sec.csv' @MR_INPUT_RAW/2023-09-28;
PUT 'file://<full path to you data folder>/2023-09-28/GammaSensitivities_Equity.csv' @MR_INPUT_RAW/2023-09-28;
PUT 'file://<full path to you data folder>/2023-09-28/VannaSensitivities.csv' @MR_INPUT_RAW/2023-09-28;
PUT 'file://<full path to you data folder>/2023-09-28/VegaSensitivities.csv' @MR_INPUT_RAW/2023-09-28;
PUT 'file://<full path to you data folder>/2023-09-28/VolgaSensitivities.csv' @MR_INPUT_RAW/2023-09-28;
PUT 'file://<full path to you data folder>/2023-09-28/ThetaSensitivities.csv' @MR_INPUT_RAW/2023-09-28;
PUT 'file://<full path to you data folder>/2023-09-28/DeltaSensitivities_CSR non-Sec.csv' @MR_INPUT_RAW/2023-09-28;
PUT 'file://<full path to you data folder>/2023-09-28/DeltaSensitivities_Equity.csv' @MR_INPUT_RAW/2023-09-28;


insert into TRADE_SENSITIVITIES
select
d.$1 as AS_OF_DATE,
ifnull(d.$2, 'N/A') as TRADE_KEY,
ifnull(d.$2, 'DATAMEMBER')as TRADE_ID,
ifnull(d.$3, 'N/A') as SENSITIVITY_NAME,
ifnull(d.$4, 'N/A') as RISK_CLASS,
ifnull(d.$5, 'N/A') as RISK_FACTOR_ID,
'N/A' as RISK_FACTOR_ID2,
tenor.value as TENOR_LABELS,
ifnull(STRTOK_TO_ARRAY(d.$7,';')[tenor.index], 'N/A') as TENOR_DATES,
mat.value as MATURITY_LABELS,
ifnull(STRTOK_TO_ARRAY(d.$9,';')[mat.index], 'N/A') as MATURITY_DATES,
mon.value as MONEYNESS,
to_double(STRTOK_TO_ARRAY(d.$11,';')[(tenor.index * ARRAY_SIZE(mat.this) + mat.index) * ARRAY_SIZE(mon.this) + mon.index]) as "VALUES",
ifnull($13, 'N/A') as CCY,
decode($12, null, 'N', 'Y') as HAS_LADDER,
ifnull($14, 'Unadjusted') as SOURCE,
ifnull($15, 'Data load') as INPUT_TYPE
from
(select $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15 from @MR_INPUT_RAW (pattern => './.(Cash|Delta|Theta|Volga|Vega|Gamma(?<!CrossGamma))Sensitivities.*.csv' , FILE_FORMAT => 'CSV_FORMAT')) d,
lateral flatten(input => STRTOK_TO_ARRAY(ifnull(d.$6, 'N/A'),';')) tenor,
lateral flatten(input => STRTOK_TO_ARRAY(ifnull(d.$8, 'N/A'),';')) mat,
lateral flatten(input => STRTOK_TO_ARRAY(ifnull(d.$10, 'ATM'),';')) mon;

  1. If the default value is marked as empty, it means that the default value is 'null' for nullable fields, and that a value needs to be explicitly set for non-nullable fields.  ↩︎