Import Internal Error during ImportXML with Google Spreadsheet

3
votes

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?

1
xpathgoogle-sheetsgoogle-sheets-importxml

1 Answers

2
votes

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.