forward_median

Name: forward_median
Creator: Strasmore
Published: 2026-10-07
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-10-07, from dragonfly-doji-follow-through.

as of 2026-10-07 table 3×6read in context →

forward_median — 3 rows by 6 columns, computed 2026-10-07 from US exchange, SIP and OPRA data.
pattern	sample_count	sample_size	median_1d_pct	median_5d_pct	median_20d_pct
dragonfly	436	436	-0.061	0.422	1.232
gravestone	306	306	0.213	0.365	1.569
other_days	95839	96 ஆயிரம்	0.092	0.327	1.193

Rows × columns: 3 × 6
Computed: 2026-10-07
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 forward_median, derived from the stored result.
Column	Type	Range	Notes
`pattern`	text	3 distinct values (dragonfly, gravestone, other_days)
`sample_count`	number	306 to 95,839	count
`sample_size`	text	3 distinct values (306, 436, 96 ஆயிரம்)
`median_1d_pct`	number	-0.061 to 0.213	percent
`median_5d_pct`	number	0.327 to 0.422	percent
`median_20d_pct`	number	1.193 to 1.569	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-10-07, 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 bars AS
(
    SELECT
        ticker,
        date,
        toFloat64(open)  AS o,
        toFloat64(high)  AS h,
        toFloat64(low)   AS l,
        toFloat64(close) AS c
    FROM global_markets.stocks_daily_aggs
    WHERE ticker IN ('AAPL','MSFT','NVDA','AMZN','GOOGL','META','JPM','XOM','JNJ','KO','PG','WMT','HD','UNH','CVX','MRK','CSCO','PEP','SPY','QQQ')
      AND date >= '2006-01-01'
      AND date <  '2026-10-01'
      AND high > low
      AND low > 0
),
shaped AS
(
    SELECT
        ticker,
        date,
        c,
        h - l                  AS rng,
        abs(c - o)              AS body,
        h - greatest(o, c)      AS upper_wick,
        least(o, c) - l         AS lower_wick,
        0.01 * o                AS min_range,
        leadInFrame(c, 1)  OVER (PARTITION BY ticker ORDER BY date ASC ROWS BETWEEN CURRENT ROW AND 20 FOLLOWING) AS c_fwd1,
        leadInFrame(c, 5)  OVER (PARTITION BY ticker ORDER BY date ASC ROWS BETWEEN CURRENT ROW AND 20 FOLLOWING) AS c_fwd5,
        leadInFrame(c, 20) OVER (PARTITION BY ticker ORDER BY date ASC ROWS BETWEEN CURRENT ROW AND 20 FOLLOWING) AS c_fwd20
    FROM bars
),
tagged AS
(
    SELECT
        ticker,
        date,
        c,
        c_fwd1,
        c_fwd5,
        c_fwd20,
        multiIf(
            rng >= min_range AND body <= 0.10 * rng AND upper_wick <= 0.10 * rng, 'dragonfly',
            rng >= min_range AND body <= 0.10 * rng AND lower_wick <= 0.10 * rng, 'gravestone',
            'other_days') AS pattern
    FROM shaped
)
SELECT
    pattern,
    count()                   AS sample_count,
    if(count() >= 10000,
       concat(toString(round(count() / 1000)), ' ஆயிரம்'),
       toString(count()))     AS sample_size,
    round(100 * (quantileDeterministic(0.5)(c_fwd1  / c, cityHash64(concat(ticker, toString(date)))) - 1), 3) AS median_1d_pct,
    round(100 * (quantileDeterministic(0.5)(c_fwd5  / c, cityHash64(concat(ticker, toString(date)))) - 1), 3) AS median_5d_pct,
    round(100 * (quantileDeterministic(0.5)(c_fwd20 / c, cityHash64(concat(ticker, toString(date)))) - 1), 3) AS median_20d_pct
FROM tagged
WHERE c_fwd1 > 0 AND c_fwd5 > 0 AND c_fwd20 > 0
GROUP BY pattern
ORDER BY pattern

SQL to run

WITH bars AS
(
    SELECT
        ticker,
        date,
        toFloat64(open)  AS o,
        toFloat64(high)  AS h,
        toFloat64(low)   AS l,
        toFloat64(close) AS c
    FROM global_markets.stocks_daily_aggs
    WHERE ticker IN ('AAPL','MSFT','NVDA','AMZN','GOOGL','META','JPM','XOM','JNJ','KO','PG','WMT','HD','UNH','CVX','MRK','CSCO','PEP','SPY','QQQ')
      AND date >= '2006-01-01'
      AND date <  '2026-10-01'
      AND high > low
      AND low > 0
),
shaped AS
(
    SELECT
        ticker,
        date,
        c,
        h - l                  AS rng,
        abs(c - o)              AS body,
        h - greatest(o, c)      AS upper_wick,
        least(o, c) - l         AS lower_wick,
        0.01 * o                AS min_range,
        leadInFrame(c, 1)  OVER (PARTITION BY ticker ORDER BY date ASC ROWS BETWEEN CURRENT ROW AND 20 FOLLOWING) AS c_fwd1,
        leadInFrame(c, 5)  OVER (PARTITION BY ticker ORDER BY date ASC ROWS BETWEEN CURRENT ROW AND 20 FOLLOWING) AS c_fwd5,
        leadInFrame(c, 20) OVER (PARTITION BY ticker ORDER BY date ASC ROWS BETWEEN CURRENT ROW AND 20 FOLLOWING) AS c_fwd20
    FROM bars
),
tagged AS
(
    SELECT
        ticker,
        date,
        c,
        c_fwd1,
        c_fwd5,
        c_fwd20,
        multiIf(
            rng >= min_range AND body <= 0.10 * rng AND upper_wick <= 0.10 * rng, 'dragonfly',
            rng >= min_range AND body <= 0.10 * rng AND lower_wick <= 0.10 * rng, 'gravestone',
            'other_days') AS pattern
    FROM shaped
)
SELECT
    pattern,
    count()                   AS sample_count,
    if(count() >= 10000,
       concat(toString(round(count() / 1000)), ' ஆயிரம்'),
       toString(count()))     AS sample_size,
    round(100 * (quantileDeterministic(0.5)(c_fwd1  / c, cityHash64(concat(ticker, toString(date)))) - 1), 3) AS median_1d_pct,
    round(100 * (quantileDeterministic(0.5)(c_fwd5  / c, cityHash64(concat(ticker, toString(date)))) - 1), 3) AS median_5d_pct,
    round(100 * (quantileDeterministic(0.5)(c_fwd20 / c, cityHash64(concat(ticker, toString(date)))) - 1), 3) AS median_20d_pct
FROM tagged
WHERE c_fwd1 > 0 AND c_fwd5 > 0 AND c_fwd20 > 0
GROUP BY pattern
ORDER BY pattern

⌘/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