A uk dividend tracker is a great way to keep track of your stocks, predict your yearly cashflows, evaluate companies, and accumulate wealth.
I built this handy automated UK dividend tracker in Google Sheets as I was not happy with the current options available out there, especially for UK investors. I personally use this to track my own investments. In this written tutorial, I will show you step by step how to use it.
If you prefer to watch a video, please check out the video tutorial I uploaded covering the entire setup process!
Click here to Download the Tracker
The Yellow Zones were designed to be the only areas that need to be edited. Everything else should be mostly automated.
1. Getting the Tracker Set Up
After gaining access to the tracker, the first thing you should do is make a copy.
File>Make a Copy
2. Adding Your API Key (Optional, I designed the sheet to still work without it)
Click the link to Alpha Vantage and EOD Historical Data and grab your free API code. You will receive an email shortly after signup
At the top of the sheet, click
Extensions>App Script
Replace to replace the two red areas with your two new API codes (keep it inside the quotes)
Click Save and then Run
You may delete this sheet when you are done, it is no longer needed!
3. Investment Dashboard
This is the main hub for overviewing your portfolio, come here to review your portfolio.
Â
- The Current Date row will update automatically. If you would like to track the performance of each, edit in the yellow line below.
- Cash – can be edited with your currently held Cash positions as a proportion of your overall investments
- Deposited – Currently deposited funds.
4. Dividend Dashboard
This is the main hub for overviewing your dividend data.
Â
- The only place you need to change will be the “UK Minimum Wage” for whenever it changes.
- Updating once per year should follow this.
5. Editing Your Stocks – US Positions
I separated UK and US stock positions for simplicity. Here you only need to add in
Â
- B – Ticker Symbol
- C – How many shares you own
- D – Average cost per share (optional)
- F – Sector or industry from the drop down
- G – Dividend growth rate (optional)
- H – How often they pay a dividend per year
- I – This is not required but in case there is ever any issue with retrieving the dividend data, this will be the backup data for pulling dividends.
- J – No need to change this
6. Editing Your Stocks – UK Positions
The same as the US, the issue with UK positions is they vary between Pence and GBP depending on the stock. There is much more inconsistency compared to US stocks.
For inputting data. Follow the same guide as the US Positions but there is a bit of difference.
Problem 1 – Stock Identification
Remember that US stocks are often given priority. If a UK and US stock share the same ticker. It will always take the US stock data. To mitigate this, write the exchange prior. So for UK stocks, the listing would generally be on the London stock exchange “LON:…”
Within the sheet. The US positions have no problems pulling dividend data but for UK stocks, it is a bit hit and miss. For example, LON:GSK dividend data will be retrieved without a problem into column AH. LON:BP on the other hand won’t (at least at the time of writing). I would recommend adding it in manually into column “i” just in case.
Problem 2 – Pence vs Pound
Most UK stocks are in pence. ETFs and a smaller select stocks are in pound. The google sheet default I made was to assume pence. However, to mitigate this issue. Remove the “/100” from column M.
This will no longer convert the pound into pence and instead just take the default pound value.
7. Adding Dividend Payments
The UK and US are again separated for simplicity and calculations. As stated, input for the appropriate year in either US or UK stocks, the stocks you hold will automatically be populated.
Problem 1 – I don’t remember which stock paid me OR I want to input for prior years
Easy, just use the blue column to input your unaccounted for funds regardless of stock.
8 Extra – Dividend Projections
The rest of the data does not need to be touched. However, you may look at Dividend Projections and change the yellow boxes if you would like to gather an idea as to how your dividends will progress over the years. Turn Drip on if you would like to include reinvestments, or off for without.
If you found this UK dividend tracker tutorial useful, let me know down in the comments. Or better yet, follow me on youtube.
Subscribe to the Youtube Channel for future updates
For a tutorial on how to update the sheet for a new year, watch this!
