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:

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

Mikhail from Russia

Friday 3rd of September 2021

Copied, prices work fine (if US then GoogleF shows, if not - Yahoo), opened script editor, approved permissions, ran - fine also! Thanks, Kyith! Very handy and reliable spreadsheet! Main thing - it still works fine almost for 10+ years despite some permission issues etc. it's a masterpiece for sure and no similar sheets around available! Thanks for all your efforts and support!

Kyith

Monday 6th of September 2021

Hi Mikhail, thanks for the shout out. Glad that you are a long time user!

Jacob

Thursday 3rd of June 2021

Hi Kyith, amazing and helpful tracker, appreciate you for sharing this with everyone.

Wanted to ask, for about a month now my "Portfolio History" tab populates daily row but it's identical to May 12 2021 row. Meaning no change every night, but we should expect nightly fluctuations. How do I resolve this? The "Portfolio Summary" tab has daily fluctuations as expected.

Thanks for the help!

Jacob

Friday 4th of June 2021

Hi Kyith, I just checked my trigger google script again, no issue though. So I don't think that's the problem.

Jacob

Friday 4th of June 2021

Hi Kyith, ok will take a look at that. The strange thing is that the script has worked fine for the past year. Only in the past month, the issue surfaced.

Kyith

Friday 4th of June 2021

Hi Jacob, I think you would have to set the Trigger in the Google Script such that on X time of the day, the Script will move the data from Portfolio Summary over to Portfolio History. In my main article, I have written how to set the trigger: https://investmentmoats.com/stock-market-commentary/portfolio-management/introducing-our-free-stock-portfolio-tracker-spreadsheet/

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!

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