How can I use a spreadsheet to track my accounts?

September 30, 2009 2:16 PM

I want to start using a spreadsheet to track and reconcile my various accounts. How do I start?

I've been using MS Money for years. Due to switching to the Mac, MS Money being discontinued, and generally hating the fact that my financial data is trapped in a proprietary program, I feel like I should use a spreadsheet to track my finances.

I have a savings account, two checking accounts, two credit card accounts, and a Roth IRA.

There are a couple of things I'm curious about: How does one reconcile accounts in a spreadsheet and are there any templates that have that checkbook entry system that MS Money has?

I've looked at some of the older posts on this topic and none seemed to answer my questions satisfactorily.

Links to templates, blog posts, howtos, general anecdotes, and anything else relating to this question are requested and appreciated. Thanks!

posted by reenum to work & money (9 comments total)

Use some of the free personal finance templates at Google Spreadsheets. You don't even have to use GS, but I'll bet those will give you some good ideas.
posted by soelo at 2:34 PM on September 30

This can easily be done in Excel.

You can make a sheet for each account, and then link them all together in another sheet, and all the sheets are in one file.

You can take a cell from one sheet, and use that total on the main sheet, and you can use the autosum feature to add up cells.

Just be aware, Excel in the past had used RAM memory, so file sizes were limited in that respect. I'm not sure if that has changed in the newer versions of Office...

You have to do the entries by hand, unless there is a way to link a MSMoney file... There probably is, all the MS Office programs can 'talk' to each other....

Open Excel, and start playing. Use the 'Help' function, it really does what it says on the tin!
posted by Jinx of the 2nd Law at 2:37 PM on September 30

are you worried about using something like Mint?
posted by wocka wocka wocka at 2:37 PM on September 30

vertex42.com has a ton of amazing excel templates for money purposes.
posted by st starseed at 6:13 PM on September 30

Wocka: I am wary of putting my personal finance info onto a website. I just don't trust it.
posted by reenum at 6:29 PM on September 30

Have you considered GnuCash? It's open-source, so you don't have to worry about forced obsolescence, and I'm pretty happy with it (I'm not using the OS X version, though, so I can't comment on any particular quirks it might have).
posted by oaf at 5:57 AM on October 1

oaf:
How do you find it? I'm considering it for managing my own accounts, but I wonder if a double entry system might not be overkill for personal use.
posted by atrazine at 8:30 AM on October 1

I have many more accounts to worry about than you do. I've experimented with Mint and GNUCash, and have settled on the spreadsheet as you seem to be moving to. I've found that neither Mint nor GNUCash save me any time over the spreadsheet, and that the spreadsheet is just much more flexible.

I keep a sheet per account, and link everything together on a budget page, sometimes doing calculations on averages for budget projections (average heating over the year, etc.).
posted by kryptonik at 9:43 AM on October 1

I'm considering it for managing my own accounts, but I wonder if a double entry system might not be overkill for personal use.

It might be overkill, but it only took me a couple of weeks to get used to it. My main reason for switching was that Quicken 2007 (which I switched from) had an interesting approach to non-USD accounts. Their recommendation was that you create mutual funds so that the exchange rate could be updated and you would know how much the account was worth in USD.

Unfortunately, anytime you try to create a mutual fund in Quicken 2007, it crashes. Hard.
posted by oaf at 11:45 AM on October 1