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?

  • 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 '16 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. – Jonathan van Clute Aug 16 '16 at 2:09
  • Which specific element do you want? Without my having to break down your xpath segment by segment – Aurielle Perlmann Aug 16 '16 at 2:41
  • Please edit your question an add what do you think is the problem. – Rubén Aug 16 '16 at 2:56
  • Additional specifics added – Jonathan van Clute Aug 16 '16 at 3:28
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

  • 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. – Jonathan van Clute Aug 17 '16 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 '16 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? – Jonathan van Clute Aug 17 '16 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...? – Jonathan van Clute Aug 17 '16 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/… – Jonathan van Clute Aug 17 '16 at 3:42
1

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

  • 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 – Jonathan van Clute Aug 16 '16 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/… – Jonathan van Clute Aug 18 '16 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 – Aurielle Perlmann Aug 18 '16 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! =) – Jonathan van Clute Aug 18 '16 at 16:15
  • OK this is so confusing and frustrating... now your getOpenInterest function is failing intermittently. Some cells it works, some it just stays on "Loading..." indefinitely, and some it eventually does load a value. – Jonathan van Clute Aug 18 '16 at 17:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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