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.
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
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())
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".
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
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
You're my life-saver! Hooorray!
ReplyDeleteHi TI,
DeleteYou are welcome :)
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?
ReplyDeleteHi Passive Income Farmer,
DeleteI 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?
Hi Passive Income Farmer,
DeleteUpdated 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.
Hi KPO,
DeleteWorks like a charm! Now its not loading any more "0"s anymore! Thanks so much!
Hahaha. That's great!
DeleteFound out about this thru the FB link and Kyith's blog.
ReplyDeleteThanks a lot for the workaround !
Hi K H Tan,
DeleteNo problem. Glad that it helped :)
I'm still getting 0 on my google sheet file :(
ReplyDeleteCould this be related on some different data formatting on non-US accounts (such as mine in Italian)?
Hi P.Greco,
DeleteIt 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.
i guess it's something not related to the symbol.
DeleteIndeed 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
My bad...i managed to make your script to work :)
DeleteThe 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!
Hahaha. Sure! Glad that you managed to get it working :)
DeleteHi, i pasted the code in script editor but i couldn't get any function in the spreadsheets
ReplyDeleteHi Unknown,
DeleteJust 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.
Hi,
ReplyDeleteWas wondering if you happen to know how to retrieve the change, 52wk low and 52wk high?
Hi weirdo,
DeleteBased 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 :)
Thanks for your reply. Not sure how to use the info in stocks.cafe into google spreadsheet. How about 'change'?
DeleteIt seems like bonds cannot be displayed anymore? Recently I see my bonds all $0.00 price.. Do you have this issue too?
ReplyDeleteHi WiRuS,
DeleteI 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...
Hi... how can i modify your formula so i can get real time quotes? maybe not every second but every 15 min would do...
ReplyDeleteThanks!
Hi Paul,
DeleteI 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!
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.
ReplyDeleteIs it still working for you? What happened? I was calling once an hour for 25 funds - is that too many calls?
Hi Nicholas,
DeleteOps, 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.
https://www.alphavantage.co/support/#api-key
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.
Hi KPO thanks for this, really useful.
ReplyDeleteWill 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.
Example
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
Hi vx,
DeleteOps, 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!
Hi there, I wrote a simple script to push price notifications to my slack at 10AM/4PM, but I noticed that AV has stopped giving SGX data since 13 July. May I ask how do you guys get reliable data other than scraping finance.yahoo ?
ReplyDeleteHi kenshinjeff,
DeleteI know man. So annoying, both StocksCafe and Alpha Vantage broke in the same month. I am currently scrapping yahoo and WSJ but this method is not reliable at all.
I have a rough idea but need time to work on it...