Keeping track of all your investments, dividend income, as well as your projected growth is a difficult task. Luckily, I stumbled onto a fine spreadsheet and decided to create my own to really make some strides to financial freedom.
Why create my own tracker?
So if there are already some dividend portfolio tracking spreadsheets out there, why did I bother creating my own? Good question! The thing is, no matter how incredibly useful these tracking spreadsheets were, they simply didn’t do everything I needed, or they required a little too much manual work.
I wanted something quick to manage but provided everything I required from a spreadsheet to track an international portfolio. Here are a few requirements that were not met by existing dividend portfolio trackers:
- Track international stocks in different markets and currencies.
- Combine multiple currency transactions on a single sheet.
- Only require data to be inserted once.
And thus my own dividend portfolio tracker spreadsheet was founded. Using a modded version of Kyith’s incredible Investment Moats dividend tracker as inspiration, I set out to create an even better spreadsheet for dividend investors to rely upon.
What makes my tracker so good
The spreadsheet I managed to complete is really good and that’s not just me saying that. It’s made by a dividend investor for dividend investors. Should you be searching for financial freedom as I am, you’ll want to check this Google Sheets document to see just how much of an improvement it makes to your portfolio.
I’ll go over some feature highlights to showcase just how awesome it really is.
Track all your stock, share, and bond transactions
Google Sheets is a powerful tool and it allows us to enter an insane amount of data. This can be used by formula to produce some wild results for tracking a whole manner of things, including stocks and shares.
With this Google Sheet, you can enter all your transactions. I’m talking about sales, purchases, stock splits, dividend income, and more. Everything you do with your investment portfolio can be entered and tracked with this spreadsheet. No longer do you simply update stock totals in a single table. It’s almost like magic.
This can provide you with extended insight. It’s handy to know just how much in dividends you’ve received from a stock hold in total, but it’s a whole different ballpark to dive in and see just how much you earned a specific year. In fact, this Google Sheet will even let you track your portfolio worth each day, so you can compare at a later date.
Keep everything simple
Dividend investing is fun, but it should also be light on your time. This game is all about getting your money to work for you, not your time since that’s even more precious and in limited quantity. This is why the spreadsheet will automatically pull down the latest stock prices from Google. If you’re trading UK stocks, it’ll even work out values, taking into account GBX. (Us Brits continue to do things a little differently to everyone else.)
The Google Sheet will take all your secondary currency transactions and convert the final values into your base currency. All using a single sheet. This allows us to have dedicated sections for each market without having to separate transactions, making everything easier to enter, manage, and reference. For instance, I like everything in GBP, thus my U.S. and European transactions are converted to GBP for everything to be managed accordingly.
As an added bonus, I make it so you can enter conversion rates for each transaction, allowing the spreadsheet to keep track of how your portfolio is performing with enhanced accuracy. Other spreadsheets will use current conversion rates for all transactions, which doesn’t make much sense for accuracy since currencies constantly fluctuate.
How to get started
Simply make a copy of one of my default templates by hitting the corresponding links below:
The major difference between these three templates is the base currency. The U.S. one is tailored to USD, while the UK one features everything converted to GBP. The same goes for the European spreadsheet and the Euro. Simply choose the one that matches your base market.
This spreadsheet has some highly sensitive formula configured, so it’s important you do not edit grey fields. Only input data in white fields. Grey = bad. White = good.
Summarizing the basics
The first tab in the spreadsheet is your portfolio summary. It’ll be empty right now but fear not as this will automatically be populated with data, pulled from all the markets you hold shares in.
We track important metrics here, including:
- Portfolio cost.
- Portfolio value.
- Unrealized gains/losses.
- Realized gains/losses.
- Expected annual dividends.
- Collected dividend totals.
- Average dividend yield.
Some charts can be found below this table. The first is dedicated to your portfolio cost and value over time. This data is pulled from the history tab, which will be automatically generated every day.
The second is dedicated to your dividend collection, split into years. This chart will allow you to follow your progress in bolstering your dividend portfolio, collecting higher figures year-over-year. Once again, this data is pulled from the history tab.
The third chart represents your sector diversification, which is pretty important for a healthy, robust stock portfolio. The saying goes, “don’t put all your eggs in one basket” and that’s exactly what this chart will help avoid.
Fourth up, we have realized gains and losses. This pulls data from the history tab, but only represents profits and losses made from stocks sold.
Securities is next up, representing how much weight you have in each market you own stocks. This isn’t incredibly important data, but interesting to see just how reliant you are on specific markets.
Lastly, we have probably one of the more important charts, which represents the diversification of your stocks. It’s better to not be too heavy on a specific stock. Try not to go above 15% if possible. Being more diverse allows you to better weather to storm should markets dip.
Configuring market trackers
The next three tabs are dedicated to EU, UK, and U.S. markets. You can configure these to your liking, adding more tabs for additional markets, or changing the default tabs altogether. It’s completely down to you on which markets you wish to track and engage in.
We’ll take a look at the UK as an example, though the premise is the same for all markets. Here’s what each column represents.
- Type: The type of stock.
- Stock: The company name.
- Ticker: What you use to track the stock.
- Industry: The company sector.
- Last Dividend: The most recent dividend payout.
- Dividend Calendar: The frequency of dividend payments.
- Inventory: How many shares owned.
- Cost Per Stock: The average price of all owned shares.
- Total Cost: How much all shares cost in total.
- Market Price: Current share price.
- Yield on Cost: Dividend yield based on initial share cost.
- Last Price Yield: Dividend yield based on last price.
- Annual Dividend: Total expected dividend per annum.
- Dividends Collected: How much has been collected to-date.
- Unrealized Gains/Loss: What you’d make if shares are sold.
- Realized Gains/Loses: What you’ve made from previous sales.
- Total: Everything added together.
- Market Value: How much all the shares are worth.
It’s important we add all the stocks we own or are interested in. Considered these tabs as your stock shortlist of sorts. Only populate fields that are white. Leave the grey fields be. In order to create new rows, simply add a new row, select and drag down to copy from the row above, then edit the first six fields.
It’s best practice to every week or so select the first row of grey cells and drag them down to the last row, resetting and recalculating everything per stock, just in case anything goes a little wrong with Google.
How to create your own market tabs
In order to add your own market tab — you may want Canada, for instance — simply create a duplicate of one of the existing market tabs and name it to the desired country code (UK, US, etc.). Enter into this new tab, clearing out all the white cells. (I will keep repeating how important it is to leave the grey cells alone.)
There are a few formula alterations that will need to be made in order to make the necessary conversions and calculations work, especially since we’re using our own base currency for summarizing our portfolio. Any jank with the numbers could be caused by incorrect calculations, which leads to a bad day.
- Add the local currency conversion rate for the market you wish to add to the Data tab. We use Google for this (
=GoogleFinance("CURRENCY:CADGBP")). Change GBP to your base currency, if you’re not in the UK.
- Add the currency to the list in
H14on the Data tab, so you can select it on the Transactions tab. (You may need to redo the data validation for the “Currency” column if it’s not showing all currencies on the Transactions tab.)
,"&TEXTJOIN(",",TRUE,US!B2:B),"to the cell
A14on the Data tab before
USto the country code you’re using for the newly added tab.
- Change the currency for the “Last Dividend” column to the local currency. (CAD for Canada, in our example.)
- Change the cell references in “Annual Dividends” column to match the cell with your currency conversion rate. Each
Data!D$16reference will need to match your conversion cell. Example being
Data!D$17if we added a new one to the Data tab.
G2="USD",H2,to each row under “Transacted Value” column in Transactions tab before
And that should be that! You can now add all your stocks to this tab and throw in any transactions you’ve already made to make sure everything works accordingly. With a new tab created, you’ll need to adjust the main summary page to add in the new market.
Registering your transactions
This tab is where all the magic happens. It’s a special sheet that calculates everything you do with your portfolio. Whether you’re buying, selling, collecting dividends or splitting stock holds, it’ll keep track of everything. All other tabs reference data from this tab, so it’s important we correctly insert all data.
It may appear a little daunting at first, but it’s incredibly easy to use. Once again, we’re only editing white cells here.
- Date: Date of transaction.
- Type: Are you buying or selling?
- Stock: Choose from the drop-down menu.
- Units: The number of shares part of this transaction.
- Price: The market price for buying/selling or dividend payout.
- Fees: Stamp duty, etc.
- Currency: Choose from whichever currency the stock is in.
- Conversion: Note the exact conversion rate used in the transaction.
- Stock split: Usually safe to leave it as 1.0 unless your splitting stocks.
- Notes: Add whatever you wish here, but it’s optional. Maybe you got a free stock or something.
That’s all you need to add with each transaction. Once you’ve filled in the white cells, simply drag the grey columns from the above row down to copy across all the formula as you work down the sheet. Here’s what each column is doing:
- Previous Row: Checks what the previous row is for this stock (if available).
- Previous Units: Counts how many units were in previous transactions.
- Cumulative Units: Total units for this stock.
- Transacted Value: Converted cost of the transaction from stock currency into your base currency.
- Previous Cost: The cost of the previous transaction.
- Cost of Transaction: If selling, this shows the cost of transaction.
- Cost of Each Unit: Works out the cost of each share if selling.
- Cumulative Cost: Adds up all costs thus far for stock.
- Realized Gains: If selling, works out realized gain/loss.
- Transaction Yield: The yield of the transaction, comparing sell and buy. prices
- Cash flow: Total amount of cash for the transaction.
That’s all you need to do with the transaction tab.
View your history
The history tab essentially takes your daily report on the summary tab and copies it here for archiving and to produce our fancy charts. This is completely automatic and optional. It does require you to spend two minutes setting up a script to run automatically each night, but it is worth it to provide some motivation as you witness the growth of your portfolio.
You don’t need to edit this tab, unless you plan on changing or adding to the years present for dividend collection history on the right-hand-side.
How to automate your history
- Choose “Tools” > “Script Editor” from the menu.
- Choose “Project Triggers” from the menu.
- Click on “+ New Trigger” to add a trigger.
- Make sure your options reflect what’s shown in the below screenshot.
- Hit save.
The script will now run automatically between 11pm and midnight each evening, saving how your portfolio performed that day. You’ll then be able to check the history tab to see how far you’ve come. This is why we’re using Google Sheets. The sheer functionality available is amazing for tracking our finances.
Sort out your data references
The Data tab is full of cool stuff. To start with, we’ve got references for data validation drop-down menus for the type of stock and currencies. The dividend monthly calculator selection is also present here. Stock selection is an interesting part of this tab as you simply cannot add anything underneath it. column D is effectively reserved for this function.
This pulls all the companies you follow across all the tabs so the transactions tab can allow you to choose from all your stocks. Anything added to these tabs is added to this list. The only issue is it’s not listed alphabetically, which is where column C comes into play. This takes column D and sorts it. So like column D, you shouldn’t add anything below this list.
Selection elements governs your transaction type. Sectors is configurable, allowing you to choose what to use for better showing portfolio diversification. Each market tab references this so you can choose the sector for each stock, which is then reflected in the summary tab pie chart. Pretty cool, huh?
The conversions found a little down in the first few columns are live rates pulled from Google. We use these to correctly convert all the transactions and stock listings to your base currency, allowing you to check how your portfolio is doing with better accuracy.
Is there an Excel version?
Not yet. But one is certainly on the cards. I’m looking into Excel’s ability to pull down stock pricing and the like, but we’ll have to see how easy the rest of the spreadsheet is in the conversion process.
Where did you get the idea?
I must give a bunch of credit to InvestmentMoats. They’re the brains who created the code to copy across the history to a new tab, as well as some of the excellent formula I used to create this tracker.
This is awesome! Can I donate?
Glad you like my tracker! I hope it helps as many dividend investors as possible. I do not accept monetary donations, unfortunately. But you could help by reading my blog and spreading the word. 🙂
Do you offer support?
Sort of. Since I’m not selling a service or product, I won’t be dedicated too much time helping others with this tracker. Should you come across problems, please feel free to reach out to me and I’ll try my best to help. If enough people follow this blog and use my tracker, I may open up a Discord channel or forum of sorts for discussion and support. Please let me know if this interests you.
- Added two new charts on the summary tab.
- Reordered the securities list and tabs to reflect the different tracker editions.
- Made the data tab easier to browse and use.
- Added new monthly dividend breakdown chart on the history tab.
- Switched the positioning of the annual dividend and transaction totals. Heavy stuff.