These are projects posted by the students of Dr. Gove Allen at Brigham Young University. These students have taken one semester-long course on VBA and generally have had no prior programming experience

Wednesday, April 13, 2011

personal budgeting made easy

One of the great personal finance tools out on the current market is Mint.com. Mint pulls all of your financial accounts into one convenient place and allows you to easily categorize and manage all transactions. It is securely linked to each any desired financial account, and provides an up-to-date read-only copy of all balances and transactions. The user has the ability to set smart, reoccurring rules when categorizing and tagging expenditures. For example, when Costco Gas comes through the bank statement, Mint can be set up to always categorize it as “Fuel & Gas”. This makes organizing and tracking income and expenditures almost seamless. Various other useful tools are available to the Mint user, including a simple budgeting tool, goal tracking, spending trends, etc.

The one major drawback with Mint is their budgeting tool. It is extremely simplistic, and the static format feels awkward. To compensate for this, most users who are serious about setting and diligently sticking to personal budgets are forced to resort to archaic Excel spreadsheets. Or, as was my case, the user already has a budget in Excel they are comfortable with and have a habit of using. I found that in order to take advantage of Mint’s various capabilities while maintaining a personal spreadsheet budget requires significant amounts of time. The manual process of transferring information from Mint.com to a spreadsheet is incredibly tedious. Most of us know from experience, if the budgeting process is not quick and painless it gets put on the backburner.

Therefore, this program was written to streamline the process of inputting Mint.com data into a well-designed, flexible spreadsheet budget. The spreadsheet breaks the process down into five simple steps:

1. Create New Budget – creates a new monthly budget based off a customized template

2. Retrieve Mint Data – allows the user to log on to their Mint.com account within Excel, then automatically download all available transactions into .csv format

3. Prep Transactions – Many transactions within one’s personal financial statements do not need to be budgeted for (ie inter-account transfers, financial aid refunds, earned interest, etc.). This step simply removes such transactions

4. Pivot Data – By capturing Excel’s powerful pivot table tool, pages of VBA code was avoided. This function asks the user what month and year they want to work on, then pivots all relevant data in less than a second

5. Input Actuals – Once all actual data is pivoted and organized by category, the last step simply takes all actual amounts and puts them next to respective budget amounts. It then calculates a variance so users can analyze their earning/spending habits

The overall objective of the program is to encourage users to set and maintain a reasonable personal/family budget.

No comments:

Post a Comment

Blog Archive