none
American Express downloads RRS feed

  • Question

  • Just a heads up. 

    Today on the AMEX website I went to download my current credit card activity into MS Mooney and received the message "QBO and QFX file types are unavailable for download at the moment, but CSV and PDF are available for use."

    Typically to transfer the information into MS Money, I download the QFX file, change the file format to OFX and save the file.  I then open the saved file and all the individual charges roll into my AMEX CC account. No problem. Quick and easy. And accurate.

    Don't know how long AMEX is going to make OFX files unavailable, but I hope it is not a permanent change. 

    Another credit card I have recently eliminated the option to download QFX files.  I no longer use that card.


    Monday, July 15, 2019 10:50 PM

All replies

  • I typically don't use the web version (the direct OFX api still work for me). But for web version,

    For my account, the is a button on the bottom right "Preview New Version". When selected, I will see a new view (using URL https://global.americanexpress.com/activity/recent), in that view, "Download" has a "Quicken" choice

    Monday, July 15, 2019 11:29 PM
  • Just a heads up. 

    Today on the AMEX website I went to download my current credit card activity into MS Mooney and received the message "QBO and QFX file types are unavailable for download at the moment, but CSV and PDF are available for use."

    Typically to transfer the information into MS Money, I download the QFX file, change the file format to OFX and save the file.  I then open the saved file and all the individual charges roll into my AMEX CC account. No problem. Quick and easy. And accurate..


    Have you considered PocketSense to download AMEX OFX info?

    I am no longer using Amex, but it may be worth a try for you.

    Tuesday, July 16, 2019 3:34 AM
    Moderator
  • I just tried to download and import my AMEX credit card transactions.  As Cal reports above, it's necessary to click on "Preview New Version" to get to a option to download to Quicken.  This does, indeed, download a QFX file, but Money won't import it.  The message from Money is  "invalid or corrupt data".

    When I run the file through the Microsoft OFX analyzer (at http://moneymvps.org/downloads/ofx/ofxanalyzer.msi) it reports "ERROR -- File not found, File empty, Out of Memory, or Unknown Parse Failure".  That's not at all helpful because there are no line numbers or additional information.  Apparently it fails immediately.

    There is a notice on the Quicken web site that AMEX is "currently undergoing website maintenance" and some functions may not work.  Maybe they broke the downloads too?  I am happy to edit the file to fix it if I can figure out what's wrong.  (BTW, the Quicken problem with AMEX has existed since at least July 15!)

    If helps, here is a copy of the start of the qfx file:

    <?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>20190729000000.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>71006</ACCTID></CCACCTFROM><BANKTRANLIST><DTSTART>20190723000000.000[-7:MST]</DTSTART><DTEND>20190822000000.000[-7:MST]</DTEND><STMTTRN><........................................


    Bill Becker

    Monday, July 29, 2019 7:38 PM
  • <?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><........................................


    Bill Becker

    Bill,

    1. I would add a blank line after
    <xml version="1.0" standalone="no"?>

    2. It may be fine, but I would try replacing
    <OFX xmlns:ns2="http://ofx.net/types/2003/04">
    with
    <OFX>


    Monday, July 29, 2019 10:41 PM
    Moderator
  • Cal,

    Excellent diagnostics!  The first change had no effect, but changing <OFX xmlns:ns2="http://ofx.net/types/2003/04"> to <ofx> allowed the import.  Money did ask which account to use, which is weird but acceptable until AMEX gets their act together.

    Many thanks!


    Bill Becker

    Tuesday, July 30, 2019 1:02 AM
  • Bill, would you like a Python file to massage the file?

    I could make the file to be modified a command line parameter, and make the output file always called amex_fix.ofx. So for example, you would invoke

    amex_fix.py amex123.qxf

    Alternatively I could always use the same name for the input file, if it is always the same.



    Tuesday, July 30, 2019 1:13 AM
    Moderator
  • Cal, - Yes, a Python file to make this change would be very nice.  I suspect that AMEX may never fix this and if they do it won't be soon.  It appears that moving things around in their customer interface takes priority over maintaining basic functionality.

    I think that it would be best to execute via a command line:

    amex_fix.py amex123.qfx

    where amex123.qfx is the downloaded file.  Once you do the hard part I can fiddle with the input and output locations.


    Bill Becker

    Tuesday, July 30, 2019 2:00 AM
  • Cal, - Yes, a Python file to make this change would be very nice.  I suspect that AMEX may never fix this and if they do it won't be soon.  It appears that moving things around in their customer interface takes priority over maintaining basic functionality.

    I think that it would be best to execute via a command line:

    amex_fix.py amex123.qfx

    where amex123.qfx is the downloaded file.  Once you do the hard part I can fiddle with the input and output locations.


    Bill BeckerC

    Copy and paste in to a *.py file. Note that if there is a space in the filename/path parameter, enclose in double quotes.

    I would invoke this from a batch file, where I would first CD to where I want to do my work.

    Filename case does not matter in Windows.

    EDIT: I see it creates a newline a the start of the file, as is. If that bothers Money, let me know. I will make a revision.

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

    #Example: AMEX_FIX.PY ax_abc.qfx
    #2019-7-29 Cal Learner...

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

    # [^>] matches any character that is not a >
    ofx_mod=re.sub("<OFX[^>]*>","<OFX>",ofx,1,flags=re.DOTALL) #the main change.

    #following adds newlines to OFX for human reading ease
    #replace any < not preceeded by whitespace to newline '<'
    ofx_mod = re.sub(r"(?<!\s)<", r"\n<",ofx_mod) #add the newlines

    ofxfile2.write(ofx_mod)

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

    ############end AMEX_FIX.PY########






    Tuesday, July 30, 2019 4:47 AM
    Moderator
  • Here is another, which should give better output:

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

    #Example: AMEX_FX2.PY ax_abc.qfx
    #2019-7-30 Cal Learner...

    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_FX2.PY########

    Tuesday, July 30, 2019 4:44 PM
    Moderator
  • Many thanks!  I used the first version on an AMEX download and the resulting file imports into Money with no issues.

    I did note that the second version is missing the:

     ofx_mod=re.sub("<OFX[^>]*>","<OFX>",ofx,1,flags=re.DOTALL) #the main change

    But that is not an issue, because I can easily merge the two versions.  I may also look at the possibility of modifying the program to insert tabs to indent some lines.  That would further improve human readability in the event that additional issues arise.


    Bill Becker

    Wednesday, July 31, 2019 1:27 AM
  • Many thanks!  I used the first version on an AMEX download and the resulting file imports into Money with no issues.

    I did note that the second version is missing the:

     ofx_mod=re.sub("<OFX[^>]*>","<OFX>",ofx,1,flags=re.DOTALL) #the main change

    But that is not an issue, because I can easily merge the two versions.  I may also look at the possibility of modifying the program to insert tabs to indent some lines.  That would further improve human readability in the event that additional issues arise.


    Bill Becker

    They should both work as is for Money. They used two different methods. Maybe change the name of the output file and compare the outputs. They are very similar in function.

    The second one has no blank line to start, and has a blank line before the <OFX>, as the spec calls for. Money is fairly tolerant.


    Wednesday, July 31, 2019 2:06 AM
    Moderator
  • Thanks yet again.  Clearly, my ignorance of Python is showing.  I will do some studying and experimenting and let you know if there are any issues.

    Bill Becker

    Wednesday, July 31, 2019 3:14 AM
  • This could also be turned into a PocketSense scrubber for the Amex card. It is not a certainty that the OFX file from the OFX server would have the same flaw, but it would seem likely.

     ofx_parts=re.split(r"\s*<OFX[^>]*>",ofx) #splits the file into 2 pieces in list

    produces two arrays. Those parts of the string, named ofx, before the thing described in the first parameter are saved as ofx_parts[0] and the part after as ofx_parts[1].

    The thing described is <OFX> along with  any whitespace that directly precedes  it, plus possible other crap before the >. We replace that thing with a string of our own choosing.

    Reading REGEX strings can be confusing for anybody, including maybe me if I try to read what I wrote I wrote a month later.




    Wednesday, July 31, 2019 3:35 AM
    Moderator