Accounting / Financial Tracking & Planning Software

[Illusions]

So I'm looking for suggestions or personal experience with software that can track my expenditures and income, and offer graphs of this data for analysis. Basically what I want to be able to do is the following:

- Input daily income and expenditures w/ predefined or custom categories (Bills, Food, Necessary, Luxury, etc.)
- Graph this data overall and at the individual category level with the option for comparisons

Having never used accounting or financial planning software before, I'm uncertain if all of these features are available, at what cost, or which software would be the best for my purposes. The reason I'm coming here though, instead of say...doing a Google search, is that I'm also mostly interested in personal experience with this type of software, not just feature lists, etc.

[Wraith]

Excel can do what you want. You probably already have it too.

I have no personal experience with dedicated financial software, so I'm out of advice now.

[Hazir]

Don't bother. If you are disciplined enough to actually use this software, you're not one of the people who needs it. And otherwise, you won't have the discipline to actually fill it with all necessary data.

[CitizenCain]

Don't bother. If you are disciplined enough to actually use this software, you're not one of the people who needs it.

How do you figure that? You can remember individual entries for your personal accounting from 2005?

Anyway, like Wraith said, Excel's probably your best bet, and you can do all that with macros pretty easily, if the built-in graphing functions don't have you covered on that front already. All the other dedicated accounting/financial software that I'm aware of is really aimed at groups and businesses, not individuals, so I'm not sure how useful they'd be for your purposes, even if you could get one without paying the obscene sticker price most of the popular accounting tools charge.

[Wraith]

How do you figure that? You can remember individual entries for your personal accounting from 2005?

Anyway, like Wraith said, Excel's probably your best bet, and you can do all that with macros pretty easily, if the built-in graphing functions don't have you covered on that front already. All the other dedicated accounting/financial software that I'm aware of is really aimed at groups and businesses, not individuals, so I'm not sure how useful they'd be for your purposes, even if you could get one without paying the obscene sticker price most of the popular accounting tools charge.

Don't even need macros, I think. PivotTables ought to do it.

[Dreadnaught]

Agreed, unless you do everything with credit cards and want that info auto-linked to a site like Mint.com...spreadsheets are the best for this.

I used Google Spreadsheets for this, mainly so I can edit it anywhere (EG doing personal expenses while things are slow at work) and put it behind two-step password authentication.

It's worth taking the time to set up a monthly spreadsheet. But I actually don't think having graphs is especially useful. I have one tab in my spreadsheet that graphs this stuff and it's honestly only worth seeing at the end of the year. Otherwise, I find it more useful to have each tab/month of my budget spreadsheet deliver important top-level metrics:

- Monthly income
- Monthly expenditures
- $ Over/under set budget (which is presumably/hopefully a lower number than my anticipated income)
- Bank balance (based on start-of-month balance +/- income and expenditures)

Wraith has a good idea about pivot tables. I have three broad categories behind my expenses. I feel dumb, but I never even though of assigning some consistent labels to the expenses and using a pivot to do a deep-dive. Like I have the column for that data, but I don't label consistently enough for pivots.

[Illusions]

Don't bother. If you are disciplined enough to actually use this software, you're not one of the people who needs it. And otherwise, you won't have the discipline to actually fill it with all necessary data.

My local bank only keeps history on 30 days worth of transactions, and I pay nearly everything by debit so its rather difficult to track spending habits and how fast I'm saving money, or even exactly how much over a period of time, when that period of time only reflects 30 days. My main reason for doing this isn't so much so I can be more disciplined at saving, but to see trends in my spending and saving.

@Everyone Else - I figured Excel, I was just uncertain if there was anything better out there. If I'm going to go this route I'll need to know a bit more about how I should lay this out, since I want to be able to look on a monthly, quarterly, and yearly basis. I'll look into pivot tables as well. Thansk for all the help though.

@Dreadnaught - I was also considering Google Documents, however I'm uncertain of how secure I'd feel with my financial data being online. You mentioned something about two step password authentication, how exactly does that work? The reason I ask is mainly because using Excel would be simple in keeping said data safe, I'd just throw it in a password protected TrueCrypt volume. Google Document...not so much. As for graphs, I'm a visual person, I'd really like to see my data in a manner that makes sense to me in a way that numbers are not immediately or rapidly apparent.

[Wraith]

@Everyone Else - I figured Excel, I was just uncertain if there was anything better out there. If I'm going to go this route I'll need to know a bit more about how I should lay this out, since I want to be able to look on a monthly, quarterly, and yearly basis. I'll look into pivot tables as well. Thansk for all the help though.

Dedicated software for it is probably a bit easier to get going, but if you already have excel, it's probably not worth spending the cash to buy one of those.

For setup, just set up a table with all the columns you want to track. Date, amount, type, and whatever else you want. Just start adding stuff to the bottom of the table - the table should be strictly data entry, not viewing the data. For viewing it, you should insert a PivotTable based on the table (click a cell on the table, insert tab->PivotTable, done). To make things easier, you can set up some calculated columns on your table; ones that'll probably be useful/interesting are Year, Month, Day, Weekday. Calculated columns will keep you from having to enter too much data.

For the PivotTable, you can just start tossing interesting fields on there. You should figure them out pretty quickly. You can add calculated columns here too/instead, if you want to keep from polluting your base table. If you have 2010 or later, you can also drop some slicers on there to build a dashboard for quickly analyzing all your data. Also look into show values as once you start getting a lot of data - it's in the right-click menu.

[CitizenCain]

@Dreadnaught - I was also considering Google Documents, however I'm uncertain of how secure I'd feel with my financial data being online. You mentioned something about two step password authentication, how exactly does that work?

You have a password, and you need to enter an additional code they text to your phone to log in. (Additionally, you can designate computers that won't be subject to the second authentication factor for 30 days at a time.) I use their 2 factor auth for some of my Google accounts.

For sensitive data, like this would be, I don't put it online, and wouldn't trust it to be there. I personally, have a secure remote-access setup for my home LAN, and when I need to access something like this on the road, I remote in and do what I need to do, but ultimately, it's all protected by crypto and AAA systems I control, rather than out "in the cloud" and subject to access by only God knows who.

[Hazir]

How do you figure that? You can remember individual entries for your personal accounting from 2005?

Anyway, like Wraith said, Excel's probably your best bet, and you can do all that with macros pretty easily, if the built-in graphing functions don't have you covered on that front already. All the other dedicated accounting/financial software that I'm aware of is really aimed at groups and businesses, not individuals, so I'm not sure how useful they'd be for your purposes, even if you could get one without paying the obscene sticker price most of the popular accounting tools charge.

No, and I don't think there is any need to know your financial transactions to that level of detail. Keeping track of your income and expenses has one goal only for a normal person and that is to balance the two. Then maybe to save a little bit.

By the way, fiscal disciple is not the same as administrative discipline. Of the first I have my fair share, of the second I have very little in my private live.

[CitizenCain]

No, and I don't think there is any need to know your financial transactions to that level of detail.

Well, tell it to the IRS. They seem to feel differently.

[Dreadnaught]

My local bank only keeps history on 30 days worth of transactions, and I pay nearly everything by debit so its rather difficult to track spending habits and how fast I'm saving money, or even exactly how much over a period of time, when that period of time only reflects 30 days. My main reason for doing this isn't so much so I can be more disciplined at saving, but to see trends in my spending and saving.

@Everyone Else - I figured Excel, I was just uncertain if there was anything better out there. If I'm going to go this route I'll need to know a bit more about how I should lay this out, since I want to be able to look on a monthly, quarterly, and yearly basis. I'll look into pivot tables as well. Thansk for all the help though.

@Dreadnaught - I was also considering Google Documents, however I'm uncertain of how secure I'd feel with my financial data being online. You mentioned something about two step password authentication, how exactly does that work? The reason I ask is mainly because using Excel would be simple in keeping said data safe, I'd just throw it in a password protected TrueCrypt volume. Google Document...not so much. As for graphs, I'm a visual person, I'd really like to see my data in a manner that makes sense to me in a way that numbers are not immediately or rapidly apparent.

I personally recommend a single spreadsheet for each year, with separate tabs for each month and one tab summarizing the year for you. It aligns your schedule with the tax year and, to be honest, you probably want to be tracking things at the monthly level more than anything else.

When it comes to security, I'm not sure why you see a password-protected volume on your HD somehow more safe than a password-protected file in Google Docs.

Some info about two-step auth:

http://googleblog.blogspot.com/2011/...-for-your.html
Good video: http://youtu.be/zMabEyrtPRg

I honestly think it's more secure, plus you don't lose data if someone breaks into your home and steals your desktop. Or if your HD crashes. If you're Cain and you're worried about being able to destroy data in case of a subpoena, I can see some advantages to a local storage solution. But I personally feel much safer knowing my data is redundantly backed-up in the cloud and protected behind a two-step authentication process.

As for visuals...well, I'm the same, but I've found that the only visual cue I really need to understand is the + or - sign.

Dedicated software for it is probably a bit easier to get going, but if you already have excel, it's probably not worth spending the cash to buy one of those.

For setup, just set up a table with all the columns you want to track. Date, amount, type, and whatever else you want. Just start adding stuff to the bottom of the table - the table should be strictly data entry, not viewing the data. For viewing it, you should insert a PivotTable based on the table (click a cell on the table, insert tab->PivotTable, done). To make things easier, you can set up some calculated columns on your table; ones that'll probably be useful/interesting are Year, Month, Day, Weekday. Calculated columns will keep you from having to enter too much data.

For the PivotTable, you can just start tossing interesting fields on there. You should figure them out pretty quickly. You can add calculated columns here too/instead, if you want to keep from polluting your base table. If you have 2010 or later, you can also drop some slicers on there to build a dashboard for quickly analyzing all your data. Also look into show values as once you start getting a lot of data - it's in the right-click menu.

Out of curiosity, what are your top five fields? Not asking for any particular data like your weird bondage habit that consumes 10% of your expenditures, but curious if you have like five broad categories or if you itemize with a bit more specificity.

[Illusions]

Keeping track of your income and expenses has one goal only for a normal person and that is to balance the two. Then maybe to save a little bit.

I guess then that I'm not a normal person since I mainly want to do this to see trends and comparisons, as opposed to how much I'm spending/saving.

I personally recommend a single spreadsheet for each year, with separate tabs for each month and one tab summarizing the year for you. It aligns your schedule with the tax year and, to be honest, you probably want to be tracking things at the monthly level more than anything else.

Monthly and yearly mainly (or from the start of data tracking to whatever day I'm looking at the data). Thanks for the suggestions.

When it comes to security, I'm not sure why you see a password-protected volume on your HD somehow more safe than a password-protected file in Google Docs.

Because with a password protected volume on my HDD the only point of access is via one point located inside of my house. That and I can have a password thats longer than the one I use for Google Docs (since I don't want to inconvenience myself daily with an incredibly long password on my Google account).

I honestly think it's more secure, plus you don't lose data if someone breaks into your home and steals your desktop. Or if your HD crashes. If you're Cain and you're worried about being able to destroy data in case of a subpoena, I can see some advantages to a local storage solution. But I personally feel much safer knowing my data is redundantly backed-up in the cloud and protected behind a two-step authentication process.

I have FTP storage that I pay yearly for, so I could always upload the encrypted copies to that for backup.

As for visuals...well, I'm the same, but I've found that the only visual cue I really need to understand is the + or - sign.

And I like graphs. (Well made and intelligently designed) Graphs are quick and easy to understand.

[Wraith]

Out of curiosity, what are your top five fields? Not asking for any particular data like your weird bondage habit that consumes 10% of your expenditures, but curious if you have like five broad categories or if you itemize with a bit more specificity.

Wasn't speaking from personal experience. I track my stuff in my head, so I don't have an actual workbook anywhere. But if you want top categories, it would be housing, utilities, student loan, food, gas, in that order. That last category is about $90/mo, and there's not much room to skimp on anything in that list.

[CitizenCain]

I honestly think it's more secure, plus you don't lose data if someone breaks into your home and steals your desktop. Or if your HD crashes. If you're Cain and you're worried about being able to destroy data in case of a subpoena, I can see some advantages to a local storage solution.

Please, that would be against the law. Better to just hand them the encrypted data and tell them to call you in 76 trillion years. (Well, in this country, anyway... I understand that in the UK police state, they can sentence people to 2 years in in jail for failing to incriminate themselves. In your country, I recommend steganography.)

But I personally feel much safer knowing my data is redundantly backed-up in the cloud and protected behind a two-step authentication process.

Um, my local data is also backed up... and there's nothing saying that there aren't encrypted containers out there in the cloud containing Cain data either.

Redundancy doesn't have to mean a lack of data security, but if your redundancy solution is slapping your stuff up in plaintext on some web service, you really have no idea who all has access to your data.

[Hazir]

I guess then that I'm not a normal person since I mainly want to do this to see trends and comparisons, as opposed to how much I'm spending/saving.

And why would you like to know these trends? Just because you love to accrue numbers for no particular reason? If that is the case you are not an average person. There could be collections of figures much more satisfying than your income and expenses then, like the weekly financial report in the back the Economist newspaper.

[Dreadnaught]

Wasn't speaking from personal experience. I track my stuff in my head, so I don't have an actual workbook anywhere. But if you want top categories, it would be housing, utilities, student loan, food, gas, in that order. That last category is about $90/mo, and there's not much room to skimp on anything in that list.

All this and you don't even use one!

Well, because it's early in the year, I spent a slow half hour at work re-jiggering my budget spreadsheet. I always had my stuff broadly categorized into "monthly", "other" and "off-budget" (big-ticket things that don't get deducted from my monthly budget and are basically coming from savings, EG new car radio).

But I added codes to all of the stuff in the "other" category. Then, on my YTD summary page, I used a bunch of SUMIFs to tally those categories. It's looking really sweet, and getting some great insight into how much I spend beyond just whether I'm anywhere close to my top-level monthly budget.

And yes, the YTD graph has two charts, but they really aren't as useful as the hard numbers. I have to agree with Hazir, Illusions: what's the point if you're not actually tracking your expenses and using this data to stick to spending goals?

[Illusions]

And why would you like to know these trends? Just because you love to accrue numbers for no particular reason? If that is the case you are not an average person. There could be collections of figures much more satisfying than your income and expenses then, like the weekly financial report in the back the Economist newspaper.

Illusions: what's the point if you're not actually tracking your expenses and using this data to stick to spending goals?

Well a few reasons:

1) I'd like to see how my savings has grown with my current spending behavior, and how it will likely grow in the future
2) Again, my bank doesn't keep any transactions longer than 30 days so this would be a way of logging it
3) So far I just know that I'm saving money, not how much I am out of what I make monthly
4) I could probably be more frugal on food I buy for lunch at work, just not certain how much
5) I'm already very frugal, to the point of putting off buying things I need or that would be useful that aren't luxury items. I would like some number based assurances that its okay to get these things.

[DecoyMilk]

Few things:

I use a budgeting software called 'You Need a Budget'. It's pretty good actually, it's based on an envelope system, so you split all your bills into separate categories, then you budget how much you can spend in category per month, and then track what you actually spend. If you don't spend all of your budgeted amount, it rolls over. So every dollar you earn has a 'job' as such. It's quite a good idea, and it has helped me track my expenses. You can import all of your bank stuff into the program directly too, then just select the category for each transaction, and has pretty graphs for everything.

So if you could have a 'savings' category, and do your budgets for all the things you need like bills, food etc, then everything that is left over, just dump in savings. And then you can see how much that 'category' grows.

Not sure if that's what you want though, and there are plenty of similar programs for different prices. If you know your way around an excel formula, you could recreate the exact same thing in Excel in about an hour.

And if you use a pivot table, you will almost certainly go to hell.

[Illusions]

Alright, so...here's the embarrassing part. Since I don't regularly use Excel, I don't know any best practices, and approaching this with a designer's/artist's mind is probably not the best idea for data crunching, so...anybody have any suggestions, or more helpfully, pictures of how to set my data up and lay it out?

[Wraith]

There probably shouldn't be very many columns. Since this is for your own interest, just figure out what you want to track. I'd think that date/location/type/amount of each transaction is plenty of information for data entry, but if you want to track at a finer level, you'll need to add columns for that. I'd suggest using negative numbers for expenses - the results are prettier and you don't have to do any crazy sumifs down the line. Also be sure to mark the cells you do your data entry in as a table (ctrl+t), it'll make your life easier once you get to doing data analysis.

How newish are you to excel? Was my post before mostly gibberish?

[Illusions]

How newish are you to excel? Was my post before mostly gibberish?

In the last 8+ years I've only used it for invoices, in which I hand typed in each date, but knew to how to write formulas for automatic calculations. Excel hasn't really been essential to any job or task I've performed in the last 10+ years. So that sort of new...

For instance, one of the current problems I'm running into is for days that have multiple transactions. Say I bought lunch, fueled my car, and got paid all on 3/1. Right now, all those entries are separate. This doesn't seem like the best or most efficient way to handle it. Or more to the point, what I have right now is a table where each entry is its own row, with columns for the date, income, expenditure, and category. This seems less than ideal as well. The layout isn't aesthetically pleasing either, and I'm uncertain how much that matters to normal usefulness.

[Wraith]

For instance, one of the current problems I'm running into is for days that have multiple transactions. Say I bought lunch, fueled my car, and got paid all on 3/1. Right now, all those entries are separate. This doesn't seem like the best or most efficient way to handle it. Or more to the point, what I have right now is a table where each entry is its own row, with columns for the date, income, expenditure, and category. This seems less than ideal as well. The layout isn't aesthetically pleasing either, and I'm uncertain how much that matters to normal usefulness.

No, I think that's the right way to do it. The table might not wind up the prettiest thing, but that should be strictly data entry - when you want to view the data you should mostly be looking at something else. Having the entries separate supports better analysis. Use a PivotTable to roll up the data and analyze it. I would, however, combine the income & expenditure columns, using negative numbers to tell the outgoing from the incoming. This makes it easier to get math work done.

I also disagree with Dread's practice of using separate tabs for each year. The data should be kept in one place for easier manipulation and analysis.

When doing date entry, one shortcut is to use ctrl-: (control + colon). It'll automatically enter today's date for you.

[Illusions]

No, I think that's the right way to do it. The table might not wind up the prettiest thing, but that should be strictly data entry - when you want to view the data you should mostly be looking at something else. Having the entries separate supports better analysis. Use a PivotTable to roll up the data and analyze it. I would, however, combine the income & expenditure columns, using negative numbers to tell the outgoing from the incoming. This makes it easier to get math work done.

Should I put pivot tables and graphs at the top of the file, above the data, or in a separate one?

When doing date entry, one shortcut is to use ctrl-: (control + colon). It'll automatically enter today's date for you.

What about prior days (since this tip won't be helpful until I've reached the current day in data entry, or if my bank is slow in making data available online)?

Edit: Now that I think of it, why hasn't anyone made a 3D data table? I could easily combine all those seperate rows of dates into one area if the table was 3D.

[Wraith]

Should I put pivot tables and graphs at the top of the file, above the data, or in a separate one?

I'd do a separate sheet (tabs at the bottom) in the same workbook, especially if you're concerned about prettiness. Putting something like Pivots above (or to the left of) something else in the grid can always be a bit dangerous, because Pivots can change shape as you play with them - you might reach a point where the pivot can't expand because it'd overwrite existing data. Charts don't have this problem since they're not actually in the grid, but I still usually like to have my charts on their own sheets.

What about prior days (since this tip won't be helpful until I've reached the current day in data entry, or if my bank is slow in making data available online)?

3/4 = March 4, 2012 (as long as you're in US locale). It'll get the year right on it's own. Sorry, but that's all the shortcut I can give you.

You can right click->format cells to make sure it writes dates the way you like. Should only have to ever do this once if you're in a table.

edit: If you're always doing this from your bank's data, you can probably set this up so that all you have to do is copy + paste from the web.

[Illusions]

Is there any way to have it automatically sum one column of data, only if it has another adjacent column with a row that matches a certain set of criteria? For instance, take all of the amounts in Column B, and sum only those cells whose row in Column C says "Lunch"?

[Wraith]

Is there any way to have it automatically sum one column of data, only if it has another adjacent column with a row that matches a certain set of criteria? For instance, take all of the amounts in Column B, and sum only those cells whose row in Column C says "Lunch"?

=SUMIF([COLUMN C],"Lunch",[COLUMN B])

Alt: On the Pivot Table, add Column B to values, Column C to Page Filter. Click the page filter dropdown, select only "lunch".

Alt-Alt: On the Pivot, add Column B to values, Column C to rows. Look for row labeled "Lunch".

[Illusions]

=SUMIF([COLUMN C],"Lunch",[COLUMN B])

It works...actually as =SUMIF(Data!C:C,"Lunch",Data!B:B) , but it works! Thank you

I take it this would work the same way for dates, however I'm assuming I'd use a wildcard or something so that I could narrow it down to a month?

Alt: On the Pivot Table, add Column B to values, Column C to Page Filter. Click the page filter dropdown, select only "lunch".

Alt-Alt: On the Pivot, add Column B to values, Column C to rows. Look for row labeled "Lunch".

Still need to do far more research about pivot tables, but I'm on my way so far.

This is helping to me accomplish part of the goal of doing this though. Which is finding out on average, or overall, how much I spend on variable expenditures, like food and gas.

Edit: To put all of this more in perspective, some of my work pants and my work shoes had become a bit torn up (noticeably so) by the glass I work with, and I had put off buying new ones, simply out of my frugality, even though new ones would be useful, and I can more than afford them.

[Wraith]

It works...actually as =SUMIF(Data!C:C,"Lunch",Data!B:B) , but it works! Thank you

If you haven't already, create a table from your data. Just click a cell inside your data and hit ctrl+t. Right now, you're using a sheet reference in that sumif, which probably isn't exactly what you want, and might lead to the introduction of errors in the future. The reference should look like [Type] if you're in the table, or Table1[Type] if you're putting your formula outside of it.

I take it this would work the same way for dates, however I'm assuming I'd use a wildcard or something so that I could narrow it down to a month?

No wildcard support. You can do this with SUMIFs, but once you get to this point, you really should be looking into PivotTables.

Still need to do far more research about pivot tables, but I'm on my way so far.

I always find PivotTables are hard to explain well in a way that the listener really gets, but they're easy to demonstrate. Throw one on there (insert tab->PivotTable, farthest button on the left) and just start playing around. You can't do any damage playing with a PivotTable, and you can't mess up your data in any way, so just feel free to experiment until you see what everything does. It basically just rolls up your data and displays different slices and cross-sections of that data to you.

[Wraith]

Example table, with sumif in totals: