$$$ KPO and CZM $$$: Bye Yahoo Finance! Hi Alpha Vantage!

Friday, November 10, 2017

Bye Yahoo Finance! Hi Alpha Vantage!

Update on 15th July 2020 - It seems that Alpha Vantage is no longer provided SGX data too...

Yahoo Finance decided to discontinued their API services last week and cause a lot of spreadsheets and Excel around the world to break including mine. lol. SGX data is very expensive hence Google Finance does not provide it and others charge a fee for it. Stocks.cafe (formerly SGX cafe) is paying thousands for the licensing fee - The True Cost of Being Free.

Kyith from Investment Moats was kind enough to come out with a workaround by pulling all the SGX stock price from his own site/server - Yahoo Finance Data Shuts Down – My Modification to My Stock Portfolio Tracker. However, that is slightly overkilled (loading 1022 stocks) if I am only interested in a few stocks' price.

While looking online for another alternative for SGX data, I saw people recommending Alpha Vantage and decided to give it a try. It is actually very easy to set up and I also found a code to parse the JSON data which makes my life even easier! So I decided to share a step by step instructions on how to set it up :)

1. Register for an Alpha Vantage API key

Go and Claim your API Key by filling up a simple form. Only first name, last name and email are required. After you have submitted, copy down the provided API key somewhere immediately! They do not send any email and you cannot login to look for it again.

2. Go to Script Editor in Google Spreadsheet

Open up the relevant Google spreadsheet, go to "Tools" and click on "Script editor". Next, copy and paste the below code snippet into the newly opened up window/tab.

You can name the project whatever you want and leave the script name as it is. It should look something like below.

Once you are done, save the project.

### Update on 11-11-2017
Some of you may have encountered a weird bug (getting a 0 instead of the latest price) which I believe is due to Alpha Vantage dropping the requests. I have added a sleep function so that it will not make all the requests at once. On the bright side, the longest it should take is 30 seconds due to limitation from Google Script side (all custom function will fail if it sleeps for longer than 30 seconds).

The function should be called in this way: =getAlphaVantageSlowly(B4, ROW())

3. Call the Function and Get your Price!

Simply call the function to get the latest price! Do note that the stock code format is similar to Yahoo Finance - "ABC.SI".

If you are more technical and can write your own code, Alpha Vantage provides other data as well, do refer to their API documentation for more information.

Hope this has helped you :)

Credits: The above code was found in one of investment moat's comments shared by chris.
Limitations: API call frequency does not extend far beyond ~100 calls per minute - Support


  1. You're my life-saver! Hooorray!

  2. My cells arent refreshing the same way for each time i open my google sheets. Sometimes it shows a 0, sometimes the right figure. Any idea how to solve this?

    1. Hi Passive Income Farmer,

      I encountered it when I tried to increase the usage. I believe it is because we are making too many requests at once so the server (Alpha Vantage) side will drop some of the requests, resulting in 0 results being returned.

      I am writing/testing another function by adding a timer to "sleep" before making another request. The trade off is it will take longer to populate the whole spreadsheet. Shall update again if I can solve it. Meanwhile you can stick with Kyith's workaround if you are loading a lot?

    2. Hi Passive Income Farmer,

      Updated it with sleep! Works for me when I tested it. Make sure you call the function with ROW() as the time to sleep so every row sends the request at different time.

    3. Hi KPO,

      Works like a charm! Now its not loading any more "0"s anymore! Thanks so much!

  3. Found out about this thru the FB link and Kyith's blog.
    Thanks a lot for the workaround !

    1. Hi K H Tan,

      No problem. Glad that it helped :)

  4. I'm still getting 0 on my google sheet file :(
    Could this be related on some different data formatting on non-US accounts (such as mine in Italian)?

    1. Hi P.Greco,

      It should work for non-US stocks as well. For Singapore stocks, we need to append ".SI" to the stock code. I did a quick check, for France stocks, you will have to append ".PA" instead.

    2. i guess it's something not related to the symbol.
      Indeed i'm able to retrieve the Json data with their URL address https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&symbol=SWDA.MI&apikey=(mykey) and the .csv file as well.
      The issue is just in the google spreadsheet which using the script returns 0

    3. My bad...i managed to make your script to work :)
      The issue was that i was not pointing in a specific cell i was passing the stock ticket in the function itself...
      Now it's ok :9
      Thanks for the script and your precious help!

    4. Hahaha. Sure! Glad that you managed to get it working :)

  5. Hi, i pasted the code in script editor but i couldn't get any function in the spreadsheets

    1. Hi Unknown,

      Just paste in the function and fill in the relevant parameters. The cell will highlight it as an error but ignore it. I hope it is working for you now.

  6. Hi,

    Was wondering if you happen to know how to retrieve the change, 52wk low and 52wk high?

    1. Hi weirdo,

      Based on the API Documentation in Alpha Vantage, they do not provide 52 week low and high. I will recommend that you go to stocks.cafe and try that out instead :)

    2. Thanks for your reply. Not sure how to use the info in stocks.cafe into google spreadsheet. How about 'change'?

  7. It seems like bonds cannot be displayed anymore? Recently I see my bonds all $0.00 price.. Do you have this issue too?

    1. Hi WiRuS,

      I do not have any bonds so did not encounter this issue. I tested earlier and it seems that bond price cannot be fetched. Not too sure what is the reason though...

  8. Hi... how can i modify your formula so i can get real time quotes? maybe not every second but every 15 min would do...


    1. Hi Paul,

      I am guessing you are referring to stock quotes from other exchanges because I do not remember SGX data being real time. In addition, I feel that there is little value in getting real time quotes.

      As much as I would like to help you, I don't think I will be able to find time to look into your request. Hope you will eventually find what you are looking for!

  9. Hey! Thanks for this - I must say it was very useful... when it worked! For the last two weeks or so I get an instant fail when I run the code on a trigger and now it fails also when I run from within the Script file directly.

    Is it still working for you? What happened? I was calling once an hour for 25 funds - is that too many calls?

    1. Hi Nicholas,

      Ops, we were overseas, hence the delay in response. Yes, it is still working for me. I can only guess that you probably made too many calls, do refer to the documentation below for more information.


      We are proud to provide free API service for our global community of users and recommend that you make API requests sparingly (up to 5 API requests per minute and 500 requests per day) to achieve the best server-side performance.

  10. Hi KPO thanks for this, really useful.

    Will like to check if you had come across cases where the ticker works with Alpha Vantage URL output, but not with the Google script? The script works with other tickers, so I'm not sure what's the issue. Wonder if you have a clue? Thanks in advance.

    Ticker: ^STI for STI Index
    Does not work with the Google script but works well here: https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&symbol=^STI&apikey=MYKEY

    1. Hi vx,

      Ops, I forgot to check my comment section. I took a quick look and realized that this is due to the special character "^".

      I am too lazy to modify the code but a quick fix for your situation would be to do URL encoding on the special character.

      In your case, "^STI" = "%5ESTI" (I have tested it and it should work.

      Hope this helps and glad that you found it useful!