4

This project will be made up of a bunch of "receipts"; that is the basic unit.

Each "receipt" will have a date and time. I'd like to be able to enter my receipts in any order (they are all in a big pile!) and then sort them by date and time.

Each "receipt" will comprise of a heading line, and then underneath that, indented lines for each item on that receipt. Each item will have a category (used to see, for example, how much I'm spending a month on categories such as "junk food", "essentials", "hygeine", etc).

So, for example, one receipt might look like this:

May 02 2014 / 1:23pm / Wal-Mart / 11.13$   
-------- 2.50$ / underwear / clothing   
-------- 2.50$ / doritos / junkfood   
-------- 5$ / milk / groceries   

My intention is to have one spreadsheet full of these receipts, and then (after sorting them by date), manually cut and paste them into separate spreadsheet files (one file per month), where at the end of each of those files I can sum up a report of how much I spent that month for each category.


One person said that I should be using a database instead of a spreadsheet; is this true? Can I still sum up the expenses for each category, for any given month, using a database?

My database knowledge is very basic. How should I structure the table if I go with a database? Should I have one table that holds the fields of Date, Time, StoreName, TotalCost, and also a field that itself is a table that holds various Items (each with fields of Price, Name, and Category)?

4
  • Are you looking for software or database design suggestion? Commented May 3, 2014 at 19:46
  • It sounds like this would be well suited to a Google Sheet or Excel. You probably don't need to go with a database for something like this, unless you're actually looking to roll your own. There are also finance dedicated apps, some which automate from your financial services, as well as scanning tools that can actually scan, itemize, and digitize physical receipts. However, it sounds like this problem is you actually want to build something, not find a tool that may do it already. Commented May 3, 2014 at 19:47
  • Jordan: yeah, i think i'm looking for a design suggestion, on whether or not a spreadsheet program or a database program is most appropriate (and if so, how i should go about tackling it in that program). specific suggestions on what software to use is not what i'm after. [i had thought that this Stack Exchange site is the closest that could help me get answers to this. i figured if there's a Stack Exchange site for, say, gardening, then probably these kinds of questions would be appropriate to ask in /some/ Stack Exchange site).
    – silph
    Commented May 4, 2014 at 0:25
  • If you're looking for something simple, create a Google Sheet in Google Drive and share it with the people who need access. It supports multiple people editing it simultaneously editing in the browser, so for something low-level like this it sounds perfect. Alternatively, you can look into financial software like Quickbooks if you're looking for a more professional solution. Lastly, you could use a very basic relational database, but that may require implementation of a program that accesses said database, or using Microsoft Access, which from the sound of it may be more complex than necessary Commented May 4, 2014 at 13:43

3 Answers 3

2

You can do this with a simple database (you need a Unique/Join key to match the LineItemDetailTable to the ReceiptHeaderTable) and you need to know how to do select statements to retrieve the data. You should also know how to create a drop down list box for standardized entries (storename, category).

You can do this with a series of spreadsheets - Tab1 being your Input Data containing columns of standardized Storenames and PurchaseCategoryTypes (clothing, junk food, etc). Tab2,3,4 could be VLookup or HLookup from InputTab based on which subset you're trying to view. Excel or OpenOffice would be fine for this. Cut&Paste is error-prone and time consuming. Let the computer do the work, you just tell it how once.

You can do this in Quicken (cheaper & less complicated than QuickBooks), possibly MSMoney but I'm not familiar with that package, breaking each receipt into its components in the "splits" field. This would also allow the individual category spending report.

2
  • @Nick Wilde - Links to what? OP asked for options and basic how to...
    – GDD
    Commented May 7, 2014 at 21:42
  • Like that - yeah I know not very important but still nice to have. Commented May 7, 2014 at 23:13
1

Basically, you have to decide:

  1. How much time am I willing to spend setting this up?
  2. How much time will it take to use, once it's up and running?

As far as reporting goes, as long as the data are there in SOME form then it'll be possible to pull reports off of it. Just how difficult that becomes is up to your database model and how much time you're willing to spend setting the whole thing up. The basic idea behind the relational model is to help prevent duplication of data and make everything easier to change/expand/administrate later on.

http://www.htmlgoodies.com/primers/database/article.php/3478051

Ignore the stuff they say about MySQL or anything else. You don't need any fancy database programs to make a relational database model work. You could do it in Excel if you wanted, or Google Apps. Google Forms has an easy GUI you can go through to make fields and automagically input them into a Google Docs spreadsheet, where you can graph the data to your heart's content, should you choose. Definitely an easy route that requires little to no programming, and you'll have a perfectly serviceable pile of data once you enter in your receipts.

One step up are programs that already do what you want to do. If you're going the free route, check out Skrooge (I use it, and it's awesome). It's a bit of a pain to set up if you're not running Linux, but it's just as powerful as MS Money or Quickbooks, etc. and it'll do everything you're asking for "out of the box", including reporting.

0

I do that every month.

My approach is:

  • Pay everything with your Credit Card or Debit Card
  • Modern banks they provide dashboards where you can see and track your spending and download them in Excel.

Not the answer you're looking for? Browse other questions tagged or ask your own question.