Using Google Sheets with IMPORTXML to scrape a piece of information from a website via XPath.

Here's the site (click here).

The specific piece of information I'm trying to extract is the Price/Sales (TTM) number, i.e., 2.97

Here's the XPath (I've tested it using the Google Chrome Extensions XPath Helper and XPather. It works.):

//div[@id='audit-integrity'][1]/table/tbody/tr[10]/td[2]

Here's my Google Finance function:

=IMPORTXML( "https://eresearch.fidelity.com/eresearch/evaluate/fundamentals/keyStatistics.jhtml?stockspage=keyStatistics&symbols=aapl" , "//div[@id='audit-integrity'][1]/table/tbody/tr[10]/td[2]")

The function doesn't work. Why?

up vote 4 down vote accepted

It's because of <tbody>. There is no <tbody> in the HTML source. Browser puts it there (on its own; nothing to do with JavaScript on the page) because it believes it should be there, according to HTML spec.

Just remove /tbody from the path:

=IMPORTXML( "https://eresearch.fidelity.com/eresearch/evaluate/fundamentals/keyStatistics.jhtml?stockspage=keyStatistics&symbols=aapl" , "//div[@id='audit-integrity'][1]/table/tr[10]/td[2]")

And in general: when XPath doesn't work, debug by removing all of the selectors, then adding one by one until the formula breaks...

  • You, sir, are a national treasure. – thanks_in_advance Aug 14 '15 at 17:19
  • Incredibly helpful thanks! To add a bit more detail for Chrome users: Chrome DevTools ("Inspect" on right-click) appears to add <tbody>, when it's not originally present. "View Source" on right-click does not appear to add it. Another wrinkle is that tbody is also included in the path when you "Copy... XPath" via Inspect in Chrome DevTools. – joelhaus Nov 11 '17 at 20:06

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.