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.