International Dividend Portfolio Tracker with Transactions Page
I’ve had a number of people ask me to include support for foreign currencies. This is that effort. The current release is based off of my Dividend Portfolio Tracker version 3.1.2.
Click here to access International Dividend Portfolio Tracker spreadsheet
You’ll have to click File > Make a copy… in order to start using it yourself.
It is very similar in use to the standard version so please visit that page to learn more about the general usage.
The main thing that has changed is support for multiple currencies. The currencies currently supported include: US Dollar ($), Australian Dollar (A$), Canadian Dollar (C$), Euro (€), British Pound Sterling (£), Swiss Franc (F), Hong Kong Dollar (HK$), Japanese Yen (¥J), and Chinese Yuan (¥C). If other currencies are desired please just write in the comments below and I’ll add them.
As you can see in the example above as noted with the red arrow, I picked “$” as the native currency for McDonald’s since it is trade on the NYSE. For Lloyd’s, I picked “£p” since it is traded in London in pence. The “native currency” column is whatever currency the exchange is traded in.
The blue arrow designates the home currency. This will update all the values, including cost basis, market value, dividends, gain/losses, and all the data on the Summary sheet to whatever currency you want. If you want to see your account listed in €, then just put that symbol there and everything will be converted to euros in real time.
Here are a few changes on the Summary sheet:
The Sector Weighting chart has been moved to the Summary sheet. It has also been changed to a bar chart, showing more easily, I believe, the respective weightings of each of the sectors. It also includes a target percentage that can be adjusted for each sector. The target percentage is the red line at the bottom of the chart.
A Currency Weighting chart has also been added to the Summary sheet. This simply shows the percentage of the entire portfolio that is kept in each currency.
That’s about it for the main superficial changes from here. There has been a ton of under-the-table changes in order for the the currency conversions to be seamless.
If you guys notice any errors or would like other changes, please let me know. I hold all my foreign stocks as ADRs so don’t have any stocks held directly in a foreign currency to truly try it out myself.
And, the idea for the currency converter came from No More Waffles’s page here. Make sure it check out his site!
The spreadsheet is free and will always be available for free. However, if you find this spreadsheet useful, please consider donating to support my coffee fund and hosting costs. Thanks!
Thanks for the international version, I really appreciate it!
By the way, the “fees” column in Transactions tab is in the same currency as the stock, right? Maybe it’d be better remove the $ symbol.
Good catch. Yes, that is in the same currency as the stock. I’ll update that.
I found another issue, Google Finance shows the price of British shares in GBX (Not GBP), so in the portfolio tab when there is a conversion from “GBP” (which is actually GBX) to another currency (my home market value is EUR), it fails, showing the price multiplied by 100 and making the totals no sense.
The fix would be to divided by 100 the price from G. Finance for British shares but I don’t know how to do it without mess the portfolio. Can you help me, please?
Sorry for my english btw 🙂
Hmm. That’s interesting that stocks are traded in pence rather than pounds. I’ll take a look at that in the morning. Do you have some example ticker symbols of British stocks that I could experiment with?
LON:NGG or LON:DGE, two of my British companies.
Thanks for all your effort.
So just to clarify that 1000 shares of NGG is worth approximately € 940? Does that sound about right?
I wanted to say LON:NG but in the case of NGG you are right. 1 share = 76 GBX = 0.76 GBP. 1000 shares of NGG 760 GBP = 1070 EUR aprox.
What I did was add a separate currency conversion from pounds to pence (and vice versa). The only caveat is that you know need to use “£p” for native currency. The conversion to pounds, euro, dollars, etc should now work. If the British stock happens to be traded in pounds, then just use £ without the “p” at the end.
It looks like some of the dividend lookup functionality may be broken or temporarily not working. I’ll have to look into that next.
Yeah, it looks like is fully working now after update the “Currency” sheet and the “native currency” to £p.
Let me know if this change works. The currency tables are available on the Currency sheet. If I change each reference to GBP (=GoogleFinance(“currency:eurgbp”) to GBX, it changes the currency conversion to work with pence instead of pounds.
Just the last thing I saw, when a company doesn’t have dividends (for example: BME:CPL), the columns Annual Dividend, Dividend Yield and YOC of Portfolio sheet show an “#N/A” error, so the final row with the totals doesn’t work. Is there any chance to ignore the companies which don’t have dividends and make the totals work?
Under ReferenceData just put 0 in the place of the dividend yield for that company. I also updated the Currency conversion. The latest version works the best. (The =GoogleFinance(“currency:gbxeur” formula wasn’t working correctly…I simply dividend by 100 instead to get the pence amount.)
Fixed, you are the man 🙂
Another way to fix this, under ReferenceData, instead of putting 0 in the place of the dividend yield is to surround the entire formula with iferror -> 0.
=iferror((if(isblank(B4),,iferror((index(split(ImportXML(“http://finance.google.com/finance?q=” & B4, “//td[@data-snapfield=’latest_dividend-dividend_yield’]/following-sibling::*”), “/”),1,2) / 100),ImportData(“http://finance.yahoo.com/d/quotes.csv?s=”&B4&”&f=d”)/C4))),0)
@scott, this way you can add this change to the new version.
Hope the comment works 🙂
I’ve used the iferror formula elsewhere but not yet for that specific purpose. Your change has been implemented on the latest version.
Thanks for the suggestion!
Thanks “tocayo” 🙂
I think the change in the Currency sheet made the “home market value” and “cost basis” don’t work properly, it’s like they use the wrong exchange rate.
I can give you access to my Portfolio if you want to check it in a better way.
Thanks for all!
Fixed, the currency sheet was copied wrong and it crashed the portfolio, now it’s working fine.
My enhancements (I recommend do add it to your template)
– Portoflio: Selection of currency add as “selectable list” .-) You already got this is in Currenctly tab
– Automaticly changing name of currency in “affected” tabs in headers instead ho “home …. ”
– Enhancing to have multiple currencies EUR, USD, CAD, CZK, GPB.. but sum it up just in desired ones..
– There is also curency which format is not as prefix like $ 100, or € – for example Kč (czech crown) is always used as suffix. In that case adding character directly into numbers wont work example: Portfolio tab and summary of Home Market Value – i recommend add suffix/preffix of currency just to “Totals [$|Kc|€” and remove chars directly from text and leave only number.. This will help for future “auto-changing” currency as you “want”..
– Typos in current document:
sum market value (in native currency is not making sense as currencies can be mixed – only “home/desired” should be summed” affected almost all sums in 54 row !Portfolio
Tell me your email and i will send you mine (updated according your), dont want make it public..
Thanks, Kadu. Those are some great enhancements. I’ll email you directly shortly.
I just implemented those changes. They are included on version 1.1.
Thanks so much for all the great suggestions! BTW, I really like the look of that TreeMap. I’ll have to play around with that and include it in a future version.
How must I do the changes if I want to update my version to 1.1?
If it is a simple addition of a formula I’ll write on the Version History section of the About tab exactly what cells need to be changed to update it. Most changes will be simple.
This update was a little more involved. It should be pretty easy to grab a brand new copy of the template and then copy over the previous data.
On the Portfolio page, just copy the Sector, Account, and Ticker symbol columns. Then just copy all the orange-ish highlighted cells on the Transactions sheet from your old one to the new version (from columns A through G).
Everything should then be updated and work correctly.
Let me know if you run into any issues.
Updated and working fine, thanks.
Thank you so much for sharing this! It is exactly what I was looking for.
Looking forward for future versions.
You’re welcome, Jorge! Let me know if there’s anything in particular you’re looking for.
Thanks Scott, this is exactly what I was looking for.
You’re welcome, Borj4. Let me know if there’s any improvements you’d like to see. I only hold foreign stocks as an ADR so am not actively using this international version.
I just finish to add all my data, awesome. Are you planing to merge both versions? My home currency is euro but I think now this international can work for you also. I´ve been looking at the standard version and it seems there are missing features in the international one.
If possible, merging save time too.
I have 2 different brokerage accounts, and I found this comment really useful for those who want to add the same ticker to different accounts: https://www.twoinvesting.com/2013/08/investing-spreadsheets/#comment-14572
Curiously, this behavior that permits it, could causes some problems. Today QVCA has change the name in Google Finance from Liberty Interactive Corp to Liberty Interactive Group.
Maybe you can add it in a FAQ.
Once again, thanks for sharing.
I’m not planning on merging the two version at this time. The reason why is that the currency conversion adds a little unnecessary complication for those of us who only hold stocks in USD.
What features are missing from the international version that are available in the standard? I thought there were actually more features on the international version. I’d, of course, love to keep the two in sync as much as possible.
It´s probably just a sensation, for example, the about tab is much better as it includes definitions that helps to understand it. This is the reason a suggested to add a FAQ.
DivCalendar sheet seems different, although it´s not clear for me by now how to use it.
A last suggestion, I think “Cash” G4 in Portfolio tab is just a little control to our cash. In my case, Interactive brokers allow me to have cash in different currencies, so I´m not always exchanging it to euro, currently I have cash in euro,USD,C$ and pounds. It´s ok to see all together in home currency in Summary, but I´d found useful to be able to have all split in portfolio.
I see. Thanks! A FAQ is a great suggestion too. I’ve been meaning to do a video tutorial as well.
I found another bug in the portfolio.
I have FRA:BMW and FRA:BMW3 shares (both are the same company but they are different shares) and the portfolio shows both together.
Do you know how I can fix it?
Regards and thanks again for your great work 🙂
I also own BMW regular share, I use ETR:BMW ticker.
I think you can use the same trick I use to have the same ticker at 2 different brokerages. Change the name of one on the Portfolio page. Let me know if it works 🙂
It didn’t work, in ReferenceData sheet seems fine (Two different tickers with different prices) but in Portfolio sheet the new (FRA:BMW3 or ETR:BMW3, I tried to use BMW and BMW3 with different prefixes also) the second one shows me the shares I have from the first one but with the BMW3 price instead the BMW price.
Here is the screenshot: http://prntscr.com/9zcq4q
I don’t have 100 shares of BMW3, I just have 50. And in Transactions sheet I only have the option to add the buy of BMW and no BMW3. It’s very weird…
Look at this example: https://goo.gl/gpBXP1
Just overwrite “Company name” formula on Portfolio tab in one of them. Then, in Transactions choose different C-stock column. It should work.
Thanks for the help, Borj4! Yes, changing the company name on both the Portfolio and Transactions sheets will fix this. Likewise, this will help if you own the exact same company and ticker symbol in two different accounts.
Thanks Scott, looks great.
Is it possible to add Israeli Shekel please (ILS)?
Your message got caught in the spam filter for some reason this morning. I just added support for Israeli Shekel. It is included in the latest version of the spreadsheet.
Let me know how it works for you. Thanks!
Oh, I’m not a spammer 🙂 Thanks for checking out the spam folder as well!
Thanks for the solution and the fast response Scott, will try the feature today.
DavidStocks recently posted…How to Pick Henna Tattoo Kits And Start Henna Tattooing Immediately
No problem! Let me know if you’re having any luck with Israeli stocks as well. You might have to add a colon with the exchange on the end, for example, KO:NYSE.
Hi Scott. Thank for the great template. Could you please include Singapore dollar (SGD) into the foreign currencies.
I was on vacation for the weekend. I’ll add it to the template on Monday night.
Just added Singapore dollar support. Sorry for the delay!!
Hi Scott, I love what you’ve done with the tracker. However, I’m finding it really slow to use. Lots of lag time in between entering data and selecting drop downs. Any possible reason why this might be?
I’ve gotten multiple people messaging me and saying they are having the same issues with the tracker starting today. It looks like it is a Google issue.
I got back too late from work tonight (nearly midnight here) to check out myself tonight, but I’ll look into it in the morning.
Thanks Scott. I appreciate the prompt reply!
If it helps, when the Google sheets freezes I got a problem with some sort of script:
I think it is a general Google problem because I’ve read they have done some updates to Google Docs. However, I have no idea where to check for a solution for this.
Thanks, Jorge. I’m taking a look through the Google support forums to see if I can find any answers as well. Does the timeframe for the reported updates fit with the beginning of the Google Sheets freezes?
At least part of the issue seems to have been resolved by changing the yahoo finance site to be http://download.finance.yahoo.com. See the comment here.
Sorry for the late answer. I was on a trip this weekend.
I’ve read that Google wanted to update the Sheets to a new format and the old formatted sheets would be automatically updated. Though, I do not know if this was the case….
I will update the references for the Yahoo Finance and see.
Thanks a lot.
One question about the dividend yield & YOC of international companies.
For american companies they usually work great, the yield is showed quickly when I open the document. But for non-american companies almost 80-90% of the times don’t work properly, I can only see a 0%, but it worked a few times, but mostly not. I attach a screenshot: http://prntscr.com/ao3opb
Any idea about the problem?
Was it working before the latest issue with Google Sheets and Yahoo Finance?
The truth is the yield has never worked very well in the last months for non American companies. So I think it’s not a problem from the latest Google update.
Alright. I’ll take a look tonight and see if I can figure it out.
Great, thanks for all your work, you are saving us 🙂
Hi Scott, I’m having some issues pulling the Petrochina data on the HK Stock Exchange. I’m entering 0857.HK and it’s not able to pull the data. Other counters are ok. Any tips?
I’ll look into it and see if I can find a fix for you.
Hi Scott, on the ReferenceData sheet, some of my counters are pulling a 0% dividend yield from Google Finance, while the other counters are pulling the dividend yields normally. Any idea why this might be? It was just fine a moment ago. Thanks for your help!
Are the Americans working fine but not the others? The same happen to me often.
Sorry guys, been busy with work. Will look into the look-up data issues for international stocks this weekend.
I think the problem is the importxml function doesn’t get the yield from Google Finance (In G. Finance all the international companies have the yield info) , then the importdata function works getting the yield from Yahoo Finance (But Yahoo only has the yield for US companies).
Hi Scott, I love what you have done here, it is a very useful spreadsheet!
If you ever find the time to add the Scandinavian currencies (Swedish SEK, Danish DKK and Norwegian NOK) it would be most appreciated. We are not many who live on this arctic fringe of civilisation, but among the poor souls who do there is not much else available to do in your free time than to track dividend stocks 🙂
I’ll add these currencies in tonight.
I too would love to have the Scandinavian currencies added! Thanks!
I’ll try to get to the Scandinavian currencies as soon as I can. Have a couple presentations coming up at work that have kept me busy!
Hi Johan and Sensible,
Sorry for the delay. Currency support for the Scandinavian countries has been added!
Hi Scott, thanks for this absolut amazing work!
I´ve “loading problems” in the International watchlist spreadsheet using the last version.
Do I have to do anything to fix it?
Hello! Thanks a lot for this amazing spreadsheet. Could you kindly please add BRL currency?
I’ll add it tonight.
Just added the support for the Real today. Sorry for the delay!
Thanks for the info Scott. As you told me in other comment, I used this and got it working for Indian stocks as well. But only problem is Dividend details are not pulled. I see that you are using yahoo feeds for dividend details. I will check and update you on this. Also In Portfolio sheet you used “Account” column but not in transactions sheet. I think its better if we have that column here as well.
I’m finding that the portfolio tracker breaks down for me after line 15. None of the tickers I enter will populate starting with the value, and I get a Did not find value ” in HLOOKUP evaluation. error.
Any idea how to fix this?
On which sheet does it break down? Have the formulas been dragged down last line 15?
Hi Scott, I got it working fine with my Indian stocks. Thanks for the international version. But XIRR value is not working after 40th row in Portfolio sheet. Any limitation in the calculations?
Hi Scott, in the original file there was a “Lists” sheet that allowed me to add items for the drop down box. Where do I locate the lists for these boxes on the International Tracker?
“Lists” is available on the International Tracker as well. It is just hidden right now. To get to it you’ll want to click the button with the four horizontal lines to the right of the “+” and to the left of the “Summary” page. On that list you’ll see it listed on the bottom.
Thanks for that version it helps me a lot to build my own small version.
Hi Lucky7, You’re welcome. Glad to help!!
Hello! Thanks a lot for this amazing spreadsheet. Could you kindly please add IDR currency?
Hi Furqan! Yep. I’ll do it soon. Busy with call at work now.
Hi Scott, thank you for creating these spreadsheets! Exactly what I was looking for. I am having difficulties getting the DivCalendar to work, mainly recognizing past div. I noticed that the Next Div Date formula was only on the first entry, so I did a copy and paste but it does not seem to help resolve the issue. I am from Canada and noticed that I have to but the stock exchange in the ticker symbol: TSE:BCE. I am very new to these types of formulas and hope that you may be able to take a look. Many thanks!
Hi Sue, I’ll take a look soon and see what the issue is. Scott
Hi Scott, I think I found my mistake. I was able to get the div to load properly. I am still not able to see Ex-Dividend Date/Dividend Pay Date this maybe due to difference between Yahoo and Google. Not a huge deal if there is no fix for this. Once again thanks for sharing your spreadsheets!
Many thanks for the sheet! For some reason Dividend Yield doesnt work. Can you pls look at this.
All the best, Jiri
What ticker symbol isn’t working? I’ll look into it after work tonight.
All tickers. Not sure why it returns –
Error Function INDEX parameter 2 value is 8. Valid values are between 0 and 1 inclusive
Thanks for sharing your work and help, Jiri
Could you send a screenshot to scott (a t) twoinvesting. com? Thanks!
I just opened the spreadsheet today (after not using if for a while) and it seems that the ticker IMIE is not working anymore. It is showing 0 shares in the portfolio page and it also shows a different name .
It is trading on the Paris markets.
Should I enter the stock differently to specify it is on the Paris markets?
Thanks for this wonderful spreadsheet.
do u have a latest version usable for Indian market.
Looks like a wonderful tool. I wonder whether it’s still supported. All the posts I see are a couple years old. I’m looking for a portfolio / dividend tracker tool for my international portfolio: US, CANADA, UK, euro area.