Market Data Table Stucture#

A series of simple examples are provided showing how to retrieve different types of market data from OneTick Cloud. Data is stored in databases specific to the venue (equity, future, option or index provider), and commonly consists of the following tables:

TRD - Trade Events
QTE - Quote Events
NBBO - NBBO Quotes across a Consolidated Market
NBBO_COMP - NBBO Quotes combined with the Best Odd Lot Orders (BOLO)
QTE_COMP - Quote Events combined with the Best Odd Lot Orders (BOLO)
MKT - Market Phase Events
PRL - Book Depth Events at L2 - Market by Level (MBL)
PRL_FULL - Book Depth Events at L3 - Market by Order (MBO)
IND - Auction Imbalance Events
EVENT - Events such as Earnings and Company Conference Calls
STAT - Static Reference Data Record

Derived features sets are constructed from the source Trade (TRD), Quote (QTE), and NBBO (NBBO) tables, and include:

TRD_1M - 1 Minute Trade Bars
QTE_1M - 1 Minute Quote Bars
LAT_1M - 1 Minute Latency Bars for Real Time Data
VWAP_1H - 1 Hour VWAP Bars
DAY - End of Day Record
QTE_NBBO_DAY - Daily Quote Metrics against NBBO
TRD_NBBO_DAY - Daily Trade Metrics against NBBO
TRD_TCA - Enriched Trade Events

Real Time last value caches are also provided as additional tables:

SNAP - Latest Trade & Quote / NBBO
SNAP_NBBO - Latest NBBO
SNAP_QTE - Latest Quotes
SNAP_TRD - Latest Trades

Symbol Universe#

All collected symbols are centrally stored in the SYMBOL_UNIVERSE database, with reference information such as Name, Security Type, Currency, MIC, etc, and importantly which DB_NAME and DB_SYMBOL stores the historic market data. The SYMBOL_UNIVERSE database is partitioned by Database and Security Type, allowing fast filtering on these terms. Common Security Types include: Equity, Future, `` Futures Spread``, Option, Option Spread, Index, and ETF.

Symbol Universe Retrieval Filtered on LSE Equities#

SELECT * FROM  SYMBOL_UNIVERSE.STAT
WHERE SYMBOL_NAME = 'LSE Equity'
and TIMESTAMP >= '2024-01-03 00:00:00 UTC'
and TIMESTAMP < '2024-01-04 00:00:00 UTC'
LIMIT 5

Symbol Universe Retrieval Filtered on LSE Equities#
Symbol	Timestamp	SYMBOL_NAME	TICK_TYPE	OMDSEQ	DB_NAME	NAME	ISIN	EXCH_SYMBOL	TRADING_CODE	MIC	OPERATING_MIC	SEC_TYPE	MKT_SEGMENT	CURRENCY	BSYM	OID	DB_SYMBOL
SYMBOL_UNIVERSE::LSE Equity	2024-01-03 04:00:00	SYMBOL_UNIVERSE::LSE Equity	STAT	124	LSE	Groupe Parot	FR0013204070	0A04	151949	XLOM	XLON	Equity	SSX4	EUR	0A04 LN Equity	1015725922	0A04
SYMBOL_UNIVERSE::LSE Equity	2024-01-03 04:00:00	SYMBOL_UNIVERSE::LSE Equity	STAT	125	LSE	Medacta Group SA	CH0468525222	0A05	151987	XLOM	XLON	Equity	SSX4	CHF	0A05 LN Equity	1015743877	0A05
SYMBOL_UNIVERSE::LSE Equity	2024-01-03 04:00:00	SYMBOL_UNIVERSE::LSE Equity	STAT	132	LSE	Stadler Rail AG	CH0002178181	0A0C	151996	XLOM	XLON	Equity	SSX4	CHF	0A0C LN Equity	1015747442	0A0C
SYMBOL_UNIVERSE::LSE Equity	2024-01-03 04:00:00	SYMBOL_UNIVERSE::LSE Equity	STAT	133	LSE	Societa Editoriale Il Fatto Spa	IT0005353484	0A0G	152040	XLOM	XLON	Equity	SSX4	EUR	0A0G LN Equity	1015758539	0A0G
SYMBOL_UNIVERSE::LSE Equity	2024-01-03 04:00:00	SYMBOL_UNIVERSE::LSE Equity	STAT	135	LSE	Twenty First Century Fox A Inc	US35137L1052	0A0X	152064	XLOM	XLON	Equity	SSX4	EUR	0A0X LN Equity	1015762601	0A0X

Symbol Universe Retrieval Filtered on Equity, Currency and Instrument Name#

SELECT NAME, EXCH_SYMBOL, DB_SYMBOL, DB_NAME, CURRENCY, SEC_TYPE FROM SYMBOL_UNIVERSE.STAT
WHERE SYMBOL_NAME LIKE '% Equity'
and CURRENCY = 'GBX'
and NAME LIKE 'Voda%'
and TIMESTAMP >= '2024-01-03 00:00:00 UTC'
and TIMESTAMP < '2024-01-04 00:00:00 UTC'
LIMIT 5

Symbol Universe Retrieval Filtered on Equity, Currency and Instrument Name#
Timestamp	DB_NAME	NAME	EXCH_SYMBOL	SEC_TYPE	CURRENCY	DB_SYMBOL
2024-01-03 00:00:00	EU_COMP	Vodafone Group plc	VOD	Equity	GBX	GB00BH4HKS39
2024-01-03 00:00:00	EU_COMP_SAMPLE	Vodafone Group plc	VOD	Equity	GBX	GB00BH4HKS39
2024-01-03 02:00:00	CBOE_APA	Vodafone Group plc	VODl	Equity	GBX	VODl
2024-01-03 03:00:00	EQUIDUCT	Vodafone Group plc	VODl	Equity	GBX	VODl
2024-01-03 04:00:00	BXE	Vodafone Group PLC	VODl	Equity	GBX	VODl

Trade Events#

Trade events are retrieved by specifying the TRD table, along with the specified database, symbol and time range. Trades are represented with:

PRICE - Trade Price,
SIZE - Trade Size or Quantity,
TRADE_PERIOD - Market period during which a trade was executed.
BOOK_TYPE - Type of order book or trading mechanism through which a trade was executed.

Plus other fields specific to the venue.

Trade Retrieval#

SELECT * FROM US_COMP_SAMPLE.TRD
WHERE SYMBOL_NAME='AAPL'
and TIMESTAMP >= '2024-01-03 00:00:00 UTC'
and TIMESTAMP < '2024-01-04 00:00:00 UTC'
LIMIT 10

Trade Retrieval Results#
Symbol	Timestamp	PRICE	SIZE	SYMBOL_NAME	TICK_TYPE	OMDSEQ	TRADE_ID	TICKER	EXCHANGE	COND	PARTICIPANT_TIME	TRF_TIME	SOURCE	TRF	TTE	SEQ_NUM
US_COMP_SAMPLE::AAPL	2024-01-03 00:00:00.218558977	185.3	33	US_COMP_SAMPLE::AAPL	TRD	0	86475	AAPL	P	@FTI	2024-01-03 00:00:00.218213653		N		1	7,564,627
US_COMP_SAMPLE::AAPL	2024-01-03 00:00:00.218560994	185.3	23	US_COMP_SAMPLE::AAPL	TRD	1	86476	AAPL	P	@FTI	2024-01-03 00:00:00.218213653		N		1	7,564,628
US_COMP_SAMPLE::AAPL	2024-01-03 00:00:00.220299709	185.31	270	US_COMP_SAMPLE::AAPL	TRD	0	86477	AAPL	P	@FT	2024-01-03 00:00:00.219957729		N		1	7,564,629
US_COMP_SAMPLE::AAPL	2024-01-03 00:00:00.435767951	185.31	163	US_COMP_SAMPLE::AAPL	TRD	0	86478	AAPL	P	@FT	2024-01-03 00:00:00.435425583		N		1	7,564,642
US_COMP_SAMPLE::AAPL	2024-01-03 00:00:02.601960283	185.29	6	US_COMP_SAMPLE::AAPL	TRD	0	86479	AAPL	P	@FTI	2024-01-03 00:00:02.601616588		N		1	7,564,650
US_COMP_SAMPLE::AAPL	2024-01-03 00:00:02.645347419	185.3	1	US_COMP_SAMPLE::AAPL	TRD	0	525468	AAPL	D	@ TI	2024-01-03 00:00:02.419488	2024-01-03 00:00:02.645322626	N	Q	0	7,564,651
US_COMP_SAMPLE::AAPL	2024-01-03 00:00:03.039775401	185.31	1	US_COMP_SAMPLE::AAPL	TRD	0	86480	AAPL	P	@FTI	2024-01-03 00:00:03.039432526		N		1	7,564,652
US_COMP_SAMPLE::AAPL	2024-01-03 00:00:03.208757357	185.3	1	US_COMP_SAMPLE::AAPL	TRD	0	525469	AAPL	D	@ TI	2024-01-03 00:00:02.920041	2024-01-03 00:00:03.208730105	N	Q	0	7,564,653
US_COMP_SAMPLE::AAPL	2024-01-03 00:00:09.805361739	185.3	1	US_COMP_SAMPLE::AAPL	TRD	0	525470	AAPL	D	@ TI	2024-01-03 00:00:09.600971	2024-01-03 00:00:09.805332604	N	Q	0	7,564,663
US_COMP_SAMPLE::AAPL	2024-01-03 00:00:10.888731406	185.31	3	US_COMP_SAMPLE::AAPL	TRD	0	86481	AAPL	P	@ TI	2024-01-03 00:00:10.888387762		N		0	7,564,664

Quote Events#

Quote events are retrieved by specifying the QTE table, along with the specified database, symbol and time range. Quotes are represented with BID_PRICE, BID_SIZE, ASK_PRICE, ASK_SIZE and other fields.

Quote Retrieval#

SELECT * FROM US_COMP_SAMPLE.QTE
WHERE SYMBOL_NAME='AAPL'
and TIMESTAMP >= '2024-01-03 00:00:00 UTC'
and TIMESTAMP < '2024-01-04 00:00:00 UTC'
LIMIT 10

Quote Retrieval Results#
Symbol	Timestamp	BID_PRICE	ASK_PRICE	BID_SIZE	ASK_SIZE	SYMBOL_NAME	TICK_TYPE	OMDSEQ	TICKER	EXCHANGE	COND	PARTICIPANT_TIME	SOURCE	SEQ_NUM	NBBO_IND
US_COMP_SAMPLE::AAPL	2024-01-03 00:00:00.220300383	185.29	185.31	3	6	US_COMP_SAMPLE::AAPL	QTE	0	AAPL	P	R	2024-01-03 00:00:00.219957729	N	110,384,045	4
US_COMP_SAMPLE::AAPL	2024-01-03 00:00:00.435768604	185.29	185.31	3	4	US_COMP_SAMPLE::AAPL	QTE	0	AAPL	P	R	2024-01-03 00:00:00.435425583	N	110,384,063	4
US_COMP_SAMPLE::AAPL	2024-01-03 00:00:00.494951248	183.1	185.64	1	4	US_COMP_SAMPLE::AAPL	QTE	0	AAPL	Z	R	2024-01-03 00:00:00.494771	N	110,384,082	0
US_COMP_SAMPLE::AAPL	2024-01-03 00:00:02.601960874	185.29	185.31	3	4	US_COMP_SAMPLE::AAPL	QTE	0	AAPL	P	R	2024-01-03 00:00:02.601616588	N	110,384,093	0
US_COMP_SAMPLE::AAPL	2024-01-03 00:00:03.039776531	185.29	185.31	3	4	US_COMP_SAMPLE::AAPL	QTE	0	AAPL	P	R	2024-01-03 00:00:03.039432526	N	110,384,095	0
US_COMP_SAMPLE::AAPL	2024-01-03 00:00:10.888732704	185.29	185.31	3	4	US_COMP_SAMPLE::AAPL	QTE	0	AAPL	P	R	2024-01-03 00:00:10.888387762	N	110,384,113	0
US_COMP_SAMPLE::AAPL	2024-01-03 00:00:11.286962866	182.99	185.62	1	2	US_COMP_SAMPLE::AAPL	QTE	0	AAPL	U	R	2024-01-03 00:00:11.286771307	N	110,384,114	0
US_COMP_SAMPLE::AAPL	2024-01-03 00:00:13.149205540	185.29	185.31	3	3	US_COMP_SAMPLE::AAPL	QTE	0	AAPL	P	R	2024-01-03 00:00:13.148859752	N	110,384,117	4
US_COMP_SAMPLE::AAPL	2024-01-03 00:00:13.149701425	185.29	185.33	3	1	US_COMP_SAMPLE::AAPL	QTE	1	AAPL	P	R	2024-01-03 00:00:13.149359783	N	110,384,118	4
US_COMP_SAMPLE::AAPL	2024-01-03 00:00:15.996461820	185.01	185.39	2	1	US_COMP_SAMPLE::AAPL	QTE	0	AAPL	K	R	2024-01-03 00:00:15.996258	N	110,384,125	0

Market Phase Events#

Market Phase events are retrieved by specifying the MKT table, along with the specified database, symbol and time range. This table is only available for a subset of databases. Phases are represented with OMD_STATUS and other fields.

OMD_STATUS has the following enumeration.

“A” - Auction

“B” - Break / Pause

“C” - Closing auction

“D” - Delisted / Pending deletion

“H” - Halted

“I” - Scheduled intraday auction

“M” - Mandatory quoting period

“N” - New instrument / Pre-listing

“O” - Opening auction

“P” - Pre-market

“Q” - Indicative quoting period

“R” - Off-book trade reporting

“S” - Suspended

“T” - Continuous trading

“U” - Unscheduled auction

“c” - Closed

“i” - IPO / Public offering auction

“n” - No market phase / Unspecified

“o” - Order management

“p” - Post-market

“t” - Trading at last price

Market Phase Retrieval#

SELECT * FROM LSE_SAMPLE.MKT
WHERE SYMBOL_NAME='VOD'
and TIMESTAMP >= '2024-01-03 00:00:00 UTC'
and TIMESTAMP < '2024-01-04 00:00:00 UTC'
LIMIT 10

Market Phase Retrieval Results#
Symbol	Timestamp	SYMBOL_NAME	TICK_TYPE	MKT_PHASE	OFF_BOOK_STATUS	OMD_STATUS	OMDSEQ
LSE_SAMPLE::VOD	2024-01-03 07:15:00.037	LSE_SAMPLE::VOD	MKT		T	R	77
LSE_SAMPLE::VOD	2024-01-03 07:50:00.067	LSE_SAMPLE::VOD	MKT	a	T	O	134
LSE_SAMPLE::VOD	2024-01-03 08:00:06.226	LSE_SAMPLE::VOD	MKT	T	T	T	26
LSE_SAMPLE::VOD	2024-01-03 16:30:00.107	LSE_SAMPLE::VOD	MKT	d	T	C	91
LSE_SAMPLE::VOD	2024-01-03 16:35:07.331	LSE_SAMPLE::VOD	MKT	u	T	t	0
LSE_SAMPLE::VOD	2024-01-03 16:40:00.025	LSE_SAMPLE::VOD	MKT	b	T	p	34
LSE_SAMPLE::VOD	2024-01-03 17:15:00.080	LSE_SAMPLE::VOD	MKT	x	T	p	106
LSE_SAMPLE::VOD	2024-01-03 17:30:00.041	LSE_SAMPLE::VOD	MKT	c	T	c	34
LSE_SAMPLE::VOD	2024-01-03 17:30:00.041	LSE_SAMPLE::VOD	MKT	c	c	c	0

End of Day Record#

End of Day Records are retrieved by specifying the DAY table, along with the specified database, symbol and time range. This tables returns the OPEN, HIGH , LOW , CLOSE and VOLUME. e.g. OHLCV. With VOLUME additionally divided by Trade category. As Trade categories are venue specific, the VOLUME fields can vary by venue.

For Derivative venues additionally the following fields are provided:

OPEN_INT - Open Interest
OPEN_INT_DATE - Open Interest Date
SETTLE_PRICE - Settlement Price
SETTLE_DATE - Settlement Date

DAY Record Retrieval#

SELECT * FROM LSE_SAMPLE.DAY
WHERE SYMBOL_NAME='VOD'
and TIMESTAMP >= '2024-01-03 00:00:00 UTC'
and TIMESTAMP < '2024-01-04 00:00:00 UTC'
LIMIT 10

Day Retrieval Results#
Symbol	Timestamp	SYMBOL_NAME	TICK_TYPE	OMDSEQ	HIGH	LOW	VOLUME	OPEN	CLOSE	ON_BOOK_VOLUME	OFF_BOOK_VOLUME
LSE_SAMPLE::VOD	2024-01-03 19:30:00	LSE_SAMPLE::VOD	DAY	0	70.75	69.38	90161664	70	69.51	31819135	58342529

NBBO Quotes across a Consolidated Market#

National Best Bid & Offer (NBBO) events are retrieved by specifying the NBBO table, along with the specified database, symbol and time range. This table is only available for databases that host composite exchanges, and has a similar schema to QTE tables, with BID_PRICE, ASK_PRICE, BID_SIZE, ASK_SIZE. For the US Market, the NBBO is received from the Consolidated Tape (the US SIP). For all other consolidated markets the NBBO is constructed by OneTick.

Additionally it also includes BID_EXCHANGE and ASK_EXCHANGE.

NBBO Retrieval#

SELECT * FROM US_COMP_SAMPLE.NBBO
WHERE SYMBOL_NAME='AAPL'
and TIMESTAMP >= '2024-01-03 00:00:00 UTC'
and TIMESTAMP < '2024-01-04 00:00:00 UTC'
LIMIT 10

NBBO Retrieval Results#
Symbol	Timestamp	BID_PRICE	ASK_PRICE	BID_SIZE	ASK_SIZE	SYMBOL_NAME	TICK_TYPE	OMDSEQ	BID_SIZE_TOTAL	BID_EXCHANGE	ASK_SIZE_TOTAL	ASK_EXCHANGE
US_COMP_SAMPLE::AAPL	2024-01-03 00:00:00.220300383	185.29	185.31	3	6	US_COMP_SAMPLE::AAPL	NBBO	0	3	P	6	P
US_COMP_SAMPLE::AAPL	2024-01-03 00:00:00.435768604	185.29	185.31	3	4	US_COMP_SAMPLE::AAPL	NBBO	0	3	P	4	P
US_COMP_SAMPLE::AAPL	2024-01-03 00:00:13.149205540	185.29	185.31	3	3	US_COMP_SAMPLE::AAPL	NBBO	0	3	P	3	P
US_COMP_SAMPLE::AAPL	2024-01-03 00:00:13.149701425	185.29	185.33	3	1	US_COMP_SAMPLE::AAPL	NBBO	1	3	P	1	P
US_COMP_SAMPLE::AAPL	2024-01-03 00:00:22.472943947	185.3	185.33	1	1	US_COMP_SAMPLE::AAPL	NBBO	0	1	P	1	P
US_COMP_SAMPLE::AAPL	2024-01-03 00:00:27.860325809	185.3	185.34	1	1	US_COMP_SAMPLE::AAPL	NBBO	0	1	P	1	P
US_COMP_SAMPLE::AAPL	2024-01-03 00:00:29.781038128	185.3	185.33	1	1	US_COMP_SAMPLE::AAPL	NBBO	0	1	P	1	P
US_COMP_SAMPLE::AAPL	2024-01-03 00:00:30.124465063	185.29	185.33	4	1	US_COMP_SAMPLE::AAPL	NBBO	0	4	P	1	P
US_COMP_SAMPLE::AAPL	2024-01-03 00:00:57.937608932	185.29	185.33	3	1	US_COMP_SAMPLE::AAPL	NBBO	0	3	P	1	P
US_COMP_SAMPLE::AAPL	2024-01-03 00:01:08.332968256	185.29	185.34	3	1	US_COMP_SAMPLE::AAPL	NBBO	0	3	P	1	P

NBBO Quotes combined with the Best Odd Lot Orders (BOLO)#

Since June 2026 the US_COMP database has also included the table NBBO_COMP, which combines the regulatory NBBO consisting of even lot quotes, with the Best Odd Lot Order (BOLO), producing a new NBBO including both Odd and Even Lot Quotes. The result is typically an NBBO that has a narrower spread than the official NBBO based on Even Lots. The schema of NBBO_COMP follows the same schemas as for NBBO.

NBBO Retrieval including Best Odd Lot Orders (BOLO) for CSCO#

select * from US_COMP.NBBO_COMP
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2026-07-23 09:30:00 America/New_York'
and TIMESTAMP < '2026-07-23 16:00:00 America/New_York'
limit 1000

Quote Events combined with the Best Odd Lot Orders (BOLO)#

Since June 2026 the US_COMP database has also included the table QTE_COMP, which combines the regulatory Quotes consisting of even lot quotes for each exchange, with the Best Odd Lot Order (BOLO), producing a new Quote for each exchange including both Odd and Even Lot Quotes. The result is typically a Quote that has a narrower spread than the official exchange quote based on Even Lots. The schema of QTE_COMP follows the same schemas as for QTE.

Quote Retrieval including Best Odd Lot Orders (BOLO) for CSCO#

select * from US_COMP.QTE_COMP
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2026-07-23 09:30:00 America/New_York'
and TIMESTAMP < '2026-07-23 16:00:00 America/New_York'
limit 1000

Book Depth Events at L2 - Market by Level (MBL)#

Level 2 (L2) Book depth events are retrieved by specifying the PRL table, returning Market by Level (MBL) data. Typically book depth data is analysed using orderbook processing functions such as OB_SNAPSHOT as a simple retrieval will return the sequence of price level updates, rather than the reconstructed book.

Book Depth Price Level Update Retrieval#

select * from BINANCE.PRL
where SYMBOL_NAME = 'BTCUSD'
and TIMESTAMP >= '2026-07-28 00:00:00.000 GMT'
and TIMESTAMP < '2026-07-29 00:00:00.000 GMT'
limit 1000

Book Depth Events at L3 - Market by Order (MBO)#

Level 3 (L3) Book depth events are retrieved by specifying the PRL_FULL table, returning Market by Order (MBO) data . Typically book depth data is analysed using orderbook processing functions such as OB_SNAPSHOT as a simple retrieval will return the sequence of order messages, rather than the reconstructed book.

Full Book Depth Order Event Retrieval#

SELECT * FROM LSE_SAMPLE.PRL_FULL
WHERE SYMBOL_NAME='VOD'
and TIMESTAMP >= '2024-01-03 00:00:00 UTC'
and TIMESTAMP < '2024-01-04 00:00:00 UTC'
LIMIT 10

Book Depth Event Retrieval Results#
Symbol	Timestamp	PRICE	SIZE	SYMBOL_NAME	TICK_TYPE	OMDSEQ	UPDATE_TYPE	ORDER_ID	ORDER_TYPE	RECORD_TYPE
LSE_SAMPLE::VOD	2024-01-03 05:00:07.876	0	0	LSE_SAMPLE::VOD	PRL_FULL	0				Z
LSE_SAMPLE::VOD	2024-01-03 05:00:07.876	69	600	LSE_SAMPLE::VOD	PRL_FULL	0	A	233244094926020495	L	R
LSE_SAMPLE::VOD	2024-01-03 05:00:07.876	69	5,800	LSE_SAMPLE::VOD	PRL_FULL	1	A	233244094926209592	L	R
LSE_SAMPLE::VOD	2024-01-03 05:00:07.876	67.2	50,000	LSE_SAMPLE::VOD	PRL_FULL	2	A	232452446553925487	L	R
LSE_SAMPLE::VOD	2024-01-03 05:00:07.876	67	10,000	LSE_SAMPLE::VOD	PRL_FULL	3	A	233244094925636785	L	R
LSE_SAMPLE::VOD	2024-01-03 05:00:07.876	66.94	7,300	LSE_SAMPLE::VOD	PRL_FULL	4	A	233244094925636407	L	R
LSE_SAMPLE::VOD	2024-01-03 05:00:07.876	66.66	20,000	LSE_SAMPLE::VOD	PRL_FULL	5	A	233244094925845524	L	R
LSE_SAMPLE::VOD	2024-01-03 05:00:07.876	66.34	7,360	LSE_SAMPLE::VOD	PRL_FULL	6	A	233244094925636400	L	R
LSE_SAMPLE::VOD	2024-01-03 05:00:07.876	66	3,000	LSE_SAMPLE::VOD	PRL_FULL	7	A	233244094925636417	L	R
LSE_SAMPLE::VOD	2024-01-03 05:00:07.876	65	70,000	LSE_SAMPLE::VOD	PRL_FULL	8	A	233244094925636406	L	R

Auction Imbalance Events#

Auction Imbalance events are retrieved by specifying the IND table, along with the specified database, symbol and time range. Events are represented with PRICE, SIZE, IMB_SIDE, IMB_VOLUME and other fields.

Auction Imbalance Event Retrieval#

SELECT * FROM LSE_SAMPLE.IND
WHERE SYMBOL_NAME='VOD'
and TIMESTAMP >= '2024-01-03 00:00:00 UTC'
and TIMESTAMP < '2024-01-04 00:00:00 UTC'
LIMIT 10

Auction Imbalance Event Retrieval Results#
Symbol	Timestamp	PRICE	SIZE	SYMBOL_NAME	TICK_TYPE	OMDSEQ	EXCH_TIME	IMB_SIDE	IMB_VOLUME	AUCTION_TYPE
LSE_SAMPLE::VOD	2024-01-03 07:50:00.075	69	6,000	LSE_SAMPLE::VOD	IND	28	2024-01-03 07:50:00.074938266	B	2,900	O
LSE_SAMPLE::VOD	2024-01-03 07:50:00.076	69	6,880	LSE_SAMPLE::VOD	IND	5	2024-01-03 07:50:00.075324726	B	2,020	O
LSE_SAMPLE::VOD	2024-01-03 07:50:00.085	69	7,370	LSE_SAMPLE::VOD	IND	8	2024-01-03 07:50:00.085221086	B	1,530	O
LSE_SAMPLE::VOD	2024-01-03 07:50:00.088	69	8,870	LSE_SAMPLE::VOD	IND	1	2024-01-03 07:50:00.087546226	B	30	O
LSE_SAMPLE::VOD	2024-01-03 07:50:00.090	68.28	9,505	LSE_SAMPLE::VOD	IND	8	2024-01-03 07:50:00.089537586	B	1,945	O
LSE_SAMPLE::VOD	2024-01-03 07:50:00.102	68.28	9,847	LSE_SAMPLE::VOD	IND	1	2024-01-03 07:50:00.101007326	B	1,603	O
LSE_SAMPLE::VOD	2024-01-03 07:50:00.116	68.28	10,006	LSE_SAMPLE::VOD	IND	0	2024-01-03 07:50:00.116153726	B	1,444	O
LSE_SAMPLE::VOD	2024-01-03 07:50:00.459	68.28	10,006	LSE_SAMPLE::VOD	IND	1	2024-01-03 07:50:00.458532226	B	1,534	O
LSE_SAMPLE::VOD	2024-01-03 07:50:00.476	68.28	10,006	LSE_SAMPLE::VOD	IND	1	2024-01-03 07:50:00.475473766	B	1,634	O
LSE_SAMPLE::VOD	2024-01-03 07:50:00.482	68.28	10,006	LSE_SAMPLE::VOD	IND	3	2024-01-03 07:50:00.482214626	B	2,234	O

Events such as Earnings and Company Conference Calls#

Corporate Events such as Earning releases and Company Conference Calls explaining the earning releasses are listed in the EVENT table. This is currently restricted to the US market, and included in the US_COMP data asset, and specifically in the US_COMP_DAILY database.

Each event includes the:

TIMESTAMP of the event
EVENT_TYPE of the event, which is either EARNING_DATE or COMPANY_CONFERENCE_CALL

Event Retrieval for CSCO from 1st January to 1st June 2026.#

select * from US_COMP_DAILY.EVENT
where SYMBOL_NAME = 'CSCO'
and TIMESTAMP >= '2026-01-01 00:00:00 America/New_York'
and TIMESTAMP < '2026-06-01 00:00:00 America/New_York'

Static Reference Data Record#

The Symbol Universe database holds a standardized schema across all collected venues. Additional fields may be available by querying the STAT table in a specific database.

Static Reference Record Retrieval#

SELECT * FROM LSE_SAMPLE.STAT
WHERE SYMBOL_NAME='VOD'
and TIMESTAMP >= '2024-01-03 00:00:00 UTC'
and TIMESTAMP < '2024-01-04 00:00:00 UTC'
LIMIT 10

Static Reference Record Retrieval Results#
Symbol	Timestamp	SYMBOL_NAME	TICK_TYPE	OMDSEQ	NAME	ISIN	SEDOL	EXCH_SYMBOL	TRADING_CODE	MIC	OPERATING_MIC	SEC_TYPE	MKT_SEGMENT	MKT_SECTOR	COUNTRY_REG	CURRENCY	LOT_SIZE	EXCH_MKT_SIZE
LSE_SAMPLE::VOD	2024-01-03 04:00:00	LSE_SAMPLE::VOD	STAT	24,595	Vodafone Group plc	GB00BH4HKS39	BH4HKS3	VOD	133215	XLON	XLON	Equity	SET1	FE10	GB	GBX	1	30,000
LSE_SAMPLE::VOD	2024-01-03 04:00:00	LSE_SAMPLE::VOD	STAT	50,834	Vodafone Group plc	GB00BH4HKS39	BH4HKS3	VOD	133215	XLON	XLON	Equity	SET1	FE10	GB	GBX	1	30,000
LSE_SAMPLE::VOD	2024-01-03 04:00:00	LSE_SAMPLE::VOD	STAT	77,076	Vodafone Group plc	GB00BH4HKS39	BH4HKS3	VOD	133215	XLON	XLON	Equity	SET1	FE10	GB	GBX	1	30,000

1 Minute Trade Bars#

Pre-calculated 1 minute Trade bars are retrieved by specifying the TRD_1M table, along with the specified bar database, symbol and time range. Trade Bars are represented with fields: FIRST_TIME, FIRST, FIRST_SIZE, HIGH_TIME, HIGH, HIGH_SIZE, LOW_TIME, LOW, LOW_SIZE, LAST_TIME, LAST, LAST_SIZE, VWAP, TWAP, VOLUME, TRADE_TICK_COUNT, TRADE_CURRENCY

1-minute Trade bars are calculated from market open to market close (the end of continuous trading or the end of the closing auction, whichever is later); The first Bar is created one minute after the Open; or, if no activity has occurred by that time, after the first minute with any activity (i.e. after the first eligible trade for trade Bars, and after the first non-empty quote for quote bars).

Filtering Rules#

Trade Bars include all trades that are eligible to set the Last price, according to the exchange’s rules. In general, this means that:

Trades executed through the orderbook (either Lit or Dark) during continuous trading are included, with the exception of odd lots;
Auction trades are included;
All other trade types (off-market trades, late-reported trades, etc.) are excluded.

For US equity markets, the trades included in the Last price (and hence the Bars) are determined by the CTA and UTP SIPs, the organizations responsible for consolidating trade and BBO data from the various US stock exchanges. The specific trade types included in the Consolidated Last price are documented in their trade feed specifications(*), as below: https://www.ctaplan.com/publicdocs/ctaplan/CTS_Pillar_Output_Specification.pdf (page 82) https://www.utpplan.com/DOC/UtpBinaryOutputSpec.pdf (page 48)

Note that, because the bars also cover the pre- and post-market periods for US equities, condition T (Extended hours trade) is an exception to this, and will be included in the bars.

Currencies#

The Bar data is grouped by currency, with the field TRADE_CURRENCY indicating the currency in which the price fields (first/high/low/last) are expressed. It is possible to have multiple Bars for a single minute if there are multiple traded / quoted currencies in that minute.

Carrying Forward rules#

If there were no trades/quotes in the current bar, some fields are carried forward from the last valid bar.

Fields carried forward for Trade bars:

LAST_TIME
LAST_PRICE
LAST_SIZE
TRADE_CURRENCY

1 Minute Trade Bar Retrieval#

SELECT * FROM LSE_SAMPLE_BARS.TRD_1M
WHERE SYMBOL_NAME='VOD'
and TIMESTAMP >= '2024-01-03 00:00:00 UTC'
and TIMESTAMP < '2024-01-04 00:00:00 UTC'
LIMIT 10

1 Minute Trade Bar Retrieval Results#
Symbol	Timestamp	SYMBOL_NAME	TICK_TYPE	CLOUD_DB	TRADE_CURRENCY	FIRST_TIME	FIRST	FIRST_SIZE	HIGH_TIME	HIGH	HIGH_SIZE	LOW_TIME	LOW	LOW_SIZE	LAST_TIME	LAST	LAST_SIZE	VWAP	TWAP	VOLUME	TRADE_TICK_COUNT
LSE_SAMPLE_BARS::VOD	2024-01-03 08:01:00	LSE_SAMPLE_BARS::VOD	TRD_1M	LSE	GBX	2024-01-03 08:00:06.232	70	184,613	2024-01-03 08:00:39.921	70.41	5,700	2024-01-03 08:00:06.232	70	184,613	2024-01-03 08:00:51.881	70.36	4,742	70.0737972481	70.2288509894	238,528	23
LSE_SAMPLE_BARS::VOD	2024-01-03 08:02:00	LSE_SAMPLE_BARS::VOD	TRD_1M	LSE	GBX	2024-01-03 08:01:53.833	70.45	5,657	2024-01-03 08:01:55.576	70.57	2,039	2024-01-03 08:01:53.833	70.45	5,657	2024-01-03 08:01:57.450	70.5	6,150	70.4931693876	70.4995881304	150,253	20
LSE_SAMPLE_BARS::VOD	2024-01-03 08:03:00	LSE_SAMPLE_BARS::VOD	TRD_1M	LSE	GBX	2024-01-03 08:02:04.030	70.51	327	2024-01-03 08:02:34.619	70.52	5,459	2024-01-03 08:02:44.016	70.44	5,760	2024-01-03 08:02:44.016	70.44	5,760	70.4684794007	70.4740251921	32,040	18
LSE_SAMPLE_BARS::VOD	2024-01-03 08:04:00	LSE_SAMPLE_BARS::VOD	TRD_1M	LSE	GBX	2024-01-03 08:03:10.598	70.43	2,850	2024-01-03 08:03:45.045	70.44	17,124	2024-01-03 08:03:55.985	70.41	5,000	2024-01-03 08:03:55.985	70.41	5,000	70.4328525667	70.430589045	24,974	3
LSE_SAMPLE_BARS::VOD	2024-01-03 08:05:00	LSE_SAMPLE_BARS::VOD	TRD_1M	LSE	GBX	2024-01-03 08:04:52.586	70.51	5,093	2024-01-03 08:04:52.586	70.53	9,000	2024-01-03 08:04:54.271	70.46	711	2024-01-03 08:04:54.294	70.46	1,352	70.5045271018	70.4722646345	61,472	13
LSE_SAMPLE_BARS::VOD	2024-01-03 08:06:00	LSE_SAMPLE_BARS::VOD	TRD_1M	LSE	GBX	2024-01-03 08:05:00.144	70.43	7	2024-01-03 08:05:00.144	70.43	7	2024-01-03 08:05:00.144	70.43	7	2024-01-03 08:05:15.027	70.43	4,614	70.43	70.43	7,814	5
LSE_SAMPLE_BARS::VOD	2024-01-03 08:07:00	LSE_SAMPLE_BARS::VOD	TRD_1M	LSE	GBX	2024-01-03 08:06:00.017	70.36	1,580	2024-01-03 08:06:00.017	70.36	1,580	2024-01-03 08:06:15.026	70.24	100	2024-01-03 08:06:15.026	70.24	1,440	70.3156606137	70.2694658487	4,791	5
LSE_SAMPLE_BARS::VOD	2024-01-03 08:08:00	LSE_SAMPLE_BARS::VOD	TRD_1M	LSE	GBX	2024-01-03 08:07:00.027	70.27	803	2024-01-03 08:07:33.169	70.42	3,729	2024-01-03 08:07:00.027	70.27	803	2024-01-03 08:07:33.169	70.42	3,729	70.3987786742	70.3716193954	20,576	8
LSE_SAMPLE_BARS::VOD	2024-01-03 08:09:00	LSE_SAMPLE_BARS::VOD	TRD_1M	LSE	GBX	2024-01-03 08:08:35.140	70.42	711	2024-01-03 08:08:58.543	70.49	11,122	2024-01-03 08:08:35.140	70.42	711	2024-01-03 08:08:59.251	70.47	100	70.4582851495	70.4570917136	52,611	13
LSE_SAMPLE_BARS::VOD	2024-01-03 08:10:00	LSE_SAMPLE_BARS::VOD	TRD_1M	LSE	GBX	2024-01-03 08:09:00.619	70.46	100	2024-01-03 08:09:05.549	70.51	1,408	2024-01-03 08:09:00.619	70.46	100	2024-01-03 08:09:05.549	70.51	4,560	70.4884943452	70.5058488405	11,583	6

1 Minute Quote Bars#

Pre-calculated 1 minute Quote bars are retrieved by specifying the QTE_1M table, along with the specified bar database, symbol and time range. Bars databases have the suffix _BAR. Quote Bars are represented with fields: FIRST_BID_TIME, FIRST_BID_PRICE, FIRST_BID_SIZE, FIRST_ASK_TIME, FIRST_ASK_PRICE, FIRST_ASK_SIZE, HIGH_BID_TIME, HIGH_BID, HIGH_BID_SIZE, ASK_PRICE_AT_HIGH_BID, ASK_SIZE_AT_HIGH_BID, LOW_ASK_TIME, LOW_ASK, LOW_ASK_SIZE, BID_PRICE_AT_LOW_ASK, BID_SIZE_LOW_ASK, LAST_BID_TIME, LAST_BID_PRICE, LAST_BID_SIZE, LAST_ASK_TIME, LAST_ASK_PRICE, LAST_ASK_SIZE, MID_TWAP, MID_MEDIAN, MID_LAST, SPREAD_MIN, SPREAD_MAX, SPREAD_TWAP, SPREAD_MEDIAN, SPREAD_LAST, QUOTE_CURRENCY, QUOTE_TICK_COUNT

1-minute Quote bars are calculated for the duration of the continuous trading session, excluding intraday auctions or other breaks in trading.

Filtering Rules#

Quotes are filtered as follows:

If the Bid side is empty, the quote is excluded from all Bid statistics other than the Last bid fields;
If the Ask side is empty, the quote is excluded from all Ask statistics other than the Last ask fields;
If either the Bid or the Ask side is empty, the quote is excluded from Mid and Spread statistics;
If both the Bid and the Ask sides are empty, the quote is excluded from the quote count;
For country-composite datasets (e.g. US_COMP, CA_COMP), quote bars are computed based on the NBBO.

Currencies#

The Bar data is grouped by currency, with the field QUOTE_CURRENCY indicating the currency in which the price fields (first/high/low/last) are expressed. It is possible to have multiple Bars for a single minute if there are multiple traded / quoted currencies in that minute.

Carrying Forward rules#

If there were no /quotes in the current bar, some fields are carried forward from the last valid bar. For quote bars, one or both sides may be carried forward from the previous bar - for example, if the Bid side updated but the Ask side did not, the last Ask fields will be carried forward.

Fields carried forward for Quote bars:

LAST_BID_TIME
LAST_BID_PRICE
LAST_BID_SIZE
LAST_ASK_TIME
LAST_ASK_PRICE
LAST_ASK_SIZE
MID_LAST
SPREAD_LAST
QUOTE_CURRENCY

1 Minute Quote Bar Retrieval#

SELECT * FROM LSE_SAMPLE_BARS.QTE_1M
WHERE SYMBOL_NAME='VOD'
and TIMESTAMP >= '2024-01-03 00:00:00 UTC'
and TIMESTAMP < '2024-01-04 00:00:00 UTC'
LIMIT 10

1 Minute Quote Bar Retrieval Results#
Symbol	Timestamp	SYMBOL_NAME	TICK_TYPE	FIRST_BID_TIME	FIRST_BID_PRICE	FIRST_BID_SIZE	FIRST_ASK_TIME	FIRST_ASK_PRICE	FIRST_ASK_SIZE	HIGH_BID_TIME	HIGH_BID	HIGH_BID_SIZE	ASK_PRICE_AT_HIGH_BID	ASK_SIZE_AT_HIGH_BID	LOW_ASK_TIME	LOW_ASK	LOW_ASK_SIZE	BID_PRICE_AT_LOW_ASK	BID_SIZE_LOW_ASK	LAST_BID_TIME	LAST_BID_PRICE	LAST_BID_SIZE	LAST_ASK_TIME	LAST_ASK_PRICE	LAST_ASK_SIZE	MID_TWAP	MID_MEDIAN	MID_LAST	SPREAD_MIN	SPREAD_MAX	SPREAD_TWAP	SPREAD_MEDIAN	SPREAD_LAST	QUOTE_CURRENCY	QUOTE_TICK_COUNT	CLOUD_DB
LSE_SAMPLE_BARS::VOD	2024-01-03 08:01:00	LSE_SAMPLE_BARS::VOD	QTE_1M	2024-01-03 08:00:06.226	70.25	2,809	2024-01-03 08:00:06.226	69.76	100	2024-01-03 08:00:39.919	70.38	4,716	70.41	5,700	2024-01-03 08:00:06.226	69.76	100	70.25	2,809	2024-01-03 08:00:59.796	70.36	247	2024-01-03 08:00:59.796	70.45	5,657	70.2922304459	70.355	70.405	-0.49	0.41	0.10209989958	0.12	0.09	GBX	526	LSE
LSE_SAMPLE_BARS::VOD	2024-01-03 08:02:00	LSE_SAMPLE_BARS::VOD	QTE_1M	2024-01-03 08:01:00.799	70.34	493	2024-01-03 08:01:00.799	70.45	5,657	2024-01-03 08:01:55.577	70.54	100	70.61	711	2024-01-03 08:01:23.004	70.44	2,160	70.35	234	2024-01-03 08:01:59.461	70.51	100	2024-01-03 08:01:59.461	70.6	2,236	70.4121677843	70.475	70.555	0.04	0.12	0.0974883870205	0.08	0.09	GBX	283	LSE
LSE_SAMPLE_BARS::VOD	2024-01-03 08:03:00	LSE_SAMPLE_BARS::VOD	QTE_1M	2024-01-03 08:02:00.189	70.51	100	2024-01-03 08:02:00.189	70.61	16,156	2024-01-03 08:02:00.190	70.52	322	70.61	2,084	2024-01-03 08:02:42.034	70.49	711	70.44	6,865	2024-01-03 08:02:47.656	70.41	6,814	2024-01-03 08:02:47.656	70.51	5,093	70.5214428784	70.535	70.46	0.00999999999999	0.13	0.0952159301801	0.09	0.1	GBX	409	LSE
LSE_SAMPLE_BARS::VOD	2024-01-03 08:04:00	LSE_SAMPLE_BARS::VOD	QTE_1M	2024-01-03 08:03:00.466	70.41	6,814	2024-01-03 08:03:00.466	70.5	2,252	2024-01-03 08:03:45.045	70.44	17,124	70.48	13,173	2024-01-03 08:03:55.985	70.44	711	70.36	3,904	2024-01-03 08:03:57.934	70.39	1,381	2024-01-03 08:03:57.934	70.49	7,154	70.4415780058	70.435	70.44	0.04	0.13	0.0972481271206	0.09	0.1	GBX	114	LSE
LSE_SAMPLE_BARS::VOD	2024-01-03 08:05:00	LSE_SAMPLE_BARS::VOD	QTE_1M	2024-01-03 08:04:00.107	70.39	1,481	2024-01-03 08:04:00.107	70.49	7,154	2024-01-03 08:04:52.590	70.51	100	70.57	711	2024-01-03 08:04:00.107	70.49	7,154	70.39	1,481	2024-01-03 08:04:59.430	70.43	2,682	2024-01-03 08:04:59.430	70.5	4,716	70.4693922495	70.47	70.465	0.04	0.11	0.0789207419899	0.07	0.07	GBX	204	LSE
LSE_SAMPLE_BARS::VOD	2024-01-03 08:06:00	LSE_SAMPLE_BARS::VOD	QTE_1M	2024-01-03 08:05:00.136	70.43	2,675	2024-01-03 08:05:00.136	70.5	4,716	2024-01-03 08:05:00.224	70.45	711	70.52	711	2024-01-03 08:05:15.026	70.42	711	70.4	100	2024-01-03 08:05:54.602	70.36	2,330	2024-01-03 08:05:54.602	70.42	4,440	70.4101993686	70.47	70.39	0.00999999999999	0.09	0.0653058599492	0.07	0.06	GBX	85	LSE
LSE_SAMPLE_BARS::VOD	2024-01-03 08:07:00	LSE_SAMPLE_BARS::VOD	QTE_1M	2024-01-03 08:06:00.015	70.36	750	2024-01-03 08:06:00.015	70.42	4,440	2024-01-03 08:06:00.015	70.36	750	70.42	4,440	2024-01-03 08:06:28.573	70.27	2,072	70.21	10,000	2024-01-03 08:06:54.402	70.27	14,355	2024-01-03 08:06:54.402	70.34	2,450	70.3002277236	70.28	70.305	0.04	0.1	0.0747613570059	0.08	0.07	GBX	304	LSE
LSE_SAMPLE_BARS::VOD	2024-01-03 08:08:00	LSE_SAMPLE_BARS::VOD	QTE_1M	2024-01-03 08:07:00.025	70.27	13,552	2024-01-03 08:07:00.025	70.34	2,450	2024-01-03 08:07:33.153	70.38	2,700	70.42	5,151	2024-01-03 08:07:00.025	70.32	711	70.27	13,552	2024-01-03 08:07:59.610	70.34	5,400	2024-01-03 08:07:59.610	70.41	4,716	70.363405669	70.355	70.375	0.03	0.09	0.0643646519383	0.06	0.07	GBX	281	LSE
LSE_SAMPLE_BARS::VOD	2024-01-03 08:09:00	LSE_SAMPLE_BARS::VOD	QTE_1M	2024-01-03 08:08:07.501	70.37	13,542	2024-01-03 08:08:07.501	70.41	4,716	2024-01-03 08:08:58.550	70.48	2,800	70.51	1,408	2024-01-03 08:08:07.501	70.41	4,716	70.37	13,542	2024-01-03 08:08:59.397	70.45	100	2024-01-03 08:08:59.397	70.51	6,124	70.4258961123	70.46	70.48	0.01	0.08	0.0506562029753	0.06	0.06	GBX	347	LSE
LSE_SAMPLE_BARS::VOD	2024-01-03 08:10:00	LSE_SAMPLE_BARS::VOD	QTE_1M	2024-01-03 08:09:00.182	70.45	100	2024-01-03 08:09:00.182	70.51	1,408	2024-01-03 08:09:05.551	70.51	100	70.55	9,800	2024-01-03 08:09:05.539	70.49	1,055	70.46	100	2024-01-03 08:09:55.303	70.48	4,251	2024-01-03 08:09:55.303	70.52	4,716	70.5048733659	70.505	70.5	0.03	0.08	0.0642580828513	0.06	0.04	GBX	269	LSE

1 Minute Latency Bars for Real Time Data#

Pre-calculated 1 minute Latency bars are retrieved by specifying the LAT_1M table, along with the specified bar database, symbol and time range. Latency bars are available for Real Time venues for the prior 48 hours.

Latency is identified through:

Load Latency - Latency between Collection and Loading into the Memory Database
Collection Latency - Latency between the Exchange Publication and Real Time Collection

Each minute 4 metrics are recorded for every real time collected symbol:

AVG_LATENCY_COLLECTION - Average Collection Latency Across the Minute Period
MAX_LATENCY_COLLECTION - Maximum Collection Latency Across the Minute Period
AVG_LATENCY_LOAD - Average Load Latency Across the Minute Period
MAX_LATENCY_LOAD - Maximum Load Latency Across the Minute Period

1 Minute Latency Bar Retrieval for CSCO across prior 48 Hours#

select * from US_COMP_BARS.LAT_1M
where SYMBOL_NAME = 'CSCO'
and TIMESTAMP >= DATEADD('HOUR',-48,NOW())
and TIMESTAMP < NOW()
limit 1000

1 Hour VWAP Bars#

Pre-calculated 1 Hour VWAP bars are retrieved by specifying the VWAP_1H table, along with the specified bar database, symbol and time range. VWAP Bars include the following fields:

VWAP - Volume Weighted Average Price
TWAP - Time Weighted Average Price
VOLUME - Total volume of eligible trades in the current bar interval
CURRENCY - Currency in which the instrument’s price is expressed

1 Hour VWAP Bars for CSCO on 3rd January 2024#

select * from US_COMP_SAMPLE_BARS.VWAP_1H
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 00:00:00 America/New_York'
and TIMESTAMP < '2024-01-04 00:00:00 America/New_York'
limit 1000

Daily Quote Metrics against NBBO#

Quote Performance against the NBBO metrics are retrieved by specifying the QTE_NBBO_DAY table. This table is only available for Composite datasets which combine fragmented liquidity providing both the exchange quotes and NBBO. The table includes fields providing either the time an exchange is at the NBBO, or the time weighted average Size for the Exchange Bid and Ask when at the NBBO. As the exhchange may be at the NBBO on one side and not on the other, Durations in Seconds are returned for the BID, ASK, BOTH and EITHER.

Fields include:

NBBO_DURATION - Main Session Duration in seconds that a valid NBBO is present
EXCHANGE - Exchange from which the update originates
AT_NBBO_BOTH_DURATION - Main Session Duration in seconds for the exchange to be at both the NBBO Bid and Ask
AT_NBBO_BOTH_SIZE - Main Session Time Weighted Average of the sum of BID_SIZE and ASK_SIZE from Exchange, when at the NBBO
AT_NBBO_EITHER_DURATION - Main Session Duration in seconds for the exchange to be at either or both the NBBO Bid or Ask
AT_NBBO_EITHER_SIZE - Main Session Time Weighted Average of the Bid and Ask Size from Exchange, when at either or both the NBBO Bid or Ask. Size not at NBBO is not included.
AT_NBBO_ASK_DURATION - Main Session Duration in seconds for the exchange to be at the NBBO Ask
AT_NBBO_ASK_SIZE - Main Session Time Weighted Average of the ASK_SIZE from Exchange, when at the NBBO Ask
AT_NBBO_BID_DURATION - Main Session Duration in seconds for the exchange to be at the NBBO Bid
AT_NBBO_BID_SIZE - Main Session Time Weighted Average of the BID_SIZE from Exchange, when at the NBBO Bid

US Market Share Metrics Comparing Quotes to NBBO for CSCO#

select * from US_COMP_MKT_SHARE.QTE_NBBO_DAY
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 00:00:00 America/New_York'
and TIMESTAMP < '2024-01-04 00:00:00 America/New_York'
limit 1000

Daily Trade Metrics against NBBO#

Trade Performance against the NBBO metrics are retrieved by specifying the TRD_NBBO_DAY table. This table is only available for Composite datasets which combine fragmented liquidity providing both the exchange trades and NBBO. Each trade is compared against the NBBO to determine whether it is:

AT_NBBO - At the NBBO
AT_MID - At the Mid Price
INSIDE_NBBO - Inside the NBBO
OUTSIDE_NBBO - Outside the NBBO

The table returns metrics based on TRADE_COUNT, TRADE_VALUE and VOLUME for each EXCHANGE.

Additionally Block trades are identified through the fields:

TRADE_COUNT_BLOCK - Block Count where block is a trade with value > $200,000 or volume > 10,000
TRADE_VALUE_BLOCK - Block Traded Value where block is a trade with value > $200,000 or volume > 10,000
BLOCK_VOLUME - Total volume of block trades executed on the current day

US Market Share Metrics Comparing Trades to NBBO for CSCO#

select * from US_COMP_MKT_SHARE.TRD_NBBO_DAY
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 00:00:00 America/New_York'
and TIMESTAMP < '2024-01-04 00:00:00 America/New_York'
limit 1000

Enriched Trade Events#

Enriched Trade events are retrieved by specifying the TRD_TCA table, along with the specified database, symbol and time range. The table is generated to both simplify and accelerate TCA analysis. Trade Events are enriched with prevailing quotes and mid price offsets to simplify TCA analysis Rather than manually calculating mark outs by repeatedly joining trades to prevailing quotes for each offset period, the work has already been performed.

This table is copy of the TRD table, plus:

Prevailing Bid and Ask from the NBBO for a Composite, and QTE for a venue
Prevailing Mid Price
Mid Price at 21 offsets (6 back, and 15 forward).

The Mid Price Offsets are:

Back 60s, 30s, 10s, 1s, 100ms & 10ms
Forward 10ms, 100ms, 500ms, 1s, 2s, 5s, 10s, 30s, 60s, 90s, 2min, 3min, 5min, 10min & 15min

Enriched Trade Retrieval for CSCO on 3rd January 2026#

select * from US_COMP_TCA.TRD_TCA
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2026-01-03 00:00:00 America/New_York'
and TIMESTAMP < '2026-01-04 00:00:00 America/New_York'
limit 1000

Futures Continuous Contracts#

Continuous contracts definitions are based on either contract expiry, Trade Volume, Open Interest or Trade count from individual contracts within the Product.

They are queried using the following syntax, across Tick, Derived such as Minute Bar and Daily databases:

<ROOT>\1 - Front Month to <ROOT>\12 - Twelfth Month
<ROOT>_r_oi - Highest Open Interest
<ROOT>_r_vol - Highest Trade Volume
<ROOT>_r_tdi - Tick Data Methodology which depends on product and is only available in the TDI_FUT database.

For the Front to Twelfth Month continuous contracts, the roll date is the expiration data of the current contract.

For the volume and open-interest based continuous contracts, each day the system identifies the most active contract (based on volume or open interest). A roll occurs only if the most active contract has changed. Two key rules are applied to ensure consistency:

The new most active contract must have an expiration date later than the current contract.
The roll takes effect the day after the most active contract is identified.

Continuous Contracts can be retrieved by changing the SYMBOL_NAME to the Continuous Contract. Additionally the SYMBOL_DATE must also be specified.

Trade Retrieval for the Front Month Continuous Contract for ES#

SELECT * FROM CME_SAMPLE.TRD
WHERE SYMBOL_NAME='ES\\1'                --
and TIMESTAMP >= '2024-01-03 00:00:00 UTC'
and TIMESTAMP < '2024-01-04 00:00:00 UTC'
and symbol_date = 20240103
LIMIT 1000

Front Month to Twelfth Month Continuous Contracts can also be specified with the Bloomberg [BSYM] symbology, using the syntax:

<Bloomberg ROOT>1 to <Bloomberg ROOT>12

For example Brent Crude (exchange symbol BRN), has Bloomberg Product code CO, producing Bloomberg Continuous Contract CO1

DAY Retrieval for the Bloomberg Front Month Continuous Contract for Brent Crude#

select * from BSYM::ICE_EU_COM_SAMPLE_DAILY.DAY
where SYMBOL_NAME='CO1 Comdty'                -- Front Month Continuous Contract ([Product code]1)
and TIMESTAMP >= '2024-01-01 00:00:00 UTC'
and TIMESTAMP < '2024-04-01 00:00:00 UTC'
and UPDATE_TYPE = 'Summary'
and symbol_date = 20240401

Latest Trade & Quote / NBBO#

The latest prices for all instruments from a venue are most efficiently retrieved by quering the SNAP table within databases with the _LATEST suffix. which combines the last value caches for both trades and quotes / NBBO. The latest prices can also be returned by querying the traditional TRD, QTE, and NBBO tables, and aggregating for the last values for each symbol. The SNAP table, only holds the last venue, and is consequently much faster to retrieve the latest state across the whole market.

As this is a real time table it is only made available to authorized subscribers.

US_COMP_LATEST provides SNAP which combines the SNAP_TRD and SNAP_NBBO tables. All other _LATEST databases provide SNAP which combines the SNAP_TRD and SNAP_QTE tables.

Latest Trade & NBBO Retrieval for US Equities#

select * from US_COMP_LATEST.SNAP
where symbol_name = '-'
and TIMESTAMP = NOW()

Latest NBBO#

The latest NBBO for all US equity instruments are most efficiently retrieved by quering the SNAP_NBBO table in the US_COMP_LATEST database. This SNAP_NBBO table is a last value cache for storing the latest NBBO quotes for each symbol. This includes for each symbol the BID_PRICE, ASK_PRICE, BID_SIZE, and ASK_SIZE.

Latest NBBO Retrieval for US Equities#

select * from US_COMP_LATEST.SNAP_NBBO
where symbol_name = '-'
and TIMESTAMP = NOW()

Latest Quotes#

The latest quotes for all instruments from a venue are most efficiently retrieved by quering the SNAP_QTE table within databases with the _LATEST suffix. This SNAP_QTE table is a last value cache for storing the latest quotes for each symbol. This includes for each symbol the BID_PRICE, ASK_PRICE, BID_SIZE, and ASK_SIZE.

Latest Quote Retrieval for NYMEX Futures & Spreads#

select * from NYMEX_LATEST.SNAP_QTE
where symbol_name = '-'
and TIMESTAMP = NOW()

Latest Trades#

The latest trades for all instruments from a venue are most efficiently retrieved by quering the SNAP_TRD table within databases with the _LATEST suffix. This SNAP_TRD table is a last value cache for storing the latest trade for each symbol. This includes for each symbol the PRICE and SIZE, together with the current accumulated VOLUME, plus HIGH, LOW, OPEN, and previous CLOSE, and CLOSE_DATE.

Derivative venues also include: SETTLE_PRICE, SETTLE_DATE, OPEN_INT, and OPEN_INT_DATE.

Latest Trade Retrieval for US Equities#

select * from US_COMP_LATEST.SNAP_TRD
where symbol_name = '-'
and TIMESTAMP = NOW()