Skip to main content
Version: 8.4.08.4

OptExpiryDefinition

V8 Message Definiton

This table maps option root/expiration combinations to their deliverable future. Mappings are sourced fromm listing exchange product definitions.\nBaseObj:Root

METADATA

AttributeValue
Topic4335-product-definition
MLink TokenOptionDefinition
ProductSRAnalytics
accessTypeSELECT

Table Definition

FieldTypeKeyDefault ValueComment
ekey_atenum - AssetTypePRI'None'option rootexpiration
ekey_tsenum - TickerSrcPRI'None'option rootexpiration
ekey_tkVARCHAR(12)PRI''option rootexpiration
ekey_yrSMALLINT UNSIGNEDPRI0option rootexpiration
ekey_mnTINYINT UNSIGNEDPRI0option rootexpiration
ekey_dyTINYINT UNSIGNEDPRI0option rootexpiration
ticker_atenum - AssetType'None'master ticker
ticker_tsenum - TickerSrc'None'master ticker
ticker_tkVARCHAR(12)''master ticker
fkey_atenum - AssetType'None'settlement future if any
fkey_tsenum - TickerSrc'None'settlement future if any
fkey_tkVARCHAR(12)''settlement future if any
fkey_yrSMALLINT UNSIGNED0settlement future if any
fkey_mnTINYINT UNSIGNED0settlement future if any
fkey_dyTINYINT UNSIGNED0settlement future if any
uPrcDriverKey_atenum - AssetType'None'underlier price driver for this option expiry default is fkey if it exists
uPrcDriverKey_tsenum - TickerSrc'None'underlier price driver for this option expiry default is fkey if it exists
uPrcDriverKey_tkVARCHAR(12)''underlier price driver for this option expiry default is fkey if it exists
uPrcDriverKey_yrSMALLINT UNSIGNED0underlier price driver for this option expiry default is fkey if it exists
uPrcDriverKey_mnTINYINT UNSIGNED0underlier price driver for this option expiry default is fkey if it exists
uPrcDriverKey_dyTINYINT UNSIGNED0underlier price driver for this option expiry default is fkey if it exists
uPrcDriverKeyTypeenum - SpdrKeyType'None'Stock or Future
uPrcBoundFKey_atenum - AssetType'None'underlier price bounding future if any for this option expiry
uPrcBoundFKey_tsenum - TickerSrc'None'underlier price bounding future if any for this option expiry
uPrcBoundFKey_tkVARCHAR(12)''underlier price bounding future if any for this option expiry
uPrcBoundFKey_yrSMALLINT UNSIGNED0underlier price bounding future if any for this option expiry
uPrcBoundFKey_mnTINYINT UNSIGNED0underlier price bounding future if any for this option expiry
uPrcBoundFKey_dyTINYINT UNSIGNED0underlier price bounding future if any for this option expiry
expirationDATETIME(6)'1900-01-01 00:00:00.000000'option expiration date and time
maturityDateDATETIME(6)'1900-01-01 00:00:00.000000'
displayFactorDOUBLE0
cabPriceDOUBLE0
priceFormatenum - PriceFormat'None'
minTickSizeDOUBLE0
timestampDATETIME(6)'1900-01-01 00:00:00.000000'

PRIMARY KEY DEFINITION (Unique)

FieldSequence
ekey_tk1
ekey_yr2
ekey_mn3
ekey_dy4
ekey_at5
ekey_ts6

CREATE TABLE EXAMPLE QUERY

CREATE TABLE `SRAnalytics`.`MsgOptExpiryDefinition` (
`ekey_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None' COMMENT 'option root+expiration',
`ekey_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 'option root+expiration',
`ekey_tk` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'option root+expiration',
`ekey_yr` SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'option root+expiration',
`ekey_mn` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'option root+expiration',
`ekey_dy` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'option root+expiration',
`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 'master ticker',
`ticker_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 'master ticker',
`ticker_tk` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'master ticker',
`fkey_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None' COMMENT 'settlement future (if any)',
`fkey_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 'settlement future (if any)',
`fkey_tk` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'settlement future (if any)',
`fkey_yr` SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'settlement future (if any)',
`fkey_mn` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'settlement future (if any)',
`fkey_dy` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'settlement future (if any)',
`uPrcDriverKey_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None' COMMENT 'underlier price driver for this option expiry (default is fkey; if it exists)',
`uPrcDriverKey_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 'underlier price driver for this option expiry (default is fkey; if it exists)',
`uPrcDriverKey_tk` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'underlier price driver for this option expiry (default is fkey; if it exists)',
`uPrcDriverKey_yr` SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'underlier price driver for this option expiry (default is fkey; if it exists)',
`uPrcDriverKey_mn` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'underlier price driver for this option expiry (default is fkey; if it exists)',
`uPrcDriverKey_dy` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'underlier price driver for this option expiry (default is fkey; if it exists)',
`uPrcDriverKeyType` ENUM('None','Stock','Future','Option','MLeg') NOT NULL DEFAULT 'None' COMMENT 'Stock or Future',
`uPrcBoundFKey_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None' COMMENT 'underlier price bounding future (if any) for this option expiry',
`uPrcBoundFKey_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 'underlier price bounding future (if any) for this option expiry',
`uPrcBoundFKey_tk` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'underlier price bounding future (if any) for this option expiry',
`uPrcBoundFKey_yr` SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'underlier price bounding future (if any) for this option expiry',
`uPrcBoundFKey_mn` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'underlier price bounding future (if any) for this option expiry',
`uPrcBoundFKey_dy` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'underlier price bounding future (if any) for this option expiry',
`expiration` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000' COMMENT 'option expiration date and time',
`maturityDate` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000',
`displayFactor` DOUBLE NOT NULL DEFAULT 0,
`cabPrice` DOUBLE NOT NULL DEFAULT 0,
`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',
`minTickSize` DOUBLE NOT NULL DEFAULT 0,
`timestamp` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000',
PRIMARY KEY USING HASH (`ekey_tk`,`ekey_yr`,`ekey_mn`,`ekey_dy`,`ekey_at`,`ekey_ts`)
) ENGINE=SRSE DEFAULT CHARSET=LATIN1 COMMENT='This table maps option root/expiration combinations to their deliverable future. Mappings are sourced fromm listing exchange product definitions.\nBaseObj:Root';

SELECT TABLE EXAMPLE QUERY

SELECT
`ekey_at`,
`ekey_ts`,
`ekey_tk`,
`ekey_yr`,
`ekey_mn`,
`ekey_dy`,
`ticker_at`,
`ticker_ts`,
`ticker_tk`,
`fkey_at`,
`fkey_ts`,
`fkey_tk`,
`fkey_yr`,
`fkey_mn`,
`fkey_dy`,
`uPrcDriverKey_at`,
`uPrcDriverKey_ts`,
`uPrcDriverKey_tk`,
`uPrcDriverKey_yr`,
`uPrcDriverKey_mn`,
`uPrcDriverKey_dy`,
`uPrcDriverKeyType`,
`uPrcBoundFKey_at`,
`uPrcBoundFKey_ts`,
`uPrcBoundFKey_tk`,
`uPrcBoundFKey_yr`,
`uPrcBoundFKey_mn`,
`uPrcBoundFKey_dy`,
`expiration`,
`maturityDate`,
`displayFactor`,
`cabPrice`,
`priceFormat`,
`minTickSize`,
`timestamp`
FROM `SRAnalytics`.`MsgOptExpiryDefinition`
WHERE
/* Replace with a ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') */
`ekey_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') */
`ekey_ts` = 'None'
AND
/* Replace with a VARCHAR(12) */
`ekey_tk` = 'Example_ekey_tk'
AND
/* Replace with a SMALLINT UNSIGNED */
`ekey_yr` = 123
AND
/* Replace with a TINYINT UNSIGNED */
`ekey_mn` = 1
AND
/* Replace with a TINYINT UNSIGNED */
`ekey_dy` = 1;

Doc Columns Query

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