# How to Create a Stock Watchlist

Above is a link to the video that shows visually how to create a free stock watchlist with the help of Google Sheets and Google Finance. If the video went too fast for you or if you would just prefer to make one with written instructions, the steps are detailed easily for your use below:

Step 1: in row 1: enter ticker, price, PE, 52 week low, 52 week high, % > Low

Step 2: in cell B2, enter the formula: =googlefinance(a2,”price”)

Step 3: in cell C2, enter the formula: =googlefinance(a2,”pe”)

Step 4: in cell D2, enter the formula: =googlefinance(a2,”low52”)

Step 5: in cell E2, enter the formula: =googlefinance(a2,”high52”)

Step 6: in cell F2, enter the formula: =(B2-D2)/D2

Step 7: while cell B6 is still selected, click the percentage button (%) on the top left to change the viewer to show that cell as a percentage

Step 8: select cell B2, hold down control, click cell D2, click cell E2, then click the dollar button (\$) on the top left to change the viewer to show those cells in dollars

Step 9: select cell B2, click the small box on the bottom right of that cell and drag it downward. This will copy the formula entered into all the cells that you drag it down into.

Step 10: replicate step 9 but with cell C2, D2, E2, F2

Step 11: highlight all F cells from F2 downward, right click and select conditional formatting (may have to scroll through your options after right clicking to find it). Change the drop down to say “less than” and then in the box next to it, enter .1 and then choose a color (green is typically suggested as green means good!). Click done.

Step 12: high all C cells from C2 downward, right click and select conditional formatting. Change the drop down to say “less than” and then in the box next to it, enter whatever number you feel to be a low P/E ratio or discount P/E ratio (20 is my typical for most stocks). Change the color option to green and click done.

Step 13: start entering stock tickers into the open A cells and the other values will start to populate and track actively with Google Finance.

### dividendreaper

Author and editor of dividendreaper.com where personal finance meets life.

## 4 thoughts on “How to Create a Stock Watchlist”

1. Thanks for that great guide! Recently, I’ve started using Google Sheets and Google Finance, too. Love that automation of my spreadsheets. I also really like the mixed content of the video and written instructions! Keep up the good work!

Like

1. Thanks, Sir. I figured I’d do both this time since the formulas can take some people a bit longer to enter in and it also helps as a backup if I were to speak out of order! 🙂

Like

2. Nice article DR. I do use Google finance APIs. They do not contain all the information though. I have found them to be sufficient for mutual funds.

Like

1. I’ve never used them for mutual funds but that’s only because I only invest in single stocks. Prefer to create my own blend and forgo the fees. But it took awhile before I had my preferred blend.

Like