move_buckets

Name: move_buckets
Creator: Strasmore
Published: 2026-09-16
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-16, from soxl-and-soxs-volatility-decay.

as of 2026-09-16 table 4×7read in context →

move_buckets — 4 rows by 7 columns, computed 2026-09-16 from US exchange, SIP and OPRA data.
label	sample_count	both_lost_count	avg_index_abs_pct	avg_pair_pct	worst_pair_pct	best_pair_pct
1: 指数±2.5%以内	17	9	1.3	-12.3	-58.7	-2.7
2: 指数±2.5〜5%	4	0	4.2	-9.1	-16.6	-4.4
3: 指数±5〜10%	14	0	7.1	-4	-16.5	3.2
4: 指数±10%超	21	0	16.2	10.5	-29.9	98.4

Rows × columns: 4 × 7
Computed: 2026-09-16
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 move_buckets, derived from the stored result.
Column	Type	Range	Notes
`label`	text	4 distinct values (1: 指数±2.5%以内, 2: 指数±2.5〜5%, 3: 指数±5〜10%…)
`sample_count`	number	4 to 21	count
`both_lost_count`	number	0 to 9	count
`avg_index_abs_pct`	number	1.3 to 16.2	percent
`avg_pair_pct`	number	-12.3 to 10.5	percent
`worst_pair_pct`	number	-58.7 to -16.5	percent
`best_pair_pct`	number	-4.4 to 98.4	percent

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-16, 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
daily AS
(
    SELECT
        ticker,
        date,
        anyLast(toFloat64(close)) AS c
    FROM global_markets.stocks_daily_aggs
    WHERE ticker IN ('SOXX', 'SOXL', 'SOXS')
      AND date >= '2021-12-15'
      AND date <  '2026-09-01'
    GROUP BY ticker, date
),
lagged AS
(
    SELECT
        ticker,
        date,
        c,
        lagInFrame(c, 1) OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS prev_c
    FROM daily
),
rets AS
(
    SELECT
        ticker,
        date,
        c / prev_c - 1 AS r
    FROM lagged
    WHERE prev_c > 0
      AND abs(c / prev_c - 1) < 0.75
      AND date >= '2022-01-01'
      AND (ticker, date) NOT IN
      (
          SELECT ticker, toDate(execution_date)
          FROM global_markets.stocks_splits
          WHERE ticker IN ('SOXX', 'SOXL', 'SOXS')
      )
),
monthly AS
(
    SELECT
        toStartOfMonth(date)                        AS m,
        exp(sumIf(log(1 + r), ticker = 'SOXX')) - 1 AS soxx,
        exp(sumIf(log(1 + r), ticker = 'SOXL')) - 1 AS soxl,
        exp(sumIf(log(1 + r), ticker = 'SOXS')) - 1 AS soxs
    FROM rets
    GROUP BY m
    HAVING countIf(ticker = 'SOXX') >= 15
       AND countIf(ticker = 'SOXL') >= 15
       AND countIf(ticker = 'SOXS') >= 15
)
SELECT
    multiIf(abs(soxx) < 0.025, '1: 指数±2.5%以内',
            abs(soxx) < 0.05,  '2: 指数±2.5〜5%',
            abs(soxx) < 0.10,  '3: 指数±5〜10%',
                               '4: 指数±10%超') AS label,
    count()                                  AS sample_count,
    countIf(soxl < 0 AND soxs < 0)           AS both_lost_count,
    round(avg(abs(soxx)) * 100, 1)           AS avg_index_abs_pct,
    round(avg(soxl + soxs) * 100, 1)         AS avg_pair_pct,
    round(min(soxl + soxs) * 100, 1)         AS worst_pair_pct,
    round(max(soxl + soxs) * 100, 1)         AS best_pair_pct
FROM monthly
GROUP BY label
ORDER BY label

SQL to run

WITH
daily AS
(
    SELECT
        ticker,
        date,
        anyLast(toFloat64(close)) AS c
    FROM global_markets.stocks_daily_aggs
    WHERE ticker IN ('SOXX', 'SOXL', 'SOXS')
      AND date >= '2021-12-15'
      AND date <  '2026-09-01'
    GROUP BY ticker, date
),
lagged AS
(
    SELECT
        ticker,
        date,
        c,
        lagInFrame(c, 1) OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS prev_c
    FROM daily
),
rets AS
(
    SELECT
        ticker,
        date,
        c / prev_c - 1 AS r
    FROM lagged
    WHERE prev_c > 0
      AND abs(c / prev_c - 1) < 0.75
      AND date >= '2022-01-01'
      AND (ticker, date) NOT IN
      (
          SELECT ticker, toDate(execution_date)
          FROM global_markets.stocks_splits
          WHERE ticker IN ('SOXX', 'SOXL', 'SOXS')
      )
),
monthly AS
(
    SELECT
        toStartOfMonth(date)                        AS m,
        exp(sumIf(log(1 + r), ticker = 'SOXX')) - 1 AS soxx,
        exp(sumIf(log(1 + r), ticker = 'SOXL')) - 1 AS soxl,
        exp(sumIf(log(1 + r), ticker = 'SOXS')) - 1 AS soxs
    FROM rets
    GROUP BY m
    HAVING countIf(ticker = 'SOXX') >= 15
       AND countIf(ticker = 'SOXL') >= 15
       AND countIf(ticker = 'SOXS') >= 15
)
SELECT
    multiIf(abs(soxx) < 0.025, '1: 指数±2.5%以内',
            abs(soxx) < 0.05,  '2: 指数±2.5〜5%',
            abs(soxx) < 0.10,  '3: 指数±5〜10%',
                               '4: 指数±10%超') AS label,
    count()                                  AS sample_count,
    countIf(soxl < 0 AND soxs < 0)           AS both_lost_count,
    round(avg(abs(soxx)) * 100, 1)           AS avg_index_abs_pct,
    round(avg(soxl + soxs) * 100, 1)         AS avg_pair_pct,
    round(min(soxl + soxs) * 100, 1)         AS worst_pair_pct,
    round(max(soxl + soxs) * 100, 1)         AS best_pair_pct
FROM monthly
GROUP BY label
ORDER BY label

⌘/Ctrl + Enter

このデータをAIアシスタントで使う

このページのデータで、すぐにクエリできる状態で開きます。無料、アカウント不要。

Claude ChatGPT Gemini Muse 自分のエージェント