Retire Before Dad

    • Start!
      • About
      • RBD Story
      • Featured on…
      • Archive
      • Portfolio
      • Guest Post Policy
      • Contact
      • Terms & Privacy Policy
      • Home
    • IPOs
    • Reviews
      • Sure Dividend Review
      • Yieldstreet Review
      • Fundrise Review
      • M1 Finance Review
      • Motley Fool Stock Advisor
      • Motley Fool Rule Breakers
      • AcreTrader Review
      • Masterworks Review
      • Roofstock Review
      • PeerStreet Review
      • EquityMultiple Review
      • Virginia 529 Review
    • Resources
      • Passive Income Ideas
      • Best Brokers for Dividends
      • Dividend Aristocrats
      • Debt-Free S&P 500 Stocks
      • Best Real Estate Crowdfunding Platforms
      • Affiliate Programs And Blogging
      • How To Start An Online Business
    • Recommended
      • Net Worth Calculator
      • Tools
      • Blogroll
      • Dads Blog Money
    • Best Cards
      • Travel Rewards
      • Cash Back
      • Small Business
      • Airline Rewards
      • Hotel Rewards
    Investing· Personal Finance· Stocks

    How I Track Dividend Income in Excel

    By Retire Before Dad

    This page may contain links to our partners. RBD may be compensated when a link is clicked. See the full disclosure here.

    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.

    The job of tracking dividend income just got a little bit easier. My primary broker, TD Ameritrade, has a new tool to help predict annual dividend income automatically. At the end, I’ll share a few screenshots of the tool.

    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 (PeerStreet and 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.

    The Income Estimator by TD Ameritrade

    After all these years being a TD Ameritrade customer and tracking dividend income in my own spreadsheet, TD Ameritrade built a new Income Estimator tool to do the job for me.

    If you have a TD Ameritrade account, just go to the Stock Profile page of any stock. You’ll find a link to it near the top on the right side.

    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.

    Click the Income Estimator link. A new browser will open. You can choose your holdings, a watch list, or any individual stock. For this example, I’m using my actual portfolio.

    The tool creates this view of forward 12-month dividend income:

    Further down, each holding is listed with helpful data. The quantity field can be modified to simulate a purchase or sale.

    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 tool was launched toward the beginning of January. I’m a big fan of this and plan to use it more frequently. Hopefully, I can find a way to integrate this into my spreadsheet. Or at the very least, include in my quarterly income updates.

    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 Personal Capital made that obsolete by automating the process every day.

    Tools like the TD Ameritrade Income Estimator and may end up doing the same. But as long as I have multiple brokerage and alternative income streams, I’ll keep building my spreadsheet.

    Photo by Christopher Sardegna via Unsplash

    Please Share!

    • Click to share on Twitter (Opens in new window)
    • Click to share on Facebook (Opens in new window)
    • Click to share on Reddit (Opens in new window)
    • Click to share on Pinterest (Opens in new window)
    • Click to share on LinkedIn (Opens in new window)
    • Click to email a link to a friend (Opens in new window)

    Favorite tools and investment services right now:

    Credible* - Refinancing makes sense if you can lower your mortgage payment amount by a significnt amount. Credible makes it painless.

    Personal Capital - A free tool to track your net worth and analyze investments.

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

    Fundrise - The easiest way to invest in high-quality real estate with as little as $10 (review)

    *Advertising Disclosure: RBD partners with Credible which offers rate comparisons on many loan products, including mortgage refinances and student loans. This content is not provided by Credible or any of the Providers on the Credible website. Any opinions, analyses, reviews or recommendations expressed here are those of the author’s alone, and have not been reviewed, approved or otherwise endorsed by Credible. RBD is compensated for customer leads. Credible Operations, Inc., NMLS Number 1681276, not available in all states. 320 Blackwell Street, Suite 200 Durham, NC 27701.
    Retire Before Dad

    I’m a 45-year-old IT professional, investor, and blogger based in the Washington, DC metro area. My primary financial goal is to retire at age 55, one year before my Dad retired. I write about how to build income streams so you can explore the unusual. Read the whole story HERE.

    Filed Under: Investing, Personal Finance, Stocks Tagged With: dividend growth investing, dividends, passive income

    Comments

    1. Please note: Responses are not provided or commissioned by the bank advertiser. Responses have not been reviewed, approved or otherwise endorsed by the bank advertiser. It is not the bank advertiser's responsibility to ensure all posts and/or questions are answered.
    2. Chris Imming says

      June 21, 2018 at 9:59 am

      Thanks for posting this

      Reply
      • Matt says

        August 2, 2020 at 5:40 am

        Has Verizon now made this Yahoo! Finance method redundant?

        Reply
        • Retire Before Dad says

          August 2, 2020 at 9:40 pm

          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.

          Reply
    3. Smile If You Dare says

      June 21, 2018 at 11:44 am

      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.

      Reply
    4. Darius says

      September 16, 2020 at 5:32 pm

      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?

      Reply
      • Retire Before Dad says

        September 17, 2020 at 4:55 pm

        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!

        Reply
    5. John says

      November 23, 2021 at 11:48 am

      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.

      Reply
      • Retire Before Dad says

        November 23, 2021 at 2:29 pm

        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.

        Reply
    6. Mark Sanders says

      April 10, 2022 at 12:52 pm

      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!

      Reply
      • Retire Before Dad says

        April 11, 2022 at 8:15 am

        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.

        Reply

    Comments Welcome! Cancel reply

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

    Services I Use Every Day

    Personal Banking: Wells Fargo
    Travel Credit: Chase Sapphire Preferred
    Primary Savings: Marcus
    Primary Broker: Fidelity
    DRIP Broker: M1 Finance
    Biz Banking: Wells Fargo
    Biz Credit: Chase Ink Business Preferred
    Net Worth Calculator: Personal Capital

    Home
    About
    Featured on
    Resources
    Website Terms/Privacy Policy/Full Disclaimer

    ADVERTISING DISCLOSURE: This website engages in affiliate marketing. This means that if you use an affiliate link to make a purchase, the website will receive a commission on that purchase. All efforts are made to ensure that affiliate links are disclosed in accordance with the FTC. Retire Before Dad has partnered with Cardratings for our coverage of credit card products. Retire Before Dad and CardRatings may receive a commission from card issuers. The Website uses Mediavine to manage all third-party advertising on the Website. This website is a participant in the Amazon Services LLC Associates Program, an affiliate advertising program designed to provide a means for sites to earn advertising fees by advertising and links to Amazon.com.

    Disclaimer

    Read the full Disclaimer policy here.

    Opinions, reviews, analyses & recommendations are the author’s alone, and have not been reviewed, endorsed or approved by any of these entities. We have made every effort to ensure that all information on this website is accurate. We make no guarantees regarding the results that you will see from using the information provided on the website. We are individual investors, not financial advisors, tax professionals or investment professionals. All information on the site is provided for entertainment and informational purposes only and should not be considered advice. Do not make investment decisions based on the information provided on this website. This website may discuss topics related to finance and investing. The information provided on this websites is provided “as is” without any representations or warranties, express or implied. The website makes no representations or warranties in relation to the financial and investing information on the website. You must not rely on the information on the website as an alternative to advice from a certified public accountant or licensed financial planner. We assume no responsibility for errors or omissions that may appear in the website. You should never delay seeking financial advice, disregard financial advice, or discontinue professional financial services as a result of any information provided on the website.

    Copyright © 2022 Retire Before Dad · Custom site by Moonsteam Design