Google sheets IMPORTXML fails for ASX data

user_15892500 0
Viewed 173

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?

Huge thanks for any help

google-sheets-formula
2 Answers

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:

user_71086530

An other solution is to decode the json contained in the source of the web page. Of course you can't use importxml since the web page is built on your side by javascript and not on server's side. You can access data by this way and get a lot of informations

  var source = UrlFetchApp.fetch(url).getContentText()
  var jsonString = source.match(/(?<=root.App.main = ).*(?=}}}})/g) + '}}}}'

i.e. for what you are looking for you can use

function trailingAnnualDividendRate(){
  var url='https://finance.yahoo.com/quote/CBA.AX'
  var source = UrlFetchApp.fetch(url).getContentText()
  var jsonString = source.match(/(?<=root.App.main = ).*(?=}}}})/g) + '}}}}'
  var data = JSON.parse(jsonString)
  var dividendRate = data.context.dispatcher.stores.QuoteSummaryStore.summaryDetail.trailingAnnualDividendRate.raw
  Logger.log(dividendRate)
}
user_150193800
Related