University Student VBA Projects

2012-02-03T15:32:00.001-08:00

Apple Inc.

Apple currently has been in the news for its record sales. It is the leading smartphone and PC vendor in Q4 of 2011. It displaced the performance of leading manufactures like Nokia by record sales. The smartphone shipment increased by 63%, PC by 15% and tablets by 247% by the end of 2011.

The main question is how is apple able to achieve such a huge growth? Let’s analyze Apple’s performance using a strategy curve.

If we compare Apple to Sony or HP, we can see that Apple products are much more expensive than its competitors. Although there are comparably more applications available in Apple products, but only few of them are available for free whereas in Android OS products more applications are available for free. But Apple bets its competitors in design, performance, customer service and reliability. However, Apple products are less customizable than its competitors.

In spite of its high pricing, Apple has been successful to gain market share. The main factor behind its success is its innovation and product reliability.

Strategy: Porter's 5 Forces

2012-01-20T13:29:00.000-08:00

Porter's five forces is a framework for industry analysis and business strategy development. These five forces effect the company's ability to serve it's customer and make profit. Lets analyze Ford Motor Company according to Porter's Five Forces.

1. Threat of New Competition: The treat of new competition is very high for Ford. With companies like Hyundai and Kia, which are gaining extensive market share, we can say that there is major threat for Ford to lose market share to these new companies which are trying establish their root in the American Auto Industry. Since both the scale of economy and Scope economy are high for the Auto Industry, the treat for new competition is imminent for company like Ford.

2. Threat of Substitute Products: The treat for substitute is low but it still exist. With the cost of gas increasing and the economy not function very well, people have cut down their spending in long term investments like automobiles. They have gone to other alternatives like public transportation.

3. Bargaining Power of Customers: Although the Automobile Industry is huge and there are huge number of buyers, due to the availability of alternatives and the current economic downturn, the Bargaining power of customers have gone up. The customers are looking for more affordable and reliable automobiles, and they have huge number of alternatives to choose from.

4. Bargaining Power of Suppliers: The bargaining power of suppliers is very low for Ford. Ford controls all its suppliers and have made special arrangement and commitments with its suppliers that favors its operation and helps cut cost for ford.

5.Intensity of Competitive Rivalry: The Intensity for Competitive Rivalry is very high for Ford. With other automobiles companies trying to boost their sales and new companies entering the market, there is intense rivalry. Although there are huge start up cost associated with new companies, but the companies trying to penetrate the market are already well established in other countries. The switching cost and exit cost associated with the automobile industry are very high which makes the rivalry even more tough.

Employee Performance Tracker

2011-12-13T21:30:00.000-08:00

Executive Summary

My sister is a supervisor in a call center for a security company. Employees in her department are evaluated and coached weekly as an initiative to improve customer service. The evaluations are based on showing up to work each day, clocking in and out at proper times, number of calls put on hold, number of calls answered, graded reports for randomly selected calls, etc. Each Supervisor has a team of 20 employees. My sister’s biggest concern is that she can easily keep track of each employee’s improvement week to week so that she can be well aware of areas of concern and achievement for each person. Right now, my sister manually copies and pastes information for data downloaded from AVAYA, KRONOS, and CMS Applications into one summary worksheet.

I offered to build her a program that will take care of updating the master sheet each week once the necessary data is downloaded. The program analyzes different worksheets individually then summarizes specific data in the master sheet. Not only will updating the master sheet take only seconds compared to hours, the user can easily see where the areas of improvement and achievement are based on color coded cells corresponding to previously set standards.

Centerville Youth Survey Process

2011-12-12T23:12:00.000-08:00

VBA Final Project

Oliver Crane

Executive Summary

The city of Centerville has a summer recreation program in which they run approximately twenty classes. My evaluation team in the MPA program has contracted to evaluate the summer recreation program. As part of the evaluation my team developed an evaluation rubric based on assessing participants 1) fun, 2) interest, and 3) safety levels.

As part of this evaluation process I have created this VBA project to automate the data processing. The spreadsheet has two buttons, 1) refreshes the page, and 2) processes the data. The spreadsheet accesses the data which has been published by the Google Docs survey. The spreadsheet then automatically divides all the data into different tabs and consequently displays the data on a summary tab. The summary tab is easy to use and assess the success of the summer recreation program. The data processing button allows the user to search the data by year which allows cross year analysis.

The spreadsheet allows a user to understand large amount of survey day through two buttons in excel. The most difficult thing in creating this was in debugging the loop function for checking the survey questions with counting the responses. It just took a long time with help from others to figure it out. Also I made simple syntax mistakes in making sure the reference cells for the counters matched the questions cells that were searched.

This document will hopefully be used by Centerville to better assess their efforts in providing fun, interesting, and safe summer recreation.

Amazon.com Price / Attribute Harvester

2011-12-11T12:49:00.001-08:00

Executive Summary

For my final project, I decided to develop an attribute & price harvester for a company called Oxspring Paul. Oxspring Paul is a product distribution company that specializes in e-commerce and media based sales channels. They primarily deal in watches. As part of their business, they have to manually collect attribute & pricing data on new watch products that they start to sell in order to create product profiles. In addition, they perform regular pricing checks on items currently in their inventory to ensure optimal marketplace competitiveness. Currently, to collect these two data types, an employee will manually look up each item on Amazon.com to harvest the available attribute data and also establish a marketplace pricing benchmark on initial product build. To maintain accurate pricing data, it is required that employees cycle through the entire existing inventory and retrieves pricing data on a regular basis. Collecting attribute & pricing data represents a very large amount of employee labor hours. With nearly 2000 new unique products being processed per year and over 4000 current SKUs that must be price checked regularly, it is estimated that new attribute & price data harvesting requires about two full-time employees. In addition, the pricing portion is just not being done as well as it should be done. Ideally, a price check would be performed on every item, every day. Oxspring Paul would be lucky if pricing data was updated on every item once a month.

In developing the attribute /price harvester, I had the following goals:

1. I wanted the harvester to be capable of operating on just one input, the Amazon unique identification number or ASIN. I chose this identifier because Oxspring Paul can easily get a list of the product ASINs of every item they sell on Amazon. In further developments, I would like to build out a version that can find an ASIN based off just the manufacturers model number.

2. Collect the following pricing attributes: List Price, Price, Buy Box Price, Buy Box Shipping, Buy Box Total Price, New Market Competitors, New Market Price, Amazon Sales Rank, Rank Category and Selling Status. In addition, I wanted it to collect the following product attributes: Brand Name, Model Number, Part Number, Model Year, Item Shape, Display Type, Clasp, Metal Stamp, Case Material, Case Diameter, Case Thickness, Band Material, Band Length, Band Width, Band Color, Dial Color, Bezel Material, Bezel Function, Calendar, Special Features, Movement, Water Resistant Depth and Dial Window Material Type.

3. I wanted the harvester to be able to diagnose what type of page (status) it was receiving from Amazon and process it accordingly. Amazon products pages are formatted in five possible statuses: In Stock, Available from These Sellers, Currently Unavailable, Only Available at These External Websites and Oops. The formatting of the page is designated based on the current selling status of the product or in the case of the Oops format, the lack of a product with the ASIN used.

4. Lastly, I wanted the program to save the sheet of data as its own .csv file in a folder (Data) based on the current location of the workbook. This .csv file would later be automatically processed by a SQL based system that would import and analyze the data.

Wells Fargo Personal Finance Program

2011-12-10T20:51:00.000-08:00

Based on personal experience, I have observed that many people will avoid doing even the most crucial tasks unless there is an extremely quick and easy way to accomplish it. This can even include going online to check bank account balances, see if the gas bill has been paid yet this month, or if we are behind on any loan payments. In order to consolidate these processes, I decided to create a program that would allow me to perform many of these same functions, all from within the same excel workbook.

The program allows a user to input his/her Wells Fargo account information, and the program will return the names and account balances of any accounts they have. If the user inputs a merchant's name into the program, it will also update and show the dates that transactions have been made with this merchant. This will let people see how often the go to certain vendors, or check to see if they remember to pay their gas bill yet this month.

The program also checks the balances and current payment information for student loans through UHEAA or FEDLOANS. It will tell you when you made your last payment and how much it was, as well as if you currently owe a payment. If you do, you will receive a reminder text message telling you to remember to pay before the due date.

- note: the actual program has been modified to remove any personal information.

http://files.gove.net/shares/files/11w/jbailey4/Jared_Bailey_-_Project_Writeup.pdf

http://files.gove.net/shares/files/11w/jbailey4/Wells_Fargo_Budget_Submission.xlsm

Products Input & Labeling Project

2011-12-10T20:44:00.000-08:00

Executive Summary:

The present project has as an objective to build a database for a food company. Such database must contain details as type of product, where it is storage, quantity per unity of product, and generate a label that describes the localization of the product in the storage place.

The process consists in a input form that allows to choose the necessary elements that will fill a datasheet. Such datasheet will be base to create the labels and print them.

Implementation Documentation

The following steps were followed to develop the project:

Identify the needs of the clients were followed to develop the project:
To get a up to date list of products used for the client (Fig 1)
Develop a system of labeling that allows product localization in it storage place
Develop an input form
Develop macros for labeling
Develop label masks (Fig 2 & 3)

Products List Example

Description:

The process was developed using a series of concepts learned during this semester at VBA classes. An user will fill an input form created as a Userform object. All variables are linked to a spreadsheet in such way that, after clicking the OK button, all data will populate the spreadsheet. Before the data are inputted in the spreadsheet, a

check routine is performed to verify if any fields are left in blank. This routine is executed using successive IF commands to verify blank spaces in each field.

An code was created having as main tool a FOR EACH loop to allows the form initialization and keep it fresh for new inputs “in a row”.

Labels are built from the datasheet. Macros were created to compo

und arrays and allows that fields in the label Masks could be filled.

The printing process must be performed in an usual manner trough the Excel program.

Label mask Examples 2 & 3

http://files.gove.net/shares/files/11w/Marcelio/Final_Project_VBA_Class.xlsm

http://files.gove.net/shares/files/11w/Marcelio/Products_Input_Project.pdf

Equity Analysis Tool

2011-12-10T16:41:00.001-08:00

TheProblem:

I want to form opinions about publicly traded equities quickly. Iparticipated in a student run investment fund, Global Financial Advisors. I listento student presentations on firms operating across the market. We discuss these firms at length. I like many students, balance listening tothe presentation with pulling up financial data, charts, and qualitativeinformation. The financial data is spread across several webpages. The basicdata is available, but it isn’t formatted to be intuitively meaningful.
I also like to paper-trade options. Often the trade is quick and exploits a technical chart pattern. In this case, as well as the first, it ishelpful to have a snapshot of the company that draws a picture of the companyfundamentals. For example, if I seepositive signal in the market for the name it would be helpful to know if thecompany was over-levered first. Conversely, if the name is significantlyundervalued I would want to know before selling it.

TheSolution:

This workbook consolidates the information that I like to have available toform an opinion about a publicly traded equity. The primary purpose of this tool is to articulate meaning with thedata. It does this in four ways:

· It only shows essential data on the main tab.

· It organizes tables more efficiently.

· It converts raw data into ratios that conveymeaning.

· It consolidates balance sheet data intocharts.

The fundamental questions it answers is,
“Should I look at thiscompany further?” & “What should I look at?”
It is not intended to answer the question, “Should I buy this stock?” So, if you use it in that way it isn’t myfault if you have a bad experience.

http://files.gove.net/shares/files/11w/crl27/equityVBAProject.docx

http://files.gove.net/shares/files/11w/crl27/equityVBAProjct.xlsm

Sales and Use Tax Return Facilitator

2011-12-09T22:59:00.000-08:00

Executive Summary

Taxes are an essential part of any well developed society, but tax laws are often very complex, tedious and downright boring. This often makes filing tax returns an unwelcomed and frustrating chore for individuals and businesses. My project is a program to help facilitate filling out sales and use tax returns. It allows the user to fill out their sales and use tax returns for 2011 and the first quarter of 2012 by simply entering data into the required fields. The user can put a copy of the return in their printer, press the “Print” button and the return will print with all of the necessary data.

2011-12-09T22:56:00.000-08:00

VBA Final Project

Devin Curry

Executive Summary

At church, I was called as a home teaching supervisor. This means that I am asked to encourage a group of priesthood brethren to complete their home teaching and assist them in any way I can. Towards the end of the month I find out how everyone did and report the results to the elder’s quorum secretary. In order to complete my calling, not only do I need to keep track of who has completed home teaching each month, but I also need to see who is consistently missing their home teaching. I also need to keep track of comments and concerns home teachers have about their assigned families so I can communicate them to the elder’s quorum presidency. The church has many of these features available on MLS; however, the elder’s quorum secretary is the one that uses that software. To keep track of things, I created an Excel based home teaching program.

The home teaching program will help me to complete my duties as a home teaching supervisor, but it is capable of helping to organize an entire ward home teaching system. The program has the following features.

· Because turnover is so high in student wards, the program uploads the ward directory off of lds.org.

· The program allows the user to add or delete families if they move during the semester.

· The program adds a new column for each month of home teaching.

· The program allows the user to assign and reassign home teachers to families.

· The user can add comments pertaining to an individual family. The user can view all pending comments and delete resolved ones.

The user has the ability to generate a number of reports to help organize home teaching and monitor home teaching consistency.

http://files.gove.net/shares/files/11w/dcurry/FinalProjectWriteup_DevinCurry.pdf
http://files.gove.net/shares/files/11w/dcurry/Final_Project.xlsm

Automating a Floral Business

2011-12-09T22:48:00.001-08:00

Description of Business

Sassy Stems is my sister’s floral business that she runs outof her house. Weddings are her sole business, and each one is time intensive.She loves the creative part of the business but hates the business side of itand has a hard time getting herself organized before each wedding. With eachnew client, she has to take notes from a consultation with a client, make alist of all the materials she will need to use and the flower arrangements shewill need to do, and produce an invoice billing the client for the services sheprovided. She does each of these in a separate document and then has to copyeverything from the consultation to the other two documents. It is ridiculouslyredundant.

My System

I took the three different documents that Sassy Stems wasproducing for each client and combined them into one easy Excel file that shecan keep track of the wedding in. I have automated it by introducing a userform that she fills out during a consultation, highlighting the client’spreferences and the things that my sister needs to make for them. Once shefills out the consultation, all she has to do is push Submit and her invoice ismade for her and her materials list is started for her. She can also email theclient the invoice right from the Excel file.

URLs

AMEX Credit Card Statement Eliminator

2011-12-09T22:46:00.000-08:00

As an investment broker for three years, I had the opportunity to consult clients on a myriad of personal finance topics. Of the many personal finance principles I learned during that time, few are as fundamental and important as this: Spend less than you earn. A critical component of that is keeping track of your expenses on a regular basis, and ensuring that they are within budget. Furthermore, the ability to set an appropriate, and realistic budget is enhanced by a knowledge of your historical spending habits over different periods of time.

This tool seeks to facilitate the process of budgeting and tracking expenses by automating several steps of the budgeting process. Specifically, the tool has two features: FEATURE 1 allows the user to import immediate, current transaction history on his/her American Express (hereafter “Amex”) credit card from the Amex website directly into a tracking spreadsheet, thereby eliminating the need to receive paper (or even emailed) statements. FEATURE 2 allows the user to generate customized spending reports (including charts) – a critical component of the budgeting process.

Executive Summary: http://files.gove.net/shares/files/11w/cyf227/Credit_Card_Statement_Eliminator_-_Chris_Farnsworth.pdf

Excel File: http://files.gove.net/shares/files/11w/cyf227/Credit_Card_Statement_Eliminator_-_Chris_Farnsworth.xlsm

Thomas Thorley Final Project

2011-12-09T21:57:00.000-08:00

Thomas Thorley
December 9, 2011
Isys 520

Identifying Star Investors

Executive Summary
My project is designed to assist me with data collection for a research article that I am currently working on. The intent of the article is to determine how much, if any, benefit is gained by acquisitions, through interaction with a star investor. The theory in principle is
that a company is more likely to be successful if it is acquired by a star investor than if some unknown company had acquired it. This Information Systems project is designed
to help me quickly gather data on multiple companies and assess whether or not
they qualify as star investors.

I will identify thirty companies to be designated as star investors and used in my study. I begin
with Forbes list of the top 100 businesses in the United States as these are all companies that are well known and thus carry a strong brand name. Using those company names I proceed to use web queries to retrieve the company’s stock ticker symbols, as well as measures of the financial performance of each company. I use the following three measures to assess financial performance and the ability to be a star investor:
1. Total Cash - represents the company’s ability to invest
2. Operating Margin - represents the company’s profitability
3. Return on Assets (ROA) - represents managerial effectiveness

These are arbitrary measures that may change in the future if I deem other measures to be more indicative of star investors, but regardless, for the purposes of this project a framework will be in place that allows for such changes in the future. I next manipulate the data and rank the companies in regards to their performance in each area. An aggregate of these rankings is used to determine the thirty star investors. An interface is then developed that allows the user to interact with the data and compare the results from each company. Initially, I thought it would be necessary to calculate measures such as ROA but as they were available, I deemed it redundant to repeat the calculation. This project will draw deeply on principles learned this
semester such as the use of arrays, loops, range manipulation, and web queries
among others.

http://files.gove.net/shares/files/11w/tthomaso/Thomas_Thorley_Final_Project.pdf

http://files.gove.net/shares/files/11w/tthomaso/Final_Project.xlsm

Vulnerability Report Analyzer

2011-12-09T19:00:00.000-08:00

Executive Summary

Security specialists utilize a vast array of tools to perform different scans on companies’ systems. These tools are utilized to detect potential vulnerabilities that relate to the configuration and patch level of a given server. Upon performing a vulnerability scan, security specialist analyze the data in order to separate false positive alerts from real system weaknesses. This analysis process can be tedious and challenging due to the large amount of data produced by the scanning tools. A factor that largely contributes to the difficulty to analyze data is that not all tools generate reports that can be easily exported to a spread sheet. One of such tools is Tenable’s Nessus Scanner.

The Nessus scanner is widely used by security professionals to analyze the vulnerability level of a given company. However, Nessus’s reports are only made available on an HTML format. While the reports have greatly improved since the initial launch of Nessus, it is still difficult to summarize data to present it to management in a meaningful way.

The Vulnerability Report Analyzer is a program that imports Nessus’ scan results and creates a report table where management can easily sort through each finding. The program can generate a summary of the top ten servers with the most critical vulnerabilities as well as a comparative graph that indicates the type of vulnerabilities per server. Users are able to create multiple reports within the spreadsheet. The program is flexible to allow users to append results of scans performed at a later date to original reports. This feature is useful to generate a single monthly scan report while weekly scans are performed. The program automatically generates pivot tables for each report in order to assist management in filtering different field. Graphs are also generated from pivot tables that can be filtered by hosts and vulnerability risk levels. This tool is particularly useful for environments that host hundreds of systems and where multiple vulnerability scans are performed.

Given than Nessus has several HTML report outputs, it is important to mention that this program currently works only with the “Detailed HTML Report (by finding)”. Further functionality could include:

· Addition of other Nessus report outputs;

· Import of .csv files from other scanning tools such as McAfee’s Foundstone; and

· Trend analysis to detect those servers that continually present the same vulnerabilities.

Overall, the program is a useful tool that can be utilized to gain meaningful information on the status of systems. As far as implementation of VBA concepts, the program makes use of virtually every topic discussed during the semester, from ranges, to forms and from arrays to pivot tables. I am confident that you will find this tool very useful in your analysis of vulnerability reports.

Links:
http://files.gove.net/shares/files/11w/au22/VulnerabilityReportAnalyzer_-_AntonioUriarte.pdf
http://files.gove.net/shares/files/11w/au22/VulnerabilityReportAnalyzer_-_Antonio_Uriarte.xlsm
http://files.gove.net/shares/files/11w/au22/VulnerabilityAnalyzer-NessusFiles.zip

Comparing Health Insurance Rates

2011-12-09T18:15:00.000-08:00

Executive Summary

Every business needs to be aware of what their competitors are doing in order to maintain their competitive advantage. Health insurance is no different. As I am working as an actuary for SelectHealth, the largest health insurer in Utah, one of my tasks is to help determine the premium rates. The rates given to large and small employers vary greatly based on a large variety of factors and is not easy to compare. However the base rates for individual policies is determined mainly based on gender and age. In the past to compare our rates with those of Regence I have had to manually enter in an age and different number of dependents into their website and then enter the rates into excel. The goal of this project was to automate excel so that it would enter in the birthdates and genders for me and gather the rates much faster than I was able to, thus leaving me with more time to work on other areas of my job.

2011-12-09T15:29:00.000-08:00

Executive Summary

For my Final Project, I am building an Inventory System for Dream Chocolate Company. Dream chocolate is a chocolate manufacturing company that produces Organic Chocolates. It has various product segments and each product segment has different types of chocolate products. Customers can order products over the phone through the customer service representative. After the order is received it is processed by the Sales Department. This project will assist the Sales department to maintain a Real Time Inventory system and manage Customer Database. Also it will help the company to automate Customer Interaction by automatically sending emails to the customer once the order is processed and the order is shipped. This will increase the Line of Visibility for the customers as they are updated on their order status.

Following is a brief overview of my project:

1. Maintain customer database: Allows the company to add new customers to the customer database and also edit current customer information.

2. Create Purchase Order: The sales department will be able to generate a Purchase Order once the order is received.

3. Real time Inventory management: They will be able to see the current inventory level available to sale and back order report.

4. Customer Interaction: The system will automatically email the customers once the order is received and also when the order is shipped.

5. Update Price: This system will allow updating the current product prices from the company’s website so that the prices are same in the system and the website.

6. Order Status: The sales department will be able to track each Purchase Order’s status.

7. Efficient Picking and Packing Process: This system will allow the sales department to generate picking slip which will include the product description, quantity and exact location of the inventory. This will expedite the picking and packing process.

merge duplicate accounts

2011-12-09T01:54:00.000-08:00

FINAL VBA PROJECT

Description of the Business

BYU Continuing Education offers a variety of conferences and workshops ranging from youth summer programs to organ classes. Workshops and conferences are typically held on BYU campus and can be completed for either college credit or for personal enrichment.

Issue

Any customer who has participated in a conference or workshop has a CE account with their personal information and an individualized account number. At times, two accounts will have very similar personal information; these are potentially clone accounts with that need to be merged into one account. This is a repetitive and tedious process. If either of the two accounts have a job title, then the merger cannot be completed. It is very frustrating when you click through three different pages and wait for all merge possibilities to load only to find out that it is impossible to complete the merge

System

The system I build solves this problem by finding which potential merges have job titles and lists these merges on an excel sheet.

http://files.gove.net/shares/files/11w/shell4/vbaproject.pdf

http://files.gove.net/shares/files/11w/shell4/merges.xlsm

KB Data & Analytics Generator

2011-12-08T23:06:00.001-08:00

Summary:

The Knowledge Base Analytics Generator is a macro-enabled Microsoft Excel 2010 workbook produced by Bradford Melluish for Brigham Young University Office of Information Technology’s Knowledge Management Group under the direction of the Knowledge Manager, Kent Ockey.

The KB Analytics Generator utilizes the Visual Basic for Applications (VBA) programming language to retrieve data from the BYU Knowledge Base and BYU CAIMS (Change Management) to retrieve KB article and product data for use in data analysis. This data is then replicated by the Generator into several different formats specific to individual user needs, and then distributed to c-suite, department, and product level managers for use in cost reduction and product maintenance strategies. This data is also made available to managers of customer-facing organizations such as OIT Service Desk and Knowledge Management itself for use in gauging incident-KB association rates, gaps in documentation, and other potential areas of improvement.

Value Proposal:

While existing cost-management solutions provide by-service cost information, KB Analytics provide the context to make this information truly relevant to management. By identifying the number of times a given KB article was associated with an incident ticket, managers are able to see not only the cost of a given service, but also gain insights into the issues driving maintenance costs. This in turn allows management to prioritize development and documentation efforts to eliminate the most frequently occurring and/or expensive issues to drive down costs, improve customer satisfaction, and provide greater value to the University. Also, the gathering of aggregate call data provides additional insights into ‘pain points’ for customers than cost data alone might not be able to portray.

Project URL: http://files.gove.net/shares/files/11w/sukiyaki/Final_Project_KB_Data_Generator.xlsm

WorkJoy by Brad Moss

2011-12-08T22:56:00.001-08:00

Executive Summary:
We all know that jobs can be boring. When a job is boring, our motivation and attention to detail tend to start lacking. Additionally, the job doesn’t fulfill out emotional needs and we look for other more fulfilling endeavors. For many, the environment of a call center is that epitome of a ‘boring’ job.
Because of the repetitive and laborious nature in call centers, more center managers face a significant business problem. The attrition of the call center workers is extremely high, even 100% in some centers. If people felt more ‘engaged’ or excited about the results of their work, then this high attrition would decrease and quality would improve greatly. The WorkJoy Experiment is an application to test a “Gamification Theory”. If I can give workers more to think about than the repetitive tasks they perform, then they will care more.
WorkJoy is a “game”. The key to this game however, is that players earn points and skills by performing real world tasks. For the sake of this class, I have tied to functions that give real world feedback into the MBA Lounge Forums. Each time people participate in the forums, their status in the game increases. In theory, the result of people playing the game should drive higher activity in the MBA Lounge Website.
http://files.gove.net/shares/files/11w/beskull/Prototype_01_v0.08.xlsm
http://files.gove.net/shares/files/11w/beskull/BradMoss_WorkJoy_Final.pdf

Mint Budgeting Tool

2011-12-08T22:54:00.000-08:00

Mint.com is a personal budgeting tool by Intuit. The site collects the user’s bank account information and uses this to populate items such as cash balances, outstanding loans, etc. Users can create different budgets on the site, which will then be automatically updated based on transactions that occur during the month (unless transaction occurs with cash).

With this project, I wanted to build functionality into the budgeting tool that allowed the user to project their budget for a period from 6 months – 2 years. The intent behind this was to allow the user to take advantage of the up-to-date account and transaction information on Mint, while being able to project/edit income and expense items easily in Excel. This spreadsheet provides a much more dynamic budgeting environment that allows the user to accurately project their financial position up to 2 years from the time when the budget is created.

Links to files:

Aaron Seiter - Final Project

2011-12-08T22:47:00.000-08:00

Final Project – Equity Option Datasheet Generator

2.1 Executive Summary

Summary

For my final project I created an equity option datasheet generator that gathers data from Bloomberg and spins off that data into individual excel workbooks then saves these files in a specified location.

Business need for the program

This program was built in order to solve a problem faced by BYU Professors Boyer and Carpenter. They are co-authoring a research paper on trade behavior of call and put equity options. In order to write the research paper they needed trade data on 8,530 US equities pulled in from a Bloomberg terminal. It would take an insurmountable amount of time to pull and manipulate this data by hand. It became necessary to write a VBA program to expedite the process.

Overview of program

The program performs five main tasks.

1. Retrieve one of the 8,530 tickers from the correct ticker tab

2. Create a formula using the retrieved ticker necessary to get the “option chain” (aka list of all options listed on Bloomberg for that ticker)

3. Filter the “option chain” to only include options that meet specified date criteria

4. Create formulas to pull the trade data from the filtered “option chain”

5. After the data is successful pulled into the three sheets dedicated to the retrieved ticker, the program will spin those three sheets into a separate workbook, rename the workbook, and save it in a specified location

Difficulties encountered

The most notable hurdles encountered while programming were:

1. Bloomberg’s formulas are unique to Bloomberg and difficult to recreate in VBA

2. The number of options in a ticker’s “option chain” is not constant between tickers. One might have 75 while another may have 475.

3. Bloomberg takes a few seconds to populate the data after you make a data call.

Excel File:

http://files.gove.net/shares/files/11w/aseiter/AaronSeiter_OptionsV11.xlsm

PDF Write Up:

http://files.gove.net/shares/files/11w/aseiter/Final_Project_Write_Up_Aaron_Seiter.pdf

2.2 Implementation Documentation

Creation of the VBA program

Step 1: Drawing Board

When I first started this program I got a blank sheet of paper and organized my thoughts. This program is the most complex program I have made to date. Before I even opened up a session of excel I outlined exactly what I wanted the program to do. Often times I didn’t have a clue of how to get Excel to do what I wanted, but I at least knew what I wanted out of the program. This saved me a lot of time since I didn’t have to build and delete unnecessary blocks of code.

Step 2: Insert Outline into VBA

I inserted the following outline I created from step 1 into two modules in VBA as comments

'This creates copies of the master sheets

'This part selects the right ticker from the sheet specified bellow

'This pastes it onto the recently copied sheets and creates the option chain formula

'This filters the option chain and creates subsequent option trade data formulas

'This part separates the sheets into its own workbook

'This part renames the sheets

'This part saves the newly minted sheet

Step 3: Fill the outline in with code that will complete the necessary tasks in the outline

Each item in the outline is essential to complete the task. The order is also very important since many steps rely on preceding steps. The following is a more detailed description of how I went about filling in the code into the outline.

1. 'This creates copies of the master sheets

a. Each of the three master sheets has pre set parameters (see screenshot below)

b. Each of the three master sheets has different parameters therefore it is necessary to copy and use all three for the spin offs

2. 'This part selects the right ticker from the sheet specified bellow

a. I broke up the 8,530 tickers into separate worksheets to make the list more manageable. I alphabetized the ticker list and then separated the tickers according to their first letter (see screenshot below)

b. The VBA activates one of the ticker sheets (the “A” sheet for example)

c. The code will then go through a “do until” loop and select the tickers one by one until there are no more tickers in the sheet

3. 'This pastes it onto the recently copied sheets and creates the option chain formula

a. The selected ticker will then be pasted into the three recently copied master worksheets

b. This newly pasted ticker will be used to create the option chain

c. It was a little tricky creating this formula since there are quotes, so it is necessary to have triple quotes where usually there is just a single quote. (see below for example)

*Note: Range “A2” is the selected ticker

*Note: “=BDS” is a formula in excel that performs a data call to Bloomberg when connected to a terminal

d. It takes Bloomberg a few seconds to bring in the requested data. To overcome this obstacle I put in a few wait times so that the sheet would have a few seconds to load the data.

4. 'This filters the option chain and creates subsequent option trade data formulas

a. When Bloomberg gives the option chain it gives all the options not just the ones needed. It is necessary to filter the options chain based on the parameters listed in the sheet. This is done with “Instr”

b. If the option in the option chain contains the correct date then a formula is created that requests the needed data from Bloomberg

5. 'This part separates the sheets into its own workbook

a. This was fairly straight forward. I simply recorded myself doing this with a macro

6. 'This part renames the sheets

a. This was fairly straightforward however it was necessary to splice in the ticker name into sheet name.

7. 'This part saves the newly minted sheet

a. Once again I needed to use the ticker in the saved name.

2.3 Difficulties Encountered

I ran into several roadblocks while filling code into the outline. Here are a few examples:

Problem #1 Bloomberg’s formulas are unique to Bloomberg and difficult to create in VBA

Bloomberg formulas a separate beast from what I was used to dealing with. It became necessary to not only learn the Bloomberg formulas, but to be able to recreate them into the code. I was initially thrown off since the Bloomberg formulas use a lot of quotation makes. In VBA this has to be addressed for the formula to be properly formatted. I overcame this obstacle by visiting with Pro. Allen who helped me get the right number of quotes in order for the VBA reader to properly format the formula

Problem #2 The number of options in a ticker’s “option chain” is not constant between tickers. One might have 75 while another may have 475.

This was a difficult task for me to work around. It would be really simple to work with different tickers if each ticker had the same number of available options. Since that was not the case it became necessary to make a robust code that could handle a variable number of options. Once again Prof. Allen helped me craft the following code to work around this problem.

Problem #3 Bloomberg takes a few seconds to populate the data after you make a data call.

When my subroutine was finished I ran it a few times and it worked beautifully except that none of the Bloomberg values populated! I realized that this was because I didn’t give Bloomberg enough time to populate the data. It takes a few seconds to fulfill a data request. My work around for this problem was to simply put in some wait periods.

What did I learn?

I learned several things while putting this VBA program together.

· I learned the nuances of Bloomberg data pulls

o How to put together a Bloomberg data pull formula

o How to put a pause into my code to ensure the data has time to populate

· I got some experience using loops. We learned these in class, but it was nice to be able to incorporate things that I learned in class into my program

· I learned how to create a program from start to finish

o I realize know just how important it is to spend the time to create a good outline then work on filling the outline with the code

· I learned how to spin off excel sheets.

o I initially thought this was going to be very very difficult, however it was as simple as me recording a macro of me doing it!

· Most importantly I gained a lot of confidence in taking this program from concept to workable product!

Things I wanted to include

· One thing I really wanted to include that I leaned about in class but ran out of time to include was a slick user interface. I really wanted to make a user-friendly user form like we did in the User form project. I created the userform but had a hard time getting the drop down lists to populate and work with the already created code

· I also am not super happy with how I handled the problem with Bloomberg taking so long to load the data. My solution works but it is not very robust. I put in an “Application.Wait (Now()+Timevalue(“00:00:03”) every time Bloomberg was populating some data. As I continue to tweek the sheet I will replace that with a loop that will check to see if the data is loaded. Once Bloomberg sends the data then the code will exit the loop and move on. I discussed this problem with Prof. Allen and he suggested I use the “doexecute” loop. I had a hard time getting it to work…

The Analysis

2011-12-08T22:44:00.000-08:00

Executive Summary

Performing financial analyses require time. If they are desired to be run on multiple companies it can be repetitive. Many financial measurements as well as ratios are already available on financial websites. More complex analyses that are not accessible for free require time to compute for each company. This VBA project automates the process of performing certain analyses.

The program pulls financial data from the web, compute analyses, and formats the excel worksheets so the user can follow the assumptions and calculations made. The program is designed with one company in mind and then computes all of the same analyses for the three most comparable firms. The data is pulled from Yahoo! Finance, while the comparable firms are located on Google’s finance website. The program takes several minutes to run depending on the speed of the user’s internet connection.

The user simply inputs a stock ticker of a company that they want to look at and then waits while the program runs. Over 20 web queries are made to pull in the necessary information while the user waits. Screen updating has been left on so that the user doesn’t think the program stopped working and exits Excel. Key financial figures are pulled into an inputs tab for the company and then the excess web query tabs are deleted. Four analysis tabs are created as the final product; valuation, profitability, solvency, and accruals. The computations on these tabs allow the user to view the formulas and trace the inputs.

-Michael Jensen-

http://files.gove.net/shares/files/11w/mjensen7/The_Analysis_Write_Up_Michael_Jensen.pdf

Stock Comparison Analysis

2011-12-08T22:42:00.000-08:00

The program I wrote is to help conduct stock comparison analysis. I personally like performing stock research and I am going to be a research analyst after graduation. Therefore, this is an area of high importance to me.

During my stock research process, I find out that peer comparison takes a considerable amount of time because I have to put a lot of ratios and numbers for each comparable company from different online data sources. A good peer analysis often requires as many comparable companies as possible. But the time it takes to get all that information seriously limits the number of companies I would like to pick.

Some sophisticated database like Bloomberg makes it easier. But I wish I can just use my own computer to do a fast analysis, instead of having to come to campus and use the Bloomberg terminal there. In addition, Bloomberg’s peer selection is somewhat lousy. I wish to be able to select my own peer tickers.

Therefore, this program is designed to automatically pull data I need for each company ticker inputted from different online sources and format the data in a user-friendly way for further analysis.

http://files.gove.net/shares/files/11w/cissy19/Final_Project.xlsm
http://files.gove.net/shares/files/11w/cissy19/Final_Project_WriteUp__Cissy_Xie.pdf

Comment Finder

2011-12-08T22:35:00.000-08:00

Executive Summary

Background:

This semester I have been working on a website idea that focuses on user comments. There are websites that focus on the news, pictures, videos, jokes and micro blogs, but none really on comments. Many people spend a ton of time reading and writing comments, but all the commenting systems used now could be improved. We believe people want to hear and be heard, and that there is a ton of good comments that could be crowd-sourced in the right system.

This website would seek to be that improvement by allowing to users to comment on anything (videos, articles, pictures, etc). Currently we are in the validation stage working on finding out what would add the most value to users of this theoretical website. While experimenting with different comment filters, voting systems, rankings and incentive systems, we need to have a database of comments to use for tests.

System Overview:

The purpose of this system is to have a convenient way to scrape highly scored comments off of websites and store them for use in testing and providing content for the future website. The two websites to be scraped are YouTube and Imgur. Both are extremely popular websites that host videos and pictures respectively. Each of them boasts a large and active user comment system. Some of these comments are entertaining or enlightening, while most are not. This system does the following:

· Accept URL’s from either website as inputs.

· Let the user know if the webpage has no comments.

· Parse through the data on the webpage to find the text, author and score of each comment.

· Filter the comment text to eliminate profanity and unnecessary links.

· Return on the data sheet the title, link, and filtered info for the top filtered comments of each webpage.

· Clear and automatically reformat the database at a click of button.

Automate Report Generation Project

2011-12-08T22:14:00.000-08:00

Introduction

This project is based on a real business problem that is related to an accounting task at College of Fine Art Center at BYU. Every month accounting manager will need to analyze reports generated from TotalTrans, which includes all income and expenses from the beginning of the year to the date of the completed month. Because this report includes so many transactions (more than 3000 transaction as of September 30, 2011), performing an analysis involves many procedures to break down the transactions into each unit, and then each account and corresponding individual subclass. Most of the procedures are routine, having one click or as few clicks as possible to generate the report will be very effective.

Executive Summary

The purpose of this project is to break down all transactions by “Operating Unit”, “Account”, “Subclass”, and lastly by “Month”. Operating Unit is department; Account is the category of transaction (such as supplies, rental expense, salaries); Subclass is the separation of discretionary budgets, faculty research funds, or special project expenses.

The user can specify what operating unit, account, subclass, or month for the report, it will then automatically generate a chart to show the profit and loss for the specific unit and calculate the subtotal for the report. A userform has been created to allow user input search keywords and to allow choosing “Quick Find”, “Quick Totals”, and “Generate Report”. The code that was written is flexible because many functions are created to allow user to modify more easily. This is expected to save about 6-10 hours monthly for the analysis of the TotalTrans. Because of flexibility of the code, most of them will be applicable to use in other reports generated monthly; therefore, with consideration of all other reports and this TotalTrans report, it is anticipated to reduce approximately 10-15 hours per month for generating reports.

http://files.gove.net/shares/files/11w/kc241/Final_Project_Documentation.pdf

http://files.gove.net/shares/files/11w/kc241/Final_Project-Final.xlsm

P.S. Most of the information are modified and replaced with other incorrect data.