If you have spent some time looking for historical weather data, you will have found that finding a reliable source can be a time consuming and lengthy affair. In this blog, we are going to use Microsoft Excel to find the historical weather and climate information for a location.
We also provide weather history and weather forecast data for download via our weather data page. Historical weather data and weather forecast data can be read into any application or script using our Weather API.
Here are the steps we will walk through:
- Add the location, date and names to Microsoft Excel
- Look up the historical weather records
- Perform basic analysis on the results in Microsoft Excel (optional)
- Save the results out of Microsoft Excel to use in another application (optional)
We will use the weather data Microsoft Excel Weather Add-in to retrieve the weather information directly into the workbook. Once we have obtained the raw historical weather data, we will then perform some basic analysis on the data directly within Microsoft Excel.
Step 1 – Add the location, date and names to Microsoft Excel
The first step is to create a new Microsoft Excel workbook. We will add location and date information into this worksheet so that the weather data retrieval can proceed. To retrieve historical weather information, we need two main pieces of information: for what location do you want data and for what time period do you want data?
We will start with specifying the locations we are interested in. We can specify the locatoin either as an address or part of an address or as a longitude and latitude coordinate. First we’ll start with addresses.
If you have addresses, place the word ‘Address’ in cell A1. In the subsequent row beneath the Address header enter an address such as “New York, NY”. Note that it is not necessary to have a fully formed street address. The weather retrieval system will look up the best estimate for the partial address given.
Longitude & latitude
If you have longitude and latitude information, place the word ‘Latitude’ in cell A1 and “Longitude” in B1. In the subsequent row beneath the headers enter the laitude and longitude of the locations.
It is generally useful to have a name column if the location information does not easily identify the location to the user. If you are retrieving weather data for a list of addresses, the address itself may be enough information for the user to identify the location. However for latitude and longitude locations, a name is very useful. The name can be any unique value – such as the address, a business name or another landmark.
The final piece of information needed to retrieve the historical weather data is the dates for which you would like the information. To do this, add a ‘Date’ column and populate it with dates or date time values. In the Visual Crossing Weather product, historical weather data is stored at the hour level so you can enter dates or times. If you enter dates without time, then the summary of the weather for that date will be retrieved. For example the results would include the daily maximum and minimum temperature and the total amount of rainfall (precipitation). Finally, if you add multiple rows of dates for the same location then you should copy all the address, longitude, latitude and name information from the previous step to all rows.
Step 2 – Look up the historical weather records
We now have the location and date information required to be able to retrieve the historical weather records. We will use the Excel Weather add-in to retrieve the weather information.
If you would prefer to load weather data into Excel using a web query, check our Excel Weather Workbook. The Weather Workbook provides a quick way to import weather data into Excel.
First install the Weather Add-in then open the add-in and sign up for a trial if necessary (note that trial accounts are limited to 100 historical weather records). When you click the Select Range button, you will see that Visual Crossing Weather automatically populates options based on the data that you have provided. By naming the columns with clear names in the previous step, you should find that the tool identifies the data correctly. If you chose column names that the add-in did not guess, then you can simply use the drop downs to select the columns manually.
For more information on using the Visual Crossing Weather Add-in, check out our full Getting Started with Weather for Microsoft Excel tutorial.
Step 3 – Perform basic analysis on the results in Microsoft Excel
We now have a set of weather data within Microsoft Excel. This is the data for New York for all of 2018. Let’s do some simple analysis! To start, we’ll plot a simple graph of Date on the x-axis and daily maximum temperature on the y. This results in the following graph.
We can see the typical seasonal trend over the year – warming in the first half of the year and cooling in the second half. Let’s make a quick summary within Excel of the annual temperatures:
The summary is made by finding the minimum and maximum values of the temperature columns. We’ve also used the convert function to show the temperatures in both Fahrenheit and Celsius.
If you would like more information on conversions, check out our Weather Unit Conversions Blog.
Step 4 – Export the results out of Microsoft Excel to use in another application
Finally, if you would like to use the data outside of Microsoft Excel in another system such as a database or business inteligence tool, you can easily save the data using Microsoft Excel’s inbuilt export tools. One of the most common formats is CSV or “Comma Separated Values”. This uses the Excel grid and saves a simple text file where each cell in the worksheet is separated by a comma. To do this, simply save the workbook as a CSV format.
Using the web services and CSV data
Another way that you can obtain the historical weather data is to use the weather data services. This web page return the historical (or forecast) weather data directly into a CSV dataset that can be imported into Excel, Google Sheets or any other application that supports the CSV format.