Budget reconciliation … UNIX style

Alright this is as geeky as it gets. Unexpected rain kept us from DNA Lounge tonight so I decided it’s a good time to look at some budget reconciliation after a prior discussion.

Bank of America online banking has a nice download feature that allows you to download a specific range of transactions in various formats. Fortunately one (Microsoft Excel format) is CSV. The description, amount and balance fields are ” delimited but not the date field. A quick one liner got me the results I wanted, most of the time:

grep -i "whole foods" march07.csv | sed -e "s/\"//g" | awk -F','  '{total+=$3;}END{print total}'

This worked great to get the total as along as there is no commas in the description field, which inevitably there were for some lines. Next stop Perl, some very, very rusty Perl at that:

#!/usr/bin/perl

$file = $ARGV[0];
$keyword = $ARGV[1];
print "searching $file for $keyword:\n";
open(FILE, $file);
@lines = ;
$linecount = 0;
$sum = 0;
foreach $line (@lines)
{
        $linecount++;
        $line =~ /(\d\d\/\d\d\/\d\d\d\d),\"(.+?)\",\"(.+?)\",\"(.+?)\"/;
        $date = $1;
        $description = $2;
        $amount = $3;
        $total = $4;

        if ($description =~ m/$keyword/i)
        {
                print "$date -> $description -> $amount\n";
                $sum += $amount;
        }
}

print "sum = $sum\n";

So a typical invocation to see what I’ve spent on food and groceries in a particular month:

./keyword.pl march07.csv “whole foods|chipotle|satsuma|jitlada|tofu|albertson|trader|safeway|pluto|asqew|longs”

(Almost not a day passes without visiting Whole Foods… got to have that organic food, not great on the budget:)

or gas:

./keyword.pl march07.csv “chevron|shell”

you get the picture…