☰
Navigation :

TRADEPNLS_VECTOR

The TRADEPNLS_VECTOR table contains the PnL vectors used as inputs for VaR and ES computations.

Column Name Type Not Null Cube Field Description
VECTOR_INDEX INT Y Index in the PnL vector.
AS_OF_DATE DATE Y Timestamp (at close of business) for the data.
TRADE_KEY STRING Y The field contains the tradeID for full data or Book#VaR Inclusion for summary data.
SCENARIO_SET STRING Y [Risk].[Scenario Sets] Name of the scenario set for the PnL vector.
CALCULATION_ID STRING Y [Risk].[CalculationIds] Name of the PnL vector calculation run. There may be several runs per AsOfDate.
RISK_FACTOR STRING Y [Risk].[Risk Factors] Underlying risk factor (may be more than one) of the risk class.
LIQUIDITY_HORIZON INT Y [Risk].[Liquidity Horizons] The Liquidity Horizon in days. This field is optional.
PNL_VECTOR DOUBLE Y PnL value corresponding to the index.

Unique Key

Columns
VECTOR_INDEX
AS_OF_DATE
TRADE_KEY
SCENARIO_SET
CALCULATION_ID
RISK_FACTOR
LIQUIDITY_HORIZON

Outgoing Joins

Target Table Source Columns Target Columns
TRADEPNLS AS_OF_DATE
TRADE_KEY
SCENARIO_SET
CALCULATION_ID
RISK_FACTOR
LIQUIDITY_HORIZON
 AS_OF_DATE
TRADE_KEY
SCENARIO_SET
CALCULATION_ID
RISK_FACTOR
LIQUIDITY_HORIZON

Snowflake

Table creation

create OR REPLACE table TRADEPNLS_VECTOR(
	VECTOR_INDEX INT NOT NULL,
	AS_OF_DATE DATE NOT NULL,
	TRADE_KEY STRING NOT NULL DEFAULT 'N/A',
	SCENARIO_SET STRING NOT NULL DEFAULT 'N/A',
	CALCULATION_ID STRING DEFAULT 'N/A',
	RISK_FACTOR STRING DEFAULT 'N/A',
	LIQUIDITY_HORIZON INT NOT NULL DEFAULT 0,
	PNL_VECTOR DOUBLE NOT NULL,
	primary key (VECTOR_INDEX,AS_OF_DATE, TRADE_KEY, SCENARIO_SET, CALCULATION_ID, RISK_FACTOR, LIQUIDITY_HORIZON),
	foreign key (AS_OF_DATE, TRADE_KEY, SCENARIO_SET, CALCULATION_ID, RISK_FACTOR, LIQUIDITY_HORIZON) references TRADEPNLS(AS_OF_DATE,TRADE_KEY,SCENARIO_SET,CALCULATION_ID,RISK_FACTOR,LIQUIDITY_HORIZON)
);

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 TradePnLs.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://C:/<full path to you data folder>/2023-09-26/TradePnLs.csv @MR_INPUT_RAW/2023-09-26;
PUT file://C:/<full path to you data folder>/2023-09-27/TradePnLs.csv @MR_INPUT_RAW/2023-09-27;
PUT file://C:/<full path to you data folder>/2023-09-28/TradePnLs.csv @MR_INPUT_RAW/2023-09-28;


insert into TRADEPNLS_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.$8, 0),
to_double(v.value),
'Unadjusted',
'Data load'
from
(select $1, $2, $3, $4, $5, $8, $11 from @MR_INPUT_RAW (pattern => '^.TradePnLs..csv$' , FILE_FORMAT => 'CSV_FORMAT')) d,
lateral flatten(input => STRTOK_TO_ARRAY(d.$11,';')) v;