scraping data from a website

**redpanda** · 05-20-2010, 02:06 PM

I am trying to scrape the hourly weather data from a NOAA web page. How can I do this? I have tried to do a web query and that isn't giving me the desired result. I only need the hourly weather data table. Here is the link, and you thank you for all your help!

http://forecast.weather.gov/MapClick....y=9&BackDay=0

**Leith Ross** · 05-20-2010, 07:45 PM

Hello redpanda,

After you have selected your tables using the Import Data Wizard, did you click the Properties... button to bring up the External Data Range Properties dialog?

On this dialog there is check box named Refresh every. When you check this box by clicking it, you can then set the number of minutes between refresh cycles. Did you do this?

**Chippy** · 05-21-2010, 06:17 AM

I think the OP means the table of hourly data, not retrieve it hourly. I tried it with Excel 2003 and a web query on the table doesn't retrieve the expected data; selecting the entire page doesn't work either. In this case you'll need VBA to do Internet Explorer automation and extract rows and cells from the appropriate HTML table:

Please Login or Register  to view this content.

**redpanda** · 05-22-2010, 09:45 PM

Thank You for the help!

**ShredDude** · 05-23-2010, 02:41 PM

The NOAA site you refer to offers the data in an XML format. IIt appears that the URL to navigate to the XML version of the data employs the Lat/Long coordinates of the location you're after.

I used the location you had in your example and retrieved the XML data into a string variable using an XMLHTTP request. From there, you can parse it down to retrieve the elements you need to capture into a spreadsheet.

Here's a rough example to get you started. Works very fast. No need to employ Internet Explorer automation.

Please Login or Register  to view this content.

.

**ShredDude** · 05-23-2010, 09:21 PM

I felt the previous post was a bit sloppy, so I re-did the routine using the responsexml.

Please Login or Register  to view this content.

Any feedback on parsing XML would be greatly appreciated as I'd like to learn the subtleties of it. I'm coming across more and more sties that offer XML. From the little I've used it, it's very efficient, as compared to scraping a traditional HTML page.

**Chippy** · 05-24-2010, 08:03 AM

Inspired by Shred Dude's code, here's a much faster version of mine which uses XMLHTTP instead of IE:

Please Login or Register  to view this content.

**Tom Schreiner** · 05-24-2010, 11:02 PM

Nice one ShredDude.

**bairdgbaird** · 07-05-2012, 01:00 PM

This thread is very helpful. I have a followup question: How can I tweak the VBA so that it waits for a page to fully load?

**Cutter** · 07-05-2012, 01:20 PM

Welcome to the Forum. Sorry, but you've inadvertently broken one of our rules. Please read over the following rule:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.