“Did this stock actually do well, or did the whole market go up?” is a reasonable question and the arithmetic is straightforward. Almost all the difficulty is in aligning two series correctly — and the failure mode is that misalignment produces confident, wrong numbers rather than an error.
First, the awkward part
This tool cannot download the Sensex. The searchable universe is equity scrips, and an index is not one — so you will need the index series from elsewhere. BSE publishes historical index values on its own site, and index data is widely available from other sources.
What you need is one row per date with a closing index value. Get it into a second sheet with dates in column A and closing values in column B, sorted oldest-first to match your export.
Before going further, decide whether the Sensex is even the right comparator. It is 30 large companies, free-float market-cap weighted, and heavily concentrated in financials and IT. If your scrip is a small-cap — which, given the shape of BSE’s listed universe, it probably is — the Sensex describes a different market from the one your stock trades in. A BSE MidCap or SmallCap index will tell you far more. The method below is identical whichever index you use.
Aligning the two series — the part that goes wrong
The tempting move is to paste the index column next to your price column and assume the rows correspond. They almost never do.
The Sensex has a value on every trading day. Your scrip only has a row on days it actually traded. Miss one no-trade day early in the range and every subsequent row is offset by one — comparing Tuesday’s stock price against Monday’s index, all the way down. Correlation and beta come out plausible-looking and completely wrong.
Always join on the date.Put your stock export in one sheet and the index in another, then pull the index onto the stock’s date spine:
=XLOOKUP($A2, Index!$A:$A, Index!$B:$B, "")
Or, without XLOOKUP:
=IFERROR(INDEX(Index!$B:$B, MATCH($A2, Index!$A:$A, 0)), "")
Note the exact-match third argument and the blank fallback. If a date is genuinely absent from the index sheet you want to see a blank and investigate, not have a nearby value substituted silently. An approximate match here is exactly the bug you are trying to avoid.
Then verify the join actually worked. Count how many rows failed:
=COUNTBLANK(C2:C1000)
A handful of blanks is tolerable. Dozens means your two sources disagree about dates — usually a date-format problem, where one sheet has real dates and the other has text that merely looks like dates. Check by running =ISNUMBER(A2) on both. This export writes genuine date cells; whatever you obtained the index from may not.
Adjust for corporate actions before you compare
Index values are continuous by construction — the index committee handles constituent changes, splits and so on internally, so the published series never has an artificial cliff.
Your stock data has no such treatment. A 1:5 split leaves an −80% day in the return column, on a date the index barely moved. In a beta or correlation calculation that single row will dominate the entire result, because regression is driven by extremes. You can get a negative beta on a stock that tracks the market closely, purely from one unadjusted split.
So adjust first. The corporate actions guide has the method. At minimum, sort your return column by absolute value and inspect the top twenty before trusting any of the numbers below.
Compute returns for both
Everything downstream works on returns, not prices. With stock close in E and the joined index value in C, add two return columns from row 3:
=IF(OR(E3="",E2=""),"",LN(E3/E2)) — stock
=IF(OR(C3="",C2=""),"",LN(C3/C2)) — index
Log returns, because they add across time. The blank guards matter: a missing value would otherwise produce a fabricated −100% return.
The three numbers worth computing
Relative performance
The simplest and most honest comparison. Over the whole period:
=SUM(F3:F1000) - SUM(G3:G1000)
With log returns this is just the difference in cumulative growth — the stock’s excess over the index. Positive means it outperformed. For a visual, chart both series rebased to 100 at the start: =100*E2/$E$2 and =100*C2/$C$2, which puts them on the same scale regardless of price level.
Correlation
=CORREL(F3:F1000, G3:G1000)
How closely the two move together, from −1 to +1. Large caps typically land somewhere around 0.5–0.8 against the Sensex. A near-zero correlation on a thin scrip is not independence from the market — it is mostly the absence of trading.
Beta
=SLOPE(F3:F1000, G3:G1000)
Equivalently =COVARIANCE.P(F,G)/VAR.P(G). Sensitivity to index moves: beta of 1.3 means the stock has historically moved about 1.3× the index. Below 1 is less volatile than the market, above 1 more.
Add =RSQ(F3:F1000, G3:G1000)alongside it. That tells you what fraction of the stock’s movement the index explains at all. A beta of 1.4 with an R² of 0.05 is not a useful beta — the relationship barely exists, and quoting the number implies a precision that is not there.
Why beta on a thin scrip is misleading
This deserves its own section, because it is not obvious and it affects most of the BSE universe.
When a scrip trades infrequently, its recorded closing price is stale — it reflects whenever the last trade happened, not the market’s state at the close. The index, meanwhile, is current to the second. So on any given day you are correlating fresh index information against old stock information.
The effect is systematic, not random: measured covariance is pushed toward zero, and beta is biased downward. An illiquid stock looks defensive — low beta, low correlation — when in truth it is simply not repricing. Anyone reading that as “this stock is insulated from market moves” has it backwards; it is more likely to gap violently when it finally does trade.
There are statistical corrections for this, but the practical answer is simpler: check liquidity first. If median No. of Trades is in the low double digits, do not report a beta. Restricting the calculation to rows where the scrip actually traded meaningfully is more honest than adjusting a bad estimate.
Pitfalls, in order of how often they bite
- Row-position alignment instead of a date join. Produces wrong numbers with no error. The most common mistake by a wide margin.
- Unadjusted corporate actions. One split can invert the sign of a beta.
- Approximate-match lookups. Quietly pairs mismatched dates. Always pass exact match.
- Text dates on one side. The join returns blanks or nonsense. Test with
ISNUMBER. - Comparing prices instead of returns. Correlating two rising price series gives a high number that reflects nothing but shared trend.
- Too short a window. Beta over three months of a thin scrip is noise. Prefer a year or more, while remembering that the longer the window, the more likely a corporate action sits inside it.
- Benchmarking a small-cap to the Sensex. Wrong comparator. Use a broader or size-matched index.
The general rule: when a comparison produces a surprising number, suspect the alignment before you believe the finding.
Keep reading
- Every column in your BSE export, explained — What Open, High, Low, Close, WAP, No. of Shares, No. of Trades, Total Turnover, Deliverable Quantity, % Deli. Qty and the two Spread columns actually mean — and where people misread them.
- BSE scrip codes: why the number matters more than the ticker — BSE identifies every security by a six-digit scrip code, not by symbol. What the code is, why two companies can share a ticker, and how to find the right one.
- BSE groups: A, B, T, Z, X, M and what they change — BSE sorts listed scrips into groups that govern how they trade — including group T, where intraday trading is banned outright. What each letter means for the price history you just downloaded.
Or go straight to the download console and pull a file.