LiveImpliedQuote
CalcSource=Tick records are computed and published each time an option NBBO price changes. CalcSource=Loop records are computed in a 2-3 minute background loop.\nNote that the underlier price (uPrc) will be the same for all options an underlier when CalcSource=Loop. This is not true for CalcSource=Tick where uPrc will be the underlier price that prevailed when the option price changed.\nIf you are consuming multicast data and only want records with consistent uPrc values for all options you should ignore Tick records. Alternatively, you can use an independent underlier price source (our StockBookQuote feed or some other) and 'adjust' the values in this table to the new underlier value.\nIf you are selecting records from SRSE you should note that OptionImpliedQuoteAdj table is a proxy implementation of this table that automatically applies the appropriate underlier adjustments as records are being returned.
METADATA
Attribute | Value |
---|---|
Topic | 1000-analytics |
MLink Token | OptAnalytics |
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, SEC | 0 | |
okey_mn | TINYINT UNSIGNED | PRI, SEC | 0 | |
okey_dy | TINYINT UNSIGNED | PRI, SEC | 0 | |
okey_xx | DOUBLE | PRI | 0 | |
okey_cp | enum - CallPut | PRI | 'Call' | |
ticker_at | enum - AssetType | 'None' | SR Ticker that this option rolls up to | |
ticker_ts | enum - TickerSrc | 'None' | SR Ticker that this option rolls up to | |
ticker_tk | VARCHAR(12) | SEC | '' | SR Ticker that this option rolls up to |
uPrc | FLOAT | 0 | underlier price usually midmarket | |
uOff | FLOAT | 0 | implied underlier price offset if any | |
years | FLOAT | 0 | years to expiration | |
xAxis | FLOAT | 0 | option moneyness | |
rate | FLOAT | 0 | discount rate | |
sdiv | FLOAT | 0 | sdiv continuous stock dividend rate | |
ddiv | FLOAT | 0 | cumulative discrete dividend value | |
oBid | FLOAT | 0 | option bid price | |
oAsk | FLOAT | 0 | option ask price | |
oBidIv | FLOAT | 0 | volatility implied by option bid price | |
oAskIv | FLOAT | 0 | volatility implied by option ask price | |
atmVol | FLOAT | 0 | option atm volatility from SR surface | |
sVol | FLOAT | 0 | option surface volatility SR surface fit model | |
sPrc | FLOAT | 0 | option surface price ie PRICEsVol uPrc uOff years rate sDiv discrete dividends if any | |
sMark | FLOAT | 0 | option surface mark option surface price wbounding rules always between bidask | |
veSlope | FLOAT | 0 | veSlope dVol dUprc assuming vol xAxis 0 remains constant hedgeDelta de ve 100 veSlope if hedging with this assumption | |
de | FLOAT | 0 | option delta | |
ga | FLOAT | 0 | option gamma | |
th | FLOAT | 0 | option theta | |
ve | FLOAT | 0 | option vega | |
va | FLOAT | 0 | option vanna | |
vo | FLOAT | 0 | option volga | |
ro | FLOAT | 0 | option rho | |
ph | FLOAT | 0 | option phi | |
deDecay | FLOAT | 0 | option delta decay | |
up50 | FLOAT | 0 | underlier up 50 slide | |
dn50 | FLOAT | 0 | underlier dn 50 slide | |
up15 | FLOAT | 0 | underlier up 15 slide | |
dn15 | FLOAT | 0 | underlier dn 15 slide | |
up06 | FLOAT | 0 | underlier up 6 slide | |
dn08 | FLOAT | 0 | underlier dn 8 slide | |
synSpot | DOUBLE | 0 | Synthetic spot price marketderived spot when the underlying is not a traded instrument | |
priceType | enum - CalcPriceType | 'None' | Equity or Future Black76 pricing framework if Future then uPrc is the forwardUPrc and sdiv rate | |
calcErr | enum - ImpliedQuoteError | 'None' | option pricing calculation error if any | |
calcSource | enum - CalcSource | 'None' | ||
srcTimestamp | BIGINT | 0 | OPRA source timestamp nanoseconds since epoch will be zero if calcSource Tick | |
netTimestamp | BIGINT | 0 | SR timestamp publish time | |
timestamp | DATETIME(6) | '1900-01-01 00:00:00.000000' |
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 |
SECONDARY INDEX (ExpirationIndex) (Not Unique)
Field | Sequence |
---|---|
okey_yr | 1 |
okey_mn | 2 |
okey_dy | 3 |
SECONDARY INDEX (TickerIndex) (Not Unique)
Field | Sequence |
---|---|
ticker_tk | 1 |
CREATE TABLE EXAMPLE QUERY
CREATE TABLE `SRAnalytics`.`MsgLiveImpliedQuote` (
`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',
`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 'SR Ticker that this option rolls up to',
`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 'SR Ticker that this option rolls up to',
`ticker_tk` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'SR Ticker that this option rolls up to',
`uPrc` FLOAT NOT NULL DEFAULT 0 COMMENT 'underlier price (usually mid-market)',
`uOff` FLOAT NOT NULL DEFAULT 0 COMMENT 'implied underlier price offset (if any)',
`years` FLOAT NOT NULL DEFAULT 0 COMMENT 'years to expiration',
`xAxis` FLOAT NOT NULL DEFAULT 0 COMMENT 'option moneyness',
`rate` FLOAT NOT NULL DEFAULT 0 COMMENT 'discount rate',
`sdiv` FLOAT NOT NULL DEFAULT 0 COMMENT 'sdiv (continuous stock dividend) rate',
`ddiv` FLOAT NOT NULL DEFAULT 0 COMMENT 'cumulative discrete dividend value',
`oBid` FLOAT NOT NULL DEFAULT 0 COMMENT 'option bid price',
`oAsk` FLOAT NOT NULL DEFAULT 0 COMMENT 'option ask price',
`oBidIv` FLOAT NOT NULL DEFAULT 0 COMMENT 'volatility implied by option bid price',
`oAskIv` FLOAT NOT NULL DEFAULT 0 COMMENT 'volatility implied by option ask price',
`atmVol` FLOAT NOT NULL DEFAULT 0 COMMENT 'option atm volatility (from SR surface)',
`sVol` FLOAT NOT NULL DEFAULT 0 COMMENT 'option surface volatility (SR surface fit model)',
`sPrc` FLOAT NOT NULL DEFAULT 0 COMMENT 'option surface price; ie. PRICE(sVol, uPrc + uOff, years, rate, sDiv, {discrete dividends, if any})',
`sMark` FLOAT NOT NULL DEFAULT 0 COMMENT 'option surface mark (option surface price w/bounding rules; always between bid/ask)',
`veSlope` FLOAT NOT NULL DEFAULT 0 COMMENT 'veSlope = dVol / dUprc (assuming vol @ xAxis = 0 remains constant); hedgeDelta = (de + ve * 100 * veSlope) if hedging with this assumption',
`de` FLOAT NOT NULL DEFAULT 0 COMMENT 'option delta',
`ga` FLOAT NOT NULL DEFAULT 0 COMMENT 'option gamma',
`th` FLOAT NOT NULL DEFAULT 0 COMMENT 'option theta',
`ve` FLOAT NOT NULL DEFAULT 0 COMMENT 'option vega',
`va` FLOAT NOT NULL DEFAULT 0 COMMENT 'option vanna',
`vo` FLOAT NOT NULL DEFAULT 0 COMMENT 'option volga',
`ro` FLOAT NOT NULL DEFAULT 0 COMMENT 'option rho',
`ph` FLOAT NOT NULL DEFAULT 0 COMMENT 'option phi',
`deDecay` FLOAT NOT NULL DEFAULT 0 COMMENT 'option delta decay',
`up50` FLOAT NOT NULL DEFAULT 0 COMMENT 'underlier up 50% slide',
`dn50` FLOAT NOT NULL DEFAULT 0 COMMENT 'underlier dn 50% slide',
`up15` FLOAT NOT NULL DEFAULT 0 COMMENT 'underlier up 15% slide',
`dn15` FLOAT NOT NULL DEFAULT 0 COMMENT 'underlier dn 15% slide',
`up06` FLOAT NOT NULL DEFAULT 0 COMMENT 'underlier up 6% slide',
`dn08` FLOAT NOT NULL DEFAULT 0 COMMENT 'underlier dn 8% slide',
`synSpot` DOUBLE NOT NULL DEFAULT 0 COMMENT 'Synthetic spot price (market-derived spot when the underlying is not a traded instrument)',
`priceType` ENUM('None','Equity','Future') NOT NULL DEFAULT 'None' COMMENT 'Equity or Future (Black76) pricing framework; if Future then uPrc is the forwardUPrc and sdiv = rate',
`calcErr` ENUM('None','InvalidUPrc','InvalidSVol','PricingError') NOT NULL DEFAULT 'None' COMMENT 'option pricing calculation error (if any)',
`calcSource` ENUM('None','Tick','Loop') NOT NULL DEFAULT 'None',
`srcTimestamp` BIGINT NOT NULL DEFAULT 0 COMMENT 'OPRA source timestamp (nanoseconds since epoch); will be zero if calcSource != Tick',
`netTimestamp` BIGINT NOT NULL DEFAULT 0 COMMENT 'SR timestamp @ publish time',
`timestamp` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000',
PRIMARY KEY USING HASH (`okey_tk`,`okey_yr`,`okey_mn`,`okey_dy`,`okey_xx`,`okey_cp`,`okey_at`,`okey_ts`),
KEY `ExpirationIndex` (`okey_yr`,`okey_mn`,`okey_dy`) USING HASH,
KEY `TickerIndex` (`ticker_tk`) USING HASH
) ENGINE=SRSE DEFAULT CHARSET=LATIN1 COMMENT='CalcSource=Tick records are computed and published each time an option NBBO price changes. CalcSource=Loop records are computed in a 2-3 minute background loop.\nNote that the underlier price (uPrc) will be the same for all options an underlier when CalcSource=Loop. This is not true for CalcSource=Tick where uPrc will be the underlier price that prevailed when the option price changed.\nIf you are consuming multicast data and only want records with consistent uPrc values for all options you should ignore Tick records. Alternatively, you can use an independent underlier price source (our StockBookQuote feed or some other) and \'adjust\' the values in this table to the new underlier value.\nIf you are selecting records from SRSE you should note that OptionImpliedQuoteAdj table is a proxy implementation of this table that automatically applies the appropriate underlier adjustments as records are being returned.';
SELECT TABLE EXAMPLE QUERY
SELECT
`okey_at`,
`okey_ts`,
`okey_tk`,
`okey_yr`,
`okey_mn`,
`okey_dy`,
`okey_xx`,
`okey_cp`,
`ticker_at`,
`ticker_ts`,
`ticker_tk`,
`uPrc`,
`uOff`,
`years`,
`xAxis`,
`rate`,
`sdiv`,
`ddiv`,
`oBid`,
`oAsk`,
`oBidIv`,
`oAskIv`,
`atmVol`,
`sVol`,
`sPrc`,
`sMark`,
`veSlope`,
`de`,
`ga`,
`th`,
`ve`,
`va`,
`vo`,
`ro`,
`ph`,
`deDecay`,
`up50`,
`dn50`,
`up15`,
`dn15`,
`up06`,
`dn08`,
`synSpot`,
`priceType`,
`calcErr`,
`calcSource`,
`srcTimestamp`,
`netTimestamp`,
`timestamp`
FROM `SRAnalytics`.`MsgLiveImpliedQuote`
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='LiveImpliedQuote' ORDER BY ordinal_position ASC;