VBA Final Project: Automated Personal Budget

Executive Summary

                Having an up-to-date personal budget can be extremely beneficial because of the heightened awareness one will experience about spending. The purpose of my project was to use the knowledge and skills learned about VBA to create a program that would help me manage my personal budget. I will provide a summary of the problem, solution, and overview of this project.

Problem

                Two years ago I became more concerned about budgeting my expenses. I wanted to know what and where I was spending and monitor expenditures by setting goals. With the little knowledge I had about Excel, I selected one of the personal budget templates provided by Office. This template was extremely simple and required that I type in each transaction. I tried to update that file weekly. However, updating just one week of spending history took a long time. Often, I would not update on a weekly basis because I could not find time that particular week. Consequently, multiple weeks would go by such that multiple hours would be required to get the file up to date.

Solution

                Essentially everything that was once done manually—by hand—would now be done automatically by VBA. I knew that I could obtain all of my spending history (except cash expenditures) from my online bank account; I wrote a program in Excel that would—at a click of a button—fetch all transaction history that had already been posted online. The program imports all the data and then puts it into a table. It then reads the transaction descriptions and assigns that particular transaction to a certain category based on the description. The program also builds a pivot table to view spending information by month. Finally, the actual spending amounts per category for the current month are compared against goals I had set and progress regarding spending goals is displayed in a bar chart.

Overview

                I will break down the steps to provide a more detailed overview of the project. The content of the solution includes the following:

·     Prompt user to complete a form to get the program started

o   Require user to enter needed usernames and passwords

o   Make user specify a date range for which transaction history is desired

·     Go to online bank account to fetch all transaction history

·     Import transaction history into Excel

o   Trim history down to date range specified by the user

o   Create a table to hold the transaction history

o   Add manually inputted cash expenditures to table

·     Read transaction descriptions and categorize accordingly

o   For descriptions the program doesn’t know how to categorize, ask user to categorize

·     Create pivot table and organize it to show transaction history by month

     Update the actual spending amounts and show chart that displays goals against actuals

http://files.gove.net/shares/files/14w/chanman2/IS_520_Final_Project_Luke_Chandler.xlsm

http://files.gove.net/shares/files/14w/chanman2/IS_520_Final_Project_Write_Up_Luke_Chandler.pdf