Skip to Content

Solution to Yahoo Finance Data Not Refreshing in Google Spreadsheet

A few readers who are currently using my FREE Stock Portfolio Tracker have emailed me that their spreadsheet have failed to get any prices from Yahoo Finance in the past two days (This was written on 11th Dec 2020)

In this post, I would provide a fix.

Whether it is permanent or not is another question altogether.

About My Stock Portfolio Tracker

My stock portfolio tracker is a free Google Spreadsheet that allows you to monitor and keep track of your investments.

I am using this to monitor my portfolio for the past 7 years.

This spreadsheet is different from others in that

  1. It allows you to track your stocks, bonds or other assets based on transactions. Very likely, you would buy a single share or bond over a period. For example, you would buy the MSCI World ETF IWDA every quarterly. This spreadsheet allows you to input each buy, sell, dividend, stock split, capital reduction transaction. These transactions will aggregate in summary, and you can review your average cost and how much unrealised returns, realised returns, dividends earned.
  2. The spreadsheet auto-grab the prices from Google Finance, Yahoo Finance, Bloomberg. The data source will depend on where you source them from. This means that you do not have to keep updating the spreadsheet.
  3. The spreadsheet data belongs to you and do not reside on a third-party server.

This is the three big reasons people like about it.

Problem: Google and Yahoo Finance Data Not Working

The problem right now is that point #2 is not working so well.

Over the years, various data sources have gone dead. If not, Google spreadsheet has issues handling them.

Here are a quick summary of the problems over the years:

  1. Google Finance supported Singapore SGD stocks but Google eventually does not wish to pay SGX for the price data feed. Google Finance will still work for markets like Hong Kong and the USA
  2. When Yahoo was acquired by Verizon, Yahoo also pulled the API off. Now the API is available but there is also a subscription cost based on usage
  3. There are people with various implementations scraping price quotes from Yahoo, Bloomberg, Morningstar and Wallstreet Journal
    1. These were successful but over time not working very well efficiently
    2. Google by its nature prevent numerous ImportXML function calls from taking place often

The latest outage seem to do more with Google Spreadsheet.

They are having problems retrieving data from CSV files. It is not a Yahoo Finance or Data source thing. This has hit people with various different implementation.

In my first implementation, you will see #ERROR! on Yahoo Data Ref sheet.

I had another implementation for some readers, which is to use a javascript function to retrieve the Yahoo Finance price. This is located in your Tools > Script Editor.

Since we are also retrieving a CSV file for this implementation, this is also hit with the problem and you will observe that under Yahoo Price it shows as #ERROR!.

My Solution to Pull Market Prices from Yahoo Finance Data (and Other Data)

The solution that I found is that Yahoo does provide data of stocks and unit trusts in JSON format, instead of CSV format.

Since the problem is that Google has an issue processing .CSV files, a JSON file format implementation should work.

I am going to talk a little about the technical implementation to help those looking for how it works. If you are not interested in this you may skip this part.

Suppose we wish to pull the latest price of the iShares Core MSCI World UCITS ETF USD (Acc), which is listed on the London Stock Exchange.

The Yahoo Finance stock quote is IWDA.L.

There are two different query strings that we can implement to get the latest market price data

https://query2.finance.yahoo.com/v7/finance/options/IWDA.L

This will give you this JSON output:

Observe that the variable regularMarketPrice shows the price of IWDA.L

The second query string that does almost the same thing is this:

https://query2.finance.yahoo.com/v10/finance/quoteSummary/IWDA.L?modules=price

This will also achieve the same result.

I like the second implementation because actually you can specify more things in the modules part, instead of only price, and you can get more information.

I have implement these as functions that your Google Sheet can call to get the prices. Here are the two implementations for the two query strings:

//this is the options JSON implementation

function getYPrice2(symbol) {
  symbol = symbol || "VWRA.L";
  symbol = encodeURI(symbol);
  Utilities.sleep(Math.floor(Math.random() * 5000))
  var url = 'https://query2.finance.yahoo.com/v7/finance/options/'+ symbol; // last one day history
  Logger.log(url);  
  var response = UrlFetchApp.fetch(url, {muteHttpExceptions: true});
  var responseCode = response.getResponseCode();
  if (responseCode === 200) {
    var chain = JSON.parse(response.getContentText());
    Logger.log(parseFloat(chain.optionChain.result[0].quote.regularMarketPrice));
    return parseFloat(chain.optionChain.result[0].quote.regularMarketPrice);
  }else{
     return -1; 
  }
}


//this is the stock JSON implementation
// guide 1: https://stackoverflow.com/questions/44030983/yahoo-finance-url-not-working
// guide 2:

function getYPrice3(symbol) {
  symbol = symbol || "VWRA.L";
  symbol = encodeURI(symbol);
  Utilities.sleep(Math.floor(Math.random() * 5000))
  var url = 'https://query2.finance.yahoo.com/v10/finance/quoteSummary/'+symbol+'?modules=price'; // last one day history
  Logger.log(url);  
  var response = UrlFetchApp.fetch(url, {muteHttpExceptions: true});
  var responseCode = response.getResponseCode();
  if (responseCode === 200) {
    var quote = JSON.parse(response.getContentText());
    Logger.log(parseFloat(quote.quoteSummary.result[0].price.regularMarketPrice.raw));
    return parseFloat(quote.quoteSummary.result[0].price.regularMarketPrice.raw);
  }else{
     return -1; 
  }
}

You can then use either getYPrice2 or get YPrice3 to get the prices in your Google Sheet.

Here is how it is implemented:

In this implementation, I am trying to get the latest price of Mapletree Commercial Trust. The Yahoo Quote is N2IU.SI.

Using getYPrice(E10) translate to getYPrice(“N2IU.SI”) and thus was able to successfully retrieve the share price.

  • Each price retrieval is not immediate. I have implemented asleep for 5000 milliseconds or 5 seconds times a random number before the function tries to retrieve the price.
  • With this implementation, you do not need the sheets Yahoo Data Ref, Yahoo Data Ref HKD, Yahoo Data Ref USD, if they are successfully implemented.
  • It looks like it will work for both stock and unit-trust
  • Apparently, these function will retrieve the latest prices, if the stock, unit trust quote is available on Yahoo Finance

Now let me explain how you can get this functionality.

For those New To My Free Stock Portfolio Tracker

If you are new to the spreadsheet, these changes are incorporated already.

To download and use the sheet, I would recommend you to read through this post on how to use the stock portfolio tracker.

If you really wish to download the latest version you can make a copy of the Google Spreadsheet here. Then you can modify and use it. (This is not an Excel spreadsheet that you will download to your desktop to use it. If you download it as a XLS or XLSX.

Once you have made a copy, you can use it like any new user. Delete most of the rows, except the second row (which contains all the formulas) and then edit the second row to put in your first stock transaction. I would suggest you read through that post in the link above to really understand how it works.

For those that have an Existing Older Version of My Free Stock Portfolio Tracker

If you have an existing version, you would have to do some modifications.

I have created a video that will take you through the changes:

If not, here are the instructions.

Step 1: Go to Tools > Script Editor

The script editor can be found by going to Tools on the menu bar and then <> Script Editor

Step 2: Copy getYPrice Function into Script Editor

Go to the last empty line in the script editor.

Then copy and paste the following set of codes:

//this is the options JSON implementation

function getYPrice2(symbol) {
  symbol = symbol || "VWRA.L";
  symbol = encodeURI(symbol);
  Utilities.sleep(Math.floor(Math.random() * 5000))
  var url = 'https://query2.finance.yahoo.com/v7/finance/options/'+ symbol; // last one day history
  Logger.log(url);  
  var response = UrlFetchApp.fetch(url, {muteHttpExceptions: true});
  var responseCode = response.getResponseCode();
  if (responseCode === 200) {
    var chain = JSON.parse(response.getContentText());
    Logger.log(parseFloat(chain.optionChain.result[0].quote.regularMarketPrice));
    return parseFloat(chain.optionChain.result[0].quote.regularMarketPrice);
  }else{
     return -1; 
  }
}


//this is the stock JSON implementation
// guide 1: https://stackoverflow.com/questions/44030983/yahoo-finance-url-not-working
// guide 2:

function getYPrice3(symbol) {
  symbol = symbol || "VWRA.L";
  symbol = encodeURI(symbol);
  Utilities.sleep(Math.floor(Math.random() * 5000))
  var url = 'https://query2.finance.yahoo.com/v10/finance/quoteSummary/'+symbol+'?modules=price'; // last one day history
  Logger.log(url);  
  var response = UrlFetchApp.fetch(url, {muteHttpExceptions: true});
  var responseCode = response.getResponseCode();
  if (responseCode === 200) {
    var quote = JSON.parse(response.getContentText());
    Logger.log(parseFloat(quote.quoteSummary.result[0].price.regularMarketPrice.raw));
    return parseFloat(quote.quoteSummary.result[0].price.regularMarketPrice.raw);
  }else{
     return -1; 
  }
}

A successfully copy and paste looks like this:

Step 3: Save the Changes in Script Editor

Click on the floppy disk icon, next to the clock-like Icon to save the implementation (I realize not a lot of people know what are floppy disks!)

Step 4: Test the Changes

We can do a test of the changes by running getYPrice2 or 3. This is because if there are no inputs into symbol, by default, getYPrice2 or 3 will try to retrieve VWRA.L which is Vanguard All-World ETF.

In the Select Function dropdown, select either getYPrice2 or getYPrice3.

Then press the Play button.

If this is the first time you are doing this in Script Editor, Script Editor might prompt you for permission, like the screen shot below:

Click on Review Permissions.

Click on the account you wish to use.

Click Advanced

It will expand with one more link which you can then click on Go to YahooFinanceData (unsafe)

Click on Allow. We are done with Permissions

Now go to View > Logs to see whether getYPrice2 or 3 is working properly.

It might not be immediately. If you do not see anything, click Logs again. A successful test would be free of error and show the price successfully retrieved.

Step 5: Implement getYPrice2 or getYPrice3 to get the latest price

Now go to your Stock Summary sheets, and add the following formula:

=iferror(IF(isblank(E2),"",getYPrice3(E2)),"--")

To the cell which you wish to retrieve the latest prices:

In this implementation I added this formula to cell F2.

getYPrice3 will try to retrieve the stock quote in cell E2.

If your cell is not E2, change accordingly.

Once you assessed that it works, you can pull and propagate this formula to the rest of the stocks and the rest of the sheets.

Done!

How Robust is This Solution?

I am not sure how long this solution will last. I have updated this article like 2 times since I had first wrote this.

Whenever we implement free solutions like this, there are always risks that the provider would just change things and we will need the solution again.

But this solution is interesting in that, this JSON object definition have been around since 2017. You can refer to Ryder Brooks’ answer in this StackOverflow question and answer for more information.

If you understand these stuff, you can pull more information than just latest prices.

I kinda like this solution. It turn out that JSON is really fast.

If you face any problems, do let me know. I will try to help.

As always if you like the FREE Stock Portfolio Tracker, you can support me by donating an amount equivalent to how much you value my work.

I invested in a diversified portfolio of exchange-traded funds (ETF) and stocks listed in the US, Hong Kong and London.

My preferred broker to trade and custodize my investments is Interactive Brokers. Interactive Brokers allow you to trade in the US, UK, Europe, Singapore, Hong Kong and many other markets. Options as well. There are no minimum monthly charges, very low forex fees for currency exchange, very low commissions for various markets.

To find out more visit Interactive Brokers today.

Join the Investment Moats Telegram channel here. I will share the materials, research, investment data, deals that I come across that enable me to run Investment Moats.

Do Like Me on Facebook. I share some tidbits that are not on the blog post there often. You can also choose to subscribe to my content via the email below.

I break down my resources according to these topics:

  1. Building Your Wealth Foundation – If you know and apply these simple financial concepts, your long term wealth should be pretty well managed. Find out what they are
  2. Active Investing – For active stock investors. My deeper thoughts from my stock investing experience
  3. Learning about REITs – My Free “Course” on REIT Investing for Beginners and Seasoned Investors
  4. Dividend Stock Tracker – Track all the common 4-10% yielding dividend stocks in SG
  5. Free Stock Portfolio Tracking Google Sheets that many love
  6. Retirement Planning, Financial Independence and Spending down money – My deep dive into how much you need to achieve these, and the different ways you can be financially free
  7. Providend – Where I used to work doing research. Fee-Only Advisory. No Commissions. Financial Independence Advisers and Retirement Specialists. No charge for the first meeting to understand how it works
  8. Havend – Where I currently work. We wish to deliver commission-based insurance advice in a better way.
Kyith

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

C

Tuesday 30th of January 2024

Hi Kyith, thank you for your portfolio tracker. After running the script getYPrice3, I'm getting the response code 404. In the spreadsheet (newly copied as of today), the Yahoo price doesn't update (returns -1). What might be the problem?

Thank you in advance!

Kyith

Thursday 1st of February 2024

Hi C, you might want to checck out this post that i just put out: https://investmentmoats.com/money/2-solutions-fix-yahoo-stock-price-break-google-stock-portfolio-tracker/

John

Friday 18th of February 2022

Thank you for helping to update the Yahoo Finance data grab for old spreadsheet. It works fine now!

Kyith

Saturday 19th of February 2022

no problem

Marcos

Wednesday 22nd of September 2021

Hello, Great post man. I have a question though: Where do you put the Yahoo Finance Api Key for the HTTP Request?

Thank you

Kyith

Thursday 23rd of September 2021

You do not need to put the key

David

Monday 5th of July 2021

Great work, how does one implement this for grabbing yahoo finance dividend and yield information?

Kyith

Thursday 8th of July 2021

Hi David, I do not do that but you might want to experiment with the yahoo API definition and try and grab on your own.

Nick

Friday 12th of March 2021

Hi Kyith,

I have been a big fan of your spreadsheet for the longest time, and similarly also faced tons of issues with pulling XML data from Yahoo/Google Finance. I have combed through many forums seeking a solution but none has been truly robust. Your solution here though seems to be working perfectly fine! Fingers crossed! Thanks once again for all your efforts in helping the community, i'm sure many of us here owe you a debt of gratitude. Stay awesome.

Cheers, Nick

Kyith

Saturday 13th of March 2021

Hi Nick, Thank you. getYPrice3() should still work even though the prices do not update as fast.

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