Future of the Dividend Meter and November 2017 Update

November 2017 EOM DividendsWhoa, what happened to my Dividend Meter spreadsheet?  I know many of you, including myself, were incredibly frustrated this past month when the automatic import of dividend data stopped working.  For those unaware, the Dividend Meter spreadsheet used common import formulas to retrieve dividend data from a popular finance portal’s free and open API service. The API service is no longer available.  While this news is disappointing for Dividend Meter fans, it has also served as a catalyst to explore alternate solutions – solutions that potentially could provide a superior dividend tracking spreadsheet. Before we get to a summary of solutions currently being explored, let’s take a brief look at what happened to the Dividend Meter portfolio in November:

Date Total Annual Dividends Notes
11/9/2017 $8,527.23 SIX dividend increase, raises meter reading $27.84
11/8/2017 $8,499.39 ADP dividend increase, raises meter reading $4.80
11/4/2017 $8,494.59 SWM dividend increase, raises meter reading $9.88
11/3/2017 $8,484.71 Sold 50 AWR, used proceeds and approx. $30 in accumulated cash dividends to buy 257 AES, raises meter reading $72.36
11/2/2017 $8,412.35 SBUX dividend increase, raises meter reading $24.00

November Summary

Four stock dividend increases and a swap of all remaining shares of American Water States (AWR) for AES Corporation (AES) combined to push the annual dividend income for the portfolio past the $8,400.00 and $8,500.00 barriers in November.  Selling the remaining shares of American Water States was a difficult decision – it’s one of a small handful of companies that have raised their dividend for more than 50 consecutive years. However, with the recent price increase of AWR shares, the dividend yield dropped further into the “sell” zone.  I felt it was a good time to take advantage of AWR’s lofty valuation and trade all remaining shares of the stock, yielding 1.88%, for a different utility company yielding 4.50%: AES Corp., a diversified electricity generation company.

Now on to potential solutions for fixing your Dividend Meter spreadsheet….

Manual Dividend Meter Fix

Let’s start with some good news – the formula for importing stock prices from Google Finance into your Google Sheets dividend tracker still works:   =(GOOGLEFINANCE($C2, “price”))   (Note, $C2 is the cell reference for the stock symbol).  The stock price is the data figure that changes most frequently, so it’s great to still be able to import this variable into your spreadsheet for free from Google Finance.  The only other data point you need to calculate dividend yield is the annual dividend rate, which typically only changes once a year.  So, if your portfolio of stocks isn’t that large, it shouldn’t be too time-consuming to verify the current dividend rate for your stocks periodically and manually update your spreadsheet for the annual dividend figure.  If you still want to see the Dividend Payout Ratio on your spreadsheet, then you have more work to do to update the EPS share data point (this changes quarterly after each earnings announcement).  After EPS is collected, then the Dividend Payout Ratio is simply a calculation (Annual Dividend dividend by Earnings-Per-Share), and not a data figure that needs to be imported.

Other Finance Data Providers

I have started evaluating other finance data providers, keeping in mind the following attributes:  Affordable, Easy-to-Use, Reliable, Quality Data, and an Expansion of Dividend-related Data Points.  Honestly, it’s too early to pass judgement or recommend any other data provider as a potential solution, so I won’t mention any specific companies yet.  The “Expansion of Dividend-related Data Points” characteristic of a data provider has been elusive.  The data providers I’ve looked at seem to provide the basics:  stock price, dividend, payout ratio.  But I haven’t come across an ideal solution yet.  I would love to have these additional data points:  Ex-Date, Payable Date, Date of Last Dividend Increase, Percentage Increase of Last Dividend Raise.  My desire to import certain stock dividend data points has led me to explore the creation of a proprietary solution:  The Dividend Meter Data File.

The Dividend Meter Data File – a Potential Future Solution

Often, the easiest solution of extracting meaningful information from a larger pool of data is to utilize an existing formula within both Excel and Google Sheets:  Index and Match.  It’s possible to upload or import a massive data file into Excel or Google Sheets on one particular tab, and then use another tab to quickly and efficiently extract what you need to replicate, even enhance, the functionality of the Dividend Meter spreadsheet using Index and Match.  My own current spreadsheet is using this method now, and you can see how it works by submitting your email address in the “View the Spreadsheet” box to the right.

You’ll notice a new tab on my spreadsheet (shown below), “Dividend_Meter_Data_File”.  The Dividend Meter Data File tab is auto-populated with data using a simple Google Sheets ImportRange formula:

=IMPORTRANGE(“1WXa9VSIm_I8H44sLRwAH–Xp9hLpnzY7_Q7BxlGAlto”,“Sheet1!A1:W5000”)

The strange code between the quotation marks in the above formula references a master data file that is currently being populated by a new partner of mine, an aspiring finance student who has started the work of manually (to avoid any violations of Terms of Service) collecting and verifying stock dividend data.  Eventually, access to the data file could be made available to Dividend Meter subscribers for a very low monthly fee.  But first, many hours of work need to be invested in building and keeping the data up-to-date.  Stay tuned for further updates…..

The New Dividend Meter Spreadsheet!

 

4 Comments

  1. Dividend Daze

    Sorry to hear about the API stuff being shut down. I heard about that not too long ago myself. I have been trying to find ways to import dividend information as well but with little success so far. Hope you figure out some good alternatives. The dividend meter seems like a big success among the community.

    Reply
  2. HelpfulHand

    Hey,

    About getting the dividend data, I’ve updated to use:

    =index(split(index(importhtml(CONCATENATE(“https://finance.yahoo.com/quote/”,$C2, “?p=”, $C2),”table”,2),6,2),” “),1,1)

    Gathered that from a another thread.

    Reply
  3. timeinthemarket

    Sorry to hear about the API change, that sucks. I wasn’t too happy about the google finance change either and I’m looking for other spots for data.

    Reply
  4. Dividend--Portfolio

    First of all, congrats on another successful November DM.

    Now, as you know, I credit the catalyst to creating my blog to the dividend meter spreadsheet tutorial you list on your site. So, I was very much affected by the fact that the API service is no longer available. I actually implemented the manual fix you’ve identified where I just manually put in the dividends in my spreadsheet. It then automatically calculate the yield. I just ensure periodically that the amount of dividends I’m receiving per share is accurate. It’s not ideal, but it’s the next best thing.

    I’m looking forward to seeing what fix you come up with.

    Reply

Leave a Comment

Your email address will not be published. Required fields are marked *