▶ Full Post Text
Most screeners online let you filter on the same 15 metrics everyone else filters on. If your process is "P/E < 15, P/B < 1.5, ROE > 15%," you're going to get the exact same list of stocks as every other person running that screen. The edge in a custom screener isn't speed, it's that you can encode *your* checklist. Mine has things like "5 year median FCF margin," "net debt to normalized EBIT," and "buyback yield net of dilution," none of which most screeners let you screen on cleanly.
Here's how to build one from scratch in Excel or Google Sheets including the full walkthrough with formulas.
**Step 1: Decide what you're actually screening for**
Before touching a spreadsheet, write your checklist on paper. Here are some of the ones I use (although of course it depends on the industry and sector):
Quality filters
* ROIC > 12% (5 yr avg)
* FCF margin > 8% (5 yr median)
* Net debt / EBIT < 3x
* Gross margin stable or expanding over 5 yrs
Valuation filters
* EV/EBIT < 12
* FCF yield > 6%
* P/E < 20
Capital allocation
* Share count flat or declining over 5 yrs
* Buyback yield + dividend yield > 3%
Your list will look different. The point is, write it before you build the sheet, otherwise you'll won't know how to structure the list properly and what you are looking for.
**Step 2: Get a ticker universe**
You need a starting list of companies to run filters against. A few ways to do this:
* Paste in S&P 500 constituents from Wikipedia
* Pull the holdings of a sector ETF you care about
* Use your existing watchlist
* Use a pre-filter to get a manageable starting universe
I do the last one. The Wisesheets add-in has a Get List feature inside the screener tab that lets you pull a list of companies, ETFs, and funds matching basic criteria, and it dumps them straight into your sheet. The flow is:
1. Open the Wisesheets add-in and go to the Screener tab, then click Get List
2. Set filters: market cap range, sector, industry, exchange, dividend yield, etc
3. Click Get Data
It returns the matching tickers along with company name, sector, industry, beta, price, and so on. This is useful because most online screeners (Finviz, Stock Rover, Zacks) gate Excel export behind paid tiers. You can run the screen for free but if you want the list in a spreadsheet so you can actually work with it, that's $30 to $60 a month. Getting the universe directly into your sheet at the start saves you the manual copy paste step entirely.
For the rest of this post I'll assume you have 200 to 500 tickers in column A starting from row 2.
**Step 3: Pull the data**
You need an add-in that can pull statement line items and historical data on demand. GOOGLEFINANCE handles price and a few basics but can't touch the income statement, balance sheet, or cash flow statement, which means it's a non-starter for any real fundamental screen. Excel's built-in Stocks data type has the same limitation.
I use Wisesheets (full disclosure, I work on it). The two main formulas you'll need are:
* `=WISE(ticker, parameter, period, year, quarter)` for fundamentals and ratios
* `=WISEPRICE(ticker, parameter)` for live price data and current valuation metrics
Other add-ins exist (Stock Connector, Finsheet, etc.) and the logic in the rest of this post translates, the formula syntax is just different.
**Step 4: Build the data columns**
In row 1, label your columns. In row 2 onward, you'll pull data for every ticker at once.
The thing that makes Wisesheets actually scale here is that you can pass a range of tickers to `=WISE()` or `=WISEPRICE()` and it returns data for all of them in one call. So instead of writing a formula in row 2 and dragging it down 500 rows (which fires 500 separate API calls and takes longer to refresh), you write *one* formula at the top of each column and it spills the entire result down the column.
Minimal layout:
|A|B|C|D|E|F|G|H|
|:-|:-|:-|:-|:-|:-|:-|:-|
|Ticker|Mkt Cap|P/E|EV/EBIT|FCF Yield|ROIC 5y|FCF Margin 5y|Net Debt/EBIT|
Assuming tickers are in A2:A501, the formulas go in row 2 and spill down automatically:
* Market cap: `=WISEPRICE(A2:A501, "market cap")`
* P/E: `=WISEPRICE(A2:A501, "pe")`
* Enterprise value: `=WISE(A2:A501, "enterprise value", "TTM")`
* EBIT: `=WISE(A2:A501, "operating income", "ttm")` (then divide EV by EBIT in a separate column with a normal formula)
* Free cash flow: `=WISE(A2:A501, "free cash flow", "ttm")`
* FCF yield: standard division on the two columns above, dragged down
* Total debt: `=WISE(A2:A501, "total debt", "ttm")`
* Cash and equivalents: `=WISE(A2:A501, "cash and equivalents", "ttm")`
* Net debt / EBIT: standard arithmetic on the columns above
For the multi-year averages, pass the ticker range *and* a year range:
* ROIC 5y avg: pull `=WISE(A2:A501, "roic", {"ly", "ly-1", "ly-2","ly-3","ly-4")` into a block of 5 columns, then `=AVERAGE()` across each row
* FCF margin 5y median: same pattern with `=WISE(A2:A501, "free cash flow margin", {"ly", "ly-1", "ly-2","ly-3","ly-4"))` then `=MEDIAN()` across the row
So you end up with a handful of "data block" formulas at the top of the sheet and the rest is normal spreadsheet arithmetic. Refreshing 500 tickers becomes a handful of calls instead of thousands, and you can rebuild the whole screen by changing the ticker range in one place.
**Step 5: Build the filter logic**
Add a column called "Pass" at the far right. Use nested AND:
=IF(AND(
C2 < 20,
D2 < 12,
E2 > 0.06,
F2 > 0.12,
G2 > 0.08,
H2 < 3
), "PASS", "")
Filter the sheet on that column. Anything that says PASS is a candidate.
A better version: score each criterion 0 or 1 and sum them, so a stock that fails one filter narrowly still surfaces:
=(C2<20)*1 + (D2<12)*1 + (E2>0.06)*1 + (F2>0.12)*1 + (G2>0.08)*1 + (H2<3)*1
Sort descending. Top of the list is your shortlist. This is more useful in practice because it surfaces "almost passes" you'd otherwise miss, and those are often the more interesting names because they haven't already been screened to death by everyone else running the same six rules.
**Step 6: Add red flag columns**
The above is the offensive screen. You also want defensive flags that don't disqualify a stock but warn you. Here are some examples:
* Share count growth > 2% per year (dilution flag)
* Goodwill > 30% of total assets (acquisition driven flag)
* Cash conversion (FCF/NI) < 0.8 over 5 years (earnings quality flag)
* Interest coverage < 4x (leverage flag)
Each gets its own column, each returns a flag string ("dilution", "low cash conv", etc.), and you concat them at the end so each row has a list of yellow flags next to its score.
**Step 7: Refresh discipline**
Once you have your data, you can refresh any time by pressing the refresh data button. Most of the time, the only thing that will change is the price-driven data; the rest won’t move until the release of a new yearly or quarterly statement, depending on how you screen.
**A few things I'd warn you about:**
* GAAP earnings are noisy. Median over 5 years > latest 12 months for almost everything.
* ROIC definitions vary between data sources. Pick one, stick with it, don't compare across vendors.
* Banks and insurance companies break almost every standard ratio. Screen them separately or exclude them.
* "Net cash" companies will show negative net debt / EBIT, which messes with the < 3 filter. Use `=IF(net_debt < 0, 0, net_debt / ebit)` to handle it.
* A screen produces *candidates*, not buys. Everything that passes still has to survive an actual read of the 10K and thorough analysis.
I'd love to hear how you currently go about screening and by the way I am thinking of adding a screener feature into Claude and ChatGPT via a connector. Any feedback is appreciated.