How to load Weather Data into Microsoft Excel
A common request from Microsoft Excel users is to extend their workbooks with historical weather records and weather forecast data. In this article, I discuss multiple ways to load weather information into Microsoft Excel.
Historical Records and Weather Forecast
There are two type of weather information you may want to import in to Microsoft Excel.
Historical Records
Historical records tell us what the weather was in the past. How much did it rain? How cold was it? Analyzing this data in a workbook allow you to answer questions such as “Did my store revenue decrease because it rained?” and “What kind of weather should I expect if I schedule my wedding for this date?”
Historical records are collected by weather stations so typically we need to look up data by finding the closest weather station or a combination of nearby stations. In the latter case, the weather history at a particular point will be a an average of the nearby measurements. Some data providers provide historical data that is already processed so that they have historical records for nearly all global locations.
Weather Forecast
The weather forecast is typically available for the coming 5–15 days. This depends on the organization that is calculating the forecast. The weather data is normally derived from ‘gridded’ models. These weather forecasts split the world into a grid of tiny squares and then calculate the weather at each point. The provider of the data then looks up the data for the cell you are requesting based off address (or latitude/longitude).
Longer term forecasts can also be found with time scales ranging multiple months into the future. These longer range forecasts often describe the forecast in departures from the normal weather (“the weather will be warmer and wetter than average”) rather than exact temperatures, rainfall etc. For day to day planning such a forecast may not be useful but they are highly beneficial to people such as farmers who deal with the general growing season.
Importing the data
There are three ways to import weather data into Microsoft Excel.
Screen scraping
Screen scraping weather data is a fairly simple process — copy the data from a web page (either manually or automatically) and have Excel try to extract the required data. If the selected web site data is a simple table, such as the historical data available on Weather Underground, (https://www.wunderground.com/history/daily/us/il/chicago/KMDW/date/2019-6-4) then Excel is good at extracting the information.
If you find a source of data that is amenable to screen scraping, the process can be very quick and easy (and cheap) but there are a couple of pitfalls:
- Screen scraping is normally not allowed by a web sites term and conditions.
- It is not a process that is suitable for automatically refreshing data set. The process is too dependent on the exact format of the incoming web page to be a reliable approach.
File and web data connection import
A much more reliable approach to finding weather data is to find a source that provides data that can be imported using an Excel Web Query. If the data provider supports saving weather data a local file to your computer, then you can also import weather using in a structured table format such as CSV (comma separated values).
Better providers will also include web accessible content that allows a direct import from the web into the workbook. This make automatically refreshing the data extremely straightforward.
Older versions of Microsoft Excel support both CSV and Getting External data from web sites and so this approach will work across all major versions of Excel.
Providers of structured weather data include Visual Crossing Weather Data, NOAA and Open Weather Map. One provider, Weather Underground, has recently announced ending the support for their weather API.
For more information on how to import weather data directly into Microsoft Excel using a web query connection, see How to Load Weather Data into Microsoft Excel using a Web Query Data Connection.
If you are interested in loading weather data into Microsoft Power BI, you will find the data connection techniques discussed for Microsoft Excel are very similar. Read more in How to Load Weather Data into Power BI.
Microsoft Excel add-in
The final approach to importing data into Excel is to use an Weather Excel add-in. Using an Excel add-in can make the process of importing the data even easier. In particular, an add-in can provide a “two way” experience so that the existing data in the workbook and the weather data can work together.
A great example of this is creating a spreadsheet that includes past weather records for existing sales data. The initial spreadsheet will include the sales data, for example revenue by store by day. The Excel add-in can the retrieve the weather for the dates in question, extending the existing table. From there, the user can easily analyze the data to see how the weather affects the revenue performance.
What can I do with the weather in Microsoft Excel?
Now we have the data into Microsoft Excel there numerous ways to analyze it. The simplest is a weather forecast so users of the workbook can quickly see how the weather will change over the coming days. In most cases you can also extend that forecast data to reflect how to use the data. For example, if you are a keen gardener or farmer, you could create an Excel workbook that analyzes the gardening weather forecast.
Where can I go from here?
Now that you have weather data directly into Excel, you can start visualizing and analyzing the data directly in the environment you are already familiar with. Do you have any interesting ways you are trying to use weather data in Excel? Let me know in the comments below!
