Home › Templates › Stock watchlist template
Stock Watchlist Template
A free stock watchlist spreadsheet for Excel and Google Sheets: list the stocks you want to buy, type a target buy price, and the sheet shows how far each one is from it, flags it "At/below target", "Within 5%" or "Wait", adds the 52-week range, P/E, dividend yield and days to earnings, and sizes a planned position from your risk per trade.
Use it in Google Sheets: choose File → Import → Upload in Sheets and pick the .xlsx, or upload it to Google Drive and open it with Google Sheets. Prices are typed by hand, so the file works anywhere; live prices are an optional add-on (see below).
| Ticker | Sector | Price | Target | Distance | Alert status | 52-wk range | P/E | Yield | Days to earnings | Planned shares |
|---|---|---|---|---|---|---|---|---|---|---|
| EXMPL1 | Information Technology | $84.20 | $80.00 | 5.25% | Wait | 45% | 39.2x | 0.00% | 7 | 31 |
| EXMPL2 | Consumer Staples | $46.10 | $47.00 | −1.91% | At/below target | 39% | 16.0x | 2.69% | 34 | 55 |
| EXMPL3 | Utilities | $61.75 | $60.00 | 2.92% | Within 5% | 68% | 18.2x | 4.21% | 90 | 50 |
| EXMPL4 | Health Care | $23.40 | $18.00 | 30.00% | Wait | 26% | n/m | 0.00% | 13 | 100 |
| EXMPL5 | Financials | $38.90 | $39.50 | −1.52% | At/below target | 60% | 9.5x | 4.11% | −9 | 55 |
| EXMPL6 | Information Technology | $151.30 | $140.00 | 8.07% | Wait | 58% | 30.6x | 0.26% | 20 | 17 |
| EXMPL7 | Energy | $29.85 | $28.50 | 4.74% | Within 5% | 62% | 14.6x | 7.04% | 5 | 100 |
| EXMPL8 | Consumer Discretionary | $72.00 | $64.00 | 12.50% | Wait | 33% | 10.0x | 1.11% | 83 | 41 |
| EXMPL9 | Industrials | $54.40 | $52.00 | 4.62% | Within 5% | 63% | 20.9x | 0.88% | 11 | 48 |
| EXMPL10 | Communication Services | $19.10 | $17.50 | 9.14% | Wait | 32% | 17.4x | 0.00% | 41 | 142 |
| EXMPL11 | Real Estate | $97.30 | $99.00 | −1.72% | At/below target | 57% | 29.9x | 4.01% | 3 | 25 |
| EXMPL12 | Materials | – | $33.00 | – | Add price/target | – | – | – | – | 62 |
Watchlist or portfolio? This sheet is for stocks you do not own yet. Once you buy, move the row to the stock portfolio tracker, which tracks shares, cost basis and gains.
What's inside the spreadsheet
| Tab | What you type | What it calculates |
|---|---|---|
| Start here | Nothing | Steps, definitions, how to add live prices, limits, disclaimer |
| Watchlist | Five settings once (as-of date, alert zone %, account size, risk %, earnings window), then one row per stock. 200 rows. | Distance to target, alert status, 52-week range position, distance from the 52-week high, P/E, dividend yield, days until earnings, earnings check, risk per share, planned shares, position value, % of account, planned $ risk |
| Summary | Nothing | Tickers by alert status (with a bar chart), high-priority alerts, earnings checks, counts by sector, and the 8 stocks closest to target |
| Example Watchlist / Example Summary | Already filled | 12 fictional stocks so you can see every formula working |
| Lists | Optional edits | Sector names for the dropdown, with 3 spare rows |
The columns
| Column | Type | Why it is there |
|---|---|---|
| Ticker, company name, sector, priority | You type (sector and priority are dropdowns) | Identify the stock and group it on Summary |
| Thesis note | You type | One line on why the stock is on the list. If you cannot write it, the stock may not belong there. |
| Current price | You type (or optional live formula) | Compared with the target on every row |
| Target buy price, planned stop | You type | The price you would act at and where the idea is wrong. Both feed the position size. |
| 52-week high and low | You type | Where today's price sits in the past year's range |
| EPS (last 12 months), annual dividend per share | You type | The sheet works out P/E and dividend yield from the current price, so they stay in step when the price changes |
| Next earnings date | You type | Days until the report, flagged when it falls inside your earnings window or has already passed |
How to use it
- On Watchlist, check the settings in C3 to C7. The as-of date is
=TODAY(); the alert zone is 5%; type your account size and risk per trade (for example 1%). - Add one row per stock: ticker, name, sector, a one-line thesis and a priority.
- Type the current price, your target buy price and the stop where you would sell if the idea fails.
- Fill the optional fields from your broker or the company's filings: 52-week high and low, EPS for the last 12 months, annual dividend per share and the next earnings date.
- Read the grey columns. A green "At/below target" or amber "Within 5%" status means the stock has reached the price you set. It is a prompt to re-check the thesis, not an instruction to buy.
- Review on a schedule, for example once a week: update prices, open Summary and fix any "Date passed: update" earnings dates.
Live prices: GOOGLEFINANCE or the Excel Stocks data type
The file ships with typed prices on purpose. Typed prices work in every spreadsheet app, offline, and never break. If you want prices to update by themselves, there are two official options. Both are delayed.
Google Sheets: GOOGLEFINANCE
Replace a typed price with a formula such as =GOOGLEFINANCE("NASDAQ:GOOG","price") or =GOOGLEFINANCE(A10,"price"). Google's GOOGLEFINANCE help page says "It’s mandatory to use both the exchange symbol and ticker symbol for accurate results", for example "NASDAQ:GOOG" instead of "GOOG". The same page lists "high52", "low52", "pe" and "eps" attributes, which can fill the 52-week and EPS columns. It lists no dividend attribute, so type the annual dividend yourself.
Delays and limits. Google describes the price as a "Real-time price quote, delayed by up to 20 minutes" and says quotes "are not sourced from all markets and may be delayed up to 20 minutes. Information is provided 'as is' and solely for informational purposes, not for trading purposes or advice." GOOGLEFINANCE exists only in Google Sheets: in Excel or LibreOffice the cell shows #NAME?.
Excel for Microsoft 365: Stocks data type
Microsoft's "Get a stock quote" page explains the steps: type tickers, select them, then Data → Stocks; another column can then pull the price. In this template, copy the tickers into a spare column (for example AB), convert those, and point Current price at them, e.g. =AB10.Price. Microsoft notes the Stocks data type "is only available to Microsoft 365 accounts or those with a free Microsoft Account", and that "Stock information is delayed, provided “as-is”, and is not for trading purposes or advice." The page applies to Excel for Microsoft 365; older versions such as Excel 2016 are not listed, which is why typed prices remain the default.
Leave the Example tabs as they are: EXMPL tickers are fictional and will not resolve to any quote.
The formulas, explained
Exact formulas from row 10 of the Watchlist tab. Inputs: F current price, G target buy price, H planned stop, I/J 52-week high/low, K EPS, L annual dividend, M next earnings date. Settings: C3 as-of date, C4 alert zone %, C5 account size, C6 risk %, C7 earnings window (days).
| Field | Formula |
|---|---|
| Distance to target % (N) | =IF(OR(F10="",N(G10)<=0),"",F10/G10-1) |
| Alert status (O) | =IF(A10="","",IF(OR(F10="",N(G10)<=0),"Add price/target",IF(F10<=G10,"At/below target",IF(F10/G10-1<=$C$4,"Within "&ROUND(N($C$4)*100,2)&"%","Wait")))) |
| Position in 52-week range % (P) | =IF(OR(F10="",I10="",J10="",N(I10)<=N(J10)),"",(F10-J10)/(I10-J10)) |
| From 52-week high % (Q) | =IF(OR(F10="",N(I10)<=0),"",F10/I10-1) |
| P/E (R) | =IF(OR(F10="",K10=""),"",IF(K10<=0,"n/m",F10/K10)) |
| Dividend yield % (S) | =IF(OR(N(F10)<=0,L10=""),"",L10/F10) |
| Days until earnings (T) | =IF(OR(M10="",$C$3=""),"",M10-$C$3) |
| Earnings check (U) | =IF(T10="","",IF(T10<0,"Date passed: update",IF(T10<=$C$7,"Earnings within "&$C$7&" days",""))) |
| Risk per share $ (V) | =IF(OR(N(G10)<=0,H10=""),"",IF(H10>=G10,"stop >= target",G10-H10)) |
| Planned shares (W) | =IF(OR(N(V10)<=0,N($C$5)<=0,N($C$6)<=0),"",ROUNDDOWN($C$5*$C$6/V10,0)) |
| Summary: tickers per status | =COUNTIF(Watchlist!$O$10:$O$209,A5) |
| Summary: average distance by sector | =IF(D5="","",IF(COUNTIFS(Watchlist!$C$10:$C$209,D5,Watchlist!$N$10:$N$209,">-1E+300")=0,"",AVERAGEIFS(Watchlist!$N$10:$N$209,Watchlist!$C$10:$C$209,D5))) |
| Summary: k-th closest to target | =IF(COUNT(Watchlist!$AA$10:$AA$209)<A23,"",SMALL(Watchlist!$AA$10:$AA$209,A23)) then =IF($I23="","",INDEX(Watchlist!$A$10:$A$209,MATCH($I23,Watchlist!$AA$10:$AA$209,0))) |
The two ratios follow standard definitions. Investor.gov (SEC) says the P/E ratio "is calculated by dividing the current stock price by the current earnings per share", with earnings per share based on "the earnings for the past 12 months". When EPS is zero or negative the sheet shows "n/m" (not meaningful) instead of a negative P/E. For yield, FINRA's Evaluating Performance page says that for stocks "yield is calculated by dividing the year's dividend by the stock's market price" and "if a stock doesn't pay a dividend, it has no yield". Investor.gov's glossary has no separate dividend yield entry, so FINRA is cited for that one.
Position size uses the fixed-fractional method: account size × risk % ÷ (target buy price − planned stop), rounded down to whole shares. It assumes you buy at your target. If the price is already below the target, size from your real entry with the position size calculator.
Worked example from the sample data
One row: EXMPL3 (fictional utility)
- Distance to target: $61.75 ÷ $60.00 − 1 = 2.92%. That is above the target but inside the 5% zone, so the status is Within 5%.
- 52-week range: ($61.75 − $52.30) ÷ ($66.20 − $52.30) = 68.0% of the way from the low to the high.
- P/E: $61.75 ÷ $3.40 EPS = 18.16x. Dividend yield: $2.60 ÷ $61.75 = 4.21%.
- Earnings on 28 Jan 2027: 90 days after the 30 Oct 2026 as-of date, outside the 14-day window, so no flag.
- Planned size: $25,000 × 1% = $250 risk ÷ ($60.00 − $55.00) = 50 shares, a $3,000.00 position (12.0% of the account).
Edge cases the sheet handles
- EXMPL1 is 5.25% above target: just outside the 5% zone, so it stays Wait. Its earnings are 7 days away, so the row is also flagged "Earnings within 14 days".
- EXMPL4 has negative EPS (−$1.35), so P/E shows n/m rather than a misleading negative number.
- EXMPL5 is below target, but its earnings date (21 Oct 2026) has passed, so the check says "Date passed: update".
- EXMPL12 has a target but no price yet: status "Add price/target", and it is left out of the distance ranking.
The Summary tab
| Rank | Closest to target | Price | Target | Distance | Status | Priority |
|---|---|---|---|---|---|---|
| 1 | EXMPL2 | $46.10 | $47.00 | −1.91% | At/below target | Medium |
| 2 | EXMPL11 | $97.30 | $99.00 | −1.72% | At/below target | Medium |
| 3 | EXMPL5 | $38.90 | $39.50 | −1.52% | At/below target | High |
| 4 | EXMPL3 | $61.75 | $60.00 | 2.92% | Within 5% | Low |
| 5 | EXMPL9 | $54.40 | $52.00 | 4.62% | Within 5% | Medium |
| Sector | Tickers | At/below | Within zone | Wait | Avg distance |
|---|---|---|---|---|---|
| Communication Services | 1 | 0 | 0 | 1 | 9.1% |
| Consumer Discretionary | 1 | 0 | 0 | 1 | 12.5% |
| Consumer Staples | 1 | 1 | 0 | 0 | −1.9% |
| Energy | 1 | 0 | 1 | 0 | 4.7% |
| Financials | 1 | 1 | 0 | 0 | −1.5% |
| Health Care | 1 | 0 | 0 | 1 | 30.0% |
| Industrials | 1 | 0 | 1 | 0 | 4.6% |
| Information Technology | 2 | 0 | 0 | 2 | 6.7% |
| Materials | 1 | 0 | 0 | 0 | – |
| Real Estate | 1 | 1 | 0 | 0 | −1.7% |
| Utilities | 1 | 0 | 1 | 0 | 2.9% |
The planned total of $15,578.50 is 62.3% of the $25,000 example account. Each planned position risks about $250, but all six together would tie up that share of the account. That is a reason to rank by priority before acting on several alerts at once.
Common mistakes
- Stale prices. Typed prices age fast. Update them before you read the status column, or use a live formula.
- Setting the target off today's price. A target picked as "5% below now" says nothing about value. Write in the thesis note why that price makes sense.
- A stop at or above the target. The sheet shows "stop >= target" and sizes nothing. For a long position the stop must be below the entry.
- Comparing P/E across industries. FINRA notes P/E "is generally used to compare companies in the same industry" (source). Use the sector column to compare like with like.
- Ignoring earnings dates. Prices can move sharply around a report. The earnings check flags rows inside your window so you can decide to wait or not.
- Forgetting the as-of date. On the Example tab it is fixed at 30 Oct 2026. On Watchlist it is
=TODAY(); if you overwrite it with a date, change it at each review.
FAQ
What should a stock watchlist include?
At minimum: ticker, the reason the stock is on the list, the current price and the price at which you would act. Useful extras are a planned stop, the 52-week high and low, valuation (P/E), dividend yield, the next earnings date and a priority. This template has all of them and turns them into an alert status and a planned position size.
How do I make a stock watchlist in Excel?
Put one stock per row with columns for ticker, current price and target buy price. Add a distance column, =F10/G10-1, and a status column with IF: at or below target, within a set percent, or wait. Conditional formatting colours the status. The free template here already has these formulas, 200 empty rows and a summary tab.
Can Google Sheets pull stock prices automatically?
Yes, with GOOGLEFINANCE, for example =GOOGLEFINANCE("NASDAQ:GOOG","price"). Google's help page says quotes may be delayed up to 20 minutes, are not sourced from all markets and are not for trading purposes. The function works only in Google Sheets. In Excel for Microsoft 365, the Stocks data type does a similar job.
Is GOOGLEFINANCE real time?
Not fully. Google lists the "price" attribute as a real-time quote "delayed by up to 20 minutes", and the "datadelay" attribute shows how far behind the data is. For a watchlist checked once a day or once a week, that delay rarely matters; for placing orders, use your broker's quote.
How do you calculate how far a stock is from your target price?
Divide the current price by the target and subtract 1. A stock at $61.75 with a $60.00 target is 61.75 / 60 − 1 = 2.92% above target. A negative result means the price is already below the target.
What is the difference between a watchlist and a portfolio tracker?
A watchlist holds stocks you do not own yet and the prices at which you would consider buying. A portfolio tracker holds positions you own: shares, cost basis, market value and gains. Many investors keep both and move a row across when they buy.
Related tools and templates
- Stock portfolio trackerFor stocks you already own
- Trading plan templateWrite the rules behind your targets
- Stock average calculatorAverage price after adding shares
- Position size calculatorShares for a set dollar risk
- Position size spreadsheetSizing for many trades
- Dividend trackerIncome from stocks you hold
- Trading checklistChecks before you buy
- All templatesFree spreadsheets
Educational template only, not financial, tax or investment advice. A target price or an alert status is a note to yourself, not a recommendation to buy or sell. Example data is fictional: tickers EXMPL1 to EXMPL12, company names and all prices, earnings and dividends are made up. Google, Microsoft, Investor.gov and FINRA material was retrieved on 30 October 2026.