MBA VBA Projects
Wednesday, December 6, 2017
Wednesday, April 22, 2015
Crime Data Cleanup
The administration
building at particular university houses the Office of Compliance. Each year,
the university’s Compliance Officer provides a report of on-campus and
off-campus crime statistics for the University along with other universities to
show comparison. The reports are simple and quick to create, and they vary from
year to year depending on which schools are requested to be included in the
comparison. Preparing the data, however, is a much more laborious process
assigned to a student employee. As many university departments experience, the “student
employee” is different every year due to high turnover. So each year a new
student must be trained by the compliance officer to clean and prepare the data.
Additionally, most student employees lack experience with spreadsheets, so they
use manual techniques to cut and paste data. It is not uncommon for rows and
columns of data to get mismatched, or for a random university to mistakenly be
deleted altogether. This adds hours of work for the compliance officer who must
review the data and check for errors. The Excel program created to address this
problem uses VBA code to get rid of unwanted data entries and combine multiple
excel files (provided online by the US DOE) into one workbook from which
reports can then be created. The automated process is complete in just minutes
and significantly reduces the potential for errors. Additionally, the workbook
includes instructions to complete the process, which reduces the time required
to train. The expected reduction in labor costs is hundreds of dollars, and the
busy Office of Compliance will be able to allocate labor to other priorities.
Personal Finance Program
Created By: Ethan Lindstrom
Executive Summary
Over the last several years I have had accounts in 4
different banks and have tried 3-4 personal budget software programs. Each of
them has had features I like but I have not been completely satisfied with any
of the packages, or have run into incompatibility problems with data coming
from one of my financial institutions. I decided that building my own personal
finance program would alleviate those problems and give me control to build the
features that I want.
My new personal finance program imports transactions, checks
for overlap in the existing and incoming data, categorizes the incoming
transactions, has a user form to allow categorization of items that were not
categorized under existing rules, and sums the categories over the specified
date range to compare against the budgeted amounts. It also pulls all data for
the house I own and rent out to a separate sheet so I can keep track of my
gains/losses for that property.
I have included a file that can be used to test the import functionality of the program (Test Transaction Sample.csv).
Monday, April 20, 2015
Symbolic Picture Story Inventory (SPSI) Basic Module
By: Ivan Marchenko
Purposes
Purposes
This project creates a basis for a program that will later
be used to automate Symbolic Picture Story Inventory (SPSI). This inventory is
used to analyze the symbolic stories that define the identity of individuals
and groups. These stories help understand the logic behind a certain type of
choices and behavior.
The method works as follows:
·
Client chooses the main heroes of his/her story:
picks a picture that will represent himself/herself and then pictures that
represent other significant heroes that should act in the analyzed context
(those pictures may be warriors, princesses, animals etc.)
·
Client chooses the environment (roads,
mountains, caves, woods, houses, castles, walls, rivers etc.) and arranges the
“stage” for heroes to act
·
Client places the heroes on the stage to show
the logic of the story
·
Client answers several questions regarding the
logic of the story
·
The resulting picture and answers are then
analyzed by a psychologist
The basic module of the program allows the user to construct
the picture choosing from a categorized menu of pictures. This version contains
pictures that are downloaded from the web, whereas the final version will
feature the pictures that will be drawn specifically for SPSI and will have
options of choosing different positions of the object (limited 3D rotation).
SPSI pictures will be black and white and emotionally ambiguous.
Functionality
This version features only the basic functions of the future
program. Full version will allow user not only to draw the picture, but also
tell the story that is depicted by answering questions. It will also allow the
researcher to automatically analyze the picture by calculating object sizes,
positions etc. This version allows to do the following:
·
Choose image to insert by specifying the
category and the type in the dropdown menu
·
Insert the picture to the drawing area by
clicking on it
·
Arrange inserted objects to form the picture
Sunday, April 19, 2015
Automated Email Scheduler for Gmail - Will Matheson
Executive Summary
This project aims
to create an email scheduling solution through Microsoft’s Excel VBA for small
organizations and individuals who use Gmail as their primary email application.
The benefits will help users efficiently communicate with clients, suppliers,
applicants, recruiters, contacts, management, direct reports etc. Once the
workbook is loaded up with pending drafts, the workbook will need to be
scheduled to run as a task in Windows Task Scheduler to be run every hour.
Relationship management with clients, suppliers, applicants, and coworkers is an important facet of today’s world of business. Employees who remember to maintain contacts with key individuals in a timely manner are able to excel. Email is becoming a critical channel for communication. Automated e-mails with canned messages are commonly used but lack the personal touch of a phone call or customized e-mail. One struggle with writing customized e-mails is making sure the message is sent to the recipient at the right time. During a busy week when work is piling up, it is easy to lose track of time to get an important e-mail out Friday morning and ends up being remembered Friday at the end of day. This can result in delayed projects, lost productivity, and slow turnaround times in communication. This effect is compounded when dealing with overseas contacts whose workday is opposite your own.
The business need is a tool that can assist users who want to schedule e-mails to send to their contacts at a specific time. This way, users can have a productive episode of drafting e-mails at any time of the day or night and have the messages sent out at scheduled times in the future. The benefit is not letting important communication between the user and their contact be at the mercy of a busy schedule.
The most commonly used application for workplace e-mail is Microsoft Outlook. A feature to schedule e-mails at a specific time exists for Outlook and greatly benefits employees as they strive to manage relationships and communication pertaining to their work. Google for Work is a growing SaaS solution for small businesses which offers Gmail as an application for e-mails. The same functionality of scheduling e-mails does not exist for Google’s Gmail. There are third party developers who have created Chrome Extensions which do offer scheduling functionality. However, these solutions come with subscription plans and have tiered service levels which can limit the number of scheduled e-mails you can schedule or send in a period of time.
Relationship management with clients, suppliers, applicants, and coworkers is an important facet of today’s world of business. Employees who remember to maintain contacts with key individuals in a timely manner are able to excel. Email is becoming a critical channel for communication. Automated e-mails with canned messages are commonly used but lack the personal touch of a phone call or customized e-mail. One struggle with writing customized e-mails is making sure the message is sent to the recipient at the right time. During a busy week when work is piling up, it is easy to lose track of time to get an important e-mail out Friday morning and ends up being remembered Friday at the end of day. This can result in delayed projects, lost productivity, and slow turnaround times in communication. This effect is compounded when dealing with overseas contacts whose workday is opposite your own.
The business need is a tool that can assist users who want to schedule e-mails to send to their contacts at a specific time. This way, users can have a productive episode of drafting e-mails at any time of the day or night and have the messages sent out at scheduled times in the future. The benefit is not letting important communication between the user and their contact be at the mercy of a busy schedule.
The most commonly used application for workplace e-mail is Microsoft Outlook. A feature to schedule e-mails at a specific time exists for Outlook and greatly benefits employees as they strive to manage relationships and communication pertaining to their work. Google for Work is a growing SaaS solution for small businesses which offers Gmail as an application for e-mails. The same functionality of scheduling e-mails does not exist for Google’s Gmail. There are third party developers who have created Chrome Extensions which do offer scheduling functionality. However, these solutions come with subscription plans and have tiered service levels which can limit the number of scheduled e-mails you can schedule or send in a period of time.
Saturday, April 18, 2015
BYU Tanner Building Room Reservation Export Tool - Daniel Christ
EXECUTIVE SUMMARY
The “business”
that my project focuses on is that of Me, Inc. but I plan to share this tool
with my classmates and future BYU business students. The first year of my MBA
at BYU is almost finished and one of the keys to being successful was working
well with your various teams. This meant meeting frequently and this often occurred
in one of the Tanner Building team study rooms. To use a study room, you must
reserve it on the BYU Marriott School room scheduler web site (http://marriottschool.byu.edu/scheduler).
To stay organized, I often would reserve a study room and then immediately create
a Google Calendar event noting the room location, date, and length of time. As
I looked back on this first year, I have booked nearly 300 room reservations
and for each of those, I manually created a Google Calendar event. If I assume my
teammates booked a similar amount, that’s another ~1,200 (~300 x 4) room
reservations that were made over the past two semesters. We were all consistent
in creating calendar events for each reservation. If we assume each event took approximately 90
seconds to create, that’s 135,000 seconds (1,500 x 90) or 2,250 minutes or 37.5
hours of time spent on simply creating Google Calendar events. Unfortunately, I
cannot have this time back but if I created a tool to automate parts of this
process, my second year of the MBA program would free up a substantial amount
of time for me and my classmates.
The
tool that I built allows me to compile all of my upcoming room reservations
into a tidy CSV file that I can then easily upload into my Google Calendar. The
outcome is having all of my study room reservations turned into individual
calendar events that are cleanly placed into my Google Calendar. My tool is
driven by a single button on a customized ribbon that quickly allows me to log
into the My Reservations web site, extracts all necessary room reservation data
from the web site, and then places all of the data into a CSV file that is then
ready to be uploaded into Google Calendar. The Web Query information is cleared
after the macro runs so it’s ready for its next batch.
Thursday, April 16, 2015
Shipment Receiving Log - Joseph Murphy
Executive
Summary
This project is for a
client company Northrop Grumman Corporation (NGC), and specifically for a
division site located at Hill AFB, UT. Northrop Grumman is an aerospace
engineering defense contractor with major military programs such as the F-35 Lightning
II Joint Strike Fighter, the B-2 Spirit stealth bomber, and the Minuteman III
Intercontinental Ballistic Missile (ICBM).
NGC’s Hill AFB site
manages operations for ICBM-related activities at seven main locations
throughout the nation. These locations include engineering offices, testing sites,
and military grounds.
This particular division
of NGC wanted improve the visibility of receiving shipments of orders / purchases. NGC purchases that are shipped to the sites
have a lot of variation; they can be highly technical, expensive pieces of
equipment ordered by engineers, or they can be low-cost commodity items such as
printer cartridges ordered by facilities management.
Currently employees at
the different sites use a clipboard and paper records to track expected
incoming shipments and whether shipments arrive or are lost in transit.
Information such as delivery courier, tracking number, sender, receiver, dates,
location, receiving personnel are recorded for accountability and tracking.
The sponsor of the
project wanted a centralized repository for this information, rather than
multiple documents for each company site, so that there’s better tracking of
whether their expected shipments are actually arriving.
The solution that I
created is an Excel document to be used with a user form interface to be used
as a shipment receiving log. This log sheet also is able to, with the click of
a button, launch the courier’s tracking website to check the status of the
shipment. This file can be posted on NGC’s SharePoint drive, so that purchasers
can input their purchases and warehouse receiving personnel can expect and
track the shipments’ arrival.
This unification of
information is critical for the division because they have had a history of delayed
shipments, lost-in-transit shipments, and instances where the sender claimed to
send the product but did not and NGC was charged for the purchase. Because
there has not been a singular communication channel, these cases existed.
http://files.gove.net/shares/files/15w/jmur2000/Receiving_Logbook.xlsm
Subscribe to:
Posts (Atom)