Evaluating the Relationships of 2 Stocks by Covariance

What covariance between two stocks tells you

Covariance measures whether two stocks' returns tend to move in the same direction. A positive covariance means that when one stock's return is above its average, the other's usually is too; a negative covariance means they tend to move in opposite directions; a value near zero means little linear relationship. For portfolio building, pairs with low or negative covariance reduce overall portfolio volatility, which is the basis of diversification.

See it in actionMarketXLS video

Try it in your own spreadsheet

Get the free MarketXLS sample workbook (.xlsx).

No account needed. MarketXLS live data requires a paid plan. Preview workbook

Explore more videos

Evaluating the Relationships of 2 Stocks by Covari - stock data and fundamental analysis in Excel with MarketXLS

Covariance is expressed in squared return units, so its size is hard to compare across pairs. Dividing it by the two stocks' standard deviations gives the correlation coefficient, which always falls between -1 and +1 and is easier to read.

How covariance affects portfolio risk

Covariance changes a portfolio's risk, not its expected return. For a two-stock portfolio with weights w₁ and w₂, portfolio variance is:

w₁²σ₁² + w₂²σ₂² + 2·w₁·w₂·cov(1,2)

A high positive covariance adds to portfolio variance, so the two holdings rise and fall together and diversify each other less. A negative covariance subtracts from variance, so losses in one holding tend to be offset by gains in the other. Expected return is still the weighted average of each stock's expected return.

The covariance formula

The covariance between two return series X and Y is the average product of their deviations from their means:

cov(X,Y) = Σ (Xᵢ − X̄)(Yᵢ − Ȳ) / (n − 1)

where X̄ and Ȳ are the mean returns and n is the number of observations (the n − 1 version is the sample covariance).

How to calculate covariance of two stocks in Excel

Pull daily closing prices for both stocks into two columns. With the MarketXLS add-in, =QM_GetHistory("AAPL") returns a price history table you can reference.
Convert prices to daily returns: =(B3-B2)/B2, copied down for each stock.
Calculate sample covariance with Excel's built-in function: =COVARIANCE.S(returns1, returns2).
For an easier-to-read measure, calculate correlation: =CORREL(returns1, returns2).

Use the same date range and frequency for both stocks, and a long enough window (for example one to three years of daily returns) for a stable estimate. Covariance changes over time and tends to rise during market sell-offs.

Summary

Covariance shows how two stocks move together. Positive covariance adds to portfolio risk; low or negative covariance reduces it. In Excel, compute it from daily returns with COVARIANCE.S, and use CORREL for a scaled version. MarketXLS supplies the price history through formulas, so the calculation updates as new prices arrive. This article is educational and not investment advice.