Portfolio tracking

Your spreadsheet stopped matching your broker

Orchard72 Team

A portfolio spreadsheet does not fail loudly. It drifts a line at a time, and the first sign is usually a total that no longer matches the number your broker is showing you.

Your spreadsheet stopped matching your brokerYour spreadsheet stopped matching your broker

The spreadsheet did not break. Nothing crashed, nothing threw an error, and every formula still calculates exactly what it was written to calculate. It is simply no longer describing the account it is supposed to describe, and it got that way one small, unrecorded event at a time.

What actually causes the drift

A manual record only reflects the events someone typed into it. A broker's own figures reflect every event the custodian processed, whether or not anyone told you about it.

Corporate actions are the most common gap. A stock split changes your share count without a purchase or a sale. A spin-off creates a new holding out of an existing one. A merger swaps one security for another, sometimes with a cash adjustment on the side. Each of these changes what you hold, and none of them shows up in a spreadsheet unless someone remembers to go back and edit it after the fact, weeks after the notice went out.

Fees and foreign exchange drift more quietly still. A small charge taken directly from a holding, or a conversion applied at the day's rate rather than the rate a spreadsheet assumed, moves a number by a fraction of a percent at a time. None of it is dramatic on its own. Over a few years of statements it adds up to a running total that no longer reconciles to a specific figure anywhere, only to a rough sense of "about right".

Transfers and partial fills are the third source. An order that fills across several trades at several prices settles as one averaged fill price, and in a manual record often gets rounded to whichever fill was easiest to copy down. A transfer between accounts or between brokers can also arrive without its original purchase history attached, which is a different problem again and the harder one to fix after the fact.

What reconciliation actually catches

Reconciliation is not a prediction and it is not advice about what to hold. It is a comparison: what your record says you have, against what the account itself says you have, line by line, until every difference is either explained or resolved.

Done regularly, it catches a corporate action before it becomes three years of an inflated or understated share count. It catches a fee that has been silently compounding against a number nobody re-checked. It catches a transfer that arrived without its cost history, while the statement that would explain it is still easy to find rather than several house moves and one broker migration away.

Done rarely, or only when something already looks wrong, reconciliation finds the same gaps much later, after the corrections have to be reconstructed from old statements rather than read straight off a current one.

Why one spreadsheet across several brokers makes this worse

Consolidating several brokers, wallets or accounts into one manual view is useful for seeing the whole picture at once. It is also where drift compounds fastest, because each source updates on its own schedule, in its own format, and a single missed update from any one of them throws off the combined total without changing any of the others.

The practical fix is not to consolidate less. It is to let each account be checked against a statement from its own source each time you have one to hand, rather than trusting whichever number was last updated from memory. That is the specific gap our portfolio tracker is built to close: upload a broker statement and it reads the holdings out of it, then shows every row for your approval before anything is saved.

The record outlives the reason you started keeping it

An accurate, reconciled record is not only useful while you are the one reading it. At some point someone else, an executor or a beneficiary, may need to work out what a portfolio actually held and what it originally cost, often without you there to explain the gaps. A spreadsheet that quietly stopped matching the broker years earlier leaves that person reconstructing figures from statements rather than reading them off a current record, at exactly the point when reconstructing them is hardest. Our guide on inheritance tax touches on why the records behind an estate matter as much as the estate itself.

So treat the mismatch as information rather than an annoyance. A spreadsheet that no longer matches the broker is not a rounding error to shrug off, it is a sign that something changed that the record never heard about, and the earlier that is caught, the smaller the reconciliation there is to do.

We use cookies to improve your experience. See our Cookie policy (opens in a new tab) for details.