Replacing Yahoo Finance with the IEX API and Alpha Vantage in Google Sheets

For those of you that make use of the Yahoo Finance API, I’m sure you are all aware of the recent shutdown.

I have found a solution which completely replaces Yahoo Finance and provides all of the same functionality plus gives room for a ton of expansion in the future. I’ve switched over to using the free data available at IEX. If you are a user of my free dividend tracking spreadsheet on Google Sheets, this IEX integration is included in the latest version. IEX, at this time, only provides data for US traded stocks. The spreadsheet has been updated to also support some international stocks through the Alpha Vantage API.

For information on how to use Alpha Vantage in this spreadsheet, please refer to Step 3 below.

Here’s how to start using it yourself:

Step 1

IEX provides JSON (JavaScript Object Notation) data. I was initially scratching my head trying to integrate this raw data into the spreadsheet.  I was fortunate to find someone that had already done the hard work. The function is called IMPORTJSON. This takes a JSON feed and imports it into the spreadsheet. Watch the video on how to use it. The code is available here and is also included in my dividend tracking spreadsheet.

Step 2

After copying the IMPORTJSON code into your script editor, here’s how you use it:

Latest stock price: =IMPORTJSON("https://api.iextrading.com/1.0/stock/AAPL/quote","latestPrice")

Dividend amount:
=IMPORTJSON("https://api.iextrading.com/1.0/stock/AAPL/stats?filter=dividendRate","dividendRate")

Dividend pay date:
=IMPORTJSON("https://api.iextrading.com/1.0/stock/AAPL/dividends/3m","0/paymentDate")

Step 3

I’ve had a few requests to add support for looking up international stocks, specifically on the Canadian exchanges. IEX works great for US stocks, but does not provide any stock data from international exchanges. After reviewing a few websites, someone made an excellent script to use the Alpha Vantage API to lookup the latest stock price for stocks on other exchanges.

I’ve implemented these changes in the latest version. The spreadsheet will first try the IEX API. If that fails, then it will default to looking up the latest price information from the Alpha Vantage API. As always, any manually entered stock price and/or dividend amount will override the data obtained from IEX or Alpha Vantage.

a) First make sure you have the latest PortfolioTracker script (Tools > Script editor).

b) Get your free API key from Alpha Vantage. This key, which you will get instantly on the website, then gets pasted between the quotes replacing the “PUT YOUR API KEY HERE” text on the script.

c) Make sure to type in the ticker symbols using the appropriate notation. TSX:TD (for Toronto-Dominion Bank, as an example)

Latest price
=getAlphaVantagePrice("TSX:TD")

Latest dividend amount: this functionality is not yet available on Alpha Vantage. I will update this post when it becomes available.

You may also like...

58 Responses

  1. dividendgeek says:

    Very solid API. Thanks for sharing mate!!
    dividendgeek recently posted…October 2017 : Investment Portfolio StatusMy Profile

  2. John says:

    Scott,

    Thank you for all the work you have put in this over the years, have been using the sheet for over a year now and enjoy inputting the data every time I receive a dividend. I am having issues with the new code getting the information for RDS-A and ADDYY. I don’t know if it is specific to foreign companies but on the reference page it shows up “Error getting data” on “Last Price” “Dividends per share” and the ex-dividend dates. Any idea how to remedy this?

    Thank you.

    • scott says:

      Hi John,

      I’m glad you are enjoying the spreadsheet. It’s been fun to update it over the years. It turns out that IEX, which I’m using to retrieve financial data, is only for US companies. I’ll have to look into an alternative for foreign companies. In the meantime you can manually type in the latest price and dividend amounts on the ReferenceData sheet…anything manually typed in will override the errors you are getting.

      That should work until I find a better solution.

      Scott

      • scott says:

        John,

        If it is a US stock, then you’ll have to adjust your symbol to match how IEX has it. For example, Royal Dutch above is actually RDS.A at IEX. Change that on your Portfolio sheet and it will work.

        You can see how companies are listed here: https://iextrading.com/apps/stocks/#/

        • John says:

          Scott,

          Thanks for figuring that out for me, much appreciated. Unfortunately I do not think they have a symbol for Adidas (ADDYY) – or if they do, I cannot find it anywhere. Regardless, thank you.

          • scott says:

            I’m coming out with an update tonight that will allow lookup of other stocks, including foreign companies.

          • John says:

            Looking forward to the update and it is much appreciated!

          • scott says:

            Hi John,

            Well, it wasn’t “tonight,” but I just released an update that will switch to Alpha Vantage data to look up some stocks that do not work on IEX. Dividend data on Alpha Vantage is not yet available.

            ADDYY does work, however.

            Details on this update are above.

            Scott

  3. Rory says:

    Scott,

    Love the spreadsheet, been using it for quite a while. After the Yahoo thing, I just used the new link and redid the transaction sheet so that I didn’t have to mess with inputting any code. The first day it worked great, but yesterday and today, when I open or reload the page, it’s still giving me the prices from 2 days ago. Is anyone else having this problem? Thank you sir.

  4. Rory says:

    I figured out what happened. Disregard the previous comment.

  5. Drew says:

    Scott, Great work. I used your script and the updates were working fine until this week when they stopped. Are you or others aware of changes or reasons why the service is not updating?

  6. Boone says:

    Scott,

    Heckuva spreadsheet. Thanks for the work. 2 questions:
    1)If I rearrange the order of the sheets, is that going to throw anything off?
    2)I’m going to start working on a sheet to tend with stocks to monitor, but haven’t bought ( a watchlist). Any thoughts on adding something like that in the future?

    • scott says:

      Hi Boone,

      Thanks!

      1) the order of the sheets will not throw anything off
      2) I actually had a watchlist on an older version of the spreadsheet. I wanted to slim down the dividend tracking spreadsheet so removed it. I will be working on an updated watchlist in the not too distant future.

  7. Boone says:

    Scott,

    Might also be be handy to have today’s NASDAQ, S&P500, DOW, etc quotes as a header on one of the pages. Or maybe the TTM of those values.

  8. Very nice! I just went through this with Google Finance. After about a month, I’ve become used to (and actually prefer) the Morningstar portfolios function. The customization and available details are great. Maybe it’s a good thing that Google and Yahoo disabled their portfolios features!

  9. Charlie says:

    Good work and a few observations/questions.
    1) Perhaps you caught it by now but the version I downloaded was missing Telecom as a sector.
    2) An error is received (Reference Data) when the company name has an apostrophe (Casey’s/CASY). (I did a manual override).
    3) It appears some ADR issues have data and some don’t. I haven’t figured out the why (maybe the sponsor?) ex. AKO.B does, CMSQY doesn’t.
    4) OTC pink sheet data (US pricing/dividends on Canadian issues) not available. ex. HRNNF (corresponds to H.TO)
    5) OTCQB market data is lacking. I didn’t check the greys as I own none.
    6) ReferenceData generates ‘error getting data’ (ExDate, PayDate) when the company does not pay a dividend.

    Appreciate the effort you invested.

    • Charlie says:

      Two additions and one update:
      1) (update) GICS/S&P announced new Communications Services sector to replace Telecom effective 28 Sep 2018.
      7) (new) Optional use column P – DivCalendar – doesn’t lock to the year as do the other columns.
      8) (New): Column H – ReferenceData – IEX data for ex-div does not reflect current announcements. Ex: KMB shows the prior 7 Sep 2017 ex rather than the current 7 Dec 2017.

      • scott says:

        Hi Charlie,

        Thanks for the suggestions:
        1) I’ve added Communications Services
        2-5) There do seem to be some errors related to the ticker symbols that IEX and Alpha Vantage can look up. IEX has a way to see what stocks it works with…Alpha Vantage does not as far as I know. For stocks that do not work, you’ll have to use the manually entered price. I’m at the liberty of what these two free APIs can do. Intrinio can look up data from foreign exchanges but costs an individual about $50/month for that data. I could afford that for myself but it is on the order of $500/month per exchange if I were to release that for “free” to the users of this spreadsheet. I can’t do that.
        6) I’d just ignore the errors. I could also make them blank in cases of errors in future versions.
        7) Good point. Can you think of a way to allow this to work? I’m struggling to make it lock to a specific year.
        8) It is updated now. I don’t know how long it takes for IEX to update their data. I’m at the liberty of whatever that data says.

  10. Boone says:

    Is there a way to import the dividend info using the API with a drag and drop type of command? In other words, if I want to import 5 new stocks, do I need to copy and paste the IMPORTJSON API 5 times into 5 cells, then change to stock ticker symbol on each? Or is there a way to past the IMPORTJSON API once and then drag down, making google sheets change the ticker symbol based on another column (e.g. the “ticker” column)?

    • scott says:

      Hi Boone,

      That method will work. You can also just add new stocks to the Portfolio sheet and the dividend information will automatically be retrieved and available on both the Portfolio and ReferenceData sheets. You can take a look at the formula to see how that works.

      Scott

  11. Marc N. says:

    Scott,

    That is simply wonderful!! I have been looking for a working solution for quite some time now. I was using different API’s or HTML imports formulas but the technique you describe seems much more effective!!
    Moreover, IEX is offering access to a lot of data and stats.

    I wonder how many simultaneous queries it can tolerate. Previously when importing data from Yahoo Finance or GoogleFinance in Google Sheets, if my watchlist had more than ~100 stocks, data would not load easily. Have you encountered that problem?

    In any case, wonderful article!
    Thanks again!
    Marc

    • scott says:

      Hi Marc,

      I don’t think IEX or Alpha Vantage would have an issue. However, some people have mentioned errors in Alpha Vantage if too many requests are put in at once. I have an updated version coming out that adds a slight delay to the requests to limit this issue.

  12. Marc N. says:

    Hi Scott,

    I have been playing around with IEX and google sheets, and I made some observations that may be of use for you or your readers.

    When accessing dividends info, your example formula works well and the xpath for the selected quarter is #/key as seen in your example:
    =IMPORTJSON(“https://api.iextrading.com/1.0/stock/AAPL/dividends/3m”,”0/paymentDate”)
    where
    # = 0/ (the selected quarter)
    key = paymentDate

    However, when accessing financials information, the xpath needs to be: financials/#/key otherwise you get an error.
    Example:
    =IMPORTJSON(“https://api.iextrading.com/1.0/stock/AAPL/financials”,”financials/0/netIncome”)

    It’s the same case for earnings. The path needs to be earnings/#/key.
    =IMPORTJSON(“https://api.iextrading.com/1.0/stock/AAPL/earnings”,”earnings/0/actualEPS”)

    Marc

  13. Josh says:

    Hi Scott

    Tnx for this amesome google sheet.

    I have a problem with the sheet which is that is is not updating like it should. I have old “last price” for both of the Alpha Vantage and IEX columns. Do you know what can be wrong? I got the correct price when i first started with the sheet, but it has not been updating lately.

    • scott says:

      Hi Josh,

      I’ll add some triggers that will force the prices and data to reload every time the spreadsheet is opened, whenever there is an edit, and every 8 hours if the spreadsheet is left open.

  14. Niels says:

    Is there a website where I can check the syntax for international exchanges for Alpha Vantage? I can’t find a lot of my stocks on the European exchanges via Alpha Vantage.
    Examples.
    VHYL.EPA
    WEATP.EPA
    OILB.EPA
    PHAG.AMS

    These are all ETFs, maybe that’s less supported. But my ETFs on the Milan exchange work.

    • Alexander says:

      Me too – i cant find the syntax for Xetra and the symbols for Siemens, Fresenius, Continental and Aareal Bank

      Okay i got something, but it doesnt work in the sheed…..

    • scott says:

      Hi Niels and Alexander,

      I can’t find any documentation saying what exchanges Alpha Vantage supports either.

      So far I’ve figured these out:
      – TSX:TD for Toronto Stock Exchange
      – ES3.SI for Singapore exchange
      – .PA for French exchange (according to a blog comment)

      Any others that work?

  15. Pecunia says:

    Excellent sheet, thank you so much for this…is there also a plan to update the international version of the sheet with the IEX and Alpha Vantage api’s ?

    • scott says:

      Hi Pecunia,

      I’m actually working on incorporating the features of the international version into this version. It would make one less spreadsheet to try and keep updated. The new version will be coming out soon.

    • scott says:

      Hi Pecunia,

      The latest version of the dividend spreadsheet now supports many of the same features as the international version, namely currency conversion. Please let me know how you like it.

  16. H. Sadek says:

    I have been trying to use the Dividend payment date function by replacing the “paymentDate” value with “exDividendDate” but it does not work. Does anyone know how to get the ex-dividend date through the Import JSON function on Google sheets?

  17. B.G says:

    Hi scott

    i have tried the latest dividend worksheet, the latest price for singapore exchange stock can not update, for example i have tried SGX:D60 but the price is zero, may i know how to solve this?

  18. Peter says:

    hi Scott,

    thanks a lot for the file, it made my life a lot easier. The one thing I noticed is that for some of my stocks (STWD and RDS.B), exDiv date is out of date (IEX still shows December ’17)
    Lookig at NASDAQ dividend page:
    https://www.nasdaq.com/symbol/rds.b/dividend-history
    there are already exDates of March for both stocks. This is obviously not an issue with your file , but it is still strange…

  19. Lucas says:

    What if there’s a mutual fund that’s not available on IEX? Vanguard Target Date Funds, for example?

    • David says:

      I updated mine to use GOOGLEFINANCE as backup when IEX fails. That way it pulls in the data for mutual funds from Google. So for instance, E5 on my ReferenceData sheet looks like this (hopefully it comes through):

      =if(isblank(A5),,if(not(isnumber(IMPORTJSON(“https://api.iextrading.com/1.0/stock/”&A5&”/quote”,”latestPrice”))),if(not(isnumber(GoogleFinance(A5))),,GoogleFinance(A5)),IMPORTJSON(“https://api.iextrading.com/1.0/stock/”&A5&”/quote”,”latestPrice”)))

      And then just copy that down to the other cells. And I updated the dividend fields to pull from Google as a backup as well.

  20. Paul Asaro says:

    Hi Scott
    Is there any way to use this to get real time NAVs on Closed end funds?

    Thanks
    Paul

  21. Stephen c says:

    Scott, firstly thanks for all the help and instructions. I invest mainly in international stocks, is Alpha Vantage providing dividend data as of yet? Their website says they do however i have not been able to get it to work. Thanks

  22. Hello Scott…I appreciate this information you’ve provided. I am looking to bring in the latest dividend rate for BAYRY and other international stocks. I thought I’d check and see if happen to know if Alpha Vantage is now providing this data? If so, would you mind providing the appropriate code string for retrieving the information? TIA!
    PIV
    Passive Income Vortex recently posted…2018 December Dividend SummaryMy Profile

  23. Matt says:

    Had some problems related to defaulting to AlphaVantage on the ReferenceData tab. Would recommend swapping the formula in Col C with the following:
    =if(isblank(A6),,if(isnumber(J6),J6,if(isnumber(E6),E6,getAlphaVantageSlowly(A6,ROW()))))

    This eliminates the double call to the function which I think was causing problems on my end but is also more straight forward to understand and puts the Override as the primary value.

  24. kanna says:

    hi
    Is there a way to get ^VIX price for a day into spareadsheet using alphavantage api?
    Thanks

  25. Brad Forden says:

    I’ve found the AlphaVantage API reports does not correctly report Canadian TSE data. Sadly the unadjusted open/close prices are not correct before and after a stock split.

  1. May 21, 2019

    […] to fellow bloggers for your tips and tricks – twoinvesting and $$$KPO and […]

  2. June 9, 2019

    […] we can use to get the data related to dividend columns which is more reliable than Google’s api. You can read more about it here, do step 1 in this site, or follow my steps […]

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.