Web Query - Select Table not being captured

[CodyMonster]

Maybe someone can help me with this one..

I'm trying to get the data from this site

http://www.cmegroup.com/clearport/p...scending&SortType=text&FilterOn=1&PageStart=1

When going through the Web Query in Excel and pulling data with the above address. I get the option to pull the data from the products table..the table with the code/name/product group/sub group..etc etc...

But when I launch the query, it doesn't pull the table data..I get everything but the product list.

Can anyone suggest a way to get this data through a query into excel?

Thanks for your help!!!!

Cody

 

[Legacy 98055]

Hi Cody. I don't think that you are going to be able to access the table using Excel's query because the it is loaded dynamically after the page is loaded. What exactly do you need? "All"? How often will you be checking this site?

 

[Legacy 98055]

Here is the GET information. You might be able to query directly. Try this URL for you query...

http://www.cmegroup.com/CmeWS/mvc/xsltTransformer.do?xlstDoc=/XSLT/md/CPCslate.xsl&file=/www/data/incoming/slate/slate.xml&sortKey=1&sortOrder=ascending&SortType=text&FilterOn=1&PageStart=1&Time=Thu Jan 28 19:23:04 2010

 

[Ron Coderre]

Yes...using that URL as the web query source displays the table, which can be selected.

Nice job.

 

[CodyMonster]

That's fantastic, can I ask one more question..
How did you figure this out?

Trying to avoid having to come back to the well every time I have a similar problem..

Any insight would be appreciated....

 

[Legacy 98055]

Hi Cody.

http://www.iewatch.com/

 

[CodyMonster]

Ok, don't think I can justify buying a software package for one thing that might or might not happen.

Just hope the well stays full, because I'm might have to come back...

Thanks again, this really was a huge help!!!

 

[CodyMonster]

Anyway I can get the date URL on the same page... see coming back already...

 

[Legacy 98055]

Hi Cody. Record a macro while creating your webquery and update the URL string with the current date and time.

URL = "http://www.cmegroup.com/CmeWS/mvc/xsltTransformer.do?xlstDoc=/XSLT/md/CPCslate.xsl&file=/www/data/incoming/slate/slate.xml&sortKey=1&sortOrder=ascending&SortType=text&FilterOn=1&PageStart=1&Time=" & Format(Now, "DDD MMM DD HH:MM:SS YYYY")

Here is an example:

Sub Macro1()
    Dim URL As String

    URL = "http://www.cmegroup.com/CmeWS/mvc/xsltTransformer.do?xlstDoc=/XSLT/md/CPCslate.xsl&" & _
        "file=/www/data/incoming/slate/slate.xml&sortKey=1&sortOrder=ascending&SortType=text&FilterOn=1&" & _
        "PageStart=1&Time=" & Format(Now, "DDD MMM DD HH:MM:SS YYYY")
    
    With ActiveSheet.QueryTables.Add(Connection:="URL;" & URL, Destination:=Range("A1"))
        .WebFormatting = xlWebFormattingNone ' xlWebFormattingAll
        .WebTables = """dyn_slateTable"""
        .Refresh False
        .Delete
    End With
    
End Sub

 

[CodyMonster]

That would work if it would provide historical data..
But it doesn't.. if you change the data after Time=
It just gives the current data and the data doesn't change.

Looks like I will need to download this daily to get a time series. Not a big deal...I'm sure I can schedule a routine. I was wanting to get the date from the same page instead of doing a manual input. But, just like the data table, you can't get this information by just using the page URL and doing a web query. You need the specific page URL, like the data table.

So to get the date for the data table, I need the specific URL...

Make sense?

Hi Cody. Record a macro while creating your webquery and update the URL string with the current date and time.

URL = "http://www.cmegroup.com/CmeWS/mvc/xsltTransformer.do?xlstDoc=/XSLT/md/CPCslate.xsl&file=/www/data/incoming/slate/slate.xml&sortKey=1&sortOrder=ascending&SortType=text&FilterOn=1&PageStart=1&Time=" & Format(Now, "DDD MMM DD HH:MM:SS YYYY")

Here is an example:

Sub Macro1()
    Dim URL As String

    URL = "http://www.cmegroup.com/CmeWS/mvc/xsltTransformer.do?xlstDoc=/XSLT/md/CPCslate.xsl&" & _
        "file=/www/data/incoming/slate/slate.xml&sortKey=1&sortOrder=ascending&SortType=text&FilterOn=1&" & _
        "PageStart=1&Time=" & Format(Now, "DDD MMM DD HH:MM:SS YYYY")
    
    With ActiveSheet.QueryTables.Add(Connection:="URL;" & URL, Destination:=Range("A1"))
        .WebFormatting = xlWebFormattingNone ' xlWebFormattingAll
        .WebTables = """dyn_slateTable"""
        .Refresh False
        .Delete
    End With
    
End Sub