Skip to main content
Version: Staging

RootDefinition

V8 Message Definiton

RootDefinition records are sourced from the listing exchange for future options and from OCC for US equity options. Records are updated as SpiderRock receives changes.

METADATA

AttributeValue
Topic4335-product-definition
MLink TokenOptionDefinition
ProductSRAnalytics
accessTypeSELECT

Table Definition

FieldTypeKeyDefault ValueComment
root_atenum - AssetTypePRI'None'
root_tsenum - TickerSrcPRI'None'
root_tkVARCHAR(12)PRI''
ticker_atenum - AssetType'None'master underlying can be a stockproduct group eg ES
ticker_tsenum - TickerSrc'None'master underlying can be a stockproduct group eg ES
ticker_tkVARCHAR(12)SEC''master underlying can be a stockproduct group eg ES
osiRootVARCHAR(8)SEC''long version of the root the short version is used in the TickerKey for example RYAAY1 not RYAA1
ccode_atenum - AssetType'None'
ccode_tsenum - TickerSrc'None'
ccode_tkVARCHAR(12)SEC''
uPrcDriverKey_atenum - AssetType'None'optional option underlier price driver all option expiries overrides optExpiryDefinition
uPrcDriverKey_tsenum - TickerSrc'None'optional option underlier price driver all option expiries overrides optExpiryDefinition
uPrcDriverKey_tkVARCHAR(12)''optional option underlier price driver all option expiries overrides optExpiryDefinition
uPrcDriverKey_yrSMALLINT UNSIGNED0optional option underlier price driver all option expiries overrides optExpiryDefinition
uPrcDriverKey_mnTINYINT UNSIGNED0optional option underlier price driver all option expiries overrides optExpiryDefinition
uPrcDriverKey_dyTINYINT UNSIGNED0optional option underlier price driver all option expiries overrides optExpiryDefinition
uPrcDriverTypeenum - SpdrKeyType'None'Stock or Future note if Future and uPrcDriverKey does not have an expiry month then FrontMonth will be used
uPrcDriverKey2_atenum - AssetType'None'optional alternate option underlier price driver all option expiries overrides optExpiryDefinition
uPrcDriverKey2_tsenum - TickerSrc'None'optional alternate option underlier price driver all option expiries overrides optExpiryDefinition
uPrcDriverKey2_tkVARCHAR(12)''optional alternate option underlier price driver all option expiries overrides optExpiryDefinition
uPrcDriverKey2_yrSMALLINT UNSIGNED0optional alternate option underlier price driver all option expiries overrides optExpiryDefinition
uPrcDriverKey2_mnTINYINT UNSIGNED0optional alternate option underlier price driver all option expiries overrides optExpiryDefinition
uPrcDriverKey2_dyTINYINT UNSIGNED0optional alternate option underlier price driver all option expiries overrides optExpiryDefinition
uPrcDriverType2enum - SpdrKeyType'None'Stock or Future note if Future and uPrcDriverKey does not have an expiry month then FrontMonth will be used
uPrcBoundCCodeenum - YesNo'None'if Yes and if a future exists with ccodeCCode and futExpiry optExpiry the use this future as a pricing bound
expirationMapenum - ExpirationMap'None'determines the underlying future if any
underlierModeenum - UnderlierMode'None'
optionTypeenum - OptionType'None'indicator for option type
multihedgeenum - Multihedge'None'indicates type of multihedge None standard root all other root types are not None
exerciseTimeenum - ExerciseTime'None'Exercise time type
exerciseTypeenum - ExerciseType'None'Exercise style
timeMetricenum - TimeMetric'None'trading time metric 252 or 365 trading days or a weekly cycle type
pricingModelenum - PricingModel'None'
moneynessTypeenum - MoneynessType'None'moneyness xAxis convention PctStd K fUPrc 1 axisVol RT LogStd LOGKfUPrc axisVol RT NormStd K fUPrc axisVol RT
priceQuoteTypeenum - PriceQuoteType'None'quoting style for the option series on the exchange price standard price quote or volatility quoted vol points
volumeTierenum - VolumeTier'None'
positionLimitINT0max contract limit
exchangesVARCHAR(24)''exchange codes
tickValueFLOAT0NLV value of a single tick change in display premium pointValue tickValue tickSize
pointValueFLOAT0NLV value of a single point change in display premium pointValue tickValue tickSize
pointCurrencyenum - Currency'None'
strikeScaleDOUBLE0manual strike price adjustment multiplier used for some CME products if set otherwise displayFactor is used okeyxx strikePrice manualStrikeScale
strikeRatioFLOAT0note effective strike strike strikeRatio cashOnExercise
cashOnExerciseFLOAT0note cashOnExercise is positive if it decreases the effective strike price
underliersPerCnINT0note always 100 if underlying list is in use
premiumMultDOUBLE0note OCC premiumstrike multiplier usually 100
symbolRatioFLOAT0note currently used when AdjConvention is None value of 0 implies symbolRatio is 1
adjConventionenum - AdjConvention'None'
optPriceIncenum - OptPriceInc'None'
priceFormatenum - PriceFormat'None'price display format
tradeCurrenum - Currency'None'
settleCurrenum - Currency'None'
strikeCurrenum - Currency'None'
defaultSurfaceRoot_atenum - AssetType'None'fallback ticker to use for option surfaces if no native surfaces are available
defaultSurfaceRoot_tsenum - TickerSrc'None'fallback ticker to use for option surfaces if no native surfaces are available
defaultSurfaceRoot_tkVARCHAR(12)''fallback ticker to use for option surfaces if no native surfaces are available
ricRootVARCHAR(6)''RIC Root
timestampDATETIME(6)'1900-01-01 00:00:00.000000'
ExchangeListJSON'JSON_OBJECT()'
UnderlyingListJSON'JSON_OBJECT()'

PRIMARY KEY DEFINITION (Unique)

FieldSequence
root_tk1
root_at2
root_ts3

SECONDARY INDEX (CCodeIndex) (Not Unique)

FieldSequence
ccode_tk1

SECONDARY INDEX (OSIRootIndex) (Not Unique)

FieldSequence
osiRoot1

SECONDARY INDEX (TickerIndex) (Not Unique)

FieldSequence
ticker_tk1

JSON Block (ExchangeList)

FieldTypeComment
optExchenum - optExch
nativeRootstring

JSON Block (UnderlyingList)

FieldTypeComment
tickerTickerKey
spcfloatnote root basket sumspc ticker 100

CREATE TABLE EXAMPLE QUERY

CREATE TABLE `SRAnalytics`.`MsgRootDefinition` (
`root_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None',
`root_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',
`root_tk` VARCHAR(12) NOT NULL DEFAULT '',
`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 underlying (can be a stock/product group; eg. @ES)',
`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 underlying (can be a stock/product group; eg. @ES)',
`ticker_tk` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'master underlying (can be a stock/product group; eg. @ES)',
`osiRoot` VARCHAR(8) NOT NULL DEFAULT '' COMMENT 'long version of the root. the short version is used in the TickerKey (for example RYAAY1, not RYAA1)',
`ccode_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None',
`ccode_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',
`ccode_tk` VARCHAR(12) NOT NULL DEFAULT '',
`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 '(optional) option underlier price driver (all option expiries) (overrides optExpiryDefinition)',
`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 '(optional) option underlier price driver (all option expiries) (overrides optExpiryDefinition)',
`uPrcDriverKey_tk` VARCHAR(12) NOT NULL DEFAULT '' COMMENT '(optional) option underlier price driver (all option expiries) (overrides optExpiryDefinition)',
`uPrcDriverKey_yr` SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '(optional) option underlier price driver (all option expiries) (overrides optExpiryDefinition)',
`uPrcDriverKey_mn` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '(optional) option underlier price driver (all option expiries) (overrides optExpiryDefinition)',
`uPrcDriverKey_dy` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '(optional) option underlier price driver (all option expiries) (overrides optExpiryDefinition)',
`uPrcDriverType` ENUM('None','Stock','Future','Option','MLeg') NOT NULL DEFAULT 'None' COMMENT 'Stock or Future (note: if Future and uPrcDriverKey does not have an expiry month then FrontMonth will be used)',
`uPrcDriverKey2_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None' COMMENT '(optional) alternate option underlier price driver (all option expiries) (overrides optExpiryDefinition)',
`uPrcDriverKey2_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 '(optional) alternate option underlier price driver (all option expiries) (overrides optExpiryDefinition)',
`uPrcDriverKey2_tk` VARCHAR(12) NOT NULL DEFAULT '' COMMENT '(optional) alternate option underlier price driver (all option expiries) (overrides optExpiryDefinition)',
`uPrcDriverKey2_yr` SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '(optional) alternate option underlier price driver (all option expiries) (overrides optExpiryDefinition)',
`uPrcDriverKey2_mn` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '(optional) alternate option underlier price driver (all option expiries) (overrides optExpiryDefinition)',
`uPrcDriverKey2_dy` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '(optional) alternate option underlier price driver (all option expiries) (overrides optExpiryDefinition)',
`uPrcDriverType2` ENUM('None','Stock','Future','Option','MLeg') NOT NULL DEFAULT 'None' COMMENT 'Stock or Future (note: if Future and uPrcDriverKey does not have an expiry month then FrontMonth will be used)',
`uPrcBoundCCode` ENUM('None','Yes','No') NOT NULL DEFAULT 'None' COMMENT 'if Yes and if a future exists with ccode=CCode and futExpiry = optExpiry the use this future as a pricing bound',
`expirationMap` ENUM('None','ExactMatch','UnderlierMap') NOT NULL DEFAULT 'None' COMMENT 'determines the underlying future (if any)',
`underlierMode` ENUM('None','Actual','FrontMonth','UPrcAdj') NOT NULL DEFAULT 'None',
`optionType` ENUM('None','Equity','Index','Future','Binary','Warrant','Flex','MapError') NOT NULL DEFAULT 'None' COMMENT 'indicator for option type',
`multihedge` ENUM('None','Simple','Complex','AllCash','Binary','Fragment') NOT NULL DEFAULT 'None' COMMENT 'indicates type of multihedge (None = standard root; all other root types are not None)',
`exerciseTime` ENUM('None','PM','AM') NOT NULL DEFAULT 'None' COMMENT 'Exercise time type',
`exerciseType` ENUM('None','American','European','Asian','Cliquet') NOT NULL DEFAULT 'None' COMMENT 'Exercise style',
`timeMetric` ENUM('None','D252','D365','SPX','WK1','WK2','WK3','WK4') NOT NULL DEFAULT 'None' COMMENT 'trading time metric - 252 or 365 trading days or a weekly cycle type',
`pricingModel` ENUM('None','Equity','FutureApprox','FutureExact','NormalApprox','NormalExact') NOT NULL DEFAULT 'None',
`moneynessType` ENUM('None','PctStd','LogStd','NormStd') NOT NULL DEFAULT 'None' COMMENT 'moneyness (xAxis) convention: PctStd = (K / fUPrc - 1) / (axisVol * RT), LogStd = LOG(K/fUPrc) / (axisVol * RT), NormStd = (K - fUPrc) / (axisVol * RT)',
`priceQuoteType` ENUM('None','Price','Vol') NOT NULL DEFAULT 'None' COMMENT 'quoting style for the option series on the exchange, price (standard price quote) or volatility quoted (vol points)',
`volumeTier` ENUM('None','Top50') NOT NULL DEFAULT 'None',
`positionLimit` INT NOT NULL DEFAULT 0 COMMENT 'max contract limit',
`exchanges` VARCHAR(24) NOT NULL DEFAULT '' COMMENT 'exchange codes',
`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',
`strikeScale` DOUBLE NOT NULL DEFAULT 0 COMMENT 'manual strike price adjustment multiplier (used for some CME products if set, otherwise displayFactor is used) (okey_xx = strikePrice * manualStrikeScale)',
`strikeRatio` FLOAT NOT NULL DEFAULT 0 COMMENT 'note: effective strike = strike * strikeRatio - cashOnExercise',
`cashOnExercise` FLOAT NOT NULL DEFAULT 0 COMMENT 'note: cashOnExercise is positive if it decreases the effective strike price',
`underliersPerCn` INT NOT NULL DEFAULT 0 COMMENT 'note: always 100 if underlying list is in use',
`premiumMult` DOUBLE NOT NULL DEFAULT 0 COMMENT 'note: OCC premium/strike multiplier (usually 100)',
`symbolRatio` FLOAT NOT NULL DEFAULT 0 COMMENT 'note: currently used when AdjConvention is None, value of 0 implies symbolRatio is 1',
`adjConvention` ENUM('None','Original','OSI','SpcOnly','OSIAlt') NOT NULL DEFAULT 'None',
`optPriceInc` ENUM('None','PartPenny','PartNickle','FullPenny') 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',
`tradeCurr` 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',
`settleCurr` 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',
`strikeCurr` 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',
`defaultSurfaceRoot_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None' COMMENT 'fallback ticker to use for option surfaces if no native surfaces are available',
`defaultSurfaceRoot_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 'fallback ticker to use for option surfaces if no native surfaces are available',
`defaultSurfaceRoot_tk` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'fallback ticker to use for option surfaces if no native surfaces are available',
`ricRoot` VARCHAR(6) NOT NULL DEFAULT '' COMMENT 'RIC Root',
`timestamp` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000',
`ExchangeList` JSON NOT NULL DEFAULT JSON_OBJECT() CHECK(JSON_VALID(ExchangeList)),
`UnderlyingList` JSON NOT NULL DEFAULT JSON_OBJECT() CHECK(JSON_VALID(UnderlyingList)),
PRIMARY KEY USING HASH (`root_tk`,`root_at`,`root_ts`),
KEY `CCodeIndex` (`ccode_tk`) USING HASH,
KEY `OSIRootIndex` (`osiRoot`) USING HASH,
KEY `TickerIndex` (`ticker_tk`) USING HASH
) ENGINE=SRSE DEFAULT CHARSET=LATIN1 COMMENT='RootDefinition records are sourced from the listing exchange for future options and from OCC for US equity options. Records are updated as SpiderRock receives changes.';

SELECT TABLE EXAMPLE QUERY

SELECT
`root_at`,
`root_ts`,
`root_tk`,
`ticker_at`,
`ticker_ts`,
`ticker_tk`,
`osiRoot`,
`ccode_at`,
`ccode_ts`,
`ccode_tk`,
`uPrcDriverKey_at`,
`uPrcDriverKey_ts`,
`uPrcDriverKey_tk`,
`uPrcDriverKey_yr`,
`uPrcDriverKey_mn`,
`uPrcDriverKey_dy`,
`uPrcDriverType`,
`uPrcDriverKey2_at`,
`uPrcDriverKey2_ts`,
`uPrcDriverKey2_tk`,
`uPrcDriverKey2_yr`,
`uPrcDriverKey2_mn`,
`uPrcDriverKey2_dy`,
`uPrcDriverType2`,
`uPrcBoundCCode`,
`expirationMap`,
`underlierMode`,
`optionType`,
`multihedge`,
`exerciseTime`,
`exerciseType`,
`timeMetric`,
`pricingModel`,
`moneynessType`,
`priceQuoteType`,
`volumeTier`,
`positionLimit`,
`exchanges`,
`tickValue`,
`pointValue`,
`pointCurrency`,
`strikeScale`,
`strikeRatio`,
`cashOnExercise`,
`underliersPerCn`,
`premiumMult`,
`symbolRatio`,
`adjConvention`,
`optPriceInc`,
`priceFormat`,
`tradeCurr`,
`settleCurr`,
`strikeCurr`,
`defaultSurfaceRoot_at`,
`defaultSurfaceRoot_ts`,
`defaultSurfaceRoot_tk`,
`ricRoot`,
`timestamp`,
`ExchangeList`,
`UnderlyingList`
FROM `SRAnalytics`.`MsgRootDefinition`
WHERE
/* Replace with a ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') */
`root_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') */
`root_ts` = 'None'
AND
/* Replace with a VARCHAR(12) */
`root_tk` = 'Example_root_tk';

Doc Columns Query

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