The Spreadsheet That Updates Itself

For a couple of years, keeping my investment tracker up to date was one of those quiet chores that never quite went away. Every so often I would log into my brokerage, export a pile of CSV files, and hand-copy the numbers into a Google Sheet. Reconcile, fix the rows that did not line up, repeat. It worked, but it was tedious, easy to get wrong, and it always seemed to need doing again a week later.

If you have read my other posts, you know how I feel about that kind of thing. I would rather fix a problem once than manage it forever. So I stopped patching the spreadsheet by hand and built a small pipeline to do the whole thing for me.

The idea

The core idea is simple: one source of truth, and everything else is generated from it.

Instead of the spreadsheet being the place where the data lives, I made a small database the place where the data lives. The spreadsheet became just one of several views that get rebuilt from that database. Nothing is ever edited by hand in the sheet, because the next rebuild would wipe it out anyway. That one rule, the database is the truth and the sheets are disposable, is what makes the whole thing reliable.

How it works

There are five stages, and each one does a single job.

• Pull. A script talks to my brokerage through the SnapTrade API and pulls in accounts, balances, positions, and transactions automatically. No more manual exports.
• Backfill. The API only reaches back so far, so I also merge in years of older CSV exports I had saved. Now the history is complete, not just the recent slice.
• Store. Everything lands in a single SQLite database. This is the source of truth. One file, one place to reconcile.
• Build. A set of scripts read that database and regenerate fully formatted Google Sheets: a trade log, current holdings, and monthly performance, along with a couple of local dashboards.
• Schedule. The whole thing runs on a weekly cron job, so it just happens in the background.

The part I am most happy with is that it is idempotent, which is a fancy way of saying you can run it as many times as you want and always get the same clean result. Run it twice by accident, no problem. Nothing double-counts and nothing breaks. That is the difference between a script you trust and one you babysit.

What I learned building it

A few things stuck with me.

One source of truth beats many copies. The moment I stopped treating the spreadsheet as the master and started treating the database as the master, a whole category of "wait, which number is right?" problems disappeared. Reconcile once, and every view downstream stays consistent.

Automation only counts if you trust it. Making the pipeline safe to re-run mattered more than making it fast. If I had to remember the exact right order of steps, or worry that running it twice would corrupt something, I would not actually use it. Idempotency is what turns a clever script into something I forget is even there.

Keep it local, keep secrets out of the repo. It runs on my own machine because that is where the credentials live. That keeps the sensitive bits off the cloud and out of version control, which is exactly where they should not be.

The honest caveats

It is not magic, and it is not truly zero maintenance. The connection to the brokerage occasionally needs to be re-authorized. It only runs where the credentials are, so it is tied to my machine rather than something humming along in the cloud. Every so often something upstream changes and I have to nudge it.

But the day to day is the part that matters, and the day to day is now a non-event. The numbers stay current on their own, and I stopped thinking about them.

Why I like it

This is the same lesson I keep coming back to, whether it is a watering system for the chickens or the right tool bought once instead of the long way around. Spend a little effort up front to remove a recurring chore, and you get that time back over and over.

I used to lose an evening here and there wrestling a spreadsheet into shape. Now a small pipeline does it while I am not looking, and the tracker is more accurate than it ever was when I did it by hand. That is a good trade in my book.