How to Calculate the Regression of Two Stocks on Excel

••• Thinkstock/Comstock/Getty Images
Share It

Regression analysis is an advanced statistical method that compares two sets of data to see if they are related. The technique is often used by financial analysts in predicting trends in the market. Linear regression is used to show trends in data, and can compare volume and price levels. Microsoft Excel has a built in function to perform linear regression based on the data from two stocks that you enter into a worksheet.

Type the data into an Excel worksheet. Place one set of stock values in column A, starting in column A2, and then the other set of stock values in column B, starting in cell B2. Type a header for the values in cells A1 and B1. For example, you might type "Stock 1" in cell A1 and "Stock 2" in cell B1.

Click the "Data" tab, then click "Data Analysis" and then click "Regression."

Type the location for your first set of data into the "Input Y range." For example, if your data is in cells A1 to A10, type "a1:a10" into the text box.

Type the location for your second set of data into the "Input X range." For example, if your data is in cells B1 to B10, type "b1:b10" into the text box.

Check the "Labels" box. This tells Excel that the first row contains labels, not data.

Type a confidence level into the "Confidence Level" text box. For example, if you want your results to be at the 95 percent confidence level, type "95" into the text box.

Click the "New Worksheet" button to have your data appear in a new worksheet or type a range of values in the "Output Range" text box to have your results appear on the same worksheet.

Click the "OK" button. Excel will perform the regression and return the results in the location you specified in Step 7.

Tips

• If you don't see "Data Analysis" listed on the Data tab, load the Analysis ToolPak. Click the "Microsoft Office button, then click "Excel Options" and then click "Add-Ins." Click "Excel Add-ins" and then click "Go." Click the "Analysis ToolPak" check box and then click "OK."