You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
This post on exploring Webservice functions is brought to you by Lee Bizek, a Program Manager in the Excel Team.
Have you ever wanted to incorporate data from online resources into your Excel spreadsheets, such as stock quotes, weather, Bing Search results or even Twitter feeds? With new Webservice functions, now you can.
I used anonymous editing in Web Excel to collaborate with a group to coordinate a 20-person camping trip that required some people to sleep outdoors in tents. Being aware of online weather updates helped us to prepare for the elements and also predict high and low tides because we wanted to go crabbing as part of the adventure.
With Excel 2013 for the desktop, we pulled the latest weather and tide information from the internet into Excel using the =WEBSERVICE(url) function--and the best part is the information updates automatically!
To learn how to use the Webservice function, we'll do 2 things:
First, find a web service. For this example with weather updates, go to http://www.wunderground.com/weather/api to create your free account. Complete the form, then click Signup for API Key.
To set up your API Key, follow these steps:
Now that you have a unique API Key, open your Excel spreadsheet and follow these steps to create the =WEBSERVICE(url) function for the current weather conditions:
You can also use cell references in the Webservice function to update URL parameters, such as your zip code. Here is how to set it up:
Now that we have the information from the web service in the Excel spreadsheet, we need to extract the pieces of data we want out of the XML, including the name of the city and current temperature and current weather conditions. To extract the data, follow these steps:
1. In cell B8, enter the =FILTERXML(B5,"//full") function. This will give you the city name associated with the zip code.
2. In cell C8, enter =FILTERXML(B5, "//temp_f") to extract the current temperature in Fahrenheit.
3. In cell D8, enter =FILTERXML(B5, "//weather") to see the current weather condition, such as Light Rain.
With the online weather updates, now our camping trip planning collaboration spreadsheet looks like this:
Please note that WEBSERVICE Functions are "non-volatile", which means they refresh only when:
Remember that you can use this functionality for many different web services over the internet that you can then analyze using Excel.
-- Lee Bizek, Excel Program Manager
Learn more about the WEBSERVICE function
Learn more about the FILTERXML function
Learn more about XPATH
Spring edutech with presence of more than 3 years in industry provides MS Excel training from basic to advanced level. We provide training to individual and corporate training. The MS Excel course is designed keeping in mind the requirement of novice and professionals.
<a href="http://www.vaayaaedu.com">Microsoft Excel Training In Ahmedabad</a>
Thank you so much for an excellent sample of the new excel functions
Thank you for the post.
We use Axapta business module at work, is there any way I can update my daily excel sheet automatically using this method? I do know Axapata is a program not a webprogram ... just curious if there is a way.
Good. That's a step forward for Excel. Now you only need to do two things: 1) make it much simpler to reference a webservice and get a result, and 2) make it more attractive for developers to publish their webservices by allowing them to earn money upon the usage quantities of their webserivces. I liked the APIKey functionality here, it'd allow for charging the right customer for usage. It should be as simple as =WEBSERVICE(APIKey;WebServicePath;[Parameters]). You should standardize outputs so that WEBFILTER wouldn't be needed at all.