University Student VBA Projects

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

Wednesday, April 20, 2016

Fantasy Baseball Simulator - Brandon Soderquist

  1. 1 Executive Summary

Fantasy baseball is extremely popular in our day. It has become a forum in which people can socialize, compete, and even gamble in some situations. However, for me it’s a great way to compete with friends while being able to enjoy the sport I love. While competing is always fun, winning is even better. My goal with this project was to be able to maximize my chances of winning in my own fantasy baseball league. This is great because it can be utilized by anyone to be able to up their chances any given week. Because of the manner in which some people play, I believe that there would be many players who would be willing to pay a price in order to put the best team possible together to improve their chances of winning. The Fantasy Baseball Simulator uses all the batting positions available on a typical fantasy baseball team to determine who would win in a particular matchup. The pitching portion of fantasy baseball is not included in my project.

Sunday, April 17, 2016

Nutritious Meal Planner - Dain Berrett

Nutritious Meal Planner

Dain Berrett

Executive Summary


In January of this year I decided to make a major lifestyle change in regards to my health and nutrition. I researched several fitness plans and settled on the Body for Life approach, mostly due to its practical nature and rounded approach to wellness. Body for Life emphasizes 6 days of exercise a week, alternating between strength training and aerobics. Because you are expected to gain muscle quickly, excellent nutrition is key. The program recommends 6 meals a day, each of which includes one portion each of protein and carbs. Additionally, two meals a day (at minimum) require vegetables. The acceptable proteins, carbs, and vegetables are limited to a certain lean, non-processed list.

For the first 10 weeks of the program things were going great and I was both losing fat and gaining muscle. I was enjoying the meals a lot at first, but was finding that I rarely had time to really plan them out. I kept on resorting to eating the same meals again and again and getting tired of them. Additionally, without knowing exactly what meals to plan for during the week, grocery shopping on Saturday was difficult for my wife and I. These difficulties were threatening my success in the program, and I knew I had to find a way to automate creating my meal plan each week from a pool of acceptable proteins, carbs, and vegetables.


My spreadsheet does this very thing, generating 6 meals for each day of the week and only drawing from a set list of Body for Life foods. Further, I have built in many of my own preferences to the logic in the macro. For example, the first meal of each day always includes eggs (I love eggs in the morning). The third and sixth meals are protein shakes (I love mixing these throughout the day). The other meals can come from any mix of the food groups. This meal generator will ensure that I stay on track with the program and continue to enjoy a diversity of foods. With one click I can generate meals for a week, and with another click I can print it off to hang on my fridge for reference.


Thursday, April 14, 2016

Equity Stock Ticker: Scraping The Web

Executive Summary



The project I did was based on a company I would like to eventually start. I plan to open up an investment fund in the future once I’ve gained more experience the industry and have a reputable name. There are multiple types of ways to run a fund, it can be passive or active, it can be long or it can be short, it can have equity and bonds or many other asset classes. This project focused specifically on a tool for an analyst, portfolio manager, or fund manager who wants to look up information on a publicly traded company and have some of the basic and important information returned to them. The project involved web scraping using the agent class that Professor Gove developed, along with scraping information from a Bloomberg Computer. Most investment funds will have access to a Bloomberg Terminal and so this would still be a very useful tool. The system I built takes one input, a stock ticker, and from there gives you the options to fill the spreadsheet with all the information you might want through the click of a few macro-linked buttons.


**Just remember that if you want the numbers to show up on the Quarterly Trend Ratios, Annual Trend Ratios, and Competitor Ratios sheets, you need to be on a Bloomberg terminal. It's w237 in the Tanner Building and there are multiple terminals there.

http://files.gove.net/shares/files/16w/julioce/vba_project.xlsm
http://files.gove.net/shares/files/16w/julioce/Final_Project_Write_Up.pdf

Events Report Automation - Cory Paxton

Executive Summary

This project creates a weekly report used by the HFAC Events Production department from event data exported from an events scheduling software. The code in this project edits the formatting of the data exported from the events scheduling software, uses several loops and if statements to find and capture the desired data, and populates a report on another sheet that contains the information specified by the department in their specified format. The project also includes buttons in their own ribbon and user forms that allow the user to customize the report to their needs. Prior to this project, a full-time employee would spend 4+ hours creating this report manually

This agenda/report is for the coordination meeting of several groups within the events production department in the Harris Fine Arts Center on campus. They are responsible for ensuring events such as concerts, musicals, dance performances, and several others are properly scheduled and executed. The information on the agenda is a key tool for the groups to properly plan for the events in the upcoming weeks. Not only does the automation save on the labor hours, but on errors as well. Prior reports contain spelling errors along with the incorrect information.


The attached Excel file includes the scheduling software data (on sheet “EMS Report”) and the header for the agenda (on sheet “Coordination Mtg Agenda”: the actual agenda is created beneath the header).


Wednesday, April 13, 2016

Jinsook Lee - Personal Books Organizer: SMART LIBRARY

Executive Summary

Since when I was young, I have enjoyed reading books. When I was a young kid, I have dreamed of having my own library. As a grown up, I have many books at my apartment here, at my home in Korea, and even online. I always wanted to use an existing business solution to organize them but never found one I want. For this project, I decided to develop a program based on that business idea. This file will enable to organize all of the books in possession, to manage them, and even to record if they are read or not. I also added a feature to add books to a wish list for future reference. 

Piano Academy Task Streamlining: Executive Summary by Dallan Carter, IS 520

Executive Summary

This project was completed for the Brigham Larson Piano Academy Program Director. She is responsible for keeping track of their music inventory, selling books to students, ordering more books when levels get too low, and entering them back into inventory when the order arrives. She also must keep track of checked-out books and know which students’ books are over due, with the option to renew the checkout. She desires to keep track of all previously checked-out books so that students can later find favorite songs quickly. On top of her inventory and library tasks, she must also manage communication with the parents. This includes sending out general information emails (such as invitations to sign up for a recital time), reminding parents to respond to a particular email if they haven’t, and letting every parent know which recital time their children have been assigned. Once that information has been collected, she must arrange the order of the performances in each of several recitals so as to provide variety in the music and allow siblings to perform one after the other; finally, she must type up the recital programs to print off and hand out at the show. This VBA project has automated virtually every responsibility listed above.


Riley Preslar MBA 614 Final Project Executive Summary

Executive Summary

Business
This program was not developed for a specific business but instead for anyone of any profession that needs to memorize facts. I am currently a student at Brigham Young University. During the course of my studies and while completing internships I have found it necessary to memorize many facts and random items of knowledge. This program is intended for students and professionals in any area of business that need to memorize facts in a quick and effective method. This program could also be used by someone preparing to enter a trivia contest because it is able to generate random questions from Wikipedia articles.

Program
This program has two major functions:
Function 1: quizzing based on study notes. This program allows students to take notes in Microsoft excel and then quiz themselves on the definition. Studies have shown that rote memorization is highly ineffective and results in almost immediate loss of knowledge. The method implemented by this program involves typing the complete name of a given definition which is much more effective in retaining the information.
Function 2: random fact quizzing. Even more impressive, is the capability of this program to generate quiz questions on random facts pulled from Wikipedia.org. This is an enjoyable way of broadening one’s knowledge base and practicing memorization techniques.

Overview of System
The program utilizes the following concepts:
§  Ribbon Customization
§  Custom Userforms
§  Data import from the internet
§  Web query and HTML conversion
§  Webpage manipulation
§  Custom functions
§  Working with defined ranges and objects
§  Manipulating dynamic arrays
§  Complex vba functions such as ubound, split, and mid
§  Parsing of complex web pages
§  Loops and nested loops

Basis for the Project

This project was inspired by the website quizlet.com which creates virtual flashcards based on study notes. Further inspiration came after watching the TV show Who Wants to be a Millionaire and wondering how contestants would prepare for questioning among various topics. If you have ever considered competing in a trivia gameshow then this program is for you.



Links:


Help Finding a Car
Erik Berglund
Executive Summary
Trying to get a good deal on a car can be daunting project.  Many websites offer you guidelines for what you can expect an individual car to spend, but looking up each option and verifying if it is fairly price can be very time consuming.  My excel project is meant to help my friends and family look for cars and narrow down their options. 
The workbook allows you to search local listings for certain cars and lists all of them.  After listing the local listings, the workbook can look nationwide and check for the same car, get its average price, get the standard deviation, and tell you where in relation to the national average, the local cars fit in price wise.


Medicare Average Claims Report

1 Executive Summary

As an actuarial student, I have to analyze extensive and unorganized data from several health insurance providers, including data from Medicare and Medicaid. The data that I deal with usually comes with extra information that is superfluous and unnecessary. The purpose of this project is to create clean and user friendly data so that users can take a quick look at the total averages of payments that Medicare users made. From the several columns that the data has, users or actuaries are usually interested in two: Ambulatory Payment Classification (APC) and Providers State. Using these two columns, or more accurately variables, users or actuaries can calculate the Total Average Payments, which helps them focus on geographical areas and services provided. In turn, the can generate a deeper analysis.

By clicking the Filter Summary bottom the user can select from as many APCs and US states to filter the data, which came from the 2013 data released by Medicare and Medicaid on their website. Once the data is filtered, it is summarized in the Filter Summary sheet. The next step is achieved by clicking the Pivot Table Graph button. This button allows the user or actuary to create a pivot table based on the filtered data. Additionally, this step creates a bar chart that shows the distribution of the Total Average Payment by state and city
http://files.gove.net/shares/files/16w/sabdysa/Medicare_Outpatients_VBA_Project.xlsm

Blog Archive