0

I'm attempting to parse the 'PEG Ratio' value of a stock from Yahoo Finance into a Google Sheet, but seeing an error.

URL used: https://finance.yahoo.com/quote/ABBV/key-statistics?p=ABBV

Cell Expression used: =IMPORTXML("http://finance.yahoo.com/quote/ABBV/key-statistics?p=ABBV", "//td[@data-reactid='132']")

Error: '#N/A' value (Error: Imported Content is empty)

Value expected is 1.28 (at the time of posting this query) - from Yahoo Finance > Statistics tab > PEG Ratio table (td has a, attribute data-reactid='132' that I have attempted to filter in the query)

Can anyone help please? Here is a link to the sheet: Google Sheet

JetStream
  • 518
  • 2
  • 6
  • 17

2 Answers2

0

Issue

IMPORTXML can only read the HTML source of a website. Therefore, those elements and components of a website added dynamically will not be able to be retrieved by the IMPORTXML and thus IMPORTXML will interpret the tag to be with empty content.

Possible workaround

Sometimes, in the JavaScript files of the website, you can find out the URL of the source of data being inserted dynamically but that is a tedious task to achieve.

Other option to get the desired value is to use other web scraping techniques.

halfer
  • 19,824
  • 17
  • 99
  • 186
Mateo Randwolf
  • 2,823
  • 1
  • 6
  • 17
  • Thanks for the simple explanation that someone with limited Javascript knowledge can understand. I was hoping there would be a Google Sheets function I could use to scrape this information after seeing that I could load the page with Javascript turned off as suggested [in this SO answer](https://webapps.stackexchange.com/a/115665). – JetStream Jul 20 '20 at 23:21
  • Hi ! Yes so after an hour testing with ```IMPORTXML``` and ```IMPORTHTML``` I could actually pull all the data but the one from the top table. Interestingly, I could pull the *Valuation Measures* title and the ones next to it. Also, from the SO answer you mentioned it states that *The Chrome's Inspect tool shows the parsed DOM; in other words the actual structure/content of the web page which could be dynamically modified by JavaScript code or browser extensions/plugins.*. I strongly believe this is the case here. Would you be open to a solution using a sheet Apps Script script? – Mateo Randwolf Jul 22 '20 at 07:54
  • Thanks for your efforts. I have the liberty of using Google Sheets built-in functions. I haven't used Apps scripts but I'll look it up. As long as the solution doesn't involve installing any third party stuff, it should be okay. With my limited Javascript knowledge I thought if Javascript was turned off, it was static content that was being parsed. – JetStream Jul 22 '20 at 15:05
  • Hi ! So after trying with Apps Script too using [UrlFetchApp.fetch()](https://developers.google.com/apps-script/reference/url-fetch/url-fetch-app) and not be able to retrieve that specific part of the website, it has been clear to me that this part was inserted with a script and therefore it cannot be retrieved in a *vanilla* way. Hope this information helps you :) – Mateo Randwolf Jul 23 '20 at 08:10
  • 1
    Uh ohh :sad: I take it, it is not possible to scrape those values into a Google Sheet through a simple mechanism then because of the way Javascript inserts those values on the rendered page. Appreciate your efforts and time. I'll give it a few days so others have a chance to read and comment. I'll come back in a few days and mark your answer as accepted if there is are no comments from others. – JetStream Jul 23 '20 at 11:56
  • Yes, I was also disappointed that IMPORTXML couldn't pull from that part of the page, or that there wasn't some other native way in Sheets to pull it, at least not that I could find... – kirkg13 Jul 24 '20 at 20:50
-1

This is probably not what you want, but I was searching around, and found a Google Sheets Add-On that does manage to pull the "1.28" value from that page. It is free for doing a very limited number of queries per month. If interested, search for IMPORTFROMWEB in the GSuite Marketplace.

I only plugged in your URL and the same XPath that you used, so I was very surprised when the data showed up. No idea how it works.

I apologise if mentioning an Add-On is not appropriate on SO. But knowing that an add-on can get that data off the web page may encourage some other ideas on how to do it natively with Sheets.

ImportFromWeb Add-On

kirkg13
  • 2,955
  • 1
  • 8
  • 12
  • Thanks for sparing the time to look into this. It was encouraging indeed. At least the Add-On confirmed that the XPath was correct. For a newbie in this area, that is a tremendous morale booster!! I would like to steer clear from paid / proprietary / throttled solutions though. – JetStream Jul 22 '20 at 15:13