Build a Dividend Income Tracker With SQL

A dividend income tracker answers two questions at once: what your holdings pay per year, and when each payment lands. You can build one with SQL and curl, no account and no key, in about thirty lines of query text: paste a holdings list, join it to the dividends each ticker has declared, then read the income out month by month with a twelve month total on top. The panels on this page run that pipeline against a six position sample list, so the output is visible before you type anything.

One word needs pinning down first. A projection here is arithmetic over dividends that companies have already declared, carried forward at the current rate. That is a run rate rather than a forecast, and any board meeting can change the rate it rests on.

What goes into a dividend income tracker

Two inputs, one join. Your side is a holdings file: ticker, share count, and the price you paid per share. The market side is the dividend record for each of those tickers: the cash amount per share, the frequency (the number of payments a year), and the pay date, which is the day the cash settles in the account, usually two to five weeks after the ex dividend date.

Start with the market side, since it is the half nobody controls.

WITH holdings AS
(
    SELECT
        tupleElement(h, 1) AS ticker,
        tupleElement(h, 2) AS shares,
        tupleElement(h, 3) AS cost_per_share
    FROM
    (
        SELECT arrayJoin([
            ('KO',   200.0,  54.10),
            ('PG',    60.0, 141.25),
            ('JNJ',   50.0, 155.40),
            ('MSFT',  40.0, 268.75),
            ('XOM',   80.0,  96.20),
            ('O',    150.0,  61.85)
        ]) AS h
    )
),
declared AS
(
    SELECT
        ticker,
        argMax(cash_amount, ex_dividend_date)              AS cash_per_payment,
        argMax(frequency, ex_dividend_date)                AS pays_per_year,
        formatDateTime(max(ex_dividend_date), '%b %e, %Y') AS last_ex_label
    FROM global_markets.stocks_dividends
    WHERE ticker IN ('KO', 'PG', 'JNJ', 'MSFT', 'XOM', 'O')
      AND currency = 'USD'
      AND frequency IN (1, 2, 4, 12)
      AND ex_dividend_date >= today() - 400
      AND ex_dividend_date <= today() + 120
    GROUP BY ticker
)
SELECT
    h.ticker                                                    AS ticker,
    round(toFloat64(d.cash_per_payment), 4)                     AS cash_per_payment,
    round(toFloat64(d.cash_per_payment) * d.pays_per_year, 2)   AS annual_per_share,
    toUInt16(d.pays_per_year)                                   AS payment_count_per_year,
    d.last_ex_label                                             AS last_declared
FROM holdings AS h
INNER JOIN declared AS d ON d.ticker = h.ticker
ORDER BY payment_count_per_year DESC, annual_per_share DESC

All 6 sample tickers come back with a declared rate. The top row, O, runs on the most frequent schedule of the six at 12 payments a year of $0.2715 a share, which annualises to $3.26. The thinnest schedule on the list pays 4 times a year. That spread of frequencies is what gives the next panel its shape.

Multiply the per share column by your share count and the tracker is already half built. The last column is the receipt: the ex dividend date of the payment each rate was read from.

How do I project dividend income by month?

Annual figures hide the cash flow. A quarterly payer drops four lumps a year, and the months in between pay nothing at all. To get a calendar, take every pay date from the trailing twelve months, carry it forward one year, and price it at the latest declared rate.

WITH holdings AS
(
    SELECT
        tupleElement(h, 1) AS ticker,
        tupleElement(h, 2) AS shares
    FROM
    (
        SELECT arrayJoin([
            ('KO',   200.0),
            ('PG',    60.0),
            ('JNJ',   50.0),
            ('MSFT',  40.0),
            ('XOM',   80.0),
            ('O',    150.0)
        ]) AS h
    )
),
declared AS
(
    SELECT
        ticker,
        argMax(cash_amount, ex_dividend_date) AS cash_per_payment
    FROM global_markets.stocks_dividends
    WHERE ticker IN ('KO', 'PG', 'JNJ', 'MSFT', 'XOM', 'O')
      AND currency = 'USD'
      AND frequency IN (1, 2, 4, 12)
      AND ex_dividend_date >= today() - 400
      AND ex_dividend_date <= today() + 120
    GROUP BY ticker
),
paid AS
(
    SELECT
        ticker,
        pay_date
    FROM global_markets.stocks_dividends
    WHERE ticker IN ('KO', 'PG', 'JNJ', 'MSFT', 'XOM', 'O')
      AND frequency IN (1, 2, 4, 12)
      AND pay_date >= today() - 365
      AND pay_date <  today()
    GROUP BY ticker, pay_date
)
SELECT
    formatDateTime(toStartOfMonth(addYears(p.pay_date, 1)), '%Y-%m') AS month,
    formatDateTime(toStartOfMonth(addYears(p.pay_date, 1)), '%b %Y') AS month_name,
    round(sum(toFloat64(d.cash_per_payment) * h.shares), 2)          AS projected_income,
    count()                                                          AS payment_count
FROM paid AS p
INNER JOIN holdings AS h ON h.ticker = p.ticker
INNER JOIN declared AS d ON d.ticker = p.ticker
GROUP BY month, month_name
ORDER BY month

The strip covers 13 months. It opens at $40.72 across 1 payments in Oct 2026, and it ends at $106 in Oct 2027. Read the line and the lumpiness is plain: quarterly payers cluster in the months after a quarter closes, leaving thin months between crowded ones. Anyone budgeting around dividend cash wants this view rather than the annual one.

The near term dates that are already declared, rather than carried forward, sit in upcoming dividend payment dates, and the dates that decide who gets paid at all are in the ex dividend calendar.

How do I total the next twelve months of dividend income?

Sum the per position annual figures. The panel below does it one row at a time, so the holdings carrying the income are visible rather than averaged away.

WITH holdings AS
(
    SELECT
        tupleElement(h, 1) AS ticker,
        tupleElement(h, 2) AS shares
    FROM
    (
        SELECT arrayJoin([
            ('KO',   200.0),
            ('PG',    60.0),
            ('JNJ',   50.0),
            ('MSFT',  40.0),
            ('XOM',   80.0),
            ('O',    150.0)
        ]) AS h
    )
),
declared AS
(
    SELECT
        ticker,
        argMax(cash_amount, ex_dividend_date) AS cash_per_payment,
        argMax(frequency, ex_dividend_date)   AS pays_per_year
    FROM global_markets.stocks_dividends
    WHERE ticker IN ('KO', 'PG', 'JNJ', 'MSFT', 'XOM', 'O')
      AND currency = 'USD'
      AND frequency IN (1, 2, 4, 12)
      AND ex_dividend_date >= today() - 400
      AND ex_dividend_date <= today() + 120
    GROUP BY ticker
)
SELECT
    ticker,
    annual_income,
    round(sum(annual_income) OVER (ORDER BY annual_income DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 2) AS running_total
FROM
(
    SELECT
        h.ticker AS ticker,
        round(toFloat64(d.cash_per_payment) * d.pays_per_year * h.shares, 2) AS annual_income
    FROM holdings AS h
    INNER JOIN declared AS d ON d.ticker = h.ticker
)
ORDER BY annual_income DESC

The running total column adds each row to the ones above it, so the bottom row is the whole list: $1928.34 over twelve months at current declared rates. The largest single line is O at $488.7, which is share count at work as much as rate. A large position in a modest yielder outweighs a small position in a richer one, whatever the headline yields look like side by side.

How do I calculate yield on cost?

Yield on cost divides the annual dividend per share by the price you paid, not by today's quote. It is the figure the subscription trackers put on the dashboard, and it is one division once the rate is in hand. The cost figures in the sample holdings are placeholders. Swap in your own fills and this is the panel that moves most.

WITH holdings AS
(
    SELECT
        tupleElement(h, 1) AS ticker,
        tupleElement(h, 2) AS cost_per_share
    FROM
    (
        SELECT arrayJoin([
            ('KO',    54.10),
            ('PG',   141.25),
            ('JNJ',  155.40),
            ('MSFT', 268.75),
            ('XOM',   96.20),
            ('O',     61.85)
        ]) AS h
    )
),
declared AS
(
    SELECT
        ticker,
        argMax(cash_amount, ex_dividend_date) AS cash_per_payment,
        argMax(frequency, ex_dividend_date)   AS pays_per_year
    FROM global_markets.stocks_dividends
    WHERE ticker IN ('KO', 'PG', 'JNJ', 'MSFT', 'XOM', 'O')
      AND currency = 'USD'
      AND frequency IN (1, 2, 4, 12)
      AND ex_dividend_date >= today() - 400
      AND ex_dividend_date <= today() + 120
    GROUP BY ticker
),
last_px AS
(
    SELECT
        ticker,
        argMax(toFloat64(close), date) AS last_close
    FROM global_markets.stocks_daily_aggs
    WHERE ticker IN ('KO', 'PG', 'JNJ', 'MSFT', 'XOM', 'O')
      AND date >= today() - 45
    GROUP BY ticker
)
SELECT
    h.ticker AS ticker,
    round(toFloat64(d.cash_per_payment) * d.pays_per_year / h.cost_per_share * 100, 2) AS yield_on_cost_pct,
    round(toFloat64(d.cash_per_payment) * d.pays_per_year / p.last_close * 100, 2)     AS current_yield_pct
FROM holdings AS h
INNER JOIN declared AS d ON d.ticker = h.ticker
INNER JOIN last_px  AS p ON p.ticker = h.ticker
ORDER BY yield_on_cost_pct DESC

The top row shows 5.27% on cost against 6.11% on the latest close for the same position. The two columns answer different questions. Yield on cost is frozen at your entry price and moves only when the company changes its rate. Current yield moves with the quote. For one number across the whole portfolio instead of a column of them, see portfolio weighted dividend yield.

What the same holdings paid over five years

A run rate is a snapshot. The harder test of a dividend list is what it actually paid with the share count held still, which is one more group by on the same join.

WITH holdings AS
(
    SELECT
        tupleElement(h, 1) AS ticker,
        tupleElement(h, 2) AS shares
    FROM
    (
        SELECT arrayJoin([
            ('KO',   200.0),
            ('PG',    60.0),
            ('JNJ',   50.0),
            ('MSFT',  40.0),
            ('XOM',   80.0),
            ('O',    150.0)
        ]) AS h
    )
)
SELECT
    toYear(p.pay_date)                                 AS year,
    round(sum(toFloat64(p.cash_amount) * h.shares), 2) AS portfolio_income,
    count()                                            AS payment_count
FROM
(
    SELECT
        ticker,
        pay_date,
        max(cash_amount) AS cash_amount
    FROM global_markets.stocks_dividends
    WHERE ticker IN ('KO', 'PG', 'JNJ', 'MSFT', 'XOM', 'O')
      AND frequency IN (1, 2, 4, 12)
      AND pay_date >= toDate('2021-01-01')
      AND pay_date <  toDate('2026-01-01')
    GROUP BY ticker, pay_date
) AS p
INNER JOIN holdings AS h ON h.ticker = p.ticker
GROUP BY year
ORDER BY year

In 2021 the sample list collected $1545.66 across 32 payments. In 2025, with identical share counts and nothing reinvested, it collected $1854.16. The gap between those two ends comes from rate changes alone, plus the occasional payment that lands on the other side of a January. Names with long records of raising the rate are the subject of dividend growth champions.

Build a dividend income tracker with curl and jq

1. Save your positions as holdings.csv with a header row ticker,shares,cost_per_share and one line per holding, such as KO,200,54.10 with your own fill price.
2. Turn the file into the SQL list the panels use with one awk line: awk -F, 'NR>1 {printf "%s(\047%s\047,%s,%s)", sep, $1, $2, $3; sep=","}' holdings.csv
3. Open any panel above, expand the SQL underneath it, and paste your list in place of the sample array at the top. Save the statement as income.sql.
4. Send it with curl, using the endpoint and body form from the free SQL API guide: curl -s "$SQL_API" --data-binary @income.sql > income.json
5. Shape the reply into a spreadsheet column with jq: jq -r '.rows[] | [.month_name, .projected_income] | @csv' income.json > income.csv. The response envelope and the key holding its rows are covered in the curl and jq walkthrough.
6. Put steps 4 and 5 in a two line shell script and give it a weekly slot with crontab -e, for example 0 7 * * 1 for Monday mornings.

That is the whole tracker. The holdings file is the only part you maintain, and it changes when you trade.

What a DIY tracker does not do

The paid trackers charge for the parts that are not arithmetic. Nothing here pushes a notification to your phone when a payment lands, and nothing here logs into a broker to read share counts, so the holdings file is yours to keep current after every trade. What the pipeline gives you instead is a file on your own disk and a refresh schedule you set, with the full arithmetic sitting in the SQL under every panel. A text file and a cron line also outlive a vendor that shuts the product down, which is not true of a dashboard.

FAQ

How do I calculate dividend income from a holdings list?

Multiply each position's share count by the latest declared dividend per share, then by the number of payments a year. Summing that column gives a twelve month run rate. Grouping the same join by the month of each pay date gives the calendar version.

What is yield on cost?

Yield on cost is the annual dividend per share divided by the price you originally paid, written as a percent. It stays fixed at your entry price while current yield moves with the quote. The two columns rarely agree, and both are shown in the panel above.

Can I build a dividend tracker without an API key?

Yes. Every panel on this page is a single read only SQL statement sent to a public endpoint with curl. The only tools involved are curl and jq.

Is projected dividend income guaranteed income?

No. A projection is arithmetic over dividends already declared, carried forward at the current rate. Boards review the rate on their own calendar and any of those meetings can change it; the record of past changes is in dividend cuts.

How often do the numbers change?

Declared rates change when a company announces, which for most quarterly payers is once a year. The next quarter's pay date appears in the record weeks ahead of the cash itself. A weekly run keeps a tracker current with room to spare.

Every panel on this page carries the SQL that produced it. Copy one, paste your own holdings over the sample array, and the numbers come back as yours. The same questions can be asked in plain English on the Strasmore terminal.