Weather Forecast Macro

[dantheram]

Morning all,

i have the code below to fetch historic data but i cannot figure out how to manipulate it to bring back a forecast, any ideas?

Sub RetrieveWeather()     
    Dim myYear As Integer
    Dim myMonth As Integer
    Dim myDay As Integer
    Dim mySite As String
    Dim myCity As String
    Dim myState As String
    Dim myStateName As String
    
    myYear = Year(Date)
    myMonth = Month(Date)
    myDay = Day(Date - 1)
    mySite = "EGNX"
    myCity = "Derby"
    myState = "United kingdom"
    myStateName = "United Kingdom"
     
    With Worksheets("Weather").QueryTables.Add(Connection:= _
        "URL;http://www.wunderground.com/history/airport/" & mySite & "/" & myYear & "/" & myMonth & "/" & myDay & "/DailyHistory.html?req_city=" & myCity & "&req_state=" & myState & "&req_statename=" & myStateName _
        , Destination:=Worksheets("Weather").Range("$A$1"))
        .Name = _
        "DailyHistory.html"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = """obsTable"""
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
        ThisWorkbook.Sheets("Sheet 1").Select
    End With
End Sub

 

[theBardd]

You have all of that data for that day, so there are many ways that you could analyse it or visualise it, but it depends upon what sort of forecast you are envisaging.

 

[dantheram]

You have all of that data for that day, so there are many ways that you could analyse it or visualise it, but it depends upon what sort of forecast you are envisaging.

my issue is not what to do with the data returned by the above code, its how to get a forecast - forecasts tend to be presented in non table format, the historical data is easy to fetch into excel via means of a linked table, but how do i link to a 10 day
forecast like this -

https://www.wunderground.com/global/stations/03418.html?MR=1

 

[Comfy]

They have an API that you can use:

https://www.wunderground.com/weather/api

The API is free if you have no more than 10 calls a minute and 500 in a day.

 

[dantheram]

They have an API that you can use:

https://www.wunderground.com/weather/api

The API is free if you have no more than 10 calls a minute and 500 in a day.

i have never used an API before is there a example somewhere, that you know of, to help me out, thanks

PS i'm on excel 2010

 

[Comfy]

I don't. You could browse their website to see if they have a developer forum.

The basic concept is:

1. Send a request from Excel to their servers using the available criteria in the API documentation.
2. Their server will return the response to the webpage (from a quick glance this would be either XML or JSON)
3. Parse the XML/JSON and return to Excel.

 

[theBardd]

You could also pull the data into Power Query using a web call, you mght find that more straight-forward than signing up and using API calls then parsing XML or JSON (Power Query handles that for you).

 

[dantheram]

You could also pull the data into Power Query using a web call, you mght find that more straight-forward than signing up and using API calls then parsing XML or JSON (Power Query handles that for you).

do these work in excel 2010 - i see they utilise webservice() which i believe is excel 2013

 

[theBardd]

I just loaded it into 2010 and it worked fine. It pulls in a number of tables, everything on the page, so there is still work to be done, but the parsing has been done for you, and you have a UI to assist you.

 

[dantheram]

I just loaded it into 2010 and it worked fine. It pulls in a number of tables, everything on the page, so there is still work to be done, but the parsing has been done for you, and you have a UI to assist you.

thanks for the info, just waiting for power query to be installed then i'll set to work