How to Calculate Stock Correlation From Daily Returns

To learn how to calculate stock correlation, take two stocks' closing prices, calculate each stock's percentage change over the same trading intervals, and run Excel's CORREL function on the two return columns. Two stocks can both gain 10% over a stretch while taking turns posting the larger daily gain. Correlating their price levels can hide that pattern.

How to calculate stock correlation in Excel

Start with two stocks that trade in the same currency and market so the closing sessions are comparable. Put the dates in column A, Stock A's closes in column B, and Stock B's closes in column C. Sort oldest to newest and keep the dates beside the numbers.

1. Match the return intervals. Check that each pair of returns starts and ends on the same sessions. A weekend between consecutive trading sessions is normal. If one stock is missing Tuesday's close, Monday to Wednesday spans two sessions. For a daily calculation, omit affected return pairs and leave the missing close blank.
2. Calculate each percentage change. With the first close on row 2, enter =B3/B2-1 in D3 and =C3/C2-1 in E3. Fill down where both price pairs are available and format the results as percentages. For a price-return comparison, check that your price series account for splits without adding dividends.
3. Correlate the paired returns. In a separate cell, enter =CORREL(D3:D6,E3:E6) for the four pairs below. For a longer series, extend both ranges through the last paired return. Keep gaps on the same rows in both ranges, or copy only complete pairs into a clean table. Microsoft documents how CORREL handles blank cells and zero variation.

If you want dividend-inclusive results, use a consistent total-return series for both securities and check your provider's adjustment method. The same formula answers a different question when you change the inputs. See price return vs total return for the difference.

Same ending price, opposite daily return pattern

Copy these hypothetical closes into rows 2 through 6 to check the formulas. The extra decimal places preserve the exact returns in the example.

Both prices rise about 10.33%. =CORREL(B2:B6,C2:C6) gives approximately +0.92 because the price levels climb together. =CORREL(D3:D6,E3:E6) gives −1.00 because when one stock earns 4%, the other earns 1%. Each stock averages 2.5% per session, so a return 1.5 percentage points above one stock's average pairs with a return 1.5 points below the other's. Both stocks gained on every session with a return.

Four return pairs make the calculation easy to inspect. A figure from four real sessions would describe less than a week of trading.

What does a stock correlation of 0.8 mean?

Pearson correlation measures how closely two series follow a straight-line relationship. For returns, a value of +0.8 means the stocks tended to be above or below their respective average returns together during the measured window. You would need a separate count to see how often both stocks rose on the same day. Near zero means little linear relationship in the sample. If either stock has the same return in every row, the coefficient is undefined because that column has no variation.

Record the start and end dates, return frequency, price basis, and number of paired observations beside the result. If one spreadsheet uses daily returns and another uses monthly returns, you are measuring co-movement at different intervals. Even with the same frequency, changing the dates can change the answer. A study of 20 years of U.S. stock data found stronger collective movement during financial crises than during more stable periods. Read the study by James, Menzies, and Gottwald.

What correlation adds to a historical stock comparison

Open Compare Stocks to inspect the two price paths over the same available window. Note where the lines pull apart or lose ground together. Use the spreadsheet calculation for the coefficient and keep its dates beside the chart. The chart shows cumulative price movement from a common starting point. The coefficient summarizes the paired returns between those points. How to compare two stocks over time explains how to read the chart and its data-through dates.

For portfolio questions, the size of each position and the size of its swings matter alongside co-movement. You can also have shared exposure that a single historical coefficient misses, such as a company held directly and through an ETF. Check ETF overlap to measure that exposure from fund holdings. Investor.gov explains the broader role of diversification.

Equity Replay provides educational simulations based on historical closing prices. It offers no investment advice or brokerage services, and the chart records no trades. See how Equity Replay calculates a historical replay.