none
Unable to import QFX files from Amex RRS feed

  • Question

  • Hi to everyone.  

    I'm new to this forum, and I realize this is an old thread, but I have been searching for an answer to a similar problem with no luck.  I will try to be brief in describing my problem.

    I have been using Microsoft Money for 20 years.  For the last 10 years I've been running it on a macbook through parallels with no problems for all my accounts:  BoA, Chase, Amex, Ally...  I download the QFX files and import into Money.  Earlier this year Amex made a change to their website and Quicken users (and subsequently Money users) were unable to import their data.  Amex made a change to the website "Return to previous version" that solved the problem until Quicken users were able to deactivate then reactivate their version of Quicken.  That solved my import problem as well.  I have had no issues importing Amex QFX files into Money using the "Return to previous version." on the Amex website.

    As of 2-3 weeks ago, Amex got rid of the choice on their website to "Return to previous version."   Now when I download the QFX file and import into Money, I get this:  

    "The file you attempted to import appears to be invalid or contain corrupt data.  Please download the file again.  If this problem continues, contact your financial institution."

    I have tried everything I can think of to correct this:  downloading using different browsers in windows/paralles, downloading in macOS environments and dragging into windows environment, renaming the file from QFX--> OFX, etc...  Obviously I contacted Amex and they say it's a Quicken problem, which doesn't help me.  Any help this group could provide would be greatly appreciated.  My family uses Amex for everything, and I can't manually enter 150+ transactions monthly and keep my sanity. I really don't want to ditch Money, and don't wan't to use Quicken, but if I can't get Amex importing as previous, I will have no choice but to look for an alternative.  Sorry for the length of this post.  Thanks in advance for any help provided.

    Mike

    Sunday, 8 December 2019 9:51 PM

All replies

  • Make the smallest QFX file that is easy to do, perhaps by limiting the range to just one day.

    Then with a text editor, such as notepad --not a word processor, change any proprietary info. That would probably only be the card number, which is in the <ACCTID> field. Change that to something like 123456789012345. Then copy the whole thing, and paste into a post here.

    Somebody can probably tell you what the problem is.

    Or maybe somebody will already know.

    Sunday, 8 December 2019 10:04 PM
    Moderator
  • Cal,

    Thanks for you fast response.  Here is the file info in textedit (mac) but was the same in notepad.  I changed the account # as you suggested.  Thanks again for your help.

    <?xml version="1.0" standalone="no"?><?OFX OFXHEADER="200" VERSION="202" SECURITY="NONE" OLDFILEUID="NONE" NEWFILEUID="NONE"?><OFX xmlns:ns2="http://ofx.net/types/2003/04"><SIGNONMSGSRSV1><SONRS><STATUS><CODE>0</CODE><SEVERITY>INFO</SEVERITY><MESSAGE>Login Successful!</MESSAGE></STATUS><DTSERVER>20191208000000.000[-7:MST]</DTSERVER><LANGUAGE>ENG</LANGUAGE><FI><ORG>AMEX</ORG><FID>3101</FID></FI><INTU.BID>3101</INTU.BID></SONRS></SIGNONMSGSRSV1><CREDITCARDMSGSRSV1><CCSTMTTRNRS><TRNUID>0</TRNUID><STATUS><CODE>0</CODE><SEVERITY>INFO</SEVERITY></STATUS><CCSTMTRS><CURDEF>USD</CURDEF><CCACCTFROM><ACCTID>123456789|12345</ACCTID></CCACCTFROM><BANKTRANLIST><DTSTART>20191205000000.000[-7:MST]</DTSTART><DTEND>20191205000000.000[-7:MST]</DTEND><STMTTRN><TRNTYPE>DEBIT</TRNTYPE><DTPOSTED>20191205000000.000[-7:MST]</DTPOSTED><TRNAMT>-10.00</TRNAMT><FITID>320193400080231601</FITID><REFNUM>320193400080231601</REFNUM><NAME>EXXONMOBIL FUEL OUTS800 2439966 </NAME></STMTTRN><STMTTRN><TRNTYPE>DEBIT</TRNTYPE><DTPOSTED>20191205000000.000[-7:MST]</DTPOSTED><TRNAMT>-23.56</TRNAMT><FITID>320193400074466167</FITID><REFNUM>320193400074466167</REFNUM><NAME>QUIKTRIP</NAME></STMTTRN></BANKTRANLIST><LEDGERBAL><BALAMT>-33.56</BALAMT><DTASOF>20191208000000.000[-7:MST]</DTASOF></LEDGERBAL></CCSTMTRS></CCSTMTTRNRS></CREDITCARDMSGSRSV1></OFX>

    Sunday, 8 December 2019 11:12 PM
  • I got it to parse with this:

    <?xml version="1.0" standalone="no"?>
    <?OFX OFXHEADER="200" VERSION="202" SECURITY="NONE" OLDFILEUID="NONE" NEWFILEUID="NONE"?>
    <OFX><SIGNONMSGSRSV1><SONRS><STATUS><CODE>0</CODE><SEVERITY>INFO</SEVERITY><MESSAGE>Login Successful!</MESSAGE></STATUS><DTSERVER>20191208000000.000[-7:MST]</DTSERVER><LANGUAGE>ENG</LANGUAGE><FI><ORG>AMEX</ORG><FID>3101</FID></FI><INTU.BID>3101</INTU.BID></SONRS></SIGNONMSGSRSV1><CREDITCARDMSGSRSV1><CCSTMTTRNRS><TRNUID>0</TRNUID><STATUS><CODE>0</CODE><SEVERITY>INFO</SEVERITY></STATUS><CCSTMTRS><CURDEF>USD</CURDEF><CCACCTFROM><ACCTID>123456789|12345</ACCTID></CCACCTFROM><BANKTRANLIST><DTSTART>20191205000000.000[-7:MST]</DTSTART><DTEND>20191205000000.000[-7:MST]</DTEND><STMTTRN><TRNTYPE>DEBIT</TRNTYPE><DTPOSTED>20191205000000.000[-7:MST]</DTPOSTED><TRNAMT>-10.00</TRNAMT><FITID>320193400080231601</FITID><REFNUM>320193400080231601</REFNUM><NAME>EXXONMOBIL FUEL OUTS800 2439966 </NAME></STMTTRN><STMTTRN><TRNTYPE>DEBIT</TRNTYPE><DTPOSTED>20191205000000.000[-7:MST]</DTPOSTED><TRNAMT>-23.56</TRNAMT><FITID>320193400074466167</FITID><REFNUM>320193400074466167</REFNUM><NAME>QUIKTRIP</NAME></STMTTRN></BANKTRANLIST><LEDGERBAL><BALAMT>-33.56</BALAMT><DTASOF>20191208000000.000[-7:MST]</DTASOF></LEDGERBAL></CCSTMTRS></CCSTMTTRNRS></CREDITCARDMSGSRSV1></OFX>

    What I did was put <?xml .. and <?OFX .... on their own lines.

    Then I changed the <OFX xml...> piece to just <OFX>. No changes were made beyond that.S

    Wait... I just remembered.  I already wrote a Python program that can fix this. I described it in https://social.microsoft.com/Forums/en-US/a9e26d11-cf55-4ba1-9b4d-727627707955/american-express-downloads?forum=money

    Use the improved  AMEX_FX2.PY version.

    If that does not work for you, let me know.



    Monday, 9 December 2019 12:33 AM
    Moderator
  • I had this issue with AMEX downloads for several months and Cal's Python program fixed those files.  Since November 20th, the downloads have imported correctly for me, but, from your experience, there are still issues.  If you are willing to manually edit the downloaded file using Notepad, I found that this single replacement worked:

    Find <OFX xmlns:ns2="http://ofx.net/types/2003/04"> and replace it with <OFX>.


    Bill Becker

    Monday, 9 December 2019 4:00 AM
  • Here is a version with a slight mod to let it run on Python 2 or 3:

    ############begin AMEX_FX3.PY########
    #modifies AMEX OFX file with extra crud in the <OFX>
    #invoke with filename of the source as the parameter.

    #Example: AMEX_FX3.PY ax_abc.qfx
    #2019-7-30 Cal Learner...
    #2019-12-8 modified to run on Python 2 or 3.

    import sys, re

    ofxfile1= open(sys.argv[1],'r') # open for read in current directory
        # Input file read in as parameter. Output file fixed in this case.
    ofxfile2= open("ModOut.ofx",'w') # open for write in current directory

    #do stuff


    ofx=ofxfile1.read()

    ofx_parts=re.split(r"\s*<OFX[^>]*>",ofx) #splits the file into 2 pieces in list
    ofx_body= re.sub(r"(?<!\s)<", r"\n<",ofx_parts[1]) #add the newlines to body

    ofx_mod=ofx_parts[0]+"\n\n<OFX>" + ofx_body #assembling the new header and the body


    ofxfile2.write(ofx_mod)

    ofxfile1.close()
    ofxfile2.close()
    print("Modified version of ", sys.argv[1],' written to "ModOut.ofx" file.')

    ############end AMEX_FX3.PY########

    Monday, 9 December 2019 4:29 AM
    Moderator
  • Cal and Bill,

    This help is much appreciated.  I will try this when home later today.

    I will first try the easy fix OFX ml --> OFX first.  Hopefully that will work for me.

    I have no experience in programming or Python, so the 2nd option is a bit daunting.  I would need very specific instructions on how to install/execute that option.

    Will let you know how it goes tonight.

    Thanks again,

    Mike

    Monday, 9 December 2019 3:00 PM
  • Guys,

    The OFX xmlns... --> OFX changed worked.  I was able to import yesterdays transactions without issue.  

    Now that my crisis has been avoided, can you please explain why this change worked?  Why does Money recognize OFX and not OFX xmlns...?  I am technologically adept and knowledgeable, 

    but have no experience with programming or coding.

    I can't thank you guys enough.  If there is a way I can reciprocate the favor or donate to a charity you support, I would be happy to do so.

    Mike

    Monday, 9 December 2019 9:39 PM
  • "<OFX xmlns..." is not in the OFX specification.

    The spec also calls for a couple of things before that to be on their own lines, but Money ignores that one.


    Monday, 9 December 2019 11:14 PM
    Moderator
  • Many thanks for the feedback.  Happy fellow forum users are our reward.

    I am not a programmer either, but I think that AMEX has been making changes that somehow cause their site to generate downloads that violate the published standard of the Open Financial Exchange (OFX).  This happened with Chase bank earlier this year and it took a couple of months to get it fixed.

    If this AMEX issue is affecting Quicken users, I think that AMEX will prioritize fixing it.


    Bill Becker

    Monday, 9 December 2019 11:23 PM
  • Someone created an application that converts qfx files to ofx file  and removes or fixes the codes that choke Money.  I  have used it on the qfx files from one of my banks (Dollar Direct)

    that produces a qfx file that does not conform to Money's rules and the converter works fine.

    The program is open source and posted on git hub which checks for viruses. I have used it for several weeks now.  However, a paranoid bot or moderator not from this group deleted the thread when the author posted a link here. 

    So if you want to try it, put ConvertToOfx dot e x e (replace the dot with a . and remove the spaces) into a search engine to get to the application.



    • Edited by bobjbkln Wednesday, 11 December 2019 4:42 AM
    Wednesday, 11 December 2019 4:40 AM