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.

If you are logged into Google you may preview the template directly. Otherwise, please visit my templates page at Google to see it.

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.

currency_support

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:

Sector Weighting

Currency Weighting

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!

You may also like...

83 Responses

  1. Borja says:

    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.

  2. Borja says:

    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 🙂

    • scott says:

      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?

      • Borja says:

        LON:NGG or LON:DGE, two of my British companies.

        Thanks for all your effort.

        • scott says:

          So just to clarify that 1000 shares of NGG is worth approximately € 940? Does that sound about right?

          • Borja says:

            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.

        • scott says:

          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.

          • Borja says:

            Yeah, it looks like is fully working now after update the “Currency” sheet and the “native currency” to £p.

    • scott says:

      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.

  3. Borja says:

    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?

    Best Regards.

    • scott says:

      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.)

      • Borja says:

        Fixed, you are the man 🙂

        • Borj4 says:

          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 🙂

  4. Borja says:

    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!

  5. kadu says:

    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..

    • scott says:

      Thanks, Kadu. Those are some great enhancements. I’ll email you directly shortly.

      Scott

    • scott says:

      Kadu,

      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.

      Thanks again,

      Scott

      • Borja says:

        How must I do the changes if I want to update my version to 1.1?

        Regards.

        • scott says:

          Hi Borja,

          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.

          Scott

  6. Jorge says:

    Hi Scott,

    Thank you so much for sharing this! It is exactly what I was looking for.
    Looking forward for future versions.

    Cheers,

    Jorge

  7. Borj4 says:

    Thanks Scott, this is exactly what I was looking for.

    • scott says:

      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.

      Scott

      • Borj4 says:

        Hi Scott,

        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: http://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.

        • scott says:

          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.

          Scott

          • Borj4 says:

            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.

            Thanks!

          • scott says:

            I see. Thanks! A FAQ is a great suggestion too. I’ve been meaning to do a video tutorial as well.

            Scott

  8. Borja says:

    Hello.

    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 🙂

  9. Borj4 says:

    Hi “tocayo”,

    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 🙂

    • Borja says:

      Hola tocayo.

      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…

  10. DavidStocks says:

    Thanks Scott, looks great.
    Is it possible to add Israeli Shekel please (ILS)?

    • scott says:

      Hi David,

      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!

      Scott

  11. DavidStocks says:

    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 ImmediatelyMy Profile

    • scott says:

      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.

      Scott

  12. stevey says:

    Hi Scott. Thank for the great template. Could you please include Singapore dollar (SGD) into the foreign currencies.

  13. Andy says:

    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?

    • scott says:

      Hi Andy,

      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.

      Scott

      • Andy says:

        Thanks Scott. I appreciate the prompt reply!

      • Jorge says:

        Hi Scott,

        If it helps, when the Google sheets freezes I got a problem with some sort of script:
        “….e_i18n_core.js”
        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.

        Cheers!

        • scott says:

          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?

          Thanks,

          Scott

        • scott says:

          Hi Jorge,
          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.

          • Jorge says:

            Hi Scott,

            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.

            Cheers!

  14. Borja says:

    Hi Scott.

    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?

  15. Andy says:

    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?

  16. Andrew says:

    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!

    • Borja says:

      Are the Americans working fine but not the others? The same happen to me often.

    • scott says:

      Sorry guys, been busy with work. Will look into the look-up data issues for international stocks this weekend.

      Scott

      • Borja says:

        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).

  17. Johan says:

    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 🙂

  18. José says:

    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?

    Thanks!

  19. Daniel Barna says:

    Hello! Thanks a lot for this amazing spreadsheet. Could you kindly please add BRL currency?

  20. Sunil says:

    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.

  21. Greg Gee says:

    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?

  22. Sunil says:

    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?

  23. Ken says:

    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?
    Thanks

    • scott says:

      Hi Ken,

      “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.

      Scott

  24. Lucky7 says:

    Thanks for that version it helps me a lot to build my own small version.
    http://www.financial—–freedom.blogspot.com

  25. Furqan says:

    Hello! Thanks a lot for this amazing spreadsheet. Could you kindly please add IDR currency?

Leave a Reply

Your email address will not be published. Required fields are marked *

CommentLuv badge