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
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
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
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
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.
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/
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!
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 Riches
You’re welcome, Tom. Feel free to look it over and grab whatever you like. I found that looking at other people’s spreadsheets was the best way to learn. I’d reverse engineer what I wanted to accomplish by seeing how other people had already done it.
Let me know if you run into any issues or if there are features you’d like to see.
Thanks!
Thanks Scott, Will do. Tom
Tom @ Dividends Diversify recently posted…Get Your Motor Runnin’
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!
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.
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
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.
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
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)
Hmm. Not sure about that. I’m at work now so can’t look into it further at the moment. I’ll get to it over the weekend. If you close the spreadsheet and reopen do the prices refresh?
This error has been fixed in the latest version.
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
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
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
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
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
Looks like the Yahoo finance API is gone. Do you have any solutions to get the data now?
I’m away from my computer at the moment so I can’t check. Do you mean that Yahoo stopped offering that data in the last day or two? It was working as of then.
Looks like it. Multiple reddit posts about the api no longer available. Possibly due to the Verizon takeover.
Can you send me a link to one of those reddit posts? Thanks!
Sorry. Wasn’t reddit, but here it is. https://www.elitetrader.com/et/threads/looks-like-yahoo-download-api-is-now-dead-11-1-2017.314661/
From Yahoo:
Admin Nixon
32m ago
Re: Is Yahoo! Finance API broken?
The new download issue which began 11/01/17 and returning an error 999 for most users is currently being investigated and we hope to have it resolved soon.
Thanks for the update, Tom. I’m on vacation now but will look into this further when I get home. There is another finance API source that I might look into using if this issue with Yahoo doesn’t get resolved.
Scott
Hi Adam,
Back from vacation. I’ve just released an update to the spreadsheet that no longer uses the Yahoo Finance API.
https://www.twoinvesting.com/2017/11/replacing-yahoo-finance-with-the-iex-api-in-google-sheets/
Spreadsheet was working as of last night (Nov 1). Now it returns the following error:
Request failed for https://download.finance.yahoo.com/d/quotes.csv?s=undefined&f=l1&p=.csv returned code 999
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
You’re welcome, Tom J. I’m glad you find it useful!
Scott
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
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
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
Hi Cathy,
An update has just been released which fixes the Yahoo Finance API issues. Enjoy!
Scott
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 Technologies
Hi Nathan,
Vacation was great in warm and sunny Tampa. 🙂 It was hard to come back to work in the cold. Just wanted to let you know that I released an update which uses a different free API to provide the financial data. Works much better than Yahoo Finance (or Google Finance) and provides a ton of data. I’ve only just scratched the surface in what I’ll be able to do with it.
Details on how to integrate it into your own spreadsheets are available here: https://www.twoinvesting.com/2017/11/replacing-yahoo-finance-with-the-iex-api-in-google-sheets/
Thanks for the Bitcoin!
Scott
Scott,
The updated IEX API works great. Thanks again for your work and sharing this with the masses.
Tom J
You’re welcome, Tom J!
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