TradeCubeDetailV5

V8 Message Definiton

TradeCubeDetail records are published by CoreRiskServers and are visible in SRSE throughout the day. They are designed to be aggregated and analyzed over time. TradeCubeDetail records are published to the SpiderRock Archive Host data pipeline after all markup detail is complete.

METADATA

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

Table Definition

Field	Type	Key	Default Value	Comment
fillNumber	BIGINT	PRI	0	SpiderRock execution number globally unique over trailing 10 days
tradeDate	DATE	PRI	'1900-01-01'	trade date
runStatus	enum - RunStatus		'None'
sysEnvironment	enum - SysEnvironment		'None'
version	TINYINT UNSIGNED		0	record version number starts at zero and goes up every time an execution record is republished eg bust 1m marks 10m marks etc
clOrdId	VARCHAR(24)		''	clOrdId resulting in fill child order
parentNumber	CHAR(19)		'0000-0000-0000-0000'	SpiderRock parent number
packageId	BIGINT		0	groups related orderbrokers together eg Legger wMLeg Parent
altOrderId	VARCHAR(24)		''	alternate order ID usually clOrdId from client
srcRoutingCode	TINYTEXT		''	inbound FIX routing code if any
spdrSource	enum - SpdrSource		'None'	SpiderRock parent order source code broker pkey
groupingCode	CHAR(19)		'0000-0000-0000-0000'	SpiderRock parent broker number broker pkey
accnt	VARCHAR(16)		''	SpiderRock Accnt Code
clientFirm	VARCHAR(16)		''	SpiderRock ClientFirm Code
priAggGroup	VARCHAR(16)		''	primary aggregation group
secAggGroup	VARCHAR(16)		''	secondary aggregation group
secKey_at	enum - AssetType		'None'	security key
secKey_ts	enum - TickerSrc		'None'	security key
secKey_tk	VARCHAR(12)		''	security key
secKey_yr	SMALLINT UNSIGNED		0	security key
secKey_mn	TINYINT UNSIGNED		0	security key
secKey_dy	TINYINT UNSIGNED		0	security key
secKey_xx	DOUBLE		0	security key
secKey_cp	enum - CallPut		'Call'	security key
secType	enum - SpdrKeyType		'None'	security type Stock Future Option
ticker_at	enum - AssetType		'None'	base ticker
ticker_ts	enum - TickerSrc		'None'	base ticker
ticker_tk	VARCHAR(12)		''	base ticker
viewGroup1	VARCHAR(12)		''	viewGroup1 user supplied if any
viewGroup2	VARCHAR(12)		''	viewGroup2 user supplied if any
viewGroup3	VARCHAR(12)		''	viewGroup3 user supplied if any
riskClass	VARCHAR(8)		''	user supplied risk class code from SymbolControl record
symbolType	enum - SymbolType		'None'	symbol type
sector	VARCHAR(16)		''	user supplied sector code 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 60
expCode	enum - ExpCode		'None'	expiration tenor code
skewCode	enum - SkewCode		'None'	strike skew code
widthCode	enum - WidthCode		'None'	market width code
priceFormat	enum - PriceFormat		'None'	SpiderRock price display format code
uPriceFormat	enum - PriceFormat		'None'	SpiderRock underlier price display code
years	FLOAT		0	years to expiration
underliersPerCn	DOUBLE		0	underliers per contract futures and options
underlierType	enum - UnderlierType		'None'	underlier type affects greek calcs
tickValue	FLOAT		0	NLV value of a single tick change in display premium pointValue tickValue tickSize
pointValue	FLOAT		0	NLV value of a point
pointCurrency	enum - Currency		'None'
uPrcRatio	FLOAT		0	UPrcRatio SymbolRatio from product definition
minTickSize	FLOAT		0
routingCode	TINYTEXT		''	SpiderRock market routing code
origExecID	VARCHAR(20)		''	original execution ID string child order
lastExecID	VARCHAR(20)		''	most recent execution ID same as origExecID unless CANCELCORRECTION has been processed
transactDttm	DATETIME(6)		'1900-01-01 00:00:00.000000'	transaction datetime as reported by exchange or down stream broker
execStatus	enum - ExecStatus		'None'	SpiderRock execution status FillBustCorrectRejectSysRej
remoteText	VARCHAR(64)		''	text comment from endpoint if any
orderLimitType	enum - SpdrLimitType		'None'	parent order limit type Vol VolX PrcDe PrcDeX etc
orderLimitClass	enum - SpdrLimitClass		'Simple'	parent order limit class makeLimitClass if fill was from making takeLimitClass if fill was from taking
parentShape	enum - SpdrOrderShape		'None'
parentOrderHandling	enum - ParentOrderHandling		'None'	base parent order algo take style algo
parentBalanceHandling	enum - ParentBalanceHandling		'None'	base parent order balance handling make style algo
parentOrderSize	INT		0	parent order size
method	VARCHAR(16)		''	execution method string
strategy	VARCHAR(36)		''	client strategy usually client supplied
userName	VARCHAR(24)		''	SpiderRock user name associated with the parent order
origin	VARCHAR(16)		''	parent order origin string
source	VARCHAR(16)		''	execution source string
server	VARCHAR(20)		''	SpiderRock execution engine that handled the parent order
clearingFirm	VARCHAR(8)		''	clearing firm delivery
clearingAccnt	VARCHAR(16)		''	clearing firm account if any
lastCapacity	enum - OrderCapacity		'None'	child order capacity
execRole	enum - ExecRole		'None'	SpiderRock relationship to this execution record Direct Drop etc
execBrkrCode	VARCHAR(16)		''	SR exec broker acronym
execBrkrAccnt	VARCHAR(16)		''	account at executing brokerprime if any
execBrkrClFirm	VARCHAR(16)		''	clientfirm at executing brokerprime if any
execBrkrUserName	VARCHAR(16)		''
clearingFlipType	enum - FlipType		'None'	clearingFlip Type CMTA or Giveup
clearingFlipFirm	VARCHAR(6)		''	clearingFlip BrokerMPIDCMTA
clearingFlipAccnt	VARCHAR(10)		''	clearingFlip additional detail subaccnt
riskGroupId	CHAR(19)		'0000-0000-0000-0000'	riskGroupId parent order group ID for this execution report
fillBrkrRate	FLOAT		0	billing brokerage rate tier 1
fillRoutingRate	FLOAT		0	billing routing rate tier 1
billingSecType	enum - BillingSecType		'None'	SpiderRock billing security type
billingCategory	enum - BillingCategory		'None'	SpiderRock billing category
firmType	enum - FirmType		'None'	chld order firm type Customer ProCust Firm MM etc
side	enum - BuySell		'None'	parentchild order side
priceType	enum - PriceType		'None'	order limit price type
ssaleFlag	enum - ShortSaleFlag		'None'	child order short sale flag
spdrSSaleFlag	enum - ShortSaleFlag		'None'	parent order short sale flag
positionType	enum - PositionType		'None'	child order position type
autoHedge	enum - AutoHedge		'None'	SpiderRock autohedge algorithm if any
orderDttm	DATETIME(6)		'1900-01-01 00:00:00.000000'	Datetime of parent order arrival
orderUBid	DOUBLE		0	underlier market bid parent order arrival
orderUAsk	DOUBLE		0	underlier market ask parent order arrival
orderBid	DOUBLE		0	option market bid parent order arrival options only
orderAsk	DOUBLE		0	option market ask parent order arrival options only
orderSurfPrc	FLOAT		0	SpiderRock surface price parent order arrival options only
orderSurfVol	FLOAT		0
orderLimitVol	FLOAT		0	parent order limit volatilty options only
orderLimitPrc	DOUBLE		0	parent order limit price parent order arrival
orderLimitRefUPrc	DOUBLE		0	limit reference underlier price parent order arrival options only
childShape	enum - SpdrOrderShape		'None'
childSize	INT		0	child order size
childPrice	DOUBLE		0	child order price
childExch	VARCHAR(6)		''	child order exchange code SpiderRock
childExDest	VARCHAR(16)		''	child order exchange code downstream
childDttm	DATETIME(6)		'1900-01-01 00:00:00.000000'	child order send datetime
childUBid	DOUBLE		0	underlier market bid child order send time
childUAsk	DOUBLE		0	underlier market bid child order send time
childBid	DOUBLE		0	option market bid child order send time options only
childAsk	DOUBLE		0	option market ask child order send time options only
childSurfPrc	FLOAT		0	SpiderRock surface price child order send time options only
childVol	FLOAT		0	child order volatilty options only
childProb	FLOAT		0	child order probability Tx
childLimitPrc	DOUBLE		0	parent order limit price child order send time
childLimitRefUPrc	DOUBLE		0	limit reference underlier price child order send time options only
childLimitPnl	FLOAT		0	child limit improvement pnl childLimitPrc to fillPrice
childAlgoHandler	enum - ChildHandler		'None'	algo that generated the child order responsible for this fill
childOrderHandling	VARCHAR(24)		''	algo that generated the child order responsible for this fill
childCreateReason	enum - ChildCreateReason		'None'	child order create reason code
childCancelReason	enum - ChildCancelReason		'None'	child order cancel reason code
childMktStance	enum - MktStance		'None'	child order was expected to be marketable child send time
childCxlAttempted	enum - YesNo		'None'	cancel attempt was made on the child order prior to receiving this fill
childCxlFillLatency	FLOAT		0	cancel latency for fills with a cancel attempt
fillExch	VARCHAR(12)		''	ExDest code from child order execution report lastMkt
fillPrice	DOUBLE		0	fill price
fillTs	DOUBLE		0	fill report ts seconds since startup 100 nanosec resolution
fillDttm	DATETIME(6)		'1900-01-01 00:00:00.000000'	Datetime of fill arrival
fillSize	INT		0	fill quantity
effFillSize	INT		0	fill size x underliers per contract
fillVol	FLOAT		0	fill volatilty options only
fillProb	FLOAT		0	fill order probability Tx
fillLimitPrc	DOUBLE		0	parent order limit price fill arrival time
fillLimitRefUPrc	DOUBLE		0	limit reference underlier price fill arrival time options only
fillLimitPnl	FLOAT		0	fill limit improvement pnl fillLimitPrc to fillPrice
fillDe	FLOAT		0	fill delta
fillVe	FLOAT		0	fill vega
fillTh	FLOAT		0	fill theta
fillUPrc	DOUBLE		0	fill underlier midmarket
fillBeta	FLOAT		0	SpiderRock estimate of beta to SPX
fillTv	FLOAT		0	theo vol user supplied
fillTp	FLOAT		0	theo price
fillUBid	DOUBLE		0	underlier market bid fill arrival
fillUAsk	DOUBLE		0	underlier market ask fill arrival
fillBid	DOUBLE		0	option market bid fill arrival
fillAsk	DOUBLE		0	option market ask fill arrival
fillSurfPrc	FLOAT		0	SpiderRock surface price fill arrival
fillPostUMark1M	DOUBLE		0	underlier mark fill arrival 1M
fillPostUMark10M	DOUBLE		0	underlier mark fill arrival 10M
fillPostMark1M	DOUBLE		0	SpiderRock mid or surface mark fill arrival 1M
fillPostMark10M	DOUBLE		0	SpiderRock mid or surface mark fill arrival 10M
fillRiskMetric1	FLOAT		0	user defined from parent order
fillRiskMetric2	FLOAT		0	user defined from parent order
fillRiskMetric3	FLOAT		0	user defined from parent order
fillRiskMetric4	FLOAT		0	user defined from parent order
fillRiskMetric5	FLOAT		0	user defined from parent order
fillRiskMetric6	FLOAT		0	user defined from parent order
fillRiskMetric7	FLOAT		0	user defined from parent order
fillS15Dn	FLOAT		0	underlier down 15 shock slide
fillS15Up	FLOAT		0	underlier up 15 shock slide
marginUDnVDn	FLOAT		0	Aggregate RiskSlide uPrc dn vol dn
marginUDnVUp	FLOAT		0	Aggregate RiskSlide uPrc dn vol up
marginUUpVDn	FLOAT		0	Aggregate RiskSlide uPrc up vol dn
marginUUpVUp	FLOAT		0	Aggregate RiskSlide uPrc up vol up
riskCode	enum - RiskCode		'None'	SpiderRock Risk Code if any
exchLiquidityTag	VARCHAR(4)		''	liquidity tag exchange
spdrLiquidityTag	VARCHAR(2)		''	SpiderRock normalized liquidity tag
fillExchDetail	TINYTEXT		''	other fix tags tagvaluetagvalue additional detail used for analysis
notionalValue	FLOAT		0	cn mult uPrc
marketValue	FLOAT		0	cn mult fillPrice
orderMktWidth	FLOAT		0	market width parent order arrival
childMktWidth	FLOAT		0	market width child order send time
fillMktWidth	FLOAT		0	market width fill arrival
parentOrderAge	FLOAT		0	elaspsed time from parent order entryupdate to this fill event
parentAgeCode	enum - ParentAge		'None'	elaspsed time from parent order entryupdate to this fill event coded
trdDelta	FLOAT		0	trade delta
trdDDelta	FLOAT		0	trade delta
trdGamma	FLOAT		0	trade gamma
trdVega	FLOAT		0	trade vega
trdWtVega	FLOAT		0	trade wtVega vega vol sqrtmax01 years 4
trdTheta	FLOAT		0	trade theta
trdPrem	FLOAT		0	trade premium over parity
trdBeta	FLOAT		0	trade beta
trdDBeta	FLOAT		0	trade beta
surfEdge	FLOAT		0	SR surface edge user supplied theo volprc to SpiderRock surface price
surfEdgeUnit	FLOAT		0	SR surface edge normalized to 100 underlier
theoEdge	FLOAT		0	theo edge user supplied theo volprc to SpiderRock surface price
theoEdgeUnit	FLOAT		0	theo edge normalized to 100 underlier
exchFee	FLOAT		0	SpiderRock exchange fee estimate
halfWidthPnl	FLOAT		0	estimated halfwidth Pnl
clArrivePnl	FLOAT		0	dir qty fillPrice clArriveMark maybe use orderSurfPrc instead
arrivePnl	FLOAT		0	dir qty fillPrice 05 orderBid orderAsk maybe use orderSurfPrc instead
arriveDnPnl	FLOAT		0	arrivePnl uDriftArrivePnl
uDriftArrivePnl	FLOAT		0	dir qty fillDe 05 fillUBid fillUAsk 05 orderUBid orderUAsk
dayDnPnl	FLOAT		0	delta neutral Pnl to EOD
dayM1Pnl	FLOAT		0	delta neutral Pnl Fill 1M
dayM10Pnl	FLOAT		0	delta neutral Pnl Fill 10M
uDriftDayPnl	FLOAT		0	underlier delta drift Pnl to EOD
uDriftM1Pnl	FLOAT		0	underlier delta drift Pnl Fill 1M
uDriftM10Pnl	FLOAT		0	underlier delta drift Pnl Fill 10M
dayPnl	FLOAT		0	actual Pnl to EOD
liveMark	DOUBLE		0	SpiderRock internal use
liveUPrc	DOUBLE		0	SpiderRock internal use
pendMark	DOUBLE		0	SpiderRock internal use
pendUPrc	DOUBLE		0	SpiderRock internal use
trdMarkError	VARCHAR(8)		''	trade mark error code
dayMarkError	VARCHAR(8)		''	day mark error code
userData1	TINYTEXT		''	client supplied data field passes through to parent and child executions and reports as well as FIX drops
userData2	TINYTEXT		''	client supplied data field passes through to parent and child executions and reports as well as FIX drops
timestamp	DATETIME(6)		'1900-01-01 00:00:00.000000'	SR system timestamp

PRIMARY KEY DEFINITION (Unique)

Field	Sequence
fillNumber	1
tradeDate	2

SELECT TABLE EXAMPLE QUERY

SELECT *
FROM `SRRisk`.`MsgTradeCubeDetailV5`
WHERE 
    /* Replace with a BIGINT */ 
    `fillNumber` = 1234567890
  AND
    /* Replace with a DATE */
    `tradeDate` = '2022-01-01';

Doc Columns Query

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