activity_by_slot

Name: activity_by_slot
Creator: Strasmore
Published: 2026-09-26
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-09-26, from us-market-volume-by-hour-beijing-time.

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

activity_by_slot — 13 rows by 4 columns, computed 2026-09-26 from US exchange, SIP and OPRA data.
et_time	beijing_time	trade_count_per_min	range_bps
09:30	21:30	2846	6.8
10:00	22:00	2080	5.9
10:30	22:30	1996	5.6
11:00	23:00	1755	4.7
11:30	23:30	1604	4.5
12:00	00:00	1371	3.9
12:30	00:30	1164	3.7
13:00	01:00	1227	3.8
13:30	01:30	1384	3.6
14:00	02:00	1464	3.7
14:30	02:30	1355	3.4
15:00	03:00	1618	3.5
15:30	03:30	3432	4.4

Rows × columns: 13 × 4
Computed: 2026-09-26
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 activity_by_slot, derived from the stored result.
Column	Type	Range	Notes
`et_time`	text	13 distinct values (09:30, 10:00, 10:30…)
`beijing_time`	text	13 distinct values (00:00, 00:30, 01:00…)
`trade_count_per_min`	number	1,164 to 3,432	count
`range_bps`	number	3.4 to 6.8

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-09-26, and the query below is what returned them.

Run it yourself

This is the exact query behind the result above. Change a ticker, a date or a column and run it against the warehouse — no account, no key. The no-signup tier is smaller than the one this page was computed on; a query that reaches past it comes back saying which plan runs it.

WITH spy_bars AS
(
    SELECT
        toTimeZone(window_start, 'America/New_York') AS et,
        toHour(et) * 60 + toMinute(et)               AS et_min,
        intDiv(et_min, 30) * 30                      AS slot,
        transactions,
        toFloat64(high)                             AS hi,
        toFloat64(low)                              AS lo,
        toFloat64(close)                            AS cl
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPY'
      AND window_start >= toDateTime('2026-04-01 04:00:00', 'UTC')
      AND window_start <  toDateTime('2026-07-01 04:00:00', 'UTC')
      AND et_min >= 570
      AND et_min <  960
      AND cl > 0
)
SELECT
    formatDateTime(toDateTime(slot * 60, 'UTC'), '%H:%i')                  AS et_time,
    formatDateTime(toDateTime(((slot + 720) % 1440) * 60, 'UTC'), '%H:%i') AS beijing_time,
    round(avg(transactions), 0)                                           AS trade_count_per_min,
    round(avg(10000 * (hi - lo) / cl), 1)                                 AS range_bps
FROM spy_bars
GROUP BY slot
ORDER BY slot

SQL to run

WITH spy_bars AS
(
    SELECT
        toTimeZone(window_start, 'America/New_York') AS et,
        toHour(et) * 60 + toMinute(et)               AS et_min,
        intDiv(et_min, 30) * 30                      AS slot,
        transactions,
        toFloat64(high)                             AS hi,
        toFloat64(low)                              AS lo,
        toFloat64(close)                            AS cl
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPY'
      AND window_start >= toDateTime('2026-04-01 04:00:00', 'UTC')
      AND window_start <  toDateTime('2026-07-01 04:00:00', 'UTC')
      AND et_min >= 570
      AND et_min <  960
      AND cl > 0
)
SELECT
    formatDateTime(toDateTime(slot * 60, 'UTC'), '%H:%i')                  AS et_time,
    formatDateTime(toDateTime(((slot + 720) % 1440) * 60, 'UTC'), '%H:%i') AS beijing_time,
    round(avg(transactions), 0)                                           AS trade_count_per_min,
    round(avg(10000 * (hi - lo) / cl), 1)                                 AS range_bps
FROM spy_bars
GROUP BY slot
ORDER BY slot

⌘/Ctrl + Enter

Work with this data in your AI assistant

Opens ready to query, with this page's data. Free, no account.

Claude ChatGPT Gemini Muse Your own agent