10 Steps to True Value Investing

From a young age, I have always been interested in the stock market. So for the final project for this class I decided I would create something that could help me in the future as I continue to invest money. I decided to extend the first assignment we did of determining whether a company was a ‘fallen angel’ or not to include all of the 10 factors that Benjamin Graham (the father of value investing) would look at to decide whether a stock was a good buy at the time or not.

In the spreadsheet a person can enter as many tickers as they would like and simply press the button at the top of the page to have the sub procedure collect data from the internet and tell the person which of the 10 tests it passes. This is very quick and helpful for anyone that ever is interested in investing their money in a safe and reliable process.

http://files.gove.net/shares/files/11w/pmv3/ParleyVernon_Semester_Project.xlsm

http://files.gove.net/shares/files/11w/pmv3/Parley_Vernon_-_Project_Write_Up.pdf

Tracking Inventory for the Romney Institute

Executive Summary

The Romney Institute of Public Management is the department in the Marriott School that offers the MPA graduate degree. “The mission of the MPA program is to prepare leaders of exceptional capability and integrity who are committed to serving their communities and improving public service organizations” (Romney Institute Mission Statement). As a student employee in the department it is my responsibility to help create an orderly environment that will best serve the faculty, students, alumni, and other friends of the Romney Institute.

During the past couple of years, the department has acquired a large inventory of gifts that they use to thank visitors who visit campus to assist in educating our students, individuals who help build networking relationships, or any other areas members of the department feel a gift is necessary. Tracking expenses, maintaining counts, and reporting sales tax on this inventory has been quite difficult to manage in the past.

A solution to the problems stated above would be to create an excel spreadsheet that would be managed by VBA using user forms. These user forms would make the inventory tracking records consistent and collect the information needed to track expenses, inventory counts, and generate sales tax reports. Expenses and counts would be generated instantly and management will be able to make decisions quickly when approached with questions about inventory.

• http://files.gove.net/shares/files/11w/kleresan/Final_Inventory_2.xlsm
• http://files.gove.net/shares/files/11w/kleresan/Current_Gifts.zip
• http://files.gove.net/shares/files/11w/kleresan/Final_Project_Write-up.pdf

VBA Web Analytics Dashboard

Executive Summary

Recently, I worked on an internet marketing consulting project for a veterinary hospital. The animal clinic’s goal for its website was to generate leads via a phone call. Consulting efforts included setting up accounts in Google Voice, Google Analytics, and Google AdWords. In order to quickly allow the veterinary hospital see the business contribution of the website, I created a VBA web analytics dashboard.

The dashboard aims to show at a glance three key metrics to making internet marketing decisions: traffic, conversion rate, and average order value. To do this the VBA dashboard requires that the user first completes the following information: Google account email, Google login password, and a date range. After this the user clicks the “View Dashboard” form button to initialize data collection and dashboard creation.

· To pull total traffic data, the VBA dashboard navigates to Google Analytics, logs-in, sets the appropriate date range, then pulls visits data and copies it to a new worksheet in the Excel file. This information then displays in the dashboard.

· To pull traffic data and costs relating to paid search, the VBA dashboard navigates to Google AdWords, logs-in, sets the appropriate date range, then pulls click data and cost data, which are copied to the Excel file. This information then displays in the dashboard.

· To pull conversion data, the VBA dashboard navigates to Google Voice, logs-in, prints to a new worksheet all the phone calls and corresponding dates that fall within the appropriate date range. The amount of phone calls, or conversions, then displays in the dashboard.

· Conversion rate is calculated using the conversion and traffic data.

· In order to determine average order value, the VBA dashboard allows a user to manually enter data from the hospital’s records regarding services purchased by individuals who called the Google Voice # listed on the website; once this data is entered, total website revenue and average order value are automatically calculated and appear in the dashboard.

Once a dashboard has been generated, a "Save Report" form button is present. When clicked, this button saves a copy of the dashboard report with the appropriate date range included in the file name.









Project write-up: VBA Web Analytics Dashboard (pdf)

VBA Web Analytics Dashboard (xlsm)

Grocery List Builder

Executive Summary

The “Grocery List Builder” is an Excel-based, VBA-powered tool which allows the user to quickly create a grocery list with an easy-to-use user form. The multi-page form is broken out into nine different grocery categories, with a selection screen and a details screen for each category (thus 18 screens total). The user calls the form by pressing either the “Make List” button on the Grocery List Builder dashboard or the “Edit List” button on the mini-dashboard in the Grocery List view. The user can move from tab to tab within the form either by clicking on the individual tabs at the top of the form or by navigating through with the “Next” and “Back” buttons at the bottom of the form. The form features various message boxes to help guide the user as he or she selects the items to complete his or her list (for example, one message box alerts the user if 14 items, the maximum, have been added for any given category).

The user can add up to 14 grocery items per category and can enter details such as quantity (in a drop down selection) and notes (i.e., brand name, size, etc. in a text box). The selections are automatically copied to the Grocery List template in the Excel workbook, which can be printed instantly by clicking the “Print List” button on either the Grocery List Builder dashboard or the mini-dashboard in the Grocery List view.

In addition to the tool’s main feature, the Grocery List Builder, this tool also features a recipe selector. The user can access this part of the tool from the main dashboard by clicking “Select Recipes”; in addition, when the user clicks “Finished” on the Grocery List Builder form, he or she has the option to select recipes before finishing. As with the Grocery List view, the tool features a Selected Recipe view which includes a mini-dashboard. The recipe selector allows the user to not only select up to six recipes to print on “cards,” but also allows the user to add the recipe ingredients to a special section of the grocery list. The selected recipes are copied to the Recipes template in the Excel workbook, which can be printed instantly by clicking the “Print Recipes” button on either the Grocery List Builder dashboard or the mini-dashboard in the Selected Recipes view.

Project Write Up
Project File

Service Assistant

Milwaukee County is changing how alcohol and other drug addiction (AODA) clients receive services. Previously, clients received treatment for almost as long as they desired it. This caused long waits for new clients since only so many clients could be funded at time. In the near future, however, clients will be limited by dollar amount. When the money runs out, their treatment and services stop. Limiting the cost per client…

• removes wait time. Since the cost per client is limited, new clients don’t need to wait for old clients to leave treatment before they begin.
• Gives clients greater visibility of choice
• Allows the county to serve more clients for less money. (A potential drawback to systems like these is that severe clients will have as much money to spend on treatment and services as clients with lesser problems. Clients with severe issues may not get as much assistance as they need, and clients with fewer difficulties may spend more money than they would under a differently structured program.)

The WIser Choice program (Wisconsin (WI) Supports Everyone’s Recovery Choice) receives funding from many sources. Clients may be eligible for different funding sources depending on their personal and familial situations. Since some funds provide more treatment dollars than others, one client may be eligible for several thousand more dollars than another client.

This VBA project allows users to select options based on his/her knowledge of the client. The first options are the funding sources the client is eligible for, and are entered by a central intake worker. The VBA project will select the highest monetary value of the selected eligibilities and use that as the max cost of treatment. The second selection the intake worker makes is the level of recovery support coordination or case management the client needs or wants. At this point, a central intake worker may share the screen with the interviewee to select the treatment and services he or she needs or wants. The user selects a service by entering the number of units they’d like to purchase for the services they choose. To make the service worthwhile to the provider and client, each service has a minimum number of units. User entries that don’t meet that minimum number of units are not included in the cost and not included in the final list of selected services. If the total dollar amount goes over the specified client limit, the number turns red, and the user cannot reach the final screen until the numbers are adjusted to fit within the limit. Once the user has finished under the limit, the final screen shows the central intake worker the service code, service name, number of units, and the number of days the authorization should span.

http://files.gove.net/shares/files/11w/lfairban/VBA_Final_Project_Service_Assistant.pdf
file://files.gove.net/shares/files/11w/lfairban/Project_Provider_Service.xls

Approximate Find - Jason Whiting

Executive Summary

Approximate Find is an Excel Add-in that allows for specialized numerical searches. I originally conceived approximate find when trying to help a friend, a fellow PwC associate, work through cooperate and individual tax workbooks. You can search for specific values in Excel but this only allows for string comparison searches. Many times when working with different accounts and ledgers you want to find a number near a certain value. You may also come across a line item that is the aggregate of other cells and want to find which cells add up to that amount. Approximate Find allows you to give a numeric value, different tolerances (percentage, fixed range), and the number of numeric values you would like to find that add up to your target value; these features greatly extending the search capabilities of Excel

Linked Files:

Write up: http://files.gove.net/shares/files/11w/jpw2001/Approximate_Find.pdf
Project file (Excel Add-in): http://files.gove.net/shares/files/11w/jpw2001/WhitingJ_Approx_Find_Final_Plugin.xlam
Test Workbook: http://files.gove.net/shares/files/11w/jpw2001/WhitingJ_Approx_Find.xlsm

Email & Text Message Sender

In the past I have worked as a referee for an indoor soccer facility. Every week I would get a text message from them asking me for my available schedule. Once they compiled the schedule, they would then send out each person’s schedule via text or email. There are a number of problems with this system that I have tried to solve, mainly:

• The soccer facility doesn’t have the ability to send out text messages because (like most businesses) their only phone is a traditional land line. The employees use their own personal cell phones.
• A different worker sends out the texts and emails each week. I never know who is texting me. The workers have to look up each referee’s file and send out either an email or a text depending on the ref’s preference.
• If a ref is slow to respond to the text, the employee who sent it might be off work and they would have to forward the text to whoever is working. This is a HUGE pain.
• There is no central database of referee’s contact information.
• Some refs like to be notified via text, others prefer email. There is no system to do them both.

In addition to notifying referees, this system can also be used to notify captains of teams about their game time. Each week the managers are using their personal cell phones to send out texts and the owners don’t reimburse these expenses. The managers are loving the fact they don’t have to use their cellphones anymore.

The system I built gives the user access to a database in Excel. They can search for contacts, edit contacts, add new contacts, or delete contacts. Each contact has its own information that is stored (once it has been entered and saved). I believe this system solves all of the above problems.

Program:

http://files.gove.net/shares/files/11w/rjw35/Ultimate-Msg-Sender.xlsm

Write up:

http://files.gove.net/shares/files/11w/rjw35/text-email-msg-sender.pdf

(If anyone ever needs help with this code in the future, email me: rhett at byu.net)

Real Estate Agent Email Capture

Executive Summary

My brother is a real estate agent in the DC area. He has access to the MRIS internet database that all other real estate agents in the surrounding area are a part of. This database has agent information by broker office and when you select an office you are then given a list of agents in that office. One issue with this database is that if he wants to send an email to several of his colleagues in the same area, or the same agency (i.e. to let them know about an open house, etc.) he would have to look up each individual and click to send an email. The email address is not listed, but can only be accessed from each individual’s record by clicking an “Email Agent” link. This link opens up a window with whatever your default email program is, and has the agent’s email in the “To:” line. So in a list of 20-30 agents, you can imagine what kind of a pain this would be.

I have developed a program that logs in to the system for him and allows him to input the broker code for an office. The program takes this code and runs to output to an excel spreadsheet, the real estate agent name and email address for each agent in the office. This will save him much time an effort, especially when he has several different broker offices of which he needs the information.

Thank you,

Aaron Fischer

Below are the links to the PDF writeup of my project and the Macro-Enabled Excel Spreadsheet:

To protect my brother's privacy I have starred out his login information in the code.

http://files.gove.net/shares/files/11w/fischaf/AaronFischer_FinalProject_REAgentEmailCapture.pdf

http://files.gove.net/shares/files/11w/fischaf/AaronFischer_FinalProject_REAgentEmailCapture_WithoutLogin.xlsm

Appraisal Reports

A young investment fund is changing its administration software to another platform that better tracks the portfolio’s performance. I originally thought that I would be extracting data and performing the performance analysis, but it turns out that we do not have adequate access to the necessary index weighting and return history to properly accomplish that. Luckily, the new software is able to do it if given the portfolio’s performance history.

The current account administration software is web-based. This project accesses the fund’s current performance history through this website and navigates the site’s menu, selects the proper report for each trading day since the fund’s initiation, formats the report in Excel, and saves the report. The reports will be imported to the new platform.

http://files.gove.net/shares/files/11w/ammonshu/Automated_Appraisal_rpts_writeup_Ammon_Shumway.pdf

http://files.gove.net/shares/files/11w/ammonshu/Automated_Appraisal_Rpts.xlsm

Read It (reading practice for elementary school children)

“Read It” is a program that generates your choice of either sentences or mad libs. The program is meant to assist young children as they learn to read. The program allows children to choose grade level and difficulty of the sentences generated. Characteristic of the original mad libs, all sentences generated are usually comical in nature but are grammatically correct. Since the purpose of the program is to introduce children to new words, there is a “Say it” option so that a child may hear the sentences if they are having trouble reading a particular word or phrase.

After choosing a grade level for noun difficulty, the user can choose a category for the adjectives he/she would like to practice. The user also may choose which letter he/she would like the verbs to start with. Of course, for a wider range of sentence randomization, the user may select "Any" for each of these categories. Once configured, the user may choose from three difficulties. The beginner difficulty generates a short random sentence with only the essential parts of a sentence. The average difficulty will generate a sentence with several more than one subject and more than one verb. The advanced difficulty incorporates adverbs.

The "Mad Lib" button will generate a short story with the user's name as the subject. Download it and have fun. Happy learning.

The project write-up is here: http://files.gove.net/shares/files/11w/emonahan/ISYS_Project_Write-up.pdf

Get "Read It" here: http://files.gove.net/shares/files/11w/emonahan/Read_It.xlsm

VBA and MySQL - Recipe Manager

As an intern this summer at Zions Bancorporation, I will be using SQL databases to extract and report banking data for affiliate banks.  Because of this, I decided on a project that would allow me to learn how VBA can interact with an SQL database.

My project was to create a recipe manager that would store all of the recipe data in a MySQL database.  The program allows users to add, search for, edit, delete, view, and print recipes through VBA forms.

The code is housed within a single module and four forms.  The module contains database connection information and other global functions and variables.  The four forms control searching for, adding, editing, deleting, printing, and viewing recipes.

If users would like to set up their own recipe database, four steps must be followed:

•  Install or get access to a MySQL database
• Install ODBC driver
• Set up reference to Microsoft ActiveX Data Objects Library
• Set up ADODB connection to connect to MySQL database

Details for each of these steps can be found in the following write-up.

Here are the links for the code and the write-up:

• Code:  http://files.gove.net/shares/files/11w/mjb243/FinalProject_MattBaker.xlsm
• Write-up:  http://files.gove.net/shares/files/11w/mjb243/MattBaker_Writeup_RecipeDB.pdf

Bomberman for Excel

Bomberman is a game where the player tries to navigate a labyrinth comprised of pillars and rocks. The player has the ability to place bombs that blow up the rocks and open up passage ways. Within the labyrinth there are enemies that are trying to get to the players location. If the enemies touch the player, the player dies. However, the player can lay bombs down to blow up the enemies. In order to win, the player must blow up all of the enemies.

I implemented my version of this game using an Excel worksheet. On the work sheet the playing field is laid out with pillars being gray squares, rocks being brown squares, the player as a green square, and enemies being red squares. The player navigates the playing field using the arrow keys and places a bomb by pressing the letter b. The bomb is activated and a timer starts as soon as the player leaves the square where he just placed the bomb. After two seconds the bomb “explodes” cause fire to extend out in all 4 directions, destroying any blocks or enemies in its path. The player, however, needs to be careful not to touch the fire or the enemies or he will die and it will be game over.

The player has the option to customize the playing field prior to starting the game. He can choose the number of rocks to be randomly placed on the playing field (from 25-150) and the number of enemies that will be randomly placed on the playing field (from 1-10). The game uses the Application.onTime event to trigger the movement of the enemies and the countdown on the bombs. This onTime event is one of the essential aspects of the game as it triggers nearly all action. The player also receives points based on the amount of time it took to kill all the enemies and points for the number of enemies killed.

• http://files.gove.net/shares/files/11w/jduvall/Bomberman_Writeup.pdf
• http://files.gove.net/shares/files/11w/jduvall/Bomberman.xlsm