How to Calculate Correlation Between Two Stocks

Correlation between two stocks is calculated from their daily returns, not their prices. Convert each price series to daily percentage returns, then apply the Pearson formula to the two return series. The result sits between minus one and plus one: plus one means the two moved in lockstep across the window, zero means no linear relationship, minus one means they moved exactly opposite. On a spreadsheet the whole job is a single CORREL call once the returns are in place.

The arithmetic matters less than what it exposes. Most traders quote correlation without having computed one, and the number carries assumptions that only become visible when you build it yourself.

Convert prices to returns first

Correlating raw closing prices is the most common error here, and it manufactures agreement that is not there. Two unrelated stocks that both drifted upward across a year will show a high price correlation, because both series share an upward trend. That tells you the market went up. It does not tell you the two positions carry the same risk.

Daily return is the change from one close to the next, as a fraction of the earlier close. A stock closing at 48.00 after a previous close of 50.00 returns minus 0.04, or minus four per cent. Do this for both stocks across the same trading days.

The alignment has to be exact. If one stock did not trade on a day the other did, drop that day from both series rather than letting the rows slip out of step. A single offset row quietly corrupts every number that follows it.

The formula, and what each half is doing

Pearson correlation is the covariance of the two return series divided by the product of their standard deviations. Read it in two halves.

The numerator asks a simple question: on days when stock X beat its own average return, did stock Y also beat its average? Every day where both deviate in the same direction contributes a positive amount. Every day where they deviate in opposite directions subtracts one.

The denominator then scales that total by how much each stock moves in general. That scaling is what forces the answer into the minus one to plus one range and makes two different pairs comparable regardless of how volatile either one is.

That second half is worth holding on to, because it means correlation deliberately discards magnitude. A pair that moves together in steps of 0.3 per cent and a pair that moves together in steps of four per cent both read 1.0. The number describes direction only, which is why it can never be the whole sizing input.

Doing it on a spreadsheet

Put closes for the first stock in column B and the second in column C, oldest row at the top, with dates in column A.

1. In D3, enter the daily return formula for column B and fill it down.
2. In E3, do the same for column C.
3. In any empty cell, run CORREL across the two return columns.

Around 250 rows is roughly a trading year. Google Sheets, Excel and LibreOffice all use the same function name.

Add one more column while you are there: a rolling sixty day CORREL, filled down so it updates each row. Chart that column. The line is almost always more useful than the single number, because it shows how unstable the relationship is. A pair averaging 0.5 across the year while swinging between 0.1 and 0.9 is a very different holding from a pair sitting near 0.5 throughout, and the single annual figure reports both as the same thing.

Reading the result for sizing

A worked case. You hold a position sized to risk one per cent of the account, and you are considering a second at the same risk.

If the two correlate near 0.9, a bad day for the shared driver hits both stops together, and your real exposure to that idea is close to two per cent, not one per cent twice. If they correlate near 0.1, the odds of both stopping out on the same day are far lower, and the two risks behave much more like separate bets.

The practical move is to treat a high correlation pair as one position and split the risk between them. Two positions correlating above roughly 0.7 take half a per cent each rather than one per cent each, so combined exposure to the shared driver stays where you intended it.

Our guide to correlation and portfolio heat works through what happens across a full book when that adjustment is skipped, and the position size calculator handles the arithmetic once you have decided on the split.

Where the number quietly misleads

Three failure modes account for most of the trouble.

The window chooses the answer. Correlation measured through a calm stretch and correlation measured through a drawdown are often different numbers for the same pair. Neither is wrong. They describe different periods, and the drawdown one matters more for sizing, because that is when the positions are actually being tested together.

It only sees straight lines. Pearson correlation measures linear relationship. Two stocks that are independent in normal conditions but both fall hard on the same macro shock can read near zero across a year that happened not to contain one. The relationship exists. It is not linear, so this tool cannot see it.

It is backward looking, always. Every correlation is a statement about a window that has already closed. It is evidence about the future rather than a measurement of it, and the gap between those two widens exactly when conditions change.

None of that makes the calculation useless. It makes it a floor rather than a ceiling. The number tells you what the relationship has been; your own reasoning about shared drivers tells you what it is likely to become. Counting the drivers behind your positions, as the portfolio heat guide sets out, catches overlaps the arithmetic misses entirely.

A ten minute weekly routine

Once a week, build return columns for everything currently open, run CORREL on each pair, and look at any reading above 0.7.

You are not looking for precision. You are looking for the pairs you had been counting as two ideas that the data says are one. That check is the entire practical value of knowing how the calculation works. The formula is secondary; running it before adding a position is the part that changes outcomes.