Data Input and Sort

Project by Joshua Kirn

My final project works with the user to quickly import, sort, and format .txt files. This was designed to simplify the work that I did during my internship at KPMG last summer and will continue to do when I return there for work after graduation. The example files that I have generated to accompany my project would probably take about an hour to import, sort, and format, and represent only a small portion of what I could expect to see on a given day. This program completes these mundane tasks with precision in a matter of seconds.

When I made my final project proposal, Professor Allen said he couldn’t quite understand what I intended for my code to do. My work is somewhat unusual, and I think understanding my final project will be easier with an understanding of what I do for work.

As mentioned above, I work for KPMG’s advisory practice in their Contract Compliance Services. We audit Software End-User License Agreements for our clients. For this, we receive information from our clients (a software company) about their clients (our “projects”) that we will be reviewing. Our client gives us data about what software the project has purchased, what type of license they have for that software, and how many units of software they have purchased. We then have other KPMG associates or IT professionals from the client or the project conduct reviews of the information systems at each of the project’s sites to collect information about the project’s actual software usage.

The reports from the client and the project are sent to me, where I have to import the data into Excel, sort it to the correct tabs, format it correctly, analyze it, and ultimately draw conclusions about how much money the project owes our client. The issue is that frequently, these jobs will involve hundreds of reports from project sites, each with anywhere from one to hundreds of records. Each time new reports are brought in, I have to redo the process, including needing insert rows, putting each bit of data into the correct cells, reformatting the sheets, and showing correct subtotals for each software group (when you replace subtotals over and over again, you realize the subtotal function is such a bigger burden than it needs to be).

The importing, sorting, and formatting of data takes a completely unreasonable amount of time relative to the amount of time that is spent actually analyzing the data and drawing conclusions. This project streamlines the importing, sorting and formatting of data to make the process much quicker, especially in regards to automatically formatting any data additions to already-in-progress files.

Files:

Write-up: 
http://files.gove.net/shares/files/14w/jkirn/Joshua_Kirn_-_Final_Project_Writeup.pdf

Excel Workbook:
http://files.gove.net/shares/files/14w/jkirn/Joshua_Kirn_-_Final_Project.xlsm

Supplementary Information:
http://files.gove.net/shares/files/14w/jkirn/Client.txt
http://files.gove.net/shares/files/14w/jkirn/Project1.txt
http://files.gove.net/shares/files/14w/jkirn/Project2.txt