Simple Expense Tracking

[kjpliny]

This is the spreadsheet I created this year for tracking my expenses. It's simple, but it works for me. I know a lot of forum users use Quicken or similar programs, but I found them to be too complicated for what I needed. Figured I'd post in case anyone finds useful.

To update for 2008, you can create a new worksheet, copy and paste the existing expense summary, and change the '2007' in all row 4 formulas, then copy the row down. Same for income formulas in row 36.

Any suggestions for automatically updating for a new year (maybe a macro or cell pointer)?

[kjpliny]

Figured out a way to have Expense Summary formulas change based on Year beginning date. You can now create a new sheet for 2008, copy and paste the 2007 table, change the beginning date in cell B1 and the summary should read all expense and income entries for the new year.

Also, found an error in previous file where Expense Summary formula for July did not include 7/31. This monthly date range lookup feature fixes that too.

[kjpliny]

Update - I noticed after adding the month lookup in the expense summary table that the data entry of both expenses and income slowed down dramatically due to calculations being made each time you leave a cell. Changing from auto to manual calculation under options speeds this up by doing all the calculations only when you save (or press F9).

[EngineeringMyFinances]

I don't track my expenses in detail so for myself I wouldn't use it. But I did find the use of the SUMPRODUCT interesting and will keep it in mind for an application of my own. Thanks for sharing it.

I would suggest separate spreadsheets for each year rather than adding worksheets. That may speed up your calculation. OTOH, since it's your spreadsheet and you know you have to manually force recalculation, it may work fine to add a worksheet for each year. If it gets too slow, you can always make a separate copy for each year and delete the extraneous data.

But you'll have to do something anyway when you have 5000 transactions recorded, since it won't summarize more than that. Recommend you at least add a mark around the 5000th row so you won't forget several years from now.

Spreadsheets are useful for recording and analyzing the past. But they also can be used to predict the future. Have you set up anything to plan your retirement?

[kjpliny]

Quote:

Originally Posted by EngineeringMyFinances I don't track my expenses in detail so for myself I wouldn't use it. But I did find the use of the SUMPRODUCT interesting and will keep it in mind for an application of my own. Thanks for sharing it. I would suggest separate spreadsheets for each year rather than adding worksheets. That may speed up your calculation. OTOH, since it's your spreadsheet and you know you have to manually force recalculation, it may work fine to add a worksheet for each year. If it gets too slow, you can always make a separate copy for each year and delete the extraneous data. But you'll have to do something anyway when you have 5000 transactions recorded, since it won't summarize more than that. Recommend you at least add a mark around the 5000th row so you won't forget several years from now. Spreadsheets are useful for recording and analyzing the past. But they also can be used to predict the future. Have you set up anything to plan your retirement?

Good point...I think you're right about saving as a new file for next year and clear existing tables. Exceeding 5000 transactions per year is not likely, so that would solve the limit issue.

This spreadsheet is not set up to track investments or income from investments...and I don't have a specific tool for forcasting. This is the first year of tracking expenses because I wanted to get a handle on where all the money is going. I also want to see if we can live on one salary because my job may be in jeopardy do to a corporate restructuring going on. By reviewing the expenses by category, I can get a better understanding on where we can cut back the spending if necessary.

We're currently putting away about $50k/yr in pre and post tax savings. Wife's pension at 55 will cover all expenses as far as I can tell, so my plan is to keep her as happy as possible.

Financial Plan:
Step 1: Take course on how to be a model husband.
Step 2: Implement techniques learned in step 1.

[megacorp-firee]

Quote:

Originally Posted by kjpliny Figured out a way to have Expense Summary formulas change based on Year beginning date. You can now create a new sheet for 2008, copy and paste the 2007 table, change the beginning date in cell B1 and the summary should read all expense and income entries for the new year. Also, found an error in previous file where Expense Summary formula for July did not include 7/31. This monthly date range lookup feature fixes that too.

This SS is great. I just started using it and have gone back to July 1st (retirement start) and started entering all data.
Question kjpliny, how do I get this to work for 2009 and beyond? I see the 2008 summary sheet you have added. Thanks.

[kjpliny]

Hi Mega,

I'm finishing up my first year using this and am thinking I will just save as a new file, name it 2008, and clear all previous expense and income detail entries. The only thing you have to do is change the date in cell B1 of the Expense Summary sheet. This will then return all values for the new year as you enter them on the Expense Detail sheet.

[xmanz3]

Kjplini

I found your spreadsheet very useful.

Thank you so much!

X-man

[kjpliny]

For those of you who are not Excel geeks, if you want to modify one of the drop down choices you just have to update the corresponding value in column A of the Expense Summary to match. To make it easier, I could have input formulas in column A to point to the appropriate cell in the Expense Detail sheet, but I didn't think of it at the time I made the sheet.

Also, if you wanted to add an expense type, you just have to add another row to the expense summary and also "re-validate" the category column in the Expense Detail sheet to include the additional selection (extend the cell range) by highlighting the column and using menu action Data, Validate, Source. Same goes for the selections under Income Types.

[MJ]

I tried opening your file several times using OpenOffice.Org but it crashes. It is the 1st excel file that has crashed on me.
Are you doing something unusual with this file?

Although I use a multisheet inter-connected spreadsheet that does a great job for me, I thought I would take a look at yours.

MJ

[kjpliny]

Hi MJ,

I'm not familiar with OpenOffice.org, but it might have trouble with the SUMPRODUCT function, which is the only advanced cell formula used in the spreadsheet.