I have been using the same spreadsheet to track my personal finance information for over twelve years. That is a long time, and after all that time, it's evident that I need to spend some time revamping it, as it has gotten too complicated, unwieldy, and difficult to use, all of which make it impossible for me to take advantage of many of the features and simplifications that Excel offers.
A Brief History of my Financial Tracking Spreadsheet
I started my tracking spreadsheet around 2002 with a simple monthly net worth calculation, as well as to track various components of my investing.
From there, it grew as needed. When I wanted to track spending in more detail, I added that. When I wanted to track specific information about my debt, I added that. Tax refund history, money market allocations, it all got added as needed. As time went on, I had many different tabs each tracking something different.
The Problem of Discontinuity
As I kept adding things, the spreadsheet began to resemble an old house that was added onto over the years, each time because of a specific need. Each addition made sense on its own, but when you look at the house as a whole, you realize that there isn't any type of cohesiveness. It's just a bunch of room.
That's what happened to my spreadsheet. At some point, it's become a bunch of tabs, all of which contain data in some fashion, but with no central way of tracking.
Part of the problem is that there's not even cohesiveness in how I track data in terms of rows and columns. Pretty much everything is tracked by month, but in some cases I would setup tracking in columns, then the next time I would set things up in rows. My ‘general ledger' tab actually manages to somehow incorporate both.
And the result is a big mess.
I can't even take advantage of formula calculations because of the disparity between row and column usage. Excel is pretty adept at picking up patterns, but for some reason it refuses to understand when I'm trying to do when I want to cross reference data in columns into rows or vice versa.
As a result, I end up re-typing numbers from one tab to another, and in some cases the same entry gets input into several different cells, sometimes in the same tab!
So, I'm going to slowly work on converting it to a more manageable system. Here are the initial phases of work I'd like to undertake:
- Combine income and expense tracking onto one tab, converting to pure column tracking. I will leave the information and categories as is, and am working on 2014 data only.
- Refine categories to remove dependencies on payment method. Right now I have a tracking page for credit card expenses and another tracking pages for debit card expenses. As it stands, if I spend money on clothing, for example, I will actually have two clothing expense categories. This makes no sense, so I'll re-work the tracking o make the tracking independent of the payment source.
- Add data from other tabs. Once I get this in, I can start merging data into other tabs, including tracking of our money market accounts, side income tracking, and other things. By the time it's done, I hope to take five tabs that exist today and be able to track the data in one tab
- Refine and merge my net worth tracking tabs. Right now, I have three different tabs for tracking net worth information. Once I get the income/expense tracking tabs merged and updated, I would like to focus on merging and simplifying these tabs. At this point, I'd have eight tabs down to two.
- Transfer historical data. As I mentioned above, I have ten years worth of data, but I'm only re-vamping 2014 data. At some point, I'd like to copy the data over so that I can get rid of the old data tabs altogether. Eventually I think I can do this largely with pivot tables.
I'm sure that many people would advise using a pre-packaged piece of sofware or an online tool. The problem with this idea is that I have simply become too used to my methods. I've tried a few different tools over the years, and none of them meets my needs or lets me do things the particular way I want to do them. I'm probably adding complexity by doing it all myself, but it's worth it to me.
Now, I just need to reduce the complexity and make it a little easier on myself. With a system that's twelve years old and counting, I'd say it's way past time!