[GNC-dev] Hello and ofx version 2

[Steve Burns]

Hello everyone, my name is Steve.  This is my first post here.
I have been using GnuCash for many years.  It reminds me of Quicken from
the 90's, only it's much better.

I have recently come across an issue with importing QFX / OFX files in the
U.S.
Importing "version 1" style .qfx (like chase credit cards or my credit
union) work fine.  The qfx file starts off with:
OFXHEADER:100
DATA:OFXSGML
VERSION:102
...

As of 2020, American Express now creates qfx files with a new format.
They allowed the old format output for a few months, but now it is
completely gone.
The new format starts off with:
<?xml version="1.0" standalone="no"?><?OFX OFXHEADER="200" VERSION="202" ...

GnuCash refuses to extract any data from this file.
I created a 4-line bash script which converts version 2 to a simulated
version 1.
Here it is:

cat activity.qfx | perl -pe 's/</\n</g' > noimport1.txt
egrep -v
"</CODE>|</SEVERITY>|</MESSAGE>|</DTSERVER>|</LANGUAGE>|</ORG>|</FID>|</INTU.BID>|</TRNUID>|</CURDEF>|</ACCTID>|</DTSTART>|</DTEND>|</TRNTYPE>|</DTPOSTED>|</TRNAMT>|</FITID>|</REFNUM>|</NAME>|</MEMO>|</BALAMT>|</DTASOF>"
noimport1.txt > noimport2.txt
printf
"OFXHEADER:100\nDATA:OFXSGML\nVERSION:102\nSECURITY:NONE\nENCODING:USASCII\nCHARSET:1252\nCOMPRESSION:NONE\nOLDFILEUID:NONE\nNEWFILEUID:NONE\n<OFX>\n"
> import.qfx
tail -n+5 noimport2.txt >> import.qfx

AmEx annoyingly outputs all qfx files as activity.qfx, but this is used to
our advantage in the script.
Line one adds carriage returns at every tag.  I don't know if this
is required, but seems reasonable.  It made the rest of the script easier.
Line two removes ending tags for the ones not present in a sample version 1
file.  Some of the ending tags are present in version 1 and so are left
in.  I just found all the tags that were NOT in version 1 and removed
them.  I don't know if this is required.
Line three inserts a version 1 start-of-file header taken from my sample
file to the output file "import.qfx".  It probably has more lines than
is required.  I know that the <OFX> tag is required, because I tried to
import a file without <OFX> and it failed.
Line four removes the version 2 first 4 lines (up to and including <OFX
xmlns:ns2="http://ofx.net/types/2003/04"> tag) and appends the meat of the
file to the output file.

My goal was to get something to work quickly, not to experiment and find
the minimal changes that allowed an import.
This was good enough to get it to import into GnuCash.
Notice that I didn't change any of the data, and not much of the data
format.

I haven't looked through the GnuCash source, but my guess is that we need
to:
1) Ignore carriage returns, or make them optional.  Use spaces and tags to
delimit.  If the parsing code is line-based, this may be some work.
2) Be able to handle version 2 headers.  They contain no useful number
data, so this may be straightforward.
3) Ignore more ending tags, if necessary.  Maybe nothing to do here.

I think this is a necessary or high-need improvement to GnuCash.  I import
QFX files almost every time I use GnuCash.
This is tested using GnuCash 3.8 on a Mac.  Version 3.10 release notes did
not mention QFX imports, so I assume that the issue is still present.
Let me know if you want me to help do anything.

Steve Burns