Pairs trading dashboard Excel is the simplest way to bring a market-neutral, mean-reverting strategy into a spreadsheet without giving up the look and feel of a real desk tool. June 2026 has been an awkward month for directional bets. Quad witching just rolled off, the Fed left rates on hold at the June FOMC, and the S&P 500 has chopped inside a tight range while sector dispersion has widened. That is the textbook setup pairs traders look for. Two stocks that normally move together get pushed apart by flows, an earnings reaction, or an index rebalance, the price ratio stretches, and the spread eventually snaps back. This guide walks through the entire workflow inside Excel: how to pick a pair, compute a rolling z-score, build a beta-neutral hedge, and size the trade using live MarketXLS formulas. Everything is wired to a downloadable premium-grade dashboard template at the bottom of the post.
Pairs trading dashboard Excel: snapshot
Before getting into the build, here is the snapshot the rest of this guide unpacks. Numbers below are typical long-run averages for a well-known consumer staples pair (Coca-Cola vs PepsiCo) and are used for educational purposes only.
| Pair stat | Value | What it tells you |
|---|---|---|
| Rolling 90-day correlation | ~0.78 | High enough for a stable spread |
| 60-day price ratio mean | ~0.483 | The anchor for the z-score |
| 60-day ratio standard deviation | ~0.012 | Sets the +/- 2 stdev entry band |
| Hedge ratio (Beta A / Beta B) | ~1.13 | Slightly more KO exposure per $ of PEP |
| Half-life of the spread | ~14 days | Fast enough to be tradeable |
| Annualized spread volatility | ~18.7% | Sets P&L expectations per leg |
| Typical entry z-score | +/- 2.0 | Stretched but not extreme |
| Typical exit z-score | 0.0 | Mean reversion |
The numbers in your own workbook will move with the market. The framework, the formulas, and the dashboard design stay the same.
Why pairs trading fits late June 2026
Three things make this a useful template to have on the desk this week.
1. Range-bound index, dispersed sectors. Even when the SPY trades in a tight band, individual sub-industries can swing 5-10% week to week. That is exactly the environment in which pair-trade spreads stretch. A directional strategy hates this tape. A market-neutral strategy welcomes it.
2. Post quad witching reset. Friday June 20 was the June quad witching expiry. Index option, single stock option, index futures, and single stock futures rolled at once, and the gamma profile resets for the new cycle. Pinning behavior unwinds, and stocks that were stuck to a strike all week can get loose again. Spreads that compressed into expiry often re-widen the following week.
3. Russell reconstitution week. The Russell index reconstitution lock date is June 27, 2026. Names being added, deleted, or shifted between indexes attract heavy flow without underlying fundamental change. Pair traders avoid the affected names but find clean opportunities everywhere else as benchmark tracking flows distort the rest of the tape.
4. Earnings drift cleanup. Q1 reports are finished, Q2 reports do not start in earnest until the second week of July. The two-week window between the rebalance and the bank earnings open is one of the cleaner liquidity backdrops of the year for non-directional trades.
The pairs trading dashboard Excel template tackles all four of these conditions inside a single workbook.
What a pair trade actually is
A pair trade is a long-short combination of two stocks that share a stable historical relationship. You go long one leg, short the other in roughly matched size, and bet that the relationship between the two prices reverts to its long-run mean.
The mechanics:
- Pick two stocks with high return correlation, ideally in the same sub-industry.
- Track the price ratio (Close A / Close B) over a rolling window.
- Compute a z-score: how many standard deviations is today's ratio away from its rolling mean?
- When the z-score crosses an entry threshold (typically +/- 2), open the trade. Long the leg that's underperformed, short the leg that's outperformed.
- When the z-score returns to zero (or another exit threshold), close.
The strategy is "market neutral" because moves in the broad market affect both legs roughly equally. What you are actually betting on is the spread - the gap between the two legs - not the direction of either stock or the index.
This is also why pair trades survive choppy markets. As long as the spread mean reverts, the pair trade pays. Directional strategies need a trend.
The classic universe of trade-ready pairs
Some pairs have decades of clean co-movement behind them. The template ships with a screener pre-populated with the most studied examples in US equities.
| Stock A | Stock B | Sector | Why it works |
|---|---|---|---|
| KO | PEP | Consumer Staples | Two dominant beverages names with overlapping geographies |
| V | MA | Financial Services | Card networks with near-identical business models |
| XOM | CVX | Energy | Both integrated supermajors, same commodity exposure |
| HD | LOW | Consumer Cyclical | Duopoly in big-box home improvement |
| GS | MS | Financial Services | Bulge bracket investment banks |
| UNH | ELV | Healthcare | Managed care leaders with shared regulatory exposure |
| CAT | DE | Industrials | Heavy equipment, cyclical capex demand |
| UPS | FDX | Industrials | Same logistics market structure |
| AAPL | MSFT | Technology | Mega-cap tech, partially overlapping customers |
| ORCL | CRM | Technology | Enterprise software peers |
| NVDA | AMD | Technology | Pure-play semiconductors |
| DUK | SO | Utilities | Regulated electric utilities in the Southeast |
These twelve pairs all sit on the Pairs Dashboard sheet of the template with live correlation, hedge ratio, and z-score columns. The conditional formatting highlights which pairs are currently stretched (green or red cells) and which are sitting at the mean (white cells).
Building the spread in MarketXLS
The whole pairs trading workflow can be reduced to a handful of MarketXLS formulas. Every formula below was verified against the MarketXLS Function Docs before being placed in the template.
Step 1 - Current prices
=QM_Last("KO") -> 67.84
=QM_Last("PEP") -> 142.55
The QM_Last function returns the pending last price for a ticker. The dashboard uses it for the two KPI tiles at the top of the workbook.
Step 2 - Current ratio
=QM_Last("KO") / QM_Last("PEP") -> 0.4760
This is the spread. It is a single number that summarizes how much KO costs in PEP units. When KO outperforms, this number goes up. When PEP outperforms, it goes down.
Step 3 - Historical series for the rolling window
=QM_GetHistory("KO", 1)
=QM_GetHistory("PEP", 1)
QM_GetHistory returns the full historical series. The second argument controls sort order: 1 for ascending dates. The Spread Analysis sheet pulls these series, computes the daily ratio, and feeds the rolling window calculations.
Step 4 - Rolling mean, standard deviation, and z-score
The rolling mean and stdev can be computed in two ways. Native Excel handles it cleanly:
RollingMean = AVERAGE(E[i-60]:E[i-1])
RollingStdev = STDEV.S(E[i-60]:E[i-1])
Z-Score = (E[i] - RollingMean) / RollingStdev
Or you can use the MarketXLS native rolling formulas where applicable:
=StandardDeviationOnClosePrice("KO", 60)
=SimpleMovingAverage("KO", 60)
The template uses the first approach because it lets the rolling window apply to the ratio (a derived series), not just to a single ticker.
Step 5 - Betas and hedge ratio
=Beta("KO") -> 0.62
=Beta("PEP") -> 0.55
HedgeRatio = =Beta("KO") / Beta("PEP") -> 1.127
The hedge ratio decides how much PEP you trade against each $1 of KO. With a beta-neutral hedge, you trade about 1.13 dollars of PEP for every dollar of KO so the two legs cancel out their market exposure.
Step 6 - Sector and industry checks
=Sector("KO") -> Consumer Defensive
=Sector("PEP") -> Consumer Defensive
=Industry("KO") -> Beverages - Non-Alcoholic
=Industry("PEP") -> Beverages - Non-Alcoholic
Always confirm the two legs share both sector and industry. The template pulls these labels into the screener so you can sort pairs and spot any that quietly drifted into different sub-industries.
Step 7 - Volatility for sizing context
=StockVolatilitySixMonths("KO")
=TwentyDayVolatility("KO")
These two formulas give you the historical volatility on each leg. The Portfolio Sizing sheet uses them indirectly through the SharpeRatio function for a risk-adjusted check on the planned trade.
The z-score signal in detail
The z-score is the single most important number in pairs trading. It converts the price ratio into a standardized score that tells you how far today's ratio is from its rolling mean, measured in standard deviations.
The formula:
Z = (TodayRatio - RollingMean60d) / RollingStdev60d
Reading the z-score:
| Z-Score | Interpretation | Action |
|---|---|---|
| Above +2.0 | Ratio is stretched high, A has outperformed B | Open short A / long B |
| +1.0 to +2.0 | Building toward an entry but not stretched | Watch |
| -1.0 to +1.0 | Spread is near the mean | Hold or wait |
| -2.0 to -1.0 | Building toward an entry on the other side | Watch |
| Below -2.0 | Ratio is stretched low, B has outperformed A | Open long A / short B |
| Crosses 0 | Mean reversion done | Close the trade |
The thresholds in the table are defaults. The Inputs sheet of the template lets you change them to anything from +/- 1.5 (more trades, less stretched) to +/- 3.0 (fewer trades, very stretched).
Hedge ratio: dollar-neutral vs beta-neutral
Once the z-score crosses the entry threshold, the question is how much of each leg to trade. Two methods are built into the template.
Dollar-neutral. Equal dollar amounts on each side. If you commit $25,000 to the pair, you put $12,500 long and $12,500 short. Simple. Ignores beta differences. Works when the two legs have similar market sensitivity.
Beta-neutral. Leg sizes weighted by beta. If KO has a beta of 0.62 and PEP has 0.55, you give PEP a bigger share so each leg contributes equal beta exposure. The Portfolio Sizing sheet does the math:
Leg A weight = Beta(B) / (Beta(A) + Beta(B)) = 0.55 / 1.17 = 0.47
Leg B weight = Beta(A) / (Beta(A) + Beta(B)) = 0.62 / 1.17 = 0.53
So a $25,000 pair gets $11,765 of KO short and $13,235 of PEP long. The market beta of the combined position is near zero.
In practice, beta-neutral is the better default because real pairs rarely have identical betas. The template defaults to beta-neutral but lets you switch by clicking a dropdown on the Inputs sheet.
Half-life: how long is the trade likely to take?
Mean reversion is not instantaneous. The spread can stretch for days or weeks before it snaps back. Half-life tells you the expected number of trading days for the spread to revert halfway to its mean.
The template uses a simple AR(1) model:
spread_t = a + (1 - kappa) * spread_{t-1} + noise
half-life = -ln(2) / ln(1 - kappa)
Reading the half-life:
- Under 10 days: very fast, tradeable but watch for transaction costs eating the edge.
- 10 to 25 days: the sweet spot for typical equity pair trades.
- 25 to 50 days: slow but workable for patient capital.
- Above 50 days: the pair is probably not mean-reverting in a tradeable way. Skip it.
This is a back-of-the-envelope calculation. Full statistical cointegration tests (Engle-Granger, Johansen) are beyond the scope of an Excel workbook. The Methodology sheet of the template flags this limitation directly.
What's inside the pairs trading dashboard Excel template
The template ships with ten sheets, each designed to look like part of a professional desk product rather than a free spreadsheet.
1. Cover. Branded title page with the workbook name, version, data-as-of date, and a table of contents. Hidden gridlines, navy background, gold accents. This is what someone sees when they open the file.
2. How To Use. Seven-step tutorial covering every input cell, every output, and every signal. Step one walks through picking the pair. Step seven explains how to use the signal log as a paper-trading journal.
3. Pairs Dashboard. The headline sheet. KPI tile row across the top with six big-number tiles: Stock A price, Stock B price, ratio A/B, z-score, 90-day correlation, and hedge ratio. Below the tiles, two embedded charts show the rolling ratio and the z-score over the last 60 days. Below the charts, a candidate-pair screener with twelve pre-built pairs and conditional formatting (color scales on correlation and z-score, data bars on hedge ratio and P/E, icon sets on beta).
4. Inputs and Controls. Yellow input cells with bold navy borders. Ticker A, Ticker B, lookback days, z-entry threshold, z-exit threshold, risk tolerance dropdown, capital per trade, hedge method dropdown (dollar-neutral or beta-neutral), risk-free rate, and a max-drawdown stop. Every other sheet references these cells, so changing one input updates the whole workbook.
5. Spread Analysis. Rolling window table with 60 days of price, ratio, rolling mean, rolling stdev, z-score, and signal. Conditional formatting on the z-score column highlights stretched values in red or green. The signal column auto-populates based on the user-set thresholds.
6. Statistical Tests. A one-page summary of the pair's suitability. Rolling 90-day correlation, beta of each leg, hedge ratio, half-life proxy, annualized spread volatility, and a pass-fail cointegration heuristic. If the pair fails, the box at the bottom turns red and you skip the trade.
7. Portfolio Sizing. Two sizing methods (dollar-neutral and beta-neutral) shown side by side with leg dollar amounts and approximate share counts. Below that, a scenario analysis matrix shows P&L for seven different market scenarios (convergence, divergence, beta-up, beta-down, single-leg blowout, fast compression). Icon sets highlight winners and losers. A small donut chart shows the capital split visually.
8. Correlation Matrix. A 10x10 heatmap of pre-screened pair candidates with red-amber-green conditional formatting. Diagonal cells are 1.00 by definition. Green cells (above 0.80) are the best pair-trade candidates. Red cells are non-starters.
9. Methodology. One-page explainer covering the core idea, pair selection rules, spread definition, z-score signal logic, hedge methods, half-life proxy, risk controls, data sources, and limitations. The "what we did not model" section is honest: no transaction costs, no borrow fees, no dividend adjustments, no corporate events.
10. Glossary and Disclaimer. Sixteen term definitions and an educational-use disclaimer.
Every sheet has a "MarketXLS Functions Used" footer listing the exact formulas active on that sheet, so you always know which functions to reference when extending the workbook.
Risk controls every pairs trader needs
Pair trades are not free money. The same forces that pull a stretched spread back to the mean can also blow it further open. The template builds three defenses into the workflow.
1. Max-drawdown stop. The Inputs sheet has a percentage stop (default 5%). When cumulative pair P&L breaches the stop, the dashboard flags "EXIT" regardless of the z-score signal. Real-money trading should use a hard stop, not just a flag.
2. Cointegration heuristic. The Statistical Tests sheet runs three checks: correlation above 0.7, half-life under 25 days, annualized spread volatility above 10%. The sheet only returns PASS if all three are met. If any fail, the workbook does not generate a trade signal.
3. Scenario analysis stress. The Portfolio Sizing sheet shows P&L under seven scenarios including single-leg blowouts. If a 6% move in one leg with no move in the other would breach your stop, you size down before opening the trade.
These are not exotic risk controls. They are the basics that get skipped when traders rush to put on a trade. The template makes them part of the workflow rather than an afterthought.
A worked example: KO vs PEP today
Putting it all together with the default pair on the dashboard.
The current ratio is around 0.476. The 60-day rolling mean is 0.483 with a standard deviation of 0.012. The z-score is therefore (0.476 - 0.483) / 0.012 = -0.58. The spread is slightly below its mean, but well inside the +/- 2 entry band. The current signal is HOLD.
Correlation over the last 90 days sits near 0.78. Beta of KO is 0.62, beta of PEP is 0.55, so the hedge ratio is 1.127. Half-life of the spread is around 14 trading days. All three of the cointegration heuristic checks pass.
If you committed $25,000 to the pair using the beta-neutral hedge, you would be sized for $11,765 short on KO and $13,235 long on PEP. Each leg contributes roughly equal market beta, so the combined position is close to market-neutral.
What you are watching for is the next time the z-score crosses below -2 (KO has materially underperformed PEP, you'd go long KO / short PEP) or above +2 (KO has outperformed, you'd go short KO / long PEP). The next move comes when something happens to break the recent equilibrium - a quarterly print, an analyst day, a guidance change, a commodity-input shock.
This is the rhythm of pair trading: long stretches of waiting, broken by sharp signals, followed by patient unwinding. The dashboard is built to make that wait productive instead of distracting.
Common mistakes to avoid
Most pair trades that fail do so for predictable reasons. The template tries to catch them.
Picking pairs across sectors. Two stocks that look highly correlated over the last six months but sit in different sectors will eventually diverge on macro forces. The screener filters pairs by sector to make this harder.
Ignoring the borrow cost. Short selling has a borrow fee. For small-cap or hard-to-borrow names, the fee can eat the entire edge. The template flags this in the Methodology sheet but does not pull live borrow rates. Always check with your broker before opening a real trade.
Using too short a lookback. A 20-day lookback makes the z-score jumpy. The default 60-day window is more stable. The Inputs sheet lets you experiment, but treat lookbacks under 30 days as research, not for real money.
Trading through ex-div days. Dividends create artificial gaps in the spread on ex-div days. A pair where the two legs have very different dividend schedules will give false signals. Match dividend timing where possible.
Ignoring corporate actions. Mergers, spinoffs, and major restructurings break pairs permanently. If a pair the model loved for years suddenly stops mean-reverting, the first thing to check is whether one of the legs had a corporate event.
The template will not catch all of these for you. It does, however, give you a place to record what you learned about each pair so the next time around you know whether to keep watching or move on.
Pairs trading vs other market-neutral strategies
Pairs trading is one flavor of market-neutral strategy. Two close cousins are worth knowing about.
Statistical arbitrage at the basket level. Instead of trading two stocks, statistical arb desks trade baskets of dozens or hundreds of names against each other. Same idea, more diversification, much harder to manage from a spreadsheet. The template is deliberately a single-pair tool, not a basket tool.
Long-short equity. Traditional long-short funds run a long book of high-conviction names and a short book of low-conviction names. Net market exposure is set by the manager, not forced to zero. Pair trading is the purest form of long-short: each pair is a self-contained, market-neutral position.
Risk parity and factor investing. These allocate across asset classes or risk factors to achieve a target volatility. Different goal entirely. They are not market-neutral by design.
The pairs trading dashboard Excel template is focused on the first cousin. It scales up to a portfolio of pairs by opening multiple copies of the workbook or extending the screener sheet to track several positions at once.
Download the templates
Download the templates:
- - Pre-filled with educational data; every data cell shows the MarketXLS formula that would generate it as a cell comment
- - Live-updating formulas across all 10 sheets
Both files are free for now and are designed to look like a professional-grade dashboard tool, not a free worksheet handout. The premium version updates every time a MarketXLS-licensed Excel session opens it.
Frequently asked questions
What is the best pair to start with?
KO/PEP is the canonical training pair. Two dominant consumer staples names, high correlation, slow mean reversion. V/MA and HD/LOW are close runners-up. Avoid technology pairs for a first trade - they have higher idiosyncratic risk and the spread can stay broken longer than a beginner is prepared to wait.
How often does the model generate a signal?
For a typical sector-pair with a 60-day lookback and +/- 2 entry threshold, the model generates one or two complete round trips per quarter. Tightening the threshold to +/- 1.5 roughly doubles signal frequency at the cost of catching more false positives.
Does pairs trading work in a directional market?
Yes, and that is the point. Because both legs share market exposure, a market-wide rally or sell-off cancels out in the spread. The strategy is designed to be insensitive to direction. The risk is structural break in the pair, not market direction.
How much capital do I need?
The template lets you set capital per trade on the Inputs sheet. The math works at any size, but the practical minimum is whatever your broker requires for both a long stock position and a short stock position with reasonable share counts on each leg. Many brokers require $25,000 minimum to short individual stocks under pattern day trader rules.
Can I trade ETFs instead of stocks?
Yes. Pair trading works on any two co-moving instruments. Sector ETFs (XLE vs XOP, XLF vs KRE) and country ETFs (EWA vs EWC) are popular examples. The template formulas work the same way with ETF tickers.
Why is the half-life so important?
Half-life is your best estimate of how long the trade will take. A pair with a 60-day half-life means even a perfect signal will take roughly a quarter to play out. If your patience or capital window is shorter than the half-life, the trade is not a fit.
How is the hedge ratio different from a simple share ratio?
A simple share ratio (one share of A vs one share of B) ignores both dollar exposure and beta. A dollar-neutral hedge equalizes dollar exposure but ignores beta. A beta-neutral hedge equalizes beta exposure. The beta-neutral hedge is the most defensible for market-neutral strategies.
Does the template handle dividends?
Not automatically. The QM_Last and QM_GetHistory functions return raw closing prices. The template flags dividend-day gaps in the Methodology sheet but leaves dividend timing for the user to handle. The simplest approach is to skip ex-div days for the legs in question.
The bottom line
Pairs trading dashboard Excel is the most direct way to bring a market-neutral strategy into a spreadsheet without leaving the comfort of a tool you already know. The math is simple - ratio, rolling mean, standard deviation, z-score, hedge ratio - but the discipline is what separates a profitable pair book from a losing one.
The June 2026 setup, with a range-bound index, fresh post-quad-witching gamma, and the Russell rebalance pulling flow around the market, is a textbook backdrop for non-directional trades. The template is the desk-grade dashboard that makes the workflow repeatable: pick the pair on the screener, confirm the cointegration heuristic on the Statistical Tests sheet, size the trade on the Portfolio Sizing sheet, log the signal on the Spread Analysis sheet.
To go further with MarketXLS, see the documentation on QM_GetHistory and historical series, explore the function library, or book a demo to walk through how the premium pairs trading dashboard plugs into a broader long-short workflow.
This template is for educational use only. Nothing here is investment advice or a recommendation to trade any particular pair. Past performance does not guarantee future results. Always paper-trade a new strategy before committing real capital, and consult a qualified financial advisor before changing your portfolio.
