Payroll Helper for Middlebury Interactive Languages

  

Stephen Godfrey

IS 520 – Spreadsheet Automation

December 10th, 2013

Executive Summary

Company Profile

Middlebury Interactive Languages (MIL) is a company that creates software that helps children to learn foreign languages. They employ roughly 150 people and have two offices, one in Middlebury Vermont and another in Provo, Utah. Vermont houses their executive team and their client services team. Provo on the other hand houses mainly technical staff such as IT, Development, Course Content, and Professional Services.

Recently they created a tool (written in Ruby) that can connect to multiple learning management systems. Some of these systems are Blackboard, Canvas, Desire to Learn, Brainhoney, and Moodle. Yet they still have a home-built learning platform (written in PHP) which some of their older customers still use.

MIL offers courses in multiple languages, but the most notable are French, Mandarin, Spanish, German, and Latin. Every language has specific courses built for students in Elementary School, Middle School, High School, and Advanced Placement.

Since they connect to multiple systems, they also need to obtain reports and data from those systems. And that is how I became employed at MIL as a Business Intelligence Developer. My primary task is to take data from multiple sources, and warehouse that data into a central location. They also need information gathered to process their payroll, and that process is quite technical. Yet I realized that part of it could be automated.

System Overview

We needed our enrollment data from Blackboard, but they would only give us an Excel file with the data. We repeatedly asked for direct database access or access through an Application Programming Interface, but they would not yield. So we came up with an alternative solution.

They would build a report that matches our database structure, and I would fill in the rest of the cells, and import the modified spreadsheet into our database. The most challenging task was matching the teacher names in our list to theirs. I had to add the teacher names and ids to a second spreadsheet and do a vlookup for the teacher name, and add the teacher’s id for the given enrollment. But the process was tedious, and so I decided to automate it.

I created a payroll helper that makes the process easy. When you simply open the workbook and the form automatically appears in the center of the screen. Then there are command buttons in the middle column that complete various tasks. Lastly, the ‘Output CSV’ file will create a csv from the data in the workbook, and save it in the same folder that input file is located. That csv will then be reviewed for any inconsistencies and imported into the teacher payroll database.