confirm_by_year

Name: confirm_by_year
Creator: Strasmore
Published: 2026-09-28
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-28, from dow-theory-and-transport-confirmation.

as of 2026-09-28 ranking 23×4read in context →

confirm_by_year — 23 rows by 4 columns, computed 2026-09-28 from US exchange, SIP and OPRA data.
year	signal_count	confirmed_count	confirmed_pct
2004	22	19	86.4
2005	15	15	100
2006	53	38	71.7
2007	38	32	84.2
2008	6	6	100
2009	43	40	93
2010	44	41	93.2
2011	34	31	91.2
2012	39	10	25.6
2013	64	59	92.2
2014	39	39	100
2015	12	4	33.3
2016	33	21	63.6
2017	70	53	75.7
2018	30	19	63.3
2019	37	17	45.9
2020	30	27	90
2021	47	35	74.5
2022	11	7	63.6
2023	29	27	93.1
2024	50	40	80
2025	29	22	75.9
2026	26	23	88.5

Rows × columns: 23 × 4
Computed: 2026-09-28
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 confirm_by_year, derived from the stored result.
Column	Type	Range	Notes
`year`	number	2,004 to 2,026
`signal_count`	number	6 to 70	count
`confirmed_count`	number	4 to 59	count
`confirmed_pct`	number	25.6 to 100	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-28, 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
px AS
(
    SELECT
        ticker,
        date,
        toFloat64(max(close)) AS close_px
    FROM global_markets.stocks_daily_aggs
    WHERE ticker IN ('DIA', 'IYT')
    GROUP BY ticker, date
),
joint AS
(
    SELECT
        date,
        anyIf(close_px, ticker = 'DIA') AS dia,
        anyIf(close_px, ticker = 'IYT') AS iyt
    FROM px
    GROUP BY date
    HAVING countIf(ticker = 'DIA') = 1 AND countIf(ticker = 'IYT') = 1
),
rolled AS
(
    SELECT
        date,
        dia,
        iyt,
        row_number() OVER (ORDER BY date) AS i,
        count() OVER () AS n,
        max(dia) OVER (ORDER BY date ROWS BETWEEN 62 PRECEDING AND 1 PRECEDING) AS dia_prior_high,
        max(iyt) OVER (ORDER BY date ROWS BETWEEN 62 PRECEDING AND 1 PRECEDING) AS iyt_prior_high
    FROM joint
),
marked AS
(
    SELECT
        date,
        i,
        n,
        if(i > 62 AND dia > dia_prior_high, 1, 0) AS dia_signal,
        if(i > 62 AND iyt > iyt_prior_high, 1, 0) AS iyt_signal
    FROM rolled
),
scanned AS
(
    SELECT
        date,
        i,
        n,
        dia_signal,
        indexOf(groupArray(iyt_signal) OVER (ORDER BY date ROWS BETWEEN CURRENT ROW AND 20 FOLLOWING), 1) - 1 AS confirm_lag
    FROM marked
)
SELECT
    toYear(date)                                        AS year,
    count()                                             AS signal_count,
    countIf(confirm_lag >= 0)                           AS confirmed_count,
    round(100 * countIf(confirm_lag >= 0) / count(), 1) AS confirmed_pct
FROM scanned
WHERE dia_signal = 1 AND i + 20 <= n
GROUP BY year
ORDER BY year

SQL to run

WITH
px AS
(
    SELECT
        ticker,
        date,
        toFloat64(max(close)) AS close_px
    FROM global_markets.stocks_daily_aggs
    WHERE ticker IN ('DIA', 'IYT')
    GROUP BY ticker, date
),
joint AS
(
    SELECT
        date,
        anyIf(close_px, ticker = 'DIA') AS dia,
        anyIf(close_px, ticker = 'IYT') AS iyt
    FROM px
    GROUP BY date
    HAVING countIf(ticker = 'DIA') = 1 AND countIf(ticker = 'IYT') = 1
),
rolled AS
(
    SELECT
        date,
        dia,
        iyt,
        row_number() OVER (ORDER BY date) AS i,
        count() OVER () AS n,
        max(dia) OVER (ORDER BY date ROWS BETWEEN 62 PRECEDING AND 1 PRECEDING) AS dia_prior_high,
        max(iyt) OVER (ORDER BY date ROWS BETWEEN 62 PRECEDING AND 1 PRECEDING) AS iyt_prior_high
    FROM joint
),
marked AS
(
    SELECT
        date,
        i,
        n,
        if(i > 62 AND dia > dia_prior_high, 1, 0) AS dia_signal,
        if(i > 62 AND iyt > iyt_prior_high, 1, 0) AS iyt_signal
    FROM rolled
),
scanned AS
(
    SELECT
        date,
        i,
        n,
        dia_signal,
        indexOf(groupArray(iyt_signal) OVER (ORDER BY date ROWS BETWEEN CURRENT ROW AND 20 FOLLOWING), 1) - 1 AS confirm_lag
    FROM marked
)
SELECT
    toYear(date)                                        AS year,
    count()                                             AS signal_count,
    countIf(confirm_lag >= 0)                           AS confirmed_count,
    round(100 * countIf(confirm_lag >= 0) / count(), 1) AS confirmed_pct
FROM scanned
WHERE dia_signal = 1 AND i + 20 <= n
GROUP BY year
ORDER BY year

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