Median spread each half hour, as a multiple of each name's tightest bucket (regular hours, ET)

Name: Median spread each half hour, as a multiple of each name's tightest bucket (regular hours, ET)
Creator: Strasmore
Published: 2026-08-08
License: https://www.strasmore.com/terms/

Answered against 22 years of US equities and 12 years of US options data and published with the query that produced it. This result is stored as of 2026-08-08, from Why Are Spreads Wider at the Open? Real Data.

as of 2026-08-08 series 13×4read in context →

Median spread each half hour, as a multiple of each name's tightest bucket (regular hours, ET) — 13 rows by 4 columns, computed 2026-08-08 from US exchange, SIP and OPRA data.
et_time	spy_x	aapl_x	etsy_x
09:30	1.01	2.36	4.98
10:00	1.01	1.98	3.4
10:30	1.01	1.67	2.81
11:00	1.01	1.5	2.01
11:30	1.01	1.34	2.02
12:00	1	1.33	1.72
12:30	1	1.33	1.52
13:00	1	1.13	1.46
13:30	1	1.11	1.51
14:00	1.01	1.02	1.47
14:30	1	1.12	1.27
15:00	1	1.01	1.26
15:30	1	1	1

Rows × columns: 13 × 4
Period covered: 09:30 to 15:30
Computed: 2026-08-08
Completeness: No missing values
Source: US exchange, SIP and OPRA market data
Licence: Strasmore terms · free, no signup
Formats: JSON · CSV · the SQL below

What each column holds

Column definitions for Median spread each half hour, as a multiple of each name's tightest bucket (regular hours, ET), derived from the stored result.
Column	Type	Range
`et_time`	date	09:30 to 15:30
`spy_x`	number	1 to 1.01
`aapl_x`	number	1 to 2.36
`etsy_x`	number	1 to 4.98

Computed from Strasmore's warehouse of US exchange, SIP and OPRA market data. Equity prices are delayed; options greeks and implied volatility are end-of-day. This result is stored, not recomputed on load — it is exactly the numbers that were returned on 2026-08-08, and the query below is what returned them.

the exact SQL behind every number

SELECT et_time,
       round(anyIf(rel, ticker = 'SPY'), 2) AS spy_x,
       round(anyIf(rel, ticker = 'AAPL'), 2) AS aapl_x,
       round(anyIf(rel, ticker = 'ETSY'), 2) AS etsy_x
FROM (
    SELECT ticker,
           et_time,
           median_bps / min(median_bps) OVER (PARTITION BY ticker) AS rel
    FROM (
        SELECT ticker,
               formatDateTime(toStartOfInterval(toTimeZone(sip_timestamp, 'America/New_York'), INTERVAL 30 MINUTE), '%H:%i') AS et_time,
               quantileExact(0.5)(toFloat64(ask_price - bid_price) / ((toFloat64(ask_price) + toFloat64(bid_price)) / 2) * 10000) AS median_bps
        FROM global_markets.cache_stocks_quotes
        WHERE ticker IN ('SPY', 'AAPL', 'ETSY')
          AND sip_timestamp >= toDateTime(today() - 14)
          AND sip_timestamp < toDateTime(today() - 3)
          AND (toHour(sip_timestamp) * 60 + toMinute(sip_timestamp)) BETWEEN 810 AND 1199
          AND bid_price > 0
          AND ask_price > bid_price
        GROUP BY ticker, et_time
    )
)
GROUP BY et_time
ORDER BY et_time

Run your own version of this

The same 22 years of US equities and 12 years of options data are queryable in SQL or plain English. A free account runs 100 queries a day and takes no card.

More from this analysisWhy Are Spreads Wider at the Open? Real Data

Share of quote updates at the one-cent minimum spread, by half hour (regular hours, ET) series 13×4 → MU: median quoted spread by half hour on July 7, 2026 (ET, regular hours) series 13×5 → Median quoted spread by phase of the trading day, in basis points (ET clock) ranking 5×4 → Session by session: how often, and by how much, the open ran wider than midday table 3×6 → The opening premium priced: median and 90th-percentile spread at the open (9:30-10:00 ET) vs. midday (12:00-14:00 ET) table 3×12 → Window guard: the UTC session filter maps to a 9:30 a.m. ET start on both ends of the rolling window scalar 1×2 → See all 2,170 queries →