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
Skill Level Intermediate
- [Voiceover] Hi, I'm Chris Dutton, and welcome to Excel workshop, working with real-time data. This course is designed to showcase a number of core analytics principles and techniques, including joining data sets, integrating real-time information, and creating clear and intuitive dashboards. I'll show you how to combine text functions and conditional statements to create new fields, how to use look up, index, and match to join data for deeper analysis, and how to apply powerful functions to pull real-time data directly into Excel through an API.
I'll demonstrate how you can use combo charts and basic trigonometry functions to create a custom gauge and needle effect. And finally wrap things up with some tips for applying what you've learned to take your analytic skills to the next level. Ready to go? Let's get started with Excel workshop, working with real-time data.