The users of my FREE Stock Portfolio Tracker, which tracks stocks that you own by transactions are complaining that their prices were not updating.
Specifically, the Yahoo prices on Yahoo Data Ref are not updating.
And they were wondering if it’s my spreadsheet acting up again.
The answer is no.
If you view the Yahoo Data Refs, you will see something like this.
What happens is that Yahoo, which got bought over by Verizon, decided to shut down the Yahoo Finance API, which they deem we are using not legally.
It has come to our attention that this service is being used in violation of the Yahoo Terms of Service. As such, the service is being discontinued. For all future markets and equities data research, please refer to finance.yahoo.com
This affects not just my Stock Portfolio Tracker.
Many folks get their live prices and data through this method for their website, Microsoft Excel and other forms of software.
You are likely not the only one disappointed.
I am too. I use my stock portfolio tracker as my main way of tracking.
This comes shortly after Google has decided to take down their stock portfolio feature.
So this has not been a good week.
How does this Impact You?
In the past, we have some outages.
Some of these are due to how Google Spreadsheet handles the data call.
Some of it is due to Kyith’s incompetency in implementation.
Some of it is due to the Yahoo Data.
This time it is likely this Yahoo Data Source is dead.
So this means that you need to rely on other data sources.
The Stock Portfolio Tracker takes its prices from Google or Yahoo.
The natural fallback plan is to use Google.
If your portfolio contains USA and Hong Kong stocks, Google still has prices for those. Your impact will be less.
But if your portfolio has exchanges that are not supported by Google (for example Singapore), you will face a problem.
You will need to find alternative data sources.
If you have those, you might be able to integrate them into the stock portfolio tracker.
Manually Update your Financial Asset Prices
One solution is to manually update the prices.
There is a reason I came up with a manual price column. It is meant for a situation like this.
If Google and Yahoo Stock Prices are not working, the manual price will take over.
Getting Data From Yahoo Finance Data via JSON Retrieve
As of Dec 2020, I was able to successfully retrieve the latest prices through function calls to a particular Yahoo Finance page.
I created a function in Tools > Script Editor to try and get prices from a query string in Yahoo Finance. The query string from Yahoo Finance provided the result in JSON format.
This works now and you can access this solution by reading this article Solution to Yahoo Finance Data Not Refreshing in Google Spreadsheet
The article shows how existing Stock Portfolio Tracker users with older versions can port and use this new implementation.
A lot of Scraping Solutions Failed Now
A reader of mine who commented below gave us a really nifty trick.
You see, the problem with scraping solutions is that, if the website you are scraping from changes things, it will break.
Secondly, if you have a lot of stocks, the generation would fail.
This is where the reader’s suggestion comes in. We basically use the IFERROR to catch that if Google Sheet fails to pick the price out from one source, we automatically try to grab from another source.
I currently import data from WSJ and Bloomberg for Singpore. I use an Iferror function to pick the first one that loads a value.
With the ticker in G8 of course
What I observe is that many of these scraping solutions failed over time.
Is the Stock Portfolio Tracker inferior now?
I think we lost something vital but for me, the beauty of the Stock Portfolio Tracker has always been its ability to track what you own by buy, sell, dividend, stock split transactions.
Whether price updates automatically is good to have.
For some users, Google prices currently are there, and for Singapore users, with this workaround, the only tired person is Kyith.
Unfortunately, if your exchange is not supported by Google, or not in Singapore you have to find the way to get the prices, and they might eventually still integrate well in Stock Portfolio Tracker.
Did you guys find some other data sources?
What other alternatives are out there for you?
Some of the write-ups on the subject:
- Free Online Investment Stock Portfolio Tracker Spreadsheet
- Updates to the Free Google Stock Portfolio Tracker
- Solution to Yahoo Finance Data Not Refreshing in Google Spreadsheet
- Automatically get Yahoo Finance End of Day Stock Price in your Google Spreadsheet
- Standard Chartered brings A-Grade Financial Planning Capability to their Customers for FREE - April 17, 2021
- Grow Your Wealth, Receive Recurring Income with MoneyOwl WiseIncome - April 11, 2021
- Does Your Desired Financial Freedom Lifestyle Cost SG$4.3 million? I break down the numbers realistically. - April 10, 2021