Just in time for a belated Christmas gift, I am very happy to share with you the much improved and less error-prone version of the upgraded Dividend Calendar Tool.
Judging by the large amounts of comments and personal messages received, the entire process of setting it up was not as straightforward as I imagined. Well, actually this should not be a surprise to me given that I have gone through the process dozens of times during development while ironing out the kinks alongside.
Also, with the old tool, it was next to impossible for me to give you tailored guidance as to what may have broken your setup. With this new release, I introduce some basic troubleshooting and progress tracking which will hopefully eliminate some common difficulties and help us better communicate about those which are left.
As a new feature, I added a fully automatic 12-month forward dividend projection which basically gives you a dividend calendar by stock and month for the next 12 months based on the last declared dividend.
To get started, you will need up three things as follows:
1. Download the new "Dividend Calendar and Dashboard Tool" for everyone; free to use!
2. Make a local copy of the Google Sheet: Dividend Stock Portfolio Tracking Part 7
3. Watch a comprehensive video tutorial that helps you set up everything and explain how you may use it.
The video tutorial explains all the settings, so what I will do in this article is to highlight existing and new features and how you can extract value from those. I will also write down the most crucial part, namely how to publish the Google Sheets as CSV.
Note: Unfortunately, the Excel Tool will only work on a Windows machine with Excel installed. On a Mac I cannot provide any support for it as I do not have one. Google Sheets itself works on a Mac and for the future I am planning to publish a simplified version of the Excel Tool on Google Sheets.
First, a personal wish here: As this article here is not about the typical content found on Seeking Alpha (stocks, strategy, etc.), I cannot anticipate how many readers will see it, thus adding importance to your feedback as to whether you find this as useful as I do, encounter any difficulties or have any other feedback to report. Please let me know so in the comments section. Thanks!
What is the purpose of all this?
Building upon my previous article on the Dividend Calendar Tool, I generally envision the following use cases for the new "Dividend Calendar and Dashboard Tool". Highlighted in bold is where I have added features and improved existing ones with this new tool. For an illustration of the other use cases, please refer back to this article.
- As a dividend investor, I want to project my dividend income for the next 12 months and compare my expected performance against what was realized.
- As a dividend investor, I want to centrally track and visualize upcoming dividends for the stocks I own. I do not want to copy and paste that information each time. I also want to be able to see historic development and track progress at different points of time over time.
- As a dividend investor, I want to have a simple and easy-to-use and easy-to-set-up tool which also allows the technical less savvy investor to work with it in an intuitive way.
- As a dividend investor, I want to distribute my dividend income across the different months. Therefore, I want to find out which stocks are paying in which month and then do further research on these or use this information as an additional aspect to consider when managing my current and future investments.
To put differently, the tool will help you achieve the following:
- Serve as a central and interactive go-to destination to track upcoming ex-dividend dates and dividend payments.
- Seamlessly integrate Google Sheets with Microsoft Excel, eliminating the need to manually look up ex-dividend dates and/or copy data to Excel for analysis.
- Monitor historic and future dividend performance over time by providing a central dashboard and further tracking options.
- Monitor my expected future dividend income and see how it distributes across one entire year.
Once everything is set up, the Excel Tool will have two green-colored sheets - "DividendDashboard" and "DividendCalendar" and a purple-colored "TrackLog".
The "DividendDashboard" sheet has seen some extensions to the previous version which I will go through in detail. For the sake of clarity, I will also briefly explain those which have already existed.
It is structured into five sections and shows the following by section:
Section 1 - YTD, historic, and expected and declared dividends (existing): Here you can see how your YTD dividend income compares with the prior year and also how it developed historically as depicted in the chart. Further, the chart gives an outlook of expected dividend payments based on the information retrieved from the "DividendCalendar" sheet. These expected payments are colored differently.
Section 2 - Expected future 12M dividends (new!): This is a monthly representation of expected 12-month forward dividends. In other words, based on the last declared dividend for an individual stock, we determine the payment rhythm, e.g. Feb., May, Aug., Nov., spread it across the next 12 months and sum up all the values for a month. At this stage, this does not factor in any dividend growth unless the last declared dividend has been the one which already incorporates the increase. For a future version, I am envisioning that the tool pulls the historical dividend growth rate and extrapolates accordingly. Or alternatively, the user simply inputs the growth rate.
Section 3 - Future dividends by dividend period and stock (new!): This is basically a drill-down of section 2 onto the stock level. Users can select one or more periods via the slicer and view the expected future dividends by stock in that given months or entire period.
Section 4 - Expected future performance in current month: Based on the current date, the ratios in the upper part disclose expected performance in the current month and compare it vs. that of the same month from the prior year. Depicted below is a breakdown of expected upcoming payments for the current month, in this case, August 2017, by stock and day.
Section 5 - Tracking progress: This basically shows the development of customizable core metrics, in this case, YTD dividends, Prior YTD dividends, and All-time dividends, over time based on the data of the data import. The respective raw data behind can be found on "TrackLog" and is updated whenever the "Import data" button is hit on "Parameters" (see video for instructions!).
In addition to that there is the "DividendCalendar" sheet, which looks like this:
That was a lot of explanation. Let's now illustrate the core use cases that I would like to cover with that solution.
Putting it into practice
To track and visualize upcoming dividends for the stocks you own, you can view that information as part of the interactive calendar on the "DividendCalendar" sheet. Once you have set up everything correctly, you will get a chart like this:
So here you can see the following:
- In blue, upcoming gross dividend payments that have been declared based on the number of shares you have.
- In red, upcoming gross dividend payments for stocks where the ex-dividend date has already been passed but the dividend has not been paid yet. For instance, Altria (MO) already went ex-dividend in December, but the respective payment (scheduled for January 10) has not yet occurred.
- In gray, expected upcoming gross dividend payments for stocks where the next ex-dividend has not been officially declared but has been derived based on when the company's stock went ex-dividend the year before. For January, no such stock meets that definition and that's why nothing is displayed.
Meanwhile, on the "Dividend Dashboard" sheet, we will now see the same breakdown of expected dividends by stock and date and are then able to put it into context as we not only see the total for this year but also for the prior year:
Also by choosing the slicer to only show the Dividend Period "2018/01", you will get the same per stock breakdown as on the "DividendCalendar" sheet, with the exception that here you can forward the next 12-months and not only the next month.
If you want to do more detailed and long-term dividend projections on a portfolio or stock level you can use a tool I build to model the growth of dividend (re)investing. You can use this to forecast future dividends to either compare low-yield-high-growth, e.g. Gilead (GILD), individual stocks fare against high-yield-low-growth stocks, e.g. AT&T (T), over time or to project multi-year future dividends on a portfolio level. The former could look like this for instance:
Instruction Tips and Troubleshooting
As this is a free product, I cannot guarantee that everything works perfectly, but these few tips along with the troubleshooting embed in the Excel Tool as well as the video I mentioned above will hopefully solve most issues.
For the setup, the most crucial part is to get the publishing of the Google Sheets right as that is the interface between Google Sheets and the Excel-based tool. First, make sure to make a copy of the linked Google Sheet by clicking on "File -> Make a copy". Next, follow these simple steps for these three Google Sheets: "RawData", "Div_Lookup", "Stock Prices":
1. Click on "File -> Publish to the Web"
2. Select the specific sheet and output format and hit "Publish"
3. Copy that link and paste it into the Excel Tool in the respective cell. In this case "C18" on "Parameters"
4. Repeat that process for the other two sheets and paste the links into "C11" for the "Div_Lookup" sheet and "C25" for the "Stock Prices" sheet.
Once everything is set up hit the "Import Data" button and wait for the process to finish once a message pops up showing you the amount of time in seconds it took. Afterwards, you can hit the "Update 12-month dividend projection" button to populate the rest of the tool.
The most common issue to occur is that the "Div_Lookup" sheet mostly/partially shows #REF values. This is a glitch in the Google API that I cannot fix. If somebody knows of a way to do that, please shout out. To improve the process, you are now able to see the "RawDivClock" sheet in the Excel Tool and can make adjustments to it. Once you have done that you can jump to the "Parameters" sheet and use the three buttons in the "Update entire tool step-by-step" section. This step-by-step process can be used anytime you make manual adjustments to the "RawDivClock" sheet in the Excel file. This is still a bit hacky but the best I was able to come up with.
Similarly, if the import of the Google Sheet information for some reason does not work you can also manually copy and paste the data to the yellow sheets in the Excel file and then run the step-by-step section as well as hitting the "Update 12-month dividend projection" button.
Also, if you discover that the import for the "Div_Lookup" sheet was corrupt, you can restore the previous information by clicking the "Restore RawDivClock Data" in the Troubleshooting section.
Personally, I hope that you enjoy this tool as much as I do and I am very grateful for having a platform here to publish and share my work. Without that platform, I would never ever have built these tools in the way they are now, and so I am very looking forward to your feedback. Just let me know what sort of features you can envision along the overall vision of seamlessly integrating data from Google Sheets into Excel for dividend and stock analysis, dividend and stock performance, projection and alike. Are you missing specific metrics? Are you missing specific charts? Just throw out anything that comes to your mind :)
Again, please let me know in the comments section if you find this helpful or encountered any difficulties with the import. Please "follow me" if you want to read more about this and let me know what other features you are missing.
And one more thing: As this tool has been expanding in terms of feature set and, hopefully, in terms of usability, still it is difficult for the latter to keep with the former. Thus, if there is any developer here who would like to team up to make this more user-friendly, I'd be more than happy to discuss!
Happy Holidays everybody!
PS: Please feel free to share this with others but please refer them back to this article in this case. Thanks a lot!
Disclosure: I am/we are long T, MO, GILD.
I wrote this article myself, and it expresses my own opinions. I am not receiving compensation for it (other than from Seeking Alpha). I have no business relationship with any company whose stock is mentioned in this article.