Join Early Retirement Today
Reply
 
Thread Tools Search this Thread Display Modes
Simple Expense Tracking
Old 09-01-2007, 05:13 PM   #1
Recycles dryer sheets
 
Join Date: Jan 2006
Posts: 163
Simple Expense Tracking

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)?
Attached Files
File Type: zip ExpenseLog.zip (32.0 KB, 123 views)
__________________

__________________
Seems to me that the corporation's race to the top is resulting in a race to the bottom for the employee's quality of life. FIRE can't come soon enough.
kjpliny is offline   Reply With Quote
Join the #1 Early Retirement and Financial Independence Forum Today - It's Totally Free!

Are you planning to be financially independent as early as possible so you can live life on your own terms? Discuss successful investing strategies, asset allocation models, tax strategies and other related topics in our online forum community. Our members range from young folks just starting their journey to financial independence, military retirees and even multimillionaires. No matter where you fit in you'll find that Early-Retirement.org is a great community to join. Best of all it's totally FREE!

You are currently viewing our boards as a guest so you have limited access to our community. Please take the time to register and you will gain a lot of great new features including; the ability to participate in discussions, network with our members, see fewer ads, upload photographs, create a retirement blog, send private messages and so much, much more!

Old 09-01-2007, 09:04 PM   #2
Recycles dryer sheets
 
Join Date: Jan 2006
Posts: 163
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.
Attached Files
File Type: zip ExpenseLog1.zip (39.4 KB, 95 views)
__________________

__________________
Seems to me that the corporation's race to the top is resulting in a race to the bottom for the employee's quality of life. FIRE can't come soon enough.
kjpliny is offline   Reply With Quote
Old 09-02-2007, 09:12 AM   #3
Recycles dryer sheets
 
Join Date: Jan 2006
Posts: 163
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).
Attached Images
File Type: jpg Calc_options.jpg (21.1 KB, 14 views)
__________________
Seems to me that the corporation's race to the top is resulting in a race to the bottom for the employee's quality of life. FIRE can't come soon enough.
kjpliny is offline   Reply With Quote
Old 09-02-2007, 09:42 AM   #4
Recycles dryer sheets
 
Join Date: Aug 2007
Location: Midwest
Posts: 109
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?
__________________
Engineering My Finances Blog
EngineeringMyFinances is offline   Reply With Quote
Old 09-02-2007, 10:17 AM   #5
Recycles dryer sheets
 
Join Date: Jan 2006
Posts: 163
Quote:
Originally Posted by EngineeringMyFinances View Post
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.
__________________
Seems to me that the corporation's race to the top is resulting in a race to the bottom for the employee's quality of life. FIRE can't come soon enough.
kjpliny is offline   Reply With Quote
Old 10-09-2007, 01:12 PM   #6
Thinks s/he gets paid by the post
 
Join Date: Apr 2007
Posts: 1,304
Quote:
Originally Posted by kjpliny View Post
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.
__________________
Life is GREAT!
megacorp-firee is offline   Reply With Quote
Old 10-09-2007, 06:29 PM   #7
Recycles dryer sheets
 
Join Date: Jan 2006
Posts: 163
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.
__________________
Seems to me that the corporation's race to the top is resulting in a race to the bottom for the employee's quality of life. FIRE can't come soon enough.
kjpliny is offline   Reply With Quote
Old 10-10-2007, 02:45 AM   #8
Recycles dryer sheets
 
Join Date: Mar 2006
Posts: 126
Kjplini

I found your spreadsheet very useful.

Thank you so much!

X-man
__________________
xmanz3 is offline   Reply With Quote
Old 10-10-2007, 07:03 PM   #9
Recycles dryer sheets
 
Join Date: Jan 2006
Posts: 163
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.
__________________
Seems to me that the corporation's race to the top is resulting in a race to the bottom for the employee's quality of life. FIRE can't come soon enough.
kjpliny is offline   Reply With Quote
Old 10-10-2007, 08:07 PM   #10
Thinks s/he gets paid by the post
 
Join Date: Mar 2004
Posts: 2,083
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
__________________
I look to the present moment because that's where I live my life.
MJ is offline   Reply With Quote
Old 10-11-2007, 05:24 PM   #11
Recycles dryer sheets
 
Join Date: Jan 2006
Posts: 163
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.
__________________

__________________
Seems to me that the corporation's race to the top is resulting in a race to the bottom for the employee's quality of life. FIRE can't come soon enough.
kjpliny is offline   Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Drugstore.com offers eligible HSA expense tracking... mykidslovedogs Health and Early Retirement 1 08-16-2007 11:30 AM
Portfolio tracking software jimhcom FIRE and Money 5 12-28-2006 08:55 AM
Tracking investments Rosalita FIRE and Money 11 08-02-2006 10:11 PM
Tracking finances virginia Young Dreamers 31 07-07-2006 12:17 PM
Tracking your money nuisance FIRE and Money 18 08-05-2005 10:15 AM

 

 
All times are GMT -6. The time now is 05:22 AM.
 
Powered by vBulletin® Version 3.8.8 Beta 1
Copyright ©2000 - 2017, vBulletin Solutions, Inc.