4

I am trying to import some data (Market Capitalization) from Bloomberg website to my Google spreadsheet, but Google gives me Import Internal Error.

=INDEX(ImportXml("http://www.bloomberg.com/quote/7731:JP","//*[@id='quote_main_panel']/div[1]/div[1]/div[3]/table/tbody/tr[7]/td"),1,1)

I really do not know what causes this problem, but I used to overcome it playing with the xpath query. This time I couldn't find a xpath query which works.

Does anybody know the reason of this error, or how can I make it work?

Rubén
  • 34,714
  • 9
  • 70
  • 166
noway
  • 2,585
  • 7
  • 41
  • 61

1 Answers1

2

I am not familiar with Google Spreadsheet, but I think there is simply a superfluous closing parenthesis in your code.

Replace

=INDEX(ImportXml("http://www.bloomberg.com/quote/7731:JP"),"//*[@id='quote_main_panel']/div[1]/div[1]/div[3]/table/tbody/tr[7]/td"),1,1)

with

=INDEX(ImportXml("http://www.bloomberg.com/quote/7731:JP","//*[@id='quote_main_panel']/div[1]/div[1]/div[3]/table/tbody/tr[7]/td"),1,1)

Also, are you sure it's ImportXml and not ImportXML?

If this does not solve your problem, you have to explain what exactly you are looking for in the HTML.

Edit

Applying the Xpath expression you show to the HTML source, I get the following result:

<td xmlns="http://www.w3.org/1999/xhtml" xmlns:og="http://opengraphprotocol.org/schema/" xmlns:fb="http://www.facebook.com/2008/fbml" class="company_stat">641,807.15</td>

Is this what you would have expected? If yes, then XPath is not at fault and the problem lies somewhere else. If not, then please describe what you are looking for and I'll try to find a suitable XPath expression.

Second Edit

The following formula works fine for me:

=ImportXML("http://www.bloomberg.com/quote/7731:JP","//table[@class='key_stat_data']//tr[7]/td")

Resulting cell value:

641,807.15

The XPath expression now looks for a particular table (since there are only 3 tables in the HTML and all of them have unique class attribute values).

EDIT

The reason why your intial path expression does not work is that it contains tbody, see this excellent answer for more information. Credit for this goes to @JensErat.

Community
  • 1
  • 1
Mathias Müller
  • 22,203
  • 13
  • 58
  • 75
  • In the original question I mistakenly added an extra paranthesis, now I removed it. Yet, I'm pretty sure that this is not about paranthesis, it is about Xpath, because I have other cells works perfectly which extract some other data from the same page. – noway Sep 19 '14 at 21:23
  • @noway Alright. What HTML element would you like to find? Are you looking for chart data? – Mathias Müller Sep 19 '14 at 21:25
  • No, as I stated in the question I am looking for Market Cap value, seventh row under the chart. – noway Sep 19 '14 at 21:26
  • Thanks, this one works. People believe that the xpath engine behind the gpogle docs is pretty buggy. I use index, because sometimes the returned data span across a few cells. Using index, I am being sure that only one cell will be updated. Thanks again. – noway Sep 20 '14 at 19:40
  • The problem is the `/tbody` axis step: http://stackoverflow.com/questions/18241029/why-does-my-xpath-query-scraping-html-tables-only-work-in-firebug-but-not-the – Jens Erat Oct 12 '14 at 17:39