Visual Crossing has introduced a new Query Builder tool scheduling service for creating bulk, scheduled datasets that can be downloaded at any time. We have updated Weather Workbook to download these datasets directly into Excel.
In this blog we will show you how to get the Weather Workbook, enable it with your own link and API Key and use it to download the weather data that you want directly into a workbook.
For those of you who want the previous version of Weather Workbook which shows you how to parameterize live queries to the weather server you can still visit the original post for it here:
Weather Workbook – Legacy Version
The key differences are the legacy version uses Excel cell values to enter information that is queried live on the server using the API and this new version is retrieving datasets that were already scheduled or run as CSV file datasets. You will find that this version is greatly simplified and offers most of the same advantages as the legacy version.
Let’s dive into the Weather Workbook and retrieving data in Excel.
What is the Weather Workbook?
The Weather Workbook is a standard Excel Workbook with defined URL dataset links to call out to the Visual Crossing Weather Data Service to get weather datasets and bring it into your workbook. It uses the built-in Excel Power Query feature to execute all data fetches. If you are not familiar with this 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 it best guess at the column data types. It is a simple-to-use but powerful data query mechanism that any user can take advantage of.
The Weather Workbook has a query pre-defined for you. This query accepts a URL link to a dataset that you will provide in a defined Excel as detailed below. The workbook will use your URL dataset link as the primary parameter in the query and stream the CSV file contents directly into your workbook. We will show you some of this infrastructure below and will do a more technical follow-up blog for advanced users.
As mentioned above, the Weather Workbook queries are retrieving your file dataset. It is not running the API query that built the dataset, it is simply retrieving the dataset file that was last run either manually or scheduled. To update any spreadsheet to pick up the latest, simply refreshing the Query will bring back your latest dataset.
How do I get the Weather Workbook?
Just download the Weather Workbook from the following link on the Visual Crossing Github site:
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 create a dataset which requires that you 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 “Query Builder” from the home page to begin building your first dataset. To begin building your first dataset please follow these instructions:
How to Build a Weather Dataset for Download, Bulk Import or Scheduling
This tutorial will result in a completed dataset with available URL links for use in this Weather Workbook.
A Note on Privacy & Security
When you first open the workbook and refresh and don’t update the URL link it will try to download from GitHub a sample CSV file result. Excel may ask you for the following:
Here you can simply hit ‘Connect’ as Anonymous to see the data results.
Another possible warning from Excel is 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. Power Query Privacy Page 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.
Retrieving your first Weather Dataset in Weather Workbook
The first step is to open the Weather Workbook in Excel. Once opened you will find 2 Sheets represented as tabs of your workbook at the bottom of the Excel application:
These tabs are as follows:
Weather Workbook Settings– This Sheet tells you all about the Weather Workbook and gives you some helpful links in getting started. Even more importantly the green highlighted area to paste your own task URL link into
Weather Query – This Sheet is the user page and where they will enter in data such as Locations, Historical Dates and more.
Wx DATA DOWNLOAD – This Sheet contains the retrieved data.
To make your first data retrieval, visit the MyDatasets page of the Query Builder and copy one of your scheduled (or manual) URL links as shown below:
Next we will navigate in Weather Workbook to the Weather Workbook Settings sheet and paste in your copied link into the green highlighted section:
– Now that we have our Historical and Forecast Queries 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. We can now visit our ‘Wx DATA DOWNLOAD’ Sheet to see our results:
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 Forecast or History independently. There are many other refresh options here that you can choose to add and those are defined in the following technote: Refresh Query Data in Excel
How do I Customize or Integrate this into my Workbooks?
The Weather Workbook is a fully yours to edit and copy as you need. This is a service we provide to our customers to use as a weather data download tool, query builder or as a starting point for a custom workbook designed for specific users. To edit the query for this Weather Workbook navigate in Excel to Data/Queries&Connections which will open its sidebar view:
Now we can right-click and choose to Edit the Query:
As seen above we can open the ‘Advanced Editor’ to see the script of the query. As you can see its primary job is to fetch data and assign the correct column types. As you grow in your needs and knowledge or PowerQuery and its scripting language, you can accomplish some very sophisticated ETL tasks.
The Future of Weather Workbook
The Weather Workbook is provided as a courtesy to our customers and is not a supported product. However, we will help any customer as needed to ensure your success with retrieving weather data. As additions are made, new versions will be released into the download site.
Questions or need help?
If you have a question or need help, please post on our actively monitored forum for the fastest replies. You can also contact us via our support site or drop us an email at support@visualcrossing.com.