About two monthsago I created a spreadsheet in Microsoft Excel to track my dividend stocks and income. Not only did I create quite the handy spreadsheet, I also shared the end result with you guys so you could all take advantage of it. Since reactions were quite positive overall, I left it at that for a while.
However, one major beef most of you had, was the fact that ittakesa lot of manual actionsto keep track of everything. As a result, I decided to try out Google Sheets, which allows for easier automation. After a couple hours of finicking around with Google’s version of Excel, I’m happy to share my now mostly automated dividend tracker!
Just like last time my spreadsheet is aimed at international investors. Being one myself I need to be able to track my investments in multiple currencies, mainly to keep an eye on the capital appreciation. Following the growth of my dividend income stream is what it’s all about, but it’s nice to know the actual value of your portfolio at any given time in your home currency too.
Since the basics of my spreadsheet haven’t changed much, the workflow remains rather the same.On the “Portfolio” tab you’ll have to enter a stock’s name, ticker, country, sector and currency. After making a purchase you’ll of course also have to provide the amount of shares bought and the cost basis in your home currency.Google will take care of the rest for you.
Currency exchange
The top left of the dividend tracker still shows a currency exchange calculator and a pie graph to represent the weight of each currency in your portfolio. You can change the currencies you invest in to whatever you like – if you’re up for some Zimbabwean companies, go for it! Contrary to last time, Google now provides the last known exchange rate automatically using the following function:
=GoogleFinance("CURRENCY:EURUSD")
Like I already said, you should enter the right currency symbol in your list of stocks in column E, otherwise Google Sheets will display an inaccurate portfolio value. Columns I and J on the same tab then do the rest to provide you the value of your holdings in your home currency.
Sector diversification
My dividend tracker also displays the diversification of your portfolio based on the input you provide in column D of the “Portfolio” tab. Since most people found this a useful and easy way to visualize the different industries they were invested in, I haven’t changed anything here. The spreadsheet still aggregates the homevalue of every individual stockin column J based on the sectors in column D.
Year-Over-Year performance
A brand-new function, and one which I really like myself, now displays the past income performance of your portfolio on a yearly basis. A small graph quickly visualises your progress for the year compared to the previous three years, as you can see in the picture below. The data needed for these statistics comes from the “Dividend income” tab, which I’ll explain in detail below.
Dividend overview
The “Dividend overview” tab provides you with adetailed table and graphs of your dividend income on a per position and per month basis. Nothing has changed here compared to the previous version of my spreadsheet. Users of my budget tracker will also be familiar with the mechanic behind this sheet. Cell O1 is based on cell Q1 on the “Portfolio” tab, which then collects all dividends from a particular year from the “Income” tab.
Like last time, the only thing you have to do to make this sheet work, is add a ticker when you buy into a new position and add that position to the “Portfolio” tab. When your dividends start rolling in you enter those on the “Income” sheet by date and ticker. The dividends entered there will also be displayed on the first page, both in the table and the YOY performance overview.
When a year passes by all you have to do is change cell Q1 to the new year and Google Sheets will update the dividend tracker. The upside of doing it this way is that you’ll be able to go back in time for a detailed overview from any of the previous years by changing the year up top again.
Further automation
If you want to automate tracking your investments and dividends even more, you could have Google Sheets insert the dividend payments automatically. Doing so, however, is rather convoluted. I haven’t added it to my spreadsheet for two reasons.
First, Google Sheets doesn’t offer a way to insert the dividend or yield through the GoogleFinance-function, so you’ll have to rely on third-party solutions like this one:
=split(ImportXML(concatenate("http://finance.google.com/finance?q=","JNJ"), "//td[@data-snapfield='latest_dividend-dividend_yield']/following-sibling::*"),"/")
The formula above tries to download the dividend and yield of Johnson and Johnson (JNJ) from the Google Finance website, which actually works great. The major downside is that Google Finance often is wrong, especially for non-US stocks or stocks listed on multiple stock exchanges under the same ticker. The chance of wrong data in your dividend tracker is just too high. Another major downside is that you won’t be able to keep historic records.
Second, and a direct result of the previous problem, I’m keeping track of my net dividends; not the gross payments the companies make. Since I have to deal with both foreign withholding taxes and national taxation on dividend payments, it’s nearly impossible to automate the entire thing. Maybe that’s not the case for you, so you could actually implement the formula above if you wished to.If you decide to do so,take a look at Tawcan’s guide for implementation ideas.
Final words
If you’ve gotten this far, you’re probably wondering when the damned download button will finally pop up! Don’t worry, here it is!
I’ll provide you guys with an updated Microsoft Excel version without the nice automation Google Sheets provides and a Google Sheets variant, which obviously does almost everything for you. If you’re looking for a real life example, the Google Sheets dividend tracker is actually the last version of my own spreadsheet.
As always, feel free to play around with the spreadsheets and adjust things to your liking. I sincerely hope some of you find them useful, especially the automated one since many of you sent me an e-mail asking for automated solutions. Let me know what you think of it and if there’s something you’d like me to add.