Stock Performance Indices, Tommy Smith

While working at an investment bank this summer, an organization that advises organizations on corporate finance issues, I had to pull stock information from the web and create graphs showing stock price performance of various industry segments to be used in presentations nearly daily. The graphs needed to be formatted with the bank’s formatting and color schemes, so I couldn’t just paste graphs from Yahoo or Capital IQ. I had to download data from the Internet to Excel and construct a chart by hand. Also, I had to combine the performance of any number of stocks to create customized industry segment performance indices. I therefore attempted to automate a program by recording macros that would pull information from Capital IQ and graph the data. However, with my limited knowledge of VBA, the program I created was inefficient, slow, and riddled with errors.

The new program I created pulls stock price information from Yahoo! and plots a line graph with stock performance of several industry segments against the performance of the S&P. The user selects which stocks to plot, which segments they are included in, and a time period through which to pull information. I will use this program extensively upon beginning work in June at an investment bank in New York City.

Write-Up: http://files.gove.net/shares/files/10f/smitht3/Final_write_up.pdf

Excel file: http://files.gove.net/shares/files/10f/smitht3/Stock_Price_Performance_Indexv5.xlsm

ArticleMaker by Dave Brown

Executive Summary:

The business of “content creation” can be profitable both on-line and off. Particularly in the area of on-line content creation, though, the speed of creation is important. An individual article on a niche blog might only generate a few dollars of advertising revenue per year. Similarly, articles submitted for publication to Yahoo’s AssociatedContent.com, HubPages.com, and other user-submitted content sites generate small amounts of money.

To make a full-time income from on-line content creation, writers must consistently produce numerous completed articles in an hour. On a pay-per-article website, for example, a writer might receive an average of $3 per completed article. Assuming a 250-day work year, a writer would have to write 100 articles per day to generate a $75,000 annual income from this site.

My ArticleMaker program makes this rapid creation of original content possible by automating the most time-consuming elements of content creation – namely, research and citation. Writers enter facts (with citations) into the program, thereby creating a database of cited facts for use on future articles. To aid in this process, the program includes a “Search Wikipedia” function that export any Wikipedia article to a Word document, accompanied by the correct citation. Once facts are entered into the database, writers use the “Write an Article” function to select facts for inclusion in a particular article. The program creates a Word document with the facts listed, each followed by a correct citation, and with a properly formatted bibliography. It is then a simple matter for the writer to expound upon the cited facts, and thereby create a completed article.

As an added bonus, I have included a “Write a Book” function that guides a writer through the creation of an outline for a full-length non-fiction book.

http://files.gove.net/shares/files/10f/dmb63/David_M_Brown_-_ArticleMaker_-_VBA_Project_Write-Up.pdf

http://files.gove.net/shares/files/10f/dmb63/ArticleMaker_by_Dave_Brown.xlsm

Survey Generator

Executive Summary:

Once a week my department at work (IT department at the MTC) sends out an email to customers asking them how their experience was while working with IT in getting their problems resolved. This spreadsheet will automatically pull all ticket information for the past 7 days from JIRA (an online ticket tracking tool) and judicially pick 10 tickets / people to send an email to asking them to rate their experience. You can then send the email to the recipients right from excel!

Excel Workbook: http://files.gove.net/shares/files/10f/bhorne/Customer_Satisfaction_Macro.xlsm

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

Expense Recorder & Analyzer

Executive Summary

The idea for the project I created came as a result of my family’s need to carefully track our expenses. I wanted a means to track where most of our money is spent so as to ensure that our budget is balanced. With that in mind, the project that I created enables users to input data into the workbook that is subsequently exported via text file and stored in an “Expense Data” folder. The program stores each text file by date, making it possible for the user to store a new expense data file per day, but also giving the user the ability to decide how often he/she decides to do so.

Along with the capability to store expense data, I needed the ability to analyze that stored data. Thus, the program that I created has the ability to import data from the “Expense Data” folder for any user-requested date range. The program then sums up all the imported expense data and creates two charts – a bar graph that details the total per expense category and a pie chart that breaks down the total expenses into percentages per category. The program is able to create charts for many different date ranges at a time to enable the user the ability to analyze how one date’s expenses affected the expenses for the overall range of dates. Again, the benefit here is that the program has given me the ability to analyze one day’s worth of data or years of it, simply by entering a date range.

http://files.gove.net/shares/files/10f/lopezo/ERA.pdf
http://files.gove.net/shares/files/10f/lopezo/VBA_Final_Project.xlsm

Recurring Revenue Report - Steven Wilde

I work for a local company that develops and licenses software for academic institutions and corporations throughout the world. The software is hosted online meaning that users purchase access to the tool for a specified period of time. Most transactions occur annually and the user pays for their access at the beginning of its license. The organization has experienced intense growth over the last few years. A key part of its business model is recurring or renewal licensing. The accounting department is responsible to keeping track of upcoming renewals and responsible to confirm renewals with sales representatives before sending invoices to clients. In prior years the task was simple and required relatively little time. However over the last year the number of transactions and the revenue generated from renewals has grown substantially. Additionally the workforce has more than doubled and there are no longer only a few sales representatives to speak with.

My VBA project reduces the amount of time for the accounting team to generate and deliver individual reports for all sales representatives through automating the process. Each sales representative has its own worksheet automatically generated with all relevant information presented in an organized matter. The solution is meant to be simple and intuitive as individuals with varying degrees of Excel experience will use the Workbook. The project has already been implemented and has reduced the time required to complete the task from two-three hours each week to just a few minutes.

Write Up

Excel Spreadsheet w/ Macro

Matt Blodgett VBA Project - Nibbles Game

Executive Summary

For my project, I decided to address the significant lack of Excel games for my young son to play. I created an excel version of the game ‘Nibbles’ or ‘Snake’.

Gameplay
In this classic game, the user controls a snake on a game board made entirely of squares. The head of the snake occupies one space on the square, with a long tail trailing behind it. The goal of the game is for the snake to eat ‘apples’, or red squares with a number embedded in it. Once a certain number of apples (9 in my version of the game) have been eaten, the level is complete and the snake moves on to another level. If the snake runs into its own body or the walls of the level, the level must be restarted. After the snake runs out of lives, the game is over. Scoring is awarded based on the number of the apple being eaten, a multiplier defined for each level, and a reward for the speed of the snake.

Creating user-defined levels

In order to keep the game fresh, new and entertaining, I added the ability of the user to create new levels to play. Users can create any number of level sets, with any number of levels. Each level can be customized in size, starting position, scoring options, and with user drawn walls.

Files

Simply download the excel workbook to start playing.
Excel Workbook:http://files.gove.net/shares/files/10f/mblodget/Nibbles.xlsm
Write-up:http://files.gove.net/shares/files/10f/mblodget/Project_Writeup.pdf

Executive Summary: Genealogy Worksheet

Problem

One evening, several months ago, my grandparents called me and asked me to help them configure the new computer they had recently purchased. I was surprised when they asked me to show them how to turn the computer on. Upon learning this, I felt that further inquisition into the extent of their computer skills was needed. I discovered, much to my chagrin, that they had purchased the computer in order to do their genealogy, but they had no idea how to do a simple internet search. As I continued to talk with them about their experience and that of their friends, I realized that there is large market that desires to do genealogical research but lacks the technical skill to do so.

Solution

In order to solve this problem I decided to create a spreadsheet that even the most computer-illiterate could use. This spreadsheet was built with simplicity and ease of use being the chief design characteristics. It allows the user to type their name and download their family history into excel with the push of a button.

http://files.gove.net/shares/files/10f/beck6/Final_Project_ISYS.xlsm

http://files.gove.net/shares/files/10f/beck6/Isys_520_Write_Up.pdf

Gathering Credit Card Data

Verifying the balance and transactions of your credit card should be a daily activity in an attempt to detect identity theft and maintain a budget. This macro enables you to quickly download all of this information from wellsfargo.com and input it into an Excel spreadsheet. From there an individual can manipulate the data to analyze it more easily.

Write Up

Excel File

NPV calculation

This VBA project will allow to reduce time spend on mechanical calculation of NPV, CAPM, WACC and price per share and spent more time on decision making aspects of projects, like preferred capital structure, analysis of growth of EBIT, CAPEX and NWC.
In order to calculate NPV of the project, an analyst needs to create a table and calculate following data for every single period:
- Earnings before income tax
- Tax
- Net Operating profit after tax
- Depreciation
- Capital expenditures
- Change in Net Working Capital
- Future cash flow
- Total Future cash flow
In order to find NPV, the analyst should calculate terminal value and WACC. Data for CAPM could be obtained from http://finance.yahoo.com/ and http://www.standardandpoors.com/indices. Then WACC can be calculated using information about capital structure and CAPM:

In accordance with particular project or particular assumptions some of the mentioned above data can grow/decline at a constant rate or change in accordance with the established pattern.
I have created NPV calculation macro, which will allow inputting available information into the series of forms and calculating NPV without manually creating a spreadsheet.
VBA macro automatically creates a final table with required information on the separate sheet “Report”.


http://files.gove.net/shares/files/10f/yshaykhu/Write-up.pdf
http://files.gove.net/shares/files/10f/yshaykhu/NPV_calculation.xlsm

Executive Summary - Brent Ray

Brent Ray

ISYS 520

Executive Summary

I am currently the Accounting Manager at my company, and one of my responsibilities entails creating the financial statements in excel. We are currently using QuickBooks, but it was improperly structured; so I must export the trial balances of the company into an excel spreadsheet, and then manually create the income statement, balance sheet, and cash flow statement (with accompanying spreadsheets that were used to build the cash flow statement). The auditors have also asked that I color code the trial balances so they are easy to look at and understand. In addition to the colors, the sides of each line item will have some type of descriptor (e.g. cash, accounts receivable, etc.). I want this process, the descriptors, to remain manual. VBA code will use these descriptors to create the financial statements, and it will be much easier to have the user classify each line item manually instead of having VBA look at the trial balance to determine if anything new was added and how to classify it.

This quarterly task usually takes me several days to create and adjust so that the resulting financial statements can gain the stamp of approval from the auditors. My desire is to automate most of this process so at most I have to click on a few buttons and the majority of the financial statements will “magically” appear. The code also needs to make appropriate changes based on the year inputted and the quarter. For example, if “2” was entered as the quarter, then the income statement should have “Quarter Ended June 30” instead of “September 30” for example.

It took 2,676 lines of code to be able to implement my solution

Write Up

VBA File

Quarterly Statement - use when you hit the Extract button