Composite Market Data Analysis#
A series of simple examples are provided showing how to analyze consolidated market data across multiple venues using OneTick Cloud composite databases. Composite databases aggregate trades and quotes from multiple exchanges and venues into a single unified dataset.
Composite Databases#
Composite databases consolidate fragmented liquidity across multiple equity venues for a given region or country. Consolidating trades, quotes, and providing an NBBO and daily rollup.
This is available for:
AU_COMP- Consolidated Across All Australian Equity VenuesCA_COMP- Consolidated Across All Canadian Equity VenuesEU_COMP- Consolidated Across All European Equity VenuesIN_COMP- Consolidated Across All Indian Equity VenuesJP_COMP- Consolidated Across All Japanese Equity VenuesKR_COMP- Consolidated Across All Korean Equity VenuesMX_COMP- Consolidated Across All Mexican Equity VenuesTW_COMP- Consolidated Across All Taiwanese Equity VenuesUS_COMP- Consolidated Across All US Equity Venues
Samples of the Composite datasets covering the first 3 months of 2024 are available for:
CA_COMP_SAMPLE- Consolidated Trades & Quotes Across All Canadian VenuesEU_COMP_SAMPLE- Consolidated Trades & Quotes Across All European VenuesUS_COMP_SAMPLE- Consolidated Trades & Quotes Across All US Venues
Data is aggregated by venue in fields such as QUOTE_VENUE, TRADE_VENUE, or EXCHANGE (for US_COMP).
Quote Count Per Venue#
The number of quotes received from each venue can be calculated by aggregating quote data. This provides visibility into the trading activity and data volume from each exchange.
select QUOTE_VENUE, COUNT(*) as QUOTE_COUNT
from CA_COMP_SAMPLE.QTE
where SYMBOL_NAME='TD'
and TIMESTAMP >= '2024-01-03 09:30:00 UTC'
and TIMESTAMP < '2024-01-03 16:00:00 UTC'
group by QUOTE_VENUE
Trade Count Per Venue#
The number of trades executed on each venue can be calculated by aggregating trade data. This shows the distribution of trading activity across different exchanges.
select TRADE_VENUE, COUNT(*) as TRADE_COUNT
from CA_COMP_SAMPLE.TRD
where SYMBOL_NAME='TD'
and TIMESTAMP >= '2024-01-03 09:30:00 UTC'
and TIMESTAMP < '2024-01-03 16:00:00 UTC'
group by TRADE_VENUE
Volume Traded Across All Symbols Per Venue#
The total volume traded across all symbols for each venue can be calculated using daily summary data. This shows the overall trading volume aggregated by venue.
Daily volume data from the DAY table provides pre-aggregated volume statistics, avoiding the need to aggregate individual trades.
The composite venue (empty string '') and primary venue ('PRIM') are typically filtered out to show individual venue volumes.
select sum(VOLUME) as VOLUME, EXCHANGE
from US_COMP_SAMPLE_DAILY.DAY
where SYMBOL_NAME LIKE '%'
and TIMESTAMP >= '2024-01-03 00:00:00 UTC'
and TIMESTAMP < '2024-01-04 00:00:00 UTC'
and EXCHANGE!=''
and EXCHANGE!='PRIM'
group by EXCHANGE
Daily Composite Trade Bars#
Returns aggregated Daily OHLCV (Open, High, Low, Close, Volume) data for a specified symbol and date range from composite databases.
Multiple rows are returned per day, with each row representing either the exchange/venue or aggregated composite data.
In US_COMP, venues are identified by the EXCHANGE field, while other composites use VENUE_ID.
Each daily bar includes comprehensive volume and price information:
VOLUME- Total volumeVOLUME_ODD_LOT- Volume from odd-lot trades (< 100 shares)VOLUME_ROUND_LOT- Volume from round-lot trades (>= 100 shares)VOLUME_OFF_EXCHANGE- Volume from off-exchange tradesVOLUME_PRE_MARKET- Pre-market volume (before market open)VOLUME_POST_MARKET- Post-market volume (after market close)VOLUME_OPENING_AUCTION- Volume from opening auctionVOLUME_CLOSING_AUCTION- Volume from closing auctionOPEN_PRICE- Opening priceCLOSE_PRICE- Closing pricePRICE_OPENING_AUCTION- Opening auction execution pricePRICE_CLOSING_AUCTION- Closing auction execution priceHIGH_PRICE- Highest price during the dayLOW_PRICE- Lowest price during the day
select * from US_COMP_SAMPLE_DAILY.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 Quote NBBO Composite Bars#
Returns aggregated Daily NBBO (National Best Bid and Offer) data from composite quote databases. Similar to trade bars, multiple rows represent different venues, the primary exchange, and the composite aggregate. Useful for analyzing best bid/ask pricing across consolidated venues.
select * from US_COMP_SAMPLE_DAILY.QTE
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 NBBO Composite Bars#
Returns aggregated Daily Trade and NBBO data combined from composite databases. Provides unified view of both trade activity (OHLCV) and prevailing bid/ask prices on a daily basis. Useful for complete market structure analysis combining executions with prevailing quotes.
select * from US_COMP_SAMPLE_DAILY.NBBO
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
NBBO Retrieval from Composite#
Retrieves the consolidated National Best Bid and Offer (NBBO) quote data from the composite. For the US, NBBOs are pre-calculated by the consolidated tape. For other regions, NBBOs are constructed by OneTick across all venues that contribute to the composite. NBBO records include bid and ask prices, sizes, and the exchanges providing the best bid and offer.
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 1000
Trade Volume to NBBO Calculation#
Classifies and aggregates trade volumes by their position relative to the National Best Bid and Offer (NBBO). This analysis shows how many shares were executed at the bid, at the ask, inside the spread, at the mid price, and outside the NBBO. Useful for understanding order execution quality, market impact analysis, and trade classification for TCA (Transaction Cost Analysis).
The analysis joins trade data with prevailing NBBO quotes and classifies each trade into one of four categories:
VOLUME_AT_MID- Volume executed at the midpoint priceVOLUME_INSIDE_NBBO- Volume executed strictly inside the bid-ask spread (between bid and ask, but not at either)VOLUME_AT_NBBO- Volume executed at the bid or ask priceVOLUME_OUTSIDE_NBBO- Volume executed outside the NBBO (below bid or above ask)
SELECT
t.SYMBOL_NAME,
t.EXCHANGE,
SUM(SIZE_AT_MID) AS VOLUME_AT_MID,
SUM(SIZE_INSIDE_NBBO) AS VOLUME_INSIDE_NBBO,
SUM(SIZE_AT_NBBO) AS VOLUME_AT_NBBO,
SUM(SIZE_OUTSIDE_NBBO) AS VOLUME_OUTSIDE_NBBO,
COUNT(*) AS TRD_COUNT
FROM (
SELECT
t.SYMBOL_NAME,
t.PRICE,
t.SIZE,
t.EXCHANGE,
q.BID_PRICE,
q.ASK_PRICE,
(q.BID_PRICE + q.ASK_PRICE) / 2 AS MID_PRICE,
CASE
WHEN t.PRICE = (q.BID_PRICE + q.ASK_PRICE) / 2 THEN t.SIZE
ELSE 0
END AS SIZE_AT_MID,
CASE
WHEN t.PRICE > q.BID_PRICE AND t.PRICE < q.ASK_PRICE THEN t.SIZE
ELSE 0
END AS SIZE_INSIDE_NBBO,
CASE
WHEN t.PRICE = q.BID_PRICE OR t.PRICE = q.ASK_PRICE THEN t.SIZE
ELSE 0
END AS SIZE_AT_NBBO,
CASE
WHEN t.PRICE < q.BID_PRICE OR t.PRICE > q.ASK_PRICE THEN t.SIZE
ELSE 0
END AS SIZE_OUTSIDE_NBBO
FROM US_COMP_SAMPLE.TRD t, US_COMP_SAMPLE.NBBO q
WHERE t.SYMBOL_NAME = q.SYMBOL_NAME
AND sametime_as_existing(t.TIMESTAMP, q.TIMESTAMP, 0) = TRUE
AND t.SYMBOL_NAME = 'CSCO'
AND TIMESTAMP >= '2024-01-03 09:30:00 America/New_York'
AND TIMESTAMP < '2024-01-03 16:00:00 America/New_York'
) t
GROUP BY t.SYMBOL_NAME, t.EXCHANGE
ORDER BY TRD_COUNT DESC
Point in Time Trade and NBBO Snapshot for Composite#
Retrieves a combined snapshot of trade and NBBO quote data at a specific point in time for a composite dataset, using a lookback period to find the most recent data.
A specific point in time is selected with TIMESTAMP equal to a specified value. All Symbols are retrieved with SYMBOL_NAME LIKE '%'. A Lookback is defined in seconds, to check for the prevailing trade and NBBO before the selected time, up to the lookback period. Both the TRD and NBBO tables are retrieved from the composite.
select t.SYMBOL_NAME as SYMBOL_NAME,
t.PRICE as PRICE, t.SIZE as SIZE,
n.BID_PRICE as BID_PRICE, n.ASK_PRICE as ASK_PRICE
from US_COMP_SAMPLE.TRD t, US_COMP_SAMPLE.NBBO n -- Both the TRD and NBBO tables are retrieved
where t.SYMBOL_NAME LIKE '%' -- Retrieve All Symbols
and t.SYMBOL_NAME = n.SYMBOL_NAME
and TIMESTAMP = '2024-01-03 12:30:00 America/New_York'
and t.init_lookback = 86400 --how many seconds to look back for prevailing Trade
and n.init_lookback = 86400 --how many seconds to look back for prevailing NBBO Quote
Spread and Mid from Composite Quotes Per Venue#
Calculates bid-ask spread and mid-price from composite quotes by venue/exchange, providing venue-level pricing analysis.
Composite datasets include both quotes per venue/exchange and an NBBO. The QTE table includes the Top of Book Quotes associated with each venue. US_COMP uses the EXCHANGE field, while other Composites use QUOTE_VENUE. SPREAD = ASK_PRICE - BID_PRICE and MID_PRICE = (BID_PRICE + ASK_PRICE)/2.
select EXCHANGE, BID_PRICE, ASK_PRICE,
(BID_PRICE + ASK_PRICE)/2 as MID_PRICE,
(ASK_PRICE - BID_PRICE) as SPREAD
from US_COMP_SAMPLE.QTE
where SYMBOL_NAME = 'CSCO'
and TIMESTAMP >= '2024-01-03 09:30:00 America/New_York'
and TIMESTAMP < '2024-01-03 16:00:00 America/New_York'
and BID_SIZE > 0 and ASK_SIZE > 0
limit 1000
Rolling Spread and Mid Statistics from Composite NBBO#
Calculates rolling spread and mid-price statistics at each point in time from composite NBBO data, showing how these metrics evolve throughout a trading period.
Composite datasets include both quotes per venue/exchange and an NBBO. The NBBO can either be regulatory in the case of the US, or constructed. Rolling Statistics across the period calculate the Time Weighted Average, High and Low at every point in time for spread and mid-price metrics.
select
BID_PRICE, ASK_PRICE, MID_PRICE, SPREAD,
TW_AVG(MID_PRICE) OVER(order by TIMESTAMP asc) as ROLLING_TW_AVG_MID_PRICE,
MAX(MID_PRICE) OVER(order by TIMESTAMP asc) as ROLLING_HIGH_MID_PRICE,
MIN(MID_PRICE) OVER(order by TIMESTAMP asc) as ROLLING_LOW_MID_PRICE,
TW_AVG(SPREAD) OVER(order by TIMESTAMP asc) as ROLLING_TW_AVG_SPREAD,
MAX(SPREAD) OVER(order by TIMESTAMP asc) as ROLLING_HIGH_SPREAD,
MIN(SPREAD) OVER(order by TIMESTAMP asc) as ROLLING_LOW_SPREAD
from
(
-- Calculate the Mid Price and Spread
select BID_PRICE, ASK_PRICE,
(BID_PRICE + ASK_PRICE)/2 as MID_PRICE,
(ASK_PRICE - BID_PRICE) as SPREAD
from US_COMP_SAMPLE.NBBO
where SYMBOL_NAME = 'CSCO'
and TIMESTAMP >= '2024-01-03 09:30:00 America/New_York'
and TIMESTAMP < '2024-01-03 16:00:00 America/New_York'
and BID_SIZE > 0 and ASK_SIZE > 0
)
Spread and Mid from Composite NBBO#
Calculates bid-ask spread and mid-price from composite NBBO (National Best Bid and Offer) data, providing consolidated pricing metrics across all venues.
Composite datasets include both quotes per venue/exchange and an NBBO. The NBBO can either be regulatory in the case of the US, or constructed. SPREAD = ASK_PRICE - BID_PRICE and MID_PRICE = (BID_PRICE + ASK_PRICE)/2.
select BID_PRICE, ASK_PRICE,
(BID_PRICE + ASK_PRICE)/2 as MID_PRICE,
(ASK_PRICE - BID_PRICE) as SPREAD
from US_COMP_SAMPLE.NBBO
where SYMBOL_NAME = 'CSCO'
and TIMESTAMP >= '2024-01-03 09:30:00 America/New_York'
and TIMESTAMP < '2024-01-03 16:00:00 America/New_York'
and BID_SIZE > 0 and ASK_SIZE > 0
limit 1000
Spread and Mid Statistics from Composite NBBO#
Aggregates spread and mid-price statistics from composite NBBO quote data, calculating time-weighted averages and extremes across a trading period.
Composite datasets include both quotes per venue/exchange and an NBBO. The NBBO can either be regulatory in the case of the US, or constructed. Statistics across the period calculate the Time Weighted Average, High and Low for spread and mid-price metrics.
select
TW_AVG(MID_PRICE) as TW_AVG_MID_PRICE,
MAX(MID_PRICE) as HIGH_MID_PRICE,
MIN(MID_PRICE) as LOW_MID_PRICE,
TW_AVG(SPREAD) as TW_AVG_SPREAD,
MAX(SPREAD) as HIGH_SPREAD,
MIN(SPREAD) as LOW_SPREAD,
count(SPREAD) as QTE_COUNT
from
(
-- Calculate the Mid Price and Spread
select BID_PRICE, ASK_PRICE,
(BID_PRICE + ASK_PRICE)/2 as MID_PRICE,
(ASK_PRICE - BID_PRICE) as SPREAD
from US_COMP_SAMPLE.NBBO
where SYMBOL_NAME = 'CSCO'
and TIMESTAMP >= '2024-01-03 09:30:00 America/New_York'
and TIMESTAMP < '2024-01-03 16:00:00 America/New_York'
and BID_SIZE > 0 and ASK_SIZE > 0
)
Spread and Mid Statistics from Composite Venues#
Aggregates spread and mid-price statistics from composite venue quotes, grouped by exchange to show venue-level pricing analysis.
Composite datasets include both quotes per venue/exchange and an NBBO. The QTE table includes the Top of Book Quotes associated with each venue. US_COMP uses the EXCHANGE field, while other Composites use QUOTE_VENUE. Statistics across the period calculate the Time Weighted Average, High and Low, grouped by EXCHANGE.
select
EXCHANGE,
TW_AVG(MID_PRICE) as TW_AVG_MID_PRICE,
MAX(MID_PRICE) as HIGH_MID_PRICE,
MIN(MID_PRICE) as LOW_MID_PRICE,
TW_AVG(SPREAD) as TW_AVG_SPREAD,
MAX(SPREAD) as HIGH_SPREAD,
MIN(SPREAD) as LOW_SPREAD,
count(SPREAD) as QTE_COUNT
from
(
-- Calculate the Mid Price and Spread
select EXCHANGE, BID_PRICE, ASK_PRICE,
(BID_PRICE + ASK_PRICE)/2 as MID_PRICE,
(ASK_PRICE - BID_PRICE) as SPREAD
from US_COMP_SAMPLE.QTE
where SYMBOL_NAME = 'CSCO'
and TIMESTAMP >= '2024-01-03 09:30:00 America/New_York'
and TIMESTAMP < '2024-01-03 16:00:00 America/New_York'
and BID_SIZE > 0 and ASK_SIZE > 0
)
group by EXCHANGE
European Composite Data#
The European Composite (EU_COMP) consolidates trades and quotes across multiple European venues. Symbols in EU_COMP are defined as ISINs rather than ticker symbols, as ticker symbols are not consistent across European venues. Trades include additional MiFID-related fields such as trading venue, currency, trade period, and book type.
European Composite Trade Data Retrieval#
Retrieves trade records from the European Composite across multiple venues. Symbols are defined as ISINs to provide consistency across European trading venues.
SELECT * FROM EU_COMP_SAMPLE.TRD
WHERE SYMBOL_NAME in ('GB00BH4HKS39','FR001400J770','DE0005140008','GB00BP6MXD84')
and TIMESTAMP >= '2024-01-03 08:00:00 Europe/London'
and TIMESTAMP < '2024-01-03 16:00:00 Europe/London'
LIMIT 1000
European Composite Bar Creation#
Returns OHLC prices for the European Composite aggregated by venue and currency into fixed time intervals. The query applies grouping by TRADE_VENUE and CURRENCY to return calculated bars per venue, since the European Composite includes trades in multiple currencies.
SELECT TRADE_VENUE, CURRENCY,
FIRST(PRICE) as FIRST_PRICE,
MAX(PRICE) as HIGH_PRICE,
MIN(PRICE) as LOW_PRICE,
LAST(PRICE) as LAST_PRICE,
VWAP(PRICE_FIELD_NAME=PRICE, SIZE_FIELD_NAME=SIZE) as VWAP_PRICE,
SUM(SIZE) as VOLUME,
SUM(PRICE*SIZE) as TRADE_VALUE,
COUNT(SIZE) as TRADE_COUNT
FROM EU_COMP_SAMPLE.TRD
WHERE SYMBOL_NAME = 'GB00BH4HKS39'
and TIMESTAMP >= '2024-01-03 10:23:01 Europe/London'
and TIMESTAMP < '2024-01-03 10:27:48 Europe/London'
group by TRADE_VENUE, CURRENCY
European Composite Bar Creation with Fill Forward#
Returns OHLC prices for the European Composite with the last price filled forward for periods without trades. Uses TIME_SERIES_TYPE='STATE_TS' to extend the last price forward into subsequent time buckets. Filters trades to continuous trading periods and lit order book trades.
SELECT TRADE_VENUE,CURRENCY,
FIRST(PRICE) as FIRST_PRICE,
MAX(PRICE) as HIGH_PRICE,
MIN(PRICE) as LOW_PRICE,
LAST(PRICE,TIME_SERIES_TYPE='STATE_TS') as LAST_PRICE,
VWAP(PRICE_FIELD_NAME=PRICE, SIZE_FIELD_NAME=SIZE) as VWAP_PRICE,
SUM(SIZE) as VOLUME,
COUNT(SIZE) as TRADE_COUNT
FROM EU_COMP.TRD
WHERE SYMBOL_NAME = 'GB00BH4HKS39'
and TIMESTAMP >= '2024-01-03 08:00:00 Europe/London'
and TIMESTAMP < '2024-01-03 16:00:00 Europe/London'
and TRADE_PERIOD = '-'
and BOOK_TYPE = '0'
group by time_bucket(INTERVAL '5' MINUTE), TRADE_VENUE, CURRENCY
European Trade Daily Composite Bars#
Returns pre-calculated Daily OHLCV data from the European Composite for specified symbols and date ranges. Multiple rows are returned per day representing different venues and currencies. The composite aggregate is represented by empty string values for VENUE_ID and CURRENCY fields.
SELECT * FROM EU_COMP.DAY
WHERE SYMBOL_NAME ='GB00BH4HKS39'
and TIMESTAMP >= '2024-01-03 00:00:00 Europe/London'
and TIMESTAMP < '2024-01-04 00:00:00 Europe/London'
LIMIT 1000