Funny about Money

The only thing necessary for the triumph of evil is for good men to do nothing. ―Edmund Burke

Excel vs. Quicken

So…how’s the bookkeeping working, after a year of using Excel instead of Quicken for Mac?

Last January I switched to Excel for tracking my bank accounts, budget, and credit card charges. After years as a Quicken customer, I’d really lost patience: data vanished in the transfer from Windows to Mac, Quicken for Mac was clunky, and I’d long ago had it with having to upgrade to a pricey  new version every time I turned around. It appears I’m not alone in those sentiments.

Excel has its advantages and its disadvantages vis à vis Quicken. Biggest negative: it can’t talk to your bank or your investment house. Quicken lets you upload and download transactions and data from those august institutions. Nor does Excel care to converse with TurboTax, Intuit’s tax preparation software. Excel talks to you and only to you (or so we hope).

If you want to integrate your bookkeeping with your banking and investing, however, there are alternatives, some of them out there in the Cloud. Programs such as Mint.com (which, alas, was purchased by Intuit), Buxfer, MoneyStrands, Pear Budget, or Thrive sometimes do that sort of thing, and of course Mint will now interact with TurboTax. Not having tried one of these programs, I hesitate to state that any are better, worse, or the same as Quicken. But there they are: something to try if your patience with Intuit wears thin.

Excel has one helluva learning curve, especially for those of us with English-major math skills. After a year of working with it, I’d say my skills are no better than they were at the outset. A year of manipulating Quicken left me with a black belt in Advanced Quickening. However, a rudimentary understanding of Excel’s functions allowed me to build checking and savings accounts and to massage the data into something that I think will be intelligible for my tax accountant.

It’s useful to know that Microsoft now offers a variety of home and office financial  management templates, designed to work with Excel. But it’s pretty easy to build your own.

To build the new Excel workbook, I tried to ape the accounts and functions of Quicken. This entailed creating spreadsheets for each bank account, laid out in identical patterns, plus another spreadsheet for credit-card charges. The latter allowed me to reset the balance each month to the amount budgeted for discretionary spending (which is all that goes on my credit cards), so that the bottom line showed how much was left in any given month’s allowance.

Typical headers for bank account

Tracking credit-card spending against an $800 budget

Come the first of this year, I created what I hope is an intelligible spreadsheet for the accountant by merging data from the credit-card spreadsheet with the bank-account spreadsheet entries and then sorting all the data by category. This made it possible to summarize tax-related data while also making all the year’s transactions, organized by budget category, easily visible and transparent to her.

A number of revelations ensued as I tried to organize this material for the tax accountant. One is that it makes sense to number tax-related categories (1, 2, 3…), so a “sort” command will bring them up at the top of the “sorted” spreadsheet. For example,

1 Medical
2 Mortgage interest
3 Trade group dues

…And the like. When “sorting” data, Excel wants to put numbered items before alphabetical items; so, if you preface each tax-related category with a numeral, the “sort” function will gather all the tax-relevant categories together.

Yeah, I know there’s something called a “pivot report,” and yes, I do suspect it could solve all my problems. However, only a Druid could comprehend the instructions in Excel’s Help file. I gave up after several efforts at trying to call upon those spirits.

In addition to its impenetrability, Excel has the annoying quirk that the (very simple!) formula you enter to create a running balance sometimes comes unstuck for no discernible reason, giving you an incorrect balance. Occasionally I haven’t discovered this until I’ve tried to reconcile my books with the bank’s. Figuring out the problem can be really difficult, because it often results not from incorrect data entry but from some mysterious disjunct between what you’ve asked the program to do and what it decides, midway through the process, to suddenly start doing. When gut instinct tells you something like this is happening, the solution is to go up to a row where the formula is visibly working and then drag the “balance” cell’s qualities all the way down the column. This corrects the error, wherever the heck it started.

We know the irritants presented by Quicken. It’s bloatware. It’s vaporware. The Mac version is clunkware whose files can neither be read by the PC version nor converted to a readable version. Your accountant, you can be sure, uses the PC version. And worst of all, its maker Intuit forces you to buy new, ever-more-bloated versions every time you take a deep breath. IMHO, these are very, very large irritants.

So, of course, is the difficulty of learning and manipulating Excel.

For me, just now it’s a toss-up. For a brief, not-so-shining moment last month, I considered running out and buying the latest version of Quicken to restart my books in 2011. But, on reflection, possibly not. Quicken’s biggest advantage over Excel is its ability to commune with your financial institutions. I’ve never felt moved to use that feature; my financial manager does the buying and selling of shares, and it’s pretty easy to access the credit union, the IRA, and the brokerage accounts online. Comparing and reconciling them is very simple, and I don’t need a piece of intermediary software to perform the desired transactions.

So. To the extent that one can be said to any software, I suppose I Excel.

What program do you prefer for bookkeeping, and why?

Author: funny

This post may be a paid guest contribution.

7 Comments

  1. I just use a notebook. My credit card bills give me info on expenses, which I can categorize if desired. I am aware of my net worth, my basic expenses, etc. Anything more detailed can be put together from cc statements plus cancelled checks. EASY.

  2. I’ve used Quicken for Mac since 1994 and I think I’ve updated 3 times…. For most software I find it really isn’t necessary to get the newest version every time one comes out. I suppose Q is clunky in some ways, but for the features I use I really can’t complain. Now Excel can do some amazing things, but right there at the top of the list is give you high blood pressure. I find that for a historian (i.e. NOT math inclined) who uses it only occasionally, it can be unbelievably obtuse.

  3. Quick ‘n’ dirty Excel over here! I like plain vanilla when it comes to using programs for tracking and budgeting. I don’t like the idea of giving one program access to ALL of my investment, saving, checking data.

  4. I have used Quicken for investment portfolio (only) since 2001, but I too am tired of having to ‘update’ every so often for no benefit. I’d like to transfer my portfolio management to Excel. I think I could manage to get my transaction histories, for three accounts, transferred by exporting an Investment Transactions report, but what then? Not clear how to create, and then manage the lots of each investment for purposes of buy, sell, and tax reporting.
    I’m not opposed to using BASIC macros, but am not readily seeing how to get started. Any suggestions appreciated.

  5. @cwillis: I for one don’t know enough about how to operate Excel to advise. If you google “use excel for investment portfolio,” quite a few hits come up.

    Another possibility: if you live near a good community college, watch its offerings for community-service courses in the use of Excel (these are often short evening courses designed for lifelong learners, rather than for students majoring in this or that). Even if the course itself doesn’t cover this, the instructor is likely to be expert enough in Excel to figure out the answer to your questions.

    Anyone else got any thoughts?

  6. You don’t need to get as complicated as pivot tables for the sort functionality you desire. Highlight the relavant data including column headers (ie vendor, date), go to the Insert tab and select “Table”. Hit OK. Each column will now have its own drop down sort function with options based on the contents. Numbers can be sorted by values, words alphabetically and dates from newest to oldest or the opposite. If you don’t see the correct option for sorting by date this is because of your cell formatting. Highlight the relevant cells, right click and select format cells. Choose “Date” and then the specific format you’re looking for (Mar-2010 or 3/01/2010 for example).

    • Thanks, Samuel! That sounds really interesting and useful. I’ll have to try it on a PC, since it apparently doesn’t exist in the version of Excel for Mac I’m u sing. There is no Insert > table function. There’s a Data > table function. When you click on that, it asks you to fill in a “row input cell” and a “column input cell.” Unclear what those are or how to enter the data correctly in the box.