Coming up in this dividend tracking spreadsheet update release, we’ll be updating the transactions tab to handle holdings in both GBP and GBX. For now, the spreadsheet isn’t smart enough to tell the difference between UK stocks in £ and p, but all that’s about to change.
GBP vs. GBX
How GBX is handled is pretty straightforward. We take the GBX figure, which could be 150p and divide by 100. This then provides the sum in GBP, so we can display everything in £. It keeps the spreadsheet looking smarter and makes calculating everything much easier.
The issue arose when I decided to purchase some shares in a stock that was listed as GBP. Already dividing UK stocks by 100 would incorrectly do the same with this share price, even though it was technically correct in £. So instead of £150 for this particular stock in GBP, the spreadsheet would show £1.50.
The easiest method of addressing this was to add a new currency for the UK market. We now have GBP and GBX in the drop-down menu for transactions. If the stock is in GBX, you’d choose GBX and the spreadsheet would divide accordingly. If it’s GBP, the spreadsheet would divide by only the conversion rate (or “1” if your base currency is GBP).
The only change that will be required in the manual per-stock sense would be the market price GBP stocks. These are also divided by 100, but you can simply remove the
/100 from the cell to cancel this out. I could add a formula to sort this out automatically, but it would require us to set the currency of each stock within the UK tab and that would make things a little messy.
London Stock Exchange: Can we please use a single currency format? Moving to GBP would sort plenty of issues out.
- Added new GBX currency to drop-down menu in transactions tab.
- Updated formula on transactions tab to correctly handle GBP stocks.
How do I update my tracker?
Make sure you already renamed the previously copied template you’re currently using. With an updated release like this one, simply copy across a new version to your Google Docs. Then follow these steps:
- Amend the code in cell
Transactionstab by changing the first “GBP” to “GBX”. If you’re using the UK Edition, you’ll need to add
G3="GBP",1,after the currencies (and before “true”) in the second part of the formula.
- Drag the first row from
T2all the way down to copy the formula.
- Update any GBP stocks in the UK tab by removing the
/100from the market value column.