- How to Calculate the Regression of Two Stocks on Excel
- How to Calculate Beta for Individual Stocks in Excel
- How to Balance Your Checkbook or Bank Account Using an Excel-Style Spreadsheet
- What Are the Advantages of a Simple Moving Average Over an Exponential Moving Average?
- How to Build an Automated Stock Trading System
- How to Calculate Portfolio Sector Contribution
Linear regression is a statistical method for finding the best-fit line of a data series. In stock trading, linear regression is sometimes called the time series forecast indicator. If you want to find the best-fit line for a series of stock data, you can use linear regression to do so. Performing linear regression is very challenging by hand, but you can use Microsoft Excel to perform the analysis in seconds.
Launch Microsoft Excel. A new worksheet opens.
Type your data into the worksheet in two columns. Place one set of data –for example, stock prices – into column A, starting in cell A1 and working down the column. Place the other set of data – for example, days that the stocks achieved the prices in column A – into column B, starting in cell B1 and then working down the column.
Click the "Data" tab on the ribbon and then click "Data Analysis."
Type the location for the data in column B into the "Input Y range" box. For example, if your data is in cells A1 through A20, type "A1:A20" (without the quotation marks) into the box.
Type the location for the data in column A into the "Input X range" box. For example, if your data is in cells B1 through B20, type "B1:B20" into the box.
Type a confidence level into the "Confidence Level" box. For example, if you want the confidence level of your results to be 90 percent, type ".90" into the box.
Click the "New Worksheet" button to make your results appear on a new worksheet or type a range of cells into the "Output Range" text box to have the results output on the same worksheet.
Click "OK" to have Excel perform the regression analysis.
The more data you type into the worksheet, the more accurate your results will be. For example, three days of stock market data won't give you an accurate picture of trends, but several years of data will.
Items you will need
- Microsoft Excel (Excel Starter is free with Windows)
- Stock market historical data
- Thinkstock/Comstock/Getty Images