Yahoo Finance Data Shuts Down - My Modification to My Stock Portfolio Tracker | Investment Moats Skip to Content

Yahoo Finance Data Shuts Down – My Modification to My Stock Portfolio Tracker

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.

=IMPORTXML("http://quotes.wsj.com/SG/"&G8&"","//span[@class='cr_num cr_curr_price']/span[@id='quote_val']")

And
=IMPORTXML("http://www.bloomberg.com/quote/"&G10&"","//span[@class='priceText__1853e8a5']")

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:

Freegan, Dumpster Diving and Financial Security, Independence
← Previous
How 1.95% More Interest on Savings Can Greatly Impact Your Wealth | The DBS Multiplier Account
Next →

This site uses Akismet to reduce spam. Learn how your comment data is processed.

K

Tuesday 5th of May 2020

Hi Kyith,

Your portfolio tracker is amazing. Have been using it since my investing journey.

However I have some problems with updates of the stock prices. I understand that now we are fetching with your own data that you update yourself, can i just check how often you update it? It seems to me that for the past week, the prices haven't been updated.

Or is there a better alternative to this new getSGPrice() method now?

Thank you!!

Anton

Saturday 30th of November 2019

Thank you! I just can't say how much I appreciate your work. I've just started investing and your solution is the best

Kyith

Saturday 30th of November 2019

Hi Anton thanks for the feedback. Means a lot to me.

Jeff

Saturday 27th of April 2019

I currently import data from WSJ and Bloomberg for Singpore. I use an Iferror function to pick the first one that loads a value.

=IMPORTXML("http://quotes.wsj.com/SG/"&G8&"","//span[@class='cr_num cr_curr_price']/span[@id='quote_val']")

And =IMPORTXML("http://www.bloomberg.com/quote/"&G10&"","//span[@class='priceText__1853e8a5']")

With the ticker in G8 of course

Kyith

Saturday 27th of April 2019

Wow! interesting! I didn't know about this trick! Looks good!

Michael

Friday 26th of April 2019

May i know if you're still update SGX end of day data? mine doesnt seem to be updated.

Kyith

Friday 26th of April 2019

yup i do

Eugene

Sunday 15th of April 2018

Hi Kyith,

Its wonderful for me to discover your stock tracker as its so well designed for DCA users like me.

I have been searching high and lows in your site regards to adding foreign exchange such as Amsterdam exchange into the yahoo finance data however, I am not able to dig out any thing about it.

May I know how can I make a yahoo finance data reference to Amsterdam market ?

Kyith

Tuesday 17th of April 2018

Hi Eugene, Yahoo Finance stop us from using their data. However, Google Finance is still functioning. If you look at my spreadsheet there is a column on getting the Google Finance price. For Amsterdam, you add an "AMS:" in front. So for Gemalto it is "AMS:GTO" Congrats this will work for Holland and Dutch folks!

This site uses Akismet to reduce spam. Learn how your comment data is processed.