Categories
Dividends

Dividend Tracker 0.7 update: New graphs and working charts

The dividend tracker has just been updated … again! Here’s what’s new.

I’m still looking to add some small touches here and there to the dividend tracker. I originally planned this 0.7 update to be one that added a feature, but it’ll also be used as a means to roll out a fix to the UK template of the tracker.

New stock performance graphs

Having the unrealised gains for each stock is a handy measurment to see how the stock is currently performing. But what if you wanted to see a little more history? Also, this isn’t that useful if you don’t actually own any shares in the said stock. You can’t have any unrealised gains without owning anyshares.

I wanted to work in a chart for each stock without making each market tab too bloated. The end result is a sparkline graph. These are amazing mini-charts available in Google Sheets and work perfectly for what I wanted to achieve. A small code in a new column shows a small line graph that shows the stock’s performance for the past 30 days.

What makes this so useful for glance checks is how quickly you can see dips to analyse further (potential purchases) or if you should consider checking the news if one of your stocks starts to tank.

Changelog

  • Added new stock performance graphs (30 days) for each market tab.
  • Added P/E ratio to each market tab.
  • Added price to earnings (EPS) to each market tab.
  • Fixed an error with the stock diversification chart by altering the formula in the UK template. (Thanks, Jay for spotting the error!)

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. Create a new column between columns J and K in each market tab.
  2. Drag J1 cell to the newly created K1 to copy across the formatting.
  3. Rename the newly created K1 cell to “Market Price (30 Days)”.
  4. Enter the following code into K2: =iferror(sparkline(GOOGLEFINANCE($C2,"price",TODAY()-30,TODAY(),"DAILY"),{"charttype","line";"linewidth",2;"color","#b4a7d6"}),).
  5. Drag K2 all the way to the last cell to copy the formula to each stock row.
  6. To fix the stock diversification chart (UK template only!), head to the Data tab and change the formula in C14, D14, and E14. They incorrectly reference column A towards the start of each formula. Example: =SUMIF(UK!A$2:A,B14,UK!I$2:I) should be =SUMIF(UK!B$2:B,B14,UK!I$2:I). Change each cell’s formula and the chart will begin to update accordingly.
  7. Add two new columns on each market tab in between Market Value and Yield On Cost. Add =IFERROR(GOOGLEFINANCE(C2, "eps"),"-") to the newly created M2 cell and =IFERROR(GOOGLEFINANCE(C2, "pe"),"-") to N2. Set M2 format to the currency of that tab (USD for US, GBP for UK, etc.). Set the format of N2 to 0.00. Drag them down to copy code.

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 *