{"id":162,"date":"2011-09-01T22:39:36","date_gmt":"2011-09-02T06:39:36","guid":{"rendered":"http:\/\/dan.minear.name\/wordpress\/?p=162"},"modified":"2011-09-01T22:39:36","modified_gmt":"2011-09-02T06:39:36","slug":"processing-quicken-qfx-files","status":"publish","type":"post","link":"http:\/\/dan.minear.name\/wordpress\/?p=162","title":{"rendered":"Processing Quicken QFX Files"},"content":{"rendered":"<p>I recently wanted to download my bank transactions from E*Trade, but they do not support Excel format for the bank statements (only brokerage).\u00a0 You <em>can<\/em>, however, download 3 months of Quicken QFX data at a time.\u00a0 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.\u00a0 So, for a try in formatting code, here&#8217;s what I got:<\/p>\n<pre>\r\n#!\/usr\/bin\/perl -w\r\n# Dan Minear\r\n# 2011-09-01\r\n#\r\n# process Quicken QFX files and output tab delimited text file\r\n#\r\n# call \r\n\r\nuse strict;\r\nuse FileHandle;\r\nuse Data::Dumper;\r\n\r\nmy $data = {};\r\nmy $capture = 0;\t\t# don't capture\r\nmy %txntypes = ();\r\n\r\nif (@ARGV < 1) {\r\n\tdie \"Syntax:  $0 <filespec>\\nExample:  $0 09*.QFX\\n\";\r\n}\r\n\r\nmy $fo = FileHandle->new( \"> out.txt\");\r\nif (! defined $fo) {\r\n\tdie \"Cannot write file\";\r\n}\r\n\r\nwhile (my $fname = shift) {\r\n\tprint $fname . \"\\n\";\r\n\tmy $fi = FileHandle->new(\"< $fname\");\r\n\tif (defined $fi) {\r\n\t\tmy $txn = {};\r\n\t\twhile(<$fi>) {\r\n\t\t\tchomp;\r\n\t\t\tchop;\r\n\t\t\t# look for <STMTTRN> \r\n\t\t\tif (\/^<STMTTRN>\/) {\t#start of record\r\n\t\t\t\t$capture = 1;\r\n\t\t\t\t$txn = {};\r\n\t\t\t\tnext;\r\n\t\t\t}\r\n\t\t\tif (\/<\\\/STMTTRN>\/) {\t# end of record\r\n\t\t\t\t$capture = 0;\r\n\t\t\t\t# add to data hash\r\n\t\t\t\tif (defined $data->{$txn->{FITID}}) { \r\n\t\t\t\t\tprint \"FITID $txn->{FITID} already defined\\n\";\r\n\t\t\t\t} else {\r\n\t\t\t\t\t$data->{$txn->{FITID}} = $txn;\r\n\t\t\t\t\t$txntypes{$txn->{TRNTYPE}} = 1;\r\n\t\t\t\t}\r\n\t\t\t}\r\n\t\t\tif ($capture) {\r\n\t\t\t\t\/<(\\w+)>(.+)$\/;\r\n\t\t\t\t$txn->{$1} = $2;\t\r\n\t\t\t}\r\n\t\t}\r\n\t} else {\r\n\t\tdie \"cannot open $fname for reading\";\r\n\t}\r\n\t$fi->close;\r\n}\r\n\r\nprint \"there are \" . keys(%$data) . \" transactions in files\\n\";\r\n\r\n#print $fo Dumper( $data );\r\nprint $fo \"DATE\\tTYPE\\tCREDIT\\tDEBIT\\tNAME\\tMEMO\\n\";\r\nforeach my $i (keys(%$data)) {\r\n\tmy $t = $data->{$i};\t# ref to hash\r\n\tmy $date = $t->{DTPOSTED};\r\n\r\n\tif ($t->{TRNAMT} < 0) {\t# it's a debit\r\n\t\tprint $fo substr($date,0,4) . \"-\" . substr($date,4,2) . \"-\" . substr($date,6,2) . \"\\t\" .\r\n\t\t\t$t->{TRNTYPE} . \"\\t\" .\r\n\t\t\t\"\\t\" .\r\n\t\t\t$t->{TRNAMT} . \"\\t\" .\r\n\t\t\t$t->{NAME} . \"\\t\" . \r\n\t\t\t$t->{MEMO} . \"\\n\";\r\n\t} else {\t# it's a credit\r\n\t\tprint $fo substr($date,0,4) . \"-\" . substr($date,4,2) . \"-\" . substr($date,6,2) . \"\\t\" .\r\n\t\t\t$t->{TRNTYPE} . \"\\t\" .\r\n\t\t\t$t->{TRNAMT} . \"\\t\" .\r\n\t\t\t\"\\t\" .\r\n\t\t\t$t->{NAME} . \"\\t\" . \r\n\t\t\t$t->{MEMO} . \"\\n\";\r\n\t}\r\n}\r\n\r\n$fo->close;\r\n\r\n=head1 uncomment to print out transaction types\r\nforeach (keys(%txntypes)) {\r\n\tprint $_ . \"\\n\";\r\n}\r\n=cut\r\n\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>I recently wanted to download my bank transactions from E*Trade, but they do not support Excel format for the bank statements (only brokerage).\u00a0 You can, however, download 3 months of Quicken QFX data at a time.\u00a0 I downloaded several files spanning the year-to-date, but then needed a program to parse up the files and output &hellip; <\/p>\n<p class=\"link-more\"><a href=\"http:\/\/dan.minear.name\/wordpress\/?p=162\" class=\"more-link\">Continue reading<span class=\"screen-reader-text\"> &#8220;Processing Quicken QFX Files&#8221;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[13,3],"tags":[41,40,39],"_links":{"self":[{"href":"http:\/\/dan.minear.name\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/162"}],"collection":[{"href":"http:\/\/dan.minear.name\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/dan.minear.name\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/dan.minear.name\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/dan.minear.name\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=162"}],"version-history":[{"count":8,"href":"http:\/\/dan.minear.name\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/162\/revisions"}],"predecessor-version":[{"id":181,"href":"http:\/\/dan.minear.name\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/162\/revisions\/181"}],"wp:attachment":[{"href":"http:\/\/dan.minear.name\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=162"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/dan.minear.name\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=162"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/dan.minear.name\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=162"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}