Google Sheets – ImportXML with XPath Not Returning Table Entry

google sheetsimportxmlxpath

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

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?

Best Answer

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...

Related Solutions

Google-sheets – In Google Spreadsheets the xPath method with Google Maps don’t work anymore

Getting data by parsing the HTML source of a page is a fragile method: when the pages gets redesign, the import is most likely going to break down because the data you want are not where you expect. It's no coincidence that most of examples on Google's own IMPORTXML documentation page are broken by now.

I recommend using Google Distance Matrix API instead (it is a subset of Google Maps API). It also accepts HTTP requests and can return data as XML; but this XML will have predictable, documented structure for the lifetime of the API version.

Examples of parsing Google's XML response with XPath are found here.

Google-sheets – Retrieve last entry via ImportXML

You might try:

=index(importhtml("http://bigcharts.marketwatch.com/markets/indexes.asp","Table"),5,4)

for the NASDAQ 100 Index and adjust the 5 to suit for others.

Best Answer

Related Solutions

Google-sheets – In Google Spreadsheets the xPath method with Google Maps don’t work anymore

Google-sheets – Retrieve last entry via ImportXML

Related Topic