Montag, 16. August 2010

using a file system for my bank account

the goal - organize your shared payments

I'm sharing a flat with my girl friend. So we regularly shop things, that we are equally paying. Sometimes it's a little hard to track the many shared payments. Especially if you use cash, your giro account and visa.

the idea - use a directory

To track all your payments you need to organize all payments in one place. Normally this can't be your giro account as your bank doesen't care about your cash payments. So my approach is to gather my payment data on my computer. Fortunately my bank supports a CVS export of my giro and visa transactions. So I can easily export the account data in a computer processable format.

the database is a directory

As database for my payments I use a plain old directory on my file system. The directory contains a subdirectory for every transaction I made. No matter whether it was on my bank accounts or cash. For the transaction directory names I use the simple syntax: <date> - <description>

Examples for the transaction directory names would be:

  • 2010-08-12 - ice cream in park
  • 2010-08-14 - VISA PAYMENT at

Each of the transaction directories is tagged with the transaction details. The taggings are applied in a text file within the transaction directory. The tagging file 2010-08-14 - GIRO PAYMENT at MY GROCERIES SHOP/.tag i.e. has the following content:

date: 2010-08-14
account: visa
amount EUR: 120.00
description: old table

The tag files for the giro and visa transactions are created by a python script I wrote. The python script creates a transaction directory for every row in my bank's CSV exports. The columns from the CSV files are applied as taggings in the tag file. The python script does a little more magic, like merging already existing entries. But that's another story.
The transaction directories for cash payments are created manually by me. But the taggings contain basically the same data. The only difference is that the account field is tagged with cash.

I introduce a new tagging to separate my payments from the payments I share with my girl friend. My tagging for shared payments is:

share: true

export filtered CSV with tagfs

Now that I've collected my transactions and added meta data like the 'share' tagging, I need to filter the transactions. I use tagfs to filter the various transactions, contexts (like 'data', 'account', ...) and taggings. tagfs is mounted as a virtual file system beside the transactions directory. Mounting the above example with tagfs will show me a directory like this:

The tagfs root directory contains various subdirectories. The subdirectories represent filters for the tagfs items aka. my transaction directories. Filtering transactions takes place by entering directories. Enter the share/true/ directory to see all transactions with a share flag. share/true/account/giro will show you all shared transactions which occurred via your giro account.

Now I create a CSV export which contains all shared transactions. To do so I open the CSV file share/true/.export/export.csv. The CSV file contains all matching transactions as rows. The columns represent the different taggings:

name date account amount EUR description share
2010-08-12 - ice cream in park 2010-08-12 cash 1.00   true
2010-08-14 - GIRO PAYMENT at MY GROCERIES SHOP 2010-08-14 giro 32.53 just food true

I open this CSV table in and calculate the sum in the 'amount EUR' column. That's real magic... isn't it?!?

adding multi dimensional spice

OK... I truly admit... the magic hasen't happend yet. What I just did was just some filter with a sum calculation. I could have used a simple excel sheet for that. Excel is a fine tool as long as you use structured data. Our transactions are structured data. Every transactions consists of a limited amount of fields with well defined values. To leave this limited view of the world you have to think of subjects instead of transactions in your bank account. A subject can be anything! A transactions can be a subject as good as a directory with my holiday pictures can be a subject (I borrowed this very abstract view of subjects from the resource description framework and the tripplestore concept). Now I can tag my transactions and my holiday pictures with holiday: india 2009. This allows me various filters:

  • holiday/india 2009 shows everything related to my india vacation in 2009. No matter whether it's a transaction on my visa account or my holiday pictures.
  • holiday/india 2009/account/visa/.export/export.csv lets me calculate all my visa expenses during the holiday.

(my) conclusion

As I think, an excel sheet or a relational database system gives you one view to your data. Viewing your data in a table like structure is good for analyzing items. These items need to be comparable in a specific way. But storing data is different to viewing data. When storing data you need the flexibility to adjust your storage to new kind of entries in your database. Relational database systems do this via tables. Storing data in tables will higher the risk for many conversions and complex table joins.