My investing diary: Building a live‑update spreadsheet for my portfolio

By Thomas | financial enthusiast

My investing diary: May 31, 2026 – Building a live‑update spreadsheet for my portfolio

I woke up with the nagging feeling that my portfolio dashboard on the broker’s app was too “black‑boxy.” First thought was, why not just make my own? I grabbed a fresh Google Sheet and set a timer. Damned, I’d never built something this interactive before, but the idea of total transparency was too tempting to ignore.

The skeleton – assets, weight, and price feed

I started by listing every ticker I own in column A, then added a column for the target weight (my strategic allocation) and another for the current weight. The tricky part was getting live prices without paying for a data feed. After a bit of Googling, I discovered the GOOGLEFINANCE function works for most US equities and a handful of ETFs. For the handful of crypto assets I hold, I used a simple IMPORTXML call to pull the latest price from CoinGecko’s public endpoint. (Works out nicely.)

The layout looks like this:

I didn’t realise how much I was missing by not seeing the Current % column in real time. The sheet automatically recalculates every minute, so I can see drift the moment the market moves.

Calculating returns – time‑weighted vs. simple

Next I wanted a clear picture of performance. I built two return columns: a simple return based on the change in market value since I bought the position, and a time‑weighted return that accounts for contributions and withdrawals. The simple return is just (Current Value – Cost Basis) / Cost Basis. For the time‑weighted version I used the XIRR function, feeding it a hidden table of cash flows (buy, sell, dividends). I was surprised how the XIRR column sometimes diverged dramatically from the simple return – especially after my dividend reinvestments.

I added a conditional format that paints the return cell green if >5% YTD, red if <‑5%. It’s a tiny visual cue, but it makes me pause before I click “sell.”

Flagging rebalancing needs automatically

Now for the magic: a flag that tells me when a position is out of line with my target allocation. I wrote a small IF formula in column G:

=IF(ABS(F2 - B2) > 0.02, "⚠️", "")

The logic is simple – if the current weight deviates by more than 2 percentage points from the target, a warning emoji pops up. I set the sheet to send me an email alert via Google Apps Script whenever a cell in column G changes to the warning symbol. (I almost missed this step, but the script now pings me at 9 am and 4 pm.)

To keep the process disciplined, I drafted a three‑step action plan:

1. Review warnings – open the sheet, note any ⚠️ symbols, and check the underlying market value.
2. Calculate trade size – use the formula Target Value = Total Portfolio × Target %; then Shares to Trade = (Target Value – Current Value) / Current Price.
3. Execute – place the trade in my broker, then immediately update the “Shares” column to reflect the new position. The sheet recalculates everything instantly.

Having this loop automated means I no longer have to eyeball percentages on a mobile app and wonder if I’m drifting. The spreadsheet does the heavy lifting, and I just approve the numbers.

Making it my own – customization tips

I spent the afternoon tinkering with colors, adding a “Notes” column for trade rationale, and even embedding a tiny chart that shows portfolio growth over the last 12 months. A quick tip: use the SPARKLINE function for a one‑cell mini‑graph. It’s a neat visual that fits in the header without cluttering the sheet.

I also built a drop‑down list for asset class (Equity, Fixed Income, Crypto, Cash) and a pivot table that aggregates exposure by class. This helped me spot that my “Cash” bucket was actually 4% of the portfolio, not the 2% I intended. (Oops.)

The whole thing took me about three hours to get to a usable state, but now I spend less than ten minutes a week maintaining it. The biggest surprise? The spreadsheet felt more reliable than any paid app I’ve tried, because I can see every formula, every reference, and tweak it on the fly.

Where to go from here

I’m already thinking about adding a macro that pulls my broker’s CSV export and populates the sheet automatically, eliminating the manual “Shares” update. Also, a small Monte Carlo simulation tab could give me a sense of future risk‑adjusted returns without leaving the spreadsheet. The sky’s the limit when you own the data pipeline.

So, if you’re tired of opaque dashboards and want full control, give a DIY spreadsheet a try. It’s cheap, transparent, and oddly satisfying to watch your numbers dance in real time. What’s the first feature you’d add to your own portfolio tracker?