Try to import website html table into excel

Hello Everybody

I've tried to do some kind of automatic import of these two webistes with Excel 2007
(Tab Data and From Web):

http://www.cmegroup.com/trading/energy/crude-oil/light-sweet-crude_quotes_settlements_futures.html

https://www.theice.com/marketdata/reports/ReportCenter.shtml#report/5

Are there spezial Tools or Makros because Excel is not able to identify the tables as table.
HelpdeskJBCAsked:
Who is Participating?
 
KyleSWCommented:
I use Chrome since it makes things easy, hit F12 to open developer tools and load the page.

On the Network Tab are all the pages requested by the web page when it loads. If you click on them you get extra info for each line, which opens on a tab to the right. The top part of the first tab that opens details the request page (the url), the response can be seen on the response tab - this was easy to spot since all it really returns is a table.
0
 
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
It worked for me.  Did you select the table in the browser?  I just used chrome, selected the area and pasted to excel.
wb1.xls
0
 
c_cardCommented:
An example where you can see whats going on:
1.  Create a page that loads the  page with the table you want in it. Just use an IFrame.
2.  Create a button "Excel Export".
3. Use jQuery to construct a get that fires from the button that retrieves the html of the table element.
The GET needs ContentType:"application/vnd.ms-excel" ad add a header that has value "Content-Disposition","attachment;filename="+ sExportFilename +".xls"

This will let you download the table as an excel spreadsheet.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
HelpdeskJBCAuthor Commented:
Hy there in the excel sheet was no connection so just fyi i need a data link to the source which i can update by clicking refresh...

I already tried to create an excel export but excel is not able to get the table so i can only mark the hole side for import and this is stupid.
0
 
KyleSWCommented:
The problem is that neither of those sites actually have tables in the source data, the first pulls the data in from another location, so that's actually straightforward. The second is a little more tricky since the tables are built dynamically on load in javascript. Try this link for the first table, note you can change the date in the url to change the data:

http://www.cmegroup.com/CmeWS/mvc/xsltTransformer.do?xlstDoc=/XSLT/da/DailySettlement.xsl&url=/da/DailySettlement/V1/DSReport/ProductCode/CL/FOI/FUT/EXCHANGE/XNYM/Underlying/CL?tradeDate=01/28/2013

I'll have a look at the second one when I get chance
0
 
HelpdeskJBCAuthor Commented:
Great work!
How did you manage to come to this link?
0
 
HelpdeskJBCAuthor Commented:
thats a great info
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.