import question, other newbie things

[Gregory T. Sullivan]

For many years, I've been downloading .QIF files from my bank,
processing them with a simple PERL script to categorize them, and then
importing the QIF files into Quicken.  One of the many reasons I'm
excited about using gnucash is that I presumably can now write the
processing code in a decent language like Scheme.  For the time being,
though, I still use the PERL script, and I've appended parts of it.

Caveats: I'm no great PERL programmer, and the script does no error
checking, but it gets the job done.

-- 
Greg      gregs@ai.mit.edu (617)253-5807
Sullivan  http://www.ai.mit.edu/~gregs/
--

#!/usr/local/bin/perl5
# Filter qif files 
#
# Author: Greg Sullivan, May 1997 and onwards
#
# Usage: perl doqif.pl input-filename output-filename 
#
# qif flags:
#    D = date
#    N = number (check number, if applicable)
#    P = payee
#    T = amount
#    C = cleared (* or blank?)
#    L = category:subcategory
#    M = memo
#    ! appears to be a commennt
#    ^ (on a line by itself) separates records
#
open(LOGFILE, "> doqif.log");
($sec, $min, $hour, $mday, $mon, $year, $wday, $yday, $idst) = 
    gmtime(time());
print LOGFILE $mon+1, "/", $mday, "/", $year, ", ", $hour, ":",
    $min, ":", $sec, "GMT\n";

$infilename = shift(@ARGV);
open(INFILE, "< $infilename") || die "Could not open $infilename.\n";
print "Reading from $infilename\n";

$outfilename = shift(@ARGV);
open(OUTFILE, "> $outfilename") || die "Could not open $outfilename.\n";
print "Writing to $outfilename\n";

print OUTFILE "!Type:Bank\n";

open(DATEFILE, "> dates.txt") || die "Could not open dates.\n";
print DATEFILE "rec sep = [$/]\n";

($date, $num, $payee, $amount, $category, $memo, $cleared) = 
    ("", "",  "",  "",  "",  "", "");
@others = ();

while($line = <INFILE>) {
    $line =~ s/\r//;		# files from win32 can have excess \r's!

    if ($line =~ /^D(.*)/) {$date = $1;}
    elsif ($line =~ /^N(.*)/) {$num = $1;}
    elsif ($line =~ /^P(.*)/) {$payee = $1;}
    elsif ($line =~ /^T(.*)/) {$amount = $1;}
    elsif ($line =~ /^L(.*)/) {$category = $1;}
    elsif ($line =~ /^M(.*)/) {$memo = $1;}
    elsif ($line =~ /^C(.*)/) {$cleared = $1;}
    elsif (($line !~ /^\^/) &&
	   ($line !~ /^!Type/)) {
	push @others, $line;
    }

    next if ($line !~ /^\^/);	# loop until end of record (a "^" as whole line)

    print DATEFILE "[$date]\n";

    ## here we have the whole record, do the massaging.

    if (($date =~ /(.*)\/00$/) ||	# Y2K
	($date =~ /(.*)\/ 0$/) ||
	($date =~ /(.*)\'00$/) ||
	($date =~ /(.*)\' 0$/)) {
	$date = "$1/2000";
    }
    if (($date =~ /(.*)\/01$/) ||	# Y2K++
	($date =~ /(.*)\/ 1$/) ||
	($date =~ /(.*)\'01$/) ||
	($date =~ /(.*)\' 1$/)) {
	$date = "$1/2001";
    }

# Don't include regular checks -- they are entered by hand
##	next if ($payee =~ /^check/i);

# M=XP24POS .*    (a debit card purchase)
# => put rest of M in P 
    if ($memo =~ /^debit card purchase:\s*(.*)/i) { # debit card purchase
	$payee = $1;
	$num = "";
    }

# M=XPCHECK .*  (this is an M/C charge)
# => P= rest of M
    if ($memo =~ /^x-press check\s*(.*)/i) { # xpcheck (like M/C)
	$payee = $1;
	$num = "";
    }

    if ($payee =~ /^bill payment\s*(.*).*Reference (.*)$/i) {
	$payee = $1;
	$num = $2;
    }
    ## sometimes the reference num. doesn't make it
    if ($payee =~ /^bill payment\s*(.*)/i) {
	$payee = $1;
	$num = $2;
    }

    if ($payee =~ /purchase/i) {
	$num = "";
	$payee = $memo;
	print LOGFILE "unidentified purchase, date:", $date,"\n";
    }

# NOW THAT PAYEE HAS BEEN FILLED IN FROM MEMO FIELD,
# MATCH AGAINST COMMON PAYEES

    if (($payee =~ /^xp24 withdrawal/i) ||
	($payee =~ /^atm withdrawal/i)) {
	$payee = "ATM Withdrawal";
	$category = "Cash";
	$num = "";
    }

# MIT payroll
    if (($payee =~ /MIT/i)
	&& ($memo =~ /payroll/i)) {
	$payee = "MIT Auto Deposit";
	$category ="Wages & Salary:MIT";
    }

# Groceries
    if (($payee =~ /star market/i) ||
	($payee =~ /bread.*circu/i) ||
	($payee =~ /hannaford/i) ||
	($payee =~ /homeruns/i) ||
	($payee =~ /nature.*heart/i) ||
	($payee =~ /stop.*shop/i)) {
	$category = "Food:Groceries";
    }
# Gas
    if (($payee =~ /texaco/i) ||
	($payee =~ /exxon/i) ||
	($payee =~ /shell/i) ||
	($payee =~ /mobil/i) ||
	($payee =~ /gulf/i)) {
	$category = "Automobile:Gasoline";
    }
# YMCA
    if ($payee =~ /west subur/i) {
	$payee = "West Suburban YMCA";
	$category = "Leisure:Swimming";
    }
# Boston Globe
    if ($payee =~ /bostonglob/i) {
	$category = "Household:Newspaper";
    }

    print OUTFILE "D",$date,"\n";
    print OUTFILE "N",$num,"\n" if $num ne "";
    print OUTFILE "P",$payee,"\n";
    print OUTFILE "T",$amount,"\n";
## do not flag the transaction as cleared:
#####	    print OUTFILE "C \n";	#  not cleared
    print OUTFILE "L",$category,"\n";
    print OUTFILE "M",$memo,"\n";
    print OUTFILE "C",$cleared,"\n";
    foreach $o (@others) {
	print OUTFILE $o;
    }
    print OUTFILE "^\n";
##	}
    ($date, $num, $payee, $amount, $category, $memo) = 
	("", "",  "",  "",  "",  "");
    @others = ();
}

close INFILE;
close OUTFILE;
close LOGFILE;
print "Done.\n";