Version: Upcoming

RiskCubeDetailV5

RiskCubeDetail records are published by CoreRiskServers and are designed to be aggregated and analyzed over time.\nRiskCubeDetail records are published to the SpiderRock Archive Host data pipeline at the end of each trading period.

METADATA

Attribute	Value
Topic	4740-risk-v5
MLink Token	ClientRisk
Product	SRRisk
accessType	SELECT

Table Definition

Field	Type	Key	Default Value	Comment
accnt	VARCHAR(16)	PRI	''	SR Accnt
secKey_at	enum - AssetType	PRI	'None'
secKey_ts	enum - TickerSrc	PRI	'None'
secKey_tk	VARCHAR(12)	PRI	''
secKey_yr	SMALLINT UNSIGNED	PRI	0
secKey_mn	TINYINT UNSIGNED	PRI	0
secKey_dy	TINYINT UNSIGNED	PRI	0
secKey_xx	DOUBLE	PRI	0
secKey_cp	enum - CallPut	PRI	'Call'
secType	enum - SecType	PRI	'None'	Call Put Stock Future Cash
tradeDate	DATE	PRI	'1900-01-01'
riskSession	enum - RiskSession	PRI	'Regular'
clientFirm	VARCHAR(16)	PRI	''	SR assigned client firm
version	enum - RiskCubeVersion		'None'
clearingFirm	VARCHAR(8)		''	Clearing Firm
clearingAccnt	VARCHAR(16)		''	Clearing Firm Accnt
riskServerCode	VARCHAR(6)		''	SR RiskServer server that published this record
viewGroup1	VARCHAR(12)		''	Client defined view group 1 can be fiteredaggregated
viewGroup2	VARCHAR(12)		''	Client defined view group 2 can be fiteredaggregated
viewGroup3	VARCHAR(12)		''	Client defined view group 3 can be fiteredaggregated
ticker_at	enum - AssetType		'None'	Risk Symbol underlier for equities product group for futureoption complexes
ticker_ts	enum - TickerSrc		'None'	Risk Symbol underlier for equities product group for futureoption complexes
ticker_tk	VARCHAR(12)		''	Risk Symbol underlier for equities product group for futureoption complexes
riskClass	VARCHAR(8)		''	Symbol risk class from SymbolControl record
symbolType	enum - SymbolType		'None'	Symbol type
beta	FLOAT		0	beta usually beta to SPX see AccountConfigbetaSource
betaSource	enum - BetaSource		'None'
sector	VARCHAR(16)		''	Client defined sector from SymbolControl record
indNum	INT		0	Ind Num 00
subNum	INT		0	Sub Num 0000
grpNum	INT		0	Grp Num 000000
nbrNum	INT		0	Nbr Num 00000000
eDays	SMALLINT		0	days to next earnings 5 30
dDays	SMALLINT		0	days to next dividend 5 30
xDelta	FLOAT		0	calls 05 delta puts 05 delta
expCode	enum - ExpCode		'None'	Timetoexpiry code W1 W2 M1 M2 M34 M56 M7C Y1 Y2
skewCode	enum - SkewCode		'None'	Skew bucket code DdDeep DownSide Dn DownSide At ATM Up UpSide DU FarUpSide
eDaysCode	enum - EDaysCode		'None'	Days to next earnings event if any
iVolRange	enum - iVolRange		'None'	Implied ATM Vol Range
skewDD	FLOAT		0	dd deep downside skew metric xde 030
skewDN	FLOAT		0	dn downside skew metric 030 xde 010
skewAT	FLOAT		0	at atm skew risk metric 010 xde 010
skewUP	FLOAT		0	up upside skew risk metric 010 xde 030
skewDU	FLOAT		0	du far upside skew risk metric 030 xde
shOpnPos	INT		0	shares startofday
fcOpnPos	INT		0	futures contracts startofday
cnOpnPos	INT		0	options contracts startofday
cnAtmEquiv	FLOAT		0	atm equivalent contracts oprcnOpnPos oprcnBot oprcnSld oprve opratmVega
shBot	INT		0	shares bot
shSld	INT		0	shares sld
shSldShrt	INT		0	shares sld short
fcBot	INT		0	future contracts bot
fcSld	INT		0	future contracts sld
cnBot	INT		0	option contracts bot
cnSld	INT		0	option contracts sld
cnOpened	INT		0	option contracts opened
cnClosed	INT		0	option contracts closed
opnDir	VARCHAR(2)		''	open startofday direction LN or SH
posDir	VARCHAR(2)		''	current startofday trades direction LN or SH
unitOpnPos	INT		0	effective opening position shares or cn underlierPerCn startofday
unitCurPos	INT		0	effective current position shares or cn underlierPerCn startofday trades
unitQtyBot	INT		0	effective shares bot shares or cn underlierPerCn
unitQtySld	INT		0	effective shares sld shares or cn underlierPerCn
dayPnl	FLOAT		0	day pnl
dayDnPnl	FLOAT		0	day pnl delta neutral
dayTrdDelta	FLOAT		0	day trade delta
dayTrdDDelta	FLOAT		0	day trade delta
dayTrdGamma	FLOAT		0	day trade gamma
dayTrdDGamma	FLOAT		0	day trade gamma
dayTrdVega	FLOAT		0	day trade vega
dayTrdWVega	FLOAT		0	day trade wVega vega vol
dayTrdTVega	FLOAT		0	day trade tVega vega sqrtmax01 years 4
dayTrdWtVega	FLOAT		0	day trade wtVega vega vol sqrtmax01 years 4
dayTrdTheta	FLOAT		0	day trade theta
dayEdgeOpened	FLOAT		0	day theo option edge opened requires a theo model
dayEdgeClosed	FLOAT		0	day theo option edge closed requires a theo model
dayMnyBot	DOUBLE		0	day money bot
dayMnySld	DOUBLE		0	day money sld
opnPnlVolMark	FLOAT		0	cursurface prsurface cnOpnClr upc
opnPnlMidMark	FLOAT		0	curmid prmid cnOpnClr upc
opnPnlClrMark	FLOAT		0	curmid prclr cnOpnClr upc
opnMarkBrkMny	FLOAT		0	cursurface curmid cnOpnClr upc
opnTheoEdge	FLOAT		0	opening edge edge at the end of prior period requires a theo model
posTheoEdge	FLOAT		0	current edge opening positions trades requires a theo model
tVolMove	FLOAT		0	LNtVol tVolPr
tEdgeMove	FLOAT		0	LNtVol iVol LNtVolPr iVolPr
tEdge	FLOAT		0	LNtVol iVol
opnPnlDe	FLOAT		0	open position mult hedgeDe dUPrc pnlSD sumpnlDe stock futures options
opnPnlGa	FLOAT		0	open position mult 05 optGamma dUPrc dUPrc
opnPnlTh	FLOAT		0	open position mult optTheta dTime
opnPnlVe	FLOAT		0	open position mult optVega dVol
opnPnlVo	FLOAT		0	open position mult 05 optVolga dVol dVol
opnPnlVa	FLOAT		0	open position mult optVanna dVol dUPrc
opnPnlSl	FLOAT		0	open position mult voluPrc slope optVega dUPrc
opnPnlRate	FLOAT		0	open position mult optRho dRate
opnPnlSDiv	FLOAT		0	open position mult optPhi dSDv
opnPnlDDiv	FLOAT		0	open position mult optPhi dDDv
opnPnlErr	FLOAT		0	unattributed pnl SR Vol Pnl opnPnlDe opnPnlGa opnPnlTh opnPnlVe opnPnlRat opnPnlSDiv opnPnlDDiv
opnEdgeChange	FLOAT		0	open position mult tEdge tEdgePr requires a theo model
posDelta	FLOAT		0	live position mult hedgeDe
posDDelta	FLOAT		0	live position mult hedgeDe UPrc
posDBeta	FLOAT		0	live position mult hedgeDe beta UPrc
posGamma	FLOAT		0	live position mult optGamma
posDGamma	FLOAT		0	live position mult optGamma uPrc uPrc 100
posVega	FLOAT		0	live position mult optVega
posWVega	FLOAT		0	live position mult optVega iVol
posTVega	FLOAT		0	live position mult optVega sqrtmax01 years 4
posWtVega	FLOAT		0	live position mult optVega iVol sqrtmax01 years 4
posVolga	FLOAT		0	live position mult optVolg
posVanna	FLOAT		0	live position mult optVanna
posTheta	FLOAT		0	live position mult optTheta
posRho	FLOAT		0	live position mult optRho
posPhi	FLOAT		0	live position mult optPhi
posNotional	FLOAT		0	live position mult uPrc
posMktValue	FLOAT		0	live position mult SR surface mark
posPremOPar	FLOAT		0	live position mult premium over parity
atmVol	FLOAT		0	atm volatility
symVol	FLOAT		0	underlier symbol volatility term21dcensored
srSlope	FLOAT		0	strike veSlope sr surfaceoptions only
prcSVol	FLOAT		0	SR Surface Vol
prcTOpx	FLOAT		0	SR Surface Price
prcYOpx	FLOAT		0	SR Yesterday Surface Price
prcUPrc	FLOAT		0	underlier price
prcYears	FLOAT		0	yearstoexpiry
prcRate	FLOAT		0	discount ratetoexpiry
prcSdiv	FLOAT		0	continuous stock div ratetoexpiry
prcDdiv	FLOAT		0	cumulative discrete dividend paid through expiry
uPrcRatio	FLOAT		0	stock price multiplier
uPrcMove	FLOAT		0	Underlier price change
iVolMove	FLOAT		0	Implied vol change fixed strike ivol change from prior period
exDivAmt	FLOAT		0	estimated exDiv amount if any
borrowRate	FLOAT		0	estimated stock borrow rate if any
modelType	TINYINT UNSIGNED		0	SRPricingLibCalcModelType
underliersPerCn	INT		0	underliers per contract
underlierType	enum - UnderlierType		'None'	underlier type used for option pricing
pointValue	FLOAT		0	value of one point
pointCurrency	enum - Currency		'None'
tickValue	FLOAT		0	value of one tick
multihedge	enum - Multihedge		'None'
multihedgeSource_at	enum - AssetType		'None'	MH Ticker Key starts with if exists
multihedgeSource_ts	enum - TickerSrc		'None'	MH Ticker Key starts with if exists
multihedgeSource_tk	VARCHAR(12)		''	MH Ticker Key starts with if exists
multihedgePVRatio	FLOAT		0	pv multiplier fraction of underlier value uPerCn x uPrc SUMuPerCn x uPrc associated with this fragment
spanPrcIncUp	FLOAT		0	default exchange span price up increment for this asset
spanPrcIncDn	FLOAT		0	default exchange span price dn increment for this asset
spanVolInc	FLOAT		0	default exchange span volatility increment for this asset
prcSpanType	enum - SpanType		'None'	exchange prcSpan type
volSpanType	enum - SpanType		'None'	exchange volSpan type
spanPricingModel	enum - SpanPricingModel		'None'	Exchangedefined model for SPAN option pricing calcs
marginType	enum - MarginType		'None'	margin slide type NMSEquity 15 NMSIndex 86 NMSMedium 10
timestamp	DATETIME(6)		'1900-01-01 00:00:00.000000'

PRIMARY KEY DEFINITION (Unique)

Field	Sequence
accnt	1
secKey_tk	2
secKey_yr	3
secKey_mn	4
secKey_dy	5
secKey_xx	6
secKey_cp	7
secKey_at	8
secKey_ts	9
secType	10
tradeDate	11
riskSession	12
clientFirm	13

CREATE TABLE EXAMPLE QUERY

CREATE TABLE `SRRisk`.`MsgRiskCubeDetailV5` (
    `accnt` VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'SR Accnt',
    `secKey_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None',
    `secKey_ts` ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','EUX','ANY','CXE','DXE','NXAM','NXBR','NXLS','NXML','NXOS','NXP','EUREX','CEDX','ICEFE') NOT NULL DEFAULT 'None',
    `secKey_tk` VARCHAR(12) NOT NULL DEFAULT '',
    `secKey_yr` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    `secKey_mn` TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `secKey_dy` TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `secKey_xx` DOUBLE NOT NULL DEFAULT 0,
    `secKey_cp` ENUM('Call','Put','Pair') NOT NULL DEFAULT 'Call',
    `secType` ENUM('None','Call','Put','Stock','Future','Cash') NOT NULL DEFAULT 'None' COMMENT '[Call, Put, Stock, Future, Cash]',
    `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',
    `version` ENUM('None','Live','EarlyArchive','LateArchive') NOT NULL DEFAULT 'None',
    `clearingFirm` VARCHAR(8) NOT NULL DEFAULT '' COMMENT 'Clearing Firm',
    `clearingAccnt` VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'Clearing Firm Accnt',
    `riskServerCode` VARCHAR(6) NOT NULL DEFAULT '' COMMENT 'SR RiskServer (server that published this record)',
    `viewGroup1` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'Client defined view group #1 (can be fitered/aggregated)',
    `viewGroup2` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'Client defined view group #2 (can be fitered/aggregated)',
    `viewGroup3` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'Client defined view group #3 (can be fitered/aggregated)',
    `ticker_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None' COMMENT 'Risk Symbol (underlier for equities; product group for future/option complexes)',
    `ticker_ts` ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','EUX','ANY','CXE','DXE','NXAM','NXBR','NXLS','NXML','NXOS','NXP','EUREX','CEDX','ICEFE') NOT NULL DEFAULT 'None' COMMENT 'Risk Symbol (underlier for equities; product group for future/option complexes)',
    `ticker_tk` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'Risk Symbol (underlier for equities; product group for future/option complexes)',
    `riskClass` VARCHAR(8) NOT NULL DEFAULT '' COMMENT 'Symbol risk class (from SymbolControl record)',
    `symbolType` ENUM('None','Equity','ADR','ETF','CashIndex','MutualFund','ShortETF','Future','Bond','DepReceipts','PreferredSec','PreferenceShare','StructuredProd','StapledSec','TradeableRights','Unit','Warrant','WhenIssued','ForeignIssue') NOT NULL DEFAULT 'None' COMMENT 'Symbol type',
    `beta` FLOAT NOT NULL DEFAULT 0 COMMENT 'beta (usually beta to SPX; see AccountConfig.betaSource)',
    `betaSource` ENUM('None','betaSPX','betaQQQ','betaIWM','clientBeta') NOT NULL DEFAULT 'None',
    `sector` VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'Client defined sector (from SymbolControl record)',
    `indNum` INT NOT NULL DEFAULT 0 COMMENT 'Ind Num (00)',
    `subNum` INT NOT NULL DEFAULT 0 COMMENT 'Sub Num (0000)',
    `grpNum` INT NOT NULL DEFAULT 0 COMMENT 'Grp Num (000000)',
    `nbrNum` INT NOT NULL DEFAULT 0 COMMENT 'Nbr Num (00000000)',
    `eDays` SMALLINT NOT NULL DEFAULT 0 COMMENT 'days to next earnings [-5, +30]',
    `dDays` SMALLINT NOT NULL DEFAULT 0 COMMENT 'days to next dividend [-5, +30]',
    `xDelta` FLOAT NOT NULL DEFAULT 0 COMMENT 'calls = +0.5 - delta; puts = -0.5 - delta',
    `expCode` ENUM('None','W1','W2','M1','M2','M34','M56','M7C','Y1','Y2') NOT NULL DEFAULT 'None' COMMENT 'Time-to-expiry code (W1, W2, M1, M2, M34, M56, M7C, Y1, Y2)',
    `skewCode` ENUM('None','DD','DN','AT','UP','DU') NOT NULL DEFAULT 'None' COMMENT 'Skew bucket code (Dd=Deep DownSide, Dn = DownSide, At = ATM, Up = UpSide, DU = FarUpSide)',
    `eDaysCode` ENUM('None','Past','eDay_0','eDay_1','eDays_2_3','eDays_4_5','eDays_6_10','eDays_11_25','eDays_26_65') NOT NULL DEFAULT 'None' COMMENT 'Days to next earnings event (if any)',
    `iVolRange` ENUM('None','iVolRng_01_03','iVolRng_03_06','iVolRng_06_09','iVolRng_09_12','iVolRng_12_15','iVolRng_15_20','iVolRng_20_25','iVolRng_25_35','iVolRng_35_50','iVolRng_50_80','iVolRng_80_up') NOT NULL DEFAULT 'None' COMMENT 'Implied ATM Vol Range',
    `skewDD` FLOAT NOT NULL DEFAULT 0 COMMENT 'dd (deep downside) skew metric (xde < -0.30)',
    `skewDN` FLOAT NOT NULL DEFAULT 0 COMMENT 'dn (downside) skew metric (-0.30 < xde < -0.10)',
    `skewAT` FLOAT NOT NULL DEFAULT 0 COMMENT 'at (atm) skew risk metric (-0.10 < xde < +0.10)',
    `skewUP` FLOAT NOT NULL DEFAULT 0 COMMENT 'up (upside) skew risk metric (+0.10 < xde < +0.30)',
    `skewDU` FLOAT NOT NULL DEFAULT 0 COMMENT 'du (far upside) skew risk metric (+0.30 < xde)',
    `shOpnPos` INT NOT NULL DEFAULT 0 COMMENT 'shares (start-of-day)',
    `fcOpnPos` INT NOT NULL DEFAULT 0 COMMENT 'futures contracts (start-of-day)',
    `cnOpnPos` INT NOT NULL DEFAULT 0 COMMENT 'options contracts (start-of-day)',
    `cnAtmEquiv` FLOAT NOT NULL DEFAULT 0 COMMENT 'atm equivalent contracts; (opr.cnOpnPos + opr.cnBot - opr.cnSld) * opr.ve / opr.atmVega',
    `shBot` INT NOT NULL DEFAULT 0 COMMENT 'shares bot',
    `shSld` INT NOT NULL DEFAULT 0 COMMENT 'shares sld',
    `shSldShrt` INT NOT NULL DEFAULT 0 COMMENT 'shares sld short',
    `fcBot` INT NOT NULL DEFAULT 0 COMMENT 'future contracts bot',
    `fcSld` INT NOT NULL DEFAULT 0 COMMENT 'future contracts sld',
    `cnBot` INT NOT NULL DEFAULT 0 COMMENT 'option contracts bot',
    `cnSld` INT NOT NULL DEFAULT 0 COMMENT 'option contracts sld',
    `cnOpened` INT NOT NULL DEFAULT 0 COMMENT 'option contracts opened',
    `cnClosed` INT NOT NULL DEFAULT 0 COMMENT 'option contracts closed',
    `opnDir` VARCHAR(2) NOT NULL DEFAULT '' COMMENT 'open (start-of-day) direction (LN or SH)',
    `posDir` VARCHAR(2) NOT NULL DEFAULT '' COMMENT 'current (start-of-day + trades) direction (LN or SH)',
    `unitOpnPos` INT NOT NULL DEFAULT 0 COMMENT 'effective opening position (shares or cn * underlierPerCn) (start-of-day)',
    `unitCurPos` INT NOT NULL DEFAULT 0 COMMENT 'effective current position (shares or cn * underlierPerCn) (start-of-day + trades)',
    `unitQtyBot` INT NOT NULL DEFAULT 0 COMMENT 'effective shares bot (shares or cn * underlierPerCn)',
    `unitQtySld` INT NOT NULL DEFAULT 0 COMMENT 'effective shares sld (shares or cn * underlierPerCn)',
    `dayPnl` FLOAT NOT NULL DEFAULT 0 COMMENT 'day pnl',
    `dayDnPnl` FLOAT NOT NULL DEFAULT 0 COMMENT 'day pnl (delta neutral)',
    `dayTrdDelta` FLOAT NOT NULL DEFAULT 0 COMMENT 'day trade delta',
    `dayTrdDDelta` FLOAT NOT NULL DEFAULT 0 COMMENT 'day trade $delta',
    `dayTrdGamma` FLOAT NOT NULL DEFAULT 0 COMMENT 'day trade gamma',
    `dayTrdDGamma` FLOAT NOT NULL DEFAULT 0 COMMENT 'day trade $gamma',
    `dayTrdVega` FLOAT NOT NULL DEFAULT 0 COMMENT 'day trade vega',
    `dayTrdWVega` FLOAT NOT NULL DEFAULT 0 COMMENT 'day trade wVega (vega * vol)',
    `dayTrdTVega` FLOAT NOT NULL DEFAULT 0 COMMENT 'day trade tVega (vega / sqrt(max(0.1, years * 4)))',
    `dayTrdWtVega` FLOAT NOT NULL DEFAULT 0 COMMENT 'day trade wtVega (vega * vol / sqrt(max(0.1, years * 4)))',
    `dayTrdTheta` FLOAT NOT NULL DEFAULT 0 COMMENT 'day trade theta',
    `dayEdgeOpened` FLOAT NOT NULL DEFAULT 0 COMMENT 'day theo option edge opened (requires a theo model)',
    `dayEdgeClosed` FLOAT NOT NULL DEFAULT 0 COMMENT 'day theo option edge closed (requires a theo model)',
    `dayMnyBot` DOUBLE NOT NULL DEFAULT 0 COMMENT 'day money bot',
    `dayMnySld` DOUBLE NOT NULL DEFAULT 0 COMMENT 'day money sld',
    `opnPnlVolMark` FLOAT NOT NULL DEFAULT 0 COMMENT '(cur.surface - pr.surface) * cnOpnClr * upc',
    `opnPnlMidMark` FLOAT NOT NULL DEFAULT 0 COMMENT '(cur.mid - pr.mid) * cnOpnClr * upc',
    `opnPnlClrMark` FLOAT NOT NULL DEFAULT 0 COMMENT '(cur.mid - pr.clr) * cnOpnClr * upc',
    `opnMarkBrkMny` FLOAT NOT NULL DEFAULT 0 COMMENT '(cur.surface - cur.mid) * cnOpnClr * upc',
    `opnTheoEdge` FLOAT NOT NULL DEFAULT 0 COMMENT 'opening edge (edge at the end of prior period) (requires a theo model)',
    `posTheoEdge` FLOAT NOT NULL DEFAULT 0 COMMENT 'current edge (opening positions + trades) (requires a theo model)',
    `tVolMove` FLOAT NOT NULL DEFAULT 0 COMMENT 'LN(tVol / tVolPr)',
    `tEdgeMove` FLOAT NOT NULL DEFAULT 0 COMMENT 'LN(tVol / iVol) - LN(tVolPr / iVolPr)',
    `tEdge` FLOAT NOT NULL DEFAULT 0 COMMENT 'LN(tVol / iVol)',
    `opnPnlDe` FLOAT NOT NULL DEFAULT 0 COMMENT 'open position * mult * hedgeDe * dUPrc			( pnlSD = sum(pnlDe) [stock + futures + options] )',
    `opnPnlGa` FLOAT NOT NULL DEFAULT 0 COMMENT 'open position * mult * 0.5 * optGamma * dUPrc * dUPrc',
    `opnPnlTh` FLOAT NOT NULL DEFAULT 0 COMMENT 'open position * mult * optTheta * dTime',
    `opnPnlVe` FLOAT NOT NULL DEFAULT 0 COMMENT 'open position * mult * optVega * dVol',
    `opnPnlVo` FLOAT NOT NULL DEFAULT 0 COMMENT 'open position * mult * 0.5 * optVolga * dVol * dVol',
    `opnPnlVa` FLOAT NOT NULL DEFAULT 0 COMMENT 'open position * mult * optVanna * dVol * dUPrc',
    `opnPnlSl` FLOAT NOT NULL DEFAULT 0 COMMENT 'open position * mult * vol/uPrc * slope * optVega * dUPrc',
    `opnPnlRate` FLOAT NOT NULL DEFAULT 0 COMMENT 'open position * mult * optRho * dRate',
    `opnPnlSDiv` FLOAT NOT NULL DEFAULT 0 COMMENT 'open position * mult * optPhi * dSDv',
    `opnPnlDDiv` FLOAT NOT NULL DEFAULT 0 COMMENT 'open position * mult * optPhi * dDDv',
    `opnPnlErr` FLOAT NOT NULL DEFAULT 0 COMMENT 'unattributed pnl: SR Vol Pnl - opnPnlDe - opnPnlGa - opnPnlTh - opnPnlVe - opnPnlRat - opnPnlSDiv - opnPnlDDiv',
    `opnEdgeChange` FLOAT NOT NULL DEFAULT 0 COMMENT 'open position * mult * (tEdge - tEdgePr) (requires a theo model)',
    `posDelta` FLOAT NOT NULL DEFAULT 0 COMMENT 'live position * mult * hedgeDe',
    `posDDelta` FLOAT NOT NULL DEFAULT 0 COMMENT 'live position * mult * hedgeDe * UPrc',
    `posDBeta` FLOAT NOT NULL DEFAULT 0 COMMENT 'live position * mult * hedgeDe * beta * UPrc',
    `posGamma` FLOAT NOT NULL DEFAULT 0 COMMENT 'live position * mult * optGamma',
    `posDGamma` FLOAT NOT NULL DEFAULT 0 COMMENT 'live position * mult * optGamma * uPrc * uPrc / 100',
    `posVega` FLOAT NOT NULL DEFAULT 0 COMMENT 'live position * mult * optVega',
    `posWVega` FLOAT NOT NULL DEFAULT 0 COMMENT 'live position * mult * optVega * iVol',
    `posTVega` FLOAT NOT NULL DEFAULT 0 COMMENT 'live position * mult * optVega / sqrt(max(0.1, years * 4))',
    `posWtVega` FLOAT NOT NULL DEFAULT 0 COMMENT 'live position * mult * optVega * iVol / sqrt(max(0.1, years * 4))',
    `posVolga` FLOAT NOT NULL DEFAULT 0 COMMENT 'live position * mult * optVolg',
    `posVanna` FLOAT NOT NULL DEFAULT 0 COMMENT 'live position * mult * optVanna',
    `posTheta` FLOAT NOT NULL DEFAULT 0 COMMENT 'live position * mult * optTheta',
    `posRho` FLOAT NOT NULL DEFAULT 0 COMMENT 'live position * mult * optRho',
    `posPhi` FLOAT NOT NULL DEFAULT 0 COMMENT 'live position * mult * optPhi',
    `posNotional` FLOAT NOT NULL DEFAULT 0 COMMENT 'live position * mult * uPrc',
    `posMktValue` FLOAT NOT NULL DEFAULT 0 COMMENT 'live position * mult * SR surface mark',
    `posPremOPar` FLOAT NOT NULL DEFAULT 0 COMMENT 'live position * mult * premium over parity',
    `atmVol` FLOAT NOT NULL DEFAULT 0 COMMENT 'atm volatility',
    `symVol` FLOAT NOT NULL DEFAULT 0 COMMENT 'underlier symbol volatility (term.21d;censored)',
    `srSlope` FLOAT NOT NULL DEFAULT 0 COMMENT 'strike veSlope (sr surface;options only)',
    `prcSVol` FLOAT NOT NULL DEFAULT 0 COMMENT 'SR Surface Vol',
    `prcTOpx` FLOAT NOT NULL DEFAULT 0 COMMENT 'SR Surface Price',
    `prcYOpx` FLOAT NOT NULL DEFAULT 0 COMMENT 'SR Yesterday Surface Price',
    `prcUPrc` FLOAT NOT NULL DEFAULT 0 COMMENT 'underlier price',
    `prcYears` FLOAT NOT NULL DEFAULT 0 COMMENT 'years-to-expiry',
    `prcRate` FLOAT NOT NULL DEFAULT 0 COMMENT 'discount rate-to-expiry',
    `prcSdiv` FLOAT NOT NULL DEFAULT 0 COMMENT 'continuous stock div rate-to-expiry',
    `prcDdiv` FLOAT NOT NULL DEFAULT 0 COMMENT 'cumulative discrete dividend paid through expiry',
    `uPrcRatio` FLOAT NOT NULL DEFAULT 0 COMMENT 'stock price multiplier',
    `uPrcMove` FLOAT NOT NULL DEFAULT 0 COMMENT 'Underlier price change',
    `iVolMove` FLOAT NOT NULL DEFAULT 0 COMMENT 'Implied vol change (fixed strike ivol change from prior period)',
    `exDivAmt` FLOAT NOT NULL DEFAULT 0 COMMENT 'estimated exDiv amount (if any)',
    `borrowRate` FLOAT NOT NULL DEFAULT 0 COMMENT 'estimated stock borrow rate (if any)',
    `modelType` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'SRPricingLib.CalcModelType',
    `underliersPerCn` INT NOT NULL DEFAULT 0 COMMENT 'underliers per contract',
    `underlierType` ENUM('None','Equity','Other','FX') NOT NULL DEFAULT 'None' COMMENT 'underlier type (used for option pricing)',
    `pointValue` FLOAT NOT NULL DEFAULT 0 COMMENT 'value of one point',
    `pointCurrency` 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',
    `tickValue` FLOAT NOT NULL DEFAULT 0 COMMENT 'value of one tick',
    `multihedge` ENUM('None','Simple','Complex','AllCash','Binary','Fragment') NOT NULL DEFAULT 'None',
    `multihedgeSource_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None' COMMENT 'MH Ticker Key (starts with ''_'') (if exists)',
    `multihedgeSource_ts` ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','EUX','ANY','CXE','DXE','NXAM','NXBR','NXLS','NXML','NXOS','NXP','EUREX','CEDX','ICEFE') NOT NULL DEFAULT 'None' COMMENT 'MH Ticker Key (starts with ''_'') (if exists)',
    `multihedgeSource_tk` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'MH Ticker Key (starts with ''_'') (if exists)',
    `multihedgePVRatio` FLOAT NOT NULL DEFAULT 0 COMMENT 'pv multiplier (fraction of underlier value (uPerCn x uPrc) / SUM(uPerCn x uPrc) associated with this fragment',
    `spanPrcIncUp` FLOAT NOT NULL DEFAULT 0 COMMENT 'default (exchange) span price up increment for this asset',
    `spanPrcIncDn` FLOAT NOT NULL DEFAULT 0 COMMENT 'default (exchange) span price dn increment for this asset',
    `spanVolInc` FLOAT NOT NULL DEFAULT 0 COMMENT 'default (exchange) span volatility increment for this asset',
    `prcSpanType` ENUM('None','Pts','Pct','Log') NOT NULL DEFAULT 'None' COMMENT 'exchange prcSpan type',
    `volSpanType` ENUM('None','Pts','Pct','Log') NOT NULL DEFAULT 'None' COMMENT 'exchange volSpan type',
    `spanPricingModel` ENUM('None','BS','B','M','W','WS','WB','CA','CE','I') NOT NULL DEFAULT 'None' COMMENT 'Exchange-defined model for SPAN option pricing calcs',
    `marginType` ENUM('None','NMS_Equity','NMS_Index','NMS_Medium') NOT NULL DEFAULT 'None' COMMENT 'margin slide type: NMS_Equity = +/- 15%, NMS_Index = -8%/+6%, NMS_Medium = +/-10%',
    `timestamp` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000',
    PRIMARY KEY USING HASH (`accnt`,`secKey_tk`,`secKey_yr`,`secKey_mn`,`secKey_dy`,`secKey_xx`,`secKey_cp`,`secKey_at`,`secKey_ts`,`secType`,`tradeDate`,`riskSession`,`clientFirm`)
) ENGINE=SRSE DEFAULT CHARSET=LATIN1 COMMENT='RiskCubeDetail records are published by CoreRiskServers and are designed to be aggregated and analyzed over time.\nRiskCubeDetail records are published to the SpiderRock Archive Host data pipeline at the end of each trading period.';

SELECT TABLE EXAMPLE QUERY

SELECT
    `accnt`,
    `secKey_at`,
    `secKey_ts`,
    `secKey_tk`,
    `secKey_yr`,
    `secKey_mn`,
    `secKey_dy`,
    `secKey_xx`,
    `secKey_cp`,
    `secType`,
    `tradeDate`,
    `riskSession`,
    `clientFirm`,
    `version`,
    `clearingFirm`,
    `clearingAccnt`,
    `riskServerCode`,
    `viewGroup1`,
    `viewGroup2`,
    `viewGroup3`,
    `ticker_at`,
    `ticker_ts`,
    `ticker_tk`,
    `riskClass`,
    `symbolType`,
    `beta`,
    `betaSource`,
    `sector`,
    `indNum`,
    `subNum`,
    `grpNum`,
    `nbrNum`,
    `eDays`,
    `dDays`,
    `xDelta`,
    `expCode`,
    `skewCode`,
    `eDaysCode`,
    `iVolRange`,
    `skewDD`,
    `skewDN`,
    `skewAT`,
    `skewUP`,
    `skewDU`,
    `shOpnPos`,
    `fcOpnPos`,
    `cnOpnPos`,
    `cnAtmEquiv`,
    `shBot`,
    `shSld`,
    `shSldShrt`,
    `fcBot`,
    `fcSld`,
    `cnBot`,
    `cnSld`,
    `cnOpened`,
    `cnClosed`,
    `opnDir`,
    `posDir`,
    `unitOpnPos`,
    `unitCurPos`,
    `unitQtyBot`,
    `unitQtySld`,
    `dayPnl`,
    `dayDnPnl`,
    `dayTrdDelta`,
    `dayTrdDDelta`,
    `dayTrdGamma`,
    `dayTrdDGamma`,
    `dayTrdVega`,
    `dayTrdWVega`,
    `dayTrdTVega`,
    `dayTrdWtVega`,
    `dayTrdTheta`,
    `dayEdgeOpened`,
    `dayEdgeClosed`,
    `dayMnyBot`,
    `dayMnySld`,
    `opnPnlVolMark`,
    `opnPnlMidMark`,
    `opnPnlClrMark`,
    `opnMarkBrkMny`,
    `opnTheoEdge`,
    `posTheoEdge`,
    `tVolMove`,
    `tEdgeMove`,
    `tEdge`,
    `opnPnlDe`,
    `opnPnlGa`,
    `opnPnlTh`,
    `opnPnlVe`,
    `opnPnlVo`,
    `opnPnlVa`,
    `opnPnlSl`,
    `opnPnlRate`,
    `opnPnlSDiv`,
    `opnPnlDDiv`,
    `opnPnlErr`,
    `opnEdgeChange`,
    `posDelta`,
    `posDDelta`,
    `posDBeta`,
    `posGamma`,
    `posDGamma`,
    `posVega`,
    `posWVega`,
    `posTVega`,
    `posWtVega`,
    `posVolga`,
    `posVanna`,
    `posTheta`,
    `posRho`,
    `posPhi`,
    `posNotional`,
    `posMktValue`,
    `posPremOPar`,
    `atmVol`,
    `symVol`,
    `srSlope`,
    `prcSVol`,
    `prcTOpx`,
    `prcYOpx`,
    `prcUPrc`,
    `prcYears`,
    `prcRate`,
    `prcSdiv`,
    `prcDdiv`,
    `uPrcRatio`,
    `uPrcMove`,
    `iVolMove`,
    `exDivAmt`,
    `borrowRate`,
    `modelType`,
    `underliersPerCn`,
    `underlierType`,
    `pointValue`,
    `pointCurrency`,
    `tickValue`,
    `multihedge`,
    `multihedgeSource_at`,
    `multihedgeSource_ts`,
    `multihedgeSource_tk`,
    `multihedgePVRatio`,
    `spanPrcIncUp`,
    `spanPrcIncDn`,
    `spanVolInc`,
    `prcSpanType`,
    `volSpanType`,
    `spanPricingModel`,
    `marginType`,
    `timestamp`
FROM `SRRisk`.`MsgRiskCubeDetailV5`
WHERE 
    /* Replace with a VARCHAR(16) */ 
    `accnt` = 'Example_accnt'
  AND
    /* Replace with a ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') */ 
    `secKey_at` = 'None'
  AND
    /* Replace with a ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','EUX','ANY','CXE','DXE','NXAM','NXBR','NXLS','NXML','NXOS','NXP','EUREX','CEDX','ICEFE') */ 
    `secKey_ts` = 'None'
  AND
    /* Replace with a VARCHAR(12) */ 
    `secKey_tk` = 'Example_secKey_tk'
  AND
    /* Replace with a SMALLINT UNSIGNED */ 
    `secKey_yr` = 123
  AND
    /* Replace with a TINYINT UNSIGNED */ 
    `secKey_mn` = 1
  AND
    /* Replace with a TINYINT UNSIGNED */ 
    `secKey_dy` = 1
  AND
    /* Replace with a DOUBLE */ 
    `secKey_xx` = 4.56
  AND
    /* Replace with a ENUM('Call','Put','Pair') */ 
    `secKey_cp` = 'Call'
  AND
    /* Replace with a ENUM('None','Call','Put','Stock','Future','Cash') */ 
    `secType` = 'None'
  AND
    /* Replace with a DATE */
    `tradeDate` = '2022-01-01'
  AND
    /* Replace with a ENUM('Regular','PostClose') */
    `riskSession` = 'Regular'
  AND
    /* Replace with a VARCHAR(16) */ 
    `clientFirm` = 'Example_clientFirm';

Doc Columns Query

SELECT * FROM SRRisk.doccolumns WHERE TABLE_NAME='RiskCubeDetailV5' ORDER BY ordinal_position ASC;

METADATA​

Table Definition​

PRIMARY KEY DEFINITION (Unique)​

CREATE TABLE EXAMPLE QUERY​

SELECT TABLE EXAMPLE QUERY​

Doc Columns Query​