Using Excel as a Music Player

Executive Summary

For my project I used Excel to find .mp3 files within a directory the user specifies, then list them in a spreadsheet with each path listed as a link.  The application recursively searches the directory supplied, then stores the path of each file in a collection which is later used in a loop to print out the results.  When clicked, the files will open and play in the default media player.

Writeup

Excel project file

Experimental Design and Conjoint Analysis

Executive Summary

One of the most important and useful tools in all of marketing research is the ability to conduct studies using conjoint analysis. Full profile conjoint analysis is a way of using experimental design and full/partial factorials to come up with a set of cards that represents a given product’s features and levels (profiles). A person will go through and rank these cards in order of preference, from which the researcher can later derive representational utility for each combination of features and levels. It is not hard to see that as you include more features or levels per feature the more possible combinations there are and the less feasible it becomes to have a person go through and rank the cards. To compensate for this, experimental design narrows the total number of possibilities (factorials) that would be on each card to a manageable set of cards between 8 and 16 in most cases. The model is limited in that the more you use fractional factorials, the less your predictive capability becomes. For example, if I have a product that has four features with four levels per feature the total number of possible combinations is 256 (4x4x4x4). The model will use partial factorials to narrow the required number of cards to 16 and gives the researcher predictive capability to estimate utility levels for any combination—even one that has not necessarily been represented on the cards.

The demand for marketers that are familiar with conjoint analysis is consistently high. Software designed to conduct the necessary computations and algorithms to accurately conduct conjoint studies sells for many thousands of dollars—thus, making it a very expensive tool. This past winter semester, Dr. Smith introduced us to a basic DOS based tool that assisted in setting up full-profile conjoint analysis. He mentioned that the tool was over 20 years old, yet due to the lack of “affordable” alternatives, it was all we could use. Due to my marketing ambitions and desire to learn how to conduct conjoint studies, I thought to make a VBA based solution for the problem. In the process, I recruited a statistical expert in Brent Taylor to assist me with the project. In continued research on the scope of this task, we were disappointed to learn that solutions currently available to help one through the experimental design approach were in excess of 10,000 lines of code. Due to the sheer size of this undertaking, we have simplified the model so that instead of outputting utility levels, we can simply use the cards to determine functional direction a firm should undertake when considering a new product offering, change to an existing product, or other line/brand extension. Dr. Smith, owner of Qualtrics, has already communicated interest in our research.

Write-Up

VBA-File

ITunes Playlist Comparer

Brandon Carroll
ISys 540
4/13/2010

Download ITunesUtils.xlsm
Link to project write-up

Motivation

For the past several years, I have wished that iTunes had the capability to compare (or perform a “diff” on) two playlists. There are many situations I have encountered where the ability to see which songs are common between two playlists and which songs are different would be useful. For instance, I have often wanted to verify that all the songs I wanted in a playlist actually got added to it. To do this, it would be a lot easier for me to scroll through a list of songs that are not in the playlist because I would notice the ones that were out of place. However, iTunes only lets you view the songs that are in the playlist, and it is a lot harder to notice songs that aren’t there but should be because nothing is out of place. This has become even more of a problem for me with the introduction of the newer iPod Touches and iPhones because their solid-state drives have smaller capacities than older iPods and are too small to hold my entire library. Thus, I have to pick and choose which songs get put on my iPod and would often like to see the list of songs that are not in the playlists that get synced to it as I make my decisions.

Solution

For my project, I used Excel and VBA to write a utility that allows the user to compare and edit playlists in iTunes. When the user selects playlists on the left and right sides, the worksheet sorts the songs from the playlists into three columns. The left and right columns contain the songs that are only in the left or right playlist, respectively. The middle column lists the songs that are in both playlists. The user can sort the three columns by any of the four fields displayed in the list. Furthermore, the user can select and move songs from one column to another, or remove them from the playlists entirely. The worksheet uses the COM interface exposed by iTunes to propagate these changes into the actual playlists in the iTunes library. The user interface is shown in the image below.

Comparable Company Automation Project

EXECUTIVE SUMMARY

THE PROBLEM
Most professionals in the investments industry (investment banking, private equity, investment management) and the valuation business (accountants, actuaries, fair value specialists) use a program called Capital IQ in order to quickly pull lots of information on public companies for comparison and valuation purposes. Capital IQ has a special excel spreadsheet that can be downloaded and used by these professionals. The spreadsheet interfaces with the Capital IQ website and quickly pulls financial data on any public company that you type into the spreadsheet. Please click on the link below to become familiar with the Capital I.Q. excel plug in and the importance of having this data automatically generated. Also, open the PDF brochure to see part of an excel worksheet that I am trying to emulate.

https://www.capitaliq.com/Main3/ourproducts_platform_excel.asp

There is only one problem. Capital IQ is outrageously expensive for students who often need the same tool. Luckily, most of the core data is available on Yahoo.com or other websites. It just needs to be pulled, sorted, formatted, and manipulated.

THE SOLUTION
I used VBA to set up a spreadsheet that allows the user to enter ticker symbols of public companies for comparison purposes. I originally set out to have the spreadsheet automated to the point that the user could put in the time period that is desired, the base for the beta analysis (i.e. S&P 500, Russell 3000, or Wilshire 5000), the time interval for the beta calculation, the currency denomination, and other variables. I also wanted the spreadsheet to pull and organize every piece of data under the sun including the companies’ stock volatility, historical working capital, historical capital expenditures, etc. I soon learned that there is a reason that Capital I.Q. is so expensive. In order to completely re-create the Capital I.Q. excel plug in would demand about a year’s worth of work for one person. Therefore, I chose a couple of pieces of the project that I could complete and left the rest for a later date.

The resulting spreadsheet automatically downloads data from Yahoo Finance upon the user manually entering the ticker symbols of the companies that are comparable to the users target company. The spreadsheet then organizes this data for each comparable company and provides the user with a quick way to compare the following data to the target company:
• Historical Beta
• Leverage ratios to automatically un-lever and re-lever beta
• Current P/E ratios, and Enterprise Value ratios
• The last three years of income statements
• Current Stock Price and Market Capitalization
• Current Cash on Hand
• Company description and applicable markets/industry

The links to my work:
Worksheet: http://files.gove.net/shares/files/10w/rbc42/Final_Project.xlsm
Write-Up: http://files.gove.net/shares/files/10w/rbc42/Final_Project_Write_Up.docx

Text file manipulation with VBA

http://files.gove.net/shares/files/10w/bnd4/PMH_done_claims_Jan_2010_example.txt
http://files.gove.net/shares/files/10w/bnd4/baselinefortextmanipulation.xlsm
This program is designed to take a text file and upload it into excel in a way that will allow it to act more like a database. The main sheet of the excel file contains a button called "Select Data File" that goes and finds the filename and file path of the text file you want brought in. The used is then able to choose the fields they want brought in by clicking on the field names on the left of the sheet. A checked box indicates a field that will be brought in.

The button "Build workbook" then goes in and builds the workbook from the original text file bringing in only the fields that are specified in a list format that can be sorted and screened easily.

The button "Build Query" launches a user form that would allow the user to build queries that would then populate another sheet in the new workbook created. Some of the features of this user form include listboxes that populate with unique values from the data in ascending order when the used choses a field they want to screen by.

Although the code for the resulting query has been started inside the VB editor, the author decided (with the advice of the professor) that everything that might be queried could be done more easily and with more flexibility with pivot tables instead. That said, the query side of this project was discarded, though the user form was left because it contained several useful and complex features.

Also included in the code is a smaller program that fixes the formatting of one of the cells to be the correct size and positioning the decimal place in the right place.

Bryce Newbold's VBA Project

Executive Summary

Objective Statement
Create a VBA module for Bryce’s family budget spreadsheet that collects current information from the web (our bank accounts) and appropriately updates the spreadsheet.

Basic Background
My wife and I have been using an excel spreadsheet to forecast our expenditures for school semesters. Each semester we have updated and improved the spreadsheet to help us more accurately depict our financial situation.

After learning VBA concepts in class, I realized that there are some great improvements to be made to the spreadsheets we have made. I would be able to make it more real-time and accurate and possibly even automate some things.

I have successfully implemented and deployed all of the scope intended. While programming however, I realized that there was one more function that I wished to add. I have created a solution however, and I documented this process in the full project report. Use the link provided below to access it.

Report
In the report you will find explanations and illustrations of how the program is used, as well as the functionality involved. When necessary, I will explain in detail the steps involved in the program’s activity. I will also explain the revision to the program.

Finally, this project completes all of the approved scope for Bryce’s project proposal, which was approved by Dr. Gove Allen on March 16, 2010.

Links to project and writeup:

Project
Writeup

Calculating and Graphing the Efficient Frontier for a Stock Portfolio

PROBLEM.

Another assignment a few weeks ago for one of my classes. A spreadsheet calculating and graphing the Efficient Frontier for portfolio of chosen stocks based the required return of the chosen stocks.

SOLUTION.

After finishing the assignment, I decided to simplify the process as much as possible. Thus, this program was created to calculate and graph the Efficient Frontier for portfolio of chosen stocks based the average required return of the minimum and maximum of the chosen stock returns.

OVERVIEW.

See the files below. To calculate and graph the Efficient Frontier, please press the Start button and follow the instructions.

Explanation

Xlsm-file

Gift Card Reconciliation

Executive Summary

Since a recent change in law prohibited gift cards from expiring or charging monthly fees, these cards can now be acquired in large quantities and stockpiled for personal use over a long-term. These cards are issued for a wide variety of products and services including restaurants, retail stores, airlines, Ebay purchases, and general credit purchases such as American Express. Keeping track of these cards is often difficult, time consuming and complicated especially where cards are only partially used with remaining balance less than the stated face value. The owner must also be careful that cards are not compromised by improper transactions.

Fortunately, many card issuers now allow for online verification of the card balances however checking these balances on a periodic basis for a large number of cards and comparing the balance to one’s record is very difficult.

The program I wrote will give the user a format to record card information and history including the current expected value of each card. It will then on demand look up the amount the vendor shows for the cards, compare these values to the expected value and highlight any discrepancies. Differences could then be investigated and reconciled.

Writeup: http://files.gove.net/shares/files/10w/bzwigard/Gift_Card_Writeup_Brad.docx

Worksheet: http://files.gove.net/shares/files/10w/bzwigard/Gift_Card_Workbook_Brad.xlsm

Personal Budget Spreadsheet

Description:

This spreadsheet accesses the user’s credit card website, downloading balance information and all transactions ytd. The program then processes that transaction information and prompts the user to classify each transaction according to the user’s budget categories. The program generates and displays current spending amounts for each budget category and also ytd spending under each budget category.

Quick Stats:

475 lines of code

19 sub procedures

2 web queries

»24 hours of work

Limitations:

This spreadsheet will not work off of my hard drive. The first reason for this is that I have removed my user name and password information that the program uses to access my financial data. The second reason is that the web query that imports the financial data into the spreadsheet is dependent upon a specific file path on my machine.

Link to workbook

Link to write-up/description/screenshots

BLS PPI Database Query to Analyse Year-to-Year Change in Commodity Prices - by Graham Orme

EXECUTIVE SUMMARY

My internship this summer will be in the sourcing department for Union Pacific Railroad. I will most likely be assigned to be a sourcing analyst to purchase commodities or other materials that Union Pacific (UP) purchases to build track or run their operations. I reached out to a BYU MBA alum who works in the same department to see if I could do my final VBA project on something that could either benefit me or the department in purchasing materials. My friend mentioned that he and his department are continuously opening the Bureau of Labor Statistics (BLS) website and pulling the Purchase Price Index (PPI) for various commodities to analyze and anticipate their price changes. Thus, was spawned my idea and you have this project before you; VBA code that automatically does exactly what my friend at UP does by hand every month.
The three commodities I am searching for in this project are sand/gravel aggregates for cement, cement itself, and concrete. All of which go into the foundation of UP’s train tracks. At the push of a button my code accesses the PPI database for each commodity, one by one, on the BLS site and loads them onto three tabs in a prepared worksheet. Two input boxes then pop up asking for the month and year of the price one would like to see from the index and loads that price and the price from exactly a year prior onto a table that calculates the percentage change in price from year to year. At the same time, the projected price for the subsequent month of that which was selected, along with the projected price of the commodity exactly a year from then is put into another table below to calculate the projected change in price.

WRITE UP
VBA CODE

Report

File

Executive Summary

The purpose of my VBA project is to provide an automated tool that scans a list of items
purchased from Staples, downloads the latest prices for the items in the inventory, and
requests partial refunds if prices for any of the purchased items have dropped.
The resulting program, PennyPincher 1.0, has the potential to save small businesses
hundreds or even thousands of dollars every year by ensuring that the business takes full
advantage of Staples’s 14 day price guarantee.

The user of this program enters key pieces of data from a Staples purchase into a
spreadsheet by using a user form that is accessed via a button on the spreadsheet. The
user may then choose one of two options to check for price changes.

One option is a onetime check that checks all the prices, sends out e-mails requesting
partial refunds for items that have dropped in price, updates the data in the spreadsheet,
then terminates. The second option sets the program to repeat the price check at regular
intervals of the user’s choosing.

One of the most valuable aspects of this program is that, when it does find an item that
has a new, lower price, the program actual sends an e-mail to Staples customer service
detailing the drop in price and requesting a refund. This ensures that no refund
opportunity is ever missed due to delay or forgetfulness on the user’s part.

Home Salon Management System

This project involved the creation of an Excel workbook intended to be used as a database and management system for running a home salon. The final product began essentially as an ‘upgrade’ to an existing workbook used by my sister who works as a hairdresser out of her home. The old workbook was outdated and ‘clunky’ with various glitches occurring. The old workbook used only worksheet formulas to keep track of basic income and expenses on a monthly basis with summary information on a master sheet. The new workbook created for this project provides all the functionality of the old workbook with improved ease of use as well as additional useful functions and features. The new workbook uses multiple user forms to make it easier to enter income and expenses to better manage the finances of a home salon business and the worksheet have a much improved appearance. Additionally, the new workbook adds functionality to store data such as contact info, hair color formulas, notes, etc. on all clients entered in the client data worksheet. Information on each client is conveniently displayed and can be edited on a user form, where new clients can also be added and managed. The user can also choose to send e-mail and text alerts to clients reminding them of their next appointment.

Excel Project: Excel Workbook

Project Write-up: PDF Document

New York City Apartment Complaint Listing

Executive Summary

A real estate company (name withheld) manages apartment buildings in New York City. When there is an issue with the apartment or building, the tenants have two options. They can tell the building manager or file a complaint with the city. NYC Housing Department files posts the complaint on its website and schedules a visit to review the issue. If a complaint is filed through the city, building managers only know of it through viewing the website or after a visit from a city official.

The company has over 10 apartment buildings it manages. It would be burdensome to check for complaints on the buildings each day as they can be put up at anytime. A subscription to an email notification alert service is also too costly. The company’s employees are often out visiting the buildings and do not have access to the internet when they are out of the office. Also, since new complaints are not filed each day, the time spent manually checking is wasted if there is no new posting. The real estate company wants to know of the complaints before the city’s official visits. This way, any complaints can be resolved and the officials only need to visit once.

The VBA code automates the process to check for complaints. The procedure goes to two websites to check for complaints on all the properties. One website is for apartment specific problems and the other is for general building issues. A complaint history is imported into Excel for each location. The procedure checks for any new complaints and an email is sent with the details. The program can be scheduled to run on a daily basis and even multiple times a day. The real estate company now has a tool to be proactive in resolving tenant complaints.

http://files.gove.net/shares/files/10w/ael6/Final_Project-_Write_Up.docx

http://files.gove.net/shares/files/10w/ael6/Final_Project-_Aaron_Lund.xlsm

Personal Finance Tool

There is one thing that most people, regardless of nationality or culture, have in common: Concerns about money. My project is not meant to solve the world’s monetary issues, but rather it is meant to be a helpful tool in the quest to get a handle on one’s everyday expenses. I call my project “The ₵entinel: Guardian of Your Dollars and ₵ents” because I look at it as a guard against ignorant spending.

Over my adult life I have used several different software solutions to keep track of my personal finances and have been seriously disappointed in all of them for various reasons—cost and/or functionality being the two biggest. The ₵entinel is my preliminary attempt at a solution to these and other personal finance pickles.

In its current form, this tool is able to automatically download personal checking account data (only from Wells Fargo’s website at this time), format the data and categorize the data according to customized spending categories. This tool can also “learn” over time—meaning that new or unfamiliar expenses will trigger a manual categorization process that the tool will use in future auto-categorization processes. In its final form, this tool will eventually evolve into a total financial tool, both for short-term management as well as long-term planning.
Report
Project File

Sales Agent Scorecard Generation for Integra Telecom

Situation:

Integra Telecom, a telecommunications service provider, has a Sales Agent Scorecard that they generate every month from the data in their Microsoft Access database. The Scorecard itself is a Microsoft Excel file that is formatted in a very particular way – a way that can’t automatically be done from Access. Thus, every month, a long, tedious process is undertaken to export the data and format it properly.

Solution:

This project imbedded the automation tools required to generate that Excel report (with the proper formatting) using a button located within the database. The user only needs to select the month the report needs to be generated for, and the location of the final Excel file. The code takes care of all the following actions:

· Exporting

· Formatting

· Rearranging

· File-naming

· Saving

· Clean-up

Full Report
Project File

The Matchmaker

The Matchmaker is a product of the LDS singles ward dynamic. This tool ultimately matches young single adults with their soul mate so they can expeditiously get hitched and live happily ever after. Yeah, right. . . In reality, this tool automates the process of matching various participants of a dating activity based on user inputs: picks and interests.

Each user fills out a survey indicating his or her top seven picks (those with whom they are interested in being matched up with for a date) as well as answering several questions regarding interests and hobbies. The Matchmaker then takes this data set and makes assignments based on the most optimal matches. The program then e-mails the men informing them who their date is and provides additional instructions on what to do in preparation for the date night activity.

The write-up: http://files.gove.net/shares/files/10w/mcs55/The_Matchmaker_write-up.pdf

The Matchmaker: http://files.gove.net/shares/files/10w/mcs55/Final_Project_-_Matt_Schultz.xlsm

Numerical Heat Transfer Optimizations in VBA

For my masters project in mechanical engineering I created a numerical model for the heat transfer through a thermoelectric energy harvester for Boeing. I created the numerical routine in Matlab because that was the language I was familiar with. Had I known how to use VBA, I would have used it. For this project, I converted the Matlab code into an integrated VBA code using a function to calculate a thermal resistance using a numerical heat transfer model and Gauss-Seidel iterative solver. I next used a macro to run Solver to optimize the design of the energy harvester for maximum efficiency. The results from the design tool match my original Matlab, but the new design tool is much simpler and more elegant.

Excel File: http://files.gove.net/shares/files/10w/msz4/CompliantHarvesterModel.xlsm

Project Report: http://files.gove.net/shares/files/10w/msz4/Zabawa_FinalProject.pdf

Web Table Converter

The Problem

Several times in the course of my employment as a web developer, I have been required to retrieve a relatively massive list from the internet and either convert it into a XML document or insert it into a SQL database. Both situations have caused a fair amount of headache for me. My typical process involved highlighting the table, copying it and pasting it into Excel, and then spending 30 minutes coming up with some crazy set of Excel functions to generate the desired output.

The Solution

I created an Excel workbook and VBA script which will parse a HTML page on the internet and find all of the tables on the page. The user can choose which table to download. After downloading the table to the spreadsheet and making any desired adjustments, the user can choose to either export the data as XML or to insert the table into a MySQL database (given a MySQL server, database, username, and password).

Workbook

Write-up

CRM Data Entry Segmenation - Chris Brasher

Background

At the start of my internship, I had the daunting task of purging through 50,000 person records and 30,000 company records to add into the brand new CRM database. This consisted of removing duplicates, separating cells using text to columns, switch “dirty” records that had first and last name swapped, and fix company names that were only a couple characters different (ie. “ABC Company, LLC” is different than “ABC Company LLC”). Now that the data is inside the system, we have had a tough time segmenting the data according to the fields we implemented recently. Thus, I wanted to develop a file that the data entry personnel could use to format correct, “clean”, segmented records before they are loaded into the CRM.

Some of the elements include:

• Copying previous submitted Company Data (Sorted & Unique Records only) in order to speed up telemarketers efficiency
  
• Validating various fields in order to qualify the record
• Segment the data according to the User defined Tables
• Preparing the Data for CRM import
• Removing duplicate company names

Project Excel
Project Write-up

Report System for The Pendulum Court

Executive Summary

Business Description

The Pendulum Court in the Eyring Science Center is a restaurant managed and run by dietetics students. The restaurant, while successful at training its students and providing high-quality food for affordable prices, did not have a viable system to record daily production and sales and generate reports that were useful to the managers. The spreadsheet presented here will be used each semester to record production and sales and generate reports to show the activity for the semester.

Purpose of System

I set out to create this system with the following goals in mind.

· Attractive and intuitive user interface

· Able to be maintained and updated by those with little exposure to Excel and data validation and controls around the data-entry

· Able to generate reports at any time and provide a snapshot of weekly activity

Overview of System

The system is a series of worksheets built on a similar template with hyperlink navigation that allows the user to jump from sheet to sheet without realizing that they are simply changing worksheets. Within each week, each day can be opened separately or hidden and the detail can be summarized or hidden by the click of a button.

The data entry has many data validation controls built in that allow even an inexperienced user to use the spreadsheet and the system still maintains the integrity of the data. After the user enters the data, they click a button that submits the data to a database from which reports can be generated. When they submit, the interface produces a checkmark next to the day to show that the data was updated successfully. If anything is changed, the checkmark is erased as well as the corresponding data on the database so that the user will be prompted to resubmit complete data. Reports can then be generated from the numerous metrics measured each day and week.

Write-Up

Excel File