Review: Visual Crossing Weather for Excel

They say that when you have nothing to talk about, you’ve got two topics to keep the conversation going:

1. The weather, and 
2. Excel. (What, did you think I was going to say sports?)

My post on retrieving real-time weather data from Weather.gov may be my most popular post, and one that certainly gets a lot of questions. Which is fine, because as we all know, “Send me data questions” is the new “Slide into my DMs:”

What’s not fine is that several readers have asked about how to pull historical or forecast weather data into Excel, and I have not had a great answer. The weather.gov service, while helpful, only returns real-time data — get it now or it’s gone, to be updated in an hour. 

For historical weather data I have suggested readers use R to pull in 24-hour weather updates. I understand that not all readers have used R, and that’s why I have developed this R Launch Checklist to get those readers started. However, all budding R mastery aside, this particular method will only make data available from the last 24 hours. What if you wanted weather data from earlier in the month? 

Then there is still the arguably harder task of pulling forecast data. Generally, weather forecasts are proprietary information that take some paid access for large quantities.

We still have the question of location. For example, weather.gov provides only information about American cities. What about other places? Maybe you need data from the Canada or Europe. Are you going to learn the in’s and out’s of each country’s public weather service API? 

With all these contingencies, there is a lot to talk about with weather and Excel. 

Fortunately, the mapping and business intelligence software provider Visual Crossing has developed a suite of tools for weather in Excel. To my knowledge this is the only tool available to gather both historical and forecast weather information, anywhere in the world, directly into Excel. 

Tools like this often run into the hundreds or thousands a month, but for a few (single) dollars a month you can pull unlimited weather data directly into a spreadsheet with this tool. I know how common a data task this is, and what a pain it can be, because I have spoken to so many readers about it. Finally, we have a tool that can do it all. 

Visual Crossing provides excellent tutorials on getting started with Weather for Excel (including the below video).

Use promocode GEORGEJMOUNT at checkout to receive 10% off your license.

Please note that I am affiliate of this product and receive a portion of the sales made using this link.

What Visual Crossing can do for you

Visual Crossing provides a suite of tools to access weather data from Excel. For a full introduction with install guide, check out their docs. Below are some highlights I have found. 

Starting Visual Crossing in Excel

Depending on your version of Excel, setting up Visual Crossing will work a little differently. I am on Excel Professional Plus 2016 and have found the easiest way to start it is by going to Insert > Store and searching for “Weather for Visual Crossing.” 

From there, I can “Add” it to my Excel session and log in. 

Pull Historical Data from Excel

The Visual Crossing Weather add-in works as a “point-and-click” menu based on information in your worksheet.

For example, let’s say we took a beautiful summer vacation to my “almae matres:” Hillsdale College, Case Western Reserve University, and Indiana University. Copy the data below to a blank worksheet to follow along:

With this data highlighted in your workbook, initialize Visual Crossing by selecting Select Range & Click Here on the sidebar:

From here, check off the data that you want (historical) and where to find the data. The weather for these locations on those days appears in our workbook. 

Pull Forecast Data from Excel

Let’s say we enjoyed this trip through Midwestern scholarship so much that we want to do it again. We can pull the forecast of each site by instead selecting “Forecast for the week ahead.” This is going to generate a table of more than three rows (21, to be precise), so this time we’ll want to locate the output elsewhere.

Let’s also select “City” as our “Name” attribute. This will make sure that our resulting table is labelled with each city’s name rather than just 0, 1, 2, etc. 

Pull Forecast Data from the Web

The Excel add-in has the benefit of loading data directly into Excel, but can take some getting used to. As an alternative, your license also allows you access to a Web portal where you can use an entirely menu-driven interface for retrieving either historical or forecast weather. 

Continue to use the same three locations — you can load in either “City, State” or ZIP code; Visual Crossing is pretty smart about intuiting the location you want. Through this menu, you can request hourly records, spaced at different durations and intervals. Powerful stuff that would be a pain to carry out in NOAA’s XML API! 

Select Generate data after you’ve made your selections, and the weather data will populate. You will have several options for what to do with the data, including downloading it as a .csv file, from which you can bring into Excel. 

Oh, The Weather You Will Scrape!

Visual Crossing’s Weather for Microsoft Excel add-in fills the gaps left by NOAA’s XML API. Give it a try for yourself with a free trial. After that, rates start at $5 monthly. 

Use promocode GEORGEJMOUNT at checkout to receive 10% off your license.

Please note that I am affiliate of this product and receive a portion of the sales made using this link.