Yahoo finance industry import to Google sheets for international stocks

Question

I am trying to import data (industry) from yahoo finance to my google sheet tracker. The formula below is able to extract the industry for every single stock that is listed on the US stock exchanges. However, when a stock is listed in Germany for example, and a suffix ".DE" needs to be added the formula stops working. I believe that the "." causes confusion to the URL somehow but cannot figure a way to fix it.

IMPORTXML("https://finance.yahoo.com/quote/"&$A2&"/";"//*[@id='Col2-12-QuoteModule-Proxy']/div/div/div/div/p[2]/span[2]")

Example

Mike Steelson Mike Steelson · Accepted Answer · 2021-06-29T14:16:41

Now, with Yahoo Finance, the web page is built on the user side by javascript, not the server side. It is then completely impossible to retrieve information by importhtml or importxml. You need to parse the json called root.App.main.

  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/1EKu4MbuwZ6OTWKvyIJrMfnXf7gXfU8TWU3jwV4XEztU/copy . If you need specific information, it is possible to adapt a small script to your needs.

Yahoo finance industry import to Google sheets for international stocks

1 Answers