Dividend Tracker 0.9 update: £, p, and market gains

I’ve released yet another update for my dividend tracker, making it even more brilliant.

Being able to see how your portfolio is performing with unrealized gains is one thing, but taking into account your earned dividends is something else. We’re focusing on passive income, so it makes sense to take a quick peek at how you’re doing with both the market and income generated thus far. My dividend tracker doesn’t do this just yet but update 0.9 changes the game.

For some reason, I didn’t have this clearly displayed on the summary tab. That’s been addressed with the revamped glance stats. You now have market gains and total gains.

You can now clearly see market and total gains.
  • Market gains: Your unrealized gains or losses regarding stock pricing alone.
  • Total gains: Your unrealized gains or losses combined with earned dividends.

As well as making it easier to decipher this information, I’ve also worked in some additional automation into the UK market tab across all spreadsheet templates. The British stock market is weird in that we use both GBX and GBP. This tracker correctly separates the currencies, but you have to do formula manually in the UK tab for market pricing.

This works well but breaks when you want to refresh the formula by dragging them down in the spreadsheet, requiring you to manually fix each formula for the GBP listings. To remedy this issue, I’ve added a new column to the UK market tab that lets you pick £ or p depending on whether the stock is in GBP or GBX.

Rich's Dividend Tracker

Now, you don’t have to do anything beyond that. The market pricing column will look at this new column to correctly calculate the current stock price.


  • Added a new column to the UK market tab, allowing you to choose between £ (GBP) and p (GBX), which is then reflected in formula calculations.
  • Revamped summary page with new data on market and total gains, with the latter taking into account dividends earned to-date.

How do I update my tracker?

Make sure you already renamed the previously copied template you’re currently using. With an updated release like this one, simply copy across a new version to your Google Docs. Then follow these steps:

  1. Right-click on column F (Dividend Calendar) in the UK market tab. Click on “Insert 1 right” to create a new column and call it whatever you wish. I went for “£.”
  2. In column G, add a p for GBX stocks and £ for GBP stocks.
  3. In K2, change the formula to =IFERROR(IFS(G2="p",GOOGLEFINANCE(C2)/100,G2="£",GOOGLEFINANCE(C2)),0). Drag and copy this cell down for every stock.
  4. You can open the templates to copy across code for the Summary tab changes, or (to do it manually) drag the change percentage data down two cells. You should now have two rows free in the glance data stats. Add =SUM(G6+B9) to B4 and =IFERROR(B4/B3,0) to C4.

By Rich Edmonds

Rich creates content for the top Windows-focused publication, but by night he tries to make his money work for him and rambles far too much here.

Leave a Reply

Your email address will not be published. Required fields are marked *