Create a real-time data feed by using the WEBSERVICE function to pull real-time XML strings directly into an Excel workbook. Practice integrating real-time data by generating a Weather Underground API key and using WEBSERVICE to access and analyze current weather conditions for any particular zip code.
Excel's Webservice function, which was introduced in 2013 is a really powerful function that allows you to pull real-time XML data directly into a worksheet through an API. It's important to note that this function is only available for 2013 and 2016 versions of Excel, with the exception of 2016 for Mac. In this particular example, I'll show you how to generate a free weather underground API key and use Webservice to access current conditions for any given zip code.
So, in order to access the weather underground API, you first need an API key. So, open up a browser and head to wunderground.com/weather/api, and from here you can either sign in or choose the sign up for free option. If you enter an email, password, and handle, they'll email you a validation key and take you back to the home page, and from here you can click Explore My Options and choose a plan. So, I'd recommend selecting the Anvil Plan, which is the most comprehensive, as well as the Developer option which is free.
Once you've made those selections, go ahead and click Purchase Key and you will be emailed a personal API key. Now, once you have your key, open up the 0501 Excel file, and head to the Weather Dashboard tab. I'll insert several rows above of row 1. So, I'll just right click and then press F4 to repeat the action, and enter some placeholders here. For Zip Code in cell B2, API Key in B3, and Weather Feed in B4.
So, for Zip Code, I'm gonna default to the Cambridge, Massachusetts zip code, since that's where the data that I'm working with came from. So 02138, and you'll notice that Excel stripped out that leading zero, because it defaulted to formatting the cell as a value. I can right click, choose Format Cells, and change that to text which will allow me to go ahead and add that leading zero back in. And now, it's formatted as a text string.
Cell C3 is where you can go ahead and enter the API key that you just generated. And then, once you've done that, we just need one additional piece of information. So, we'll open back up the browser, head to wunderground.com/weather/API once again, and choose the documentation tab. If you scroll down here, you'll see a URL string, which will return current conditions in a US city. So, we're gonna press Control + C, go ahead and copy that, then head back to Excel and C4 is where you'll type in that Webservice function.
So, go ahead and type =Webservice. The only input to this function is the URL. So, you can press Control + V to paste that URL, close the parenthesis and surround this entire string in quotes. Now, you'll need to make some customizations here to make this work exactly how you need it to. So, the first thing that you'll do is delete the Your Key string, close the quotation mark, add an ampersand, and this is where you'll dynamically pull in the value of your API key stored in cell C3.
So, press F4 to fix that reference, add another ampersand, and open the quotes back up. The second piece that I need to customize is where it says CA San Francisco. So, I don't always wanna return San Francisco conditions. I want to return the conditions for whatever zip code I've entered into cell C2. So, after the q slash, I'm gonna close the quotation mark, add an ampersand, link to cell C2, and press F4 to lock it in, add another ampersand, open the quotes back up, and last but not least, just change this from json to xml.
Now, when I hit enter, you'll see this long xml string with all sorts of conditions specific to Cambridge, Massachusetts, which is the zip code that I've selected. You can test this out by changing this to another zip code, 90210, and when you scroll back to the right, you'll see that this is now showing conditions for Beverly Hills, California. So, this example uses weather data in particular, but the Webservice function can be used for any real-time data accessible through an API.
So, for instance, you can use it to pull things like real-time stock prices or market data, Google maps or Google directions, even real-time Twitter feeds. So, the key takeaway here is to use Webservice to pull real-time XML strings from an API directly into Excel.
Using conditional statements and text functions like LEFT, MID, RIGHT, and CONCATENATE, you'll learn to standardize data sets from the NCDC and create new dimensions. Then find out how to use VLOOKUP to join the data, and use COUNTIF statements to analyze trends. Once the historical data is in place, Chris shows how to use Excel's powerful WEBSERVICE and FILTERXML functions to tap directly into Weather Underground's API, which provides real-time weather information for any location and allows you to estimate accident rates based on current conditions.
Last but not least, you'll learn how to build custom charts to show the expected accident risk based on real-time inputs, and walk through some additional opportunities for deeper analysis.
- Standardizing data
- Creating new data dimensions fields
- Joining data
- Analyzing trends in the data with COUNTIF
- Creating an API feed with WEBSERVICE
- Extracting components with FILTERXML
- Visualizing the findings in a custom combo chart
- Applying your new skill set