Google Sheet Web-scraping ImportXml Xpath on Yahoo Finance doesn't works with french stock

Question

I'm using GoogleSheets and Yahoo Finance to get dividend stocks values.

=IMPORTXML(CONCATENER("https://finance.yahoo.com/quote/"; index(C2));"/html/body/div[1]/div/div/div[1]/div/div[3]/div[1]/div/div[1]/div/div/div/div[2]/div[2]/table/tbody/tr[6]/td[2]")

I use this formula to get the result, in the C2 cell I have the symbol ex: AAPL.

And it works fine !

But when I want to get some french stocks the pattern symbol is 'XX.PA' ex: AI.PA.

I don't know why with french stocks I have a bad issue

If someone have any ideas ?

Myotonia

The issue is because of the "." in the ticker and is presumably a bug. It has been widely commented upon as it renders IMPORTXML and IMPORTHTML useless in this cases. The suggested workarounds are to use App Scripts. Side note: you can make your xpath slightly more robust, in working cases, with //*[@id='quote-summary']//tr[6])[2]/td[2]. Not spent a lot of time looking at more robust paths but that is certainly better than a long fragile path. – Swordfish

Does this answer your question? Yahoo finance historical close price to google sheets returns n/a for close later than 100 days – Babism

Yes, thanks so I use an other web site now – Myotonia

the xpath depends on how the site is published, how the site is structured, so you can't use an xpath from one web site to another, if you want to do so, ask a specific question on boursorama – Pious

Answer 1

In yahoo finance, since the page is built by the mean of javascript, you can't use native import functions.

However, there is a big json that you can parse

  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)

you can fetch for instance the regularMarketPrice

  var regularMarketPrice = data.context.dispatcher.stores.StreamDataStore.quoteData[code].regularMarketPrice.raw

or dividend rate

var dividendRate = data.context.dispatcher.stores.QuoteSummaryStore.summaryDetail.dividendRate.raw

application

in your sheet =dividend("SAF.PA") or =marketPrice("SAF.PA") with these custom functions

function marketPrice(code) {
  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 regularMarketPrice = data.context.dispatcher.stores.StreamDataStore.quoteData[code].regularMarketPrice.raw
  return regularMarketPrice
}

function dividend(code) {
  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 dividendRate = data.context.dispatcher.stores.QuoteSummaryStore.summaryDetail.dividendRate.raw
  return dividendRate
}

Pious

Hi, thanks for your response but how can I add this code to my googleSheet ? And why it works with american stock and not french stock there is the same quantity of data ? – Myotonia

Thanks for your answer but I used an other stock site. I'm pretty sure that the dot in the stock symbol makes it not work. – Myotonia

Can you give this stock symbol? For marketprice, I put code in brackets to prevent that situation. – Pious

I use boursorama https://www.boursorama.com/cours/1zAXA/ so the stock symbol go from 'EPA.CS' to '1zAXA' – Myotonia

if you use boursorama, you can try =index(importxml("https://www.boursorama.com/cours/1zAXA/","//span[@class='c-instrument c-instrument--last']"),1) – Pious

EPA.CS is not supported by yahoo finance – Pious

As of January 4th 2023 is no longer working – Absalom