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 How to Sign up With Weather Data Services.
Once logged in, you will see the following query page which will start by asking you for your list of locations.
We will choose to ‘Add Manual’ and add the “Herndon, VA” location. Next we will be prompted to enter in the type of query.
Here we will choose ‘Forecast’ and click the button to ‘Request Weather Data’. We will now get a results preview as follows.
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.
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.
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.
After we click ‘OK’ and choose to ‘Load’ the data we can now see the data loaded into excel.
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.
For further reading, check out How to Automatically Refresh Weather Data in Microsoft Excel.