Inside June 29, 2026 trade tape: wetin really happen

Dis na di inside gist wey follow di June 29, 2026 market recap: di raw data wey dey under di minute bars — 156.1 million single trades and 505.38 million NBBO quote updates for one normal Monday session. Every number na stored query result (open any panel make you see di exact SQL), and di two forensic finds for di day get full write-ups: one volume correction wey di tape own code dictionary demand, and one vendor file wey quietly stop for letter S.

Di trade tape, print by print

Di session check dey come first, we no dey assume am: di exchange holiday calendar get 0 rows for June 29, 2026, and SPY print exactly 390 minute bars inside di regular window — na full session, 9:30 am to 4:00 pm New York time. Across di extended day, di consolidated tape record 156.1 million individual trades.

WITH
    (SELECT count() FROM global_markets.stocks_market_holidays WHERE date = '2026-06-29') AS holiday_rows,
    (
        SELECT countIf(window_start >= '2026-06-29 13:30:00' AND window_start < '2026-06-29 20:00:00')
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'SPY' AND window_start >= '2026-06-29 00:00:00' AND window_start < '2026-06-30 00:00:00'
    ) AS spy_bars
SELECT
    holiday_rows AS holiday_rows_jun29,
    spy_bars AS spy_regular_session_bars,
    round(count() / 1e6, 1) AS trades_m,
    round(100.0 * countIf(size < 100) / count(), 2) AS odd_lot_pct_of_trades,
    round(100.0 * toFloat64(sumIf(size, size < 100)) / toFloat64(sum(size)), 2) AS odd_lot_pct_of_shares,
    multiIf(
        countIf(size <= 19) >= 0.5 * count(), 19,
        countIf(size <= 20) >= 0.5 * count(), 20,
        countIf(size <= 21) >= 0.5 * count(), 21,
        countIf(size <= 22) >= 0.5 * count(), 22,
        countIf(size <= 23) >= 0.5 * count(), 23,
        0) AS median_print_shares,
    round(countIf(size = 1) / 1e6, 1) AS one_share_trades_m,
    round(100.0 * countIf(size != round(size)) / count(), 2) AS fractional_pct_of_trades
FROM global_markets.stocks_trades
WHERE sip_timestamp >= '2026-06-29 00:00:00' AND sip_timestamp < '2026-06-30 00:00:00'

72.61% of all prints na odd lots — trades wey get fewer dan 100 shares — yet dem move only 8.79% of di raw shares — di uncorrected total wey di volume-forensics section below go trim. Di median print na 21 shares, wey dem measure wit a self-verifying bracket probe — cumulative counts at 19 through 23 shares — wey dey return 0 if di bracket miss, and e dey bounded so a miss go hold di post. 17.9 million prints na for exactly one share and 4.73% carry fractional sizes — a pattern wey dey consistent wit retail apps wey dey hit di tape one slice at a time, wit market makers wey dey take di oda side.

Volume forensics: how we take "matched volume" from the dictionary

If you add up the size field for every print wey happen for June 29, e go show say 24.79 billion shares trade. But that figure no correct, and the tape itself dey tell us so. Every print dey carry condition codes, and the SIP — na the Securities Information Processor wey dey put every venue inside one stream — dey publish one dictionary wey dey state, code by code, whether one print dey update consolidated volume. Some messages na just administrative re-broadcast of volume wey dem don already count one time. Instead of me to hardcode one list of "known" codes, this post derive the exclusion set from the dictionary itself:

SELECT
    count() AS excluded_codes,
    sum(id) AS excluded_id_checksum,
    arrayStringConcat(arraySort(groupArray(concat(toString(id), ' ', name))), ' · ') AS codes
FROM global_markets.stocks_condition_codes
WHERE asset_class = 'stocks'
  AND type = 'sale_condition'
  AND JSONExtractBool(update_rules, 'consolidated', 'updates_volume') = 0

3 sale conditions dey carry updates_volume = false for the consolidated feed: 15 Market Center Official Close · 16 Market Center Official Open · 38 Corrected Consolidated Close (per listing market). Na here the lesson dey: one earlier draft of the daily recap hardcode codes 15 and 16 — na the two official open/close messages wey every data guide dey mention — and e miss code 38 completely. Na the dictionary be the source of truth, no be one code list wey person remember.

The two blocks dey deliberate: one scalar subquery over the dictionary inside one scan of 156.1-million rows go pass our 30-second cap. So the block wey dey up derive the set as one row — wey get sanity-bound on the code count and one id checksum (69) — and the block wey dey down apply the derived literals. If the dictionary ever change, the checksum bound go hold this post for review; the literals no fit silently go stale.

SELECT
    round(toFloat64(sum(size)) / 1e9, 2) AS raw_shares_bn,
    round(toFloat64(sumIf(size, NOT hasAny(conditions, [15, 16, 38]))) / 1e9, 2) AS matched_shares_bn,
    round(toFloat64(sumIf(size, hasAny(conditions, [15, 16, 38]))) / 1e9, 2) AS excluded_shares_bn,
    countIf(hasAny(conditions, [15, 16, 38])) AS excluded_prints
FROM global_markets.stocks_trades
WHERE sip_timestamp >= '2026-06-29 00:00:00' AND sip_timestamp < '2026-06-30 00:00:00'

The correct accounting: 24.79 billion shares raw, 20.66 billion SIP-counted, 4.13 billion excluded across 91175 administrative prints. One definition, wey we state precisely: "matched volume" mean the volume wey the SIP own condition rules count toward the consolidated total — nothing pass that one. We no dey judge which trades get economic meaning; we dey apply the tape own accounting.

Di biggest prints, wey dem decode

Di biggest prints for di day na where condition codes stop to be small talk:

SELECT
    ticker,
    formatDateTime(toTimeZone(sip_timestamp, 'America/New_York'), '%H:%i:%S') AS et_time,
    round(toFloat64(size) / 1e6, 2) AS shares_m,
    toFloat64(price) AS price,
    round(toFloat64(size) * toFloat64(price) / 1e6, 1) AS notional_usd_m,
    round(100 * (toFloat64(size) * toFloat64(price)) / max(toFloat64(size) * toFloat64(price)) OVER (), 1) AS pct_of_biggest_dollar_print,
    arrayStringConcat(arrayMap(c -> toString(c), conditions), ', ') AS condition_id
FROM global_markets.stocks_trades
WHERE sip_timestamp >= '2026-06-29 00:00:00' AND sip_timestamp < '2026-06-30 00:00:00'
  AND size >= 10000000
  AND NOT hasAny(conditions, [15, 16, 38])
ORDER BY size DESC
LIMIT 10

Read di top rows as one worked example. Di biggest print for di day — 61.69 million CAG (Conagra) shares for $13.98, wey be roughly $862.5 million, wey dem stamp 16:04:53 ET under codes 8, 41 (8 Closing Prints, 41 Trade Thru Exempt) — na di NYSE closing auction result wey dem publish after 4:00 pm. E dey count toward volume exactly once. Di same 61.69 million shares come show face again under code 15, Market Center Official Close: na re-report wey di dictionary exclude, part of di 4.13 billion wey dem strip for up. Dat code-15 row dem strip am from di table for up by design, so im receipt get im own cheap one-row panel:

SELECT
    round(toFloat64(maxIf(size, has(conditions, 8))) / 1e6, 2) AS auction_print_shares_m,
    round(toFloat64(maxIf(size, has(conditions, 15))) / 1e6, 2) AS official_close_reprint_shares_m,
    round(toFloat64(maxIf(size, has(conditions, 8))) - toFloat64(maxIf(size, has(conditions, 15))), 2) AS share_difference,
    formatDateTime(toTimeZone(argMaxIf(sip_timestamp, size, has(conditions, 15)), 'America/New_York'), '%H:%i:%S') AS reprint_et,
    argMaxIf(exchange, size, has(conditions, 15)) AS reprint_exchange_id,
    countIf(has(conditions, 15)) AS cag_code15_prints
FROM global_markets.stocks_trades
WHERE ticker = 'CAG'
  AND sip_timestamp >= '2026-06-29 00:00:00' AND sip_timestamp < '2026-06-30 00:00:00'

Di biggest of di 6 official-close messages for CAG dey carry 61.69 million shares — di auction im 61.69 million exactly (difference: 0) — wey dem stamp 16:04:53 ET from exchange id 10, NYSE for di stocks directory: di listing market dey re-state im own auction.

Di other CAG rows na different animal — di 15.17 million-share print under codes 12, 2 (12 Form T / extended hours, 2 Average Price Trade) and two more under code 12 na post-auction prints wey dem report through FINRA wey im conditions dey carry updates_volume = true. Di SIP dey count dem as real prints, and our matched figure dey do di same.

For anoda place, code 9 dey mark di Nasdaq closing crosses — NVDA im $3389.5 million cross na di table im biggest print for dollars — and di codes for di SNAP print (53, 41) include 53, one Qualified Contingent Trade.

Di clock: ten minutes wey own di day

For minute-by-minute level, di trade tape dey gada almost entirely for di edges of di session:

SELECT
    formatDateTime(toTimeZone(toStartOfMinute(sip_timestamp), 'America/New_York'), '%H:%i') AS et_minute,
    round(count() / 1e6, 2) AS trades_m
FROM global_markets.stocks_trades
WHERE sip_timestamp >= '2026-06-29 00:00:00' AND sip_timestamp < '2026-06-30 00:00:00'
GROUP BY et_minute
ORDER BY trades_m DESC
LIMIT 10

Di 15:59 ET minute print 3.98 million trades — e pass sixty thousand for one second. Whether di rest of di top ten dey hug di open and di close na im be one-row measurement:

SELECT
    countIf(NOT (et_minute BETWEEN '09:30' AND '09:39' OR et_minute BETWEEN '15:50' AND '16:00')) AS entries_outside_open_close,
    countIf(et_minute BETWEEN '09:30' AND '09:39' OR et_minute BETWEEN '15:50' AND '16:00') AS entries_in_open_close,
    maxIf(et_minute, NOT (et_minute BETWEEN '09:30' AND '09:39' OR et_minute BETWEEN '15:50' AND '16:00')) AS outside_entry_minute,
    maxIf(trades_m, NOT (et_minute BETWEEN '09:30' AND '09:39' OR et_minute BETWEEN '15:50' AND '16:00')) AS outside_entry_trades_m
FROM (
    SELECT
        formatDateTime(toTimeZone(toStartOfMinute(sip_timestamp), 'America/New_York'), '%H:%i') AS et_minute,
        round(count() / 1e6, 2) AS trades_m
    FROM global_markets.stocks_trades
    WHERE sip_timestamp >= '2026-06-29 00:00:00' AND sip_timestamp < '2026-06-30 00:00:00'
    GROUP BY et_minute
    ORDER BY trades_m DESC
    LIMIT 10
)

9 of di ten dey siddon inside di opening ten minutes or di final ten before di close. Di one exception — 10:15 ET, one burst of 0.88 million prints — na true mid-morning anomaly; we no know wetin cause am from dis data, and we no go make up one. Di same clock time go show face again for down.

Di quote stream: half a billion updates

Di National Best Bid and Offer — di best buy and sell price across every exchange, wey dem dey re-publish anytime any of dem change — update 505.38 million times on June 29, against 156.1 million trades.

WITH
    (
        SELECT (round(countIf(ticker = 'QQQ') / 1e6, 2), round(countIf(ticker = 'NVDA') / 1e6, 2),
                round(countIf(ticker = 'AAPL') / 1e6, 2), round(countIf(ticker = 'TSLA') / 1e6, 2))
        FROM global_markets.cache_stocks_quotes
        WHERE ticker IN ('QQQ', 'NVDA', 'AAPL', 'TSLA')
          AND sip_timestamp >= '2026-06-29 00:00:00' AND sip_timestamp < '2026-06-30 00:00:00'
    ) AS pinned
SELECT
    round(count() / 1e6, 2) AS updates_m,
    pinned.1 AS qqq_updates_m,
    pinned.2 AS nvda_updates_m,
    round(pinned.1 - pinned.2, 2) AS qqq_minus_nvda_updates_m,
    round(pinned.2 - greatest(pinned.3, pinned.4), 2) AS nvda_minus_next_single_m
FROM global_markets.cache_stocks_quotes
WHERE sip_timestamp >= '2026-06-29 00:00:00' AND sip_timestamp < '2026-06-30 00:00:00'

Index products dey dominate di quoting: QQQ alone log 5.23 million updates against 1.8 million for NVDA, di single stock wey dem quote pass wey we check — 0.67 million updates clear of di next single name. Full per-ticker census of every listing no fit enter under di 30-second cap, so di leaderboard wey dey below na checked set, no be exhaustive ranking:

SELECT ticker, round(count() / 1e6, 2) AS updates_m
FROM global_markets.cache_stocks_quotes
WHERE ticker IN ('QQQ', 'SPY', 'TQQQ', 'IWM', 'SQQQ', 'NVDA', 'AAPL', 'TSLA')
  AND sip_timestamp >= '2026-06-29 00:00:00' AND sip_timestamp < '2026-06-30 00:00:00'
GROUP BY ticker
ORDER BY updates_m DESC

QQQ lead di checked set at 5.23 million updates; di top of di board na index funds and dia leveraged cousins, no be household stocks.

SPY under microscope: nanosecond receipts, and di spread two ways

WITH
    (
        SELECT (formatDateTime(toTimeZone(toStartOfSecond(sip_timestamp), 'America/New_York'), '%H:%i:%S'), count())
        FROM global_markets.cache_stocks_quotes
        WHERE ticker = 'SPY' AND sip_timestamp >= '2026-06-29 13:30:00' AND sip_timestamp < '2026-06-29 20:00:00'
        GROUP BY toStartOfSecond(sip_timestamp)
        ORDER BY count() DESC, toStartOfSecond(sip_timestamp) ASC
        LIMIT 1
    ) AS busiest_sec,
    ordered AS (
        SELECT
            toFloat64(ask_price - bid_price) AS spread,
            bid_price,
            ask_price,
            toFloat64(sip_timestamp - lagInFrame(sip_timestamp) OVER (ORDER BY sip_timestamp, sequence_number)) AS gap_s,
            greatest(toFloat64(least(leadInFrame(sip_timestamp, 1, toDateTime64('2026-06-29 20:00:00', 9)) OVER (ORDER BY sip_timestamp, sequence_number ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING), toDateTime64('2026-06-29 20:00:00', 9)) - sip_timestamp), 0) AS dwell_s,
            rowNumberInAllBlocks() AS rn
        FROM global_markets.cache_stocks_quotes
        WHERE ticker = 'SPY' AND sip_timestamp >= '2026-06-29 13:30:00' AND sip_timestamp < '2026-06-29 20:00:00'
    )
SELECT
    round(count() / 1e6, 2) AS spy_updates_m,
    busiest_sec.1 AS busiest_second_et,
    toUInt8(startsWith(busiest_sec.1, '10:15')) AS busiest_second_in_1015,
    busiest_sec.2 AS busiest_second_updates,
    round(minIf(gap_s, rn > 0 AND gap_s > 0) * 1e9) AS min_gap_between_updates_ns,
    round(quantileExactIf(0.5)(gap_s, rn > 0) * 1e6, 1) AS median_gap_us,
    countIf(rn > 0 AND gap_s = 0) AS identical_timestamp_pairs,
    round(sumIf(spread * dwell_s, bid_price > 0 AND ask_price >= bid_price) / sumIf(dwell_s, bid_price > 0 AND ask_price >= bid_price) * 100, 2) AS time_weighted_avg_spread_cents,
    round(avgIf(spread, bid_price > 0 AND ask_price >= bid_price) * 100, 2) AS per_update_avg_spread_cents,
    round((avgIf(spread, bid_price > 0 AND ask_price >= bid_price) - sumIf(spread * dwell_s, bid_price > 0 AND ask_price >= bid_price) / sumIf(dwell_s, bid_price > 0 AND ask_price >= bid_price)) * 100, 2) AS per_update_premium_cents,
    countIf(bid_price > 0 AND ask_price > 0 AND ask_price < bid_price) AS crossed_updates,
    countIf(bid_price <= 0 OR ask_price <= 0) AS zero_bid_or_ask_updates
FROM ordered

SPY quote change 3.98 million times for regular hours alone. Di second wey busy pass — 10:15:43 ET, inside di 10:15 minute wey dem flag up — carry 1461 updates. Di smallest gap between updates wey follow each other na 1375 nanoseconds, di median na 245.6 microseconds, and 75138 pairs wey follow each other carry identical nanosecond stamps — even nanosecond resolution no fit fully arrange di modern tape.

Dem nanoseconds dey earn dia keep for measuring di bid-ask spread. If you count every update equally, SPY average spread na 2.72 cents; if you weight each quote by how long e stand, na 2.34 cents. Di per-update figure dey read 0.38 cents wider mechanically: updates dey cluster exactly for di moments wen di spread wide and dey move, while resting order dey live inside clock time. Anytime you see spread statistic, ask which clock measure am. Di census dey disclosed, no be hidden: 1544 regular-hours updates momentarily cross (ask below bid, na interleaving of quotes from different venues) and 0 carry zero bid or ask; di spread statistics exclude both and count wetin dem exclude.

Di spread get clock

Wen you arrange SPY quoted spread by half-hour, di microstructure turn to curve wey any trader fit use:

SELECT
    formatDateTime(toTimeZone(toStartOfInterval(sip_timestamp, INTERVAL 30 MINUTE), 'America/New_York'), '%H:%i') AS et_time,
    round(quantileExactIf(0.5)(toFloat64(ask_price - bid_price), bid_price > 0 AND ask_price >= bid_price) * 100, 1) AS median_spread_cents,
    count() AS quote_updates
FROM global_markets.cache_stocks_quotes
WHERE ticker = 'SPY' AND sip_timestamp >= '2026-06-29 08:00:00' AND sip_timestamp < '2026-06-30 00:00:00'
GROUP BY et_time
ORDER BY et_time

Di median na 8 cents for di 04:00 ET premarket bucket, 3 cents for di 09:30 opening bucket, and 2 cents by 14:00 — to trade di same share for premarket open go cost you roughly four times di spread of early-afternoon order. Di spread no be constant; e be schedule.

Forensic exhibit: di file wey stop for S

Every daily vendor file dey get completeness probe before we publish any number from am: count di tickers, check di alphabetical span, compare neighbors. Di FINRA off-exchange short-volume file for June 29 na di exhibit wey show why:

SELECT
    date,
    uniqExact(ticker) AS tickers,
    min(ticker) AS first_ticker,
    max(ticker) AS last_ticker,
    countIf(ticker = 'TSLA') AS tsla_rows,
    round(100 * sum(short_volume) / sum(total_volume), 2) AS short_pct_of_reported
FROM global_markets.stocks_short_volume
WHERE date BETWEEN '2026-06-26' AND '2026-06-30'
GROUP BY date
ORDER BY date

Di June 26 file cover 15052 tickers, A go reach ZYME. Di June 29 file cover 5489, A go reach SSUS — e just stop for middle of di S names. Every ticker wey dey alphabetically after dat point — TSLA dey inside, for 0 rows for June 29 against 1 for June 26 — dey missing; by June 30 di file don return to 15362 tickers. Completeness probe no be anything wey pass dis one-row-per-day receipt — na di difference between publishing a statistic and publishing an artifact.

With dat disclosure inline — and only with am — na here di ratio dey: marked-short volume compute to 45.85% of reported off-exchange volume for June 29, alongside 46.89% for June 26 and 48.82% for June 30. Di June 29 figure cover only di A-to-S slice, so treat am as partial reading. Near-half short share na di routine level wey every neighboring session dey show — and daily short volume no be short interest.

Data notes

How We Do Am

• We dey store timestamps for UTC, and we dey filter dem with raw UTC boundaries (regular hours for June 29 na 13:30 to 20:00 UTC); toTimeZone dey show only for SELECT lists.
• We dey check di session type against di holiday calendar and di 390-bar regular window wey we observe — we no dey ever assume am.
• "SIP-counted (matched) volume" na di volume wey di SIP own condition rules dey count towards di consolidated total; we dey derive di exclusion set from di dictionary, wey checksum dey bound, we no dey ever hardcode am.
• Per-update spread statistics dey give each NBBO update equal weight; time-weighted statistics dey weight each quote by how long e stand. Both dey disclose dia exclusions.
• Decimal price/size columns dey sum natively and we dey cast dem to 64-bit floats before any ratio or price-times-size arithmetic.
• Tick tables dey retain full history with no rolling expiry; dis stored, versioned results na di permanent record no matter wetin happen. Warehouse state as of July 3, 2026.

Every panel wey dey above na one stored object — chart, table, and SQL dey together. Open any of dem, copy di query, and carry am further for di Strasmore terminal.