Have you ever want to do more with weather data in your Excel workbooks? In this blog entry we are going to consider the tasks of a construction site manager and we want to create a workbook where we can easily monitor the weather at a large number of sites to evaluate many different weather variables for different job tasks. Here are a few common scenarios:
- It is below 32 degrees at 10 locations so concrete cannot be poured at those sites.
- Wind gusts are going to exceed 50 mph so extra precautions with high-rise or crane work must be adhered to .
- Rain is going to exceed two inches in a short time period so grading work will be unsafe and extra measures must be taken to stabilize completed grading work.
- Excessive cold or heat may impact workers or machinery.
This workbook would need to cover several different functional areas in order to make it a successful daily workbook. Here is our list:
- Automatically query for live 15-day forecasts of various weather conditions from an accurate weather source.
- Return weather data for dozens of weather locations anywhere in the world.
- Set weather condition alerts and show which locations have potential issues.
- Easily see forecast calendars for all sites by either daily or detailed hourly data to see specific hourly windows where tasks may be interrupted.
Once we are able to accomplish these goals, we will make our operation better prepared for weather conditions which will translate to a more efficient and safer workplace.
In this blog we are discussing the final workbook for construction, how you can download, use and modify it. We will discuss some Excel techniques as well as touch on the PowerQuery and PivotTable features that make this possible. In other blogs and tech notes we will feature specific technical solutions and how you can implement them. Let’s get started.
Weather Workbook Series
This workbook builds upon several other workbooks in our series that teach users how to use Excel to retrieve weather data and utilize to accomplish tasks. Here are some valuable links for you to visit and learn more about the background of this series:
Weather Workbook for Excel
Multi-site Forecast Workbook for Excel
How Can I Get The Construction Weather Workbook?
DOWNLOAD THE CONSTRUCTION WORKBOOK
Other weather workbooks are available on the GitHub: Weather Workbooks on GitHub
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 an 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 Tour of the Construction Weather Workbook
There are many sheets on the workbook and we will define them here so that you are aware of their uses. Note that some of them may be hidden and may be optionally shown by the user.
- Introduction – Page to describe the workbook
- Forecast Settings – This is the main user control panel for the workbook. Here you set the locations, set conditions thresholds, enter your API key and monitor alerts for locations.
- Forecast Calendar – This is a list of locations in calendar form for the next 15 days. It will show all forecast conditions and color highlight those that are outside of the set values.
- Hourly Calendar – This is similar to the Forecast Calendar but shows hourly data instead of daily aggregations.
- ALERTS TABLE (hidden) – The alerts table is the raw query result of alerted conditions only. This is a filtered set of the queried data.
- RAW FORECAST DATA (hidden) – This is the raw forecast result and is only required for debugging purposes or to allow users to look at the raw data.
- Admin Settings (hidden) – This sheet is where the strings that build the query are constructed for submission to the weather server. Only if you are customizing the worksheet should you ever need this page. Technical Support may ask occasionally for you to send them the final query string for debug purposes.
This Workbook was designed for the following use case: A construction manager can easily enter in many construction sites, set weather alert values that they would like to be alerted on for all of their locations. Then find more details about which days and hours are triggering those alerts so that they can take appropriate action. They can also use the calendar functions to plan tasks for up to two weeks ahead.
Forecast Settings
The main settings page of the Construction Workbook is the Forecast Settings sheet. Here we can do the following tasks
- Add API Key – All of the Weather Workbooks use the Visual Crossing Weather Data Server and require that you have an API Key. See the section above on how to acquire and copy yours from your account. Simply past your API Key into the green shaded area labeled API Key.
- Enter in Locations – Being able to monitor your many locations in a single view is critical to managing sites. In the green list area labeled “Enter Your Locations” you can enter in your location strings. Anything from full addresses, zip code, cities, counties or lat/lon values are acceptable.
- Set and Monitor Alert Values – Scanning weather forecasts for dozens of construction sties is error-prone and inefficient, one the key goals of the Construction Workbook is to allow managers to set their own values that they wish to be alerted on. We have included several conditions here but the workbook can be modified for other conditions as well. Simply enter in your maximum (minimum for min temp) values and the system put a red “ALERT FOUND” indicator next to locations that have triggered an alert.
Forecast Calendar
The Forecast Calendar sheet is a PivotTable that takes the results of a PowerQuery to the Visual Crossing Weather Service. The table is pivoted such that dates are along the columns and all weather variables for every location are listed in the rows.
In addition to the forecast calendar view you will notice that this table utilizes Excel’s Conditional Formatting feature to highlight our conditions when they exceed condition alert values. Managers can easily weeks at a glance for dozens of locations and where areas of concern will be.
Hourly Calendar
Many times it is not good enough to know if a day’s high or low values exceed limitations but rather construction managers need to be able to schedule windows in the day where conditions are right. It also allows for a more granular level of efficiency through the day by allowing weather-related tasks to be “squeezed” into a schedule. The Hourly Calendar is another PivotTable derived from a PowerQuery using the Visual Crossing server to pull hourly forecast data.
Note that here the Visual Crossing server is giving us temp rather than mintemp and maxtemp found in the Forecast Calendar. There is no minimum and maximum for the hour… just a temperature value, but we will still have two alerts for max/min but they apply to the same temp value. Again, through conditional formatting we can see how many hours during the day, cold/heat/precip will last. This gives us a finer grain control over our tasks.
Alerts Table
Even though this table is hidden it drives the “ALERT FOUND” status on our Forecast Settings page. This table has a specialized PowerQuery code feature that allows us to filter in only our alerted conditions rather than loading an entire Excel table with data that we don’t need.
We can see that this is a shortened list of only locations and dates that triggered an alert. We can then use a location’s existence on this list to trigger the alert feature on the main sheet. We won’t go into too much detail in this blog as to how we accomplish this but we will show you the script in the PowerQuery editor that helps us to accomplish this task. We will discuss this in greater detail in a technical blog.
let
WxQuery=Excel.CurrentWorkbook(){[Name="ALERTQUERY"]}[Content]{0}[Column1],
MinTemp=Excel.CurrentWorkbook(){[Name="MINTEMP"]}[Content]{0}[Column1],
MaxTemp=Excel.CurrentWorkbook(){[Name="MAXTEMP"]}[Content]{0}[Column1],
ChancePrecip=Excel.CurrentWorkbook(){[Name="CHANCEPRECIP"]}[Content]{0}[Column1],
MaxPrecip=Excel.CurrentWorkbook(){[Name="MAXPRECIP"]}[Content]{0}[Column1],
Gust=Excel.CurrentWorkbook(){[Name="GUST"]}[Content]{0}[Column1],
MaxSnow=Excel.CurrentWorkbook(){[Name="MAXSNOW"]}[Content]{0}[Column1],
RawData=Web.Contents(WxQuery),
Source = Csv.Document(RawData,[Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.None]),
PromotedHeaders = try Table.PromoteHeaders(Source, [PromoteAllScalars=true]) otherwise Source,
ChangedType = try Table.TransformColumnTypes(PromotedHeaders,{{"address", type text}, {"datetime", type datetime}, {"latitude", type number}, {"longitude", type number}, {"resolvedAddress", type text}, {"conditions", type text},{"name", type text}})
otherwise error Text.FromBinary(RawData, TextEncoding.Ascii),
ChangedType1 = try Table.TransformColumnTypes(ChangedType,{{"wdir", type number}}) otherwise ChangedType,
ChangedType2 = try Table.TransformColumnTypes(ChangedType1,{{"mint", type number}}) otherwise ChangedType1,
ChangedType3 = try Table.TransformColumnTypes(ChangedType2,{{"maxt", type number}}) otherwise ChangedType2,
ChangedType4 = try Table.TransformColumnTypes(ChangedType3,{{"temp", type number}}) otherwise ChangedType3,
ChangedType5 = try Table.TransformColumnTypes(ChangedType4,{{"wspd", type number}}) otherwise ChangedType4,
ChangedType6 = try Table.TransformColumnTypes(ChangedType5,{{"cloudcover", type number}}) otherwise ChangedType5,
ChangedType7 = try Table.TransformColumnTypes(ChangedType6,{{"heatindex", type number}}) otherwise ChangedType6,
ChangedType8 = try Table.TransformColumnTypes(ChangedType7,{{"pop", type number}}) otherwise ChangedType7,
ChangedType9 = try Table.TransformColumnTypes(ChangedType8,{{"precip", type number}}) otherwise ChangedType8,
ChangedType10 = try Table.TransformColumnTypes(ChangedType9,{{"sealevelpressure", type number}}) otherwise ChangedType9,
ChangedType11 = try Table.TransformColumnTypes(ChangedType10,{{"snowdepth", type number}}) otherwise ChangedType10,
ChangedType12 = try Table.TransformColumnTypes(ChangedType11,{{"snow", type number}}) otherwise ChangedType11,
ChangedType13 = try Table.TransformColumnTypes(ChangedType12,{{"humidity", type number}}) otherwise ChangedType12,
ChangedType14 = try Table.TransformColumnTypes(ChangedType13,{{"wgust", type number}}) otherwise ChangedType13,
ChangedType15 = try Table.TransformColumnTypes(ChangedType14,{{"windchill", type number}}) otherwise ChangedType14,
#"Filtered Rows" = Table.SelectRows(ChangedType15, each [mint] <= MinTemp or [maxt] >= MaxTemp or [wgust] >= Gust or [pop] >= ChancePrecip or [precip] >= MaxPrecip)
in
#"Filtered Rows"
Briefly what is going on here is that the PowerQuery script is reading the user-set alert values, querying the data, setting types for every weather condition and then applying a filter for every single weather value that we set alerts on. This is a very powerful feature that anyone can take advantage with limited skills and without requiring messy macros on large datasets.
All Done!
As you can see the simple Weather Workbook shows the amazing capabilities of Excel and the Visual Crossing Weather Service work together. Being able to retrieve data in CSV format for multiple locations in a single query gives us the capability to query for a single table of data that gives us everything we need to load weather data directly into Excel and do powerful, time-saving exercises with it. The Construction Workbook is just one example but it can be used out-of-the-box or modified by users to meet their specific needs. If you need any help at all please contact us at support@visualcrossing.com .