Be careful calculating your expenses

[FurBall]

I use customized, memorized Quicken reports for YTD numbers. Then I put the totals from those reports into my Excel spreadsheet so I can track spending/saving/income by quarter. At first I just tracked total expenses YTD but ran into the issue of large tax and insurance expenses that fall early in the year. Tracking by quarter lets me see where I was last year at the same time and avoids Q1 panics.

 

[audreyh1]

I also regularly paste reports generated by Quicken into a spreadsheet to do further processing - like calculating my asset allocation and portfolio rebalancing!

Audrey

 

[calmloki]

At year end last year Quicken had us at Diamond Jim Brady levels. Being as I hadn't been lighting any cigars with hundred dollar bills a quick inspection showed that transfers between accounts were our biggest money maker. If I'd known that taking money out of one pocket and putting it in another was so lucrative I'd a never gone into other lines of work.

 

[ERD50]

If I'd known that taking money out of one pocket and putting it in another was so lucrative I'd a never gone into other lines of work.

Politics?

-ERD50

 

[NW-Bound]

Sorry, but I cannot help making the following edit.

If I'd known that taking money out of a person's pocket and putting it in another's was so lucrative I'd never gone into other lines of work.

Politics?

Heh heh heh... If you are not a politician, you would be called a thief, and in some countries, would have your hand cut off.

 

[ItDontMeanAThing]

XL before retirement, Google Docs now.

At the end of my first full year retired I attempted to calculate how much I'd spent by looking at account movements and my check registers, which are spreadsheet versions of the classic paper one.

All monies spent that year came from a single money market account, but then went to one savings and two checking accounts. Funds move both ways between all the accounts and then there is interest and some income. I gave up after two weeks because I there was several thougsand dollars difference between this calculation and my spreadsheet where I record daily spending.

I'm a computer programmer with a spreadsheet, I should be able to solve this for the new year, I thought. The result may be overkill, but it works.

I created a spreadsheet for each of the savings and checking accounts. There are six columns for incoming money (from MMF, savings, checking, interest and income) and five for outgoing (to savings, checking, MMF, ATM, electronic bill paying). One spreadsheet looks at the other 3 and summarizes. More importantly it has error checking, e.g., the amounts transfered out of the two checking accounts better agree with the amount coming into the savings account from checking. A double entry check register, I think.

Then I screwed it up by opening a Thai Baht savings account. Only took a week to incorporate it.

 

[FUEGO]

One word, Excel !

+1

This is why I decided to build my expense tracker spreadsheet. I know what goes in and what comes out.

 

[WanderALot]

The engineer in me wants to use Excel, but I just don't seem to be enough of an Excel guru and am not too motivated to learn. Those of you who use Excel or OpenOffice, how do you categorize your downloaded transactions from your different accounts?

I was using Wesabe to do manual imports of my credit cards because it had a great tagging feature to categorize expenses, but Wesabe is now shutdown. This was unfortunate since DW liked using it! They have open-sourced their code so I'm in the process of setting up the code on a Linux box.

I've also debated going to MS Money's Sunset Edition since its free, but worried about long term prospects of support for their download format.

 

[Rustward]

I can find at least one reason to agree with just about every post in this thread.

Personally I like Quicken, but I also use Excel spreadsheets. To each his own. Some of the Quicken reports are difficult to understand at first, but usually if you spend enough time investigating (running with very small subsets of data to understand the behavior) it can usually be figured out; but maybe that flies in the face of Quicken's name. This might be a little easier for me after having spent most of the last 35 years debugging mostly other peoples' code.

The fact that I have 14 years of expense data, and every trade and dividend and CG distribution transaction we ever made in Quicken adds to the value of Quicken for me.

I do think expecting a default report to be just what you wanted is asking a lot of any software -- especially a configurable product like Quicken. I had the same reaction as Rich the first time I ran an expense report. WTF Then I found the Transfers: Exclude All setting on the Advanced Customization tab.

Fully understand why so many people code their own Excel spreadsheets.

Edit to add: Is there anyone here who understood a FireCalc report on the first run?

 

[figner]

The engineer in me wants to use Excel, but I just don't seem to be enough of an Excel guru and am not too motivated to learn. Those of you who use Excel or OpenOffice, how do you categorize your downloaded transactions from your different accounts?

I use the OpenOffice spreadsheet for tracking and rebalancing my portfolio, but for budgeting and tracking categories of expenses I like GnuCash. Have not tried Quicken personally so I can't compare, but it does graphs, double-entry accounting, etc. The graphs aren't all that slick looking but that doesn't bother me.

 

[FUEGO]

Those of you who use Excel or OpenOffice, how do you categorize your downloaded transactions from your different accounts?

I currently do it manually. Sort of manually. I usually get all the expenses in a spreadsheet from a couple credit cards and a copy/paste of my checkbook spreadsheet. Get the format cleaned up some so it is in "expense name"/"date"/"amount" format. Clear out non-expense stuff (like transfers from money market to checking, interest/checking fees that offset each other, investments, etc).

Then I have maybe 30-50 transactions each month. I sort by "expense name" so, for example, all the "Shell Gas" purchases are listed consecutively. Then I go down the column and for each expense I put a number next to it corresponding to the expense category. Gas might be a 10. Walmart/Target/Grocery might be 13. Most expenses fall into a few different category numbers (gas, groceries, dining out primarily).

Then I sort by category number and copy/paste the groups of expenses into my expense tracking spreadsheet for each of my ~24 categories.

Takes about 30 minutes each month, or if I did it quarterly it would probably take 45-60 minutes a quarter.

I imagine I could write a macro and get some automation built into my spreadsheet and automatically categorize some expenses. But it would still require manual tweaking. For example, I just went on vacation on 7/31-8/2 and I'm going again 8/7-8/9/2010. As a result, I want all expenses incurred on those dates to get categorized as "Vacation" expenses, not gas, groceries, dining out, etc. Easy to implement this with my manual method. Probably easier than with quicken since I can sort my raw expense data by date, paste a "19" code (for "vacation") on all the 7/31-8/2 and 8/7-8/9/2010 expenses, and move on to categorizing the rest of the expenses outside that date range.

As to creating "custom reports", I guess I could create a new worksheet for each report that I wanted. But I have some pretty basic needs for expense tracking. What am I spending per month/year on the basics, and what on non-essentials (vacation, charity, etc). What about mortgage which will go away before FIRE? If I wanted more complicated reports I guess I would have to spring for quicken. I guess my data is already in a sufficient format to import into quicken if I ever wanted to switch over.

I track my investments and capital gains separately with another spreadsheet. Usually update it near the end of the year for the one or two dozen taxable buys and sells that I have each year.

 

[WanderALot]

I use the OpenOffice spreadsheet for tracking and rebalancing my portfolio, but for budgeting and tracking categories of expenses I like GnuCash.

Thanks figner. I tried GnuCash years ago and it was a bit too quirky for me, I'll give it a shot again.

I currently do it manually. Sort of manually. I usually get all the expenses in a spreadsheet from a couple credit cards and a copy/paste of my checkbook spreadsheet. Get the format cleaned up some so it is in "expense name"/"date"/"amount" format. Clear out non-expense stuff (like transfers from money market to checking, interest/checking fees that offset each other, investments, etc).

Thanks Fuego. This is what I was afraid of. I love the automatic categorization of transactions that Quicken/Money/Mint.com/Wesabe offers. When I did things manually, I found out that I dreaded the chore (especially since DW couldn't get herself to help) that I never got around to it.

I'm too chicken to give my info to Mint.com and don't want to tied to Quicken/Money, so those options are out.

 

[FUEGO]

Thanks Fuego. This is what I was afraid of. I love the automatic categorization of transactions that Quicken/Money/Mint.com/Wesabe offers. When I did things manually, I found out that I dreaded the chore (especially since DW couldn't get herself to help) that I never got around to it.

I'm too chicken to give my info to Mint.com and don't want to tied to Quicken/Money, so those options are out.

It really isn't too bad assuming you use credit cards for almost all your expenses. We do. I think we have maybe 2-3 cash transactions a month, and I don't really care to track anything under a few bucks. We balance out the cash each month, and so far we had a net of $4 unaccounted for from cash. Probably 90% of the remainder of the transactions were from the CC statement downloads. And many of the purchases are from the same places. Ie - 5 purchases from Walmart in July - just mark "13" for each of the 5. Next is 4 purchases from McDonald's - put a 17 next to those. 5 gas purchases at Shell - that's a 9. Since the transactions are sorted alphabetically all the same stores are grouped together, making manual classification a lot easier. And the scalability of the chore would make it a lot quicker to do the categorization once a quarter versus once a month (ie categorizing 15 gas purchases at Shell might take a second longer than categorizing 5 purchases at Shell).

The actual categorization task itself takes only a few minutes. Getting the data all into the same expense/date/amount format takes a little longer, and then copy/pasting it into my master expense tracking spreadsheet takes a little longer to manually do. I may re-write my spreadsheet some to automate it, but so far it isn't too burdensome to manually classify data.

DW's chore is to tell me when she has a cash transaction over a few bucks, and let me count her cash once a month. So far I haven't had any questions on her credit card spending - it has been clear enough from the CC statement's store description.

Works for us!

 

[deserat]

I have used many systems over the years - I have some old Quicken files, spreadsheet files and now MoneyDance. My husband tracks the investments in a spreadsheet as well as uses Quicken for expenses. We correlate once a year to figure out our yearly costs of living and then look at specific categories which seem high. I'm kind of like Nords in that budgeting isn't the issue now, just tracking and looking at the bigger picture. We're quite good at LBOM and will be when fully retired - I've looked at it as building streams of income for retirement and when those reach the magic number - poof - we are living off those streams. I think that tax efficiency will be our issue and not income...especially with the looming tax increases and government deficits.

Bottom line - YMMV