Skip to main content
Version: 8.4.08.4

TickerAnalytics

V8 Message Definiton

METADATA

AttributeValue
Topic3225-market-statistics
MLink TokenSystemData
ProductSRAnalytics
accessTypeSELECT

Table Definition

FieldTypeKeyDefault ValueComment
ticker_atenum - AssetTypePRI'None'
ticker_tsenum - TickerSrcPRI'None'
ticker_tkVARCHAR(12)PRI''
tradingdateDATEPRI'1900-01-01'trading period
securityIDINT0security ID is used to track a ticker over time It is a best effort attempt to track a ticker through name changes and other corporate actions
altIDINT0SR security ID is used to track a ticker over time It is a best effort attempt to track a ticker through name changes and other corporate actions
dayNumberINT0cardinal day number increments by 1 each trading period
openpriceFLOAT0open print
highpriceFLOAT0highest regular hours print price
lowpriceFLOAT0lowest regular hours print price
closepriceFLOAT0official exchange close as reported by primary exchange
prCloseFLOAT0adjusted prior period close closePr prior Close X returnFactor
prCloseUnadjFLOAT0unadjusted prior period close
week52HighDOUBLE052 week high
week52HighDateDATE'1900-01-01'52 week high date
week52LowDOUBLE052 week low
week52LowDateDATE'1900-01-01'52 week low date
avgVolumeFLOAT0trailing average 20D daily stock volume
ccvarDOUBLE0closeclose log return daily variance using the adjusted yesterday closeLn CloseClosePr 2
hlvarDOUBLE0highlow daily variance
dayreturnFLOAT0daily return adjusted for CorpAction
returnfactorFLOAT0adjustment factor
cumreturnfactorFLOAT0cumulative adjustment factor
timestampDATETIME(6)'1900-01-01 00:00:00.000000'record update timestamp

PRIMARY KEY DEFINITION (Unique)

FieldSequence
ticker_tk1
ticker_at2
ticker_ts3
tradingdate4

CREATE TABLE EXAMPLE QUERY

CREATE TABLE `SRAnalytics`.`MsgTickerAnalytics` (
`ticker_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None',
`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',
`ticker_tk` VARCHAR(12) NOT NULL DEFAULT '',
`tradingdate` DATE NOT NULL DEFAULT '1900-01-01' COMMENT 'trading period',
`securityID` INT NOT NULL DEFAULT 0 COMMENT 'security ID is used to track a ticker over time. It is a best effort attempt to track a ticker through name changes and other corporate actions',
`altID` INT NOT NULL DEFAULT 0 COMMENT 'SR security ID is used to track a ticker over time. It is a best effort attempt to track a ticker through name changes and other corporate actions',
`dayNumber` INT NOT NULL DEFAULT 0 COMMENT 'cardinal day number (increments by 1 each trading period)',
`openprice` FLOAT NOT NULL DEFAULT 0 COMMENT 'open print',
`highprice` FLOAT NOT NULL DEFAULT 0 COMMENT 'highest regular hours print price',
`lowprice` FLOAT NOT NULL DEFAULT 0 COMMENT 'lowest regular hours print price',
`closeprice` FLOAT NOT NULL DEFAULT 0 COMMENT 'official exchange close (as reported by primary exchange)',
`prClose` FLOAT NOT NULL DEFAULT 0 COMMENT 'adjusted prior period close; closePr = prior Close X returnFactor',
`prCloseUnadj` FLOAT NOT NULL DEFAULT 0 COMMENT 'unadjusted prior period close',
`week52High` DOUBLE NOT NULL DEFAULT 0 COMMENT '52 week high',
`week52HighDate` DATE NOT NULL DEFAULT '1900-01-01' COMMENT '52 week high date',
`week52Low` DOUBLE NOT NULL DEFAULT 0 COMMENT '52 week low',
`week52LowDate` DATE NOT NULL DEFAULT '1900-01-01' COMMENT '52 week low date',
`avgVolume` FLOAT NOT NULL DEFAULT 0 COMMENT 'trailing average 20D daily stock volume',
`ccvar` DOUBLE NOT NULL DEFAULT 0 COMMENT 'close-close log return daily variance (using the adjusted yesterday close:Ln (Close/ClosePr) ^ 2',
`hlvar` DOUBLE NOT NULL DEFAULT 0 COMMENT 'high-low daily variance',
`dayreturn` FLOAT NOT NULL DEFAULT 0 COMMENT 'daily return (adjusted for CorpAction)',
`returnfactor` FLOAT NOT NULL DEFAULT 0 COMMENT 'adjustment factor',
`cumreturnfactor` FLOAT NOT NULL DEFAULT 0 COMMENT 'cumulative adjustment factor',
`timestamp` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000' COMMENT 'record update timestamp',
PRIMARY KEY USING HASH (`ticker_tk`,`ticker_at`,`ticker_ts`,`tradingdate`)
) ENGINE=SRSE DEFAULT CHARSET=LATIN1 COMMENT='';

SELECT TABLE EXAMPLE QUERY

SELECT
`ticker_at`,
`ticker_ts`,
`ticker_tk`,
`tradingdate`,
`securityID`,
`altID`,
`dayNumber`,
`openprice`,
`highprice`,
`lowprice`,
`closeprice`,
`prClose`,
`prCloseUnadj`,
`week52High`,
`week52HighDate`,
`week52Low`,
`week52LowDate`,
`avgVolume`,
`ccvar`,
`hlvar`,
`dayreturn`,
`returnfactor`,
`cumreturnfactor`,
`timestamp`
FROM `SRAnalytics`.`MsgTickerAnalytics`
WHERE
/* Replace with a ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') */
`ticker_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') */
`ticker_ts` = 'None'
AND
/* Replace with a VARCHAR(12) */
`ticker_tk` = 'Example_ticker_tk'
AND
/* Replace with a DATE */
`tradingdate` = '2022-01-01';

Doc Columns Query

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