OptionCloseMark

V8 Message Definiton

OptionCloseMark records are published immediately after the market close - 5 min and again when exchanges publish official marks.\nOptionCloseMark records are published to the SpiderRock elastic cluster when clsMarkState=Final

METADATA

Attribute	Value
Topic	3120-market-marks
MLink Token	OptMarkData
Product	SRAnalytics
accessType	SELECT

Table Definition

Field	Type	Key	Default Value	Comment
okey_at	enum - AssetType	PRI	'None'
okey_ts	enum - TickerSrc	PRI	'None'
okey_tk	VARCHAR(12)	PRI	''
okey_yr	SMALLINT UNSIGNED	PRI	0
okey_mn	TINYINT UNSIGNED	PRI	0
okey_dy	TINYINT UNSIGNED	PRI	0
okey_xx	DOUBLE	PRI	0
okey_cp	enum - CallPut	PRI	'Call'
tradeDate	DATE		'1900-01-01'
clsMarkState	enum - ClsMarkState		'None'	Preview or Final
uBid	DOUBLE		0	SpiderRock closing underlier bid C 5m
uAsk	DOUBLE		0	SpiderRock closing underlier ask C 5m
uSrCls	DOUBLE		0	SpiderRock underlier closing mark C 5m
uClose	DOUBLE		0	exchange underlier closing mark
bidPrc	FLOAT		0	SpiderRock closing option bid C 5m
askPrc	FLOAT		0	SpiderRock closing option ask C 5m
srClsPrc	DOUBLE		0	SpiderRock close mark close 5min NBBO midmarket
closePrc	DOUBLE		0	official exchange closing mark last printthen official close
hasSRClsPrc	enum - YesNo		'None'
hasClosePrc	enum - YesNo		'None'
hasUClsPrc	enum - YesNo		'None'
bidIV	FLOAT		0	implied vol of SpiderRock closing bid price C 5m
askIV	FLOAT		0	implied vol of SpiderRock closing ask price C 5m
srPrc	FLOAT		0	sr close mark price always within bidPxaskPx C 5m
srVol	FLOAT		0	sr close mark volatility C 5m
srSrc	enum - MarkSource		'None'	sr close mark source SRVol is SurfaceVol
de	FLOAT		0	delta
ga	FLOAT		0	gamma
th	FLOAT		0	theta
ve	FLOAT		0	vega
vo	FLOAT		0	volga
va	FLOAT		0	vanna
rh	FLOAT		0	rho
ph	FLOAT		0	phi
srSlope	FLOAT		0	surface slope SR surface
deDecay	FLOAT		0	delta decay
sdiv	FLOAT		0	sdiv rate
ddiv	FLOAT		0	ddiv amount sum of discrete dividend amounts
ddivPv	FLOAT		0	ddiv present value amount sum of discrete dividend pv amounts
rate	FLOAT		0	discount rate
iDays	INT		0	interest days today to expiry T1
years	FLOAT		0	years to expiration
error	TINYINT UNSIGNED		0	calculation error code
openInterest	INT		0	option open Interest
prtCount	INT		0	print count
prtVolume	INT		0	total printed volume all prt types
srCloseMarkDttm	DATETIME(6)		'1900-01-01 00:00:00.000000'	from MarketCloseQuotesrCloseMarkDttm
timestamp	DATETIME(6)		'1900-01-01 00:00:00.000000'	record timestamp

PRIMARY KEY DEFINITION (Unique)

Field	Sequence
okey_tk	1
okey_yr	2
okey_mn	3
okey_dy	4
okey_xx	5
okey_cp	6
okey_at	7
okey_ts	8

CREATE TABLE EXAMPLE QUERY

CREATE TABLE `SRAnalytics`.`MsgOptionCloseMark` (
    `okey_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None',
    `okey_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',
    `okey_tk` VARCHAR(12) NOT NULL DEFAULT '',
    `okey_yr` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    `okey_mn` TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `okey_dy` TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `okey_xx` DOUBLE NOT NULL DEFAULT 0,
    `okey_cp` ENUM('Call','Put','Pair') NOT NULL DEFAULT 'Call',
    `tradeDate` DATE NOT NULL DEFAULT '1900-01-01',
    `clsMarkState` ENUM('None','Preview','Final') NOT NULL DEFAULT 'None' COMMENT 'Preview or Final',
    `uBid` DOUBLE NOT NULL DEFAULT 0 COMMENT 'SpiderRock closing underlier bid (C - 5m)',
    `uAsk` DOUBLE NOT NULL DEFAULT 0 COMMENT 'SpiderRock closing underlier ask (C - 5m)',
    `uSrCls` DOUBLE NOT NULL DEFAULT 0 COMMENT 'SpiderRock underlier closing mark (C - 5m)',
    `uClose` DOUBLE NOT NULL DEFAULT 0 COMMENT 'exchange underlier closing mark',
    `bidPrc` FLOAT NOT NULL DEFAULT 0 COMMENT 'SpiderRock closing option bid (C - 5m)',
    `askPrc` FLOAT NOT NULL DEFAULT 0 COMMENT 'SpiderRock closing option ask (C - 5m)',
    `srClsPrc` DOUBLE NOT NULL DEFAULT 0 COMMENT 'SpiderRock close mark (close - 5min) [NBBO mid-market]',
    `closePrc` DOUBLE NOT NULL DEFAULT 0 COMMENT 'official exchange closing mark (last print;then official close)',
    `hasSRClsPrc` ENUM('None','Yes','No') NOT NULL DEFAULT 'None',
    `hasClosePrc` ENUM('None','Yes','No') NOT NULL DEFAULT 'None',
    `hasUClsPrc` ENUM('None','Yes','No') NOT NULL DEFAULT 'None',
    `bidIV` FLOAT NOT NULL DEFAULT 0 COMMENT 'implied vol of SpiderRock closing bid price (C - 5m)',
    `askIV` FLOAT NOT NULL DEFAULT 0 COMMENT 'implied vol of SpiderRock closing ask price (C - 5m)',
    `srPrc` FLOAT NOT NULL DEFAULT 0 COMMENT 'sr close mark price (always within bidPx/askPx) (C - 5m)',
    `srVol` FLOAT NOT NULL DEFAULT 0 COMMENT 'sr close mark volatility (C - 5m)',
    `srSrc` ENUM('None','NbboMid','SRVol','LoBound','HiBound','Other') NOT NULL DEFAULT 'None' COMMENT 'sr close mark source (SRVol is SurfaceVol)',
    `de` FLOAT NOT NULL DEFAULT 0 COMMENT 'delta',
    `ga` FLOAT NOT NULL DEFAULT 0 COMMENT 'gamma',
    `th` FLOAT NOT NULL DEFAULT 0 COMMENT 'theta',
    `ve` FLOAT NOT NULL DEFAULT 0 COMMENT 'vega',
    `vo` FLOAT NOT NULL DEFAULT 0 COMMENT 'volga',
    `va` FLOAT NOT NULL DEFAULT 0 COMMENT 'vanna',
    `rh` FLOAT NOT NULL DEFAULT 0 COMMENT 'rho',
    `ph` FLOAT NOT NULL DEFAULT 0 COMMENT 'phi',
    `srSlope` FLOAT NOT NULL DEFAULT 0 COMMENT 'surface slope (SR surface)',
    `deDecay` FLOAT NOT NULL DEFAULT 0 COMMENT 'delta decay',
    `sdiv` FLOAT NOT NULL DEFAULT 0 COMMENT 'sdiv rate',
    `ddiv` FLOAT NOT NULL DEFAULT 0 COMMENT 'ddiv amount (sum of discrete dividend amounts)',
    `ddivPv` FLOAT NOT NULL DEFAULT 0 COMMENT 'ddiv (present value) amount (sum of discrete dividend pv amounts)',
    `rate` FLOAT NOT NULL DEFAULT 0 COMMENT 'discount rate',
    `iDays` INT NOT NULL DEFAULT 0 COMMENT 'interest days (today to expiry) (T+1)',
    `years` FLOAT NOT NULL DEFAULT 0 COMMENT 'years to expiration',
    `error` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'calculation error code',
    `openInterest` INT NOT NULL DEFAULT 0 COMMENT 'option open Interest',
    `prtCount` INT NOT NULL DEFAULT 0 COMMENT 'print count',
    `prtVolume` INT NOT NULL DEFAULT 0 COMMENT 'total printed volume (all prt types)',
    `srCloseMarkDttm` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000' COMMENT 'from MarketCloseQuote.srCloseMarkDttm',
    `timestamp` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000' COMMENT 'record timestamp',
    PRIMARY KEY USING HASH (`okey_tk`,`okey_yr`,`okey_mn`,`okey_dy`,`okey_xx`,`okey_cp`,`okey_at`,`okey_ts`)
) ENGINE=SRSE DEFAULT CHARSET=LATIN1 COMMENT='OptionCloseMark records are published immediately after the market close - 5 min and again when exchanges publish official marks.\nOptionCloseMark records are published to the SpiderRock elastic cluster when clsMarkState=Final';

SELECT TABLE EXAMPLE QUERY

SELECT
    `okey_at`,
    `okey_ts`,
    `okey_tk`,
    `okey_yr`,
    `okey_mn`,
    `okey_dy`,
    `okey_xx`,
    `okey_cp`,
    `tradeDate`,
    `clsMarkState`,
    `uBid`,
    `uAsk`,
    `uSrCls`,
    `uClose`,
    `bidPrc`,
    `askPrc`,
    `srClsPrc`,
    `closePrc`,
    `hasSRClsPrc`,
    `hasClosePrc`,
    `hasUClsPrc`,
    `bidIV`,
    `askIV`,
    `srPrc`,
    `srVol`,
    `srSrc`,
    `de`,
    `ga`,
    `th`,
    `ve`,
    `vo`,
    `va`,
    `rh`,
    `ph`,
    `srSlope`,
    `deDecay`,
    `sdiv`,
    `ddiv`,
    `ddivPv`,
    `rate`,
    `iDays`,
    `years`,
    `error`,
    `openInterest`,
    `prtCount`,
    `prtVolume`,
    `srCloseMarkDttm`,
    `timestamp`
FROM `SRAnalytics`.`MsgOptionCloseMark`
WHERE 
    /* Replace with a ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') */ 
    `okey_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','ESX','ANY','CXE','DXE','NXAM','NXBR','NXDUB','NXLS','NXLDN','NXML','NXMLT','NXOS','NXP','EUREX','CEDX','ICEFE') */ 
    `okey_ts` = 'None'
  AND
    /* Replace with a VARCHAR(12) */ 
    `okey_tk` = 'Example_okey_tk'
  AND
    /* Replace with a SMALLINT UNSIGNED */ 
    `okey_yr` = 123
  AND
    /* Replace with a TINYINT UNSIGNED */ 
    `okey_mn` = 1
  AND
    /* Replace with a TINYINT UNSIGNED */ 
    `okey_dy` = 1
  AND
    /* Replace with a DOUBLE */ 
    `okey_xx` = 4.56
  AND
    /* Replace with a ENUM('Call','Put','Pair') */ 
    `okey_cp` = 'Call';

Doc Columns Query

SELECT * FROM SRAnalytics.doccolumns WHERE TABLE_NAME='OptionCloseMark' ORDER BY ordinal_position ASC;