2

This is the website:

https://eresearch.fidelity.com/eresearch/goto/evaluate/snapshot.jhtml?symbols=aapl&type=o-NavBar

I'm trying to pull out the following two pieces of data into a Google Sheet:

  • Sector (GICS®)
  • Industry (GICS®)

That is, I want the following two to show up for the above:

  • Information Technology
  • Technology Hardware, Storage & Peripherals

I've tried the usual techniques including:

  • importData
  • importHTML
  • importXML (this gave an error).

The XPath's I've derived via Google's Inspect Element tool are:

  • //*[@id="companyProfile"]/div[8]/span/a
  • //*[@id="companyProfile"]/div[13]/span/a

Nothing has worked so far. How can I extract this data into a Google Sheet?

1 Answer 1

2

The command IMPORTXML ignores the nodes with no text content: for example, taking //div[3] from the document where the body consists of

<div>First</div> <div>Second</div> <div></div> <div>Fourth</div>

results in "Fourth". So, when you count the <div> elements, skip over those where there is no text. The elements you are looking for are returned with

=IMPORTXML( url , "//div[@id='companyProfile']/div[4]/span")

and

=IMPORTXML( url , "//div[@id='companyProfile']/div[5]/span")

For a more robust solution, I advise not relying on the numbering of elements at all. The following command returns both values you want with a single call, one under another:

=IMPORTXML( url , "//div[@id='companyProfile']/div[@class='sub-heading']/span")

You can apply TRANSPOSE to the result to put them side by side, if preferable. Or, if some custom positioning is needed, get one at a time with

=IMPORTXML( url , "//div[@id='companyProfile']/div[@class='sub-heading'][1]/span")

and

=IMPORTXML( url , "//div[@id='companyProfile']/div[@class='sub-heading'][2]/span")

Note that one should use single quotes in an XPath command, since it's a string surrounded by double quotes.


Using Inspect Element tool is not an inherently bad idea (it shows a nice tree view of the document), but there is an important caveat: this tool shows the document after any JavaScript runs on the page, while IMPORTXML gets the source as it is before any JavaScript processing. This matters when some elements get added by a script (example in my answer here). To see exactly what IMPORTXML works with, use right-click -> "View Page Source" in Chrome, or its equivalent in other browsers.

6
  • Appreciate the answer. So, using the Chrome "Inspect Element" tool is apparently not a good idea. Can you recommend a good tutorial for XPath? I find it really confusing (as I do Regex). Jul 20, 2015 at 1:53
  • 1
    I commented on Inspect Element. The best tutorial on XPath is apparently still at W3schools, unfortunately. This guide to ImportXML looks good. For regex the resources are far more plentiful, e.g., regular-expressions.info
    – user79865
    Jul 20, 2015 at 2:34
  • A follow-up question: is there a tool such as "Inspect Element" which lets me right click on some element in the page source to select an XPath to the content (or otherwise see the tree structure of the page source)? Thanks. Aug 11, 2015 at 21:27
  • 1
    You should be able to use the regular Inspect Element -> Copy XPath if you disable JavaScript (in Chrome's Content Settings) prior to loading the page. Indeed, ImportHTML is like a browser with JavaScript disabled.
    – user79865
    Aug 11, 2015 at 21:40
  • I'm back with another question. I really want to find "the" shortcut technique to find XPath info. When you evaluated this page, what was the process you used to determine that the correct XPath was (for instance) not //*[@id="companyProfile"]/div[8]/span but //div[@id='companyProfile']/div[4]/span ... are there some tools you used to help with this? Aug 12, 2015 at 22:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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