How to Develop a Trading Strategy in Excel

by Eric Duncan ; Updated July 27, 2017

Items you will need

  • Microsoft Excel
  • Internet connection

Microsoft Excel can be a powerful tool in making investment and trading decisions. By importing external data and using Excel’s conditional formatting and formulas for calculations, investors can develop trading strategies and get instant buy and sell indicators.

Importing Data

Step 1

Select any cell in an Excel spreadsheet with the cursor. On the Menu Bar select “Data-Import External Data-New Web Query." This will open a dialog box that allows you to direct Excel to a website from which data can be imported. In the address bar of the browser in the dialog box, type "www.finance.yahoo.com" and click on the “Go” button.

Step 2

Type in the ticker symbol for one of the stocks you are watching in the website’s search bar. The dialog box has the browser inside it. Search for quotes as though you were using the website. This will be the ticker symbol imported into Excel.

Step 3

Check the box that highlights the “Last Trade.” At the bottom of the dialog box, click “Import.” Another dialog box will appear asking where you want to import the data. The cell that was originally highlighted should be visible, i.e., “=$A$1.” If this is correct, click OK.

Step 4

Hide rows that are not desired to be seen on the spreadsheet. The import tool will import eight pieces of data. Any data that is not relevant to the research must be hidden, not deleted. If the data/rows are deleted, Excel will produce errors the next time you attempt to import data. To hide data, highlight the applicable rows, right-click on them and select “Hide”.

Stock Analysis Formulas

Step 1

Calculate the growth rates of categories such as Earnings Per Share (EPS), Sales, Cash Flow and Equity by using Excel’s “=Rate” formula. Excel will calculate the growth rate of any two given values over a given period of time.

The formula looks like this: =rate(nper, pmt, pv, [fv], [type], [guess]). The “nper” is the number of years being calculated. The “pmt” is not used in this calculation. The “pv” is the beginning value (inputted as a negative number). The “[fv]” is the ending value. The “[type]” and [guess]” values are also ignored for this calculation.

To determine the EPS growth rate over the past 10 years for a company that has risen from 2.2 to 4.5, you would input the following calculation: =rate(10,,-2.2,4.5), giving an EPS growth rate of 7%.

Step 2

Calculate the future value of a stock to determine a purchase price by using Excel’s future value (FV) formula, “=FV(rate, nper, pmt, [pv], [type])”. The “rate” is the growth rate calculated in Section 2, Step 1. The “nper” is the number of years out to predict. The “pmt” is not used. The “[pv]” is the starting value (inputted as a negative number).

To determine what the stock price of a company that has a current stock price of $14 and a growth rate of 7% is going to be in five years, you would use this formula: =FV(7%,10,,-14), giving a stock price of $27.54 in five years at the current growth rate.

Step 3

Organize your spreadsheet to import stock price data and hide the unneeded data. Use Excel’s formulas to determine each company’s past growth rates in the columns adjacent to the share price. Then use the above formulas to determine target purchase prices for each stock in the next adjacent column.

Conditional Formatting

Step 1

Utilize conditional formatting. Imagine if you were tracking the stock prices of 100 companies and had buy or sell prices associated with each stock. It would take a considerable amount of time to manually scan and filter through all the data to determine if there was a buy or sell in a portfolio. Conditional formatting allows the user to quickly identify cells that meet certain criteria. When that criterion is met, Excel will highlight the cell.

Step 2

Click on a cell and type in the actual share price of a stock. In the next adjacent cell, input the target buy price of the stock. Click back on the cell containing the actual share price. As this number changes, Excel will compare it to the target buy price and highlight it if it drops to it or below. On the Menu Bar click "Format and Conditional Formatting." The Conditional Formatting dialog box will open.

Step 3

Set the first drop-down menu to “Cell Value Is” and the second drop-down menu to “less than or equal to”. On the third drop-down menu, click the “get data” button on the right side of the box, allowing you to choose the data you want to compare. In this case, click on the cell that had the target buy price in it and click OK. The Conditional Formatting dialog box will reappear.

Step 4

Click the “Format” button to open the “Format Cells” dialog box. Click the “Patterns” tab to choose the color the cell is to be highlighted if the cell meets the criteria placed on it. The font and other formatting can be changed here by clicking the “Font” tab. The conditions are in place and there should be no highlighted cells on the spreadsheet. As the stock price changes in the “actual share price” cell, if its price drops below the “target buy price” cell, Excel will highlight the cell.

Step 5

Change the value in the “actual stock price” cell to a number below the target buy price. The cell will be highlighted, indicating it has met the conditions that have been set identifying it as a stock to buy.

Set up conditional formatting for all stocks on your spreadsheet that contains the imported data and target price calculations. When a stock price meets your investing strategy’s criteria, that cell will be highlighted, alerting you to a possible investment opportunity.

Tips

  • A common strategy is to use past company performance to indicate future company and stock performance. Excel’s Rate Formula can calculate growth rates for a number of categories. The Future Value (FV) formula can predict the future value of these categories, giving the investor the tools and numbers needed to make investing decisions based on his strategies.

Warnings

  • The information in this article is for general information and reference. Investing is different for every individual based upon personal risk tolerance. Investing in the stock market has inherent risks. Develop a trading strategy and plan that meet your risk tolerance and financial needs.

References

  • Rule #1; Phil Town; 2006

About the Author

Eric Duncan is a military veteran and a professional in the safety, travel and aviation industries. Duncan has been writing since 2002 for magazines, newspapers, local business literature and on such websites as Singletraks.com. He has earned his Bachelor of Science in professional aeronautics and his Master of Business Administration.