Microstructure Deep-Dive: June 29, 2026, Tick by Tick

This is the under-the-hood companion to the June 29, 2026 market recap: the raw feed beneath the minute bars — 156.1 million individual trades and 505.38 million NBBO quote updates in one ordinary Monday session. Every number is a stored query result (expand any panel for the exact SQL), and the day's two forensic finds get full write-ups: a volume correction the tape's own code dictionary demands, and a vendor file that quietly stops at the letter S.

The trade tape, print by print

The session check comes first, never assumed: the exchange holiday calendar holds 0 rows for June 29, 2026, and SPY printed exactly 390 minute bars inside the regular window — a full session, 9:30 am to 4:00 pm New York time. Across the extended day, the consolidated tape recorded 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 were odd lots — trades of fewer than 100 shares — yet they moved only 8.79% of the raw shares — the uncorrected total the volume-forensics section below trims. The median print was 21 shares, measured with a self-verifying bracket probe — cumulative counts at 19 through 23 shares — that returns 0 if the bracket misses, and is bounded so a miss holds the post. 17.9 million prints were for exactly one share and 4.73% carried fractional sizes — a pattern consistent with retail apps hitting the tape one slice at a time, with market makers taking the other side.

Volume forensics: deriving "matched volume" from the dictionary

Add up the size field on every print and June 29 shows 24.79 billion shares traded. That figure is wrong, and the tape itself says so. Every print carries condition codes, and the SIP — the Securities Information Processor that consolidates every venue into one stream — publishes a dictionary stating, code by code, whether a print updates consolidated volume. Some messages are administrative re-broadcasts of volume already counted once. Rather than hardcoding a list of "known" codes, this post derives 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 carry updates_volume = false on the consolidated feed: 15 Market Center Official Close · 16 Market Center Official Open · 38 Corrected Consolidated Close (per listing market). Here is the worked lesson: an earlier draft of the daily recap hardcoded codes 15 and 16 — the two official open/close messages every data guide mentions — and missed code 38 entirely. The dictionary, not a remembered code list, is the source of truth.

Two blocks is deliberate: a scalar subquery over the dictionary inside a 156.1-million-row scan blows past our 30-second cap. So the block above derives the set as one row — sanity-bounded on the code count and an id checksum (69) — and the block below applies the derived literals. If the dictionary ever drifts, the checksum bound holds this post for review; the literals can never 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 corrected accounting: 24.79 billion shares raw, 20.66 billion SIP-counted, 4.13 billion excluded across 91175 administrative prints. One definition, stated precisely: "matched volume" means the volume the SIP's own condition rules count toward the consolidated total — nothing more. We are not judging which trades were economically meaningful; we are applying the tape's own accounting.

The largest prints, decoded

The day's biggest prints are where condition codes stop being trivia:

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 the top rows as a worked example. The day's largest print — 61.69 million CAG (Conagra) shares at $13.98, roughly $862.5 million, stamped 16:04:53 ET under codes 8, 41 (8 Closing Prints, 41 Trade Thru Exempt) — is the NYSE closing auction result published after 4:00 pm. It counts toward volume exactly once. The same 61.69 million shares then re-appear under code 15, Market Center Official Close: a re-report the dictionary excludes, part of the 4.13 billion stripped above. That code-15 row is stripped from the table above by design, so its receipt gets its 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'

The largest of the 6 official-close messages on CAG carries 61.69 million shares — the auction's 61.69 million exactly (difference: 0) — stamped 16:04:53 ET from exchange id 10, NYSE in the stocks directory: the listing market re-stating its own auction.

The other CAG rows are a different animal — the 15.17 million-share print under codes 12, 2 (12 Form T / extended hours, 2 Average Price Trade) and two more under code 12 are post-auction prints reported through FINRA whose conditions carry updates_volume = true. The SIP counts them as real prints, and so does our matched figure.

Elsewhere, code 9 marks the Nasdaq closing crosses — NVDA's $3389.5 million cross is the table's biggest print in dollars — and the codes on the SNAP print (53, 41) include 53, a Qualified Contingent Trade.

The clock: ten minutes own the day

At minute resolution, the trade tape concentrates almost entirely at the edges of the 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

The 15:59 ET minute printed 3.98 million trades — over sixty thousand a second. Whether the rest of the top ten hugs the open and the close is itself a 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 the ten sit inside the opening ten minutes or the final ten before the close. The one exception — 10:15 ET, a burst of 0.88 million prints — is a genuine mid-morning anomaly; its cause is unknown from this data, and we won't invent one. The same clock time returns below.

The quote stream: half a billion updates

The National Best Bid and Offer — the best buy and sell price across every exchange, re-published whenever either changes — updated 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 dominate the quoting: QQQ alone logged 5.23 million updates against 1.8 million for NVDA, the most-quoted single stock we checked — 0.67 million updates clear of the next single name. A full per-ticker census of every listing does not fit under the 30-second cap, so the leaderboard below is a checked set, not an 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 led the checked set at 5.23 million updates; the top of the board is index funds and their leveraged cousins, not household stocks.

SPY under the microscope: nanosecond receipts, and the 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's quote changed 3.98 million times in regular hours alone. Its busiest second — 10:15:43 ET, inside the 10:15 minute flagged above — carried 1461 updates. The smallest gap between consecutive updates was 1375 nanoseconds, the median 245.6 microseconds, and 75138 consecutive pairs carried identical nanosecond stamps — even nanosecond resolution cannot fully order the modern tape.

Those nanoseconds earn their keep measuring the bid-ask spread. Counting every update equally, SPY's average spread was 2.72 cents; weighting each quote by how long it stood, 2.34 cents. The per-update figure reads 0.38 cents wider mechanically: updates cluster in exactly the moments when the spread is wide and moving, while a resting order lives in clock time. Whenever you see a spread statistic, ask which clock measured it. The census is disclosed, not hidden: 1544 regular-hours updates were momentarily crossed (ask below bid, an interleaving of quotes from different venues) and 0 carried a zero bid or ask; the spread statistics exclude both and count what they exclude.

The spread has a clock

Bucketing SPY's quoted spread by half hour turns the microstructure into a curve any trader can 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

A median of 8 cents in the 04:00 ET premarket bucket, 3 cents in the 09:30 opening bucket, and 2 cents by 14:00 — trading the same share at the premarket open costs roughly four times the spread of an early-afternoon order. The spread is not a constant; it is a schedule.

Forensics exhibit: the file that stopped at S

Every daily vendor file gets a completeness probe before we publish a number from it: count the tickers, check the alphabetical span, compare neighbors. June 29's FINRA off-exchange short-volume file is the exhibit for 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

The June 26 file covers 15052 tickers, A through ZYME. The June 29 file covers 5489, A through SSUS — it simply stops partway through the S names. Every ticker alphabetically after that point — TSLA included, at 0 rows for June 29 against 1 on June 26 — is missing; by June 30 the file is back to 15362 tickers. A completeness probe is nothing fancier than this one-row-per-day receipt — the difference between publishing a statistic and publishing an artifact.

With that disclosure inline — and only with it — here is the ratio: marked-short volume computes to 45.85% of reported off-exchange volume on June 29, alongside 46.89% on June 26 and 48.82% on June 30. The June 29 figure covers only the A-to-S slice, so treat it as a partial reading. A near-half short share is the routine level every neighboring session shows — and daily short volume is not short interest.

Data notes

Methodology

• Timestamps are stored in UTC and filtered with raw UTC bounds (regular hours on June 29 are 13:30 to 20:00 UTC); toTimeZone appears only in SELECT lists.
• The session type is verified against the holiday calendar and the observed 390-bar regular window — never assumed.
• "SIP-counted (matched) volume" is the volume the SIP's own condition rules count toward the consolidated total; the exclusion set is derived from the dictionary, checksum-bounded, never hardcoded.
• Per-update spread statistics weight each NBBO update equally; time-weighted statistics weight each quote by how long it stood. Both disclose their exclusions.
• Decimal price/size columns are summed natively and cast to 64-bit floats before any ratio or price-times-size arithmetic.
• Tick tables carry a rolling one-to-two-month retention; these stored, versioned results are the durable record. Warehouse state as of July 3, 2026.

Every panel above is one stored object — chart, table, and SQL together. Open any of them, copy the query, and take it further on the Strasmore terminal.