Exactly a year after I released my real-time Dividend Calendar and Dashboard, I am now very happy to share with you a brand-new and bullet-proof dividend calendar spreadsheet taking monitoring and interactivity to the next level as a sort of belated Christmas gift.
While there are currently less features than in the previous version, the entire tool is bullet-proof, very easy to use and as simple as possible.
I have been very delighted by all the positive and constructive feedback I have received over the last year, and thus instead of trying to fix the existing tool, I decided it was time to build it up from scratch. At the core of this new release is the notion that all data will be extracted from Seeking Alpha, thereby leveraging the power of the platform and avoiding unreliable data imports.
Over time I will add the old and new features to the tool and release them. We will no longer use slow and unreliable Google sheets but instead use Excel and Tableau Public.
I am sure the latter is new to many people, but I feel very confident that you will love it as much as I do for these purposes.
To get you started, you will need the following four things:
- Create an account on Seeking Alpha and set up a Portfolio containing the stocks you want to track. The Dividend Calendar currently supports up to 200 stocks.
- Download the new "Dividend Calendar" tool; free for everyone to use.
- Download Tableau Public for full interactivity. This is optional but will greatly enhance user experience, especially for future releases of the "Dividend Calendar" tool.
- Read this article to understand what the tool can do for you and how to set up.
Note: Unfortunately, I only have a PC and thus cannot tell whether it will work on a Mac. In case it works smoothly, please let me know so that I can eventually take out that disclaimer here.
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?
To keep it short, I generally envision the following use cases with the tool in its current release:
- 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 in a cumbersome and manual 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.
- As a dividend investor, I want to centrally track and visualize upcoming dividends for the stocks I own.
- 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.
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.
- Monitor historic and future dividend performance over time by providing a central dashboard and further tracking options.
What data are we using, and how do we import it?
Generally, we will use data from Seeking Alpha's Portfolio feature in order to retrieve ex-dividend dates, dividend yields, dividend growth, the actual dividend rate and the payout ratio (payment dates are currently not provided here but can either be manually added into the tool or extrapolated based on historic days between ex-dividend date and payment date).
Therefore, you need to first set up an account with Seeking Alpha and then create a new portfolio. In the screenshot below, I have set up a portfolio with all members of the Dow Jones which looks as follows when you click on the "Dividends" tab:
This gives a comprehensive and real-time overview of all relevant dividend metrics. We can create multiple portfolios here that we want to track and import up to 200 stocks into the custom tool.
The other tabs, such as "Momentum", "Valuation" or "Profitability", offer rich information as well but won't be used in the custom tool for now. Now, to import that data into the tool, you only have to copy and paste the respective section into the respective Excel sheet. There is a very easy to use checklist included in the tool as well as a concise GIF showcasing it here.
Additionally, on the "Holdings" tab, you can add the number of shares to your stocks that you currently own. This data will then later be used to calculate upcoming dividend payments and track progress.
The tool itself comes with pre-populated data for all members of the Dow Jones (10 shares in each stock), but you can completely customize these and import your own list of stocks and respective shares that you want to track.
Once everything is set up (i.e., you created a "Portfolio" on Seeking Alpha, copied that data into the Excel tool and followed the steps on the "Checklist" sheet), the Excel Tool's output sheet "DividendCalendar" looks as follows:
So this sheet contains a lot of information. I have numbered the individual sections and will outline their meaning subsequently
- One-year overview: This 12-month rolling calendar always covers the next 12 full months, in this case from December 2018 to November 2019. It highlights all ex-dividend dates over that period for the stocks you want to track 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 1) this section displays all relevant information for the stock 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. Here we have selected February 28 where we expect 7 Dow members to go ex-dividend.
- Days until ex-dividend date by stock: This provides an overview of when stocks will go ex-dividend, displaying the number of days until that day is reached as well as displaying the exact date.
- Payment Dashboard: This is an interactive chart depicting expected dividend payments by day and stock. It is color-coded into 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; and gray 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.
- These slicers allow to filter the data. Slicers colored in purple apply to 3) slicers in pink, 4) turquoise slicers apply to both.
- Next ex-dividend date: Here we are notified about the next ex-dividend date based on today's date. In case multiple stocks go ex-dividend date on that date, it shows data for the first one in alphabetical order; here it is American Express (AXP).
- "FIX" button: Hit this button if the "Daily Dividend Information" is not refreshing when you click on a color-coded date in section 2.
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, Nike (NKE) already went ex-dividend on November 30, but the respective payment (scheduled for January 2) has not yet been paid.
- 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, Johnson & Johnson (JNJ) is colored in gray, which indicates that the company has not yet declared the next ex-dividend date, but is expected to pay on March 11, 2019. The corresponding dividend payment received in 2018 dates back to March 13, 2018.
Finally, there is Tableau. As a starting point, I have only prepared a viz that depicts upcoming dividend payments in a calendar view. This is fully filterable and interactive. Right now, it shows all members of the Dow and expected dividend payments at 10 shares in each stock. This is in beta stadium and as such not yet customizable which will follow with future releases. It is meant to give a glimpse of how we can leverage that data using Tableau. For instance, the ex-dividend calendar now looks like this:
If you hover over the February 28 data, you will see a little box appear which shows all stocks going ex-dividend on that date with their dividend per share and expected future payment. By using the slider at the top-right, you can only show those dates, where, for e.g., three stocks go ex-dividend; in this case only on January 3 and February 15:
The other viz "Expected Payments" shows a different calendar view of upcoming dividend payments by stock and reveals the following information when hovering over any data point; in the example below, I hovered over Cisco (NASDAQ:CSCO):
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 much looking forward to your feedback. Just let me know what sort of features you would like. Are you missing specific metrics? Are you missing specific charts?
Disclosure: I am/we are long AAPL, CSCO, DIS, INTC, JNJ, JPM, KO, MCD, MMM, MSFT, NKE, PFE, PG, V, VZ, WBA, XOM. 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.