Import error: "Ressource at URL not found" only for some specific stocks

2
votes

I'm creating a dividend tracker sheet in google sheets. I'm using following formula as I want to use some dividend data from yahoo finance

=SPLIT(INDEX(IMPORTHTML(concatenate("https://finance.yahoo.com/quote/";B8);"table";2);6;2);" ")

B8 is referring to the ticker from yahoo finance

It works well for all US stocks(Example: "T" and "JNJ") and for 1 EU stocks ("VOW3.DE"). For all other EU stocks (Ex: "SIE.DE") it doesn't work. I'm using though the same formula =SPLIT(INDEX(IMPORTHTML(concatenate("https://finance.yahoo.com/quote/";B5);"table";2);6;2);" ")

B5 refers to SIE.DE

I'm getting following error message:

"#N/A": Error Ressource at url not found

Strange as it works well for VOW3.DE ticker (Volkswagen)

1
htmlgoogle-sheetsgoogle-sheets-formulayahoo-finance

1 Answers

0
votes

This site is now built on client side by javascript, not on server side. Then you can't retrieve informations by importxml, neither importhtml. Hopefully, the page contains a big json called root.App.main that you can parse and retrieve data :

  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)

for instance https://docs.google.com/spreadsheets/d/1sTA71PhpxI_QdGKXVAtb0Rc3cmvPLgzvXKXXTmiec7k/copy