Search code examples
google-sheets-formula

Google sheets IMPORTXML fails for ASX data

I am trying to extract the "Forward Dividend & Yield" value from https://finance.yahoo.com/ for multiple companies in different markets, into Google Sheets.

This is successful:

=IMPORTXML("https://finance.yahoo.com/quote/WBS", "//*[@id='quote-summary']/div[2]/table/tbody/tr[6]/td[2]")

But this fails with #N/A:

=IMPORTXML("https://finance.yahoo.com/quote/CBA.AX", "//*[@id='quote-summary']/div[2]/table/tbody/tr[6]/td[2]")

I cannot work out what needs to be different for ASX ticker codes, why does CBA.AX cause a problem?

Solution

  • When I tested the formula of =IMPORTXML("https://finance.yahoo.com/quote/CBA.AX", "//*"), an error of Error Resource at url not found. occurred. I thought that this might be the reason of your issue.

    But, fortunately, when I try to retrieve the HTML from the same URL using Google Apps Script, the HTML could be retrieved. So, in this answer, I would like to propose to retrieve the value using the custom function created by Google Apps Script. The sample script is as follows.

    Sample script:

    Please copy and paste the following script to the script editor of Google Spreadsheet and save it. And, please put a formula of =SAMPLE("https://finance.yahoo.com/quote/CBA.AX") to a cell. By this, the value is retrieved.

    function SAMPLE(url) {
  const res = UrlFetchApp.fetch(url).getContentText().match(/DIVIDEND_AND_YIELD-value.+?>(.+?)</);
  return res && res.length > 1 ? res[1] : "No value";
}

    Result:

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

    enter image description here

    Note:

    • When this script is used, you can also use =SAMPLE("https://finance.yahoo.com/quote/WBS").
    • In this case, when the HTML structure of the URL is changed, this script might not be able to be used. I think that this situation is the same with IMPORTXML and the xpath. So please be careful this.

    References: