These are projects posted by the students of Dr. Gove Allen at Brigham Young University. These students have taken one semester-long course on VBA and generally have had no prior programming experience

Thursday, December 12, 2013

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.