How to Calculate the Regression of 2 Stocks Using Excel

By Markets Fool.com

Successful investing requires the ability to distinguish long-term trends from the short-term noise that moves stock prices on a minute-to-minute basis. One way to tune out the random oscillations and detect long-run patterns is to use a statistical process called "regression analysis."

Continue Reading Below

There are several ways you can use regression analysis in stock investing, but one method involves looking at two different stocks to see how their movements correlate over time. Below, we'll run through the process of setting up a regression analysis using Excel and interpreting the results.

Comparing two stocks' returns
The purpose of the two-stock regression analysis is to determine the relationship between returns of two stocks. With some pairs of stocks, the two stock prices will tend to move in tandem. In other cases, an opposite relationship might prevail, or there might be no clear relationship at all.

The first step in the analysis is to get price data on the two stocks in question. Enter their closing share prices at whatever intervals you see fit -- daily, weekly, or monthly are common picks -- and then calculate the percentage return from period to period.

Next, have Excel run the regression on the two columns of return data you generated. Under the Data menu, the Data Analysis button allows you to select Regression. If you don't have a Data Analysis option available, then you'll need to download the Analysis ToolPak from Excel. Pick one column to be the Y range and the other to be the X range.

What the results mean
The results you get will show a relationship between the returns of the two stocks. It will be in the following form:

Continue Reading Below

Return of Stock Y = a constant +/- (Return of Stock X * a coefficient)

The most important number above is the coefficient. If the coefficient is 1, then the two stocks will typically move in roughly the same direction and magnitude as each other. If it is greater than 1, then the stock you chose as Stock Y will move with more volatility than Stock X. If it's less than 1, then Stock X is the more volatile of the two. Negative coefficients indicate opposite direction of movement in most cases.

The other key result is the correlation of the two. Regression statistics will typically include an R-squared value. The closer to 1 this is, the stronger the correlation between the returns of the two stocks. An R-squared figure of zero indicates no correlation.

Regression analysis is complicated to do by hand, but spreadsheets make it easier. Although looking at past price data can't definitively predict the future, seeing how two stocks have behaved relative to each other in the past can at least provide some insight into future returns.

This article is part of The Motley Fool's Knowledge Center, which was created based on the collected wisdom of a fantastic community of investors. We'd love to hear your questions, thoughts, and opinions on the Knowledge Center in general or this page in particular. Your input will help us help the world invest, better! Email us atknowledgecenter@fool.com. Thanks -- and Fool on!

The article How to Calculate the Regression of 2 Stocks Using Excel originally appeared on Fool.com.

Try any of our Foolish newsletter services free for 30 days. We Fools may not all hold the same opinions, but we all believe that considering a diverse range of insights makes us better investors. The Motley Fool has a disclosure policy.