☰
Navigation :

TRADE_SENSITIVITIES_VECTOR

The TRADE_SENSITIVITIES_VECTOR table contains the ladder data used for calculations related to sensitivities.

Column Name Type Not Null Default Value1 Cube Field Description
VECTOR_INDEX INT Y Index in the ladder vector.
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.
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
LADDER DOUBLE Y 0.0 Ladder value.

Unique Key

Columns
VECTOR_INDEX
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_SENSITIVITIES 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

Table creation script

Snowflake

Table creation

create OR REPLACE table TRADE_SENSITIVITIES_VECTOR(
  VECTOR_INDEX INT NOT NULL,
  AS_OF_DATE DATE NOT NULL,
  TRADE_KEY STRING NOT NULL DEFAULT 'N/A',
  SENSITIVITY_NAME 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',
  LADDER DOUBLE NOT NULL DEFAULT 0.0,
  primary key (VECTOR_INDEX, 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, SENSITIVITY_NAME, RISK_FACTOR_ID, RISK_FACTOR_ID2, TENOR_LABELS, TENOR_DATES, MATURITY_LABELS, MATURITY_DATES,MONEYNESS, SOURCE, INPUT_TYPE) references TRADE_SENSITIVITIES(AS_OF_DATE, TRADE_KEY, SENSITIVITY_NAME, RISK_FACTOR_ID, RISK_FACTOR_ID2, TENOR_LABELS, TENOR_DATES, MATURITY_LABELS, MATURITY_DATES,MONEYNESS)
);

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_VECTOR
select
l.INDEX as VECTOR_INDEX,
AS_OF_DATE,
TRADE_KEY,
SENSITIVITY_NAME,
RISK_FACTOR_ID,
RISK_FACTOR_ID2,
TENOR_LABELS,
TENOR_DATES,
MATURITY_LABELS,
MATURITY_DATES,
MONEYNESS,
to_double(l.VALUE) as LADDER,
SOURCE,
INPUT_TYPE
from(
select
d.$1 as AS_OF_DATE,
ifnull(d.$2, 'N/A') as TRADE_KEY,
ifnull(d.$3, 'N/A') as SENSITIVITY_NAME,
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,
ROUND(ARRAY_SIZE($12) / (ARRAY_SIZE(tenor.this) * ARRAY_SIZE(mat.this) * ARRAY_SIZE(mon.this))) as LADDER_SIZE,
(tenor.index * ARRAY_SIZE(mat.this) + mat.index) * ARRAY_SIZE(mon.this) + mon.index as CURRENT_LADDER,
ARRAY_SLICE($12,CURRENT_LADDER * LADDER_SIZE, (CURRENT_LADDER + 1) * LADDER_SIZE) as 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, STRTOK_TO_ARRAY($12,';'), $13, $14, $15 from @MR_INPUT_RAW (pattern => './.(Cash|Delta|Theta|Volga|Vega|Gamma(?<!CrossGamma))Sensitivities.*.csv' , FILE_FORMAT => 'CSV_FORMAT') where $12 is not null) 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
) d1,
lateral flatten(input => LADDER) l;

  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.  ↩︎