Automated Web Stats Collection

VBA Final Project: Automated Web Stats Collection for Adobe Industry Strategy Group

The Industry Strategy Group within the Omnture Business Unit at Adobe consults with the management teams of strategic clients to help them know how to make the most of their digital business. These engagements are referred to as Strategic Value Assessments and include a competitive analysis of the client’s top competitors which involves gathering publicly available data about website traffic/conversion metrics to identify sub-category trends and benchmarks. The purpose of this tool is to automate process of gathering and analyzing those data. When I began doing this work this summer it normally took about two days to gather data and perform a complete analysis. By the end of the summer I could complete the task in two hours. This tool is able to do most of the work in two minutes.

• http://files.gove.net/shares/files/10f/tyrwhite/VBAProjectTylerWhite.pdf
• http://files.gove.net/shares/files/10f/tyrwhite/AutomatedPublicWebTrafficDataPullVBA.xlsm

Add A Class

I didn't get into a class that I wanted on the night of registration back in October. I was super bummed that I didn’t get the class and I decided that I would change my final project for VBA and create a program that would help me to add the class. The program allows users to navigate through BYU's Route Y and course registration process through a series of drop boxes and find the class that they are looking to add. I did not finish the part of the program that will create continue to check if the class has been dropped and if the class is dropped, then the program will then add the class. I plan on finishing this part of the project so that I can get the class that I want to get before graduation.

• http://files.gove.net/shares/files/10f/miw3/Final_Writeup.pdf
• http://files.gove.net/shares/files/10f/miw3/Add_A_Class.xlsm

Access Wells Fargo and American Express Accounts

This project is intended to help to organize the finances of a person with accounts in the Wells Fargo Account and American Express credit card. It allows the user to enter his user name and password, define the time range desired and then download the statements and organize it month by month including totals. The program will allow the user to do in a couple minutes what has been done in about 30-45 minutes and will facilitate all sorts of analysis he will be able to do with the data arranged in an organized and neat way. This will make easier for him to track his financial behavior and to better make financial decisions for the future.

• http://files.gove.net/shares/files/10f/mribeir0/Accessing_Bank_account_-_Write_up.pdf
• http://files.gove.net/shares/files/10f/mribeir0/Final_Project.xlsm

End-of-Semester Reconciliation

I am an accountant in BYU’s Student Leadership department. One of my responsibilities includes reconciling all of the expenses we approved throughout the semester with all of the expenses that actually came through on our reports. Our department does most of the activities put on by the university, and it is mostly run by students. By the end of one semester there are hundreds of transactions to go through in ten different areas, handled by hundreds of students. This job is very tedious and can consume up to weeks of time.

This program automates the semester-end reconciliation process by matching budgeted expenses with actual expenses and organizing them into four different categories: 1) already-reconciled and completed expenses, 2) expenses that need to be marked as reconciled, 3) budgeted expenses that never occurred and should be voided, and 4) actual expenses that occurred but were not budgeted for, and so must be documented in the database. Further review and processing is required for the last three categories of expenses, so the program also sorts them into separate spreadsheets for quick analysis.

http://files.gove.net/shares/files/10f/mjbobson/End_of_Semester_Reconciliation.xlsm

http://files.gove.net/shares/files/10f/mjbobson/Project_Write-Up.pdf

Discounted Cash Flow Model

Executive Summary

An essential part of working in the financial services industry is performing valuation on projects, assets, or entire firms. This allows corporate management, investment bankers, private equity managers, and other financial professionals to evaluate the potential profitability of a financial undertaking. There are many different valuation methods, but perhaps the most popular and widely-used method is the discounted cash flow model.

The discounted cash flow model, or DCF, projects out cash flows over a number of periods, then discounts them back to the present using an appropriate discount rate. Essentially this process allows you to determine the net present value (NPV) of the streams of cash flows to be expected from the asset or company you are evaluating. In other words, you are able to determine what it is worth and whether is a worthwhile investment.

This program is designed to assist in the process of importing data from online databases and building a DCF valuation model. The model was created for those familiar with Microsoft Excel and financial modeling since flexibility and sensitivity analysis is a crucial part of creating a DCF. Valuation is just as much an art as it is a science. My goals in writing this program were to provide users with data imported from the Internet to guide their assumptions, create an outline that is easily understood and manipulated establish checks set to trigger if the user has skipped a step or should be aware of an abnormal variable, and most importantly, automate the valuation process as much as possible to maximize a financial analyst’s time and resources.

Discounted Cash Flow Model

DCF Model Write-Up

Classified Ads Deal Finder

Executive Summary

I, like many people, enjoy browsing online classified listings and looking for a good deal. One problem I have noticed while looking at items on these websites is that I don't really know if many of the items listed are a good deal because I do not know what they may be worth. I have found that using the internet is a good way to overcome this problem, thanks to the existence of comparison shopping websites like Nextag.com and Google Shopping. Unfortunately, using these websites to find the value of individual items can be time-consuming, and I have found that I usually only do it for items that I am already familiar with. This prevents me from finding deals on products that may be similar but have a less familiar brand or model name.

To resolve this problem, I determined to make a program using VBA and web queries to pull a list of recently listed items and look up their values on the internet. Since this is done by the computer, it is much faster than searching manually and allows me to more easily evaluate the listed price. For ease of evaluation, I have the results display in a spreadsheet along with the discount being offered from the average internet price. These discount percentages are highlighted in different colors to illustrate which listings are the best "deals."

Write-up

Project

BYU Auto-Registration Program

Every semester, BYU students bemoan the fact that they have to stay up for all hours of the night to register for the classes that they want. Many have found that if they don’t click the “register” button at exactly the right time, midnight on the dot, their efforts are in vain, and they don’t get the classes that they wanted to. For my VBA project, I chose to create a program that will allow a user to automatically register for classes so they don’t have to stay up until midnight to register. My program allows a user to select which courses they want to register, and then after the user clicks on “register at midnight”, the program will wait until midnight, and the second that their computer clock hits the stroke of 12, the program will register for those classes for the user. It also stores the username and password encrypted with a basic algorithm so that the user does not have to log in over and over again.

http://files.gove.net/shares/files/10f/swh24/Final_Write-Up.pdf

http://files.gove.net/shares/files/10f/swh24/BYUClassRegistrator_v1.6.xlsm

Analytic Hierarchy Automated Decision Making Process

The first semester of my graduate program in Public Administration I took a Decision Analysis class that used Excel as it’s primary tool to teach us how to make informed future decisions using what is called the Analytic Hierarchy model.  The model involves entering in the alternatives and objectives you have for a decision, then comparing both the alternatives to each other and the objectives to each other to see why objectives are more important and which alternatives are more desirable when compared.  With that information and through a bunch of formulas, a summary page is created that shows which alternative wins most often in a series of trials.  It is rather tedious to recreate all of these formulas and tables each time you want to make a decision, so I created a VBA project that automates the process.  Using a series of buttons, user forms, and input boxes, I take you through the process of entering in the pertinent data and then Excel automatically crunches the numbers and comes up with a summary page.  

Writeup: http://files.gove.net/shares/files/10f/jolenaj/Final_Project_Overview.pdf

Project: http://files.gove.net/shares/files/10f/jolenaj/Final_Project_AHP_Automation.xlsm

 

Rotation Recommendation

Executive Summary

My father, Robert owns a Baskin Robbins franchise in Scotland. He is always looking for new ways to improve the business, and his income. The store is small, but has a prime location close to the city center. Most of the day-time customers are passing the store on foot (usually returning from town). The night-time customers almost always drive to the store. The store currently has 10 employees, and most of them are students.

The weather in Scotland is very temperamental, and it plays a big part in how much business the store generates. Typically, demand goes down during inclement weather, but the labor supply stays the same. Also, when it is unusually hot, large queues will form and customers become impatient and sometime leave. The problem I hope to remedy is over and under-staffing. If Robert could better predict demand for ice-cream, he could avoid over and under-staffing.

Robert does periodically check the weather, but he does not gather enough information to persuade him to adjust the staff rotation.

The VBA program which I designed pulls together weather data from multiple web sources. The program standardizes the data and compiles a weather summary. This summary averages out the forecast temperatures and chance of precipitation from the different web sources. The summary is then translated into staffing recommendations. These recommendations are obtained using thresholds for varying weather conditions. The user will have the option of viewing a 5-day forecast and also a 24-hour forecast.

Robert now has a toll that can improve the efficiency of the store, improve his profits, and take a process that would have taken him 20 minutes down to a few seconds.

Files:

Write-up: http://files.gove.net/shares/files/10f/bgoldie/WriteUp.pdf

Program: http://files.gove.net/shares/files/10f/bgoldie/Rotation_Recommend.xlsm

Mutual Fund Assistance Model

This Project mainly deals with selecting mutual funds, an asset class that gives instant diversification to the average investor. For those taking BusM418, mutual funds are an integral part of building your Personal Financial Plan. Seeing as their are 1,000's of mutual funds, it can be hard to tell which is best for you. Students are encouraged to use Morningstar, a financial services data provider, to screen mutual funds by your personal criteria. This narrows your search substanstially. But what if you still have hundreds of mutual funds to choose from? Do you simply look through all of them and then choose randomly? No! You use this program to aggregate all of the data, and then filter and sort according to your desire. This project is designed to aggregate all of the mutual funds that have made it passed your screening process, and put in a spreadsheet so the user can manipulate what they like to see, what they don't like to see and sort according to their preferences. My goal of this project is to help students make a more well-informed investment decisions by being able to see all of the information together in hopes that they may earn a good return on their mutual fund investment.


Writeup:
http://files.gove.net/shares/files/10f/tmoody2/VBA_FInal_Project_Writeup.pdf

Project:
http://files.gove.net/shares/files/10f/tmoody2/Final_Project_Isys520_5.xlsm

YSA Tracking Program

            I live in Bluffdale, Utah. Currently I am the Executive Secretary in the Bluffdale 12^th YSA ward. There are several YSA (Young Single Adults) within the stake that are not currently active in our ward. They may be attending their home ward, but many are not attending church at all. The Stake was required to call Shepherding Couples in each of the wards to minister to these individuals. There have been several difficulties in coordinating our ward’s efforts with those of the other wards in the stake. Another difficulty encountered is keeping everyone up to date on which individuals need to be ministered to. Individuals are constantly moving out or coming into the stake. Many times we find individuals who have left, but have not had their records sent to a new ward. If an individual is not attending our ward, we may send their record back to the family ward to be ministered to there. Keeping track of the changes and needs of each individual and communicating them back and forth is something that would be a full time calling in the Church. We do not have the luxury of having a person assigned to this; therefore, we needed to develop an efficient way of gathering and dispersing the information to all individual involved.

            In order to more rapidly determine which individuals were under who’s stewardship I developed a program which would use the information from a stake custom report and generate a report for each ward alerting them to changes in their ward. These changes include new members under their stewardship, members who are no longer under their stewardship, and changes to individuals who are currently under their stewardship. For individual changes, we look at a few important items, namely: phone numbers—cell and home, email address, living address, and assigned home teachers.

            Beyond just alerting each couple to the details of the individuals that they are ministering to, we wanted to provide a way that they could track their progress, ensure that an individual is progressing, and ultimately report and be held accountable for their ministering. In order to facilitate this we developed a system similar to the missionary forms used for tracking investigators. The form we developed is designed to be used on each individual. It provides a location to write details about each individual, activities and goals to accomplish, and a place to record the results of each visit. This allows for each couple to report individually on each person under their stewardship. It also allows them to easily pass the information on to another person should they be released and a new couple called. These forms could be filled out with each person’s information manually, but that would be time consuming. In order to make it easier for the couples, the program will fill out the forms for each individual in their ward. On a monthly basis it will send them new forms for the new individuals who have moved in. They simply need to keep a binder with the forms, or maintain the workbook on a computer, and add or remove individuals as they come and go.

            Although there is no automation in this portion, there is also a form designed to aid in the communication between the couple and the bishop, or between wards. It can be filled out after each visit to report actionable items that need to be completed by others. For example, if a couple visits and individual, and a visit from a member of our ward would help them, they could write the individual’s information and give some details of what we can accomplish in a visit. They can then pass that form to us. We will assign someone to visit the individual or complete the actionable item. Once complete we can use another form to report the results of our visit back to the couple, we may also include other visits or information we gain relevant to individuals in that ward.

If you ever have any questions feel free to contact me at wmorris(the symbol you get when you press shift and 2)byu.net 


Files:

The spreadsheet can be found here:
http://files.gove.net/shares/files/10f/wmorris/YSA_Tracking.xlsm 
The write up can be found here:
http://files.gove.net/shares/files/10f/wmorris/Final_Project_Writeup.pdf
Other files required are an export from MLS. As this contains personal information for several people I will not be uploading it. If you do need it, I can try and create some dummy data but this will not be public either.

Investment Analysis Calculator

Executive Summary

Problem

I have a friend that works in the oil and gas industry, where they market natural gas and crude oil. They use hedges to protect against the volatility of the market for such commodities. One problem they have is that the market reacts so fast to different stimuli that to calculate all of the variables and make a decision to sell of hold is very difficult. Their current software is not very user friendly and is not performing as they would like. I decided to create a simple, easy-to-use VBA model that would compare certain criteria, established by the user, against current market data and give a decision. This program was to be the basis for a piece of software that I could take to market and sell to the companies in the oil and gas industry. However, when I contacted my friend to get the criteria that they use, I was told that it was proprietary and due to confidentiality reasons I would not be able to acquire what indicators they used to base their investment decisions. As I took a second look at what I wanted to create, I realized that the VBA model I wanted to build could also be used to compare stocks, bonds, ETFs, and other commodities.

Excel File
Write up

Comparable Company Analysis and the WACC

At Zions Bank, I would periodically recalculate the enterprise value of companies in the current investment portfolio.  To arrive at an accurate value for each firm I would perform two types of valuation (1) discounted cash flow analysis and (2) comparable company analysis.  Comparable company analysis is one of the most used and most important types of valuation that a firm can perform because it takes into account current market conditions.  It provides a solid framework to discover what the market is willing to value the firm at by comparing it to the value of its peers. 

This program walks the user through the process of performing the important function of valuing any company by using comparable company analysis.  It also calculates the weighted average cost of capital; a key component in discounting cash flows.  The program takes up-to-date market data from of the web and user inputs from the user to accomplish these two important valuation functions.  

Write-up

• http://files.gove.net/shares/files/10f/dixoncgd/Final_VBA_Write_Up.pdf

Excel Spreadsheet

• http://files.gove.net/shares/files/10f/dixoncgd/CarlDixon_VBA_Final.xlsm

Used Car Search

Searching for a used car on many different websites can be a hassle, especially if you know exactly what kind of car you are looking for along with the year, price, mileage, etc. because you have to constantly input these items into the search option. It’s also an annoyance to have to go to each and every website and check if the new postings fit your criteria.

To alleviate these hassles, I wrote a program where the user inputs information into the user form and then the user form takes what was entered and searches it on ksl.com. Once the search is complete, the price, year, and mileage, along with a link to a picture of the car and a link to the ad is posted on the spreadsheet. The user can then sort the data based on price, mileage, or year. This allows the user to easily access cars that meet their criteria without having to enter in all the information themselves.


http://files.gove.net/shares/files/10f/gb228/final_project_writeup.pdf
http://files.gove.net/shares/files/10f/gb228/final_project.xlsm

Your Financial Dashboard

Executive Summary:

Mint.com does a terrific job with categorizing one’s financial transactions. However, when presenting data spanning more than two months, the user is unable to quickly determine how much of each category was accumulated in each particular month. Instead, the user finds that Mint aggregates the amounts from each month and presents a totaled number for the period. This is a problem if the user wishes to see a quick categorized summary for multiple months at one time.

My project involved producing a 12-month dashboard summary that will assist the user in quickly capturing and maintaining the categorized numbers for 12 months of financial data. This required accessing one’s Mint.com account, navigating through the Mint.com web page, importing the data, and presenting the results in a customized dashboard summary sheet. The dashboard also automatically produces graphs of importance for the user.

• http://files.gove.net/shares/files/10f/willier/Dashboard.xlsm
• http://files.gove.net/shares/files/10f/willier/YourFinancialDashboard.pdf