StockPositionRecordV5

V8 Message Definiton

StockPositionRecords are live risk records that contain start-of-day positions and all subsequent executions, including executions reported as done away. These records are published by a CoreRiskServer and represent the position and risk markup detail for a single equity or ETF security. New records are published immediately when a position changes and about once per minute if no position has changed. Note that all stock, future and option records for a single ticker are published simultaneously and records for the same chain should have consistent marks.

METADATA

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

Table Definition

Field	Type	Key	Default Value	Comment
ticker_at	enum - AssetType	PRI	'None'
ticker_ts	enum - TickerSrc	PRI	'None'
ticker_tk	VARCHAR(12)	PRI	''
accnt	VARCHAR(16)	PRI	''
tradeDate	DATE	PRI	'1900-01-01'
riskSession	enum - RiskSession	PRI	'Regular'
clientFirm	VARCHAR(16)	PRI	''	SR assigned client firm
riskServerCode	VARCHAR(6)		''
priAggGroup	VARCHAR(16)		''	primary aggregation group
secAggGroup	VARCHAR(16)		''	secondary aggregation group
symbolType	enum - SymbolType		'None'
tickValue	FLOAT		0	NLV value of a single tick change in display premium pointValue tickValue tickSize
pointValue	FLOAT		0	NLV value of a single point change in display premium pointValue tickValue tickSize
pointCurrency	enum - Currency		'None'
priceFormat	enum - PriceFormat		'None'	price display format code
stkPrc	FLOAT		0	current stock price any market session persists if market closedhalted
stkBid	FLOAT		0	current stock bid any market session zero if market closedhalted
stkAsk	FLOAT		0	current stock ask any market session zero if market closedhalted
stkMark	DOUBLE		0	current stock mark freezes at SR CloseMarkTime
stkMarkErrCodes	VARCHAR(255)		'None'
stkMarkSource	enum - UMarkSource		'None'
stkOpnMidMark	DOUBLE		0	startofday SR stock mark rotated from prior day record corp action adjusted
stkOpnClrMark	DOUBLE		0	startofday CLR stock mark supplied by clientclearing firm via clearing position load should be corp action adjusted
stkOpnPosPrv	INT		0	startofday SR share position rotated from prior day record corp action adjusted
stkOpnPosClr	INT		0	startofday CLR share position supplied by clientclearing firm via clearing position load should be corp action adjusted
stkOpnPos	INT		0	start of period share position effective can be from either CLR or SR
stkOpnPosSrc	enum - PositionSource		'None'	start of period position source
shBot	INT		0	number of shares bot today
shSld	INT		0	number of shares sld today
shSldShrt	INT		0	number of shares sld short today
shBotHdg	INT		0	number of shares bot today from hedging spdrSourceHedgeTool
shSldHdg	INT		0	number of shares sld today from hedging spdrSourceHedgeTool
shBotTrh	INT		0	number of shares bot today from hedging spdrSourceTradeHedge
shSldTrh	INT		0	number of shares sld today from hedging spdrSourceTradeHedge
shBotOpn	INT		0	number of shares bot today from hedging spdrSourceOpenHedge
shSldOpn	INT		0	number of shares sld today from hedging spdrSourceOpenHedge
shBotTrd	INT		0	number of shares bot today from any trade hedging source AutoHedge or Trades loop
shSldTrd	INT		0	number of shares sld today from any trade hedging source AutoHedge or Trades loop
stkMnyBot	DOUBLE		0	sum of settle cash for all buy executions
stkMnySld	DOUBLE		0	sum of settle cash for all sell executions
dayPnl	FLOAT		0
opnPnlMidMark	FLOAT		0
opnPnlClrMark	FLOAT		0
exDivAmt	FLOAT		0	amount of any dividend going ex today
borrowRate	FLOAT		0	overnight rate required to borrow stock if known
beta	FLOAT		0	beta usually beta to SPX see AccountConfigbetaSource
betaSource	enum - BetaSource		'None'
marginUDn	FLOAT		0	Aggregate RiskSlide uPrc dn
marginUUp	FLOAT		0	Aggregate RiskSlide uPrc up
numExecutions	INT		0	number of included SpdrParentExecution records
maxExecDttm	DATETIME(6)		'1900-01-01 00:00:00.000000'	max timestamp of included SpdrParentExecution records
timestamp	DATETIME(6)		'1900-01-01 00:00:00.000000'

PRIMARY KEY DEFINITION (Unique)

Field	Sequence
ticker_tk	1
ticker_at	2
ticker_ts	3
accnt	4
tradeDate	5
riskSession	6
clientFirm	7

CREATE TABLE EXAMPLE QUERY

CREATE TABLE `SRRisk`.`MsgStockPositionRecordV5` (
    `ticker_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None',
    `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',
    `ticker_tk` VARCHAR(12) NOT NULL DEFAULT '',
    `accnt` VARCHAR(16) NOT NULL DEFAULT '',
    `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',
    `riskServerCode` VARCHAR(6) NOT NULL DEFAULT '',
    `priAggGroup` VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'primary aggregation group',
    `secAggGroup` VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'secondary aggregation group',
    `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',
    `tickValue` FLOAT NOT NULL DEFAULT 0 COMMENT '$NLV value of a single tick change in display premium	(pointValue = tickValue / tickSize)',
    `pointValue` FLOAT NOT NULL DEFAULT 0 COMMENT '$NLV value of a single point change in display premium (pointValue = tickValue / tickSize)',
    `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',
    `priceFormat` ENUM('None','N0','N1','N2','N3','N4','N5','N6','N7','F4','F8','Q8','F16','F32','H32','Q32','F64','H64','FullPenny','PartPenny','PartNickle','EQT','V1','V2','V3','V4','V5','V6','V7','V8','V9','V10','V11','V12','V13','V14','V15','A0','A1','A2','A3','A4','A5','A6','A7','E32') NOT NULL DEFAULT 'None' COMMENT 'price display format code',
    `stkPrc` FLOAT NOT NULL DEFAULT 0 COMMENT 'current stock price (any market session) (persists if market closed/halted)',
    `stkBid` FLOAT NOT NULL DEFAULT 0 COMMENT 'current stock bid (any market session) (zero if market closed/halted)',
    `stkAsk` FLOAT NOT NULL DEFAULT 0 COMMENT 'current stock ask (any market session) (zero if market closed/halted)',
    `stkMark` DOUBLE NOT NULL DEFAULT 0 COMMENT 'current stock mark (freezes at SR CloseMarkTime)',
    `stkMarkErrCodes` VARCHAR(255) NOT NULL DEFAULT 'None',
    `stkMarkSource` ENUM('None','OpenMark','CloseMark','Print','LiveQuote','QuoteBound') NOT NULL DEFAULT 'None',
    `stkOpnMidMark` DOUBLE NOT NULL DEFAULT 0 COMMENT 'start-of-day SR stock mark (rotated from prior day record) [corp action adjusted]',
    `stkOpnClrMark` DOUBLE NOT NULL DEFAULT 0 COMMENT 'start-of-day CLR stock mark (supplied by client/clearing firm via clearing position load) [should be corp action adjusted]',
    `stkOpnPosPrv` INT NOT NULL DEFAULT 0 COMMENT 'start-of-day SR share position (rotated from prior day record) [corp action adjusted]',
    `stkOpnPosClr` INT NOT NULL DEFAULT 0 COMMENT 'start-of-day CLR share position (supplied by client/clearing firm via clearing position load) [should be corp action adjusted]',
    `stkOpnPos` INT NOT NULL DEFAULT 0 COMMENT 'start of period share position (effective; can be from either CLR or SR)',
    `stkOpnPosSrc` ENUM('None','Zero','SR','CLR') NOT NULL DEFAULT 'None' COMMENT 'start of period position source',
    `shBot` INT NOT NULL DEFAULT 0 COMMENT 'number of shares bot today',
    `shSld` INT NOT NULL DEFAULT 0 COMMENT 'number of shares sld today',
    `shSldShrt` INT NOT NULL DEFAULT 0 COMMENT 'number of shares sld short today',
    `shBotHdg` INT NOT NULL DEFAULT 0 COMMENT 'number of shares bot today from hedging (spdrSource=HedgeTool)',
    `shSldHdg` INT NOT NULL DEFAULT 0 COMMENT 'number of shares sld today from hedging (spdrSource=HedgeTool)',
    `shBotTrh` INT NOT NULL DEFAULT 0 COMMENT 'number of shares bot today from hedging (spdrSource=TradeHedge)',
    `shSldTrh` INT NOT NULL DEFAULT 0 COMMENT 'number of shares sld today from hedging (spdrSource=TradeHedge)',
    `shBotOpn` INT NOT NULL DEFAULT 0 COMMENT 'number of shares bot today from hedging (spdrSource=OpenHedge)',
    `shSldOpn` INT NOT NULL DEFAULT 0 COMMENT 'number of shares sld today from hedging (spdrSource=OpenHedge)',
    `shBotTrd` INT NOT NULL DEFAULT 0 COMMENT 'number of shares bot today from any trade hedging source (AutoHedge or Trades loop)',
    `shSldTrd` INT NOT NULL DEFAULT 0 COMMENT 'number of shares sld today from any trade hedging source (AutoHedge or Trades loop)',
    `stkMnyBot` DOUBLE NOT NULL DEFAULT 0 COMMENT 'sum of settle cash for all buy executions',
    `stkMnySld` DOUBLE NOT NULL DEFAULT 0 COMMENT 'sum of settle cash for all sell executions',
    `dayPnl` FLOAT NOT NULL DEFAULT 0,
    `opnPnlMidMark` FLOAT NOT NULL DEFAULT 0,
    `opnPnlClrMark` FLOAT NOT NULL DEFAULT 0,
    `exDivAmt` FLOAT NOT NULL DEFAULT 0 COMMENT 'amount of any dividend going ex today',
    `borrowRate` FLOAT NOT NULL DEFAULT 0 COMMENT 'overnight rate required to borrow stock (if known)',
    `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',
    `marginUDn` FLOAT NOT NULL DEFAULT 0 COMMENT 'Aggregate RiskSlide: uPrc dn',
    `marginUUp` FLOAT NOT NULL DEFAULT 0 COMMENT 'Aggregate RiskSlide: uPrc up',
    `numExecutions` INT NOT NULL DEFAULT 0 COMMENT 'number of included SpdrParentExecution records',
    `maxExecDttm` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000' COMMENT 'max (timestamp) of included SpdrParentExecution records',
    `timestamp` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000',
    PRIMARY KEY USING HASH (`ticker_tk`,`ticker_at`,`ticker_ts`,`accnt`,`tradeDate`,`riskSession`,`clientFirm`)
) ENGINE=SRSE DEFAULT CHARSET=LATIN1 COMMENT='StockPositionRecords are live risk records that contain start-of-day positions and all subsequent executions, including executions reported as done away.\nThese records are published by a CoreRiskServer and represent the position and risk markup detail for a single equity or ETF security.\nNew records are published immediately when a position changes and about once per minute if no position has changed.\nNote that all stock, future and option records for a single ticker are published simultaneously and records for the same chain should have consistent marks.';

SELECT TABLE EXAMPLE QUERY

SELECT
    `ticker_at`,
    `ticker_ts`,
    `ticker_tk`,
    `accnt`,
    `tradeDate`,
    `riskSession`,
    `clientFirm`,
    `riskServerCode`,
    `priAggGroup`,
    `secAggGroup`,
    `symbolType`,
    `tickValue`,
    `pointValue`,
    `pointCurrency`,
    `priceFormat`,
    `stkPrc`,
    `stkBid`,
    `stkAsk`,
    `stkMark`,
    `stkMarkErrCodes`,
    `stkMarkSource`,
    `stkOpnMidMark`,
    `stkOpnClrMark`,
    `stkOpnPosPrv`,
    `stkOpnPosClr`,
    `stkOpnPos`,
    `stkOpnPosSrc`,
    `shBot`,
    `shSld`,
    `shSldShrt`,
    `shBotHdg`,
    `shSldHdg`,
    `shBotTrh`,
    `shSldTrh`,
    `shBotOpn`,
    `shSldOpn`,
    `shBotTrd`,
    `shSldTrd`,
    `stkMnyBot`,
    `stkMnySld`,
    `dayPnl`,
    `opnPnlMidMark`,
    `opnPnlClrMark`,
    `exDivAmt`,
    `borrowRate`,
    `beta`,
    `betaSource`,
    `marginUDn`,
    `marginUUp`,
    `numExecutions`,
    `maxExecDttm`,
    `timestamp`
FROM `SRRisk`.`MsgStockPositionRecordV5`
WHERE 
    /* Replace with a ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') */ 
    `ticker_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') */ 
    `ticker_ts` = 'None'
  AND
    /* Replace with a VARCHAR(12) */ 
    `ticker_tk` = 'Example_ticker_tk'
  AND
    /* Replace with a VARCHAR(16) */ 
    `accnt` = 'Example_accnt'
  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='StockPositionRecordV5' ORDER BY ordinal_position ASC;