Validating Market Regimes Across Six Tickers

This is the second post in a multi-part series on tracking six tickers across multiple decades with different buy-sell models. The first post in the series illustrates how to collect historical prices with the GOOGLEFINANCE function in Google Sheets for six tickers and transfer the historical prices to a SQL Server database in order to achieve downstream modeling and analysis objectives. The six tickers for the post series are CAT, JPM, LLY, MSFT, NVDA, and SHW. The first post in the series includes a brief section describing the company for each ticker.

This second post segments that same price history into a sequence of eras and assigns each one a regime: expansion, contraction, or shock. Driven by favorable market, economic, or societal conditions, an expansion era consistently generates higher prices at its close than at its beginning. Contraction or shock eras should end with lower prices than when they start because of events that happen during either one of these era types. What separates contraction eras from shock eras is duration — a shock era denotes a short, sharp price drop, and a contraction era represents a price drop over a longer duration.

Validation refers to the process of reconciling historical price changes for eras to the labels assigned to eras. The validation process can run across all six tickers or individual tickers. This post demonstrates the process for validating the process across all six tickers. However, the appendix titled "A.4: Section 2 — Validation By Individual Ticker" towards the end of this post contains a sql script for performing for an individual ticker.

Later in this post series, the validated regime labels for eras will provide a framework for assessing buy-sell model performance relative to a buy-and-hold strategy.

The Eras for this Post

A total of 21 eras are defined for the dataset in this post. The following table lists each era in chronological order (sequence 1 through 21), along with descriptive titles and notes characterizing the primary market drivers. For example, the first era is associated with the 1973 OPEC oil embargo, the fourteenth era was driven by the COVID-19 pandemic, and the twentieth era reflects market dislocations during the intensive 2026 U.S./Israel bombing of Iran.

Each era is assigned a market regime label. Generally, an expansion era is followed by a contraction or shock era, though consecutive expansions can occur (e.g., Eras 6 and 7). The transition between eras is dictated by the specific market, macroeconomic, or societal events that define their start and end dates.

Era 21 remains open because price data collection ends on 2026-07-31 without a terminating structural event. Because an open era lacks a settled conclusion, including it would distort duration-normalized metrics. Therefore, while 21 eras are defined in the ledger, only the 20 closed eras (Eras 1–20) are evaluated in the validation analysis.

Era, Title, Regime, and Descriptive Note
Era	Title	Regime	Descriptive Note
1	Oil-Shock Bear Market	Contraction	1973 OPEC oil embargo and stagflation drove a prolonged bear market.
2	Stagflation Recovery	Expansion	Extended recovery through a decade marked by persistent inflation.
3	Volcker Recession	Contraction	Fed rate hikes to break inflation triggered a sharp recession.
4	1980s Expansion	Expansion	Sustained bull market following Volcker-era disinflation.
5	Black Monday & Aftermath	Shock	Oct 19, 1987 single-day crash (−22.6%) and its immediate aftermath.
6	Late-80s–Pre-Dot-Com Run	Expansion	Extended recovery and bull run following the 1987 crash.
7	Dot-Com Build-Up	Expansion	Tech-bubble-inflating bull run.
8	Dot-Com Bust	Contraction	Tech bubble collapse and prolonged bear market.
9	Credit-Fueled Bull Run	Expansion	Housing- and credit-driven expansion preceding the GFC.
10	Global Financial Crisis	Contraction	Subprime mortgage collapse and global financial crisis.
11	Post-GFC Bull Market	Expansion	Extended post-crisis recovery and bull market.
12	2018 Trade-War Correction	Shock	Fed tightening fears plus U.S.–China trade-war rhetoric drove a sharp Q4 selloff.
13	Pre-COVID Recovery	Expansion	Recovery from the Dec 2018 correction through the pre-pandemic market peak.
14	COVID Crash	Shock	Pandemic-driven crash — the fastest bear market on record at the time.
15	COVID Recovery / Bull Run	Expansion	Stimulus-fueled recovery and bull run.
16	2022 Rate-Hike Bear Market	Contraction	Fed rate-hiking cycle to combat inflation.
17	AI-Driven Bull Run	Expansion	AI-capex-driven bull market.
18	2025 Tariff Shock	Shock	"Liberation Day" tariff announcement (Apr 2) through Trump's 90-day pause and the resulting +9.5% rally (Apr 9) — the S&P's biggest one-day gain since 2008.
19	2025–26 Recovery Rally	Expansion	Tariff-shock recovery to new highs. Absorbs the June 2025 Twelve-Day War (Israel/US strikes on Iran) — a real one-day shock that fully round-tripped within two weeks, leaving no lasting break.
20	2026 Intensive Iran Bombing	Shock	U.S./Israeli strikes on Iran (Operation Epic Fury / Roaring Lion, began Feb 28, 2026) through the day before the initial ceasefire. S&P still down 3.3% YTD on this era's last day.
21	2026 Post-Bombing Recovery	Expansion	Absorbs the naval blockade, failed Islamabad talks, and the June 17 MOU signing — none left a lasting price break. Unlike other eras in the dataset for this post, this era is not closed. Its end date merely marks the end of data collection (2026-07-31) not the end of an era.

The next table presents two additional bits of information about each era besides that presented in the preceding table. For example, the Date range column values denote the start and end dates for each era in YYYY-MM-DD format. The Duration column values denote the duration of an era in years, months, or days. Notice that era 21 Duration column value is 3.8 mo+. This is another reminder that era 21 is not yet closed.

Era, Title, Regime, Date Range, and Duration
Era	Title	Regime	Date range	Duration
1	Oil-Shock Bear Market	Contraction	1972-06-01 → 1974-12-06	2.5 yr
2	Stagflation Recovery	Expansion	1974-12-07 → 1981-06-30	6.6 yr
3	Volcker Recession	Contraction	1981-07-01 → 1982-08-11	1.1 yr
4	1980s Expansion	Expansion	1982-08-12 → 1987-10-18	5.2 yr
5	Black Monday & Aftermath	Shock	1987-10-19 → 1987-12-31	2.4 mo
6	Late-80s–Pre-Dot-Com Run	Expansion	1988-01-01 → 1995-12-31	8.0 yr
7	Dot-Com Build-Up	Expansion	1996-01-01 → 2000-03-10	4.2 yr
8	Dot-Com Bust	Contraction	2000-03-11 → 2002-10-09	2.6 yr
9	Credit-Fueled Bull Run	Expansion	2002-10-10 → 2007-10-09	5.0 yr
10	Global Financial Crisis	Contraction	2007-10-10 → 2009-03-09	1.4 yr
11	Post-GFC Bull Market	Expansion	2009-03-10 → 2018-09-20	9.5 yr
12	2018 Trade-War Correction	Shock	2018-09-21 → 2018-12-24	3.1 mo
13	Pre-COVID Recovery	Expansion	2018-12-25 → 2020-02-19	1.2 yr
14	COVID Crash	Shock	2020-02-20 → 2020-03-23	33 days
15	COVID Recovery / Bull Run	Expansion	2020-03-24 → 2022-01-03	1.8 yr
16	2022 Rate-Hike Bear Market	Contraction	2022-01-04 → 2022-10-12	9.3 mo
17	AI-Driven Bull Run	Expansion	2022-10-13 → 2025-04-01	2.5 yr
18	2025 Tariff Shock	Shock	2025-04-02 → 2025-04-09	8 days
19	2025–26 Recovery Rally	Expansion	2025-04-10 → 2026-02-27	10.6 mo
20	2026 Intensive Iran Bombing	Shock	2026-03-02 → 2026-04-07	37 days
21	2026 Post-Bombing Recovery	Expansion	2026-04-08 → 2026-07-31	3.8 mo+

Computing the Era/Regime Metrics

The eras and regimes just presented are only labels until they're checked against what actually happened to each ticker's price. The dbo.regime_ticker_metrics table makes that assessment possible. This table calculates three metrics (overall_pct_return, annualized_pct_return, and max_drawdown_pct) across the 111 unique era-ticker combinations available in the 20 closed eras.

Overall_pct_return is the percentage price change from the era’s starting close price to its ending close price..
Annualized_pct_return is the Compound Annual Growth Rate (CAGR) normalized using a standard 252-trading-day year.
Max_drawdown_pct is the maximum peak-to-trough percentage decline recorded from any daily running peak within that era.

The values in the dbo.regime_ticker_metrics table depend on a joined data source of the dbo.stockhistory_clean_gs table with both the dbo.all_tickers_shared_dates and the dbo.market_regime_ledger tables. The following script has four main sections.

Section 1 contains the table joins for the joined data source and computes the running peak and the first/last-day markers for the current table row.
Section 2 computes each day's drawdown from the running peak for the current table row.
Section 3 aggregates the daily rows into one row per era and ticker, capturing the trading-day count, the era's start and end dates, the start and end close prices, and the worst drawdown recorded during the era.
Section 4 calculates the final percentage metrics from the aggregated boundary prices, excludes invalid or missing start prices, filters out the open Era 21, and materializes the final output into dbo.regime_ticker_metrics.

use SecurityTradingAnalytics

-- Conditionally drop the prior materialized result set before rebuilding it.

-- This needs its own batch (GO) -- a WITH clause must be the first statement

-- in its batch, so this can't just be a semicolon-terminated statement ahead

-- of the CTE chain below.

if object_id('dbo.regime_ticker_metrics', 'U') is not null

drop table dbo.regime_ticker_metrics;

-- Regime x ticker summary metrics

-- Revision (2026-08-31): replaced max_single_day_loss_pct with max_drawdown_pct

-- (worst peak-to-trough decline within the era, not just the single worst day) --

-- drawdown is the standard tail-risk metric, and it's directly comparable to

-- POGL's own pullback-from-peak exit logic in a way a single-day return isn't.

-- 1. join clean price history on shared dates directly to the market regime ledger

with era_prices as (

select

m.era_id,

m.era_name,

m.regime,

gs.ticker,

gs.[date],

gs.[close],

max(gs.[close]) over (

partition by m.era_id, gs.ticker

order by gs.[date]

rows between unbounded preceding and current row

) as running_peak_close,

row_number() over (

partition by m.era_id, gs.ticker

order by gs.[date] asc

) as seq_asc,

row_number() over (

partition by m.era_id, gs.ticker

order by gs.[date] desc

) as seq_desc

-- dbo.stockhistory_clean_gs is the historical price

-- table for the tickers in this post; see the post at

-- https://securitytradinganalytics.blogspot.com/2026/08/a-multi-decade-framework-for-comparing.html

-- for the script to create and populate the table

from dbo.stockhistory_clean_gs gs

-- see a script for creating and populating

-- dbo.all_tickers_shared_dates in a post appendix

inner join dbo.all_tickers_shared_dates d

on gs.[date] = d.[date]

inner join dbo.market_regime_ledger m

on gs.[date] >= m.start_date

and gs.[date] <= m.end_date

where gs.ticker in ('CAT', 'JPM', 'LLY', 'SHW', 'MSFT', 'NVDA')

-- 2. each day's drawdown from the running peak within its own era

daily_drawdowns as (

select

era_id,

era_name,

regime,

ticker,

[date],

[close],

seq_asc,

seq_desc,

case

when running_peak_close > 0

then ([close] - running_peak_close) / running_peak_close

else null

end as drawdown_pct

from era_prices

-- 3. aggregate era boundary prices, total days, and worst drawdown

era_boundaries as (

select

era_id,

era_name,

regime,

ticker,

count(*) as trading_days,

min([date]) as era_start_date,

max([date]) as era_end_date,

max(case when seq_asc = 1 then [close] end) as start_close,

max(case when seq_desc = 1 then [close] end) as end_close,

min(drawdown_pct) as max_drawdown

from daily_drawdowns

group by

era_id,

era_name,

regime,

ticker

)

-- 4. final calculations, materialized into dbo.regime_ticker_metrics so the

-- section 1 and section 2 appendix scripts have a stable table to query

-- against instead of re-running this whole CTE chain every time.

select

era_id,

era_name,

regime,

ticker,

trading_days,

era_start_date,

era_end_date,

start_close,

end_close,

-- overall percentage return across the era

cast(((end_close - start_close) / start_close) * 100.0 as numeric(10, 2)) as [overall_pct_return],

-- annualized percentage return (cagr) using 252 trading days/year

cast((power(cast(end_close / start_close as float), (252.0 / nullif(trading_days, 0))) - 1.0) * 100.0 as numeric(10, 2)) as [annualized_pct_return],

-- worst peak-to-trough decline within the era (replaces max_single_day_loss_pct)

cast(max_drawdown * 100.0 as numeric(10, 2)) as [max_drawdown_pct]

into dbo.regime_ticker_metrics

from era_boundaries

where start_close is not null

and start_close > 0

and era_id <> 21

order by

ticker,

era_id;

-- optionally echo return set

select *

from dbo.regime_ticker_metrics

order by

ticker,

era_id;

The following screenshot displays separate rows for the twenty unique era-regime-ticker combinations for the CAT ticker in the dbo.regime_ticker_metrics table.

The first four columns show the era_id, era_name, regime, and ticker values for each unique combination.
The fifth column reveals the count of trading days for the era-regime-ticker combination of the current row.
The sixth and seventh columns show the first and last dates within the current row's era for which CAT has recorded price data — not necessarily the era's exact calendar boundary dates from the earlier table, since the two can differ by a few days when a boundary falls on a weekend, holiday, or other non-trading gap.
The eighth and ninth columns show the close prices on those two trading dates.
The last three columns reveal the metric values for assessing how well each row confoms to its regime expectation.

Overall_pct_return is the percentage change for ending close value relative to the beginning close value for each era-regime pair.
Annualized_pct_return calculates the constant annual rate of growth between the beginning and ending date within each era-regime pair.
Max_drawdown_pct is the worst-case peak-to-trough decline for daily close values in an era-regime pair.

There are also twenty-row sets in the dbo.regime_ticker_metrics table for the JPM, LLY, and SHW tickers. The dbo.regime_ticker_metrics table also contains 17 and 14 row sets, respectively, for the MSFT and NVDA tickers. The following screenshot displays the row set for the NVDA ticker.

Notice there are just 14 rows in the NVDA row set.
There are two reasons for this:

NVDA data does not start until era 7, and
era 21 is not closed by a terminating event (just the cessation of historical price collection)

Highlights from Era/Regime Metrics Analysis

As the title of this post implies, the main objective of the current post is to validate the regime assignments for eras across the six-ticker set tracked in this post. This section presents two tabular result sets that highlight key findings across the six tickers.

Recall that the process for downloading historical open and close prices for each of the six tickers is described in an earlier post titled “A Multi-Decade Framework for Comparing Ticker Prices from Two Spreadsheet Data Providers”. That earlier post accomplished two main objectives:

First, it downloaded historical prices for the six tickers in this post with the GOOGLEFINANCE function in Google Sheets and the STOCKHISTORY function in Microsoft Excel. It turned out that the returns from GOOGLEFINANCE function were more appropriate for the requirements of this post. Therefore, this post uses historical close prices from just the GOOGLEFINANCE function.
Second, the earlier post inserted the downloaded historical prices from GOOGLEFINANCE into a SQL Server table named dbo.stockhistory_clean_gs. This table is referenced by several code samples in this post, including Appendix A.3: Section 1 — Validation Across All Tickers. The code in the appendix generates results for this section.

The preceding section in this post performs two primary functions. First, it calculates overall_pct_return, annualized_pct_return, and max_drawdown_pct metric values for each of the 111 era-ticker combinations available for analysis. Furthermore, each of the 111 era-ticker combinations is categorized under one of three regimes: Expansion, Contraction, or Shock. The Appendix A.3: Section 1 — Validation Across All Tickers includes code for calculating the median value for each metric for each regime. The following table shows the outcome of these calculations.

Era-ticker pairs with an expansion regime had a median overall_pct_return (121.955), which was large and substantially more positive versus era-ticker pairs having contraction or shock regimes, respectively (-39.12 and -8.53). These results clearly validate that overall_pct_return performance grows during expansion regimes and contracts during contraction and shock regimes.
The median max_drawdown_pct is negative for era-ticker pairs across all three regimes.

Era-ticker pairs with a contraction regime have the most negative median max_drawdown_pct value.
At first glance, it may seem counterintuitive that Expansion regimes exhibit a deeper median maximum drawdown (-27.48%) than Shock regimes (-18.58%). However, this is primarily driven by duration and opportunity: Expansion eras span years (allowing substantial intra-era pullbacks during a broader bull run), whereas Shock eras last only days or weeks, limiting the time window for peak-to-trough losses to accumulate. Conversely, Contraction regimes—which combine multi-year duration with sustained bear markets—exhibit the severe drawdown (-47.43%).

The medians for daily_pct_rate and annualized_pct_return values exhibit corresponding patterns across regimes.

Era-ticker pairs with a shock regime have the most negative value for both medians.
Era-ticker pairs with an expansion regime both have positive median values.
Finally, era-ticker pairs with a contraction regime have medians that are between the median values for the other two regimes.
These patterns also clearly validate the assignment of regimes to era-ticker combinations.

A second check on the regime assignments looks at whether each era-ticker pair's return sign matches what its regime implies: a positive overall_pct_return for an expansion pair, a negative one for a contraction or shock pair. Anything else counts as a mismatch. The following result set reports, for each regime, the number of era-ticker pairs, the mismatch count, and the mismatch percentage.

Mismatch rates rise from expansion (5.4%) to contraction (15.4%) to shock (20.7%), point in the same direction as the median metrics above. This check is a cruder instrument than that analysis, though — it only looks at whether a return crossed zero, not by how much, so a return that barely missed its expected sign counts the same as one that missed it badly. Treat it as a secondary data point that's broadly consistent with the median results, not as independent confirmation of them.

Next Steps

This post is the second one in a multi-part series on collecting, analyzing, and modeling muti-decade historical data series. While this post focuses on validating names and dates for historical eras, the first post in the series presents a framework for collecting multi-decade historical price series.

This post validated the eras for historical data going back to the early 1970’s through slightly past the middle of 2026. Each era was assigned a regime label of expansion, contraction, or shock. A historical price analysis supported the validity of the era dates and regimes in the sense that market performance for tickers tracked in this post generally matched the performance of the six-ticker set tracked in this post.

Subsequent posts in the series will highlight different modeling approaches for the historical price series and eras discussed in this post. The models are for automating the designation of when to enter and exit a trade. The next post will illustrate the use of the Proper Order and Gain Lock In (POGL) model for the tickers and eras discussed in this post. For those who would like to learn more about the POGL, you can ramp up to speed by scanning any of these three prior posts.

Up to two additional models will be described and fitted to the historical data collected in the first post within the series. Finally, the last post in the series will summarize accomplishments of all prior series posts and include a deep audio dive into the series’ content for those who prefer to learn my listening.

Appendix A.1: Market Regime Ledger — Table and Population Script

The dbo.market_regime_ledger table backs every era/regime figure shown earlier in this post; this script creates it and loads the 21 eras.

use SecurityTradingAnalytics

-- Conditionally drop any prior version before rebuilding it.

if object_id('dbo.market_regime_ledger', 'U') is not null

drop table dbo.market_regime_ledger;

create table dbo.market_regime_ledger (

era_id int not null primary key,

era_name varchar(60) not null,

regime varchar(20) not null check (regime in ('Expansion', 'Contraction', 'Shock')),

start_date date not null,

end_date date not null,

check (end_date >= start_date)

);

-- Populates dbo.market_regime_ledger with the 21 eras from the Market Regime

-- Ledger. Rows 1-17 as corrected 2026-08-22 (Era 3/4 boundary moved to the

-- Dow's actual closing low, Aug 12 1982, replacing the NBER recession end

-- date; Era 11 and Era 15 boundaries source-confirmed). Rows 18-21 replace

-- the old, too-coarse single Era 18 ("2025-26 Tariff Era (open)") as of the

-- 2026-08-25 revision, splitting it into the Apr 2026 tariff shock, the

-- recovery rally, the 2026 Iran bombing shock, and the still-open

-- post-bombing recovery.

-- KNOWN OPEN ITEM: Era 19 end (2026-02-27) to Era 20 start (2026-03-02) is a

-- 3-calendar-day gap, not the 1-day contiguity every other boundary in this

-- table follows. A later source check (Wikipedia, "Timeline of the 2026 Iran

-- war") puts the war's actual start at 2026-02-28, one day earlier than the

-- 2026-03-02 boundary this table uses -- so the gap is a real, not-yet-

-- resolved discrepancy, not a typo. Also still open: whether the "2026

-- Post-Bombing Recovery" era (21) needs splitting given the ceasefire was

-- reported as repeatedly violated (renewed strikes ~2026-05-07 and

-- ~2026-07-29) within what this table treats as one uninterrupted Expansion.

-- Otherwise, no gaps or overlaps: era_id N's end_date is always the day

-- before era_id N+1's start_date.

insert into dbo.market_regime_ledger (era_id, era_name, regime, start_date, end_date)

values

(1, 'Oil-Shock Bear Market', 'Contraction', '1972-06-01', '1974-12-06'),

(2, 'Stagflation Recovery', 'Expansion', '1974-12-07', '1981-06-30'),

(3, 'Volcker Recession', 'Contraction', '1981-07-01', '1982-08-11'),

(4, '1980s Expansion', 'Expansion', '1982-08-12', '1987-10-18'),

(5, 'Black Monday & Aftermath', 'Shock', '1987-10-19', '1987-12-31'),

(6, 'Late-80s-Pre-Dot-Com Run', 'Expansion', '1988-01-01', '1995-12-31'),

(7, 'Dot-Com Build-Up', 'Expansion', '1996-01-01', '2000-03-10'),

(8, 'Dot-Com Bust', 'Contraction', '2000-03-11', '2002-10-09'),

(9, 'Credit-Fueled Bull Run', 'Expansion', '2002-10-10', '2007-10-09'),

(10, 'Global Financial Crisis', 'Contraction', '2007-10-10', '2009-03-09'),

(11, 'Post-GFC Bull Market', 'Expansion', '2009-03-10', '2018-09-20'),

(12, '2018 Trade-War Correction', 'Shock', '2018-09-21', '2018-12-24'),

(13, 'Pre-COVID Recovery', 'Expansion', '2018-12-25', '2020-02-19'),

(14, 'COVID Crash', 'Shock', '2020-02-20', '2020-03-23'),

(15, 'COVID Recovery / Bull Run', 'Expansion', '2020-03-24', '2022-01-03'),

(16, '2022 Rate-Hike Bear Market', 'Contraction', '2022-01-04', '2022-10-12'),

(17, 'AI-Driven Bull Run', 'Expansion', '2022-10-13', '2025-04-01'),

(18, '2025 Tariff Shock', 'Shock', '2025-04-02', '2025-04-09'),

(19, '2025-26 Recovery Rally', 'Expansion', '2025-04-10', '2026-02-27'),

(20, '2026 Intensive Iran Bombing', 'Shock', '2026-03-02', '2026-04-07'),

(21, '2026 Post-Bombing Recovery (open)','Expansion', '2026-04-08', '2026-07-31');

-- Sanity checks -- both should come back clean before trusting this table

-- for anything downstream.

-- 1. Row count should be 21.

select count(*) as row_count from dbo.market_regime_ledger;

-- 2. No gaps or overlaps: for every era_id N > 1, its start_date should be

-- exactly one day after era_id N-1's end_date. This returns 0 rows if

-- the ledger is fully contiguous -- expect exactly ONE row back right

-- now (era_id 20, gap_days = 3), the known open item noted above.

select

curr.era_id,

curr.era_name,

prev.end_date as prev_end_date,

curr.start_date as curr_start_date,

datediff(day, prev.end_date, curr.start_date) as gap_days

from dbo.market_regime_ledger curr

join dbo.market_regime_ledger prev

on curr.era_id = prev.era_id + 1

where datediff(day, prev.end_date, curr.start_date) <> 1;

Appendix A.2: Shared Trading Dates Across Tickers

dbo.all_tickers_shared_dates isolates the trading dates common to whichever tickers were active as of that date, which the metrics script joins on to keep every era's day count fair across tickers with different start dates.

use SecurityTradingAnalytics

-- create table to hold shared trading dates across active tickers

if object_id('dbo.all_tickers_shared_dates', 'u') is not null

drop table dbo.all_tickers_shared_dates;

create table dbo.all_tickers_shared_dates (

[date] date not null primary key

);

-- populate table dynamically based on active ticker counts per era

with ticker_bounds as (

-- step 1: identify the first trading date for each ticker string

select

ticker,

min([date]) as first_date

from dbo.stockhistory_clean_gs

group by ticker

expected_counts as (

-- step 2: determine how many tickers were active on each calendar date

select

d.[date],

count(b.ticker) as expected_ticker_count

from (select distinct [date] from dbo.stockhistory_clean_gs) d

inner join ticker_bounds b

on d.[date] >= b.first_date

group by d.[date]

)

-- step 3: insert dates where actual distinct ticker count matches expected count

insert into dbo.all_tickers_shared_dates ([date])

select g.[date]

from dbo.stockhistory_clean_gs g

inner join expected_counts e

on g.[date] = e.[date]

group by g.[date], e.expected_ticker_count

having count(distinct g.ticker) = e.expected_ticker_count;

Appendix A.3: Section 1 — Validation Across All Tickers

This section's script pools all six tickers to check the regime labels two ways: whether each era's price direction matches what its regime implies, and how each regime's typical return and drawdown compare.

use SecurityTradingAnalytics

-- ============================================================================

-- "Validating Market Regimes Across Six Tickers" -- Section 1 (all-ticker

-- validation) appendix script

-- ============================================================================

-- ASSUMPTION: this queries dbo.regime_ticker_metrics, one row per

-- (era_id, ticker), with columns: era_id, era_name, regime, ticker,

-- trading_days, era_start_date, era_end_date, start_close, end_close,

-- overall_pct_return, annualized_pct_return, max_drawdown_pct -- the shape

-- produced by regime_ticker_metrics.sql. If the actual table/view has a

-- different name, that's the only line each query below needs changed.

-- ERA 21 EXCLUDED: both queries filter out era_id 21. Its end date

-- (2026-07-31) only marks where price data collection currently stops, not

-- a real conclusion to the era the way every other era's end date does --

-- the overview section's table already tells the reader era 21 is open and

-- why, so the validation queries work only with the 20 closed eras rather

-- than mixing a settled 20-era result with one provisional, still-moving

-- data point.

-- Two result sets, both pooling all six tickers (CAT, JPM, LLY, MSFT, NVDA,

-- SHW -- MSFT and NVDA enter partway through the ledger, so early eras are a

-- 4- or 5-ticker comparison, not 6):

-- 1. Magnitude vs. velocity, by Regime -- MEDIAN (not AVG; ~20 eras per

-- Regime is a small, outlier-prone sample, same reasoning as the rest

-- of this analysis) of overall_pct_return and max_drawdown_pct (the

-- aggregate, duration-capped view of a regime) alongside a plain

-- linear daily rate and annualized_pct_return / CAGR (the

-- duration-normalized view). SQL Server's PERCENTILE_CONT is a window

-- function, not a GROUP BY aggregate, so it needs

-- OVER (PARTITION BY regime) plus SELECT DISTINCT to collapse to one

-- row per Regime.

-- 2. Sign-mismatch summary -- does overall_pct_return's sign match what

-- each Regime implies (positive for Expansion; negative for

-- Contraction and Shock)? max_drawdown_pct is deliberately excluded

-- from this check: drawdown is structurally <= 0 for every era

-- regardless of Regime, so "wrong sign" isn't a meaningful test for

-- that column. NOTE: with only 26-56 era-ticker rows per regime and

-- mismatch counts of just 3, 4, and 6, these percentages rest on very

-- small absolute counts -- same small-sample caution as the reason

-- medians are used above, not averages. Read this as a suggestive

-- pattern worth further backtesting on other tickers and longer

-- timeframes, not a settled conclusion the way the medians above are.

-- ============================================================================

-- ---- Result set 1: magnitude (aggregate) vs. velocity (rate), by Regime --

-- The DISTINCT lives inside this CTE; the ORDER BY happens on the plain

-- SELECT * outside it, where SQL Server's "ORDER BY items must appear in

-- the select list" restriction on SELECT DISTINCT no longer applies.

with regime_medians as (

select distinct

regime,

percentile_cont(0.5) within group (order by overall_pct_return)

over (partition by regime) as median_overall_pct_return,

percentile_cont(0.5) within group (order by max_drawdown_pct)

over (partition by regime) as median_max_drawdown_pct,

percentile_cont(0.5) within group (order by overall_pct_return / trading_days)

over (partition by regime) as median_daily_pct_rate,

percentile_cont(0.5) within group (order by annualized_pct_return)

over (partition by regime) as median_annualized_pct_return

from dbo.regime_ticker_metrics

where era_id <> 21 -- exclude the still-open era; see header note

)

select *

from regime_medians

order by case regime when 'Expansion' then 1 when 'Contraction' then 2 when 'Shock' then 3 end;

-- ---- Result set 2: sign-mismatch summary, all six tickers pooled ----------

with flagged as (

select

case when regime = 'Expansion' then 1 else 0 end as expected_positive,

case when overall_pct_return > 0 then 1 else 0 end as actual_positive

from dbo.regime_ticker_metrics

where era_id <> 21 -- exclude the still-open era; see header note

)

select

regime,

count(*) as era_ticker_rows,

sum(case when expected_positive <> actual_positive then 1 else 0 end) as mismatches,

cast(100.0 * sum(case when expected_positive <> actual_positive then 1 else 0 end)

/ count(*) as decimal(5,1)) as mismatch_pct

from flagged

group by regime

order by case regime when 'Expansion' then 1 when 'Contraction' then 2 when 'Shock' then 3 end;

Appendix A.4: Section 2 — Validation By Individual Ticker

This section's script re-runs the same checks one ticker at a time, so a reader can see how any single ticker compares to the six-ticker pooled pattern from Section 1.

use SecurityTradingAnalytics

-- ============================================================================

-- "Validating Market Regimes Across Six Tickers" -- Section 2 (per-ticker

-- validation) appendix script

-- ============================================================================

-- Same source assumption as the section 1 script: dbo.regime_ticker_metrics,

-- one row per (era_id, ticker). Adjust the FROM clause in both queries below

-- if the real object has a different name.

-- ERA 21 EXCLUDED: same reasoning as the section 1 script -- era 21's end

-- date only marks where price data collection currently stops, not a real

-- conclusion to the era, so it's left out of both result sets here too.

-- Set @ticker to any of CAT, JPM, LLY, MSFT, NVDA, SHW and re-run. (MSFT and

-- NVDA only have rows from the era each ticker's own price history begins --

-- era 4 for MSFT, era 7 for NVDA -- so fewer than 20 rows come back for

-- those two.)

-- Two result sets:

-- 1. This ticker's own era-by-era results, with the same sign-mismatch

-- flag used in the section 1 pooled summary, so a reader can see

-- exactly which eras this ticker's return direction disagreed with

-- its Regime.

-- 2. This ticker's median overall_pct_return and max_drawdown_pct by

-- Regime, set side by side against the all-six-ticker pooled medians

-- from section 1 -- so a reader can see at a glance whether this

-- ticker runs milder, harsher, or in line with the pooled pattern.

-- ============================================================================

declare @ticker varchar(20) = 'LLY'; -- <-- change this and re-run

-- ---- Result set 1: this ticker's era-by-era results, flagged -------------

select

era_id,

era_name,

regime,

trading_days,

overall_pct_return,

annualized_pct_return,

max_drawdown_pct,

case

when (regime = 'Expansion' and overall_pct_return <= 0)

or (regime <> 'Expansion' and overall_pct_return > 0)

then 'mismatch'

else ''

end as sign_check

from dbo.regime_ticker_metrics

where ticker = @ticker

and era_id <> 21 -- exclude the still-open era; see header note

order by era_id;

-- ---- Result set 2: this ticker vs. the six-ticker pooled median, by Regime

with pooled as (

select distinct

regime,

percentile_cont(0.5) within group (order by overall_pct_return)

over (partition by regime) as pooled_median_overall_pct_return,

percentile_cont(0.5) within group (order by max_drawdown_pct)

over (partition by regime) as pooled_median_max_drawdown_pct

from dbo.regime_ticker_metrics

where era_id <> 21 -- exclude the still-open era; see header note

this_ticker as (

select distinct

regime,

percentile_cont(0.5) within group (order by overall_pct_return)

over (partition by regime) as ticker_median_overall_pct_return,

percentile_cont(0.5) within group (order by max_drawdown_pct)

over (partition by regime) as ticker_median_max_drawdown_pct

from dbo.regime_ticker_metrics

where ticker = @ticker

and era_id <> 21 -- exclude the still-open era; see header note

)

select

p.regime,

t.ticker_median_overall_pct_return,

p.pooled_median_overall_pct_return,

t.ticker_median_max_drawdown_pct,

p.pooled_median_max_drawdown_pct

from pooled p

join this_ticker t on t.regime = p.regime

order by case p.regime when 'Expansion' then 1 when 'Contraction' then 2 when 'Shock' then 3 end;

Search This Blog

Security Trading Analytics

Validating Market Regimes Across Six Tickers

The Eras for this Post

Computing the Era/Regime Metrics

Highlights from Era/Regime Metrics Analysis

Next Steps

Appendix A.1: Market Regime Ledger — Table and Population Script

Appendix A.2: Shared Trading Dates Across Tickers

Appendix A.3: Section 1 — Validation Across All Tickers

Appendix A.4: Section 2 — Validation By Individual Ticker

Comments

Post a Comment

Popular posts from this blog