Unable to import transactions from American Express Savings account

Options
sashk
sashk Member ✭✭

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.

«1

Comments

  • QWinUser
    QWinUser Member ✭✭✭✭
    Options

    @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.

  • sashk
    sashk Member ✭✭
    Options

    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? 

  • sashk
    sashk Member ✭✭
    Options

    @QWinUser did you try to contact them? Agents are clueless, and only suggestion is to use Chrome. So they are not helpful, and fail to properly handle complaint.

    Anyway, looks like I'm going to switch off from Amex Savings because of this.

  • sashk
    sashk Member ✭✭
    Options

    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.

  • sashk
    sashk Member ✭✭
    edited August 2023
    Options

    Found older topic with [Removed - Language] answer from quicken. [Removed - Disruptive], they sent it to American Express suggesting to ask for supervisor. LOL.

  • sashk
    sashk Member ✭✭
    edited August 2023
    Options

    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?

  • QWinUser
    QWinUser Member ✭✭✭✭
    Options

    @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.

  • QWinUser
    QWinUser Member ✭✭✭✭
    edited August 2023
    Options

    @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.)

  • sashk
    sashk Member ✭✭
    Options

    @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.

  • sashk
    sashk Member ✭✭
    Options

     I think most users who have the Amex High Yield Savings Account use EWC

    European Wax Center? wdym?:)

  • QWinUser
    QWinUser Member ✭✭✭✭
    Options

    @sashk sorry for the Qwin acronym. It stands for "Express Web Connect". I think for Mac users it's "Quicken Connect."

  • QWinUser
    QWinUser Member ✭✭✭✭
    Options

    @sashk I think we can analyze it as much as we want, but bottom line, I think you would need to contact Amex to troubleshoot the file.

  • sashk
    sashk Member ✭✭
    Options

    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.

  • sashk
    sashk Member ✭✭
    Options

    @QWinUser did you keep the file which was provided from old Amex Savings site? If so, could you please remove PII and post it here, or upload somewhere? I'd like to compare… but I didn't keep my old files.

  • QWinUser
    QWinUser Member ✭✭✭✭
    Options

    @sashk I don't have any from the old site. I started trying to download files after it changed to the new site.

  • sashk
    sashk Member ✭✭
    Options

    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 :)

  • QWinUser
    QWinUser Member ✭✭✭✭
    Options

    @sashk thank you! I will give this a try and report back. It would be a good thing if you called Amex to relay this fix to them. Whether or not they act upon it, is another story.

  • QWinUser
    QWinUser Member ✭✭✭✭
    Options

    @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.

  • QWinUser
    QWinUser Member ✭✭✭✭
    Options

    I updated the <BALAMT> in the file and reloaded it and it corrected the online balance amount in my register.

  • QWinUser
    QWinUser Member ✭✭✭✭
    Options

    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>.

  • sashk
    sashk Member ✭✭
    Options

    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.

  • QWinUser
    QWinUser Member ✭✭✭✭
    Options

    The balance was just wrong. It had a number that was no where near what it should be.

  • QWinUser
    QWinUser Member ✭✭✭✭
    Options

    @sashk - I figured out why the online balance <BALAMT> is wrong. Amex programming is pulling the balance as of the start date of the range you choose. They need to change this to reflect the end date of the range you choose.

  • sashk
    sashk Member ✭✭
    Options

    Hmm. mine looks good — they use Available Balance amount, not Current Balance. Which is expected.



  • sashk
    sashk Member ✭✭
    Options

    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.

  • QWinUser
    QWinUser Member ✭✭✭✭
    Options

    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.

  • sashk
    sashk Member ✭✭
    Options

    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.

  • QWinUser
    QWinUser Member ✭✭✭✭
    Options

    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.

  • QWinUser
    QWinUser Member ✭✭✭✭
    edited August 2023
    Options

    @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:NONE

    Here 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"?><OFX

    CHARSET="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).

  • sashk
    sashk Member ✭✭
    edited August 2023
    Options

    Here are the specs: https://www.financialdataexchange.org/FDX/FDX/About/OFX-Work-Group.aspx?a315d1c24e44=2 , 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.

This discussion has been closed.