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

Tuesday, April 15, 2014

Hockey Statistic Data Collection Program

For the past eight months I have been interning with High Performance Hockey, a local hockey data analytics company. In my role as an intern I am responsible for aggregating all of the data collected by the data collection volunteers. This year High Performance Hockey collected all of the statistics for the BYU Men’s Hockey Team. At the start of my internship I created a series of excel workbooks that served as templates for collecting the various statistics. After I input serval thousands of these records by hand, I finally decided that there should be a much simpler way to collect the data. The goal of this project was to create a data collection system consolidated on to one workbook. I wanted this system to make it easy for anyone volunteering to collect data for the team. I also wanted the workbook to be able to sync to a centralized database, so that I never had to worry about consolidating several different excel files again.

My final project features several user forms that allow collectors to collect data in real time if they had their laptop or access to excel on a tablet at a hockey game. My project is adaptable on a game by game basis. Changes to the rosters of either team and the game schedule are reflected in the user forms. There are several different buttons in the “Data Collection” group on the ribbon, each opens a separate user form customized to collect data for that particular statistic. Whenever these forms are opened they create a new worksheet to hold the data, if that sheet does not already exist. Each form has “Previous” and “Next” buttons that allow the user to write data to the selected sheet and go back over the data if they need to correct an error in the previous entry. Once a user is done collecting data they can select the “Setup Access Connection” button to specify the location of the access database they would like to sync to. After the connection is set up they can click the “Sync with Access” button to send their data to a local access database. Before I had created this program I would have users edit a spreadsheet indicating that they collected a piece of data for a particular game. This worked with limited success, I would often find files in our shared DropBox folder that I was not aware of. For this reason I built in the option to notify the database administrator via email once data was synced to the database. The email simply notifies the administrator which tables were updated for which particular game. High Performance Hockey primarily relies on interns to collect their data, so the goal of this notification system was to make it easier to monitor if interns were collecting their assigned data in a timely manner. 

No comments:

Post a Comment

Blog Archive