More than half a year after I released the upgraded Dividend Calendar Tool, I have now taken some time to enhance it further by building on the feedback I have received and on my own ideas. The one thing that was truly missing was some kind of dashboard that automatically presents investors with a core overview regarding historic and upcoming dividend performance.
I have simplified the spreadsheet as much as possible, and you can use slicing and dicing to trim and view the data as you like.
To get started, you will need up to three things as follows (depends on whether you already use the previous Dividend Calendar Tool or just start out):
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 6
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. But ...
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 up on 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 constantly monitor when and which stocks are going ex-dividend without having to check several different links (one per stock) every day to retrieve that information and then manually copy and paste it into my spreadsheets.
- 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.
- 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.
To put it 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.
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 is the central improvement to the previous tool and looks like this:
It is structured into three sections and shows the following by section:
Section 1 - YTD and historic performance: Here you can see how your YTD dividend income compares to 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 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 3 - 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:
So this sheet contains a lot of information. I have numbered the individual sections and will outline their meaning subsequently
- Next ex-dividend date: Here we are notified about the next ex-dividend date based on today's date (this is taken from the original article in January and thus the January date); in this case, Main Street Capital Corporation (NYSE:MAIN) goes ex-dividend next.
- One-year overview: This 12-month rolling calendar stretches from 2017 to 2018 and highlights all ex-dividend dates over that period with a little red flag symbol. Based on the number of stocks going ex-dividend on that day, the color changes from light blue to dark blue. Saturdays and Sundays are grayed out.
- Daily Dividend Information: By clicking on any date in the calendar in 2) this section displays all relevant information for the stock(s) going ex-dividend currently; it will inform you about the ex-dividend date, the dividend payment date and the declared $ dividend per share amount. Additionally, if you own shares in this stock, it will calculate the total gross dividend payment.
- "FIX" Button: Hit this button if the "Daily Dividend Information" is not refreshing when you click on a color-coded date in section 2.
- Payment Dashboard: This is an interactive chart depicting expected dividend payments by day and stock. It is color-coded in three categories, where blue indicates payment information for stocks that have declared an ex-dividend date; red represents officially declared ex-dividend dates where the last ex-dividend date has already been passed but payment is still outstanding, hence, the next ex-dividend date has not yet been declared; gray (not depicted in the screenshot) indicates payments dates for stocks that haven't declared the next ex-dividend date yet and whose previous dividend has already been paid - for these stocks the information is derived by assuming that the payment in the previous year will repeat itself in the current year.
- Stock slicer: This slicer allows you to select all, one or multiple stocks you want to show in the chart below.
- Declared slicer: This slicer contains three different elements indicating the declaration status of the dividend where "yes" corresponds to the data shown in blue on the Payment Dashboard, "no" relating to the color gray on the dashboard and "no, dividend not yet paid" reflected in the red bars on the dashboard chart.
- Year_Month slicer: Here you can select the payment periods you are interested in plotting on the chart.
- Active slicer: This slicer allows you to only plot stocks on the chart where you own shares (active = 1), to only plot those where you do not own shares (active = 0) or both.
That was a lot of explanation. Let's now illustrate the core use cases that I would like to cover with that solution. As mentioned above, I will specifically highlight where the added features will come in handy for this use case:
- 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.
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, Verizon (NYSE:VZ) already went ex-dividend in July, but the respective payment (scheduled for August 1) has not yet been entered into the raw data.
- 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. As you can see, Fortress Investment Group (NYSE:FIG) is colored in gray, which indicates that the company has not yet declared the next ex-dividend date but is expected to pay over the next two weeks. We are expecting payment of $4.50.
Meanwhile, on the "Dividend Dashboard" sheet, we will now see the same breakdown of expected August 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:
Additionally, based on the data that has been retrieved, that all the derived expected dividend payments are also plotted on the historic chart, which allows you to take a sneak peek at future development.
This projection works very reliably for the current quarter right now as this is generally the period for the which ex-dividend dates have either already been declared or can be derived easily. Extrapolating data beyond that time frame will be a feature I will work on in the future with the goal to give you a 12-month forward dividend projection.
In the meantime, you can still project this but only on a high-level basis. Therefore, 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 (NASDAQ:GILD), individual stocks fare against high-yield-low-growth stocks, e.g. AT&T (NYSE:T), over time or to project multi-year future dividends on a portfolio level. The former could look like this for instance:
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.
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, GILD, MAIN.
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.