Google Sheet: IMPORTXML from Yahoo Finance fails for some stocks

Question

I am trying to import some quotes from Yahoo Finance to Google Sheets via IMPORTXML that Google Finance does not offer. For example to import the current rate from IBM or Apple I use =IFNA(VALUE(IMPORTXML("https://finance.yahoo.com/quote/IBM","//*[@class=""D(ib) Mend(20px)""]/span[1]")))

or

=IFNA(VALUE(IMPORTXML("https://finance.yahoo.com/quote/AAPL","//*[@class=""D(ib) Mend(20px)""]/span[1]")))

This works so far. However, I fail to import the price of this company. https://finance.yahoo.com/quote/0P00016MCQ.F

I just don't know why and what to do. Can someone help me? I would be very happy about it!

Answer 1

I can't answer to the reason why you can't fetch the data for 'global online retail' and you can do it for 'IBM'. Nevertheless, the right way to do that is to have a look at the source code of the page and especially the json called 'root.App.main' which contains all the informations you need.

function marketPrice() {
  var code = 'C2PU.SI'
  var info = 'regularMarketPrice'

  var url='https://finance.yahoo.com/quote/' + code
  var source = UrlFetchApp.fetch(url).getContentText()
  var jsonString = source.match(/(?<=root.App.main = ).*(?=}}}})/g) + '}}}}'
  var data = JSON.parse(jsonString)
  var price = data.context.dispatcher.stores.StreamDataStore.quoteData.item(code).item(info).raw
  Logger.log(price)
}
Object.prototype.item=function(i){return this[i]};