0

I'm trying to fetch a piece of data using importxml() but it just fails with "Loading..." and never resolves. Other pages have no such problem at all, just this one and I don't know why.

Here is the formula that doesn't work:

=ImportXML("http://eoddata.com/stockquote/OPRA/WFM160826P00032000.htm", "//*[@id='ctl00_cph1_qp1_div1']/div[2]/table/tbody/tr[2]/td[5]/font/b")

Ultimately I'm trying to fetch the "bid", "ask", and "open interest" elements, but all fail. By way of comparison, this works fine:

=ImportXML("http://finance.yahoo.com/quote/WFM160826P00032000", "//*[@id='quote-summary']/div[1]/table/tbody/tr[4]/td[2]")

any ideas what's wrong?

4
  • I just tried the first formula. It returned #N/A > Error > Imported content is empty. using "//*" as the xPath returns all the xml text in the web page.
    – Rubén
    Aug 16, 2016 at 1:52
  • Right, so what is wrong with the formula? Obviously I don't want to fetch the entire web page, just the one part, hence the use of xpath. Is oeddata.com doing something to prevent remote calling of their pages? I can't get any remote import function to work with their site.
    – JVC
    Aug 16, 2016 at 2:09
  • Which specific element do you want? Without my having to break down your xpath segment by segment Aug 16, 2016 at 2:41
  • 1
    Possible duplicate of ImportXML with XPath does not return an entry of a table
    – Rubén
    Jun 21, 2019 at 2:34

2 Answers 2

1

Short answer

The problem is the xPath, specifically the /tbody part as it's not present in the source code.

Explanation

The source html code of the page doesn't include the tbody tag. It's added by the browser when it is missed to create a DOM hierarchy but the algorithm behind IMPORTXML doesn't do the same, it requires the xPath for the original source code of the web page.

Removing it from the xPath will solve the problem.

The following formula returns the value of OPEN INT, 141:

=ImportXML("http://eoddata.com/stockquote/OPRA/WFM160826P00032000.htm",
     "//*[@id='ctl00_cph1_qp1_div1']/div[2]/table[1]/tr[2]/td[5]/font/b"
 )

References

https://stackoverflow.com/questions/938083/why-do-browsers-insert-tbody-element-into-table-elements

5
  • That certainly sounds like it could be the issue, however the example you gave also does not work for me. I just get "Loading..." and nothing further.
    – JVC
    Aug 17, 2016 at 0:20
  • I just copy & paste it to a new spreadsheet. It worked fine to me, but the result is 154.
    – Rubén
    Aug 17, 2016 at 0:22
  • I don't know why it won't work for me but I will keep experimenting. Also regarding tbody, my working yahoo finance example has that too but works fine. Why would that work when eoddata does not? Did yahoo explicitly code it into their markup?
    – JVC
    Aug 17, 2016 at 0:48
  • Well, it still doesn't work no matter what sheet I put it in. No useful error message either, just "Loading..." =( - CORRECTION!! About a minute after putting it on an entirely new document, it finally changed to display the expected value. WTF...?
    – JVC
    Aug 17, 2016 at 1:58
  • Though I am still having problems, they appear to be specific to my sheet and on a new document, your answer does mostly work. My other issue is here in case you are interested: webapps.stackexchange.com/questions/97629/…
    – JVC
    Aug 17, 2016 at 3:42
2

EDIT:

So If your interested in trying google apps script, here is short simple script that grabs the piece you want:

function getOpenInterest(url) {
  var found, html, content = '';
  var response = UrlFetchApp.fetch(url);
  if (response) {
    html = response.getContentText();
    if (html) content = html.match(/OPEN INT:<BR><FONT size=1 color='#333333'><B>(\d|\d+,?\d+?)<\/B>/gi)[0].match(/<B>(\d|\d+,?\d+?)<\/B>/i)[1];
  }
  return content;
}

Basically the pieces following the html.match segment, which are surrounded by a pair of forward slashes / represent the regex. The piece in the parentheses handles both a single digit, and a number of digits, also a digit with a comma should it occur.

enter image description here

Another way to do it, if your not into google apps script, is to use importdata and then regex out the piece you want:

=REGEXREPLACE(FILTER(INDEX(IMPORTDATA("http://eoddata.com/stockquote/OPRA/WFM160826P00032000.htm"),,1),REGEXMATCH(INDEX(IMPORTDATA("http://eoddata.com/stockquote/OPRA/WFM160826P00032000.htm"),,1),"OPEN INT:")),"(^.*OPEN INT:<BR><FONT size=1 color='#333333'><B>)(\d+)(<.*)","$2")

The reason I do it this way is because if you concatenate all of it, it will exceed the character limit for one cell... however this method is unreliable and not guaranteed the piece you want will always be found in that first column

enter image description here

7
  • Interesting approach, thanks! Unfortunately while it works for some symbols, it does not work for all, and I'm not sure why... I'm lousy with Regex. Here is an example URL that fails using your approach: http://eoddata.com/stockquote/OPRA/GDX160916P00030000.htm
    – JVC
    Aug 16, 2016 at 3:35
  • I do still like this approach, but can't figure out why it doesn't work consistently. Can you spare a moment to take a look at my sheet here and see if you can identify the issue in column D? docs.google.com/spreadsheets/d/…
    – JVC
    Aug 18, 2016 at 7:01
  • So the reason it wasn't always working is because using the index function i was restricting it to the first column from importdata - and sometimes it wouldnt return in the first indexed column... I went ahead and added an app script in there for you that handles all cases and is much cleaner/more reliable, ill update my answer Aug 18, 2016 at 14:35
  • Aha! That's excellent. While this answer doesn't technically answer the original question, it IS a very valid workaround so I greatly appreciate you educating me on this. Thank you and I'm sure future readers will thank you too! =)
    – JVC
    Aug 18, 2016 at 16:15
  • 1
    Weird now it's fine again. Guess it must just be some intermittent throttling or similar. I'll just have to live with it I suppose... thanks!
    – JVC
    Aug 18, 2016 at 17:31

Your Answer

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

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