·

How I Track Dividend Income in Excel

This article outlines the step-by-step process to track dividend income by creating a dataset in Yahoo Finance and inserting it into Microsoft Excel. In the almost five years of sharing my investments on this blog, I’ve never shown exactly how I track dividend income.

This article lays out the steps (with screenshots) I take each month to import dividend data into my tracking spreadsheet, estimate forward dividend income, and easily capture dividend increases.

My forward 12-month investment income (F12MII) is a single number that represents how much I earn annually from dividend stocks, my rental property, interest on cash, and real estate crowdfunding.

I share the F12MII number on my portfolio page.

My goal is for the F12MII number to eventually cover most of our essential living expenses before I retire by age 55. That way, we can live off of a sustainable income stream and use retirement accounts for discretionary spending.

I’m also using the no-fee online broker M1 Finance (read my review here) to further diversify my income streams. It’s perfect for beginner to intermediate investors. 

How to Track Dividend Income with a Microsoft Excel Spreadsheet

The way I track dividend income has remained mostly the same since 2003, but I’ve adapted my spreadsheet to account for new income streams and to automate some tasks.

I pay to use Microsoft Excel because I like it and use it so much. Google Sheets is free and sufficient for most, but I still prefer Excel.

I’ve set up the spreadsheet to automatically calculate my monthly and annual dividends in the expected month of payment. All I have to do is retrieve a dataset and add or remove any stocks when my portfolio changes.

Since I’ve always had multiple brokerage accounts, I keep a consolidated portfolio view on Yahoo Finance. That is where I create the dataset for input into my spreadsheet.

I’ve been using Yahoo Finance since about the first time I tried Netscape in 1995. Here’s the step-by-step process to track dividend income using Yahoo Finance and a spreadsheet.

Step 1 – Create a Yahoo Finance Dataset

Yahoo Finance My Portfolios is a free functionality that allows you to track the stocks in your portfolio, the number of shares you own, and the cost basis for each holding. You can manually input the data tax lot by tax lot, or just copy the total shares and cost basis from your regular brokerage.

They can even link to your online broker now, but I have not tried that functionality yet.

Start by creating a custom portfolio view, or click Create New View. Pick all the fields you want to capture for your spreadsheet. I customized a portfolio view called Performance. Here’s what it looks like:This article outlines the step-by-step process to track dividend income by creating a dataset in Yahoo Finance and inserting it into Microsoft Excel.

You can see all the fields I want on the top row. Most importantly, I track shares, forward dividend rate, and forward dividend yield. These fields will eventually be used to calculate F12MII.

Next, click the Add Symbol tab to input all the stocks you own. You can add multiple symbols at a time.

This article outlines the step-by-step process to track dividend income by creating a dataset in Yahoo Finance and inserting it into Microsoft Excel.

Once all the stocks are listed in the portfolio, enter each tax lot purchase or the total shares (required), cost basis (not required), and trade date (if you like).

Click the My Holdings tab, then check the box next to the holding you want to add shares and cost basis for.

This article outlines the step-by-step process to track dividend income by creating a dataset in Yahoo Finance and inserting it into Microsoft Excel.

Do that for each of your holdings and you’ll have a functioning live data source.

Once your portfolio is complete and saved, you can copy the numbers as a dataset.

Click the name of the portfolio view you’ve created (mine is Performance) so that the data is refreshed.

You have the option to export the data to a .CSV file. That’s never worked right for me. Yahoo Finance does not export my customized view. It exports a default view.

So I just use my mouse to copy the table, all the way down to the bottom of the portfolio. The copied data is highlighted.This article outlines the step-by-step process to track dividend income by creating a dataset in Yahoo Finance and inserting it into Microsoft Excel.

And now the dataset is saved to my clipboard.

Seeking Alpha, The Motley Fool and some others have similar free portfolio tracking portfolio tools. Experiment to see what works for you. If you only used one online broker, you should be able to create a sufficient dataset for export within your account.

I use the same technique to update the Dividend Aristocrats Ranking list and the Debt-Free S&P 500 resource.

There are also ways to get live quote data into Excel with APIs and code. I’ve experimented with these, but never invested the time to commit to it.

Step 2 – Paste the Dataset into Excel

The next step is to get your dataset into Excel. Now that it’s already on your clipboard, open an Excel sheet and do a Home/Paste Special/As Text. Or simply right-click and paste as unformatted text.

Pasting without formatting will clean the data, removing any unwanted links or tags.

In my master spreadsheet, I have a tab called Raw, for raw data. I paste it there. The first time you paste the data, capture the field headings too. But after that, you only need the stock symbols and data.

This article outlines the step-by-step process to track dividend income by creating a dataset in Yahoo Finance and inserting it into Microsoft Excel. The most important fields are the Shares and DIV/Share fields.

Step 3 –  Build Out Your Spreadsheet

Now that you’ve created a Yahoo Finance portfolio, update it every time you buy or sell a stock so the table is always ready to import into your Excel workbook.

If you build out your spreadsheet properly, pasting the data will automatically update the rest of the spreadsheet.

Of course, building out your spreadsheet takes the most time. But once the framework is set, changes are only needed when you buy a new stock or completely sell a holding.

I’ve got a bunch of different tabs along the bottom of my Excel workbook, each serving a different function. But the main two tabs are the Schedule and 2018 Received tabs. Those are where I track F12MII and income received for the year.

Here are the tabs on my spreadsheet and a brief description of each:

This article outlines the step-by-step process to track dividend income by creating a dataset in Yahoo Finance and inserting it into Microsoft Excel.

For the Cash, Rental, LC (LendingClub) and RE CF (Fundrise) tabs, I still update them manually each month. The data all feeds into the Schedule tab. We’ll look at that next.

Step 4 – Create a Dividend Income Schedule

I’ve seen dozens of dividend bloggers create a similar schedule for tracking dividends. Everyone has their own version. Whatever works for you is fine.

The Schedule tab is where I combine all of the dividend data that comes from the Raw data tab, which was retrieved from Yahoo Finance. I first use the Input Data tab to clean up the Raw data, making it easier for the Schedule tab to digest.

I use the Schedule tab to track dividend income increases and scheduled payments.

Each year I start with a fresh page noting the beginning dividend amount (Start Div). That’s a static number.

Then the current dividend amount (Curr Div) is linked to the Input Data tab. Using these two columns, I can identify when a company increases their dividend automatically. When Yahoo Finance updates the dividend, it’s captured in my spreadsheet.

The Div Δ column calculates the percentage increase/decrease. Conditional formatting turns the cell green or red to highlight an increase or decrease.

By tracking and highlighting increases this way, I don’t have to watch the news releases for dividend increases. They just show up here the last day of the month.

I also add the dividend increase date from the previous year so I know when to expect the next increase for a company. The final two columns are Per Div (dividend amount per period), and # of Shares (linked to Input Data).

This article outlines the step-by-step process to track dividend income by creating a dataset in Yahoo Finance and inserting it into Microsoft Excel.

I then multiply # of Shares by Per Div in the month when the dividend payment is expected. The dividend payment date is usually paid quarterly.

You can find the month of expected dividend payment on the Yahoo Finance Statistics page. Here’s an example for ABT, the next expected payment is on August 15th.

I copy the same formula to the three other months the dividends are expected. For ABT, that’s November, February, and May. Do this for all holdings. It’s only required the first month a new dividend stock is purchased. After that, it updates when I dump a new dataset into the spreadsheet.

This article outlines the step-by-step process to track dividend income by creating a dataset in Yahoo Finance and inserting it into Microsoft Excel.

The expected forward dividend income is then totaled in column “T“.

Lastly, at the bottom, I link to the Cash (Capital One 360 accounts), Rental, LC, and RE CF tabs. The estimated forward monthly income amounts from each tab are added, then everything is summed at the end, giving me a grand total at the very bottom.

That’s the F12MII number I update readers with every quarter. These values are carried over to the Received tab.

This article outlines the step-by-step process to track dividend income by creating a dataset in Yahoo Finance and inserting it into Microsoft Excel.

Step 5 – Create a Dividends Received Sheet and Chart

Inspired by other dividend bloggers, I started to track dividend income received in 2015. This tab simply contains the dividends I receive each month plus other income streams. I still gather this manually, but the data is easily retrievable from my online brokers.

Here’s a look at the bottom part of the tab showing income received for stocks and other income streams:

This article outlines the step-by-step process to track dividend income by creating a dataset in Yahoo Finance and inserting it into Microsoft Excel.

Once the numbers for the month are complete, I copy rows 54-59 for the latest month over to another table which I use to create the chart I share each quarter.

This article outlines the step-by-step process to track dividend income by creating a dataset in Yahoo Finance and inserting it into Microsoft Excel.

This article outlines the step-by-step process to track dividend income by creating a dataset in Yahoo Finance and inserting it into Microsoft Excel.

You may notice a recent downward trend. This last quarter was disappointing. I’ll fill you in on what happened in the next quarterly update in early July.

If you’ve read this far and would like to check out my spreadsheet, here’s it is. I’ve sanitized it and added dummy data to show how it works.

Track Dividend Income with Google Sheets

If you prefer the free Google Sheets over Microsoft Excel, all of what I’ve done in Excel can be replicated in Sheets.

Google Sheets is also cool because certain fields can update with live data using the =GoogleFinance function. However, data options are limited.

I used to use Google Sheets to embed spreadsheets on this website. Just copy the Excel data into a Google Sheet and paste the embedded code into a WordPress installation.

The portfolio table, Dividend Aristocrats Ranking tool, and the Debt-Free S&P 500 List are no longer using Google Sheets. I now use a WordPress plugin called Tables by Supsystic.

Live data from Yahoo Finance used to integrate well with Google Sheets, but that functionality no longer works. There’s another way to do it now.

If you’re looking for a spreadsheet to track dividend income for your own portfolio but don’t want to create your own, I recommend checking out this one from TwoInvesting.com. The curators of the blog and spreadsheet have built an awesome free tool with live data integration. They explain how it works better than me.

It takes some techie stuff to get it set up. But the instructions are good, it works well, and they update the tool when needed. Check it out.

Conclusion

Fellow spreadsheet wizards may find article really interesting. The rest probably didn’t make it this far.

Spending the time to track dividend income is a long-time hobby of mine. I like measuring my progress and estimating forward income. It’s one of the foundations of my retirement plan. My way isn’t the fanciest, but it gets the job done.

But lately, I’ve been questioning whether it’s necessary every single month. I’m considering only updating my spreadsheet every quarter instead of monthly starting next year.

I used to track my net worth every month until Empower made that obsolete by automating the process every day.

Only brokers are making our jobs easier by adding new tools. But as long as I have multiple brokerage and alternative income streams, I’ll keep building my spreadsheet.

Photo by Christopher Sardegna via Unsplash


Favorite tools and investment services right now:

Sure Dividend — A reliable stock newsletter for DIY retirement investors. (review)

Fundrise — Simple real estate and venture capital investing for as little as $10. (review)

NewRetirement — Spreadsheets are insufficient. Get serious about planning for retirement. (review)

M1 Finance — A top online broker for long-term investors and dividend reinvestment. (review)

Comments Welcome!

This site uses Akismet to reduce spam. Learn how your comment data is processed.

10 Comments

  1. Chris Imming says:

    Thanks for posting this

    1. Has Verizon now made this Yahoo! Finance method redundant?

      1. Yahoo Finance has undergone a lot of changes in the past two years. I use it because the data is easy to structure and download. MS Excel’s new stock function doesn’t work for dividends yet, though power query can import the data. I haven’t spent the time to do that yet, as the method described above still works well.

  2. Thanks for detailing it!

    I also use spreadsheets to track my dividend income as well. Not sure I would call myself a spreadsheet wizard, but I don’t need budgeting/tracking/money management websites, I can do it all myself on my PC.

    It’s a lot of fun (for me, anyway), to do these spreadsheets myself. Keeps my technical skills up to date, and I periodically think of new things to track. I like what you did tracking income by month, I track them by security.

  3. I understand that Excel doesn’t auto update dividend rates. But have you found a way for that information to update your spread sheet instead of having to do it manually (click and past) everytime from yahoo?

    1. Hi,
      Yes, there are ways to do it. I assume that Microsoft will put this feature into Excel eventually. However, to do it now, there are two ways that I know of. I just haven’t spent the time getting it set up.

      The first way is using an API called IEX as explained by Two Investing. They have a spreadsheet you can download to use, or help you figure out how to do it. This takes some time to set up, and requires a subscription. But is probably the best way.

      Another option is to use the Excel Power Query function to get dividend data. I’ve experimented with this, but haven’t spent enough time to make it perfect and share. You can find YouTube videos and blog posts about it, but it’s a bit more complex. Automation is the way to go if you have the patience to make it right. Good luck!

  4. I’m having trouble figuring out how you calculate the F12MII month over month in the table used to build your graph “Monthly Income Received vs. Projected Monthly Income.” Could you please provide more details? Thank you very much for this article, it is very helpful.

    1. Hi. You can see an updated version of the F12MII table on my Portfolio page. To make this chart, I first check the accounts each month to determine how much dividends I actually receive. Then my F12MII is a calculated projection based on current dividend payouts. So I’m looking at the current dividend rate, frequency (usually quarterly), and the looking to see which months the dividend is paid out. So with pretty good accuracy, I know today what I’ll earn in the next twelve months. The table above the chart is the data for the chart. Then I use a combination chart (bar and line) to compare monthly actual income (bars) to average monthly projected income (line). The image with “Start Div” and “Curr Div” highlighted in red is where I calculate the F12MII. I hope that helps to clarify.

  5. Mark Sanders says:

    I like your article and have subscribed to stay in the loop.

    I, too, have tracked my portfolio in multiple brokerages in Sheets. Having done this for over 10 years, I first started when I also used Quicken. I then moved on to Yahoo and Google Finance using their API and function calls, but success was hit and miss. Looking for alternatives in the past couple of years, I have been working with Finviz and IEX. Well, most recently, Finviz calls have failed and IEX is reportedly now charging for data. I don’t especially want to pay for stock data as up to now, I have just wanted the current dividend.

    So, as of today, my income tracking is broken and i am looking for alternatives to fix it.

    Thank you for sharing!

    1. Same thing happened to me. I started using Google Sheets and the Yahoo Finance calls. That broke. I’ve spent some time exploring IEX, but it’s frustrating any time I try something new and it breaks. Hopefully, one of the more reliable services will add more features (maybe GoogleFinance, or possibly Excel will expand stock data). Power Queries in Excel can do it, I believe. But again, spending the time to get it right, then hoping it doesn’t break can be frustrating. I’d love to automate this and a few other spreadsheets in the future. But for now, I’m ok with this technique.