The Microsoft Excel WEBSERVICE function retrieves data from a URL into a single cell within an Excel worksheet. In this article we describe how to use WEBSERVICE to retrieve weather forecast and historical weather data directly into Excel without the need for macros or scripts. However, is it always the best way to work with weather data web services in Excel? Read on to find out!
How does the Excel WEBSERVICE function work?
The WEBSERVICE function is very easy to construct:
=WEBSERVICE(url)
Given a URL, the WEBSERVICE function will retrieve the data for the URL and populate that data into the Excel cell as piece of text. From the Microsoft documentation, a fictitious example would be as follows
=WEBSERVICE(“http://mywebservice.com/serviceEndpoint?searchString=Excel”)
Once the function executes, the data in the cell is available for normal text manipulation using Excel functions.
How to use the Excel WEBSERVICE to retrieve weather data
To retrieve weather data, we first need a web service to retrieve the weather data. We will be using the Timeline Weather API as it includes weather forecast and historical weather data. The Weather API also provides a number of options to manipulate and filter the results which makes it easy to show using the WEBSERVICE function.
If you want to follow along, you can sign up for a free account that will allow you to retrieve 1000 free weather data results every day.
Here’s a simple URL web service the retrieves the weather forecast for the next 15 days:
https://weather.visualcrossing.com/VisualCrossingWebServices/rest/services/timeline/New%20York%20City%2CNY?unitGroup=us&contentType=csv&key=YOUR_API_KEY
Note the ‘contentType=csv’ parameter – this tells the web service to return the weather data as comma separated values rather than the default JSON format.
If we put this URL in the browser, we can see that we have a table of weather forecast data:
We can put the data into our Excel worksheet using the WEBSERVICE function:
=WEBSERVICE("https://weather.visualcrossing.com/VisualCrossingWebServices/rest/services/timeline/New%20York%20City%2CNY?unitGroup=us&include=days&contentType=csv&key=YOUR_API_KEY")
All this data gets put into Excel as a single cell so the results are a little tricky to read:
This is because the data is large and the CSV formatting isn’t automatically handled by Excel.
How to use the WEBSERVICE function results
As we’ve seen, the WEBSERVICE function can easily retrieve weather data from a Weather API URL. So far the results have been a little hard to understand so now we’ll turn the raw data into something useful.
There are two approaches to this simplification. First we will reduce the amount of information returned from web service call itself. Reducing the amount of data will make our processing easier, faster and will avoid any risk of hitting the Excel cell limit (approximately 32000 characters).
The following request uses the ‘elements’ parameter of the Weather API to reduce the data from the request to the date, maximum temperature and minimum temperature:
https://weather.visualcrossing.com/VisualCrossingWebServices/rest/services/timeline/New%20York%20City%2CNY?unitGroup=us&include=days&contentType=csv&key=YOUR_API_KEY&elements=datetime,tempmax,tempmin
We can make things even easier by eliminating the headers and targeting the high temperature for a single day:
https://weather.visualcrossing.com/VisualCrossingWebServices/rest/services/timeline/New%20York%20City%2CNY/2021-05-31?unitGroup=us&include=days&contentType=csv&key=YOUR_API_KEY&elements=tempmax&options=noheaders
Note how we added the date parameter of 31st May 2021. If this is in the future, the query will look up the forecasted high temperature for that date. If this is the past, then the result will be the actual observed high temperature for that date.
This request doesn’t need any kind of processing. It is simply the high temperature for a single date. We can use multiple cells and string concatenation to create a simple weather forecast.
Given a column which includes the date, the request to look up the high temperature for a high temperature is constructed using concatenation:
=CONCAT("https://weather.visualcrossing.com/VisualCrossingWebServices/rest/services/timeline/New%20York%20City%2CNY/",TEXT(A5,"yyyy-mm-dd"),"?unitGroup=us&include=days&contentType=csv&key=YOUR_API_KEY&elements=tempmax&options=noheaders")
Note how the date of the URL is dynamic inserted by formatting the date value found in cell A5
We can surround this with the webservice call to retrieve the data:
=WEBSERVICE(CONCAT("https://weather.visualcrossing.com/VisualCrossingWebServices/rest/services/timeline/New%20York%20City%2CNY/",TEXT(A5,"yyyy-mm-dd"),"?unitGroup=us&include=days&contentType=csv&key=YOUR_API_KEY &elements=tempmax&options=noheaders"))
If we use a table of dates, we can then create a small weather forecast table in Excel:
How to process more complex WEBSERVICE results
In the above example, we generated a simple weather forecast table using multiple functions. We were requesting the weather data using multiple WESERVICE calls – one for each cell. Is there way to request the data just once and the use Excel to process the data?
There are three ways to parse complex result of a web service in Excel:
- Use text manipulation functions of Excel to parse the result of the Web Service call directly within the worksheet.
- Use VBA macros to parse the result.
- Use Web Query connector (which in turn uses PowerQuery to load and parse the data)
Using text manipulation functions of Excel
In the above example, we used a separate WEBSERVICE function to retrieve the data for the high and low temperatures. Let’s see if we can combine these into one:
The web service call is:
=WEBSERVICE(CONCAT("https://weather.visualcrossing.com/VisualCrossingWebServices/rest/services/timeline/New%20York%20City%2CNY/",TEXT(A5,"yyyy-mm-dd"),"?unitGroup=us&include=days&contentType=csv&key=YOUR_API_KEY&elements=tempmax,tempmin&options=noheaders"))
We now have an cell with a concatenated value resembling “70,58” representing 70 for the forecast high temperature and 58 for the forecast low temperature. To isolate the high and low temperatures we can use a little formula to split the text around the “,” character.
Let’s assume that the above WEBSERVICE is placed in cell B12. We can then use Excel text functions to split up the value of cell B12 .
The left hand side is the high temperature:
=LEFT(B12,FIND(",",B12)-1)
This says “extract the left characters of cell B12 up to, but not including, the first “,” character (the -1 will exclude the “,” character).
The right hand side is the low temperature:
=RIGHT(B12,LEN(B12) - FIND(",",B12))
This says “extract the right characters after the “,”. As the FIND function starts from the left we take the overall length and subtract the result of the FIND to find the start of the low temperature.
Using VBA macros to parse the query results
Using Excel functions to parse the result of the WEBSERVICE function can get a little difficult. Excel has additional tools to manipulated CSV and JSON data. The first is to write a custom VBA script to parse the incoming data.
Web Query Connector & PowerQuery
The next option is to use a ‘Get & Transform Data’ request from the Excel Data menu. Get & Transform Data requests construct a PowerQuery script to retrieve and then transform the data.
PowerQuery is a scripting language of sorts that is built into Excel which includes rich ability to import and manipulate data from web services before populating the result data into an Excel sheet.
The learning curve to using PowerQuery query can be quite steep however importing well defined CSV data such as the Weather API data described here is a simple case. If you would like more information on how to load weather data into Excel using PowerQuery, please see our additional articles.
To WEBSERVICE or PowerQuery?
The Excel WEBSERVICE function is an easy-to-use way to retrieve weather data. This makes the function the easiest way to retrieve simple weather data values into Excel from a Weather API. You can retrieve the high temperature for a day or hour in a single, simple Excel formula.
However WEBSERVICE does not include any way to process the weather data results. If you need to retrieve a long, multiple column weather data result in JSON or CSV format, WEBSERVICE may not the right choice for you. In those cases, PowerQuery is exactly designed for your needs.
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.