This post will be focused on presenting a tool for your marathon to 100K as opposed to my usual presentation of knowledge. If that sounds boring or useless, I’ll take that on the chin and see you next week.
I have spoken a lot in previous posts about dividends (see posts below for a refresher). Dividends are essentially free money you get from a company simply for holding shares of their company.
If you set up dividends as I have, using DRIP (Dividend Reinvestment Program), it is easy to lose track of your dividend gains over time. They get lost in the shuffle of your portfolio’s movement. It can be very useful to track your dividend gains over time not to just feel good about all that money, but also to keep track of how your dividends grow over time alongside your portfolio.
I started off performing dividend tracking manually by entering this info into a spreadsheet periodically, but this quickly got tiring. In my day job as a Quality Engineer, I was introduced to programming in Visual Basic for Microsoft Excel and Access. I quickly became excited by the possibility of automating a user’s tasks (probably the nerdiest thing you’ll hear all day). So I worked on automating the process of entering my dividend reinvestments.
This Excel spreadsheet essentially allows you to enter your dividend gains in a form instead of manually entering it in cells and such, and automatically enters, formats, and totals the data.
Above is an example of the sheet in action. For each year, just 2021 and 2022 here, it lists all of the months, their total dividends, and each dividend in that month. Along with each dividend’s ticker (representation of the company), amount, and brokerage it came from. Finally, at the bottom and top of each year is that year’s total.
Upon first opening the workbook, you may get a couple of pop-ups:
- Enable content (see above)
- Make sure to click “Enable Content”. This allows the macros to run.
- Macros disabled
- If excel disables macros for security, they need to be re-enabled to run the automation.
- The enable macros:
- Click File -> Options -> Trust Center -> Trust Center Settings -> Macro Settings -> Enable VBA Macros
This is required to run the automation. If you are curious about what is going on in the code behind the scenes, I have left the code unlocked. To view this, select your Developer tab, then Visual Basic. If you don’t have the Developer tab, enable this right click on any of your tabs in the ribbon at the top (eg Home, Insert, etc.) and click “Customize the Ribbon”. Finally, ensure “Developer” is checked in the list on the right. You may need to scroll to see it.
To enter the info, click the “log dividend” button in the top left of the sheet. This will bring up the small form shown above to enter the dividend’s info. Note: the form defaults to the current year.
Yup. It’s that simple.
This program is compatible with both Windows and MacOS computers that have Microsoft Excel installed.
If you have any issues or suggestions with using the spreadsheet, please reach out via the comments below so that others can see any issues & solutions!
Leave a Reply