MARKET_SHIFTS_VECTOR
The MARKET_SHIFTS_VECTOR table contains the market shifts vector for the Taylor VaR calculations and FX shifts for FX risk computation.
Column Name | Type | Not Null | Default Value1 | Description |
---|---|---|---|---|
VECTOR_INDEX | INT | Y | Index in the market shift vector. | |
AS_OF_DATE | DATE | Y | Timestamp (at close of business) for the data. | |
RISK_FACTOR_ID | STRING | Y | N/A | The internal risk factor/bucket identifier: instrument, curve, vol surface/cube identifier. |
SCENARIO_SET | STRING | Y | N/A | Name of the set of scenarios. Example: “Historical”, “Stress”. |
TENOR | STRING | Y | N/A | Tenor label, such as 3M, 5Y, and so on, if applicable. |
MATURITY | STRING | Y | N/A | Maturity label, such as 3M, 5Y, and so on, if applicable. |
MONEYNESS | STRING | Y | N/A | Moneyness label, if applicable. |
VALUES | DOUBLE | Y | Market shift value corresponding to the index. |
Unique Key
Columns |
---|
VECTOR_INDEX |
AS_OF_DATE |
RISK_FACTOR_ID |
SCENARIO_SET |
TENOR |
MATURITY |
MONEYNESS |
Outgoing Joins
Target Table | Source Columns | Target Columns |
---|---|---|
MARKET_SHIFTS | AS_OF_DATE RISK_FACTOR_ID SCENARIO_SET TENOR MATURITY MONEYNESS |
AS_OF_DATE RISK_FACTOR_ID SCENARIO_SET TENOR MATURITY MONEYNESS |
Table creation script
Snowflake
Table creation
create OR REPLACE stage MR_INPUT_RAW;
create OR REPLACE table MARKET_SHIFTS_VECTOR(
VECTOR_INDEX INT NOT NULL,
AS_OF_DATE DATE NOT NULL,
RISK_FACTOR_ID STRING NOT NULL DEFAULT 'N/A',
SCENARIO_SET STRING NOT NULL DEFAULT 'N/A',
TENOR STRING NOT NULL DEFAULT 'N/A',
MATURITY STRING NOT NULL DEFAULT 'N/A',
MONEYNESS STRING NOT NULL DEFAULT 'N/A',
"VALUES" DOUBLE NOT NULL,
primary key (VECTOR_INDEX, AS_OF_DATE, RISK_FACTOR_ID, SCENARIO_SET, TENOR, MATURITY, 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 MarketShifts.csv present in the folders 2023-09-26, 2023-09-27 and 2023-09-28):
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/MarketShifts.csv @MR_INPUT_RAW/2023-09-26;
PUT file://<full path to you data folder>/2023-09-27/MarketShifts.csv @MR_INPUT_RAW/2023-09-27;
PUT file://<full path to you data folder>/2023-09-28/MarketShifts.csv @MR_INPUT_RAW/2023-09-28;
insert into MARKET_SHIFTS_VECTOR
select
v.index,
d.$1,
d.$2,
ifnull(d.$3, 'N/A'),
ifnull(d.$4, 'N/A'),
ifnull(d.$5, 'N/A'),
ifnull(d.$6, 'ATM'),
to_double(v.value)
from
(select $1, $2, $3, $4, $5, $6, $7 from @MR_INPUT_RAW (pattern => './MarketShifts..csv.gz' , FILE_FORMAT => 'CSV_GZIP')) d,
lateral flatten(input => STRTOK_TO_ARRAY(d.$7,';'));
-
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. ↩︎