Unable to import transactions from American Express Savings account
This is the error I get when I try to import the QFX file downloaded from American Express Savings file. Why? Could you please provide more details what's wrong? Is it that hard to show more useful information?
I'm not going to provide Quicken with my username and password for FI, so don't tell me to use that update method.
This is not the case where Quicken blacklists FID, as changing FID to 3000 (Wells Fargo) doesn't allow to proceed with download.
Comments
-
@sashk - I also tried to download the file for my Amex Saving Acct and it failed (for Q Win). I took a look at the file, and it looks like it is in the wrong format and has formatting errors throughout the file and doesn't look like it would be just a simple fix. My only suggestion would be (the same as the error message you got) to please contact American Express.
0 -
Ah, ok, here is what happens. Malformed file.
2023-08-05 22:27:45.788 Quicken[53085:1151280] Failure parsing ofxXMLmapping file: /Users/your/username/sucks/transactions.QFX! Error: { kCFXMLTreeErrorDescription = "Encountered malformed start tag"; kCFXMLTreeErrorLineNumber = 1; kCFXMLTreeErrorLocation = 200; kCFXMLTreeErrorStatusCode = 9; } 2023-08-05 22:27:45.794 Quicken[53085:1151280] reportException:extraData:, exception = Encountered malformed start tag, extraData = (null) 2023-08-05 22:27:45.794 Quicken[53085:1151280] reportException:extraData:, exception = Encountered malformed start tag, extraData = (null)
Care to elaborate?
0 -
Idk, the file looks good to me, and I tried to parse it with multiple xml parsers: xmllint, python code I wrote to parse xml and swift code to parse xml. Nothing extraordinary was found. So I guess the bug is with Quicken, not American Express.
Specifically, knowing how Quicken blocks banks from adding data to quicken via ofx (example is Barclay's bank, where switching FID to known working bank suddenly makes file marked as malformed to work properly), suggests that Quicken is very bad-bad-bad and need to act up.
0 -
Found older topic with [Removed - Language] answer from quicken. [Removed - Disruptive], they sent it to American Express suggesting to ask for supervisor. LOL.
0 -
Here is code, if you're wondering:
Save as ofx.swift, and compile it swiftc ofx.swift -o ofx (you need Command-line tools installed for Xcode). then run ./ofx and it will parse your transactions.ofx file.
So, Quicken Support, what's your move now?
1 -
@sashk if you compare the Amex QFX file to another "good" one from another FI, you'll see what I mean. The Amex file is a mish mash without any order or structure. OFX files usually follow a structed template.
I haven't contacted Amex yet. I think most users who have the Amex High Yield Savings Account use EWC, so they probably haven't even tried importing a QFX file. And, I think most people wouldn't be able to find the file on Amex website. They have hidden it well.
-1 -
@sashk - here is what the file looks like when I open it. It could be just a difference between Mac and Windows but nevertheless, all that I see points to an issue with their file.
<?xml version="1.0" encoding="UTF-8" standalone="no"?><OFX CHARSET="1252" COMPRESSION="NONE" DATA="OFXSGML" ENCODING="USASCII" NEWFILEUID="NONE" OFXHEADER="100" OLDFILEUID="NONE" SECURITY="NONE" VERSION="102"><SIGNONMSGSRSV1><SONRS><STATUS><CODE>0</CODE><SEVERITY>INFO</SEVERITY></STATUS><DTSERVER>20230806132515</DTSERVER><LANGUAGE>ENG</LANGUAGE><FI><ORG>American Express National Bank</ORG><FID>18858</FID></FI><INTU.BID>18858</INTU.BID></SONRS></SIGNONMSGSRSV1><BANKMSGSRSV1><STMTTRNRS><TRNUID>0</TRNUID><STATUS><CODE>0</CODE><SEVERITY>INFO</SEVERITY></STATUS><STMTRS><CURDEF>USD</CURDEF><BANKACCTFROM><BANKID>124085066</BANKID><ACCTID>0000000000</ACCTID><ACCTTYPE>SAVINGS</ACCTTYPE></BANKACCTFROM><BANKTRANLIST><DTSTART>20230707000000</DTSTART><DTEND>20230808235959</DTEND><STMTTRN><TRNTYPE>CREDIT</TRNTYPE><DTPOSTED>20230724000648</DTPOSTED><TRNAMT>000.00</TRNAMT><FITID>867</FITID><NAME>Interest Payment</NAME></STMTTRN><STMTTRN><TRNTYPE>DEBIT</TRNTYPE><DTPOSTED>20230720085001</DTPOSTED><TRNAMT>00000.0</TRNAMT><FITID>862</FITID><NAME>xxxxxxxxxxxxxxxxx</NAME></STMTTRN></BANKTRANLIST><LEDGERBAL><DTASOF>20230808235959</DTASOF><BALAMT>000000.00</BALAMT></LEDGERBAL></STMTRS></STMTTRNRS></BANKMSGSRSV1></OFX>(I removed the account number and amounts and replace them with zeros.)
0 -
@QwinUser The only difference between Amex and other is that other is multiline (i.e there is new line character after closing tag), while amex is single line. that doesn't say that file is not valid xml. Mine looks exactly same, and yet it is still parsed properly my xml parser. And the proof that it is parsed properly by Quicken is showed up in the ~/Library/Application Support/Quicken/Logs/OFXLog.txt, where it prints it out in pretty print.
0 -
I think most users who have the Amex High Yield Savings Account use EWC
European Wax Center? wdym?:)
0 -
It's not Amex problem, it's Quicken problem. They need to figure out why they can't parse it, don't point fingers at someone else. All data is there.
0 -
Ok, I found a fix.
Open the QFX file in text editor and modify first snippet of the text.
<?xml version="1.0" encoding="UTF-8" standalone="no"?><OFX CHARSET="1252" COMPRESSION="NONE" DATA="OFXSGML" ENCODING="USASCII" NEWFILEUID="NONE" OFXHEADER="100" OLDFILEUID="NONE" SECURITY="NONE" VERSION="102">
With
<?xml version="1.0" standalone="no"?><?OFX CHARSET="1252" COMPRESSION="NONE" DATA="OFXSGML" ENCODING="USASCII" NEWFILEUID="NONE" OFXHEADER="100" OLDFILEUID="NONE" SECURITY="NONE" VERSION="102"?><OFX>Then try to import the file. Works :)
1 -
@sashk - it worked, and the transactions loaded just fine but the outstanding balance on the file is wrong <BALAMT>. It is way off.
The other thing is the last download date and time is not being updated in the register.
Other than that, this is great progress, at least the file uploads without an error message.
0 -
I updated the <BALAMT> in the file and reloaded it and it corrected the online balance amount in my register.
0 -
The last upload date and time are working fine. I had forgotten to clear out the old online connection information when I downloaded the first time.
So the only other issue besides the header, is the <BALAMT>.
0 -
You probably either copied too much, or cut too much. All I had to touch was the to the end of the open <OFX> tag. Total balance is part of the statement, which is further down the line.
0 -
The balance was just wrong. It had a number that was no where near what it should be.
0 -
Hmm. mine looks good — they use Available Balance amount, not Current Balance. Which is expected.
0 -
For those wondering, why my fix worked, explanation on stack overflow what <?..?> is. Basically, I'm marking their provided OFX opening tag as a, basically "comment", and creating new empty opening <OFX> tag. Didn't want to get into details of the OFX format, but it's something that perhaps Quicken doesn't support properly, yet.
0 -
My balance was really wrong. I looked at the balance that they used and it was the beginning balance for the time period I chose. I ran many files and each time the same result. It was the beginning balance for the time period. What should be used is ending balance. Not available balance because that would include pending items, for a savings account doesn't make sense.
In my opinion, the formatting of the file does not follow the same formatting and syntax of the rest of the QFX files I've looked at. And I have looked at many for different FIs. The QFX header follows a standard format and syntax for all the other files I looked at.
So bottom line, Amex needs to fix the header, and used the end date balance, instead of begin date.
0 -
Disagree. In this case, I strongly believe that Quicken needs to fix their parser. There is nothing wrong with the xml file, the problem lies in failure to properly parse it.
0 -
The header is wrong. That is why when you replace the header with a correct one, it works. There is nothing Quicken can do to fix that. It is up to Amex to correct the header in their file. And also, Amex needs to fix the current online balance amount on the file.
0 -
@sashk - Here is a typical header on a QFX file -
OFXHEADER:100
DATA:OFXSGML
VERSION:102
SECURITY:NONE
ENCODING:USASCII
CHARSET:1252
COMPRESSION:NONE
OLDFILEUID:NONE
NEWFILEUID:NONEHere is what the Amex Savings QFX file header looks like -
(I parsed the header for easier reading)
<?xml
version="1.0"
encoding="UTF-8"
standalone="no"?><OFXCHARSET="1252"
COMPRESSION="NONE"
DATA="OFXSGML"
ENCODING="USASCII"
NEWFILEUID="NONE"
OFXHEADER="100"
OLDFILEUID="NONE"
SECURITY="NONE"
VERSION="102">As you can see there are several formatting and syntax errors that Amex needs to fix. Quicken cannot parse or accommodate a file that is defective. Also the <OFX> you added to the end of the header to get it to work is actually part of the body of the program and not part of the header technically. If you look closely at the Amex header the <OFX> looks like it was mistakenly melded into the header (4th and 13th lines).
0 -
Here are the specs: , and they are xml based, so I don't think Amex doing something wrong here. And this is OFX format starting with version 2, version 1 was SGML based, one which you show above has mention of OFXSGML, AND VERSION 1.02, So perhaps these are two different versions.
0