Import Online Banking Transactions – VBA Final Project

Shawn Tabrizi's Executive Summary

The system that I built for this project is meant to automate and simplify the process of consolidating bank transactions into a P&L statement, and a statement of tax deductible expenses for the year. Rather than solving a business problem, this solves a personal financial management problem by partly automating the process of producing monthly income and expense statements and a tax deductible expense report.

The motivation for building this system comes from my own personal financial management habits. For several years, I have tracked and summarized my family’s finances. For us, a P&L statement is helpful for two reasons. First, it gives us the format to budget our spending in a way that makes sense for our family. Based on projected cash flows, we can forecast debt repayment plans, make purchase plans, and make other crucial decisions. Second, it’s very helpful to see how our actual spending habits compare to our budget. Without this information, we would likely be spending much more money than we do. Lastly, aggregating our tax deductible expenses throughout the year into a simple and streamlined report aids in simplifying tax reporting and record keeping.

The Intent of the Code

When I sought after this project, I wanted to organize the code into three components, each associated with a button on the “Transactions” tab of the workbook. The Import Transactions button is meant to do exactly that. The goal was to make this sub procedure completely customizable. If I only wanted to pull transactions from one account, I should have that option. If I only wanted transactions for ten days, I should be able to do that, and so on. The Edit Transactions button is meant to edit or add to the imported data. One poignant example is the need to designate tax deductible expenses, as online banking services do not have that information. The Generate Statements button is meant to use the imported transaction data to aggregate the transaction data among the other two sheets, “Cash Flow” and “Tax Deductions.” The optimal system would do the same thing for multiple years.

What the Code Does

As written, the only component that works out of the three above is the first. The reason for this is that I grossly underestimated the scale of this project. Because I wanted to make the process of importing transactions so flexible, there was over 50 hours of coding (from a rather inexperienced coder, mind you) that went into that component alone. So, though I intend to complete the project with time, the system as it stands only imports transaction data.

Learning Points

As I spent so much time automating a spreadsheet that is really for my use only (though any chase.com user can use it with a little customization), I wondered if I would have any ROI. However, much of that 50 hours of work was spent going through a problem-solving process that is unique to programmers, I think. Mostly, this project ended up being a refining immersion into the world of programming that I believe will benefit me personally, as well as in my career.

Links to Project Write-up and Workbook

http://files.gove.net/shares/files/14w/stabrizi/Shawn_Tabrizi_Final_Project_Write-up.pdf
http://files.gove.net/shares/files/14w/stabrizi/Shawn_Tabrizi_VBA_Final_Project.xlsm

BracketCity: Testing a Social Engagement Tool

I’m working with a friend of mine on a business idea. Our business is based on the idea that social media is simply a social engagement tool, or a method to increase or smooth human social-interactions. On that premise, we’re developing BracketCity. BracketCity is a simple method for people to share and complete brackets with their friends. But the brackets behind BracketCity are meant to go beyond what you typically see during March madness. Using the BracketCity app, you can create a bracket of anything you want to share with your friends. The idea is users can generate brackets which appeal uniquely to their friend-group, like which friend is most likely to be a millionaire or who’s the best looking girl at your school, etc.

For this project, I have built a prototype, which will be used for testing the marketability and usability of our bracket business. BracketCity relies heavily on creative user-generated content and our main focus with this prototype is testing out several brackets, which we are putting extra effort into.  Essentially, we’re testing whether or not great content will get higher response rates and attract more users. Additionally, this prototype will be used as a minimum viable product, we’ll ask for feedback from users on the format, layout and flow before programming a prototype online or in the IOS/Andriod environment.

• http://files.gove.net/shares/files/14w/nab47/Nate_Barnes_BracketCity_Writeup.pdf

• http://files.gove.net/shares/files/14w/nab47/BracketCity.xlsm

Colonial Heritage Foundation database and user form - Oertel Sparks and Mike Weber

Colonial Heritage Foundation (http://colonialheritage.org) is a recently established 501(c)(3) with the goal of preserving the history of America's founding. They engage in a variety of activities such as presenting at schools, coordinating reading groups and historical reenactments, and teaching the occupational skills of early America through internships and apprenticeships. The foundation is positioned to launch fundraising initiatives to expand their reach. This project was a coordinated effort to help design a skeleton database and create sample forms to be used for volunteers to be able to interact with and update the database.

Paper: http://files.gove.net/shares/files/14w/mweber56/VBA_final_project_paper.pdf

Excel File: http://files.gove.net/shares/files/14w/mweber56/interest-final.xlsm

Shorting Stocks with the Beneish Model

Executive Summary

The Beneish Model is a tool developed by Professor Messod Beneish back in 1999 that identifies public companies who have (abnormally) manipulated their earnings. For my project, I decided to develop a tool that would calculate the Beneish “M-score” on all of the stocks in the S&P 500, and then summarize the stocks with Beneish scores below the given threshold that show manipulated earnings. With this knowledge, I then plan to short those stocks.

This is of interest to me because I’ve traded stock options before, and have been trying to get back into it this year. Shorting stocks is where I have the most experience and have had the most success, so this tool fits right into my trading philosophy.

• http://files.gove.net/shares/files/14w/jtl43/Beneish_Model_Stock_Shorter.xlsm
• http://files.gove.net/shares/files/14w/jtl43/VBA_Final_Project_-_Jake_Lewis.pdf

Neal Armstrong- StylishShopper Dropbox Pictures

The Executive Summary
My Project was to create a database for a clothing distribution company called “StylishShopper”.  The company is a small five person company that contracts designers in multiple cities to create clothes, then the company sells them to high end retail chain across the world.  These are higher quality clothes so the owner wants a nice system that shows pictures of the clothes that both the designers and sales people can see them.  Sales are organized by the buyers.  The sales team has had a hard time working with photos in excel and google docs.  The photos are low quality and create an awkward page layout when just pasted in the excel document.  Also, filtered could not be used well because of the formatting.  The spreadsheet also contained lots of data and could not be emailed easily.

Stylish Shopper is looking for a program that can track orders and share photos taken by any person in the company.  I have created the “StyleShopperDB” which will allow the company to share its information quickly and easily. This spreadsheet is created with the mobile users in mind.  The form is stored in the Microsoft OneDrive and can be accessed from any mobile device.  This was an important requirement from the company. 

The Overview
StyleShopperDB is a data entry system connected to a cloud based file sharing folder (dropbox) created to enable the company to communicate effectively across all types of devices. 

Step 1. The designer creates a piece of clothing, takes a picture and uploads it to the dropbox file on either their smartphone, tablet, or computer.Step 2. The designer names the item by changing the name of the file to match the item number/name. Step 3. The sales person logs into dropbox and sees the new file and can show it to buyers. Step 4. They buyers place their orders and the sales person enters the order into the spreadsheet.Step 5. The seller and the operations/shipping team can see the order and see the piece of clothing sold and the quantity and destination.Step 6. Any employee can open the excel file and see upcoming orders and quantities.Step 7. Sales are made and the company grows and then hires Neal as the head of operations where he continues to build an extensive database with multiple pictures, email reminders, extensive grouping, forecasting models, sales data, and much much more.  

• http://files.gove.net/shares/files/14w/neala/StyleShopper_WriteUp_Neal_Armstrong.pdf
• http://files.gove.net/shares/files/14w/neala/Neal_VBA_Final_V2.xlsm

Peer to Peer Lending: Loan Filter

Jared Kulbeth

MBA 614 Final Project

Executive Summary

Background:

This project addresses investing in the peer to peer lending industry.  Peer to peer lending involves a borrower funding a personal loan by way of multiple investment lenders. Borrowers are matched with lenders through peer to peer lending websites.  Borrowers generally get a better rate than credit cards or traditional banks.  Investors generally get a higher return than bank savings products and incur less risk than typical investment vehicles.    

I’m deriving this project from a lender/investor perspective.  The company I’m choosing to use is called Lending Club.  The site lists all the loans submitted by applicants meeting Lending Club’s minimum loan criteria.  Loans are assigned grade values based on several risk criteria such as credit score of borrower, loan amount, term of loan, etc.  Lower risk loans are charged lower rates which generally means lower return for the investor.  Higher risk loans incur higher lending rates with the corresponding chance of greater return for the investor.  The lender then looks through the list of loans and selects which loans in which he wishes to invest.

Project Overview:

At any given time there are well over 1,000 different loans to choose from at Lending Club.  My project addresses the time consuming task of browsing all these loans to the find the loans which meets the investor’s desired criteria for investing.  The user will be able to login to the website and download the available loans to a spreadsheet.  The user will then be given options for filtering the loans.  The final result is a list of loans which meet the lender’s investment criteria.

• Final_VBA_project_write_up.pdf http://files.gove.net/shares/files/14w/jwk23/Lending_Club_project.xlsm
• Final project code http://files.gove.net/shares/files/14w/jwk23/Lending_Club_project.xlsm

Dan Stafford Final Project: Basketball Japan League Player Efficiency Ranking Tool

Executive Summary

The Basketball Japan League (BJL) is professional league and the purpose is to provide entertaining basketball for fans, and earn money for the teams. It was founded in 2005 with 6 teams; the league has expanded rapidly, and today there are 21 teams. Due to rapid growth the game rules, league administration, and head coaches are constantly changing. Following the end of the 2010-2011 season 13 of the then 16 head coaches lost their jobs, including the coach of the championship team.

What does it take to be successful as a coach? The NBA, which is the world authority on professional basketball is moving increasingly toward analytics—the statistical analysis of success drivers—in order to maximize winning. NBA Coaches that are successful pay attention to analytics to guide decision making. NBA.com and ESPN.com both list efficiency ratings (EFF) and performance evaluation ratings (PER), in addition to clutch ratings and other important measures. The BJL website contains nothing beyond basic player stats. Coaches in this league are blind to analytics regarding their own team as well as their opponents.

What I have built is an EFF ranking system for the BJL. This can be used by a coach to see the most effective players on their own team as well other teams. This data is useful in making decisions about playing time, line-up dynamics, and as a starting point for studying other trends that go beyond data such as strategy formulation.

The EFF ranking system pulls cumulative season statistical data for every player in the league, and then runs that data through a formula that adds positive contributions and subtracts negative ones to produce a raw contribution number or EFF. The EFF is then divided by minutes played to give the efficiency per minute (EFF/MIN) rate, this rate can be used to compare players across the board. The final step is to rank the players in order from highest EFF/MIN to lowest. 

• http://files.gove.net/shares/files/14w/dstaffo2/Final_Project_Dan_Stafford.pdf
• http://files.gove.net/shares/files/14w/dstaffo2/BJL_EFF_Ranking_Final_Project_Dan_Stafford.xlsm

WEB QUERY: ORGANIZING ONLINE CLASSIFIEDS

Executive Summary

I love spending time in the outdoors and finding a good deal. Since I grew up in Utah, I have used KSL classifieds to buy and sell several items. It is a unique website that connects buyers and sellers. It also has more users and greater reliability than Craigslist. For this project, I created a webquery that could help me search for items using the search tools of KSL. I plan to use this to collect information on postings I am interested in such as, backpacking equipment, ski tickets, and classic Nintendo games. Because I have a background knowledge in this equipment and typical prices, I could visually sort out the good buys and make purchases for my own use or to flip.

This project would allow a user to search the KSL classifieds for any specific item. The user could edit their search results and have displayed for them all the postings meeting their search requirements. If a user had a specific interest in a certain item, they could click on a link to the right of the post to access the internet and find more information. I feel this is a great solution for people that want to quickly review and compare postings on the KSL classifieds.

Alex Alard

• http://files.gove.net/shares/files/14w/ddeserae/VBA_Project_Write-Up_Alex_Alard.pdf
• http://files.gove.net/shares/files/14w/ddeserae/Classifieds_Search_-_Alex_Alard.xlsm

BYU Study Abroad Itineraries

EXECUTIVE SUMMARY

Background

The BYU MBA Program offers at least two opportunities each year for its students to travel abroad.  These international experiences provide access and exposure to global businesses and cultures, which maximize education for all participants.  The MBA Program hopes these trips motivate students to focus on a global career and earn the BYU sponsored Global Management Certificate.  One of the requirements to obtain this certificate is an international experience; therefore, a plethora of BYU MBA students have gone on an MBA Study Abroad trip to earn the certificate as a step towards a global career.

Project Purpose

After traveling on the MBA Europe Study Abroad trip in January 2014, information, including travel (including flights), daily tourist excursions, business visits and names of business contacts was not distributed to those traveling on the trip.  This lack of information put the students at a distinct disadvantage, specifically when meeting with businesses and contacts.   We were not able to research the companies/individuals prior to meeting with them; therefore, questions posed  during meetings were haphazard and showed a lack of research and knowledge.  This project was developed to combat this lack of knowledge.

The ultimate goal was to build a project that can be adapted for use on future MBA Study Abroad trips so the participants obtain critical information about business visits and activities.  This project could potentially be a small part to an MBA student making a favorable impression on a business leader, while on a Study Abroad, that leads to a job offer in a foreign country.

Project Overview

The project builds individual itineraries for each participant on the MBA Asia Trip, which takes place in April and May 2014 and includes visits to five countries over 21 days.  The itineraries contain daily, and sometimes hourly, details for each country that will be visited.  All names of companies, business leaders, networking contacts as well as logistics like flights, hotel information, transportation from airport to hotel, when meals are provided.  These components are built into one itinerary that is saved as a spreadsheet and eventually shared with the participant. 

Project Solution

The key to the success of the project was obtaining the correct information.  The collection of all of the necessary information allowed the itinerary to be built using an automated system.  The automated system feeds details, specifically flight numbers and departure/arrival times, into the specific country itinerary and then produces and saves an individual spreadsheet into a desktop folder for easy organization and dissemination.  Now each participant has all of the information necessary to research companies, a better understanding of what to pack, when free time is available and when they have a scheduled flight.

Final Project Paper
Final Project Code

Apartment Comparison Tool

For the past 4 years, my wife and I have been managing a condo here in Provo.  One of the challenges we face every year is deciding what is the appropriate rent and security deposit to charge our students for the upcoming year. Historically, we have had to log onto the BYU Off campus Housing Portal, search through the different listings, look up each listing’s individual information, and then see how that apartment compares to our apartment. This process not only takes several hours, but it is extremely tedious.

This semester as I prepared to go through this process once again, I realized this was the perfect opportunity to automate something using VBA to try and save time.  I created the Provo Apartment Comp Tool to help automate and simply the comparison process.  The tool logs onto the BYU web portal, navigates to the listings page,  searches for listings that meet the correct criteria, pulls the data into excel, and then re-formats the data in a simple and easy to view comparison page.






Writeup: http://files.gove.net/shares/files/14w/pflclub/Final_Project_Writeup.pdf
Rental Comparison Tool: http://files.gove.net/shares/files/14w/pflclub/RentalCompTool.xlsm

RECIPE BOOK CREATOR AND MEAL PLANNER

Business Description:

Amy Collier is a registered dietician who recently graduated from Brigham Young University. During her studies, she had access to expensive proprietary software that allowed her to create recipes, to plan meals, and to create automated grocery lists. Now that she has graduated, she does not currently have those resources when she consults with clients. Buying the software is not currently an option with her budget, so she needs a functioning substitute to be more effective at her job.

At home, she likes to pick and choose recipes from a large number of physical recipe books and online sources. Because of this, it is often difficult to find specific recipes and remember which recipe book or file folder included that particular recipe. Planning meals and creating grocery lists can also be a challenge. Amy suggested that I make one program that would meet all of these needs.

Solution:

In order to address all of these needs, I created “RecipePro” in Excel VBA code.

Creating a recipe:
I created a simple and easy-to-use user interface that allows the user to enter in recipe information and pictures which are then transferred to a separate saved worksheet with a professional format.

Recipe Book Generator:

Once the user has created a few recipes and saved them, the recipe book generator allows the user to pick which saved recipes should be included in the new recipe book. The user can also choose to customize a simple cover for the recipe book. Once the user is finished, he or she can save the workbook for later use.

Meal Calendar:

The user has the option to create a calendar and assign specific recipes to different meals on a particular day. There is also the option to import and edit a previously created calendar.

Grocery List:

Once the meals are planned for the week, the user can print out a grocery list of the items needed to make the planned recipes.

Final Project Write-up
RecipePro

Suruchi Agnihotri_OD&L Repository for M&As

Executive Summary:

Organization Development and Learning is a Human Resource Specialty which is a deliberately planned, organization-wide effort to increase an organization's effectiveness and/or efficiency and/or to enable the organization to achieve its strategic goals [1]. Over my internship, I and my team were asked to build a tool for this department which will be used by HR people assigned to a Merger/ Acquisition (M&A). M&As are complex; although they happen all the time, yet study after study puts the failure rate of mergers and acquisitions somewhere between 70% and 90% [2]. HR Processes play a big role in the success of M&As. With the organization that I interned with, the problem was there was no standard repository to guide HR generalists, HR Directors, or even business managers to guide through the process. Everyone acknowledged that the issue is important and needs to be handled, and HR people involved in previous M&As had made tools, gathered best practices for themselves, but they were not managed centrally, and learning was not being shared. My internship team created a model of the ‘would be OD and L repository’ after interviewing and collecting data from more than 50 people. I recreated the toned down version of the same repository.

1. Best Practices repository

Important ODnL processes in M&As are 1. Change Management 2. Culture 3. Organizational Design 4. Communication

M&A process timeline may be segregated as: 1. Due Diligence (Pre-integration) 2. Integration Planning (Pre-integration) 3. Month1(Year of Integration) 4. Months 2-12 (Year of integration)

These two dimensions are captured in the tool using a 2*2 matrix format. The processes that fall at the intersection of these dimensions are fetched and displayed from the already existing database. See this figure for the sample. Only the first row has actual phases as there might be some legal bindings

**Implementation also has been done for the first row (Change) only.

2. ‘Submit Best Practices’ tool

After an M&A is finished, it is required for the HR personnel involved in that particular project to submit all the best practices or any new learning.  The ‘Submit Best Practices’ Tool provides an interface for the HR personnel to accomplish this task. The person in the role submits best practices through the tool, which got to a ‘Draft data’ sheet, which is later analyzed by the Repository admin. Repository admin is notified via email on any new submission. The process of looking at submissions and adding to actual excel database is a manual process (looking and judging the validity of submission, removing duplications, adding in proper format). It is out of scope of this project.

3. ‘Search Merger Information’ tool

When a new HR person is assigned to an M&A project, he/she might need information about the previous M&As of the company, and the people involved in that. This is a very useful process for learning. Search merger Information tool lets the user search on the basis of Region, Merger Type, Year, or a combination of these. The search returns corresponding M&A names, and on selecting a merger name and searching further, Merger Details are shown. No editing is possible on this tool.

Links:

http://files.gove.net/shares/files/14w/sur14/ODnL_repository_for_MnA.xlsm

http://files.gove.net/shares/files/14w/sur14/ODnLRepository_for_MnA.pdf

Brooks Benson

The “Option Value Calculator"

Every investor and every MBA student will eventually run into the problem of how to value a stock option.  The former Director of the BYU MBA Program, Craig Merrill, says that every MBA student must learn how to value a stock as part of his or her MBA experience.  More importantly, however, Professor Merrill says that every MBA student must learn how to value a stock using a binomial pricing model.  But, there is more than one way to value an option, including the use of the Black-Scholes formula, which complicates the task. 

I have taken several classes that required the students to build either a Black-Scholes or a binomial model to price options.  These basic models have completed the task at hand, but were clunky and did not allow for variable inputs.  For example, often the models would not allow the number of sub-periods before expiration to be altered.  This is an important variable in any option calculation.  Moreover, these models would only value a call or a put independently, but not both together for comparison.  In the end, these models were hard to use and only spewed out limited data.

Most recently, Professor Thorley, of BYU’s Finance Department, asked the students of his Investments class to build a model that would value a European call option.  As a student of that class, I decided to take the project much further and build a model using his template and VBA that would value both a put and call option for the same stock.  By clicking on a single button, the “Option Value Calculator” will allow you to enter the necessary inputs into a user form, and by clicking “okay”, the model will automatically build out the binomial trees for both the call and put option.  The model will also generate the put and call values using the Black-Scholes formula, allowing the user to compare values for each method.

As a result of the model, the complex task of pricing an option can be completed at the click of button.

• http://files.gove.net/shares/files/14w/bbenson8/Option_Values_Spreadsheet_Brooks_Benson.xlsm
• http://files.gove.net/shares/files/14w/bbenson8/Option_Value_Model_Brooks_Benson.pdf