Google sheet web-scrape table within table

user_15892500 0
Viewed 63

On this website, https://finance.yahoo.com/quote/ANZ.AX/financials - I am trying to extract the value for "Basic EPS" from the 2017 column, into cell J12 (google sheets).

I have the ANZ.AX ticker code in cell C12.

In J12 I have the formula:

=EPSGROWTH("https://finance.yahoo.com/quote/"&C12&"/financials")

The GROWTH script is where I'm having a problem, as normally the 'xxxx' has been a unique string in the html to match, but in this case it is 'fin-col' which appears everywhere on the page.

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

Is it because this is a table within a table? (either way I don't know the solution)

Obviously, I could be way off with my approach, but hopefully the solution is simple for someone new to coding :)

Any help is welcomed and very much appreciated. Cheers T

google-apps-script google-sheets
1 Answers

In this case, how about directly retrieving the values from JSON data which is used with Javascript? This is the related thread. Ref When your script is modified, it becomes as follows.

Modified script:

function EPSGROWTH(url) {
  const res = UrlFetchApp.fetch(url).getContentText().match(/"annualBasicEPS":(\[.+?])/);
  if (res && res.length > 1) {
    const obj = JSON.parse(res[1]).filter(e => e && e.asOfDate && e.asOfDate.includes("2017"));
    return obj.length > 0 ? obj[0].reportedValue.fmt : "No value"; // or obj[0].reportedValue.raw
  }
  return "No value";
}
  • In this case, it seems that when obj[0].reportedValue.raw is used instead of obj[0].reportedValue.fmt, the raw value can be retrieved.

Result:

When above modified script is used, it becomes as follows.

enter image description here

References:

user_71086530
Related