More details about the new Dividend Stock Portfolio Spreadsheet

Over about the past week I’ve been working on a few big updates to my dividend stock tracking spreadsheet. It is now up to version 5. It is more automated and faster than ever. No more waiting for N/A’s or Loading…

 

Update #1

The major feature of this update is that it uses javascript as part of the Google Script editor to provide the lookup features to retrieve a stock’s last price, dividend amount, ex-dividend date, essentially anything that is available through the IEX API. This replaces Yahoo Finance and Google Finance for retrieving financial data.

I used the amazing resources available on Kyith’s website, Investment Moats, to learn how to do this.

To add these features to your own spreadsheet, add these functions to your script editor within your Google Sheets spreadsheet (Tools > Script editor). You can also grab a copy of my latest spreadsheet and start using it yourself. If you are a current user of an older version of my spreadsheet, getting started with the new version is as simple as copying the stock name and symbols from the Portfolio sheet and copying over the Transaction data.

 

Yahoo Finance recently stopped working (November 2017). In order to maintain the financial lookup capability of the spreadsheet, I have switched over entirely to the free service provided by IEX for US stocks. For more information on how I integrated that API into my dividend tracking spreadsheet, please see the post here: Replacing Yahoo Finance with the IEX API in Google Sheets

International stocks, such as Toronto-Dominion, TSX:TD, will also work through the Alpha Vantage API. Dividend amounts will still have to be manually entered since that data is not yet available. Please see Step 3 of this page for more information.

Update #2

I also completely revamped my page explaining how to set up your own dividend portfolio spreadsheet. I was getting a lot of the same questions. This should provide a great walk through on the best way to get started.

 

Click here to read about the new and improved Dividend Stock Portfolio Spreadsheet for Google Sheets

 

You may also like...

42 Responses

  1. Stalflare says:

    Ciao Scott,
    Once again great work with the spreadsheet, it’s really impressive and to me it’s a great way of pushing my programming boundaries further! As you know I trade in 3 different currencies and use options too, so I came up with a “Frankenstein” that kind of works, but there are parts of the new developments that you posted that are too cool not to “try to have”. Let’s start with the Yahoo Finance formula, you said that it can be used like the googlefinance() one, do you simply cut and paste in a cell? Or do you need it embedded in a separate part of the sheet (the script bit) like you did on yours? Why using Yahoo and not Google Finance data?
    Ciao and thanks!
    Stal

    • scott says:

      Hi Stal,

      Thanks! What you’ll want to do is grab a copy of the updated spreadsheet. Then, go to Tools > Script Editor. You can copy that entire code or just the functions for Yahoo Finance, which are near the bottom. You’ll then paste that into your own spreadsheet.

      From there, you use it like this =getYahooLastPrice(A1), where A1 contains the ticker symbol of the stock you want. The price will be updated whenever A1 gets changed or you click the run button with getYahooLastPrice selected. This will make more sense once you take a look at the Script Editor. You can create as many new functions like this as you want.

      To have it update whenever you open the spreadsheet or even at certain intervals (like every 5 minutes), you click the little clock symbol in the Script Editor. There’s also a way to put a button on the spreadsheet to force the data to update. I’m going to be working on getting this button working…haven’t figured it out yet.

      I used Yahoo Finance because they have an API which makes it easy to pull in dividend data. Googlefinance(), unfortunately, does not yet have that easy ability without resorting to grabbing it directly from the website. If googlefinance() had that ability, we wouldn’t necessarily even need to resort to using the scripting language.

      Let me know if you have any questions.

      Scott

      • Stalflare says:

        Thanks a lot Scott, I’ll give it a spin! Yes Google doesn’t give any dividend information and to a dividend investor like me it’s a bit of a pain, so far I have updated manually… 😛
        Thanks and ciao,
        Stal

        • scott says:

          This one also has the ability to update price and dividend manually. Anything typed into those fields will be used in place of anything the formulas bring in.

    • scott says:

      Hi Stal,

      As I’m sure you are aware, Yahoo Finance recently stopped working. I’ve integrated a replacement here: https://www.twoinvesting.com/2017/11/replacing-yahoo-finance-with-the-iex-api-in-google-sheets/

      • Stalflare says:

        Yup I have been following the developments, although I am out of the country until end November and will have no chance to update the new sheet that I was working on! Thanks for the update though, great work as usual!

  2. I use spreadsheet tools as well to manage my stock portfolio. You guys are far more advance than me. I will have to dig into this a little more and see what I can learn and apply to my own situation. Thanks for sharing this. Very educational. Tom
    Tom @ Dividends Diversify recently posted…Rags To RichesMy Profile

  3. Rick says:

    Scott- (or anyone) Quick question,

    How do I update all information from the previous spreadsheet and all of the details to the new and improved spreadsheet? Im sure is may be easy, but please share
    thanks!

    • scott says:

      Hey Rick,

      It is easy as copying over the orangish cells from the Portfolio page (name, symbol, etc) and then copying all the orangish cells from the Transaction sheet. Provided you hadn’t manually added anything, that should do it.

      Let me know if you have any issues.

      • Rick says:

        HI Scott,
        Great, I figured but wanted to check. All completed now (had to copy additional pages with orange cells, like div cal, and divpayout calc.. all set now and look forward to using the new sheets with some of the additional information that you have added. thanks much!
        Rick

  4. JLH says:

    i’m using the new google sheet. One thing i noticed is the last price on the reference sheet is not updating. Any ideas? For instance – the price on the sheet says AT&T is at 33.80, but I go to yahoo it says the price is 33.95. I keep trying to refresh the sheet , but the prices seem to be stuck.

    • scott says:

      Hi JLH,

      Since it is using scripts to update the stock data, I had it refresh every time you open the sheet. I’ll be adding a button to allow refreshing more often, but thought most people would not be using this spreadsheet to day trade or track the daily price changes. You can force a reload by going to Tools > Script Editor and then choose getYahooLastPrice and then click the Run button (looks like a little play symbol).

      I’ll be adding a refresh button to the sheet in the next update.

      Let me know if this works for you.

      Scott

  5. JLH says:

    When I refresh i am getting an error in the script code – line 78:
    var csvContent = UrlFetchApp.fetch(string).getContentText();

    At the top of the screen it says: DNS Error:http://undefined (line 78, file “Code)

  6. Stalflare says:

    Sorry Scott I have one more question…. The automatic Portfolio History updater points to a sheet that I think it’s not present on the main file “Portfolio History”. I can see a “Summary Historical” but I guess it’s a different thing altogether, I was expecting a sheet with a bunch of data one on top of the other… Sorry again for the strange/stupid question, I understand what it does and it’s a great help for a thing that I am doing manually today and that I can automate if I get this to work correctly… 😛
    Thanks!
    Stal

    • scott says:

      Stalflare,

      Not a stupid question at all. “Portfolio History” is a sheet that I’m still working on that will keep track of weekly portfolio values, dividend amounts, etc and then will display them as graphs. Not ready for primetime yet.

      Scott

      • Stalflare says:

        Oh I see! Then it’s more wait for me on that, the function is really interesting, I might try to fiddle with it and try to get it working as my reporting as an Index Fund does that on weekly bases, but if it’s done automatically I could do it DAILY and that’s just great because I get more data to play with… 🙂
        I’ll wait for the update then!
        Ciao ciao
        Stal

  7. Steve says:

    New spreadsheet is working very well for me. One question, I don’t see the Watchlist tab in the new version of the spreadsheet, why was this removed?
    Steve

    • scott says:

      Hi Steve,

      It was removed because there were so many features that kept breaking. I’m debating about releasing a stand-alone watchlist spreadsheet. Or, would everyone prefer to have it remain part of the original spreadsheet? I could add it back.

      Scott

  8. Adam says:

    Looks like the Yahoo finance API is gone. Do you have any solutions to get the data now?

  9. Tom Jacky says:

    Scott,
    Awesome spreadsheet (again). I was using your last version for over a year and was thankful for the time it saved me entering data. The new version is even better. Thanks for all your work.

    Tom J

  10. Cathy says:

    Scott: I made a copy of your spreadsheet, but when I try to authorize the yahoo update script, I get the following error message:

    Request Failed for https://download.finance.yahoo.com/d/quotes.csv?s=underfined&f=|1&p=.csv returned code 999. Details Dismiss

    I have used your old spreadsheet for over a year. I love it and am trying the move my data to the new spreadsheet. I haven’t moved my data yet as I wanted to make sure the update function worked first.

    Thanks, Cathy

    • scott says:

      Hey Cathy,

      Yeah, I just got on vacation and people have been messaging me letting me know this. Yahoo just turned off access to their Finance API, I guess. Before I get this fixed, the new spreadsheet has the ability for you to manually type in stock prices and dividend information.

      The old spreadsheet is also affected by this change.

      Scott

  11. Cathy says:

    Scott, It makes sense because yahoo updates stopped in my old spreadsheet too. I will wait for word that it is fixed.

    Thanks and enjoy your vacation,

    Cathy

  12. Hey Scott. I hope you enjoyed your vacation. Just wanted to say that I played around with the new spreadsheet last week and it’s such an improvement over the last version. Thanks so much for spending time on this and for sharing it with us.

    I noticed the API is broken, hopefully, you have a workaround.

    I’m going to send you some Bitcoin as a thank you.
    Investment Hunting recently posted…Stock Buy and Sell – HIVE Blockchain TechnologiesMy Profile

  13. Tom Jacky says:

    Scott,
    The updated IEX API works great. Thanks again for your work and sharing this with the masses.

    Tom J

  14. Robert says:

    Scott and Johnny:
    Have only recently found your website. Superb googlesheet development! I have been using a modified version of your your dividend tracking sheets which worked seamlessly after installing the JSON script and the Alpha Advantage key
    All the formulas have recently stopped importing. I reran the JSON script, and even tried a new AlphaAdvantage api key. All Google permissions seem active.
    Here is a sample of an import formula for dividend. Any ideas? I have come to rely on this tool, so I am feeling quite stuck. All help appreciated.

    =if(isnumber(IMPORTJSON(“https://api.iextrading.com/1.0/stock/DOW/stats?filter=dividendRate”,”dividendRate”)),IMPORTJSON(“https://api.iextrading.com/1.0/stock/DOW/stats?filter=dividendRate”,”dividendRate”),””)

    (the ticker DOW, in my spreadsheet, is actually a relative ref to a column of ticker symbols, hence the if statement to eliminate errors.)

    RObert

Leave a Reply

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

CommentLuv badge

This site uses Akismet to reduce spam. Learn how your comment data is processed.