Tyler Ravsten Financial Tracker and Account Manager

520 Semester Project Executive Summary

Background

How often do we think “where the heck did all my money go?” This question was the catalyst for my semester project. My goal is to be better prepared for the future by understanding my family’s spending habits as well as our income trends. To get the most information I wanted to be able to track every transaction we made. Like most people, we don’t have only one form of payment. Sometimes we pay things in cash, sometimes they go on the debit card, a majority of the time they go onto our two credit cards. I needed one place to put everything. My personal finance record allows me to do that.

Functionality

To make the data meaningful I needed to be able to aggregate and divide the data in multiple ways. In order to do that I built a transaction sheet for each month.  Standardized sheets for each month made sure that I was collecting the same data for every transaction. Each entry included a brief description of the charge or income, the date, the category it belonged to and then the value of the transaction was placed in a column indicating the payment form. Two other columns kept a running total balance for our spending money and our savings account.

Automating the Process

The first process I automated had to do with income. Every time my wife and I make any money we want to put away a percentage of it towards savings. VBA makes recording this simple. Once I learned about User forms the entire process of recording information and numbers became automated through a form. I was able to then make a form for expenses as well. I was able to keep data validated by using a list of categories in the form. The Income form automatically splits off a tithing payment, makes another record for it, and assigns it to the correct category. All of the data is summarized through various functions and dynamically placed into charts to graphically show our progress.

The model is designed to show the real amount of money we have if everything was paid off that instant. Thus, even when we haven’t yet paid it yet, the sheet takes away tithing and payments made on our credit cards. VBA made it easy to see what we had actually paid versus what had only been recorded on the sheet. I wrote a simple sub-procedure to iterate through each record and see if the tithing had been paid based of its description. The results are then presented back to me and then I’m asked if I want to mark those records as paid. Another procedure iterates through and marks records as paid.  

Forms and sub-procedures were great for helping automate and speed up my recording process, but learning to modify the ribbon in Excel is what helped to make it look great. With simple buttons on the ribbon the model becomes clean and intuitive to operate. Sometimes my sheet gets off a little, so I wrote a procedure to go check the balance of my Discover card and compare it with what my sheet says. It’s also always fun to know what little stacks of cash you have hiding somewhere, so I wrote another sub-procedure to connect to Discover and check out how much Cash Back Bonus I have.

Conclusion

I’ve been changing and improving my sheet as I’ve progressed through the semester, and now recording transactions is easy. Everything dynamically handles itself. I don’t have to do anything but push a button! As time goes on I look forward to using more VBA to add even more functionality to my model.

Excel Workbook
http://files.gove.net/shares/files/16w/bengt92/Personal_Finances_Semester_Project.xlsm
Project Write Up
http://files.gove.net/shares/files/16w/bengt92/Project_Write_Up.pdf

Numbers have been changed to protect the innocent! :)
P.S. Sheets for other months are hidden in the workbook.