Note: StocksCafe API is now deprecated and no longer available anymore - updated on 5th July 2020.
Ever since Yahoo abandoned us, we were left stranded without a data source to fetch stock prices. I previously blogged about an alternative - Bye Yahoo Finance! Hi Alpha Vantage! but it has its limitations too. For unknown reasons, sometimes it just doesn't work/return the price of a particular stock code/counter. I suspected that it could be due to the high number of API calls and added additional control such as retrieving the stock price only if we are still owning them but it still wasn't very successful.
As a result, I even modified my google spreadsheet by having a second and third column to re-call the API if it fails. I even have a "Manual" column to fill in the price on my own if all else fails... Yes, it do happen ~.~"
Regular readers will know that we have been using StocksCafe to track the performance of our portfolio and it has released its own API to access stocks data a few months back. I started testing it and found it to be extremely reliable. I no longer need to update the stock price manually! Unfortunately, it is only limited to Friends of StocksCafe (paid users).
Evan's (founder and sole developer of StocksCafe) story is pretty interesting and you can read about it here - The True Cost of Being Free. We are called friends because it started out as a donation to keep StocksCafe alive due to the huge licensing cost.
If there is no chance of you becoming a friend, Alpha Vantage is still the way to go. Otherwise, Friends of StocksCafe or potential Friends, the below steps will be very helpful if you are tracking your portfolio using google spreadsheet too (like us):
1. Go to your profile and take note of your API key. There is a limit on your usage so I do not recommend sharing your key - 100 per day, reset at 12 am SGT. You can see how much we have contributed too :)
2. Go to Script Editor in Google Spreadsheet
Open up the relevant Google spreadsheet, go to "Tools" and click on "Script editor".
3. The API documentation can be found here. If you are in IT, this should be familiar to you and you can do a lot more than what I am going to share. Otherwise, it will probably look very foreign/alien to you, so you can just copy and paste the below code snippet into the newly opened up window/tab.
4. Modify the code slightly by replacing your username, API key, the sheet name and the relevant columns for the function - dailyUpdateStocksCafeColumn() or dailyUpdateStocksCafeColumn2(). Do not modify the rest of the code or do it at your own risk!
5. Go to Current Project's Trigger from Edit.
6. Click on Add Trigger. The project trigger should be empty. Note that the trigger has a 0% error rate.
7. Specify the function you want to run, it should either be dailyUpdateStocksCafeColumn or dailyUpdateStocksCafeColumn2. Next, change the event source to be Time-driven, day timer, the time you will like it to be updated and proceed to save it. You can reference mine below.
What this does is on a daily basis, at the specified time, the function will be called/ran automatically and the specified price column will be updated with the end of date stock price from StocksCafe. Updating the stocks prices once a day will ensure that we are keeping within the limits of the API usage.
As of this moment, StocksCafe has data for SGX, HKEX, KLSE and USX so it is not just limited to SGX stocks data. Evan was kind enough to provide a special discount for KPO and CZM readers! You can sign up using our referral link and see if it suits you first before deciding if you want to contribute.
Current Price:
- Monthly Plan: SGD 4.9 a month
- Annual Plan: SGD 39 a year (SGD 3.25 a month or >30% OFF)
- 7 Years Plan: SGD 215 for 7 years (SGD 2.56 a month or >45% OFF)
KPO Referral Price:
- Monthly Plan: SGD 3.9 a month (~20% OFF)
- Annual Plan: SGD 35 a year (~SGD 2.90 a month or ~40% OFF)
- 7 Years Plan: SGD 195 for 7 years (~SGD 2.32 a month or ~52% OFF)
Benefit: You get an excellent portfolio tracker and I get a follower? lol. If you ever become a "Friend of StocksCafe", I get a 20% commission! At this price point (~$2+ per month - about 2 cups of kopi/teh or $3/4+ per month - 1 cai peng/meal), I think it is a bargain for an intelligent portfolio tracker, access to quality data and many other features.
Anyway, hope this has helped you too :)
Do like any of the following for the latest update/post!
1. FB Page - KPO and CZM
2. Twitter - KPO and CZM
3. Click here to subscribe using email :)
4. Instagram - KPO_and_CZM (Did you see those delicious food photos to the right --> Unfortunately, you can't see it on mobile.)
Ever since Yahoo abandoned us, we were left stranded without a data source to fetch stock prices. I previously blogged about an alternative - Bye Yahoo Finance! Hi Alpha Vantage! but it has its limitations too. For unknown reasons, sometimes it just doesn't work/return the price of a particular stock code/counter. I suspected that it could be due to the high number of API calls and added additional control such as retrieving the stock price only if we are still owning them but it still wasn't very successful.
As a result, I even modified my google spreadsheet by having a second and third column to re-call the API if it fails. I even have a "Manual" column to fill in the price on my own if all else fails... Yes, it do happen ~.~"
Regular readers will know that we have been using StocksCafe to track the performance of our portfolio and it has released its own API to access stocks data a few months back. I started testing it and found it to be extremely reliable. I no longer need to update the stock price manually! Unfortunately, it is only limited to Friends of StocksCafe (paid users).
Evan's (founder and sole developer of StocksCafe) story is pretty interesting and you can read about it here - The True Cost of Being Free. We are called friends because it started out as a donation to keep StocksCafe alive due to the huge licensing cost.
If there is no chance of you becoming a friend, Alpha Vantage is still the way to go. Otherwise, Friends of StocksCafe or potential Friends, the below steps will be very helpful if you are tracking your portfolio using google spreadsheet too (like us):
1. Go to your profile and take note of your API key. There is a limit on your usage so I do not recommend sharing your key - 100 per day, reset at 12 am SGT. You can see how much we have contributed too :)
2. Go to Script Editor in Google Spreadsheet
3. The API documentation can be found here. If you are in IT, this should be familiar to you and you can do a lot more than what I am going to share. Otherwise, it will probably look very foreign/alien to you, so you can just copy and paste the below code snippet into the newly opened up window/tab.
4. Modify the code slightly by replacing your username, API key, the sheet name and the relevant columns for the function - dailyUpdateStocksCafeColumn() or dailyUpdateStocksCafeColumn2(). Do not modify the rest of the code or do it at your own risk!
5. Go to Current Project's Trigger from Edit.
6. Click on Add Trigger. The project trigger should be empty. Note that the trigger has a 0% error rate.
7. Specify the function you want to run, it should either be dailyUpdateStocksCafeColumn or dailyUpdateStocksCafeColumn2. Next, change the event source to be Time-driven, day timer, the time you will like it to be updated and proceed to save it. You can reference mine below.
What this does is on a daily basis, at the specified time, the function will be called/ran automatically and the specified price column will be updated with the end of date stock price from StocksCafe. Updating the stocks prices once a day will ensure that we are keeping within the limits of the API usage.
As of this moment, StocksCafe has data for SGX, HKEX, KLSE and USX so it is not just limited to SGX stocks data. Evan was kind enough to provide a special discount for KPO and CZM readers! You can sign up using our referral link and see if it suits you first before deciding if you want to contribute.
Current Price:
- Monthly Plan: SGD 4.9 a month
- Annual Plan: SGD 39 a year (SGD 3.25 a month or >30% OFF)
- 7 Years Plan: SGD 215 for 7 years (SGD 2.56 a month or >45% OFF)
KPO Referral Price:
- Monthly Plan: SGD 3.9 a month (~20% OFF)
- Annual Plan: SGD 35 a year (~SGD 2.90 a month or ~40% OFF)
- 7 Years Plan: SGD 195 for 7 years (~SGD 2.32 a month or ~52% OFF)
Anyway, hope this has helped you too :)
Do like any of the following for the latest update/post!
1. FB Page - KPO and CZM
2. Twitter - KPO and CZM
3. Click here to subscribe using email :)
4. Instagram - KPO_and_CZM (Did you see those delicious food photos to the right --> Unfortunately, you can't see it on mobile.)
