Extract data from web into excel

[abhi2611]

Guys - I am using the latest version of Microsoft office and am trying to get some data from a web page but having a tough time figuring it out and keep getting this error "table highlighting is disabled because this page uses internet explorer compatibility mode". Could someone please guide me through this process. the URL for the site is below and all I am trying to extract is the table with values that auto refresh.

https://www.cmegroup.com/trading/energy/natural-gas/natural-gas_quotes_globex.html

Even a macro would be helpful. I open multiple web pages of the similar kind, copy and paste data into excel which is very time-consuming.

 

[Chihiro]

Table is populated via code on the site. Using the url you provided in code/web query will not provide the data.

There is timed script that runs every 30 seconds that retrieves JSON data. Which is then used to populate the table.

Ex:
https://www.cmegroup.com/CmeWS/mvc/Quotes/Future/444/G?quoteCodes=null&_=1549397992054

Last part "1549397992054" looks like Unix timestamp concatenated with something else...
"1549397992" = 2019-02-05, 20:19:52 UTC
Not sure what the last 3 digits represent. It's usually milliseconds, but it doesn't make sense as the site increments this number by 1 every 30 sec...

At any rate, you'll need to read the JSON document via code or some other method to get at the data.

 

[abhi2611]

Hi, Thanks for responding.

Not really familiar with coding or how to go about this.

Just curious - where did you get the link you posted above?

 

[Chihiro]

Use Chrome browser and use Developer Tool to trace network activities.

Also by scanning source code. Found table id "quotesFuturesProductTable1".

Then used CTRL + F to find other locations where that id was referenced. And found java script below.

<script type="text/javascript">

var component = {};

component.url = "/CmeWS/mvc/Quotes/Future/444/G";
component.refreshQuotesUrl = "null";
component.quoteCodes = "null";
component.extraData = {"globex":true,"componentId":1,"text_last":"Last","currentFoi":"F","showHighLowLimit":true,"text_showpricechart":"Show Price Chart","text_aboutthisreport":"About This Report","text_high":"High","text_updated":"Updated","options":true,"text_month":"Month","text_charts":"Charts","text_hilowlimit":"Hi / Low Limit","nbColTotal":14,"floor":false,"text_open1open2":"Open 1 / Open 2","optionPath":"/trading/energy/natural-gas/natural-gas_quotes_globex_options.html","text_priorsettle":"Prior Settle","currentPath":"/trading/energy/natural-gas/natural-gas_quotes_globex.html","text_legend":"Legend","text_low":"Low","text_$colon":":","text_options":"Options","text_pricechart":"Price Chart","tableId":"quotesFuturesProductTable1","text_open":"Open","text_close1close2":"Close 1 / Close 2","text_change":"Change","text_volume":"Volume","currentVenue":"G"};
component.tableId = '#quotesFuturesProductTable1';
component.handler = "cme.component.product.QuotesFutureComponent";
component.componentId = 1;

window.cmeComponents[window.cmeComponents.length]  = component;

</script>

 

[Peter Bartholomew]

This is not my territory so I am not sure whether this is helpful. However I recently saw an article suggesting that it is possible to use Excel Power Query to parse JSON. I had noticed the Parse button for JSON or XML but, as yet, I have had no cause to explore.

http://theexcelclub.com/how-to-parse-custom-json-data-using-excel/

 

[abhi2611]

Thanks for sharing!

 

[Chihiro]

If using PowerQuery. Site uses multi-layer nested json format, so you should first navigate to List, then convert list of records into table. Then expand the column(s).


You can see an example I did in link below. See post #3 attachment.
https://chandoo.org/forum/threads/power-query-extract-from-web.38655/#post-231361

However, your main issue will be constructing appropriate query string as your source. Last 3 digits of query string... as I mentioned is bit odd and I don't know the rules surrounding it.

You may also want to convert Excel/PQ Datetime to UNIX timestamp.

To convert to Datetime to UNIX time stamp...
Since UNIX timestamp is duration in seconds since 1970-01-01 0:00. FixedLocalNow() is used here, to avoid function returning different values over the course of execution.
https://docs.microsoft.com/en-us/powerquery-m/datetime-fixedlocalnow

=Duration.TotalSeconds(DateTime.FixedLocalNow() - #datetime(1970, 1, 1, 0, 0, 0))