2

I want a formula to fetch the Float of eg BEKB.BR https://finance.yahoo.com/quote/BEKB.BR/key-statistics?p=BEKB.BR it's 36.45M Formula's like

=index(IMPORTHTML("http://finance.yahoo.com/q/ks?s= BEKB.BR+Key+Statistics","table", 2), 4, 2)

or the ticker in cell A27:

=index(IMPORTHTML("http://finance.yahoo.com/q/ks?s="& $A$27&"+Key+Statistics","table", 2), 4, 2)

don't give a result. Thanks for your help.

Rubén
  • 34,714
  • 9
  • 70
  • 166
Emiel
  • 21
  • 2
  • Data populated by JS in a page can't be extracted using `IMPORTHTML`/`IMPORTXML`. See [here](https://stackoverflow.com/questions/61636468/yahoo-finance-historical-close-price-to-google-sheets-returns-n-a-for-close-late/61637397#61637397) – Rafa Guillermo Nov 08 '21 at 08:42
  • Thanks Muhammet! It works like a charm. It's a brand new approach for me. I wonder if it is possible to alter this script by saying "fetch the ticker from column A and put in corresponding cell D it's Float? – Emiel Nov 08 '21 at 19:42

1 Answers1

3

The IMPORTHTML() and IMPORTXML() functions don't fetch this page for some reason I don't know.

You can use URLFetchApp in Google Apps Script instead.

const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

function fetch() {
  //Replace the "A2" with A1 notation of the cell which you'd like to type the keys
  const key = sheet.getRange("A2").getValue();
  url = `https://finance.yahoo.com/quote/${key}/key-statistics`;
  const response = UrlFetchApp.fetch(url).getContentText();

  //We will use Cheerio for HTML parsing
  const $ = Cheerio.load(response);
  const value = $('fin-streamer[data-field="regularMarketPrice"][data-test="qsp-price"]').text();

  //Replace the "B2" with A1 notation of your target cell
  sheet.getRange('B2').setValue(value || "Not found!");
}

And my apsscript.json manifest file is as follows:

{
  "timeZone": "Europe/Istanbul", //or whatever
  "dependencies": {
    "libraries": [
      {
        "userSymbol": "Cheerio",
        "version": "14",
        "libraryId": "1ReeQ6WO8kKNxoaA_O0XEQ589cIrRvEBA9qcWpNqdOP17i47u6N9M5Xh0"
      }
    ]
  },
  "oauthScopes": [
    "https://www.googleapis.com/auth/script.external_request",
    "https://www.googleapis.com/auth/spreadsheets.currentonly"
  ],
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8"
}

Here is a working example. When you click on the button it writes the Float value to A2 cell.

Muhammet Yunus
  • 541
  • 4
  • 14
  • 3
    The reason is that data populated by JavaScript in a web page can't be extracted using `IMPORTHTML`/`IMPORTXML`. See [here](https://stackoverflow.com/questions/61470783/google-sheets-importxml-returns-empty-value/61475850#61475850) and [here](here) for example/explanation – Rafa Guillermo Nov 08 '21 at 08:44
  • @Ihopethisishelpfultoyou Hi again. I've authorized the function above as owner, but if another user tries to run it in my sheet, it requests his/her authorization as well. I wonder why? – Muhammet Yunus Nov 08 '21 at 10:08
  • 1
    each person needs to authorise the script to run on their behalf. After you authorise, you’ve only authorised it to run as you, on your account. Not as another user – Rafa Guillermo Nov 08 '21 at 10:27
  • read: https://developers.google.com/apps-script/guides/services/authorization – Rafa Guillermo Nov 08 '21 at 10:28
  • Thanks, but for example if you open [this](https://docs.google.com/spreadsheets/d/14EQP1rQS38h0zw_2VEOldZpK9oYild_hDBVok05TUT0/edit?pli=1#gid=0) sheet and write in A1 cell anything, it triggers the onEdit function and rename the second sheet with A1's value without any authorization. Can you try please? – Muhammet Yunus Nov 08 '21 at 10:41
  • 1
    [Simple onEdit triggers don’t need authorisation](https://developers.google.com/apps-script/guides/triggers) – Rafa Guillermo Nov 08 '21 at 10:50
  • 1
    As of January 4th this isn't working anymore. See https://meta.stackoverflow.com/q/422494/1595451 – Rubén Jan 05 '23 at 01:00
  • @Rubén I've just updated my answer and the source code. It works for me now. Could you take a look please? – Muhammet Yunus Jan 06 '23 at 20:43
  • Thank you very much for your updating your answer. Please consider to extend a bit the explanation of your script, i.e. the part of the xPath. P.S. Are you aware that Yahoo! Finance have been changing the DOM structure and have introduced measures like encryting certain data? Considering this would you suggest something to future readers in case that the script stop working in the future? – Rubén Jan 06 '23 at 22:20