CREATE TABLE `SRRisk`.`MsgAccountRiskRecordV5` (
`accnt` VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'SR account acronym',
`currency` ENUM('None','AUD','BRL','CAD','CHF','CNH','CNY','EUR','GBP','JPY','KRW','MXN','MYR','NOK','NZD','SEK','TRY','USD','USDCents','CZK','ZAR','HUF','USX','GBX') NOT NULL DEFAULT 'None' COMMENT 'point currency of all associated positions and pnl values',
`tradeDate` DATE NOT NULL DEFAULT '1900-01-01',
`riskSession` ENUM('Regular','PostClose') NOT NULL DEFAULT 'Regular',
`clientFirm` VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'SR assigned client firm acronym',
`lnDDelta` DOUBLE NOT NULL DEFAULT 0 COMMENT 'long net position $delta value (underlier + options) (today; SR marks)',
`shDDelta` DOUBLE NOT NULL DEFAULT 0 COMMENT 'short net position $delta value (underlier + options) (today; SR marks)',
`lnStkMktValue` DOUBLE NOT NULL DEFAULT 0 COMMENT 'long stock market value (today; SR marks)',
`shStkMktValue` DOUBLE NOT NULL DEFAULT 0 COMMENT 'short stock market value (today; SR marks)',
`futMktValue` DOUBLE NOT NULL DEFAULT 0 COMMENT 'future market value (today; SR marks)',
`stkOptMktValue` DOUBLE NOT NULL DEFAULT 0 COMMENT 'stock option market value (today; SR vol marks)',
`futOptMktValue` DOUBLE NOT NULL DEFAULT 0 COMMENT 'future option market value (today; SR vol marks)',
`vegaLong` FLOAT NOT NULL DEFAULT 0 COMMENT 'sum of long vega symbol positions;=sum(+srs.vega) if srs.vega > 0',
`vegaShort` FLOAT NOT NULL DEFAULT 0 COMMENT 'sum of short vega symbol positions;= sum(-srs.vega) if srs.vega < 0',
`wVegaLong` FLOAT NOT NULL DEFAULT 0 COMMENT 'sum of long weighted vega symbol positions;=sum(+srs.wVega) if srs.wVega > 0',
`wVegaShort` FLOAT NOT NULL DEFAULT 0 COMMENT 'sum of short weighted vega symbol positions;= sum(-srs.wVega) if srs.wVega < 0',
`wtVegaLong` FLOAT NOT NULL DEFAULT 0 COMMENT 'sum of long time weighted weighted vega symbol positions;=sum(+srs.wtVega) if srs.wtVega > 0',
`wtVegaShort` FLOAT NOT NULL DEFAULT 0 COMMENT 'sum of short time weighted vega symbol positions;= sum(-srs.wtVega) if srs.wtVega < 0',
`thetaLong` FLOAT NOT NULL DEFAULT 0 COMMENT 'sum of long vega symbol positions;=sum(+srs.theta) if srs.theta > 0',
`thetaShort` FLOAT NOT NULL DEFAULT 0 COMMENT 'sum of short vega symbol positions;= sum(-srs.theta) if srs.theta < 0',
`rhoLong` FLOAT NOT NULL DEFAULT 0 COMMENT 'sum of long rho symbol positions;=sum(+srs.rho) if srs.rho > 0',
`rhoShort` FLOAT NOT NULL DEFAULT 0 COMMENT 'sum of short rho symbol positions;= sum(-srs.rho) if srs.rho < 0',
`ivolLn` FLOAT NOT NULL DEFAULT 0,
`ivolSh` FLOAT NOT NULL DEFAULT 0,
`wtVeDd` FLOAT NOT NULL DEFAULT 0 COMMENT 'Aggregate Dd Time Weighted Vega;=sum[wtVega] if opr.xde < -0.30',
`wtVeDn` FLOAT NOT NULL DEFAULT 0 COMMENT 'Aggregate Dn Time Weighted Vega;=sum[wtVega] if -0.30 <= opr.xde < -0.10',
`wtVeAt` FLOAT NOT NULL DEFAULT 0 COMMENT 'Aggregate At Time Weighted Vega;=sum[wtVega] if abs(opr.xde) <= 0.10',
`wtVeUp` FLOAT NOT NULL DEFAULT 0 COMMENT 'Aggregate Up Time Weighted Vega;=sum[wtVega] if +0.10 < opr.xde <= +0.30',
`wtVeDu` FLOAT NOT NULL DEFAULT 0 COMMENT 'Aggregate Du Time Weighted Vega;=sum[wtVega] if +0.30 < opr.xde',
`wtVeM1` FLOAT NOT NULL DEFAULT 0 COMMENT 'Aggregate M1 Time Weighted Vega;=sum[wtVega] if days < 10',
`wtVeM2` FLOAT NOT NULL DEFAULT 0 COMMENT 'Aggregate M2 Time Weighted Vega;=sum[wtVega] if 10 < days < 25',
`wtVeM3` FLOAT NOT NULL DEFAULT 0 COMMENT 'Aggregate M3 Time Weighted Vega;=sum[wtVega] if 25 < days < 65',
`wtVeM4` FLOAT NOT NULL DEFAULT 0 COMMENT 'Aggregate M4 Time Weighted Vega;=sum[wtVega] if 65 < days < 130',
`wtVeM5` FLOAT NOT NULL DEFAULT 0 COMMENT 'Aggregate M5 Time Weighted Vega;=sum[wtVega] if 130 < days',
`ddelta` FLOAT NOT NULL DEFAULT 0 COMMENT 'net $delta;=sum(srs.ddelta)',
`numSymbols` INT NOT NULL DEFAULT 0 COMMENT 'count of srs records being aggregated',
`absCurSh` INT NOT NULL DEFAULT 0 COMMENT 'absolute number of account shares (CLR + trades);=sum(srs.opShAbsCur)',
`absCurFc` INT NOT NULL DEFAULT 0 COMMENT 'absolute number of account future contracts (CLR + trades);=sum(srs.opFcAbsCur)',
`absCurCn` INT NOT NULL DEFAULT 0 COMMENT 'absolute number of account option contracts (CLR + trades);=sum(srs.opCnAbsCur)',
`premOvPar` FLOAT NOT NULL DEFAULT 0 COMMENT 'aggregate premium over parity for the option position;=sum(srs.premOvPar)',
`optExAsPnl` DOUBLE NOT NULL DEFAULT 0,
`dividendPnl` DOUBLE NOT NULL DEFAULT 0,
`corpActCashPnL` DOUBLE NOT NULL DEFAULT 0,
`stkOpnPnlMid` DOUBLE NOT NULL DEFAULT 0,
`stkOpnPnlClr` DOUBLE NOT NULL DEFAULT 0,
`futOpnPnlMid` DOUBLE NOT NULL DEFAULT 0,
`futOpnPnlClr` DOUBLE NOT NULL DEFAULT 0,
`stkOptOpnPnlVol` DOUBLE NOT NULL DEFAULT 0,
`stkOptOpnPnlMid` DOUBLE NOT NULL DEFAULT 0,
`stkOptOpnPnlClr` DOUBLE NOT NULL DEFAULT 0,
`futOptOpnPnlVol` DOUBLE NOT NULL DEFAULT 0,
`futOptOpnPnlMid` DOUBLE NOT NULL DEFAULT 0,
`futOptOpnPnlClr` DOUBLE NOT NULL DEFAULT 0,
`stkDayPnl` DOUBLE NOT NULL DEFAULT 0 COMMENT 'stock day pnl;=sum(srs.stPnlDay)',
`futDayPnl` DOUBLE NOT NULL DEFAULT 0 COMMENT 'futures day pnl;=sum(srs.fcPnlDay)',
`stkOptDayPnl` DOUBLE NOT NULL DEFAULT 0 COMMENT 'stock option day pnl;= sum(srs.opPnlDay)',
`futOptDayPnl` DOUBLE NOT NULL DEFAULT 0 COMMENT 'future option day pnl;= sum(srs.opPnlDay)',
`stktDaySh` INT NOT NULL DEFAULT 0 COMMENT 'stock day shares (bot + sld);',
`stkDayMny` DOUBLE NOT NULL DEFAULT 0 COMMENT 'stock day mny (sld - bot)',
`futDayCn` INT NOT NULL DEFAULT 0 COMMENT 'future day contracts (bot + sld);',
`futDayMny` DOUBLE NOT NULL DEFAULT 0 COMMENT 'future day mny (sld - bot)',
`stkOptDayCn` INT NOT NULL DEFAULT 0 COMMENT 'stock option day contracts (bot + sld);',
`stkOptDayMny` DOUBLE NOT NULL DEFAULT 0 COMMENT 'stock option day mny (sld - bot)',
`futOptDayCn` INT NOT NULL DEFAULT 0 COMMENT 'future option day contracts (bot + sld);',
`futOptDayMny` DOUBLE NOT NULL DEFAULT 0 COMMENT 'future option day mny (sld - bot)',
`dayDDelta` FLOAT NOT NULL DEFAULT 0 COMMENT 'day $delta;= sum(srs.dayDelta)',
`opDayVega` FLOAT NOT NULL DEFAULT 0 COMMENT 'option vega traded today',
`opDayWVega` FLOAT NOT NULL DEFAULT 0 COMMENT 'option (vol weighted) vega traded today',
`opDayTVega` FLOAT NOT NULL DEFAULT 0 COMMENT 'option (time weighted) vega traded today',
`opDayWtVega` FLOAT NOT NULL DEFAULT 0 COMMENT 'option (vol time weighted) vega traded today',
`opDayTheta` FLOAT NOT NULL DEFAULT 0 COMMENT 'option theta traded today',
`opEdgeOpened` FLOAT NOT NULL DEFAULT 0 COMMENT 'option theo edge opened today',
`opEdgeClosed` FLOAT NOT NULL DEFAULT 0 COMMENT 'option theo edge closed today',
`pnlDn` FLOAT NOT NULL DEFAULT 0 COMMENT 'option delta neutral pnl;=sum(srs.pnlDn)',
`pnlDe` FLOAT NOT NULL DEFAULT 0 COMMENT 'option delta pnl;=sum(srs.pnlDe)',
`pnlSl` FLOAT NOT NULL DEFAULT 0 COMMENT 'option vega/delta pnl;=sum(srs.pnlSl)',
`pnlGa` FLOAT NOT NULL DEFAULT 0 COMMENT 'option gamma pnl;=sum(srs.pnlGa)',
`pnlTh` FLOAT NOT NULL DEFAULT 0 COMMENT 'option theta pnl;=sum(srs.pnlTh)',
`pnlVe` FLOAT NOT NULL DEFAULT 0 COMMENT 'option vega pnl;=sum(srs.pnlVe)',
`pnlVo` FLOAT NOT NULL DEFAULT 0 COMMENT 'option volga pnl;=sum(srs.pnlVo)',
`pnlVa` FLOAT NOT NULL DEFAULT 0 COMMENT 'option vanna pnl;=sum(srs.pnlVa)',
`pnlDDiv` FLOAT NOT NULL DEFAULT 0 COMMENT 'option DDiv pnl;=sum(srs.pnlDDiv)',
`pnlSDiv` FLOAT NOT NULL DEFAULT 0 COMMENT 'option SDiv pnl;=sum(srs.pnlSDiv)',
`pnlRate` FLOAT NOT NULL DEFAULT 0 COMMENT 'option Rate pnl;=sum(srs.pnlRate)',
`pnlErr` FLOAT NOT NULL DEFAULT 0 COMMENT 'option unexplained (error) pnl;=sum(srs.pnlErr)',
`pnlTe` FLOAT NOT NULL DEFAULT 0 COMMENT 'option theo edge pnl;=sum(srs.pnlTe)',
`pnlLn` FLOAT NOT NULL DEFAULT 0 COMMENT 'option pnl from symbol positions with +vega',
`pnlSh` FLOAT NOT NULL DEFAULT 0 COMMENT 'option pnl from symbol positions with -vega',
`tEdge` FLOAT NOT NULL DEFAULT 0 COMMENT 'aggregate option theo edge;=sum(srs.tEdge)',
`tEdgeMult` FLOAT NOT NULL DEFAULT 0 COMMENT 'denominator for computing edge per unit;=sum(srs.tEdgeMult)',
`posTEdgePnl` FLOAT NOT NULL DEFAULT 0 COMMENT 'aggregate pnl (positive edge symbols);=sum(srs.posTEdgePnl)',
`negTEdgePnl` FLOAT NOT NULL DEFAULT 0 COMMENT 'aggregate pnl (negative edge symbols);=sum(srs.negTEdgePnl)',
`badTEdgePnl` FLOAT NOT NULL DEFAULT 0 COMMENT 'aggregate pnl (no theo edge symbols);=sum(srs.badTEdgePnl)',
`VaRsu90` FLOAT NOT NULL DEFAULT 0 COMMENT 'Aggregate RiskSlide: uPrc up 90%, vol unchanged (newUPrc = uPrc * exp(+0.90))',
`VaRsd90` FLOAT NOT NULL DEFAULT 0 COMMENT 'Aggregate RiskSlide: uPrc dn 90%, vol unchanged (newUPrc = uPrc * exp(-0.90))',
`VaRsu50` FLOAT NOT NULL DEFAULT 0 COMMENT 'Aggregate RiskSlide: uPrc up 50%, vol unchanged (newUPrc = uPrc * exp(+0.50))',
`VaRsd50` FLOAT NOT NULL DEFAULT 0 COMMENT 'Aggregate RiskSlide: uPrc dn 50%, vol unchanged (newUPrc = uPrc * exp(-0.50))',
`VaRsu15` FLOAT NOT NULL DEFAULT 0 COMMENT 'Aggregate RiskSlide: uPrc up 15%, vol unchanged (newUPrc = uPrc * exp(+0.15))',
`VaRsd15` FLOAT NOT NULL DEFAULT 0 COMMENT 'Aggregate RiskSlide: uPrc dn 15%, vol unchanged (newUPrc = uPrc * exp(-0.15))',
`VaRsu10` FLOAT NOT NULL DEFAULT 0 COMMENT 'Aggregate RiskSlide: uPrc up 10%, vol unchanged (newUPrc = uPrc * Math.Exp(+0.10))',
`VaRsd10` FLOAT NOT NULL DEFAULT 0 COMMENT 'Aggregate RiskSlide: uPrc dn 10%, vol unchanged (newUPrc = uPrc * Math.Exp(-0.10))',
`VaRsu05` FLOAT NOT NULL DEFAULT 0 COMMENT 'Aggregate RiskSlide: uPrc up 5%, vol unchanged (newUPrc = uPrc * exp(+0.05))',
`VaRsd05` FLOAT NOT NULL DEFAULT 0 COMMENT 'Aggregate RiskSlide: uPrc dn 5%, vol unchanged (newUPrc = uPrc * exp(-0.05))',
`VaRsu1e` FLOAT NOT NULL DEFAULT 0 COMMENT 'Aggregate RiskSlide: uPrc up 1x implied earn move, vol ramp out',
`VaRsd1e` FLOAT NOT NULL DEFAULT 0 COMMENT 'Aggregate RiskSlide: uPrc dn 1x implied earn move, vol ramp out',
`VaRsu2e` FLOAT NOT NULL DEFAULT 0 COMMENT 'Aggregate RiskSlide: uPrc up 2x implied earn move, vol ramp out',
`VaRsd2e` FLOAT NOT NULL DEFAULT 0 COMMENT 'Aggregate RiskSlide: uPrc dn 2x implied earn move, vol ramp out',
`VaRearn` FLOAT NOT NULL DEFAULT 0 COMMENT 'Aggregate RiskSlide: vol earn ramp out (no uPrc move)',
`VaRcash` FLOAT NOT NULL DEFAULT 0 COMMENT 'Aggregate RiskSlide: uPrc up 30%, vol = 0.01, 6mn deal close (delta neutral)',
`hcCnt` FLOAT NOT NULL DEFAULT 0 COMMENT 'count of haircut > $0.0 symbols',
`haircut25` FLOAT NOT NULL DEFAULT 0 COMMENT 'haircut ($25 minimum/cn) [act basis]',
`haircut37` FLOAT NOT NULL DEFAULT 0 COMMENT 'haircut ($37 minimum/cn) [act basis]',
`optCnMinimum` FLOAT NOT NULL DEFAULT 0 COMMENT 'option contract margin minimum (37.50 for equities;SPAN minimum for options on futures)',
`span01` FLOAT NOT NULL DEFAULT 0 COMMENT 'span1: uPrc=unch, vol=up',
`span02` FLOAT NOT NULL DEFAULT 0 COMMENT 'span2: uPrc=unch, vol=down',
`span03` FLOAT NOT NULL DEFAULT 0 COMMENT 'span3: uPrc=+33.33%, vol=up',
`span04` FLOAT NOT NULL DEFAULT 0 COMMENT 'span4: uPrc=+33.33%, vol=dn',
`span05` FLOAT NOT NULL DEFAULT 0 COMMENT 'span5: uPrc=-33.33%, vol=up',
`span06` FLOAT NOT NULL DEFAULT 0 COMMENT 'span6: uPrc=-33.33%, vol=down',
`span07` FLOAT NOT NULL DEFAULT 0 COMMENT 'span7: uPrc=+66.67%, vol=up',
`span08` FLOAT NOT NULL DEFAULT 0 COMMENT 'span8: uPrc=+66.67%, vol=down',
`span09` FLOAT NOT NULL DEFAULT 0 COMMENT 'span9: uPrc=-66.67%, vol=up',
`span10` FLOAT NOT NULL DEFAULT 0 COMMENT 'span10: uPrc=-66.67%, vol=down',
`span11` FLOAT NOT NULL DEFAULT 0 COMMENT 'span11: uPrc=+100%, vol=up',
`span12` FLOAT NOT NULL DEFAULT 0 COMMENT 'span12: uPrc=+100%, vol=down',
`span13` FLOAT NOT NULL DEFAULT 0 COMMENT 'span13: uPrc=-100%, vol=up',
`span14` FLOAT NOT NULL DEFAULT 0 COMMENT 'span14: uPrc=-100%, vol=down',
`span15` FLOAT NOT NULL DEFAULT 0 COMMENT 'span15: uPrc=+300%; price slide * 0.33',
`span16` FLOAT NOT NULL DEFAULT 0 COMMENT 'span16: uPrc=-300%, price slide * 0.33',
`worst3Risk` FLOAT NOT NULL DEFAULT 0 COMMENT 'sum of 3 worst 50% slide loss symbols',
`worstSym1_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None' COMMENT 'symbol with the largest 50% slide loss',
`worstSym1_ts` ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','ESX','ANY','CXE','DXE','NXAM','NXBR','NXDUB','NXLS','NXLDN','NXML','NXMLT','NXOS','NXP','EUREX','CEDX','ICEFE') NOT NULL DEFAULT 'None' COMMENT 'symbol with the largest 50% slide loss',
`worstSym1_tk` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'symbol with the largest 50% slide loss',
`worstSym2_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None' COMMENT 'symbol with the second largest 50% slide loss',
`worstSym2_ts` ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','ESX','ANY','CXE','DXE','NXAM','NXBR','NXDUB','NXLS','NXLDN','NXML','NXMLT','NXOS','NXP','EUREX','CEDX','ICEFE') NOT NULL DEFAULT 'None' COMMENT 'symbol with the second largest 50% slide loss',
`worstSym2_tk` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'symbol with the second largest 50% slide loss',
`worstSym3_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None' COMMENT 'symbol with the third largest 50% slide loss',
`worstSym3_ts` ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','ESX','ANY','CXE','DXE','NXAM','NXBR','NXDUB','NXLS','NXLDN','NXML','NXMLT','NXOS','NXP','EUREX','CEDX','ICEFE') NOT NULL DEFAULT 'None' COMMENT 'symbol with the third largest 50% slide loss',
`worstSym3_tk` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'symbol with the third largest 50% slide loss',
`stkLiqRisk` FLOAT NOT NULL DEFAULT 0 COMMENT 'estimated cost of neutralizing/liquidating all stock positions (half market width * position size)',
`futLiqRisk` FLOAT NOT NULL DEFAULT 0 COMMENT 'estimated cost of neutralizing/liquidating all future positions (half market width * position size)',
`optLiqRisk` FLOAT NOT NULL DEFAULT 0 COMMENT 'estimated cost of neutralizing/liquidating all option positions (half market width * position size)',
`srRiskMargin` FLOAT NOT NULL DEFAULT 0 COMMENT 'SR assessed risk margin MAX(haircut37, worst3Risk + optCnMinimum)',
`lastActivity` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000',
`timestamp` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000',
PRIMARY KEY USING HASH (`accnt`,`currency`,`tradeDate`,`riskSession`,`clientFirm`)
) ENGINE=SRSE DEFAULT CHARSET=LATIN1 COMMENT='AccountRiskRecords contain account level position and risk summary detail. These records are published by AggRiskServers throughout the day approximately once per minute.';