Not able to get data from yahoo finance using IMPORTXML in Google Sheets

Question

I'm trying to get the Price/Sales (time-to-market) data for this stock. I used the IMPORTXML function in Google Sheets along with the XPath of the value as shown below,

=IMPORTXML(CONCATENATE("https://finance.yahoo.com/quote/",C4,".NS/key-statistics?p=",C4,".NS"),"/html/body/div[1]/div/div/div[1]/div/div[3]/div[1]/div/div[1]/section/div[2]/div[1]/div/div/div/div/table/tbody/tr[6]/td[2]")

C4 in my sheet is the stock code ABB. However, it says, Resource not found at URL. What am I missing?

Answer 1

From Resource not found at URL, I'm worried that in this case, the URL cannot be requested from IMPORTXML. And, at finance.yahoo.com, the values are included in the HTML body. But, unfortunately, in the current stage, it seems that the values are encrypted. And, it seems that it is difficult to directly retrieve the key for decrypting. But, in your situation, it seems that your expected value is included in the HTML body without encrypting by retrieving it with Google Apps Script.

Sample script

Please copy and paste the following script to the script editor of Google Spreadsheet and save the script.

When you use this script, please put a custom function of =SAMPLE(CONCATENATE("https://finance.yahoo.com/quote/",C4,".NS/key-statistics?p=",C4,".NS")) into a cell. By this, the script is run.

function SAMPLE(url) {
  const search = "Price/Sales"; // This is from your question.
  const r1 = [...UrlFetchApp.fetch(url).getContentText().matchAll(/<td.+?<\/td>/g)];
  const idx = r1.findIndex(([e]) => e.includes(search));
  if (idx == -1) return null;
  const r2 = r1[idx + 1][0].match(/>(.+)<\/td>/);
  return r2 ? Number(r2[1]) : null;
}

Testing

When this script is used, the following result is obtained.

Note

When I posted this answer, I confirmed that the script works and the expected value can be obtained. But, on the server side, the HTML is sometimes changed. So, when the HTML is changed on the server side, this script might not be able to be used. Please think of this as the current sample script. Please be careful about this.

References:

• Custom Functions in Google Sheets
• fetch(url, params)