1

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

Rubén
  • 34,714
  • 9
  • 70
  • 166
Kristian Tonev
  • 15
  • 4

1 Answers1

0

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.

Mike Steelson
  • 14,650
  • 2
  • 5
  • 20
  • Hi Mike, thanks for your help, i did find you script a few hours ago and have been trying to play with it a bit. It works amazingly! Thank you! I am an almost complete noob when it comes to coding but did manage to make it work to get the top 10 holdings of SPY. My struggle is to get the sector/industry weighting to be listed and transposed per ETF/fund. – Kristian Tonev Jun 29 '21 at 17:55