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.

Download Excel template (.xlsx) 128 KB · no macros · Excel 2016+ and Google Sheets · v1.0, 30 Oct 2026

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).

Preview: the Example Watchlist tab on 30 Oct 2026. Tickers EXMPL1 to EXMPL12, company names and all prices are fictional. Settings: 5% alert zone, $25,000 account, 1% risk per trade.
TickerSectorPriceTargetDistanceAlert status52-wk rangeP/EYieldDays to earningsPlanned shares
EXMPL1Information Technology$84.20$80.005.25%Wait45%39.2x0.00%731
EXMPL2Consumer Staples$46.10$47.00−1.91%At/below target39%16.0x2.69%3455
EXMPL3Utilities$61.75$60.002.92%Within 5%68%18.2x4.21%9050
EXMPL4Health Care$23.40$18.0030.00%Wait26%n/m0.00%13100
EXMPL5Financials$38.90$39.50−1.52%At/below target60%9.5x4.11%−955
EXMPL6Information Technology$151.30$140.008.07%Wait58%30.6x0.26%2017
EXMPL7Energy$29.85$28.504.74%Within 5%62%14.6x7.04%5100
EXMPL8Consumer Discretionary$72.00$64.0012.50%Wait33%10.0x1.11%8341
EXMPL9Industrials$54.40$52.004.62%Within 5%63%20.9x0.88%1148
EXMPL10Communication Services$19.10$17.509.14%Wait32%17.4x0.00%41142
EXMPL11Real Estate$97.30$99.00−1.72%At/below target57%29.9x4.01%325
EXMPL12Materials–$33.00–Add price/target––––62
Example Watchlist: distance from current price to target buy price for 11 fictional tickers. Three are at or below target, three are within the 5% alert zone and five are further away.-5%0%+5%+10%+20%+30%EXMPL2−1.91%EXMPL11−1.72%EXMPL5−1.52%EXMPL32.92%EXMPL94.62%EXMPL74.74%EXMPL15.25%EXMPL68.07%EXMPL109.14%EXMPL812.50%EXMPL430.00%green = at/below target · amber = within 5% · purple = waitCurrent price vs target buy price

Chart: distance to target from the same tab (EXMPL12 has no price yet, so it shows "Add price/target"). The workbook's Summary tab has a native bar chart of tickers by status.

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

TabWhat you typeWhat it calculates
Start hereNothingSteps, definitions, how to add live prices, limits, disclaimer
WatchlistFive 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
SummaryNothingTickers 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 SummaryAlready filled12 fictional stocks so you can see every formula working
ListsOptional editsSector names for the dropdown, with 3 spare rows

The columns

ColumnTypeWhy it is there
Ticker, company name, sector, priorityYou type (sector and priority are dropdowns)Identify the stock and group it on Summary
Thesis noteYou typeOne line on why the stock is on the list. If you cannot write it, the stock may not belong there.
Current priceYou type (or optional live formula)Compared with the target on every row
Target buy price, planned stopYou typeThe price you would act at and where the idea is wrong. Both feed the position size.
52-week high and lowYou typeWhere today's price sits in the past year's range
EPS (last 12 months), annual dividend per shareYou typeThe sheet works out P/E and dividend yield from the current price, so they stay in step when the price changes
Next earnings dateYou typeDays until the report, flagged when it falls inside your earnings window or has already passed

How to use it

  1. 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%).
  2. Add one row per stock: ticker, name, sector, a one-line thesis and a priority.
  3. Type the current price, your target buy price and the stop where you would sell if the idea fails.
  4. 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.
  5. 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.
  6. 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).

FieldFormula
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)

Edge cases the sheet handles

The Summary tab

12tickers on the list
3at or below target
3within 5% of target
5wait (more than 5% above)
5earnings within 14 days
$15,578.50planned $ in the 6 alert rows
RankClosest to targetPriceTargetDistanceStatusPriority
1EXMPL2$46.10$47.00−1.91%At/below targetMedium
2EXMPL11$97.30$99.00−1.72%At/below targetMedium
3EXMPL5$38.90$39.50−1.52%At/below targetHigh
4EXMPL3$61.75$60.002.92%Within 5%Low
5EXMPL9$54.40$52.004.62%Within 5%Medium
SectorTickersAt/belowWithin zoneWaitAvg distance
Communication Services10019.1%
Consumer Discretionary100112.5%
Consumer Staples1100−1.9%
Energy10104.7%
Financials1100−1.5%
Health Care100130.0%
Industrials10104.6%
Information Technology20026.7%
Materials1000–
Real Estate1100−1.7%
Utilities10102.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

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

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.