Tag: QFX

I recently wanted to download my bank transactions from E*Trade, but they do not support Excel format for the bank statements (only brokerage).  You can, however, download 3 months of Quicken QFX data at a time.  I downloaded several files spanning the year-to-date, but then needed a program to parse up the files and output a tab delimited text file.  So, for a try in formatting code, here’s what I got:

#!/usr/bin/perl -w
# Dan Minear
# 2011-09-01
#
# process Quicken QFX files and output tab delimited text file
#
# call 

use strict;
use FileHandle;
use Data::Dumper;

my $data = {};
my $capture = 0;		# don't capture
my %txntypes = ();

if (@ARGV < 1) {
	die "Syntax:  $0 \nExample:  $0 09*.QFX\n";
}

my $fo = FileHandle->new( "> out.txt");
if (! defined $fo) {
	die "Cannot write file";
}

while (my $fname = shift) {
	print $fname . "\n";
	my $fi = FileHandle->new("< $fname");
	if (defined $fi) {
		my $txn = {};
		while(<$fi>) {
			chomp;
			chop;
			# look for  
			if (/^/) {	#start of record
				$capture = 1;
				$txn = {};
				next;
			}
			if (/<\/STMTTRN>/) {	# end of record
				$capture = 0;
				# add to data hash
				if (defined $data->{$txn->{FITID}}) { 
					print "FITID $txn->{FITID} already defined\n";
				} else {
					$data->{$txn->{FITID}} = $txn;
					$txntypes{$txn->{TRNTYPE}} = 1;
				}
			}
			if ($capture) {
				/<(\w+)>(.+)$/;
				$txn->{$1} = $2;	
			}
		}
	} else {
		die "cannot open $fname for reading";
	}
	$fi->close;
}

print "there are " . keys(%$data) . " transactions in files\n";

#print $fo Dumper( $data );
print $fo "DATE\tTYPE\tCREDIT\tDEBIT\tNAME\tMEMO\n";
foreach my $i (keys(%$data)) {
	my $t = $data->{$i};	# ref to hash
	my $date = $t->{DTPOSTED};

	if ($t->{TRNAMT} < 0) {	# it's a debit
		print $fo substr($date,0,4) . "-" . substr($date,4,2) . "-" . substr($date,6,2) . "\t" .
			$t->{TRNTYPE} . "\t" .
			"\t" .
			$t->{TRNAMT} . "\t" .
			$t->{NAME} . "\t" . 
			$t->{MEMO} . "\n";
	} else {	# it's a credit
		print $fo substr($date,0,4) . "-" . substr($date,4,2) . "-" . substr($date,6,2) . "\t" .
			$t->{TRNTYPE} . "\t" .
			$t->{TRNAMT} . "\t" .
			"\t" .
			$t->{NAME} . "\t" . 
			$t->{MEMO} . "\n";
	}
}

$fo->close;

=head1 uncomment to print out transaction types
foreach (keys(%txntypes)) {
	print $_ . "\n";
}
=cut