“The stock is up 18%” means very little on its own. If the broader market rose 22% over the same window, that 18% is underperformance. Benchmarking is the difference between a number and a judgement.
This walks through aligning two price series and computing relative performance, correlation and beta. The alignment step is where nearly everyone goes wrong, so it gets the most space.
Getting both series
Download your stock from the console here. For the benchmark, index data is a separate dataset — this tool covers equities, not indices — so use the companion index tool linked from the console for Nifty 50, Bank Nifty and similar.
Request the identical date range for both. It will not fully solve alignment, for reasons below, but starting with mismatched ranges guarantees trouble.
A workable alternative if you want to stay entirely within this tool: use a large, liquid stock as a crude proxy for the market. It is a much weaker benchmark and you should say so in any conclusion, but it is better than comparing against nothing.
Aligning the two series — the part that matters
The instinct is to paste both close columns side by side and start calculating. Do not do this.
Two series over the same calendar window will not necessarily have the same rows. A stock can be suspended, can go untraded for a day, or can be newly listed partway through — while the index has a value on every trading day. Paste them side by side and every row after the first mismatch is comparing a stock’s Tuesday against the index’s Wednesday. The result looks completely plausible and is entirely wrong.
Always join on date. Put the stock on Sheet1 and the index on Sheet2, then build a combined table keyed on the stock’s dates:
=XLOOKUP(A2,Sheet2!$A:$A,Sheet2!$H:$H,"")
Or, in older Excel and in Google Sheets:
=IFERROR(VLOOKUP(A2,Sheet2!$A:$H,8,FALSE),"")
Both return the index close for the stock’s date, or blank when that date is absent from the benchmark. Verify the join worked before going further:
=COUNTBLANK(P2:P500)
A handful of blanks is normal. Many blanks means your ranges do not overlap the way you assumed. Delete rows where either side is blank — every calculation below needs both values present on the same date.
Relative performance
You cannot compare a ₹3,200 stock to a 24,000-point index directly. Rebase both to 100 at the start, in two new columns:
=H2/$H$2*100 for the stock, and =P2/$P$2*100 for the index.
Note the anchored $H$2 — that is the whole trick. Chart both columns on one line chart and you have a directly readable picture of which one led and when.
The ratio between them makes divergence even clearer:
=(H2/$H$2)/(P2/$P$2)
Above 1 means the stock has outperformed since the start date; below 1 means it has lagged. A rising line means it is outperforming right now, regardless of whether either is going up.
One caution: every one of these numbers depends on your start date. Shift it by a month and the story can invert. Before drawing a conclusion, test two or three different start dates — if the conclusion does not survive, it was an artefact of where you began.
Correlation
With daily returns for the stock in column Q and the index in column R (each computed as =H3/H2-1 on its own series):
=CORREL(Q3:Q500,R3:R500)
The result runs from −1 to +1. Around 0.8 or above means the stock largely moves with the market. Around 0.3 means it is doing its own thing. Negative — genuinely rare among equities — means it tends to move opposite.
Correlation says nothing about magnitude. A stock can be almost perfectly correlated with the index while moving three times as far. That is what beta measures.
Beta
Beta is the slope of the stock’s returns regressed on the index’s. Excel gives it directly:
=SLOPE(Q3:Q500,R3:R500)
Order matters — stock first, index second. Reversing them produces a different number that is not beta. The equivalent longhand:
=COVARIANCE.P(Q3:Q500,R3:R500)/VAR.P(R3:R500)
Reading it:
- Beta near 1 — moves roughly in line with the market.
- Beta above 1 — amplifies market moves in both directions. A beta of 1.5 implies roughly a 1.5% move for each 1% market move, on average.
- Beta below 1 — dampens them.
Beta is a historical average, not a property of the company. It changes with the measurement window, it is unstable for illiquid stocks, and it describes the average relationship while telling you nothing about behaviour in the extreme sessions that actually matter. Treat it as description, never as prediction.
The R-squared of that relationship — how much of the stock’s movement the market explains at all — is worth having alongside it:
=RSQ(Q3:Q500,R3:R500)
A beta of 1.4 with an R-squared of 0.15 is close to meaningless: the market explains almost none of this stock’s movement, so the slope is fitted to noise.
Pitfalls
- Unadjusted prices break everything here. A split in the stock but not the index produces a fake −50% return on one day, which wrecks correlation, beta and the rebased chart simultaneously. Check for corporate actions first.
- Price return versus total return. Your stock series excludes dividends. Depending on which index series you use, the benchmark may or may not include them. Comparing a price series to a total-return index understates the stock by roughly its dividend yield, every year.
- Never sort one column independently. Sort the whole table, or the join silently breaks.
- Short windows prove nothing. Beta and correlation from thirty observations are statistically fragile. Use at least a year.
- Sector matters more than you expect. A bank benchmarked against a broad index is partly measuring the banking sector. Bank Nifty may be the fairer comparison.
None of this is investment advice or a recommendation about any security — see the disclaimer.
Keep reading
- Every column in your NSE export, explained — What OPEN, HIGH, LOW, PREV. CLOSE, LTP, CLOSE, VWAP, VOLUME, VALUE, NO OF TRADES, DELIVERY QTY and DELIVERY % actually mean in an NSE historical data file — and where people misread them.
- NSE series codes: EQ, BE, BZ, SM and ST — Why your export sometimes has two rows for the same date, what each NSE series code means for how a stock trades, and which rows you probably want to keep.
- Delivery percentage: how to read it, and its limits — How NSE delivery quantity and delivery percentage are calculated, what high and low readings suggest about participation, and the cases where the number misleads.
Or go straight to the download console and pull a file.