Extract individual nodes, or components, from an XML string using Excel’s FILTERXML function. Use basic XPath syntax to isolate and compare real-time elements from Weather Underground’s API feed, including temperature, precipitation, wind speed, and location. Understand the importance of using FILTERXML to process and organize XML data for analysis in Excel.
- [Voiceover] The FILTERXML function allows you to…pull specific components from an XML string…using what's called XPath Syntax.…In this case I'll demonstrate how to extract fields…from the weather underground API feed access…through a web service function in Excel.…Open up the 0502 exercise file, and now you may get…a security warning telling you that your…web service function has been disabled.…Go ahead and click the enable button to make sure…that it functions properly.…
What I have here is a weather feed, in cell C4,…which is a long XML string that comes from a web service…function, and what this is showing me is current…conditions in the ZIP code in cell C2.…I'll actually go ahead and change this ZIP code…to 02138, which is Cambridge, Massachusetts, since that's…where the data that I have in front of me came from.…Now in cell E2, I'm just gonna type, current conditions,…and beneath this I'm gonna list out a bunch of individual…conditions or individual pieces from that XML string…that I’d like to extract and highlight.…
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