As the global leader in weather data, Visual Crossing provides the most comprehensive and accurate historical and forecast datasets available. One feature that sets us apart is the flexibility we offer for getting that data where you need it. This includes an easy-to-use API and multiple data formats. To support the most common business data tool, Microsoft Excel, Visual Crossing has created a multitude of solutions to get the weather data you need onto your workbooks ready for analysis.
Whether you need a one-time data dump or a fully automated, dynamic risk-monitoring dashboard, we have a solution tailored to your workflow. Here are the four primary methods to bring the world’s weather into your workbooks.
The CSV Method: Simple & Reliable
The most straightforward way to work with weather data is via a standard CSV (Comma Separated Values) file or through a more direct Excel format, the XLS file which is similar to CSV but with additional Excel formatting information. You can build your query in our Weather Query Builder and download the results instantly. Just load the file directly into Excel and you can begin working with it.
- Pros: Simple to use; files are encapsulated and easy to email or share.
- Cons: No “live” updating and requires manual editing/downloading of another dataset from the Query Builder.
- Best For: One-off analysis for those who need a specific historical record for a single project.
Here is a brief tutorial on how you can construct your own CSV weather dataset:
Web Service URL: The Simple Direct Link
Every query you build in Visual Crossing can be represented as a unique Web Service URL. You can paste this link directly into Excel’s WEBSERVICE function. You can learn more here: https://www.visualcrossing.com/resources/documentation/weather-data-for-excel-and-google-sheets/how-to-use-the-microsoft-excel-webservice-function-to-retrieve-weather-data/
- Pros: Creates a dynamic link to our weather data servers. While parameterization is limited, you can use the Timeline API features such as its ability to retrieve a dynamic dates. The query can be set to a date macro like TODAY() which the server will resolve to the current date as it makes your query. Every refresh of the sheet will re-query data for “today”. Another option is to query scheduled/completed datasets (found in MyDatasets on your web account) that have a fixed URL always pointing to the latest results from today’s run. These are also a great option for single-day updates of a dataset. The benefits of the latter, as explained later, is that scheduled datasets can’t be refreshed to create repeated billing.
- Cons: Unlike Power Query, this is typically a single hard-coded query at a time which has no parameters or dynamic changes to the query.
- Best For: Users who need consistent, refreshed data pulls without manual file downloads.
Power Query: Portable Queries and Customizable Data ETL
By using the Data -> From Web menu in Excel to get started, you unlock the full power of the Power Query infrastructure found in both Excel and PowerBI. Simply paste in any valid Visual Crossing URL (easily found in Query Builder) and it will pull the results into Excel. This will set up a more complicated set of queries and connections driven by a script behind the scenes. Later you can learn to customize this script, create ETL rules and much more. This system allows you to treat weather data as a live feed that you can filter, reshape, and automate. You can try a basic tutorial and pull data in a matter of a few seconds by just cutting and pasting:
- Pros: 100% flexible and simple to start. You can later choose to parameterize the URL query, meaning your query can read values from other cells to build custom dynamic queries. You can utilize other cells in your sheets to feed in parameters such as your weather elements, locations, dates and more. With the script capability, you can fetch the weather data you want and process as you need it. Another advantage of the PowerQuery solution is that the workbooks are 100% portable. There is no requirement from your shared users other than an initial security approval to run a shared dashboard that pulls in live weather data.
- Cons: The M Language can be a challenge for advanced tasks like looping or error handling. AI coding tools can help greatly but be patient as the coding here requires some trial and error.
- Samples: We offer Sample Weather Workbooks like our Multi-Site Forecast Workbook. This allows you to monitor thousands of sites at once for custom risk thresholds. Many customers use these workbooks to manage their construction sites or ship weather-sensitive products to worldwide addresses. Here is a link to our standard sample workbook, but you can reach out to support at www.visualcrossing.com/support for the latest workbook:
The Excel Add-In: A Familiar Formula-Based Solution
Our Add-in found in the Microsoft Store, allows you to use weather queries just like any other Excel function. All of the documentation you need is included in the Add-in. Once installed, just start typing formulas just as you do with thousands of other built-in Excel functions. Auto-complete parameters with direct text strings or cell references and the system will pull in weather data from our servers. You can start with the add-in or review the tutorial here:
Key API Calls:
- VISUALCROSSING.GETWEATHER: Inserts a range of data for one or more locations and dates.
- VISUALCROSSING.GETWEATHERVALUE: Retrieves a single specific value (like temperature or humidity) for a specific location and time—perfect for use directly within existing formulas.
- VISUALCROSSING.RETRIEVESTOREDDATASET: Accesses data from your previously stored datasets in the Visual Crossing platform.
- Pros: Works out of the box for any user; supports autocomplete and “drag-to-copy” cell functionality.
- Cons: Must be installed in every Excel app, which can make it harder to distribute workbooks to external partners. If your admin can offer a corporate installation of the weather add-in all users can access it easily.
Use Cases
Everyone uses Excel differently, and many organizations have pre-built systems that need a specific type of integration. Here is how our customers typically match their needs to our tools:
1. The One-Time or Occasional Analysis
If you are an analyst who needs to pull a single dataset for a specific project:
- The Manual Download: Build your query in the Query Builder and download a CSV file for immediate analysis.
- The Power Query Path: Copy the API Web URL from the Query Builder and use the Data -> From Web functionality to pull the data directly into your workbook for easier refreshing later. This could also be the beginning of a script that you can slowly build upon later. Perhaps if you have changing locations you can edit the Power Query to read locations from cells within your Excel workbook.
- The Add-in: Install the add-in and fill out the GETWEATHER() formula using auto complete to pull in a full range of data into your workbook sheet.
2. The Regular Daily Update
For users who need to update a daily dataset without re-constructing a new query:
- Automated Scheduling: Use the Scheduler to run your dataset at the same time every day. You can reference a permalink found in ‘MyDatsets’ on your web account that keeps same reference ID, even as the data inside the result updates. Remember that time macros are very helpful for these queries. If you are simply running a default forecast it will default to ‘today’ for you. All of our methods for retrieval can get these datasets for you. You can manually update locations and query parameters anytime in the ‘MyDatasets’ page for the scheduled query that you built.
- Live Daily Queries: For more advanced needs, use Power Query OR the Add-in which can update your workbook anytime you ask for a refresh of the data. Be careful with frequent refreshes as they may hurt your budget if you are not a large data plan like Pro or Corporate. Even though you are returning the same data, a refresh is still a live query to the server. This is why scheduled datasets are often preferred for single day updates. This dynamic option is more ideal for queries that are changed by multiple users or multiple times a day so as they add flexibility to the user.
3. The “True Data Join”
When you need to merge weather data dynamically into an existing table of business data:
- Query then Merge: Build a Power Query script to pull a full dataset into your workbook, then use Excel references to link weather variables to your specific location and time rows. The data table you pull can be scheduled or live queries but it is written to separate location and used for table references into cells. Can be useful but refresh scenarios may not look live and require more intelligence to update the cells of your joined data set.
- Formula-Based Join: Use the Excel Add-In to write a formula for every cell or area that needs weather data. You can reference the location and date from other cells in your table, allowing the weather data to “follow” your existing data perfectly. The Add-in can also pull full ranges of data similar to Power Query solutions. It is ideal for Excel users who prefer formula based solutions and just want the data to insert itself right from the query.
4. Dynamic Query Parameters
This is a favorite for when you need to know frequently changing weather like the 15-day forecast for dynamically changing locations or other parameters.
- Custom Excel Apps: Build a custom application within Excel using Power Query or the Add-In to retrieve data for hundreds of risk locations found in your workbook. Reference the locations data and use it to populate the query as a parameter.
- Multi-Site Monitoring Sample: Utilize our Multi-Site Weather Workbook. Simply paste in your list of locations, and the workbook will automatically generate a color-coded dashboard showing you exactly where weather thresholds are being hit over the next 15 days.
Cost & Efficiency Considerations
To manage your account effectively, consider how often your data refreshes. Every live query incurs a record cost based on the size of the data returned. Here are two options to save money:
Set a Strict Refresh Policy: In Excel, ensure your background refreshes are set strictly to only what you need.
Use Stored Datasets: By using the Stored Dataset API or Scheduler, you can pre-run your data. Because the URL link remains fixed, you can retrieve that data thousands of times across an entire enterprise without additional billing. It is simply downloading the dataset rather than executing a query each time. This is the most efficient way to share a single “source of truth” across a mass deployment of spreadsheets.
Start Today
Whether you are a data analyst performing a one-time study or an operations manager building a global risk-monitoring system, Visual Crossing provides the right “bridge” to get weather data into Excel on your terms. From the simplicity of a manually downloaded CSV to the automated power of our Excel Add-In and Power Query workbooks, you have the tools to turn raw atmospheric data into actionable business intelligence. Choose the method that fits your current workflow today, and rest assured that as your data needs grow, our flexible platform will be ready to scale right along with you.
Sign up for the free trial and you can start with any of the methods we outline and you can decide what is best for your solution.