Building a Dividend Reinvestment Tracker in Python Against a Brokerage API

Your broker’s app shows a position size and a total return percentage. Neither answers the question a dividend-growth investor actually asks: how many of these shares did you buy with cash, how many did the dividends buy for you, and what is the cost basis of each reinvested sliver? Brokers track that internally — they have to, for the 1099 — but most surface it only as a year-end PDF, and almost none expose per-lot DRIP history in a form you can chart.

So you write your own. We built one against a REST brokerage API over a few evenings, and the interesting problems were not the ones we expected. Pulling JSON is trivial. Making the same pull twice not corrupt your history, and making a ticker symbol survive a spinoff, is where the work is.

The ledger is the product; the dashboard is a view

The first instinct is a positions table with a shares column you keep updating. Resist it. The broker already owns that number, and any time your copy and theirs disagree you have no way to tell which is wrong or when the drift started.

Store an append-only event log instead, and derive everything by folding it:

# schema.sql — one table you actually write to
CREATE TABLE events (
  broker_txn_id TEXT PRIMARY KEY,   -- idempotency key, from the broker
  instrument_id INTEGER NOT NULL,   -- NOT the ticker; see below
  kind TEXT NOT NULL,               -- BUY | SELL | DIV_CASH | DIV_REINVEST
                                    -- | SPLIT | ROC | FEE | TRANSFER_IN
  trade_date TEXT NOT NULL,
  settle_date TEXT,
  quantity TEXT,                    -- Decimal as string
  price TEXT,
  amount TEXT,
  currency TEXT NOT NULL DEFAULT 'USD',
  status TEXT NOT NULL,             -- pending | settled | corrected
  raw JSON NOT NULL                 -- the untouched API payload
);

Three things earn their keep here. broker_txn_id as the primary key makes re-ingestion a no-op, which lets you re-fetch overlapping date windows without dedupe logic. status gives you somewhere to put a dividend that posts as pending and later settles at a different amount. And raw means that when you discover in month eight that the broker was sending a field you ignored, you can backfill from your own database instead of re-paginating three years of API history.

Use decimal.Decimal throughout, never float. DRIP produces fractional shares — a $47.20 dividend on a stock trading at $138.44 buys 0.340941 shares — and floats accumulate error across hundreds of those. Store quantities as strings with at least six decimal places, keep money in Decimal, and quantize() only at the point of display.

SQLite is the right default. A 30-position portfolio held for a decade generates on the order of a few thousand rows. You will not outgrow it, and a single file you can copy is worth more than a Postgres container you have to keep alive.

The sync loop: idempotent pulls and a reconciliation check

Brokerage APIs generally expose an activities or transactions endpoint paginated by date range. The naive loop — fetch since last run, insert — fails in two specific ways.

First, activity is not immutable. A dividend can appear as pending on the pay date and be corrected days later, and some brokers backdate corrections into a window you have already swept past. Re-fetch a rolling window (we use 45 days) on every run and upsert on broker_txn_id. If the payload for an existing ID differs from the stored raw, write the new version and flip status to corrected rather than silently overwriting — you want the diff visible.

Second, nothing tells you when you have quietly lost a transaction. Add a reconciliation step that runs after every sync: fold your event log into a share count per instrument, pull the broker’s current positions endpoint, and compare.

def reconcile(derived, broker_positions, tol=Decimal("0.000001")):
    problems = []
    for iid, qty in derived.items():
        actual = broker_positions.get(iid, Decimal(0))
        if abs(qty - actual) > tol:
            problems.append((iid, qty, actual, qty - actual))
    return problems  # non-empty => stop, do not publish numbers

This is the single highest-value 15 lines in the project. Without it, a tracker degrades invisibly; with it, a missed corporate action fails loudly on the next cron run.

On pricing: the reinvestment price is the price on the pay date, not the ex-dividend date. Most APIs give you the executed reinvestment price directly on the transaction — use it, and only fall back to a market data lookup for transfer-in lots where the broker sent no basis.

What breaks in year two

The tracker that works for six months breaks on the first corporate action, and always in the same places.

Tickers are not stable keys. Symbols get reused, companies rename, and a spinoff hands you shares of an instrument you never bought. This is why the schema above keys on instrument_id with the ticker as a mutable attribute. Retrofitting that after you have three years of history is a genuinely unpleasant migration.

Splits rewrite quantities, not events. A 4-for-1 split should be an event in the log, applied during the fold, not a bulk UPDATE against past rows. Rewriting history destroys your ability to reconcile against a broker statement from before the split.

Return of capital reduces basis rather than counting as income. If you fold ROC as ordinary dividend income, your yield on cost is overstated and your eventual capital gain is understated. Give it its own kind and handle it explicitly.

Transfers arrive without basis. Move accounts and lots frequently land with acquisition dates but no cost, sometimes for weeks. Flag those lots and exclude them from cost-basis reporting instead of letting a zero propagate into a return calculation.

Once the ledger is honest, the metrics are a handful of queries: yield on cost (trailing 12 months of distributions divided by cash basis, deliberately excluding DRIP-purchased shares from the denominator), the share of current position acquired through reinvestment, and a projected forward income figure. Those are the numbers no broker dashboard gives you, and they are the reason to build the thing.

Testing the parts that will actually bite

Write fixtures from real payloads, with the account numbers scrubbed. The tests worth having are the awkward ones: a dividend that posts pending and settles at a different amount, a 4-for-1 split mid-year, a spinoff that introduces a new instrument, a partial sale that has to pick lots under FIFO versus specific identification, and a re-run of the same sync window that must leave the database byte-identical.

That last test — sync twice, assert no change — catches more real bugs than any of the others. If it passes, your idempotency key is doing its job, and you can run the job hourly without thinking about it.