Risk-On Risk-Off Dashboard Excel: Track Market Regime with 8 Intermarket Ratios (June 2026)

A risk-on risk-off dashboard in Excel scores the market regime from eight intermarket ratios: XLY/XLP, SMH/XLU, HYG/LQD, IWM/SPY, SPY/TLT, QQQ/SPY, EEM/SPY, and GDX/GLD. Each ratio gets +1 if it is above both its 50-day and 200-day moving averages, -1 if below both, and 0 in between; the eight votes sum to a score from -8 (strong risk-off) to +8 (strong risk-on), which the template maps to a cyclical or defensive sector allocation. Prices come from MarketXLS formulas such as =QM_Last("XLY")/QM_Last("XLP"). A sample and a live-formula version of the template are linked below.

See it in actionMarketXLS video

The full MarketXLS demo

Watch full demoBook a demo

Try it in your own spreadsheet

Get the free MarketXLS sample workbook (.xlsx).

Email address

Download free workbook

No account needed. MarketXLS live data requires a paid plan. Preview workbook

Explore more videos +

IntroductionHistorical DataOptions DataUse-cases

In the template's June 6, 2026 sample data, the aggregate score was +4 (Risk-On), down from +5.5 in December 2025. The eight ratios are telling a more nuanced story than the S&P 500 chart alone: semis are still leading utilities, high yield bonds are still leading investment grade, but small caps have started losing to large caps and emerging markets have softened against the US. The template shows those cross-currents on one page.

Quick Look: Risk-On Risk-Off Snapshot (June 6, 2026)

The combined picture is straightforward: equity-related risk is being rewarded, credit risk is being rewarded, but breadth has narrowed enough that small caps and emerging markets are no longer participating. Scoring each indicator independently shows that mix, which a single index chart hides.

What Risk-On Risk-Off Actually Means

Risk-on and risk-off describe whether investors are moving money toward higher-volatility assets (risk-on) or toward safer ones (risk-off).

In a risk-on regime, money flows into:

• Cyclical sectors: Consumer Discretionary (XLY), Financials (XLF), Industrials (XLI), Materials (XLB)
• High-beta growth: Semiconductors (SMH), Biotech (IBB), Innovation (ARKK)
• Small caps (IWM), Emerging Markets (EEM)
• High Yield credit (HYG, JNK)

In a risk-off regime, money rotates into:

• Defensive sectors: Consumer Staples (XLP), Utilities (XLU), Healthcare (XLV)
• Long-duration Treasuries (TLT, IEF, EDV)
• Gold (GLD, IAU) and Gold Miners (GDX)
• Investment Grade corporate bonds (LQD)
• Cash, T-bills, money market funds

The same S&P 500 print can hide either move. What matters is which side of the boat investors are sitting on. Intermarket ratios make that visible.

Why an Intermarket Ratio Beats a Single Index

An intermarket ratio is one price divided by another, and its direction shows which of the two assets investors prefer. If XLY (Consumer Discretionary) is rising 8% and XLP (Consumer Staples) is rising 4%, the XLY/XLP ratio is rising - and that tells you investors prefer the cyclical bet over the defensive one. Both are up; one is more loved than the other.

Three reasons ratios beat single indices for regime detection:

1. They strip out the macro tide. If everything is up because liquidity expanded, the absolute returns of XLY and XLP are both high. The ratio cuts through that and isolates relative preference.
2. They turn into clean trends. Because ratios oscillate around their own moving averages, you can score them mechanically: above 50DMA = risk-on, below = risk-off.
3. They can lead the headline index. Cyclical leadership sometimes breaks down before the S&P 500 does, so a rolling-over XLY/XLP ratio can be an early warning, though it also gives false signals.

The Eight Ratios on This Dashboard

The template tracks eight ratios, each chosen because it captures a distinct dimension of risk appetite.

1. XLY / XLP (Consumer Discretionary / Consumer Staples)

The classic cyclical-vs-defensive ratio. Discretionary stocks (Amazon, Tesla, Home Depot, McDonald's) are sensitive to consumer confidence and economic strength. Staples (Procter & Gamble, Coca-Cola, Walmart, Costco) are sold no matter what the economy does. A rising XLY/XLP ratio says investors expect strong consumer spending and are willing to pay up for cyclical earnings.

=QM_Last("XLY") / QM_Last("XLP")

Formula documentation: QM_Last

2. SMH / XLU (Semiconductors / Utilities)

The high-beta-growth vs lowest-beta-defensive ratio. Semis are the most cyclical sub-sector inside Technology, and Utilities are the most regulated and steady. When SMH leads XLU, investors are pricing in productivity gains, capex cycles, and AI demand. When XLU leads SMH, investors want yield and recession protection.

=QM_Last("SMH") / QM_Last("XLU")

Formula documentation: QM_Last

3. HYG / LQD (High Yield / Investment Grade Credit)

The credit-risk-appetite ratio. HYG holds B and BB rated corporate bonds where default risk matters; LQD holds A and BBB rated bonds where rate risk dominates. A rising HYG/LQD ratio says investors are willing to be paid for credit risk, which is the bond market's equivalent of buying small caps. Falling = stress in credit, often the earliest warning of an equity selloff.

=QM_Last("HYG") / QM_Last("LQD")

Formula documentation: QM_Last

4. IWM / SPY (Small Caps / Large Caps)

The breadth ratio. When small caps outperform, the rally is broad-based and the risk premium is being earned across the cap spectrum. When large caps dominate, leadership has narrowed - often to a handful of mega-caps - and the underlying market is weaker than the index suggests.

=QM_Last("IWM") / QM_Last("SPY")

Formula documentation: QM_Last

5. SPY / TLT (Stocks / Long Treasuries)

The asset-allocation ratio. SPY is the broad equity market; TLT is the 20+ year Treasury bond ETF. When SPY/TLT rises, equity risk premium is being earned and investors are choosing stocks over duration. When it falls, investors are buying bonds for safety or for falling-rate exposure.

=QM_Last("SPY") / QM_Last("TLT")

Formula documentation: QM_Last

6. QQQ / SPY (Nasdaq / S&P 500)

The tech-leadership ratio. Nasdaq 100 over the broad market signals growth, momentum, and mega-cap tech outperformance. When it rolls over, leadership has rotated into value, dividend, or defensive groups.

=QM_Last("QQQ") / QM_Last("SPY")

Formula documentation: QM_Last

7. EEM / SPY (Emerging Markets / S&P 500)

The global-risk-appetite ratio. Emerging markets are higher-volatility and dollar-sensitive. When EEM/SPY rises, investors are reaching for risk across borders, often because the dollar is weakening or global growth is firming. When it falls, capital is repatriating to US-listed safety.

=QM_Last("EEM") / QM_Last("SPY")

Formula documentation: QM_Last

8. GDX / GLD (Gold Miners / Gold Bullion)

The commodity-risk-appetite ratio. Gold miners trade with operational leverage to the gold price; bullion does not. When GDX outperforms GLD, miners are being paid for their operating leverage - usually because the commodity tape is firming. When GDX underperforms, even gold is being held defensively rather than for upside.

=QM_Last("GDX") / QM_Last("GLD")

Formula documentation: QM_Last

How the Regime Score Works

Each ratio gets a vote: +1, 0, or -1.

• +1 (Risk-On) if the current ratio is above both its 50-day and 200-day moving averages
• -1 (Risk-Off) if the current ratio is below both
• 0 (Neutral) if the ratio sits between the two

Summing eight votes produces an aggregate score from -8 to +8. The template then maps that score to one of five regimes:

The sample reading of +4 sits at the boundary between Risk-On and Strong Risk-On. The dashboard's color-coded scorecard shows which two ratios (IWM/SPY and EEM/SPY) are dragging the score lower - useful information that no single index captures.

What's Inside the Template

The premium template ships with 11 sheets, all designed to look presentation-ready when you open the file. Tab colors, hidden gridlines on the cover and dashboard, frozen panes, KPI tiles, embedded charts, conditional formatting (color scales, data bars, and icon sets), and data validation dropdowns are all built in.

1. Cover - branded title page with edition tag, last-updated date, color block, and a numbered table of contents.
2. How To Use - eight numbered onboarding steps plus a full inventory of every MarketXLS function used in the workbook with one-line descriptions.
3. Dashboard - the headline sheet. KPI tile row at the top (VIX, 10Y Treasury, fed funds, high yield spread, dollar index, aggregate regime score, regime label), a fully scored intermarket ratio table with conditional formatting, a 12-month regime score line chart, and a sector heatmap.
4. Inputs & Controls - yellow input cells, data validation dropdowns for scenario name and lookback period, custom ticker slots, sleeve weight inputs. Every downstream sheet references these inputs.
5. Scenario Analysis - five regimes mapped to historical 3-month returns for S&P 500, Tech, Utilities, Staples, Small Caps, High Yield, and Gold, with traffic-light conditional formatting and an embedded bar chart.
6. Sector Tilts - all 20 sector and theme ETFs with beta, YTD return, dividend yield, RSI, and a regime score column. Data bars on beta, color scale on YTD and score, icon set arrows on RSI.
7. Portfolio Allocation - sleeve table for the current regime with weight, dollar allocation (driven by the Inputs sheet), portfolio yield, and portfolio beta. A pie chart visualizes the allocation breakdown.
8. Correlation Matrix - 8 by 8 cross-asset correlation grid with red-to-green color coding plus six key insights bullets.
9. Regime History - 12-month table of aggregate score with regime label and a "what was working" narrative for each month, plus an embedded line chart.
10. Methodology - one-page explainer covering universe, scoring logic, regime bands, data sources, assumptions, and limitations.
11. Glossary & Disclaimer - definitions for every term in the workbook plus an educational-only disclaimer.

The whole workbook is designed to be opened in front of a portfolio committee or a client without further polish. Every chart has axis titles, every table has clean number formatting, every input cell is highlighted yellow, and the cover sheet sets the visual tone.

Building the Dashboard in MarketXLS

The template is built end-to-end on MarketXLS formulas. Three primitives do most of the work.

Live prices drive every ratio:

=QM_Last("XLY")
=Last("SPY")

Formula documentation: QM_Last, Last

Moving averages drive the regime votes. SimpleMovingAverage returns the average for a single ticker, so to get the moving average of a ratio, pull daily closes for both ETFs with =QM_GetHistory("XLY") and =QM_GetHistory("XLP"), divide the two close columns, and average the last 50 and 200 values:

=SimpleMovingAverage("XLY", 50)
=SimpleMovingAverage("XLY", 200)
=TwoHundred_DayMovingAverage("SPY")
=ExponentialMovingAverage("QQQ", 20)

Formula documentation: SimpleMovingAverage, TwoHundred_DayMovingAverage, ExponentialMovingAverage

Macro indicators drive the KPI tiles:

=VIX("US")
=TreasuryRate10Y("US")
=TreasuryRate3M("US")
=FederalFundsRate("US")
=BondYieldBAA("US") - BondYieldAAA("US")
=FinancialStressIndex("US")

Formula documentation: VIX, TreasuryRate10Y, TreasuryRate3M, FederalFundsRate, BondYieldBAA, BondYieldAAA, FinancialStressIndex

For each ratio, the Dashboard sheet builds a numerator and denominator price, divides them, and compares the live ratio against its own moving averages. A nested IF then converts that comparison into a +1, 0, or -1 vote. The eight votes sum into an aggregate score, which a single IF chain maps to one of five regime labels. None of it requires VBA or Power Query: MarketXLS (a paid subscription) handles the data pull and the dashboard handles the math. Quotes are 15-minute delayed on the Standard plan and real-time on Advanced and Business; on Mac or Excel for the web, prefix functions with mxls..

For the Sector Tilts sheet, the template uses the broader factor set:

=Name("XLY")
=Beta("SMH")
=StockReturnYTD("XLK")
=DividendYield("XLP")
=RSI("SMH")
=StockVolatilityThirtyDays("TLT")
=Sector("AAPL")

Formula documentation: Name, Beta, StockReturnYTD, DividendYield, RSI, StockVolatilityThirtyDays, Sector

For the Portfolio Allocation sheet, every dollar amount is =PortfolioSize * SleeveWeight, which means changing the portfolio size on the Inputs sheet recalculates every sleeve in the workbook.

Reading the Current Regime

The June 2026 sample readings on the Dashboard sheet show three things.

One: credit and equity are aligned. HYG/LQD is at 0.74 (above both moving averages), SPY/TLT is at 6.65 (well above the 200DMA), and the BAA-AAA credit spread sits at 3.12% (tight). Bond and equity markets are pointing the same way.

Two: leadership has narrowed. IWM/SPY at 0.37 is below its moving averages. Small caps are dragging. EEM/SPY at 0.084 is also rolling over. The market is being carried by mega-caps and semis, which means the rally has less breadth than it looks. The dashboard scorecard makes this immediately obvious - five tiles are green, one is yellow, and two are red.

Three: vol is low but not euphoric. VIX at 14.6 is below the 50DMA but not at extreme complacency levels (sub-12 is the typical signal). The dashboard's macro tile row flags this as confirmatory but not contrarian.

The framework's read: risk-on, with IWM/SPY and EEM/SPY as the ratios to watch. If those two also lose their 50DMAs, the aggregate score drops to +2 - still risk-on, but on a deteriorating trend. That is exactly the kind of nuance the template is built to surface.

How to Use the Template Day-to-Day

Open the live-formula version, click Data > Refresh All, and let MarketXLS pull every price, moving average, RSI, and macro indicator. Three workflows.

Daily check (5 minutes). Open the Dashboard sheet. Read the aggregate regime score. Look at the color-coded ratio scorecard for any votes that changed since yesterday. Glance at the macro tile row to check VIX and credit spreads. If the score moves by 2 or more points in a week, that warrants a sleeve review.

Weekly sleeve review (30 minutes). Open the Portfolio Allocation sheet. Compare current sleeve weights against the recommended weights for the current regime. If the gap exceeds your rebalancing threshold (say, 5%), rotate.

Monthly deep dive (60 minutes). Open the Regime History sheet to see how the aggregate score has trended. Use the Scenario Analysis sheet to remind yourself what historically follows each regime. Update the Inputs sheet with any new portfolio size, sleeve weight, or custom ticker choices.

Common Mistakes With Risk-On Risk-Off Frameworks

Three mistakes tend to recur.

Mistake 1: treating the score as a market-timing signal. The aggregate score is a tilt signal, not a market-timing signal. It tells you how to position within an asset class, not whether to be in the asset class at all. Going to 100% cash because the score hit -2 will whipsaw you out of every Risk-Off head-fake.

Mistake 2: ignoring macro confirmation. A +4 score with a 25 VIX and widening credit spreads is not the same regime as a +4 with a 14 VIX and tightening credit. The template puts both on the same screen so you do not have to choose.

Mistake 3: anchoring on one ratio. The XLY/XLP ratio is widely quoted, but it has given false signals. Aggregating eight independent ratios reduces the chance that any single quirky ETF flow drives the regime call.

Frequently Asked Questions

What is the difference between risk-on and risk-off? Risk-on is a market environment where investors are willing to hold higher-volatility assets - equities (especially cyclicals and small caps), high yield credit, emerging markets, and commodities. Risk-off is the opposite: investors rotate into Treasuries, gold, staples, utilities, investment grade credit, and cash.

Which intermarket ratio is most reliable? No single ratio is reliable on its own. HYG/LQD and XLY/XLP are often watched as early warnings, but each has had false signals. Aggregating eight ratios reduces the weight of any one false signal; when most of them point the same way, the regime read is more robust.

How often should I check the dashboard? Daily is fine but not required. Weekly is enough for most tactical allocators. The aggregate score does not change by more than 1 point on most days. When you do see a 2-point or larger move, that is your cue to look more carefully and consider a sleeve rebalance.

Can I use this for individual stocks? The dashboard is built around ETF ratios because ETFs cleanly represent the sectors and asset classes you are voting on. You can layer a single-stock screener on top of the regime call - for example, use the Sector Tilts sheet to identify which sectors are favored, then drop into a stock screener inside MarketXLS to pick names within those sectors.

Does this replace a sector rotation model? It is complementary. A pure sector rotation model picks the strongest sector each month. A risk-on/risk-off framework sits one level higher: it tells you whether to be in cyclical sectors at all. The two work well stacked. Use the regime to decide cyclical-vs-defensive lean, then use a sector momentum model to pick the specific sleeve within that lean.

Is this an investment recommendation? No. The template is built for education and research. It surfaces a structured way to read market regimes, but it does not predict returns and it does not constitute investment advice. Always consult a licensed advisor before acting on any market-related framework.

Download the Templates

Download the templates:

• - pre-filled with current June 2026 data, every data cell has a comment showing the MarketXLS formula that produced it.
• - live formulas in every cell; opens, refreshes, and renders the entire regime call automatically.

Open the sample if you want to see what the dashboard looks like before installing the MarketXLS add-in. Open the template if you already use MarketXLS and want a workbook that updates the moment you click Refresh.

The Bottom Line

Markets do not move on a single ratio, but a portfolio committee that wants a one-screen read of risk-on vs risk-off needs more than the S&P 500 chart. The Risk-On Risk-Off Dashboard template aggregates eight ratios that separate cyclical leadership from defensive rotation, scores each one mechanically against its own moving averages, and translates the regime into a sleeve allocation that a working portfolio can actually use.

The sample +4 score reads as Risk-On with narrowing breadth. Whether the score holds, decays toward neutral, or pushes into Strong Risk-On is exactly what this dashboard is built to track over the next 90 days.

For more Excel templates, see MarketXLS templates or book a demo to see the full MarketXLS feature set in action.

Risk-on risk-off frameworks are educational tools. They do not predict returns and they do not constitute investment advice. Always consult a licensed advisor before acting on any market-related research.