import question, other newbie things
Gregory T. Sullivan
gregs@ai.mit.edu
28 Mar 2001 20:37:07 -0500
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";