google-sheetsweb-scrapingxpathgoogle-sheets-formulayahoo-finance

Get information from Yahoo Finance

I want to get the Market Cap value from this site using importxml: https://finance.yahoo.com/quote/KIT.OL?p=KIT.OL&.tsrc=fin-srch

Where it says 3.217B.

I am using this to get the "previous close" value:

=ImportXML("https://sg.finance.yahoo.com/quote/"&B3&"/history?p="&B3; "//tbody/tr[1]/td[6]")

I was hoping I could just adjust the above formula to get te market cap value. Anyone who can help?

Thanks!

Solution

  • try:

    =INDEX(IMPORTXML(A1, "//tr"), 9, 2)

    enter image description here

    or:

    =INDEX(QUERY(TO_TEXT(IMPORTXML(A1, "//tr")), 
 "select Col2 where Col1 = 'Market Cap'", 0))

    enter image description here

    however!

    this way you can get only the old value. to get the new one you will need to use a script:

    function YAHOO(url) {
  const res = UrlFetchApp.fetch(url, {muteHttpExceptions: true});
  const tables = [...res.getContentText().matchAll(/(<table[\w\s\S]+?<\/table>)/g)];
  if (tables.length < 2) return "No tables. Please confirm URL again.";
  const values = tables.reduce((ar, [,table]) => {
    if (table) {
      const root = XmlService.parse(table).getRootElement();
      const temp = root.getChild("tbody", root.getNamespace()).getChildren().map(e => e.getChildren().map(f => isNaN(f.getValue()) ? f.getValue() : Number(f.getValue())));
      ar = ar.concat(temp);
    }
    return ar;
  }, []);
  return values[0].map((_, i) => values.map(r => r[i]));
}

    and formula:

    =INDEX(YAHOO(A1), 2, 9)

    enter image description here

    extra reading: https://stackoverflow.com/a/65914858/5632629