With various accounts being used to invest from Brokerage, Roth and HSA’s I find it convenient to have all that data in one location. This is especially true if your accounts are spread across multiple custodians. Google has a vast array of tools at your disposal and most are free to use and just as powerful as paid versions. Google Sheets is similar to Microsoft Excel and can crunch the numbers for you so put away the pencil and ledger 🙂
With some help I have put together a nice little Portfolio Tracker with a Dashboard that requires only a few entries from the end user to bring it to life. The data you enter is then using sites like yahoo finance and finviz to pull real time stock info.
The Dashboard Sheet contains all your totals and charts for an overview of your accounts.
- Top Left has your account balances along with your top ten holdings
- Top Center interactive ticker info, allows you to select any of your holdings to view current info along with a 12 month chart
- Top right is your industry breakdown pie chart with running total underneath
- Center and Bottom dividend focused charts a running monthly payment chart etc.
The Transaction Sheet this is where most of your data entry takes place. You have three types of transactions – buy, sell or dividend. At this time the dividend option will only work if you DRIP, if you receive it as cash there is no need to enter as it does nothing. The total is auto calculated and all that’s left to do is select the correct account from the last dropdown menu. This information will then transfer to the account you selected. If selling a position you would use a negative notation for share total. The blank copy has 4 accounts, more can be added if needed.
The Account Sheets this would be were all your transaction entries are stored and the numbers are crunched to give you your yields, gains etc. The only data entry needed on the account pages are ‘Div Rate’ which is column L, This would be the quarterly dividend payment for said ticker, this will be used in conjunction with column Q to calculate Forward Dividend. * Note this is based on quarterly payments, if your holding pays out monthly the formula in Q needs to be adjusted to multiply by 12 instead of 4.
The industry in column S will also need to be inputted, I have listed them in a convenient dropdown.
Div 2023 Sheet this is a dividend payout calendar of sorts and will require the user to input data. The left column contains the tickers imported from your account sheets and the colored cells would need to be adjusted to your specific holdings payout schedule.
I hope you find this tool helpful in tracking your portfolio. Get your free copy Dividend Portfolio with DashboardÂ