Solution to Yahoo Finance Data Not Refreshing in Google Spreadsheet | Investment Moats 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.

Do Like Me on Facebook. I share some tidbits that is not on the blog post there often. You can also choose to subscribe to my content via 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 currently 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

Couples with Opposite Money Insecurities Attract One Another.
← Previous
Survival, Freedom and Power: The 3 Phases of Life & Money
Next →

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

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.

Sietse

Tuesday 9th of March 2021

I managed to get the longName (referred to my older comment) but is there also a way to get the sector and industry form the profile page?

Sietse

Tuesday 9th of March 2021

Hi, is it also possible to get the ticker name. So APPL is Apple Inc.? I see the atribute in the query2 (longName) but I don't know how to extract it.

KP

Monday 22nd of February 2021

Hi Kyith,

What triggers the Yahoo price updates? It seems from the description that it should be refreshReadMeFirstTime() function, which is triggered by a timer every 5 minutes. But it looks to me that it just updates the date in B5 in "Read This First" sheet and nothing else. My problem that when entering a new symbols I can get the correct Yahoo price using all 3 getYPrice methods and all 3 work when manually running in the script editor. But there are no periodic updates doesn't matter what I do. I see that refreshReadMeFirstTime() function runs fine every 5 minutes, but still no price updates. cannot figure it out.

Thanks KP

KP

Tuesday 23rd of February 2021

function refreshReadMeFirstTime() { var d = new Date(); SpreadsheetApp.getActiveSpreadsheet().getSheetByName("First").getRange('B5').setValue(Utilities.formatDate(d, "GMT-5", 'MMMM dd, yyyy hh:mm:ss Z')); }

Could you plz explain how this function triggers getYPrice3() to run?

KP

Tuesday 23rd of February 2021

@Kyith, Currently it's getYPrice3() function, but I have tried all 3 variants. They all work when triggered manually, but no periodic updates. I am trying to understand what triggers those periodic updates.

Kyith

Monday 22nd of February 2021

Hi KP, the price updates are shown on the stock summary sheets under the Yahoo or Auto prices. Can you tell me which formula do you see? is it a getYPrice() function or something else. Apologies as there are a few versions floating around.

JayTee

Thursday 21st of January 2021

Hi Kyith, thank you for sharing your portfolio tracker with everyone, it has really helped since I started investing last year. I recently experienced an issue with Yprice2 & Yprice3 not automatically updating the prices, causing the portfolio history to be "flat" for a couple of days until I realized the issue. However if i make some changes to the cell, e.g. change from Yprice2 to Yprice3, it manages to pull the most recent price from Yahoo. Any ideas what might've caused it? Thanks in advance!

JayTee

Sunday 24th of January 2021

@Kyith, Thanks so much for taking the time to reply. Seems to work on and off at times, will continue to monitor. Thanks!

Kyith

Friday 22nd of January 2021

HI JayTee, I have no idea why that happen. For some people it happens as well. I am using the YPrice3 function daily, and so I am trying to detect if the function has a problem. If there are errors i would let people know.

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