Dividend Tracker Spreadsheet: Free Template With Live Dividend Data

A dividend tracker spreadsheet is a sheet with one row per holding and columns for shares, cost basis, annual dividend, income, yield, yield on cost and the next ex-dividend and pay dates. The problem, as of November 2026, is data: Google's GOOGLEFINANCE function returns prices but no dividend, yield or ex-dividend figures, which is why most templates rely on numbers typed in by hand and quietly go stale. The free SmarterDividends API fixes that with one IMPORTDATA formula per ticker, returning the annual dividend, forward yield, next ex-date and pay date, payout ratio, safety grade and growth streak in a single row, with no API key.

This guide gives you the full column layout, every formula, a monthly income view and a free starter template.

Where the data can come from

The column layout

Start with one tab called Holdings. Row 1 holds the headers, and each holding gets its own row from row 2 down. The formulas below are for row 2; copy them down.

Format G and H as percentages (Format, Number, Percent). For portfolio totals, put these anywhere below or on a summary tab:

• Total annual income: =SUM(F2:F200)
• Average monthly income: =SUM(F2:F200)/12
• Portfolio yield: =SUM(F2:F200)/SUMPRODUCT(B2:B200,D2:D200)
• Portfolio yield on cost: =SUM(F2:F200)/SUMPRODUCT(B2:B200,C2:C200)

If you add to a position at a different price, use your average cost per share in column C. Your broker's cost basis page shows it.

Why most dividend spreadsheets go stale

Google's own documentation for GOOGLEFINANCE lists the attributes it can return: price, priceopen, high, low, volume, marketcap, tradetime, datadelay, volumeavg, pe, eps, high52, low52, change, beta, changepct, closeyest, shares and currency, plus open, close, high, low and volume for history. There is no dividend, yield or ex-dividend attribute. Google also notes quotes may be delayed up to 20 minutes and are not for trading purposes.

So columns E, I, J, K and L in nearly every free template are typed by hand. Companies raise their dividends once a year, ex-dates move every quarter, and a cut can happen at any board meeting. A sheet that was right in January is wrong by spring, and nothing in it tells you so.

Pull live dividend data with IMPORTDATA

The free SmarterDividends dividend API has a spreadsheet format. Add ?format=csv to a stock lookup, and Google Sheets can read it with IMPORTDATA:

=IMPORTDATA("https://smarterdividends.com/api/v1/stocks/KO?format=csv&header=0")

That returns one row for Coca-Cola. With a ticker in A2, build the URL from the cell instead:

=IMPORTDATA("https://smarterdividends.com/api/v1/stocks/"&A2&"?format=csv&header=0")

&header=0 leaves out the header row, so each formula fills exactly one row. Leave it off if you want the column names on top. The row's columns come back in this order:

There's no API key and no sign-up. The data is free to use with attribution, which means a link to smarterdividends.com somewhere in your sheet (a note in the header row is fine). It covers U.S.-listed stocks, ETFs and funds, and tickers are not case sensitive. For share classes, use a dash, as in BRK-B. The full reference is in the API docs.

Formatting tip: select the forward_yield and payout_ratio columns and format them as percentages, and 0.0221 displays as 2.21%.

A layout that uses it

The quickest live tracker puts your own inputs on the left and lets IMPORTDATA fill everything to the right.

The formula in D2 spills across D through Q: D is symbol, E name, F price, G forward yield, H annual dividend, I frequency, J next ex-date, K next pay date, L payout ratio, M growth streak, N safety grade, O fair value, P valuation and Q as-of date. Type the headers for D1 to Q1 yourself, or put the version without &header=0 in a spare row once to copy them.

A few things keep it tidy:

• Leave D through Q empty apart from the formula in column D. If anything sits in a cell the import needs, Sheets shows a #REF! error instead of the data.
• The IF(A2="","",...) wrapper stops empty rows from calling the API, so you can copy the formula down 100 rows ahead of time.
• A misspelled or unsupported ticker returns an error in that row only. Fix the ticker and the row fills in.

Your totals then become =SUM(R2:R200) for annual income, =SUM(R2:R200)/SUM(T2:T200) for portfolio yield, and =SUM(R2:R200)/SUMPRODUCT(B2:B200,C2:C200) for yield on cost.

Keeping the 12-column layout instead

If you prefer the compact layout from the first table, put the imports on a second tab. On a tab named Data, enter =Holdings!A2 in A2 and the IMPORTDATA formula (pointing at Data!A2) in B2, so the API row spills across B to O. Then pull fields back into Holdings with VLOOKUP, where the number is the column's position in the list above:

• Price (D): =VLOOKUP($A2,Data!$B:$O,3,FALSE)
• Annual dividend per share (E): =VLOOKUP($A2,Data!$B:$O,5,FALSE)
• Next ex-date (I): =VLOOKUP($A2,Data!$B:$O,7,FALSE)
• Next pay date (J): =VLOOKUP($A2,Data!$B:$O,8,FALSE)
• Safety grade (K): =VLOOKUP($A2,Data!$B:$O,11,FALSE)
• Growth streak (L): =VLOOKUP($A2,Data!$B:$O,10,FALSE)

Still one import per ticker, and your main tab stays readable.

A monthly income view

Annual income is the headline, but most people plan in months. Using the live layout above, add a helper column for payments per year, then twelve month columns.

In U2:

=SWITCH(I2,"monthly",12,"quarterly",4,"semiannual",2,"semi-annual",2,"annual",1,"weekly",52,0)

Put the numbers 1 to 12 in V1 through AG1 (1 is January). Then in V2:

=IFERROR(IF($U2>=12,$R2/12,IF(AND($K2<>"",MOD(V$1-MONTH($K2),12/$U2)=0),$R2/$U2,0)),0)

Copy V2 across to AG2 and down your rows. Each cell shows the income that holding should pay in that month. Monthly and weekly payers spread evenly across the year. Quarterly, semiannual and annual payers land in the months that line up with their next pay date. Total each month with =SUM(V2:V200) at the bottom, and a column chart of that row shows your thin months at a glance.

Treat it as an estimate. It assumes the current dividend and schedule continue, and companies do shift pay dates. When a holding has no declared pay date yet (Realty Income, a monthly payer, showed none in our data on November 6, 2026), monthly payers still fill in, but quarterly payers show zero until the next date is announced. If you want to fill gaps in the calendar, monthly dividend stocks are the usual answer, and the dividend income calculator shows what a monthly target takes.

In Excel

Excel reads the same URL through Power Query. Go to Data, From Web, paste https://smarterdividends.com/api/v1/stocks/KO?format=csv, and load it. Excel creates a small table for that ticker. Right-click it and choose Refresh to update, or set a refresh interval in the query's properties.

Power Query creates one query per URL, so this suits a short list of holdings. For a long list, Google Sheets with the IMPORTDATA layout is less work. The income, yield and yield-on-cost formulas above work the same way in Excel.

Limits to know

• IMPORTDATA refreshes about once an hour. That suits a dividend tracker, since dividends change slowly, but don't use the sheet for intraday prices.
• Google limits the number of import calls per sheet. If a big sheet shows "Loading..." or errors on some rows, split holdings across two spreadsheets or use the Data-tab layout so each ticker is imported once.
• Every row carries an as_of date. Check it if something looks off.
• The safety grade and fair value are our estimates, not guarantees. We publish how the grades did against every recorded cut in our safety track record.

Download the free template

Our dividend tracker template is a CSV you can import into Google Sheets (File, Import, Upload) or open in Excel. It has the 12 columns from the first table and three example rows (Coca-Cola, Johnson & Johnson and Realty Income) filled with our data as of November 6, 2026, using made-up share counts and cost basis.

The example rows hold plain values, so after importing, replace the Annual Income, Yield and Yield on Cost cells with the formulas from the column table, and swap in the IMPORTDATA or VLOOKUP formulas if you want the dividend columns to stay current. Delete the example rows, add your own tickers, and you have a working tracker.

When a spreadsheet stops being enough

A sheet does income, yield and dates well. It struggles with three things.

It can't warn you. Nothing in a spreadsheet emails you the morning a company cuts its dividend, and you only find out the next time you open it and notice the number changed. Our weekly dividend changes page lists every raise and cut if you want to check by hand.

It needs you to look. Safety grades sit in a column, but reading 40 of them every month is a chore. SmarterDividends data shows that only 8% of graded dividend stocks are rated D or F, yet 52% of the cuts recorded since April 2026 came from companies graded D or F beforehand (as of November 6, 2026). An app that flags those few holdings for you does the looking.

It doesn't sync with your broker. Every trade means another manual edit.

When those start to bite, an app is the next step. SmarterDividends free tracks up to 10 holdings; Pro ($99 a year as of November 6, 2026) adds unlimited holdings, a safety grade on every holding and email alerts on raises and cuts, though holdings are pasted or uploaded by CSV rather than synced. If automatic broker sync matters more, Snowball Analytics does that on its paid plans. We compare the methods in best way to track dividends, the free plans in best free dividend tracker, and every app side by side on our dividend tracker comparison.

How we wrote this guide

The column order, formula syntax and example values come from our own API on November 6, 2026. The GOOGLEFINANCE attribute list comes from Google's documentation for the function, checked the same day. SmarterDividends is our product, and the API is ours, so we have an interest in you using it. The layout works just as well with dividends typed in by hand.

Quick decision guide

• If you own a few holdings and enjoy tinkering, build the live layout above.
• If you just want a starting point, download the template.
• If you want an alert when a dividend is cut, use an app with alerts instead of a sheet.
• If you'd like an AI assistant to read the same data, connect our free MCP server.

For informational purposes only, not investment advice.