▶ Full Post Text
Built a data platform as a side project. Used it to analyse sector rotation patterns across 27 years of S&P 500 sector ETF data.
---
**Methodology**
- **Data:** S&P 500 Sector SPDR ETFs (XLK, XLE, XLF, XLV, XLY, XLP, XLI, XLB, XLU, XLRE, XLC)
- **Period:** 1999-2026 (XLRE from 2015, XLC from 2018)
- **Approach:** Rank all sectors by annual return, track year-over-year transitions, count reversals
**Why ETFs, not stock averages:** Market-cap weighted. A $3T company has more influence than a $100M micro-cap. I initially used FMP's sector_performance data (simple averages) but got garbage. +73% single-day sector returns from micro-cap noise. ETFs reflect actual investable returns.
---
**Results**
| Metric | Count |
|:-------|:------|
| Total year-over-year transitions | 246 |
| Bottom-3 → Top-3 next year | 27 (11%) |
| Top-3 → Bottom-3 next year | 34 (14%) |
| Major reversals (either direction) | 61 (25%) |
No sector stayed #1 for more than 2 consecutive years.
---
**Recent rankings (2024-2026)**
| Year | #1 | #2 | #3 | Worst |
|:-----|:---|:---|:---|:------|
| 2024 | Comm Services (+36%) | Financials | Consumer Disc | Materials (0%) |
| 2025 | Technology (+25%) | Comm Services | Industrials | Utilities (-43%) |
| 2026 YTD | Materials (+7%) | Energy | Consumer Staples | Financials (-2%) |
Materials: worst in 2024, leading 2026 YTD. The pattern continues.
---
**Notable reversals**
| Sector | Year 1 | Rank | Year 2 | Rank |
|:-------|:-------|:-----|:-------|:-----|
| Energy | 2020 | #11 (-33%) | 2021 | #1 (+53%) |
| Energy | 2021 | #1 (+53%) | 2022 | #1 (+59%) |
| Technology | 1999 | #1 (+63%) | 2000 | #9 (-44%) |
| Financials | 2008 | #9 (-54%) | 2012 | #1 (+25%) |
---
**SQL**
Annual sector ETF returns with rankings:
```sql
WITH etf_prices AS (
SELECT
symbol,
EXTRACT(YEAR FROM CAST(date AS DATE)) as year,
FIRST(adjClose ORDER BY date) as first_close,
LAST(adjClose ORDER BY date) as last_close
FROM fmp.stock_eod
WHERE symbol IN ('XLK', 'XLE', 'XLF', 'XLV', 'XLY', 'XLP', 'XLI', 'XLB', 'XLU', 'XLRE', 'XLC')
GROUP BY symbol, year
)
SELECT
year,
symbol,
ROUND((last_close - first_close) / first_close * 100, 1) as return_pct,
ROW_NUMBER() OVER (PARTITION BY year ORDER BY (last_close - first_close) / first_close DESC) as rank
FROM etf_prices
ORDER BY year, rank;
```
Count reversal events:
```sql
WITH rankings AS (
SELECT
symbol,
year,
ROW_NUMBER() OVER (PARTITION BY year ORDER BY (last_close - first_close) / first_close DESC) as rank,
COUNT(*) OVER (PARTITION BY year) as total_sectors
FROM (
SELECT
symbol,
EXTRACT(YEAR FROM CAST(date AS DATE)) as year,
FIRST(adjClose ORDER BY date) as first_close,
LAST(adjClose ORDER BY date) as last_close
FROM fmp.stock_eod
WHERE symbol IN ('XLK', 'XLE', 'XLF', 'XLV', 'XLY', 'XLP', 'XLI', 'XLB', 'XLU', 'XLRE', 'XLC')
GROUP BY symbol, year
)
),
lagged AS (
SELECT
a.year,
a.symbol,
a.rank as this_year_rank,
b.rank as last_year_rank,
a.total_sectors
FROM rankings a
LEFT JOIN rankings b ON a.symbol = b.symbol AND a.year = b.year + 1
WHERE b.rank IS NOT NULL
)
SELECT
COUNT(*) as total_transitions,
SUM(CASE WHEN last_year_rank >= total_sectors - 2 AND this_year_rank <= 3 THEN 1 ELSE 0 END) as bottom3_to_top3,
SUM(CASE WHEN last_year_rank <= 3 AND this_year_rank >= total_sectors - 2 THEN 1 ELSE 0 END) as top3_to_bottom3
FROM lagged;
```
---
**What I haven't tested**
- Monthly/quarterly rotation signals
- Transaction cost impact
- Risk-adjusted returns
- Optimal holding period after reversal
Anyone here trading sector rotation? What signals work for timing entry?
---
*Data: FMP. DuckDB on Parquet.*