Replace Microsoft Money with a Spreadsheet

I bought a desktop computer many years ago (back when people still bought desktop computers), and it came with a CD-ROM containing Microsoft Money '99. I installed it and used it to track my personal finances for years, migrating it from one computer to the next as I upgraded computers, until a) it no longer ran on the version of Windows I had installed and b) I had gotten tired of booting out of Linux and into Windows just to balance my checkbook. I was tempted to (and who knows, maybe someday I really will) write my own replacement in GTK, but in the meanwhile, I've found that I can replace pretty much all of the core functionality that I used from Microsoft Money with a fairly simple spreadsheet. I use OpenOffice (now LibreOffice), but all of this ought to work with Excel as well.

Of course the first thing I need to do is to keep track of individual transactions. I put the check number in column A, the date in column B, the Payee in column C, and the debit amount in column D (for a withdrawal) or the credit amount in column E (for a deposit). This makes the total column in column F a simple formula: =F1-D2+E2. I just copy that cell all the way down to the bottom and I have a running total, as long as I reserve the first row for the beginning balance in column F.

Figure 1: Running totals

Pretty simple stuff so far. Now, to avoid running into a limit, I create a separate tab for each quarter; the total for the Q4 '13 is the formula =Q313.F133, for example. Things get tricky when a statement comes and I want to do a reconciliation. I personally reconcile my statements to the penny every month (and so should you!), and I track both checks and credit card transactions in the same spreadsheet. So when I get a credit card statement, I put a unique identifier for the card/statement tuple in column H, toward the top of the spreadsheet, something like MS3429_0513 (MS for Mastercard, followed by last four digits, followed by due date in MMDD format). In column J, I put the amount shown on the statement, and in column K, I create a formula for the unreconciled amount: =J2-I2.

Figure 2: Starting a reconciliation

So what about column I? This is the reconciled amount — and this is where it gets a little bit complicated. With MS Money, I could go through the paper statement one line at a time, and check a checkbox once I found the matching item in my ledger. Instead what I do in the spreadsheet is I copy the contents of the H cell (for the statement I'm reconciling) into column G of the line that matched the statement. But before I start doing that, I define column I of the reconciliation line to the formula:

=SUMIF(G1:G500,H2,D1:D500)-SUMIF(G1:G500,H2,E1:E500)

Figure 3: Formula for reconciled amount

What the SUMIF function does is it first checks the contents of any cell in the range listed in its first argument; here G1:G500, which is where I copy the reconciliation identifier (e.g. MS3429_0513). If the contents of any of those cells match the contents of its second argument H2, the reconciliation idenitifer itself. If it finds a match, it adds the amount in the corresponding row in its third argument (the D column here). In semi- plainer English, this says, "Find every row that has a 'MS3429_0513' in it's G column and add up the contents of it's D column". Then, I do the same thing for the E columns. Remember that the D columns were my payments and the E columns were my deposits (or, in the case of a credit card statement, my refunds). It looks "backwards" that I'm adding up the debits and subtracting back out the credits, but I'm trying to match the amount shown on my credit card statement — the amount I owe.

Figure 4: Cleared all transactions

Once column K shows a balance of $0, I've reconciled every transaction on the statement. I usually have to add a few for receipts that I lost track of, but once it's ready to pay I go ahead and pay it. This works great for credit card statements, but is slightly more complicated for bank statements. The problem here is that when I pay a credit card bill, I don't add a ledger entry for that payment, since I've already accounted for them as individual transactions. That means that my bank statement has a line entry for each credit card payment I made that doesn't appear in my own ledger.

Now, I could deal with this by creating a separate set of tabs (or even separate spreadsheets) for each of my accounts — one for each credit card and one for my bank. However, that would lead to a lot of tabs since each one has to be subdivided by quarter, and I can't have tabs within tabs. Instead what I do is I add the reconciled amount from each credit card payment into the bank total. So, for example, if I've gotten three credit card statements that have all been reconciled, my reconciliation section might look like figure 5:

Figure 5: Starting a bank statement

So the formula in the I cell for my bank statement will not just be

=SUMIF(G1:G500,H2,D1:D500)-SUMIF(G1:G500,H2,E1:E500)
But instead:
=SUMIF(G1:G500,H2,D1:D500)-SUMIF(G1:G500,H2,E1:E500)+I2+I3+I4
(As I verify the payment amounts on the paper statement, of course).

Figure 6: Accounting for credit card payments

Finally, bank statements don't show an "amount owed", of course, but a "starting balance" and an "ending balance". So, for bank statements, column L is the starting balance and M is the ending balance, and K, rather than being the amount owed, is the formula =L5-M5.

Figure 7: Beginning and ending balances

This is all semi-manual; I think I'm pushing the limits of what you can really do with a spreadsheet. The commercial products like Quicken and MS Money had more advanced features like auto-reconcile, and they could even tap into your accounts and download your statements for you in electronic form. I'm not 100% comfortable with that myself; programmer or not, I still want to review each transaction. This may not be for anybody, but if you're struggling with a way to keep your personal finances up-to-date, a simple spreadsheet like this one may be a big step in that direction.

Add a comment:

Completely off-topic or spam comments will be removed at the discretion of the moderator.

You may preserve formatting (e.g. a code sample) by indenting with four spaces preceding the formatted line(s)

Name: Name is required
Email (will not be displayed publicly):
Comment:
Comment is required
My Book

I'm the author of the book "Implementing SSL/TLS Using Cryptography and PKI". Like the title says, this is a from-the-ground-up examination of the SSL protocol that provides security, integrity and privacy to most application-level internet protocols, most notably HTTP. I include the source code to a complete working SSL implementation, including the most popular cryptographic algorithms (DES, 3DES, RC4, AES, RSA, DSA, Diffie-Hellman, HMAC, MD5, SHA-1, SHA-256, and ECC), and show how they all fit together to provide transport-layer security.

My Picture

Joshua Davies

Past Posts