statementToCategories.pl

Well, this is my first financial software, such as it is. I've been using and refining this over the last few months. It *does* work with my credit union, it may or may not work with your institutions downloadable bank statements.

It has only been tested on my own computer, which is a Ubuntu Linux computer. It requires Perl and the File::Basename and Date::Calc modules. It may or may not work on Windows.

#!/usr/bin/perl

#====================================================
#
# statementToCategories.pl
#
# This script will take in a csv formatted bank statement and output two
# csv files. The first file is a list of categories and how much was spent
# in each category. The other is the original csv, with a list of which
# cateogry each line item was categorized as.
#
# If a particular line hasn't been seen before, the script will prompt
# the user to select a category for the line. It saves the categories to
# ~/.finance_categories
#
# The format for the bank statement csv file that the script reads is:
# "Date","Check No.","Description","Debit","Credit"
#
# License and Copyright
# Copyright 2008, Richard Better <richerandbetter@gmail.com>
# This program is Free software. Use it at your own risk. I take
# no responsibility for the results of the use of this software. 
#
# Licensed under the GNU GPL Version 3
# View the full license here: http://www.gnu.org/licenses/gpl.html
#
# Enjoy!

#====================================================
# Run more safely. Import needed modules
use strict;
use warnings;
use File::Basename;
use Date::Calc qw(Delta_Days);


#====================================================
# Make sure we were passed at least one file
if(scalar @ARGV < 1)
{
   print "\nUSAGE: statementToCategories.pl financialData.csv [financialData.csv ... financialData.csv]\n\n";   
   exit(-1);
}


#====================================================
# Categories we'll be sorting into
# To customize categories, edit this array.
my @cats = (
   'car-gas',
   'car-insurance',
   'car-payments',
   'car-maintenance',
   'cash',
   'clothing',
   'electronics',
   'food-dining',
   'food-groceries',
   'gifts',
   'home-maintenance',
   'home-rent',
   'medical',
   'megamart',
   'recreation',
   'school',
   'util-electric',
   'util-gas',
   'misc',
);

#====================================================
# Setup variables

# Global variables
# Set initial date range
my @mindate = (2050,12,31);
my @maxdate = (1901,1,1);

my %predef = (); # Already defined categories
my %values = (); # A hash of categories and amount spent
my @longdesc = (); # An array which stores the original data, plus it's categorization, so that you can figure out why categories have the values they do
my $predefvalues;
my $outdir;
my $homepath = $ENV{'HOME'};
chomp($homepath);

#====================================================
# Run the script

createOutdir();
prepCategories();
parseFiles();
saveDefs();
writeResults();

#====================================================
# Just functions below

sub createOutdir
{
   # Create the output directory if it doesn't exist
   $outdir = $homepath . '/Finance/';
   mkdir($outdir) unless (-e $outdir);
}

sub prepCategories
{
   # Prepopulate category totals
   foreach(@cats){
   $values{$_} = 0;
   }

# Find existing categories
   $predefvalues = $homepath . "/.finance_categories";

   if (-e "$predefvalues")
   {
 open(FH,"<$predefvalues");
 while(<FH>){
     my @def = split(',',$_);
     chomp($def[1]);
     $predef{$def[0]} = $def[1];
 }
 close(FH);
   }
}

sub parseFiles
{
   # The main loop. It takes one or more files and reads them line by line, processing each and adding their contents to the totals
   foreach(@ARGV){
 my $file = $_;
 print "Processing $file\n";

 die("File $file does not exist\n") unless (-e $file);

 open(COSTS,"<$file");
 while(<COSTS>){

     my $preline = $_;
     $preline =~ s/\"//g;

     my @line = split(',',$preline);
     next if $line[0] =~ m/Date/; # Description line
     checkDates($line[0]); # Find our date range

     next if $line[3] !~ m/-/; # Payments / credits
     next if $line[2] =~ m/Transfer/; # inter-account transfer
     next if $line[2] =~ m/Savings\ Withdrawal/;


     my $amount = $line[3];
     $amount =~ s/-//g;

     my $origPurchase = $line[2];
     my $purchase = cleanupPurchase($line[2]);

     # Flip the value to be positive

     # Autocategorize
     if($predef{$purchase}){
  $values{$predef{$purchase}} += $amount;
  push(@longdesc,"\"" . $predef{$purchase} . "\"," . $preline);
     }
     else
     {
  system 'clear';
  print "Please enter a category number for the following item, then press ENTER:\n\n";

  for(my $i = 0;$i<@cats;$i++){
      print "($i) " . $cats[$i] . "\n";
  }

  print "\n$amount at '$origPurchase', short name is '$purchase' \n";
  print "-----------------------\n";

  # Read in the new category
  my $newcat = <STDIN>;
  $newcat =~ s/^.*[^\d](\d+)[^\d].*$/$1/;
  if($newcat eq '')
  {
      $values{'misc'} += $amount;
      push(@longdesc,"\"misc\"," . $preline);
  }
  else
  {
      $predef{$purchase} = $cats[$newcat];  
      $values{$predef{$purchase}} += $amount;
      push(@longdesc,"\"" . $predef{$purchase} . "\"," . $preline);
  }
     }
 }
 close(COSTS);
   }
}

sub saveDefs
{
   # Write the definitions to a file, so we don't have to re-categorize everything each time
   open(CATS,">$predefvalues");
   foreach(sort keys %predef){
 print CATS $_ . "," . $predef{$_} . "\n";
   }
   close(CATS);
}

sub writeResults
{
   # Write the two results files
   system 'clear';
   print "What do you want the file named?\n";
   my $outfile = <STDIN>;
   chomp($outfile);
   $outfile =~ s/\s+/_/g;

   my $outfiledesc = $outdir . '/' . $outfile . "_desc.csv";
   $outfile = $outdir . '/' . $outfile . ".csv";

   open(OUTPUT,">$outfile");

   my $months = getMonths();

   print OUTPUT "\"Category\"\t\"Total Spent from " . join('/',@mindate) . " to " . join('/',@maxdate) . "\"\t\"Average per 30 day month ($months months total)\"\n"; 

   foreach(sort(keys %values)){
 print OUTPUT "\"$_\"\t\"" . $values{$_} . "\"\t\"". ($values{$_} / $months) ."\"\n";
   }
   close(OUTPUT);

   open(DESC,">$outfiledesc");
   foreach(sort(@longdesc)){
 print DESC $_;
   }
   close(DESC);

   print "Output is a tab separated file at $outfile\n";
}

sub getMonths
{
   # Determine how much of a month is being considered here
   my $days = Delta_Days(@mindate,@maxdate);
   return ($days/30 + 1);
}

sub cleanupPurchase
{
   # Strip un-needed info from the purchase description. This will let more similar names end up the same
   my $purchase = shift;

   $purchase =~ s/\s*Cr\ Crd\ Purchase\  \@\ //g;
   $purchase =~ s/\s*\ \ Trace\ #[0-9]*//g;
   $purchase =~ s/\s*[^\d\s]+\ \S\SUS//g;
   $purchase =~ s/\t/\ /g;
   $purchase =~ s/"/\ /g;
   $purchase =~ s/\s*\ \S\SUS//g;
   $purchase =~ s/\d*//g;
   $purchase =~ s/#//g;
   $purchase =~ s/[\s\@*-]+$//g;
   chomp($purchase);
   return $purchase;
}


sub checkDates
{
   # Check if the current date is a new start or end date, and set the approriate variable if needed
   my $strcurdate = shift;
   my @curdate = split('/',$strcurdate);
   @curdate = ($curdate[2],$curdate[0],$curdate[1]);
   if(Delta_Days(@mindate,@curdate) < 0){
 @mindate = @curdate;
   }
   if(Delta_Days(@maxdate,@curdate) > 0){
 @maxdate = @curdate;
   }
}