You can imagine where it goes from here…

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…

Advertisements

March 25, 2007 - Posted by | Tech

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: