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

Friday, November 10, 2017

Bye Yahoo Finance! Hi Alpha Vantage!

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

21 comments:

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

    ReplyDelete
  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?

    ReplyDelete
    Replies
    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?

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

      Delete
    3. Hi KPO,

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

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

    ReplyDelete
    Replies
    1. Hi K H Tan,

      No problem. Glad that it helped :)

      Delete
  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)?

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

      Delete
    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

      Delete
    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!

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

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

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

      Delete
  6. Hi,

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

    ReplyDelete
    Replies
    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 :)

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

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

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

      Delete