Keeping track of spending categories?

[MKKShah]

Not everyone is sold on Mint.com. It has its issues. For one, you can't manually upload your data without Mint wanting access to your accounts. For another, it's my understanding (I could be wrong) that you can only add up to three months' worth of data via the synch feature.

You are right on both accounts. Yes you can' upload, and it only pulls in 3 months of data. However, I do think the 90 day limitation is from the financial institutions. (And since mint wont allow you to upload manually, you can't have more than 3 months data when you start). After you start though it continues to store everything (likely forever). I originally signed up in 2009. But never look at the accounts for most of the last 2.5 years. And when I wanted to do the math on my spending for the entire year 2011 (last month), everything was there, and I was impressed. YMMV.

[kork13]

Boy, I wish I could help you with that - I really, truly enjoy building worksheets.

haha me too... is it bad to confess that? haha

Really, you just need to find what works for you. I've got my gucci excel spreadsheet that works great for me, but many (most) others find some automatic program that does well for them. Find your 'thing' and stick to it.

[mrpaseo]

I would like to see some of these excel trackers that you all use. I am willing to share a sterile copy of the one I developed.

As you can see I use excel, actually, I used it for about 45 days and learned what I needed to learn. The only hard part is getting my DW to keep track and report what she spends... It became a chore and frankly I learned what I needed to learn in the first 45 days.

That said, if anyone wants to see what I put together I will figure out where I can put it so others can see... Maybe this google documents?

Ray

[Frügal]

In Google Docs, you can make it viewable to the public, and keep it from being edited.

[LMA]

I agree with Nika that Excel is the best way to go. You can make it whatever you want. There are a gazillion budgets in Excel in the internet right now and you can pick one that works for you. Plus you can get Excel for free through Open Office in case you don't already have it.

I download information from my bank straight into Excel so there is no data entry. All i do is organize into the categories I want to track and that's it. You can look in more detail at an example of how I set it up my personal budgeting.

Of course IMHO the best way to stick with a budget is to make it simple so you cannot go wrong with just a paper notebook where you keep track of income vs spending.

I also recommend to keep the categories to a minimum. You can have a 1,000 categories if you really want to track it all but then it becomes too complicated and for budgeting to work it needs to be quick and easy so you can do it weekly.

[Frügal]

This is getting irritating...

I cannot find anything in any guides or online telling me how to keep running totals of spending categories. I'm having to do everything by hand, completely defeating the purpose of spreadsheets.

So, at any given time, I don't know how much we have left in certain categories. Sweet... Ugh! Missing that iPhone. Definitely getting an iPod Touch when we can.

[humandraydel]

This is getting irritating...

I cannot find anything in any guides or online telling me how to keep running totals of spending categories. I'm having to do everything by hand, completely defeating the purpose of spreadsheets.

So, at any given time, I don't know how much we have left in certain categories. Sweet... Ugh! Missing that iPhone. Definitely getting an iPod Touch when we can.

Spreadsheets can be very powerful, but you have to use the built in functions. Here is a VERY simple (and therefore not very good or robust) example. There are 2 tabs to this worksheet (at the bottom) - one called "Ledger" and one called "Totals". You add things to the ledger worksheet and everything gets added up in the "Totals" worksheet.

https://docs.google.com/spreadsheet/ccc?key=0At0iDVZsCDptdE0taVlRUzRMaWdfWjlMR054TmpjRlE

The ledger is nothing more than a running list. In the Totals worksheet, every cell has an IF statement. If the "Category" of the line item in the ledger is equal to the category at the top of the column, it adds the dollar amount to the column and then sums everything up.

[Frügal]

Spreadsheets can be very powerful, but you have to use the built in functions. Here is a VERY simple (and therefore not very good or robust) example. There are 2 tabs to this worksheet (at the bottom) - one called "Ledger" and one called "Totals". You add things to the ledger worksheet and everything gets added up in the "Totals" worksheet.

https://docs.google.com/spreadsheet/ccc?key=0At0iDVZsCDptdE0taVlRUzRMaWdfWjlMR054TmpjRlE

The ledger is nothing more than a running list. In the Totals worksheet, every cell has an IF statement. If the "Category" of the line item in the ledger is equal to the category at the top of the column, it adds the dollar amount to the column and then sums everything up.

Sweet. How do I do that?

Here is a link to mine (changed values and descriptions to retain some anonymity): https://docs.google.com/spreadsheet/...3ZBOXBYdDVTcWc

[kork13]

Sweet. How do I do that?

Here is a link to mine (changed values and descriptions to retain some anonymity): https://docs.google.com/spreadsheet/...3ZBOXBYdDVTcWc

You probably want to use "SUMIF" equations, which are really useful with expense tracking. Here's the basic format for using them... In the cell you want to use as your "category total", type: =SUMIF(D: D,Gas,E:E)

So what that'll do is if any cell in the "D" column (Category) is "Gas", then it'll add the corresponding value in your "E" column (Amount). With that one formula, it'll automatically add up everything you spend on "Gas". You'll need to set up a SUMIF equation for each category you are going to use (Rent, Clothing, Groceries, etc.), so try to pick a few general categories that you know you'll use often. You may also consider grouping your categories, so instead of using individual categories for phone, cable, internet, water, and electricity, you might want to group all of them simply to be "Utilities". That'll make you spreadsheets simpler to use.

(hopefully all that makes sense...)

[Frügal]

You probably want to use "SUMIF" equations, which are really useful with expense tracking. Here's the basic format for using them... In the cell you want to use as your "category total", type: =SUMIF(D: D,Gas,E:E)

So what that'll do is if any cell in the "D" column (Category) is "Gas", then it'll add the corresponding value in your "E" column (Amount). With that one formula, it'll automatically add up everything you spend on "Gas". You'll need to set up a SUMIF equation for each category you are going to use (Rent, Clothing, Groceries, etc.), so try to pick a few general categories that you know you'll use often. You may also consider grouping your categories, so instead of using individual categories for phone, cable, internet, water, and electricity, you might want to group all of them simply to be "Utilities". That'll make you spreadsheets simpler to use.

(hopefully all that makes sense...)

That makes sense, indeed!

So, for Phones, I would use "=SUMIF(D: D,Phone,E:E)"? Or do I need to change more?

Also, I have generalized categories before, but I think for monitoring purposes, I do better seeing exactly where my money is going. Also helps me remember when to pay.

Thanks much! I will give this a shot once the daughter is not being needy. Ha

edit: Did not work. "#NAME?" is displayed...

[kork13]

That makes sense, indeed!

So, for Phones, I would use "=SUMIF(D: D,Phone,E:E)"? Or do I need to change more?

Also, I have generalized categories before, but I think for monitoring purposes, I do better seeing exactly where my money is going. Also helps me remember when to pay.

Thanks much! I will give this a shot once the daughter is not being needy. Ha

edit: Did not work. "#NAME?" is displayed...

Did you take out the space between the D's? Otherwise, try restricting the ranges to exclude text... make the ranges "D3: D400" and "E3:E400" (again, take out the space with the D's)

[Frügal]

I've no clue what's going on...

https://docs.google.com/spreadsheet/ccc?key=0AoMxlCeYZJVRdElBMEFtUVEtVVgxX3ZBOXBYdDVTcWc

Looks like removing the 'Gas' worked, but how is it going to track gas expenses...?

[kork13]

I've no clue what's going on...

https://docs.google.com/spreadsheet/ccc?key=0AoMxlCeYZJVRdElBMEFtUVEtVVgxX3ZBOXBYdDVTcWc

Looks like removing the 'Gas' worked, but how is it going to track gas expenses...?

Try this: =SUMIF(Register!D3: D400,"=Gas",Register!E3:E400)

It didn't work the first time because I didn't realize you were doing the totals on a separate tab... so my bad there.

If that doesn't work, I suspect it has to do with your formatting -- I copied your spreadsheet into excel, used the formula above, and by taking away the formatting, it worked fine. How are you typing the amounts into your spreadsheet? Do you just type the number, or are you typing the dollar sign and parentheses as well? It appears that the equation is not recognizing your 'Amount' entries as numbers, so it's trying to "add" them as text... obviously not gonna work.

(btw, this is unfortunately pretty typical with excel... you have to go through a few rounds of trial/error to make sure your equations are done correctly. Once you get it down, you're set....which is why I'm getting the format wrong myself -- I've not built these equations for years. )

[jpg7n16]

Try either changing Gas to "Gas", or referencing another cell.

=SUMIF(D: D,"Gas",E:E)

{don't put a space in the D: D section. The only reason the space is there is because there's a smilie with it}
Otherwise my post would look like this!
=SUMIF(D,"Gas",E:E)

Will do the columns and add up anything with "Gas" in column D.

Check this one out:

https://docs.google.com/spreadsheet/ccc?key=0AqUB5Cvg7RXidE4ySEVadGFZZDl3SVZtSkdsMjRyb2c#gid=0

Oh and I updated your running total balance column too. Worth looking at that too. Let the spreadsheet do the work for you

[Frügal]

Try either changing Gas to "Gas", or referencing another cell.

=SUMIF(D: D,"Gas",E:E)

{don't put a space in the D: D section. The only reason the space is there is because there's a smilie with it}
Otherwise my post would look like this!
=SUMIF(D,"Gas",E:E)

Will do the columns and add up anything with "Gas" in column D.

Check this one out:

https://docs.google.com/spreadsheet/ccc?key=0AqUB5Cvg7RXidE4ySEVadGFZZDl3SVZtSkdsMjRyb2c#gid=0

Oh and I updated your running total balance column too. Worth looking at that too. Let the spreadsheet do the work for you

Access requested.

Since switching to using only one tab, the formula is working.