1

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

enter image description here

If someone have any ideas ?

Rubén
  • 34,714
  • 9
  • 70
  • 166
xXx_Ninj4_Kill3r_xXx
  • 180
  • 10
  • 1
    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. – QHarr Jan 22 '22 at 17:31
  • Does this answer your question? [Yahoo finance historical close price to google sheets returns n/a for close later than 100 days](https://stackoverflow.com/questions/61636468/yahoo-finance-historical-close-price-to-google-sheets-returns-n-a-for-close-late) – Rafa Guillermo Jan 24 '22 at 08:11
  • Yes, thanks so I use an other web site now – xXx_Ninj4_Kill3r_xXx Jan 24 '22 at 09:11
  • 1
    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 – Mike Steelson Jan 24 '22 at 11:05

1 Answers1

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
}
Mike Steelson
  • 14,650
  • 2
  • 5
  • 20