How to track dividends in Google Sheets

Why a spreadsheet is the right place to start

Most dividend investors start in a spreadsheet, and most of them should. It costs nothing, nobody gets your brokerage password, and the layout bends to however you actually think about your portfolio. No app gives you that last part. If a sheet is working for you, this page isn't trying to talk you out of it — the second half is about recognising the point where it stops working, which is a different question.

The build: nine columns

Open a blank sheet and put these headers in row 1. Everything else is derived from the first three, so those are the only columns you type into.

The formulas, row 2 down

• D2 — live price: =IFERROR(GOOGLEFINANCE($A2,"price"),"")
• E2 — market value: =IFERROR($B2*$D2,"")
• G2 — annual income: =IFERROR($B2*$F2,"")
• H2 — yield on market: =IFERROR($G2/$E2,"") — format as a percentage
• I2 — yield on cost: =IFERROR($G2/($B2*$C2),"") — format as a percentage

Wrap every GOOGLEFINANCE call in IFERROR. The function returns #N/A intermittently — mid-recalculation, on thinly traded tickers, on some non-US listings — and one bare error cell poisons every total that references it. Prices are also delayed (Google documents up to 20 minutes), which is irrelevant for dividend tracking and worth knowing anyway.

Totals, in a spare block of cells

• Portfolio value: =SUM(E2:E)
• Annual dividend income: =SUM(G2:G)
• Average monthly income: =SUM(G2:G)/12
• Portfolio yield: =IFERROR(SUM(G2:G)/SUM(E2:E),"")

The open-ended ranges (E2:E rather than E2:E40) matter: they keep working when you add a holding at the bottom instead of silently excluding it. That silent exclusion is the most common bug in home-made portfolio sheets.

The part Google doesn't do: the dividends

Here's the thing nobody tells you up front. GOOGLEFINANCE is excellent at prices and has no dependable dividend data. There's a "yieldpct" attribute that works for some US listings and fails or returns nothing for plenty of others, and no attribute at all for annual dividend per share. That's why column F above is a column you type into.

The usual workarounds, and why each one bites:

• IMPORTHTML / IMPORTXML scraping a finance site. Works beautifully until the site changes its markup, at which point your income figures turn into #N/A — or, worse, quietly import the wrong column. Scrapes break on someone else's deploy schedule, not yours.
• Apps Script pulling an API. Genuinely solid, and now you maintain a small software project with an API key in it.
• Typing it in. Unglamorous and the most robust option. Dividend-per-share changes a few times a year per holding; a quarterly fifteen-minute pass over column F keeps the whole sheet honest.

Either way, hand-maintained numbers are the real cost of a dividend spreadsheet — not the setup. Know that going in and the sheet will serve you for years.

If you want yield on cost to mean something

Keep average cost per share in column C rather than total cost paid, and update it when you buy more. Yield on cost is the number that makes dividend growth visible — a position bought at a 3% yield that now pays 5.2% on your original cost is the entire thesis in one cell — and it's the first figure to break if column C drifts.

The payment calendar is where sheets get painful

Annual income is easy arithmetic. When the money arrives is not: it needs an ex-date and a pay-date per holding per quarter, each on its own schedule, none of it available from GOOGLEFINANCE. Building a real monthly calendar in a spreadsheet means roughly four hand-entered dates per holding per year — 80 cells a year for a 20-position portfolio, each of which goes stale silently.

Most people try this once, maintain it for two quarters, and then stop. That's not a discipline failure; it's the task being a bad fit for the tool.

When a spreadsheet stops scaling

Pre-commit to the tripwires now, while you're not annoyed, so you're not deciding this in a moment of frustration:

• Column F upkeep slips two quarters in a row. Stale dividend data means every total on the sheet is decorative.
• You want the monthly calendar and keep not building it. Lumpy quarterly income is the thing most worth seeing, and it's precisely what a sheet is worst at.
• You've started wanting projections. Reinvestment compounding, dividend growth, contributions — modelling that properly in cells is a weekend, and the result is hard to trust because the assumptions hide inside formulas.
• You're fixing #N/A more than once a month. Maintenance has become the hobby.
• You want it on your phone without pinch-zooming. A 9-column sheet on a 390px screen is nobody's idea of a dashboard.
• Past ~25 holdings. Soft threshold, consistent pattern: that's roughly where hand-maintenance stops being a quarterly chore and starts being a monthly one.

None of those fire for a tidy ten-holding portfolio. If they're all firing at once, you already knew.

Moving on without giving anything up

The reason people stay in a spreadsheet past the point of usefulness is reasonable: every tracker wants an account, most want a brokerage login, and most want a monthly subscription for the privilege. That's a bad trade for a nicer table.

It isn't the only trade available. Incomebrook's tracker keeps the two properties that made the spreadsheet good — no account and no bank linking, with your holdings stored in your own browser rather than on a server — and adds the two things sheets are worst at: a monthly income calendar that maintains its own dates, and projections where the assumptions are visible inputs you can change. Unlimited holdings on the free tier; Pro is a single $29 payment if you want multiple portfolios, CSV import, and what-if scenarios.

Or run both. Export a CSV of tickers, shares, and average cost from your sheet, import it once, and keep the spreadsheet for the custom analysis no app will ever do your way. The sheet stays your archive; the tracker does the upkeep. Try the snowball calculator first if you want to see the projection maths before moving anything — it needs no sign-up either.