Educational Resource · 100% Native Google Sheets · Zero Scripts

Free Stock & ETF Screener for Google Sheets

Screen your watchlist against moving-average conditions using only GOOGLEFINANCE formulas. No add-ons, no coding.

Requires a free Google account. Opens a copy in your own Google Drive.

Core Architecture

What It Does

A deterministic, auditable screening tool running directly inside your Google Drive.

Zero Add-Ons or Extensions

Runs purely on native Google Sheets functions. No third-party plugins, no browser extensions, and zero security authorizations.

Automated Multi-Condition Logic

Screen up to 30 tickers simultaneously across moving average crossovers, long-term trends, and 52-week proximity rules.

Clean Running Results Lookup

The Results sheet uses non-blank sequential indexing to automatically list only qualifying tickers without gaps or manual sorting.

100% Customizable & Visible

No black box algorithms. Every query, calculation window, and logic test is visible, auditable, and customizable in plain sight.

Workflow Pipeline

How It Works (4 Steps)

Data flows cleanly from watchlist input to automated criteria testing to the final results dashboard.

Step 1

Add Watchlist Tickers

Add tickers on the Watchlist sheet (up to 30, e.g. AAPL, SPY, NASDAQ:MSFT).

Sheet: Watchlist

Step 2

Configure Conditions

Toggle conditions Yes/No and edit parameters on the Conditions sheet.

Sheet: Conditions

Step 3

Engine Evaluates Logic

The Engine sheet pulls prices and tests each condition.

Sheet: Engine

Step 4

View Screened Results

The Results sheet lists every ticker that meets at least one enabled condition and which conditions it meets.

Sheet: Results

File Blueprint

Workbook Structure (5 Sheets)

A clean separation of concerns keeps input, configuration, calculation, and output strictly isolated.

Sheet Tab	Role	Detailed Description
01. Start Here	Documentation	Quick-start guide, operational instructions, formula syntax rules, and financial disclaimers.
02. Watchlist	Input	Simple column (A6:A35) for up to 30 ticker symbols with optional user tags or notes in column B.
03. Conditions	Control Panel	Toggle screening conditions Yes/No, set the 52-week threshold parameter, and configure custom spare slots.
04. Engine	Calculation	Pulls historical quotes via GOOGLEFINANCE, computes moving averages, tests enabled conditions, and calculates running match numbers.
05. Results	Output	Automated dashboard extracting qualifying tickers, latest close prices, and triggered conditions with zero empty rows.

Pre-Engineered Rules

Sample Conditions Table

Tested momentum, moving-average alignment, and relative strength criteria.

Code	Condition Name	Formula Logic	Objective & Description
C1	Price crosses above the 21-day moving average from below	=AND(C6<E6,B6>D6)	Captures fresh momentum breakouts where yesterday's close settled below the prior 21MA and today's close settled strictly above it.
C2	Price above the 200-day moving average	=B6>F6	Ensures the security is situated in a macro long-term uptrend above its primary institutional moving average.
C3	Uptrend alignment (Price > 21-DMA > 200-DMA)	=AND(B6>D6,D6>F6)	Validates stacked moving average alignment where price leads short-term trend, which leads long-term trend.
C4	Price within 5% of 52-week high	=B6>=G6*(1-0.05)	Screens for high relative strength securities consolidating immediately below yearly high watermarks.
C5	Spare Custom Slot A	[User Configured]	Pre-wired column ready for custom indicators (e.g. 50-day MA, volume threshold, or ATR band).
C6	Spare Custom Slot B	[User Configured]	Second spare slot for additional risk filters, spread ratios, or multi-timeframe rules.

How "Crosses Above" Is Defined

In technical screening, testing only whether price is above a moving average (Price > MA) flags securities that have been above the line for months. To isolate an authentic crossover event, our template requires:

"crosses above" = prior close below prior MA AND latest close above latest MA, using daily closes

Formula: =AND(C6<E6,B6>D6). This rigorously confirms that the transition occurred specifically over the last completed trading session.

Under the Hood

Formulas Used in the Engine

Every formula used in the Engine sheet. Ticker is in A6 and rows 6 to 35 are filled down.

Last Close (B6)

=IFERROR(INDEX(QUERY(GOOGLEFINANCE(A6,"close",TODAY()-14,TODAY()),"select Col2 order by Col1 desc limit 1",1),2,1),"n/a")

Pulls historical closing prices for the last 14 calendar days, orders descending by date, and extracts the most recent closing price.

Prior Close (C6)

=IFERROR(INDEX(QUERY(GOOGLEFINANCE(A6,"close",TODAY()-14,TODAY()),"select Col2 order by Col1 desc limit 1 offset 1",1),2,1),"n/a")

Pulls historical closing prices over the 14-day window and uses "limit 1 offset 1" to extract the previous trading session's close.

21-day MA (D6)

=AVERAGE(QUERY(GOOGLEFINANCE(A6,"close",TODAY()-46,TODAY()),"select Col2 order by Col1 desc limit 21",1))

Queries a 46-calendar-day window, orders dates descending, limits to 21 trading sessions, and computes the 21-day simple moving average.

Prior 21-day MA (E6)

=AVERAGE(QUERY(GOOGLEFINANCE(A6,"close",TODAY()-46,TODAY()),"select Col2 order by Col1 desc limit 21 offset 1",1))

Calculates yesterday's 21-day moving average using "limit 21 offset 1" to evaluate the prior side of the moving-average crossover.

200-day MA (F6)

=AVERAGE(QUERY(GOOGLEFINANCE(A6,"close",TODAY()-332,TODAY()),"select Col2 order by Col1 desc limit 200",1))

Queries a 332-calendar-day window to guarantee at least 200 trading days, extracts the latest 200 daily closes, and computes their average.

52-week high (G6)

=GOOGLEFINANCE(A6,"high52")

Fetches the trailing 52-week high directly from Google Finance as an instantaneous security attribute.

C1 flag (H6): Price crosses above 21-DMA

=AND(C6D6)

Returns TRUE if prior close was strictly below prior 21MA and latest close is strictly above latest 21MA.

C2 flag (I6): Price above 200-DMA

=B6>F6

Returns TRUE if the latest closing price is strictly higher than the 200-day moving average.

C3 flag (J6): Uptrend alignment

=AND(B6>D6,D6>F6)

Returns TRUE when price is above the 21-DMA and the 21-DMA is simultaneously above the 200-DMA.

C4 flag (K6): Within 5% of 52-week high

=B6>=G6*(1-0.05)

Returns TRUE when the latest close is within 5% of the 52-week trailing peak price.

Conditions met (O6)

=TEXTJOIN(", ",TRUE,IF(H6,"Price crosses above 21-DMA",""),IF(I6,"Price above 200-DMA",""),IF(J6,"Uptrend alignment",""),IF(K6,"Within 5% of 52-week high",""))

Joins the textual names of all triggered conditions into a clean, comma-separated summary string.

Match number (P6)

=IF(N6>0,COUNTIF($N$6:N6,">0"),"")

Generates a continuous sequence number (1, 2, 3...) for qualifying rows by counting positive total scores down to the current row.

Results lookup (B9)

=IFERROR(INDEX(Engine!$A$6:$A$35,MATCH($A9,Engine!$P$6:$P$35,0)),"")

Looks up the sequence number in cell A9 against Engine column P and returns the corresponding matching ticker with no blank rows.

Why TODAY() - N Uses a Generous Calendar Window

Financial exchanges are closed on weekends and federal holidays, yielding roughly 252 trading sessions per calendar year. If you passed TODAY() - 21 to Google Finance, it would return only 14 to 15 trading days, making a true 21-day average impossible.

TODAY() - N is a generous calendar window that returns more than N trading days. For example, TODAY() - 46 guarantees at least 21 trading sessions, and TODAY() - 332 guarantees at least 200 trading sessions. The QUERY(..., "limit 21") or limit 200 clause then precisely slices the exact trading day count required.

Numbered Tutorial

Build It Yourself, Step by Step

A complete tutorial for creating this stock and ETF screener from a blank Google Sheet.

Create the 4 Core Sheets

Open a blank Google Sheet. Create the 4 core sheets: Watchlist, Conditions, Engine, and Results (plus a Start Here tab for notes).

Tip: Color-code your sheet tabs (e.g. green for Results, blue for Watchlist, gray for Engine) to create an immediate visual hierarchy.

Set Up the Watchlist Input Column

On the Watchlist sheet, label cell A5 as Ticker Symbol and cell B5 as Notes / Sector. Fill down cells A6 to A35 with your watchlist of up to 30 tickers (e.g. AAPL, SPY, QQQ, NASDAQ:MSFT).

Tip: If you screen non-US securities or ambiguous symbols, prefix them with the exchange code (e.g. NASDAQ:AAPL, NYSE:BRK.B, or TSE:SHOP).

Put the Formulas on an Engine Sheet

On the Engine sheet, reference the ticker in cell A6 with =Watchlist!A6. Then place the formulas from Section 6 into row 6: B6 for Last Close, C6 for Prior Close, D6 for 21-day MA, E6 for Prior 21-day MA, F6 for 200-day MA, and G6 for 52-week High. Fill rows 6 to 35 down.

Tip: Wrap GOOGLEFINANCE formulas in IFERROR so empty rows in your watchlist don't display #N/A or #VALUE! errors.

Make Flags Per Condition

In columns H6, I6, J6, and K6, insert the Boolean flags for C1, C2, C3, and C4. In column N6, calculate the enabled match count by multiplying each Boolean flag by whether that condition is toggled "Yes" on the Conditions tab:

=SUM(IF(AND(Conditions!$C$6="Yes",H6),1,0), IF(AND(Conditions!$C$7="Yes",I6),1,0), IF(AND(Conditions!$C$8="Yes",J6),1,0), IF(AND(Conditions!$C$9="Yes",K6),1,0))

Tip: In column O6, use the TEXTJOIN formula to assemble the readable names of every triggered condition.

Build the Results Lookup Using the Running Match Number

In cell P6 of the Engine sheet, put =IF(N6>0,COUNTIF($N$6:N6,">0"),"") and drag down to P35. On the Results sheet, place numbers 1 to 30 in column A (A9:A38). In cell B9, write =IFERROR(INDEX(Engine!$A$6:$A$35,MATCH($A9,Engine!$P$6:$P$35,0)),""). Repeat for Price and Conditions Met.

Tip: The running COUNTIF($N$6:N6, ">0") produces numbers 1, 2, 3 only on matching rows, so the Results sheet pulls them in sequence with zero empty rows.

Add Yes/No Dropdowns via Data > Data Validation

Select cells C6:C11 on the Conditions sheet. Navigate to Data > Data validation > Add rule, set criteria to Dropdown, and input Yes and No.

Tip: Set color chips in data validation: soft green for "Yes" and neutral gray for "No" for an intuitive control panel interface.

How to Add a Custom Condition

To add your own condition (e.g. 50-day MA crossover or volume threshold), write your Boolean test in column L6 of the Engine sheet. Add a Yes/No toggle in Conditions row 10, include the test in the column N SUM formula, and add an extra IF(L6, "Your Custom Condition Name", "") clause to the TEXTJOIN in column O.

Tip: Columns L and M in the Engine sheet and slots C5 and C6 on the Conditions tab are pre-reserved as custom slots for this exact purpose.

Technical Boundaries

Limitations and Good-to-Know

Transparent architectural parameters to ensure dependable execution.

Daily Closes, Not Intraday

Formulas evaluate settled end-of-day market closes. Intraday price spikes above a moving average will not register as a completed crossover until the trading session officially concludes.

Data Can Be Delayed & May Not Cover Every Ticker

Google Finance data can be delayed up to 20 minutes depending on the exchange. Illiquid OTC tickers, specialized foreign listings, or newly listed securities may lack historical QUERY endpoints.

Google Limits Lookups (Sized for 30 Tickers)

Google limits lookups to prevent server strain. The template is intentionally sized for 30 tickers to ensure fast recalculations and prevent Google's formula throttling limits.

Needs Price History & Works Only in Google Sheets

Securities need enough price history for the MA period (e.g. 200 trading days for the 200-DMA). Furthermore, because this template uses GOOGLEFINANCE and QUERY formulas, it works only in Google Sheets (not Excel).

Common Questions

Frequently Asked Questions

Everything you need to know about setting up and using the Google Sheets screener.

Is this Google Sheets stock screener completely free? ▾

Yes. This template is 100% free to copy and use. It runs entirely on native Google Sheets formulas with no subscription, paid add-on, or hidden fees.

Do I need to install any Google Sheets add-ons or authorize third-party scripts? ▾

No. The entire engine is powered purely by built-in GOOGLEFINANCE, QUERY, AVERAGE, and INDEX/MATCH formulas. There are zero Apps Scripts and zero third-party permissions required.

Can I screen more than 30 stocks or ETFs at a time? ▾

The template is intentionally configured for 30 tickers to respect Google's internal query limits and prevent formula throttling. You can add more rows, but recalculation times may increase.

Why does GOOGLEFINANCE use calendar days instead of trading days? ▾

GOOGLEFINANCE date math (such as TODAY()-46) operates on calendar days. Because weekends and market holidays are skipped, a larger calendar window is required so that the QUERY statement can retrieve the exact number of trading days.

How often do the moving averages and prices update? ▾

Google Finance quotes refresh automatically in Google Sheets roughly every 15 to 20 minutes when the sheet is open. Daily closes update once daily market sessions complete and settle.

Can I use this for non-US stocks or international exchanges? ▾

Yes, provided Google Finance supports the exchange prefix (for example, TSE:SHOP for Toronto or LON:VOD for London). Enter the exchange prefix directly in column A.

What happens if a stock was recently IPO'd or has less than 200 days of history? ▾

If a stock has fewer than 200 trading days, the 200-day moving average formula safely handles missing rows, but conditions referencing it will evaluate to false until sufficient price history accumulates.

Can I create my own custom technical conditions? ▾

Yes. The template includes dedicated spare condition slots (C5 and C6) on both the Conditions and Engine sheets where you can define custom logic such as moving average bands, spread thresholds, or custom ranges.

Important Financial Disclaimer

For educational purposes only. This tool is not investment, tax or legal advice and does not recommend any security. Technical signals do not guarantee future results. Data is provided by Google Finance and may be delayed or inaccurate. Do your own research or consult a licensed professional.

Read our full editorial disclaimer →

Start Screening in Google Sheets Today

Open a clean, pre-built copy in your Google Drive and begin screening your 30-ticker watchlist immediately.

Make a Free Copy