Need VBA Macro to Stack Specific Website Weather Data

[decaman]

Hi, thanks for viewing my post!

I am trying to write a macro to query a specific website, copy the contents of the web page, paste into an excel worksheet, then go to the next sequence (day) of the website, copy those contents, paste into the next available row of the worksheet, and keep going until I have the days of data I need stacked into the worksheet. This is the specific website:

https://www.wunderground.com/history/airport/KNYC/2015/1/30/DailyHistory.html?req_city=New+York&req_state=NY&req_statename=&reqdb.zip=10001&reqdb.magic=5&reqdb.wmo=99999&format=1

It contains historical weather data for a specific location and day (hr by hr) and I want to stack all of the data (hr by hr) for a specific year, such as 2015, in a single worksheet where I can delimit, sort, analyze, etc. Right now I am manually copying and pasting and sequencing the website forward manually each day by changing the day number and it is taking me forever to do a whole year for a specific location.

Interestingly, I can sequence the day number greater than the available days in the month and it just goes to the next month. For example, If the "2015/1/30" was changed to "2015/1/32" it would show the data for Feb 1, 2015. The macro would only need to sequence the day number up to 365 and leave the month as January (i.e. "2015/1/XXX", where XXX is the day of the year) which should simplify the macro and still give all the days of the year.

I think this is pretty easy to do but I am terrible at VBA. Can someone generate a quick macro to do this or give me some tips? Thanks!

 

[John_w]

The URL returns rows of CSV data, so you can use Get External Data from Text, specifying the URL as the file name, to import the data. Each row is terminated by "< br />", so do a Find and Replace All to delete these. Record macros for both these steps and you have the starting point to import the data for each day in a year.

To generate all the yyyy/mm/dd dates in a year you could use the following loop:

Sub Loop_Dates()

    Dim dt As Date, d As Integer, y As Integer
    
    y = 2015  'required year
    dt = DateSerial(y, 1, 1)
    Do
        Debug.Print Format(dt, "yyyy/mm/dd")
        dt = dt + 1
    Loop Until Year(dt) <> y

End Sub

 

[decaman]

Thanks for the reply, John. My problem is I don't know how to loop the "Get External Data" to automatically stack the data for multiple days.

 

[decaman]

Does anyone have any more advice (or code, haha) to help me with this?