Skip to main content
Version: Staging

SpdrOptTheoRecord

V8 Message Definiton

SpdrOptTheoRecords can be used to override theoretical volatilities for specific strikes when using SpdrTheoExpSurface records.

METADATA

AttributeValue
Topic1945-client-theos
MLink TokenSystemData
ProductSRTheo
accessTypeSELECT,UPDATE,INSERT,DELETE

Table Definition

FieldTypeKeyDefault ValueComment
okey_atenum - AssetTypePRI'None'
okey_tsenum - TickerSrcPRI'None'
okey_tkVARCHAR(12)PRI''
okey_yrSMALLINT UNSIGNEDPRI0
okey_mnTINYINT UNSIGNEDPRI0
okey_dyTINYINT UNSIGNEDPRI0
okey_xxDOUBLEPRI0
okey_cpenum - CallPutPRI'Call'
theoModelVARCHAR(16)PRI''
clientFirmVARCHAR(16)PRI''client firm this theo model is associated with controls visibility
TheoVolFLOAT0midpoint fair volatility
BVolFLOAT0buy vol
SVolFLOAT0sell vol
BEdgeFLOAT0edge premium spread when buying
SEdgeFLOAT0edge premium spread when selling
buySellConventionenum - BuySellConvention'None'rule used to interpret BVol SVol BEdge and SEdge
refUPrcFLOAT0Reference uPrc for dynamic vol vol TheoVol vegaSlope uPrc refUPrc
vegaSlopeFLOAT0Set to zero for no dynamic hedge delta delta vegaSlope vegarequires accounthedgeType TVol theo delta vegaSlope or TvS surface delta vegaSlope default is surface delta only
theoStatusenum - TheoStatus'Hold'indicates where theos for this ticker will be utilized
timestampDATETIME(6)'1900-01-01 00:00:00.000000'

PRIMARY KEY DEFINITION (Unique)

FieldSequence
okey_tk1
okey_yr2
okey_mn3
okey_dy4
okey_xx5
okey_cp6
okey_at7
okey_ts8
theoModel9
clientFirm10

CREATE TABLE EXAMPLE QUERY

CREATE TABLE `SRTheo`.`MsgSROptTheoRecord` (
`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',
`theoModel` VARCHAR(16) NOT NULL DEFAULT '',
`clientFirm` VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'client firm this theo model is associated with (controls visibility)',
`TheoVol` FLOAT NOT NULL DEFAULT 0 COMMENT 'mid-point (fair) volatility',
`BVol` FLOAT NOT NULL DEFAULT 0 COMMENT 'buy vol',
`SVol` FLOAT NOT NULL DEFAULT 0 COMMENT 'sell vol',
`BEdge` FLOAT NOT NULL DEFAULT 0 COMMENT 'edge premium spread when buying',
`SEdge` FLOAT NOT NULL DEFAULT 0 COMMENT 'edge premium spread when selling',
`buySellConvention` ENUM('None','Minimum','BSSpread','BSPctSprd','BSOffsetPts','BSOffsetPct') NOT NULL DEFAULT 'None' COMMENT 'rule used to interpret BVol, SVol, BEdge, and SEdge',
`refUPrc` FLOAT NOT NULL DEFAULT 0 COMMENT 'Reference uPrc for dynamic vol: vol = TheoVol + vegaSlope * (uPrc - refUPrc).',
`vegaSlope` FLOAT NOT NULL DEFAULT 0 COMMENT 'Set to zero for no dynamic. hedge delta = delta + vegaSlope * vega;requires account.hedgeType = [TVol (theo delta + vegaSlope) or TvS (surface delta + vegaSlope)] (default is surface delta only)',
`theoStatus` ENUM('Hold','Auto','Scanner','Markup','CloseOnly') NOT NULL DEFAULT 'Hold' COMMENT 'indicates where theos for this ticker will be utilized',
`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`,`theoModel`,`clientFirm`)
) ENGINE=SRSE DEFAULT CHARSET=LATIN1 COMMENT='SpdrOptTheoRecords can be used to override theoretical volatilities for specific strikes when using SpdrTheoExpSurface records.';

SELECT TABLE EXAMPLE QUERY

SELECT
`okey_at`,
`okey_ts`,
`okey_tk`,
`okey_yr`,
`okey_mn`,
`okey_dy`,
`okey_xx`,
`okey_cp`,
`theoModel`,
`clientFirm`,
`TheoVol`,
`BVol`,
`SVol`,
`BEdge`,
`SEdge`,
`buySellConvention`,
`refUPrc`,
`vegaSlope`,
`theoStatus`,
`timestamp`
FROM `SRTheo`.`MsgSROptTheoRecord`
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'
AND
/* Replace with a VARCHAR(16) */
`theoModel` = 'Example_theoModel'
AND
/* Replace with a VARCHAR(16) */
`clientFirm` = 'Example_clientFirm';

UPDATE TABLE EXAMPLE QUERY

UPDATE `SRTheo`.`MsgSROptTheoRecord` 
SET
/* Replace with a FLOAT */
`TheoVol` = 1.23,
/* Replace with a FLOAT */
`BVol` = 1.23,
/* Replace with a FLOAT */
`SVol` = 1.23,
/* Replace with a FLOAT */
`BEdge` = 1.23,
/* Replace with a FLOAT */
`SEdge` = 1.23,
/* Replace with a ENUM('None','Minimum','BSSpread','BSPctSprd','BSOffsetPts','BSOffsetPct') */
`buySellConvention` = 'None',
/* Replace with a FLOAT */
`refUPrc` = 1.23,
/* Replace with a FLOAT */
`vegaSlope` = 1.23,
/* Replace with a ENUM('Hold','Auto','Scanner','Markup','CloseOnly') */
`theoStatus` = 'Hold',
/* Replace with a DATETIME(6) */
`timestamp` = '2022-01-01 12:34:56.000000'
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'
AND
/* Replace with a VARCHAR(16) */
`theoModel` = 'Example_theoModel'
AND
/* Replace with a VARCHAR(16) */
`clientFirm` = 'Example_clientFirm';

INSERT TABLE EXAMPLE QUERY

INSERT INTO `SRTheo`.`MsgSROptTheoRecord`(
/* Replace with a ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') */
`okey_at`,
/* 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`,
/* Replace with a VARCHAR(12) */
`okey_tk`,
/* Replace with a SMALLINT UNSIGNED */
`okey_yr`,
/* Replace with a TINYINT UNSIGNED */
`okey_mn`,
/* Replace with a TINYINT UNSIGNED */
`okey_dy`,
/* Replace with a DOUBLE */
`okey_xx`,
/* Replace with a ENUM('Call','Put','Pair') */
`okey_cp`,
/* Replace with a VARCHAR(16) */
`theoModel`,
/* Replace with a VARCHAR(16) */
`clientFirm`,
/* Replace with a FLOAT */
`TheoVol`,
/* Replace with a FLOAT */
`BVol`,
/* Replace with a FLOAT */
`SVol`,
/* Replace with a FLOAT */
`BEdge`,
/* Replace with a FLOAT */
`SEdge`,
/* Replace with a ENUM('None','Minimum','BSSpread','BSPctSprd','BSOffsetPts','BSOffsetPct') */
`buySellConvention`,
/* Replace with a FLOAT */
`refUPrc`,
/* Replace with a FLOAT */
`vegaSlope`,
/* Replace with a ENUM('Hold','Auto','Scanner','Markup','CloseOnly') */
`theoStatus`,
/* Replace with a DATETIME(6) */
`timestamp`
)
VALUES(
'None',
'None',
'Example_okey_tk',
123,
1,
1,
4.56,
'Call',
'Example_theoModel',
'Example_clientFirm',
1.23,
1.23,
1.23,
1.23,
1.23,
'None',
1.23,
1.23,
'Hold',
'2022-01-01 12:34:56.000000'
);

DELETE TABLE EXAMPLE QUERY

DELETE FROM `SRTheo`.`MsgSROptTheoRecord` 
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'
AND
/* Replace with a VARCHAR(16) */
`theoModel` = 'Example_theoModel'
AND
/* Replace with a VARCHAR(16) */
`clientFirm` = 'Example_clientFirm';

Doc Columns Query

SELECT * FROM SRTheo.doccolumns WHERE TABLE_NAME='SpdrOptTheoRecord' ORDER BY ordinal_position ASC;