Filter and Alert on Large Volumes of Weather Forecast Data.
The Weather Alert Workbook is designed for the end-user who wants to query a large number of site forecasts and be alerted to exceptional weather conditions at those locations. It is a free Excel Workbook for all of our users to download and use and/or modify to fit their needs. You do not need to be familiar with Power Query, filtering or advanced Excel techniques. Simply download, enter your key and start using it right away. However, some advanced users may use this workbook as a starting point for developing their own workbooks. In this blog we will cover both usage and details of construction.
If you have been following the Weather Workbook and Multi-Site Weather Forecast Blogs you are aware of how these workbooks can query Historical and Forecast data directly into your Excel workbook. While the Weather Workbook is a general tool for making weather queries, the Multi-Site Forecast workbook allows users to easily monitor the entire forecast for many locations at once. However, in some instances, users will need to query large numbers of locations and many weather variables. For example, a large construction company can have 100 sites going at once and they may need to monitor max temperature, min temperature, wind gusts, precipitation, snow and much more. Even an effective interface such as the Multi-Site Forecast Workbook requires too much manual scanning. This is where the Weather Alert Filtering Workbook can help. This workbook uses the Power Query infrastructure to pre-filter only conditions that meet certain criteria and then the workbook can flag only the locations that may have concerns. In this blog, we will walk you through the details of how this workbook queries, filters and alerts users to exceptional forecast conditions.
As always If you are interested in learning about all the features of the Weather API you can visit the documentation page:
Weather API Documentation
If you have questions about the weather variables and their definition you can review the following:
If you are interested in the core Weather Workbook, please visit this blog:
Weather Workbook for Excel
If you think that the Multi-Site Forecast Workbook meets your needs, please see the following blog:
Multi-site Forecast Workbook for Excel
Let’s get started with the filtered weather alerts in Excel.
How do I get the Weather Alert Workbook?
This part is simple… Just download it from the following link on the Visual Crossing website:
Weather Alert Workbook (click to download)
Once you save this workbook to your desktop, simply run the Excel Workbook just as you would any other workbook you use.
You will also need to have an API Key to send with the query so the server can authenticate your request as being from a registered user. Trial accounts and some paid accounts will provide you with API Key access. To sign up for an account, please follow the steps in this technote:
How to sign up for a Weather Data & Weather API Account
Once you have an account, just click on the “Account” button after signing in and copy the API Key. You will use this below when making your first query.
A Note on Privacy
When you first open the workbook or possibly when you make your first query, the Excel system will warn you that this workbook is making external queries with the data you enter.
One simple solution is to check the ‘Ignore’ option and continue on. If you are concerned about Privacy or have Corporate policies to adhere to, then please visit the following page on the Microsoft support site to properly set up your Privacy options for Power Query.
Also, if you want to know the exact string that is being sent as a query URL, click on the ‘AdminSettings’ sheet of the Weather Workbook and you can verify all data sent.
Making your first Weather Alert Query?
The first step is to open the Weather Alert Workbook in Excel. Once opened you will find 5 Sheets represented as tabs of your workbook at the bottom of the Excel application:
These tabs are as follows:
Introduction – This Sheet tells you all about the Weather Alert Workbook and gives you some helpful links in getting started. Including the link to sign up for the service and getting an API Key.
Forecast Settings – This Sheet is the user page and where they will enter in data such as Locations and Alert Conditions. If there are weather variables that you don’t want to alert on, leave them empty. (do not set them to zero)
Alert Calendar – This sheet shows a list of all the alert situations found in the ALERT TABLE but in a Pivot Table. This allows the user to see location by location alerts ordered by date where the alert conditions are highlighted.
ALERTS TABLE – This Sheet contains the rows of the entire forecast that have been flagged for alert-level conditions. It is a subset of the RAW FORECAST.
RAW FORECAST DATA – This Sheet contains the forecast query definition and shows the data returned from the forecast query for all locations for all 15 days for every weather variable available in the system.
Admin Settings – This Sheet has some settings to help build the URL Query Strings. This sheet should not be modified but is helpful for learning about queries and debugging any issues you may run into.
To make your first query, simply click on the “Forecast Settings” sheet. We simply fill in the Locations, Weather Warning Limit values and your API Key.
PLEASE NOTE: All user-editable sections in this workbook are color coded with a green background.
Let’s get started…
Step 1 – The most important step is to enter in your private API Key into the green box next to the “API KEY” label.
Step 2 – In the ‘ENTER YOUR LOCATIONS’ section at the left, enter a few locations that you wish to query on. These can be addresses, cities, zip codes, etc…
Step 3 – Next, you can enter in your Warning/Alert Conditions next to each weather variable. Start simple with Temperature levels by setting Min Temperature to 32 and Max Temperature to 90 degrees.
FINAL STEP – Now that we have our Forecast Alert Query defined, we can fetch the data simply by using the ‘Refresh’ functionality of the Excel Power Query system. To do this, click on the ‘Data’ menu and click on “Refresh All”.
This action will refresh every query in the workbook. Below, we describe other options you have for submitting/refreshing queries.
We can now examine which locations were alerted right on our Forecast Settings sheet:
We can easily see which locations may have problems in the near future. By clicking over to the ‘ALERTS TABLE’ sheet we can see the details of those locations and the dates of concern in a filtered set of rows.
Now we will modify the weather alerts for all conditions to show off the Alert Calendar:
Here we make use of all of the weather variables. PLEASE NOTE: if you don’t want to alert on specific weather variable, simply select the box and hit the delete key to clear the contents to blank. Setting to 0 is not helpful as it will be considered a condition value. Now click over to the “Alert Calendar” sheet and select Data->Refresh All from the Excel Ribbon.
Here we can see the value of the Alert Calendar and easily highlight our alert conditions at every locations. Every single row in this list will have an alert condition as they have been filtered by our Power Query.
Refreshing Data
One of the first questions users have is about the need to click on “Refresh All” to update the data. Users can simply right-click on the datasets and refresh any sheet independently. There are many other refresh options here that you can choose to add and those are defined in the following technote:
How to automatically refresh Weather Data in Microsoft Excel
There are many good options and if you need help just email us at support@visualcrossing.com
How does the Weather Alert Workbook Work?
The Weather Alert Workbook is a standard Excel Workbook with defined URL queries to call out to the Visual Crossing Weather Data Service to get weather forecast data and bring it into your workbook. There are three (3) main areas of interest that we will examine: Query, Filter, Alerts. Remember that you do not need to understand the details of how the workbook is constructed, you simply need to enter locations and weather limits.
Query
uses the built-in Excel Power Query feature to execute all data fetches as well as filtering of the data. If you are not familiar with the Power Query feature, simply open any workbook, click on the ‘Data’ menu and click on the button titled “From Web” as seen below:
If you click on this button, Excel will ask you for a URL from where it can retrieve data. As described in our technote:
How to Load Weather Data into Microsoft Excel using a Web Query Data Connection
A complete URL will fetch the data immediately for you. It is not required that you try this functionality at this time, the workbook does it for you. Here’s how:
This Power Query (“From Web”) functionality automatically builds a connection and defines the query for you based on the URL you enter. The data that comes back is automatically detected and put into columns with data types. It is a simple-to-use but powerful data query mechanism that any user can take advantage of.
The Weather Alert Workbook fetches a Forecast dataset and provides a table in Excel of the result data. Here is an example of the URL Query String it passes to the Power Query Feature set
To examine the actual query that is made, simply choose Data->Queries&Connections. This will open a sidebar where we can see our two main queries for this workbook:
If we click on either of the Queries we can choose to ‘Edit’ these queries. If we additionally choose ‘Advanced’ we can see the code editor for our queries:
Filtering
One of the great features of the Weather Alert Workbook is that it pre-filters data at the query level rather than loading it fully into Excel and forced into using Excel functions or formatting to highlight the data. To accomplish this, the workbook is equipped with two features. First is the cell input locations. On the Forecast Settings sheet you can see for every weather warning value that it has a defined name so that it can be used by the weather query.
We can see that the Max Temp cell value is defined as ‘MAXTEMP’ in the upper left of the window. Each entry value is uniquely defined.
The second part of the query is the Power Query code. We will go back to our earlier steps and click edit on our ‘Alert Query’ and choose ‘Advanced Editor’ to see our Power Query code.
In the example of our Max Temperature we can see above that we fetch the cell value by fetching it by name (MAXTEMP) from the CurrentWorkbook() at row 0 and Column1. Once we have this value it is part of the larger filter which is shown in code as “Table.SelectRows” function which only selects the rows from the larger data set when the conditions are met.
Alerting
The final step in the process is alerting the user to the fact that an exceptional weather condition was found based upon your settings. This is accomplished on the Forecast settings page under the “ALERT” column. If we highlight any of those columns we can see that it does a VLOOKUP on our ALERTS TABLE. If it finds that a location is in that table, it highlights it in the row with the text “ALERT FOUND”.
We now have a complete round trip alert query! The user can change locations or weather warning values at any time and simply hit Data->Refresh All to update the entire workbook.
If you have any requests or tips you would like to share please let us know at support@visualcrossing.com