Case Studies - Excel and Automate

Excel User Defined Functions (UDFs)

What do you do when you have data, but it's not in the format you want or it's missing information?

While Excel has a ton of built-in functions like Sum, Average, VLOOKUP, there are times when they aren't enough to solve your particular problem.

Before you give up on your project or resort to manually calculating each data point, there are ways to solve your challenge with some advanced Excel features. For example, user defined functions (UDFs), which act like built-in functions, can be customized to perform a particular action. In the example to the right, we created a UDF called "ConvertName()" that takes names in different formats and automatically outputs the first and last name. This UDF can be applied to any list of improperly formatted names.

While this is a simple example of a customized formula we can create for you, UDFs are a very powerful feature of Excel that can solve your data challenges quickly and accurately.

Python - The Power of Custom Programming

You have a website, but how do you determine whether it's attracting your target audience? Specifically, how do you know from where and how often people are viewing your site? The server hosting your website stores visitor data in a log file, but it is nearly impossible to use the file, as is, to derive any useful information.

There are software packages that analyze website log files, but they don't always answer your questions. With a programming language like Python you can extract, transform, and present the data exactly how you need it.

This challenge can be solved in three steps.

We first code a "regular expression" to parse all the data from the website log files. With that, we can then extract key information like IP address, referring website, and what pages they viewed.
The next step is to query a website that provides geolocation data for IP addresses so we can determine the city and state of our users.
Finally, we pull all the data together and analyze it using Pandas, a Python library focused on data analysis.

With a click of a button, we can now visualize the origin of our visitors, the frequency of their visits, and the date of their last visit. This just scratches the surface of what is possible with Python, and how we can slice and dice data to extract the information you need to make important business decisions.

Excel Macros

Do you waste time on repetitive tasks like formatting, manipulating, and calculating data? For this project, the client was manually calculating the cost of an injury over a person's lifetime and re-formatting the output each time.

While Excel templates with formulas can be useful, this particular challenge needed something that was much more flexible and customizable. In particular, it needed to handle variable cost inputs and be used for different clients.

Excel macros are written in VBA code that can automate a manual sequence of steps and output the data exactly how we want it. In this example, we created an input sheet to plug in the variable cost factors. We then wrote a macro to perform the manual calculations and output the customized analysis sheet. By simply clicking a "run" button, the analysis was complete in seconds. To enhance the solution, we added error checks to make sure the input data was correct and formatted properly. As in this example, Excel macros can save you time and headaches by automating common, repetitive tasks.