How to Load Weather Data into Microsoft Excel using a Web Query Data Connection

One of the powerful features of the Visual Crossing Weather Service is the ability to query data via a Web Service or URL.  This ability isn't only limited to coders but rather can be utilized to import data into most Business Intelligence Systems including Excel.   When you include your data into Excel, you can refresh the data or refer to it from other Sheets to pull out specific time periods or weather variables as needed.   In this document, we will show you the basic steps required in loading this data.

 Before we begin, if you want to see our Weather Workbook which is a complete weather query system built into a single Excel Workbook, please take a look at our blog explaining how the Weather Workbook can be downloaded and used:   https://www.visualcrossing.com/blog/weather-workbook-for-excel

Let's continue on building our own simple query here...

Building the Query

All Queries can be built using the query builder page which is shared with the weather data download page.  Just visit:  https://www.visualcrossing.com/weather/weather-data-services

Upon first visit you will need to register for an account.  Free trials are available and it only takes an email address to sign up.   If you need help signing up, please review the following article:

https://support.visualcrossing.com/hc/en-us/articles/360039064432

Once logged in, you will see the following query page which will start by asking you for your list of locations.

 

mceclip0.png

We will choose to 'Add Manual' and add the "Herndon, VA" location.   Next we will be prompted to enter in the type of query.

mceclip1.png

Here we will choose 'Forecast' and click the button to 'Request Weather Data'.   We will now get a results preview as follows.

mceclip2.png

If we click on the 'Query API' button we will be taken to the API builder page that will allow us to get the necessary query string that we need to load our data.

 

mceclip0.png

Here you will see many different options for 3 types of queries including:  GET, POST, ODATA.   The default GET option will be our choice for this exercise so we simply click on the 'Copy full query' button and the entire query will be loaded into your Copy-Paste buffer.   Now we are ready for Excel.

Start a new Excel Document and choose a blank template for this exercise.  Once open, click on the 'Data' tab and select the 'From Web' option as seen below.

 mceclip1.png

Now we can paste in our url from the Weather Query Builder.  Please note that the query builder automatically embeds your API key (if you have the proper level of privileges on your license)  into the URL Query String.  This will allow you to refresh you query every time you load the Excel page and will not require you to rebuild your query unless you changes locations, dates, or query types.

mceclip2.png

After we click 'OK' and choose to 'Load' the data we can now see the data loaded into excel.

mceclip3.png

The data is ready to use.  The most common usage is to have this table as a source sheet that can be referenced in other parts of the spreadsheet.  Many users choose to create a pivot table to create different views and aggregation levels for the weather data as well.   

 Please note that if you want to have this table automatically refresh data please review the following document:

https://support.visualcrossing.com/hc/en-us/articles/360039091392-How-to-automatically-refresh-Weather-Data-in-Microsoft-Excel-

 

 

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.