In this article we will use Web Queries in Microsoft Excel to retrieve Weather Data such as a weather forecast, historical weather observations or historical weather and climate statistics. We will then set the queries up to refresh automatically.
This powerful refresh technical can be used to ensure that the latest weather forecast is populated into an Excel Workbook so that it can be used to make real time business decisions.
Step 1 – Set up a Weather Data Query in Microsoft Excel
We will use the steps describe in How to Load Weather Data into Microsoft Excel via Web Query URL to retrieve create the initial weather query. This uses the Visual Crossing Weather API services to retrieve the required weather data.
As described in the above document, to create the initial query:
- Log into https://www.visualcrossing.com/weather/weather-data-services
- Add one or more locations
- Create the appropriate weather data definition for Weather Forecast, Historical Weather or Historical Weather Summaries as your requirements specify.
- Use the query build to create the URL that will be used for the Microsoft Excel Web Query request.
After the query is set up, you will have the initial data in Microsoft Excel and also a web query data connection set up.
Step 2 – Set the update rules for the Weather Data Query in Microsoft Excel
You can refresh the weather data whenever the workbook is opened, on a schedule or based upon other refresh rules set within the connection properties of the query within Microsoft Excel.
To set the refresh properties, open the connection properties in Microsoft Excel:
This will open the connection properties:
Right mouse click on the query and click on Properties to bring up the Query Properties dialog
The refresh control section defines the options available for automatically updates. The options for refreshing the data include updating based upon:
Time period – update the data every fixed number of minutes
When opening the workbook – update the data when the workbooks is opened
Updating on Refresh All – update the data automatically whenever the user hits ‘Refresh All’
For more information on updating queries within Microsoft Excel see Refresh an external data connection in Excel on the Microsoft Support site.