Using Google Spreadsheets to Track Your Dividend Portfolio, Updated Template

Using Google Spreadsheets to Track Your Dividend Portfolio

About two months ago I created a spreadsheet in Microsoft Excel to track my dividend stocks and income. Not only did I create quite the handy spreadsheet, I also shared the end result with you guys so you could all take advantage of it. Since reactions were quite positive overall, I left it at that for a while.

However, one major beef most of you had, was the fact that it takes a lot of manual actions to keep track of everything. As a result, I decided to try out Google Sheets, which allows for easier automation. After a couple hours of finicking around with Google’s version of Excel, I’m happy to share my now mostly automated dividend tracker!

Just like last time my spreadsheet is aimed at international investors. Being one myself I need to be able to track my investments in multiple currencies, mainly to keep an eye on the capital appreciation. Following the growth of my dividend income stream is what it’s all about, but it’s nice to know the actual value of your portfolio at any given time in your home currency too.

Since the basics of my spreadsheet haven’t changed much, the workflow remains rather the same. On the “Portfolio” tab you’ll have to enter a stock’s name, ticker, country, sector and currency. After making a purchase you’ll of course also have to provide the amount of shares bought and the cost basis in your home currency. Google will take care of the rest for you.

 

Currency exchange

The top left of the dividend tracker still shows a currency exchange calculator and a pie graph to represent the weight of each currency in your portfolio. You can change the currencies you invest in to whatever you like – if you’re up for some Zimbabwean companies, go for it! Contrary to last time, Google now provides the last known exchange rate automatically using the following function:

=GoogleFinance("CURRENCY:EURUSD")

Like I already said, you should enter the right currency symbol in your list of stocks in column E, otherwise Google Sheets will display an inaccurate portfolio value. Columns I and J on the same tab then do the rest to provide you the value of your holdings in your home currency.

The dividend tracker keeps your portfolio overseas investments up to date by taking into account exchange rates

 

See how well diversified your portfolio is in the blink of an eyeSector diversification

My dividend tracker also displays the diversification of your portfolio based on the input you provide in column D of the “Portfolio” tab. Since most people found this a useful and easy way to visualize the different industries they were invested in, I haven’t changed anything here. The spreadsheet still aggregates the home value of every individual stock in column J based on the sectors in column D.

 

Year-Over-Year performance

A brand-new function, and one which I really like myself, now displays the past income performance of your portfolio on a yearly basis. A small graph quickly visualises your progress for the year compared to the previous three years, as you can see in the picture below. The data needed for these statistics comes from the “Dividend income” tab, which I’ll explain in detail below.

The Dividend Tracker shows the YOY performance of your dividend income

 

Dividend overview

The “Dividend overview” tab provides you with a detailed table and graphs of your dividend income on a per position and per month basis. Nothing has changed here compared to the previous version of my spreadsheet. Users of my budget tracker will also be familiar with the mechanic behind this sheet. Cell O1 is based on cell Q1 on the “Portfolio” tab, which then collects all dividends from a particular year from the “Income” tab.

Like last time, the only thing you have to do to make this sheet work, is add a ticker when you buy into a new position and add that position to the “Portfolio” tab. When your dividends start rolling in you enter those on the “Income” sheet by date and ticker. The dividends entered there will also be displayed on the first page, both in the table and the YOY performance overview.

When a year passes by all you have to do is change cell Q1 to the new year and Google Sheets will update the dividend tracker. The upside of doing it this way is that you’ll be able to go back in time for a detailed overview from any of the previous years by changing the year up top again.

 

Further automation

If you want to automate tracking your investments and dividends even more, you could have Google Sheets insert the dividend payments automatically. Doing so, however, is rather convoluted. I haven’t added it to my spreadsheet for two reasons.

First, Google Sheets doesn’t offer a way to insert the dividend or yield through the GoogleFinance-function, so you’ll have to rely on third-party solutions like this one:

=split(ImportXML(concatenate("http://finance.google.com/finance?q=","JNJ"), "//td[@data-snapfield='latest_dividend-dividend_yield']/following-sibling::*"),"/")

The formula above tries to download the dividend and yield of Johnson and Johnson (JNJ) from the Google Finance website, which actually works great. The major downside is that Google Finance often is wrong, especially for non-US stocks or stocks listed on multiple stock exchanges under the same ticker. The chance of wrong data in your dividend tracker is just too high. Another major downside is that you won’t be able to keep historic records.

Second, and a direct result of the previous problem, I’m keeping track of my net dividends; not the gross payments the companies make. Since I have to deal with both foreign withholding taxes and national taxation on dividend payments, it’s nearly impossible to automate the entire thing. Maybe that’s not the case for you, so you could actually implement the formula above if you wished to. If you decide to do so, take a look at Tawcan’s guide for implementation ideas.

 

Final words

If you’ve gotten this far, you’re probably wondering when the damned download button will finally pop up! Don’t worry, here it is!

I’ll provide you guys with an updated Microsoft Excel version without the nice automation Google Sheets provides and a Google Sheets variant, which obviously does almost everything for you. If you’re looking for a real life example, the Google Sheets dividend tracker is actually the last version of my own spreadsheet.

As always, feel free to play around with the spreadsheets and adjust things to your liking. I sincerely hope some of you find them useful, especially the automated one since many of you sent me an e-mail asking for automated solutions. Let me know what you think of it and if there’s something you’d like me to add.

83 Comments

  1. Nice sheet, NMW. Thanks for sharing.

    I use Google Sheets exclusively for all my portfolio tracking and all other budgeting. Its a great tool and while there are limitations, its the best option as I use multiple devices and having it online works best for me.

    cheers
    R2R

    1. R2R,

      You’re welcome! I hope my sheet gave you some inspiration to add to your own.

      Google Sheets has quite a lot of limitations indeed (and it’s not as user-friendly as Excel), but the Google Finance integration and online synchronisation to multiple devices works beautifully.

      Thanks for stopping by,
      NMW

  2. Sounds like a fantastic tool NMW. I haven’t downloaded this yet, as I enjoy playing around with my own spreadsheets and customising them for what I like to see, but I’ll test yours out at some point!

    Automation is always a little tricky, but I actually don’t mind doing the manual updates, it usually means I spend more time really understanding what’s been happening with my investments.

    Great to see you adding value for your readers with tools like this NMW!

    1. Jason,

      Let me know what you think of my spreadsheet when you give it a quick testdrive! Any feedback that helps me create a better tool for the community and that increases my knowledge of Excel/Sheets is appreciated.

      I don’t mind manual updates too much either, but entering the most recent price of my stocks felt a bit cumbersome. Entering my dividends manually is not a problem though, love doing that!

      Cheers,
      NMW

    1. Henry,

      MS Excel definitely is way more user-friendly. I actually prepared my spreadsheet in Excel, then transfer them over to Sheets. Currently I’m only using Sheets for my dividend portfolio. All my other spreadsheets are Excel-based.

      Cheers,
      NMW

  3. Dammit! I was happy with your Excel version because I only had that at home – with a Google version, it means that I will be distracted at work because I’ll be able to access it from there! I must resist and not log in from work…lol!

    Well done, great job on it!

    1. Weenie,

      Sorry I’m not sorry! I’m glad you’re enjoying my old Excel version so far.

      Don’t let investing get in the way of your job, haha!

      Best wishes,
      NMW

    1. AG,

      I can imagine how much time it takes to do everything with pen and paper when you have a portfolio the size of yours! I hope you find some inspiration in my spreadsheet so you can focus more on your actual investments rather than your portfolio.

      Thanks for stopping by,
      NMW

  4. With all the automation that Google Sheets pulls from Google Finance… I don’t know if I could ever leave it to go back to Excel. Thanks for your examples. I think I might take a few ideas from them and add them into my own.

    1. PMU,

      I know how you feel! Especially the automatic price updates are useful. Can’t imagine having to deal with a large portfolio and manual labour like in Excel.

      Glad you liked the spreadsheet and implemented some of the stuff in there in your own.

      Cheers,
      NMW

  5. NMW thanks for sharing!

    If you’re interested in retrieving foreign dividends automatically. Try retrieving them from Yahoo. I did that in the past and it worked well.
    It also provides an alternative because if I remember correct you’re limited in the amount of googlefinance() functions one can use.

    1. FRD,

      You’re welcome! I hope this is of any use to you.

      I know you can retrieve information from Yahoo as well, even from Microsoft Excel. Even though it works, it’s hardly an elegant solution. Just like Google, Yahoo also gets the dividends wrong for some companies, especially when they’re listed on multiple stock exchanges and declare dividends in multiple currencies.

      Thanks for chiming in,
      NMW

  6. Wow, this is a really great guide – very detailed and precise. I have never really tracked it, but my investment platform does provide some tracking tools. I do like Excel for budgeting and stuff but I don’t think I have the patience for doing this in Excel.

    1. Glad you like it, PET!

      If you don’t have the patience to do this in Excel, you’ll probably be better off with the investment platform of you broker. I’m just really into collecting this kind of data and putting awesome graphs together.

      Thanks for stopping by,
      NMW

  7. Hi NMW,
    thanks a lot for the tracker. I have adjusted it slightly but it looks much better than the one I created on my own. Well, slightly might be the wrong word, but anyways the baseline set by you is wonderful.

    On little remark from my side. Personally I do not see any benefit to have currency graph in figures of currency. I would keep it in value of invest in home currency.

    Kind Regards,
    Lars

    1. Lars,

      No problem! I’m glad you found it useful and used some of its elements to enhance your own spreadsheet.

      You are absolutely right about the currency graph. I definitely have to change that. Doesn’t make any sense to make a graph based on different currencies. Thanks for pointing that out.

      Best wishes,
      NMW

    1. Alexander,

      To add Swedish stocks, just switch out one of the foreign currencies with a symbol for SEK (cells B5 to B7) and change the formula in cells C5 to C7. The spreadsheet should then allow you to add Swedish stocks.

      If you’re unsure on how to proceed, feel free to contact me through my contact-page and I’ll get you started!

      Cheers,
      NMW

  8. Nice spreadsheet. I was wondering how would you edit this spreadsheet if you wanted to remove all the currencies out of it?

    1. Sorry to be dense… but my home currency is US Dollars – so where should I change this?
      In the upper left hand corner, would I just insert $ into cell B4 or would I change c5 to read 1. Ideally I’d like Home cost basis and market value to be reflective of US dollars on the bottom part. I tried to delete column I foreign currency but it threw everything out of whack. Obviously I have remedial excel skills so thanks for any help 🙂

      1. Dzogen,

        You could add $ to cell B4 to make it your home currency indeed. If you don’t need any of the currency functions, I’ll send you an edited version!

        I saw you already mailed me, so expect a reply relatively soon.

        Cheers,
        NMW

        1. Hey thanks so much. Ya I don’t need any currency functions so if you’d be willing to do that that would be amazing. Thank you!! 🙂

  9. Hi NMW,

    Great tool you have here! Thank you for sharing.

    I tried to fill in my own prortfolio in this tool but encountered two issues. Hopefully you can help me with that. First of all in the dividend income tab. I fill in my received dividends in both EUR and USD. All fine. But after that the total overview of dividends does not translate to the home currency but just adds up the amounts from the dividend income tab. How should this be done? Translate to the home currency already in the dividend income tab?

    Secondly I was wondering how you should process a sell of a stock without loosing the historical dividends received.

    Thanks!

    1. Marnix,

      Glad to hear you find my spreadsheet useful!

      First, it’s not possible to enter your dividends in foreign currencies and have them convert to your home currencies. That’s because the exchange rate changes all the time. (Imagine the Dollar appreciating a year from now. The spreadsheet would then show a higher amount in Euros for a dividend received today. That’s not what we want!) There are two solutions:
      1. Always manually convert your exchange rate to your home currency. This is how I do it and is by far the most logical way to do things.
      2. Separate all currencies and don’t convert them to your home currency. This makes tracking your portfolio’s progress rather difficult, however.

      Second, good question. Hadn’t thought of that myself. You’d have to keep the stock you sold in the spreadsheet, but enter zero shares and zero cost basis in the spreadsheet. This gives an error when calculating the performance percentage, however. I’ll have a look at that soon to fix the issue. The overall performance tracking of your portfolio doesn’t break though.

      Good luck and don’t hesitate to ask any further questions.

      Cheers,
      NMW

      1. Regarding the dividends in foreign currencies; I actually added a couple of columns to the Dividend Income tab to convert my USD & AUD dividends to £ (my home currency).

        I then updated the Dividend Overview tab to pick up the values from new home currency column.
        The $ dividends build up in my broker account and so I figure that the current exchange rate is valid whilst I still have the dividend funds in my account.

        I might be wrong but I think it also makes the Yield calculations more accurate as they’re based on the home currency and mixing foreign and home currency can’t be accurate.
        I might be wrong on this but I think this works better for me.

        Great spreadsheet by the way and really saved a lot of time rather than trying to create something from scratch

    1. Thank you, Jeff! Hope you like it and that you’ll continue to use it. Let me know if you have any questions.

      Best wishes,
      NMW

  10. Hey NMW,

    thank you so much for sharing your sheet! Also thank you for your tutorial but I’m not so good at Excel and I would like to adjust the positions a bit. How can I add Portfolio positions? When I do it like in your tutorial the positions do not update the dividend income…
    Thank you!

    Luckyone

    1. Luckyone,

      You can add positions by inserting rows in the stocks table. Simply right-click on row number 14, for example, and select insert row. Then make sure to copy the formulas over by using the + tool on the bottom-right of the cells.

      Hope this was helpful,
      NMW

  11. I was wondering if you could help me out? I just tried to use the spreadsheet but it is only coming up with a “view only” version that I cannot edit. What am I doing wrong?

  12. I made a sheet similar to this, but this is wayyy better. For portfolio breakdown, what about real estate holdings, ie REIT?

    1. M Kwong,

      I classify REITs under Financials, but you can always add more categories if you’d like!

      Cheers,
      NMW

  13. Appreciate the hard work,

    I’m trying to enter in some Canadian tsx symbols, and it seems like some work and some don’t. For example, for suncor, input su.to and the fields populate automatically. When I put genworth inc (mic.to), all the fields say n/a.

    Why is this happening?

      1. Hi Vince. Are you able to enter any Canadian REIT’s? They have a UN extension. I’m not sure if I would need to input TSE:CREIT.UN, TSE:CREIT-UN, TSE:CREIT UN. I also have stock in preferred shares, SJR.PR.CA., and some mutual funds, VAB. I’m not sure how to input these. Thanks.

    1. Vince,

      Sorry for the late reply! You’ll have adjust your symbols to the annotation that Google Finance uses. Glad to hear you figured it out yourself already.

      Best of luck,
      NMW

  14. Great Sheet! I was wondering however, my cost basis and foreign market values are not the same as my google finance numbers. Im afraid this might be skewing my other numbers. Im not sure If I have done something incorrectly or if this is normal.

    Other than my confusion here this is exactly what I have been looking for.

    1. Clayton,

      Glad to hear you like the spreadsheet and enjoy using it!

      I’m sorry but I don’t quite get your question. What do you mean by your cost basis and foreign market values are not the same as the Google Finance numbers? Which Google Finance numbers?

      In the cost basis field you should enter your gross purchase price, while the foreign market value is a formula based on the amount of shares and the price from Google Finance.

      Hope this was helpful. If not, shoot me another message. 😉

      Good luck,
      NMW

      1. Hey,

        I have a portfolio built on google’s finances page. I was referring to that sample portfolio I built there. But I understand now. Before I did not manually enter my gross purchase price and it generated a number in that column already so I left that number in thinking it was part of the automation. Once I manually entered my gross purchase price everything added up nicely.

        Thanks again!

  15. Problem 1: The code only returns the most recent dividend. Is there a way to get older dividends?

    Problem 2: I have some stocks which pay dividend in foreign currencies. I want to convert the dividend amount to my own currency as of the date when the dividend was paid out. How do I obtain the date when the dividend was pait out? Assuming A1 = ticker, A2 = dividend amount in local currency, A3 = dividend date, A4 = currency, A5 = exchange rate and A6 = converted dividend amount, then the dividend from one share would be calculated like this:

    A1 = some ticker, for example “GOOG” or “HEL:KESAV” for Google class C or Kesko class A.
    A2 = the code you used above
    A3 = ???
    A4 = =GOOGLEFINANCE(A1, “currency”)
    A5 = =IF(A4=”XYZ”, 1, INDEX(GOOGLEFINANCE(CONCATENATE(A4, “XYZ”), “close”, A3),2,2)) (assuming my country’s currency code is XYZ)
    A6 = =A2*A5

    But how do I fill in A3?

    1. Anonymous,

      Sadly I’m not aware of a way to receive other dividends other than the most recent one. That’s because this is already a “hack”. I wish Google Sheets would finally support dividend information in its GOOGLEFINANCE() function!

      As a result, I don’t think you can achieve what you’re trying to do. I haven’t found a way to look up a dividend or the date it was paid in Google Sheets. That’s why I enter all dividend amounts manually (after taxes and in my home currency). It’s the easiest way to keep track of things for me.

      Sorry I couldn’t be of any more help. If you do figure something out, let me now.

      Best of luck,
      NMW

  16. love your sheet.
    quick question, say i bought 10 shares of a stock at 1.00 and I add another 10 at 1.05 how would i enter the additional purchase?

    do i change the shares column to 20 and the cost basis column to 2.05?

    1. Vince,

      Exactly! Just add the new shares to the old number and increase the cost basis.

      I’m working on a more elegant solution, so in the mean time you’ll have to make due with this solution.

      Cheers,
      NMW

  17. NMW, thank you for sharing this awesome template! I ran into your blog yesterday, and your template was what I was looking for for a long time.

    I have a blog for the same dream as yours, and I modified your template a bit and shared that to my readers. I appropriately credited your blog, so I hope you don’t mind. I really love your template, and wanted to share that knowledge with someone who doesn’t know English in my country. 🙂 So I basically translated all the words and added some local information.

    If you wish your template not to be modified and shared by someone else, that’s okay.. but I’ll be really really sad!

    I added your blog to my feedly rss feed, so I’ll be around. Thank you very much!

    Oh, my blog address with your link in it.. http://financialfreedom.kr/878

    1. Youngbin,
      (Or is Lee your first name – I’m sorry, really have no clue!)

      No worries, feel free to edit the template as much as you like for your own use and reshare it! Just make sure to credit me where credit is due. Glad to hear you did so! 😉

      Thank you for dropping by and taking the time to leave a comment.

      Cheers,
      NMW

  18. Sorry, NMW. I’m here to seek your help. 🙂
    I’m really inspired your Euro dividend all-star list and all the other dividend champion list. I’m starting to think that it’d be cool for me to creat Korean dividend champion list. I’m trying to make a excel spreadsheet to do this work, and I’ve got stucked.

    If I have a historical dividend list, how could I calculate consecutive years that have increased dividend? Can you share a function or a workaround to do this?

    Thanks.
    Youngbin

    1. Youngbin,

      Good question! It’s something I’ve been doing manually, but it would be easier to do it through a spreadsheet function. So far I haven’t found a workaround yet, but I’m not an Excel expert – Google didn’t get me far either though.

      What you could do is make a nested IF function or a nested COUNTIF function, but that would get very complicated very soon, I fear.

      I’ll look into it some more the coming days and let you know if I found a solution!

      Cheers,
      NMW

  19. Hello,
    Thank you for this tool but i can’t use it: i’m just allowed a ‘only viewing’ mode so i cannot insert my own datas… (strange no one else here has my problem).
    I was allowed to send the owner a request to enable a ‘writing mode’, I did but no answer and no cahanges…

    1. Francesca,

      I believe you’ve also sent me an e-mail which I’ve responded to, but just to be sure:

      To access the dividend template and use it yourself you’ll have to click on file > make a copy. The template will then be added to your own Google Drive account.

      Let me know if that works for you!

      Cheers,
      NMW

    1. Guy,

      You could look at the ImportXML function as described in the article above, but I’m not sure how to import the NAV from a CEF. Sadly I’m not an Excel/Google Sheets wiz. 🙂

      If you find a solution, let me know!

      Cheers,
      NMW

    2. Hi Guy,

      I played around with various importxml formulas for awhile and didn’t have any luck since the content of the page is dynamically generated using javascript. ImportXML by itself, therefore, doesn’t work. This page may be able to help using a Google Apps Script. https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/how-do-i/chrome-browser/-uGQs2sQaIk

      I don’t have time to play much more with the possible solution on that page, but let us know here if you get it working.

      Scott

  20. Hey NMW,

    I am loving the spreadsheet still, I just have one more question. I have a funny feeling that my Home Market value isn’t calculating properly on my USD Stocks. I may have messed something up here. My home currency is CAD, am I correct in assuming that my Home market value is the current value of the stock in my home currency. If that is the case should my home market value be a higher number than the foreign value since the US dollar is higher than the CAD?

    For instance I have 111 shares of GM currently at 32.44 per share. A cost basis of 3488.73, Foreign market value of 3600.84. And it’s showing my Home market value is 2574.23? Does this look correct? I might just not be understanding something properly here.

    Any help in this matter would be greatly appreciated.

    Thanks Again,
    Clayton

    1. Clayton,

      3500 USD is indeed about 5000 CAD, so you’re right that the home market number isn’t correct. You’re formulae in the currency exchange frame is probably wrong. Make sure cells C5 through C7 contain something like =GoogleFinance(“CADUSD”).

      Hope this was helpful?

      Cheers,
      NMW

      1. Hey NMW

        Yes that makes sense. The formula in there was USDCAD i ended up switching that to CADUSD and I believe it’s working now.

        Thanks so much.
        Clayton

    1. W Wong,

      You’re welcome! I hope you find it useful even though you can’t track all your stocks properly anymore.

      Thank you for pointing that out to anyone else running into the same problem.

      Best wishes,
      NMW

  21. maybe a dumb question….what if you decided to sell a stock? would you delete the stock from the spreadsheet or put the shares to 0?

    1. Vincent,

      The current version of the spreadsheet doesn’t really take selling a stock into account. I’m working on a new version that does. It’s best just to delete the stock from the spreadsheet even though it might screw up your return numbers a bit.

      Cheers,
      NMW

  22. Do you mind explaining how you calculate the COST BASIS – HOME?

    For e.g =(606,51-15)*(1/1,3069)

    What does the 606,51 mean and what does (1/1,3069) mean ?

    Thanks !

    1. SC,

      I’m not entirely sure what you mean, but I think you’re referring to the formula to calculate the home value of a foreign investment?

      In this example the foreign cost is 606.51 dollars plus 15 dollars transaction costs. Then you have to use the exchange rate (1/1,3069 – back when the Dollar was still really weak) to calculate the cost in Euros.

      Cheers,
      NMW

      1. Regarding this, If I bought stock in the same market as my home is set, Shouln’t I have columns H, I, J with the same values? I find this field pretty confusing (I’m pretty new on this, sorry!)

  23. Dear NMW,

    This might be a really basic question but Ive adapted your initial spreadsheet to my portfolio. Now Ive added another company to my portfolio.

    How do I add another row beneath the existing rows that maintains the data structure of the rows above in the dividend portfolio tab? If I click add row I have to enter all values manually, the layout gets messed up etc. I must be doing something wrong.

    Best regards,
    Steve

    1. Steve,

      You right-click on the row number to the left and choose “add another row” from the drop-down list. Then you select the entire row and copy the formulae over to the new row.

      Best of luck!

      Cheers,
      NMW

  24. Awesome spreadsheet, but I have one issue. It seems that it doesn´t work when I try to use (set my home currency to norwegian “NOK” . Any thoughts to why it doesn´t work?

    btw, saying “NOKGBP” is okey, so it knows what NOK is.

    1. Heya,

      I didn’t program the NOK into the current spreadsheet, so you’ll have to add it manually. The currency exchange does work fine, but you’ll have to give it a specific symbol in column B (e.g. D for Danish Kroner).

      Another issue you might run into is that the Google FInance function doesn’t find the stock values because it lacks the right stock market. You’ll have to edit column F to include the Danish stock exchange. See the formula for examples.

      Hope this was helpful!

      Cheers,
      NMW

  25. Hi NMW,
    Thanks for making this. Finding it quite useful. I’m in the start of a long growth study. For now, I need to collect data on the Sector, Region and yearly dividend of about 1200 companies whose Ticker symbols and Company names I have. Could you please advise the appropriate Google finance formulae for the same?
    Thank you in advance.
    Jyo

Join the conversation!