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

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

Leave a Reply

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

CommentLuv badge