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 28, 2015

Scraping Census Data

My project is a web scraping tool useful in gathering and aggregating demographic information. This useful information is left of separate pages for each city, county, and state with no simple way of collecting the information for thousands of cities. Additionally each Metropolitan Statistical Area (MSA) is an important geographic area in the real estate industry, but there is no information about each collective MSA. My project gathers all these points, and can also aggregate the information by MSA.

Saturday, April 18, 2015

Conversation Theory - Improving Knowledge Structures

The central purpose of this application is prototype an interface to build a dynamic model of knowledge of any topic.  Traditional architectures of intelligent tutoring systems employ a model of student knowledge and a model of expert knowledge.  The difference between these models is what the student has yet to learn.  Since the 1970s, these models have become more and more complex resulting in a wide range of research related to how to improve the student model and the expert model, and how to compare them for differences.  Artificial Intelligence has a strong hold in this arena because of the similarities between model-generation and the definition of artificial intelligence espoused by scientists for decades.
The purpose of this project within this context is to take a small step toward applying technology to improve teaching and learning.  Specifically, this project takes the ideas from Conversation Theory and applies them to the design and development of an interface to build an entailment mesh.  An entailment mesh is an instructional design mechanism for instructors, instructional designers, and educational technology developers.  It is a way to organize knowledge that is different from current knowledge representation systems.  This application helps the user participate in a knowledge generating activity either by oneself or with other users.  Instructors, students, instructional designers, and educational technology developers will all find value through this tool.  Instructors will find benefit in TW by building a more coherent (tight) curriculum including aligning the curriculum with course objectives and skills.  Students will find value in TW by challenging what they think they know on a topic and learning from themselves and others what they did not know they did not know.  Instructional designers can find value in TW by comparing the knowledge structures they have embedded in instructional products and services with the dynamic knowledge structures afford by TW.  Finally, educational technology developers will discover value in TW by learning how dynamic knowledge structures will lead to improving the design of adaptive learning technologies.

Friday, April 17, 2015

Automating Circulation Data Records


I have been a student worker in the Harold B. Lee Library’s Office of Digital Content Management for close to three years. Among other things, my office is in charge of generating and recording the library’s circulation reports. Circulation data is collected as follows: At the beginning of each month, a text file is generated using a program called WorkFlows, which, among other things, stores and records information about library circulation. This text file shows 1) the number of items checked out, 2) the number of individual patrons who check out items, and 3) the ratio of items checked out per patron for each hour of each day that the library’s circulation desks are in operation during the last full month. The output from these text files is then read into an Excel spreadsheet, where it can be used for analysis. Up until recently, data from these text files was entered manually into the spreadsheet. For my project, I made the decision to write a program which would automate this process, saving on average half an hour to 45 minutes of worktime.

The program that I have written includes a File Dialog, which allows the user updating the spreadsheet to open the text file containing the data for the month that they wish to import into the spreadsheet. The program then uses arrays to find and paste in the data, as well as the date heading for the data. After the program has run, the user is asked whether they would like to import more text files containing circulation into the spreadsheet, continuing in a loop if the user selects the “Yes” option and exiting the program if the user selects the “No” option.


Thursday, April 16, 2015

What am I Eating?

Eating healthy has been a struggle for many people nationwide. We always think we should be eating healthier, and we make New Year’s resolutions to do so, but usually no changes are actually made to our diets. We at Health-Tracker are committed to helping America improve their diet through our eating programs.

Health-Tracker is a non-profit organization that offers programs including meal plans, health evaluation, personal trainers, and much more in order to help people improve their health. In our work to help people improve their health we have found that the most important step to any successful diet is to track the foods you eat. The tracking process helps us see clearly what we eat, how much we eat, trends in diets, and the nutritional value of what we eat. This is essential to identify how your diet is lacking, and to keep you accountable to the goals you have set. With a clear record of what you eat, you can’t “forget” about the cake you ate, and you can clearly see just how few vegetables you are probably eating.

The Health-Tracker food tracking program will help you to quickly and easily create a log of the foods you eat and their nutritional value. To begin using our program you input your profile including your height, weight, age, and gender. The system will then pull your nutritional needs from the internet to give you an estimate of what you need in your diet including calories, amount of fat, amount of carbs, amount of protein, vitamin a, vitamin c, vitamin d, calcium, iron, and fiber. Next, foods are entered to the Food Log sheet of the program. On the food log you open an internet browser to get URLs for the foods you ate so the program can get the nutritional information from the internet and input it to the log. As you enter the foods you eat into the food log our program will track this nutrition info to give you continuous updates on how you are doing to meet your nutrition goals. You can see all-time, weekly, and monthly reports to show your results over time and to help identify trends.

By tracking the foods you eat, you will be able to get a clear picture of your current nutrition needs. By taking a little time to input the foods you eat you will get a useful report to show which nutrients you are lacking, how many calories you are eating in a day, and how you can improve. We at Health-Tracker hope this information will be useful to help bring in a healthier, happier America.

Wednesday, April 15, 2015

Elder’s Quorum Contact tools / HT Reporting Tool

In my ward’s Elder’s Quorum we use a Google Form to collect home teaching reports. However, the task of organizing the results from the form to a simple percentage is a manual task. For this reason I have built a tool whose purpose is to gather the data from the Google Form and automatically compile it into a report on home teaching (a final percentage). In addition to this several other functions were built into the program. First, the spreadsheet stores the names, emails, and phone numbers of the quorum members. With the click of a button I can send a mass-text or a mass-email to all the elders. Second, once the numbers are compiled an email can be sent to the need-to-know parties.

Database Consolidation Report Generator

American Greetings Corporation, LLC is the world’s largest greeting card company. Based in Brooklyn, Ohio, a suburb of Cleveland, the company sells paper greeting cards, electronic greeting cards, party products, and electronic expressive content. (Wiki) I will be interning with American Greetings (AG) this summer and got a jump start on some potential projects for this summer.
When there is an issue at one of 90,000 locations that carry AG’s products, management has to manually pull data from multiple databases to compile a snapshot of that stores current situation. My project automates the process of gathering all necessary information into an easy to understand dashboard. The data in this project has been falsified for the protection of AG. The user simply enters the chain number and store number for the store of interest then at the click of a button, my macro reaches out to the necessary databases (dummy databases on were used in place of AG’s actual data), establishes a connection, then gives the user options for which data to include in the dashboard. The user can choose to get a store summary, order tables, recent shipping info, whether or not to generate a PDF report, and to send an email with the report. Following the users selection, the macro takes over to generate the dashboard and report if chosen. The user will input a name for the report as well as email credentials and info.

This project automates the lengthy data gathering process freeing up managements time to look over the data and make meaningful decisions to correct issues faster. The simplicity of the macro enables lower level employees to easily generate a report and automatically send to their superior for valuable analysis. 


Social Media Auto Grader

My project is designed to automate the grading process for a MBA 693R class for which I was enrolled in this semester. Currently there are weekly assignments for each student to do the following:

·      Tweet 15 times
·      Follow 10 new people
·      Write a blog post
·      Comment on 3 class members blog posts

Each week the TA must examine each of the students’ social media sites to determine if they met the requirements for the assignments. With one button click, my program executes web queries using the Twitter API and html scraping to find the relevant information and makes a report sheet summarizing the fulfillment of the assignments per student.

What's In My Kitchen?


I often forget which food I have in my refrigerator and pantry. I sometimes will forget for long enough that some foods will expire and spoil. Other times, I will run out of something and forget about it. Then I return home, only to discover that I need that one item that I ran out of a couple of weeks ago and had since forgotten about it. This problem extends to food storage. As a student, I don’t have much food storage. However, my parents do have a food storage and while I was growing up I was able to see the pain point of not remembering everything that is in food storage, or if it may expire soon. What’s In My Kitchen will track all food items, including their quantity and expiration date, so that food is not wasted, and shopping trips are efficient. There is another feature included within this program that will allow the management of recipes. I can create a recipe, complete with the instructions and the foods required. The foods required can be selected from a list of foods that have been added to the Inventory spreadsheet database. Both the recipe and items lists can be printed out with the click of a button. This is a program that can eventually be ported to a mobile application, and it is targeted towards anyone with a kitchen with a stronger focus on stay-at-home moms.

University Writing Email Generator

Executive Summary
I currently work on campus as a research assistant for the BYU Writing Department. As an extension of the English department, our focus is to help teachers across all curriculums learn how to improve their students’ writing, and how to be a better writing teacher. Our department hosts several luncheons and clinics that various professors across campus choose to participate in. Over the years, University Writing has composed a matrix of teacher names, department information, and email addresses of those teachers who have previously expressed interested in being a part of the University Writing curriculum. As a department, we send out multiple emails every week to different departments about different events that we are hosting. There are often errors produced in these emails with incorrect dates and times of events, or they are sent to the wrong people. I created a system that takes the list and cleans up the data, and then I also created a form that the department can use to send mass emails to people in different departments to ensure that all of the event information is entered correctly.  
*Note: The code only works for Outlook, and all email address have been changed for confidentiality. 

Automating Technology Transfer Processes

The Technology Transfer office focuses on taking professors’ disclosed inventions and patenting them in the U.S. and other countries. Furthermore, once the process has begun to patent these technologies, the Technology Transfer Office seeks out companies who would be interested in licensing the technology, and enters into license agreements with these companies.

Because of the nature of the work (patenting), the Technology Transfer does a lot of work with law firms outside of BYU. Currently, the process to approve and pay the law firms’ invoices, as well as subsequently billing the licensees to reimburse for the invoices paid is an arduous, manual task. This VBA project eliminates most of the manual effort (excluding data entry) involved in this accounts payable/receivable process by downloading the necessary tables of information from an access database, creating approval summaries for the director and his associates, creating summaries pages for each law firm that is getting paid, and finally creating an invoice for each licensee that has licensed a technology that was paid on.

Portfolio Tracker

This project allows you to track your exchange traded securities. Allows buying and selling, and will update with current prices, character and amount of gain / loss, and portfolio allocation percentages.


Battle Ship

Executive Summary
This is a fully functioning human vs computer version of the popular game battleship. The computer has been programmed to guess intelligently based on previous successful hits. The program recognizes when ships are sunk and declares a winner win a player has sunk all of the opponent’s ships.


Tuesday, April 14, 2015

Ethan Kitzmiller VBA Project

Executive summary:

Bluehost is an internets hosting business that specializes in helping customers have their own website. As a data analyst for Blue Host, I have to pull various amounts of employee performance data, on a daily basis. This often takes about an hour to pull data for any given project. The task was to automate the data collection process in order to reduce time and eliminate human errors. This project will save an estimate 1 hour of time every business day.

Richins Insurance

Executive Summary:

My Dad is a life insurance agent. He sells to an Indian tribe in Roosevelt, Utah. Since most of the work he does is through pen and paper, I have decided to write an excel program that helps him in his process.
            The process he goes through is as follows: My dad receives a list of clients from the Indian tribe, describing every customer, their policy type, as well as any outstanding loans they have with my dad’s loan agency (these come in PDF format). He then has to go, by hand, and manually ‘balance’ the statement from the tribes with the statements he receives from the companies for which he sells (these come in different formats, some in PDF, some excel, others word docs). This is time-consuming in and of itself, but since there are many children who have parents who are paying for their policy this gets confusing, considering the insurance companies send a list of individuals, while the tribe sends the total for each family.

            I wrote a program that pulls all the information received from the tribe and the insurance companies and automates the balancing process.

Blog Archive