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.
- 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:
- Create a new column between columns
Kin each market tab.
J1cell to the newly created
K1to copy across the formatting.
- Rename the newly created
K1cell to “Market Price (30 Days)”.
- Enter the following code into
K2all the way to the last cell to copy the formula to each stock row.
- To fix the stock diversification chart (UK template only!), head to the
Datatab and change the formula in
E14. They incorrectly reference column A towards the start of each formula. Example:
=SUMIF(UK!B$2:B,B14,UK!I$2:I). Change each cell’s formula and the chart will begin to update accordingly.
- 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
M2format to the currency of that tab (USD for US, GBP for UK, etc.). Set the format of
N2to 0.00. Drag them down to copy code.