Navigation :

TRADEPNLS

The TRADEPNLS table contains some of the attributes of the PnL data used as inputs for VaR and ES computations. The PnL vectors are present in the TRADEPNLS_VECTOR table.

Column Name	Type	Not Null	Cube Field	Description
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.
TRADE_ID	STRING	Y	[Booking].[Trades]	If TradeId comes from multiple systems you may need to prepend source system to the ID for uniqueness. Note that in certain cases, the TradeId could be for adjustment purposes. In such cases we might only have one PnL vector per Book or desk. The TradeId should contain this information clearly (ADDON or ADJ). Example: “IR_IRSWAP_LIBOR3M”, “EQ_12345677”, etc.
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.
RISK_CLASS	STRING	Y	[Risk].[Risk Classes]	Defines the risk class that the PnL vector is computed for.
SENSITIVITY_NAME	STRING	Y		Name of the sensitivity that the PnL is attributed to.
LIQUIDITY_HORIZON	INT	Y	[Risk].[Liquidity Horizons]	The Liquidity Horizon in days. This field is optional.
CCY	STRING	Y	[Currencies].[Currencies]	Currency in which the PnL values are expressed.
MTM	DOUBLE			The mark-to-market value of the trade.

Unique Key

Columns
AS_OF_DATE
TRADE_KEY
SCENARIO_SET
CALCULATION_ID
RISK_FACTOR
LIQUIDITY_HORIZON

Incoming Joins

Target Table	Source Columns	Target Columns
TRADEPNLS_VECTOR	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

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	AS_OF_DATE RISK_FACTOR_ID

Snowflake

Table creation


create OR REPLACE table TRADEPNLS(
	AS_OF_DATE DATE NOT NULL,
	TRADE_KEY STRING NOT NULL DEFAULT 'N/A',
	TRADE_ID STRING NOT NULL DEFAULT '_DATAMEMBER_',
	SCENARIO_SET STRING NOT NULL DEFAULT 'N/A',
	CALCULATION_ID STRING NOT NULL DEFAULT 'N/A',
	RISK_FACTOR STRING NOT NULL DEFAULT 'N/A',
	RISK_CLASS STRING NOT NULL DEFAULT 'N/A',
	SENSITIVITY_NAME STRING NOT NULL DEFAULT 'N/A',
	LIQUIDITY_HORIZON INT NOT NULL DEFAULT 0,
	CCY STRING NOT NULL DEFAULT 'N/A',
	MTM DOUBLE,
	primary key (AS_OF_DATE, TRADE_KEY, SCENARIO_SET, CALCULATION_ID, RISK_FACTOR, LIQUIDITY_HORIZON),
	foreign key (AS_OF_DATE, TRADE_KEY) references TRADE_ATTRIBUTES(AS_OF_DATE,TRADE_KEY),
	foreign key (AS_OF_DATE, RISK_FACTOR) 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 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 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/TradePnLs.csv @MR_INPUT_RAW/2023-09-26;
PUT file://<full path to you data folder>/2023-09-27/TradePnLs.csv @MR_INPUT_RAW/2023-09-27;
PUT file://<full path to you data folder>/2023-09-28/TradePnLs.csv @MR_INPUT_RAW/2023-09-28;
copy into TRADEPNLS from (
select
$1,
$2,
$2,
ifnull($3, 'N/A'),
ifnull($4, 'N/A'),
ifnull($5, 'N/A'),
ifnull($6, 'N/A'),
ifnull($7, 'N/A'),
ifnull($8, 0),
ifnull($9, 'N/A'),
$10,
'Unadjusted',
'Data load'
from @MR_INPUT_RAW (pattern => '^.TradePnLs..csv$' , FILE_FORMAT => 'CSV_FORMAT'));