☰
Navigation :

PNL

The PNL table contains Profit & Loss and Product Control data.

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.
DAILY DOUBLE Y 0 The DTD PnL value.
MONTHLY DOUBLE Y 0 The MTD PnL value.
YEARLY DOUBLE Y 0 The YTD PnL value.
LIFETIME DOUBLE Y 0 The LTD PnL value.
TYPE STRING Y N/A [PnL].[Types] The type of PnL.
Example: ‘Actual PL’
PLDRIVER STRING Y N/A [PnL].[PL Drivers] Driver for the PnL value.
Example: ‘Market moves’
IS_FULL_REVAL STRING Y N/A [PnL].[IsFullRevals] Indicates whether the PnL comes from a full revaluation in the risk engine.
CCY STRING Y N/A [Currencies].[Currencies] The currency of the PnL value.
RISK_FACTOR STRING Y N/A [Risk].[Risk Factors] The underlying risk factor (may be more than one) of the risk class.
RISK_CLASS STRING Y N/A [Risk].[Risk Classes] The risk factor’s asset class
BUCKET STRING Y N/A [PnL].[Buckets] Placeholder for a set of risk factors that are grouped together by common characteristics.

Unique Key

Columns
AS_OF_DATE
TRADE_KEY
TYPE
RISK_FACTOR

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

Table creation script

Snowflake

Table creation

create OR REPLACE table PNL(
  "AS_OF_DATE" DATE NOT NULL,
  TRADE_KEY STRING NOT NULL DEFAULT 'N/A',
  TRADE_ID STRING NOT NULL DEFAULT '_DATAMEMBER_',
  "DAILY" DOUBLE NOT NULL DEFAULT 0,
  "MONTHLY" DOUBLE NOT NULL DEFAULT 0,
  "YEARLY" DOUBLE NOT NULL DEFAULT 0,
  "LIFETIME" DOUBLE NOT NULL DEFAULT 0,
  "TYPE" STRING NOT NULL DEFAULT 'N/A',
  "PLDRIVER" STRING NOT NULL DEFAULT 'N/A',
  "IS_FULL_REVAL" STRING NOT NULL DEFAULT 'N/A',
  "CCY" STRING NOT NULL DEFAULT 'N/A',
  "RISK_FACTOR" STRING NOT NULL DEFAULT 'N/A',
  "RISK_CLASS" STRING NOT NULL DEFAULT 'N/A',
  "BUCKET" STRING NOT NULL DEFAULT 'N/A',
  PRIMARY KEY("AS_OF_DATE", "TRADE_KEY", "TYPE", "RISK_FACTOR")
);

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


copy into PNL from (
select
$1 as AS_OF_DATE,
ifnull($2, 'N/A') as TRADE_KEY,
ifnull($2, 'DATAMEMBER') as TRADE_ID,
ifnull($3, 0.0) as DAILY,
ifnull($4, 0.0) as MONTHLY,
ifnull($5, 0.0) as YEARLY,
ifnull($6, 0.0) as LIFETIME,
ifnull($7, 'N/A') as "TYPE",
ifnull($8, 'N/A') as PLDRIVER,
ifnull($9, 'N/A') as IS_FULL_REVAL,
ifnull($10, 'N/A') as CCY,
ifnull($11, 'N/A') as RISK_FACTOR,
ifnull($12, 'N/A') as RISK_CLASS,
ifnull($13, 'N/A') as BUCKET,
ifnull($14, 'Unadjusted') as SOURCE,
ifnull($15, 'Data load') as INPUT_TYPE
from @MR_INPUT_RAW (pattern => '^.PLPC..csv$' , FILE_FORMAT => 'CSV_FORMAT'));

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