Dumb & Dumber: Quicken & Amex

Christopher Rath

2005-12-07 (updated 2006-02-24, 2006-03-04, & 2007-11-21)

Introduction

American Express Canada is not a customer service oriented company.  The best evidence of this is their website: there is no method to tell Amex when their website is broken... I know, because I spent an hour today (7 December, 2005) attempting to do just that.  American Express has no telephone number, email address, or "feedback button" one can use to inform Amex of a website problem.  When I called the toll-free number on the back of my card and attempted to report the problem with their website, the agent was unable to determine how to log the problem; even after talking to two other help desks inside of American Express.

Intuit is also not a customer service oriented company.  Quicken is the best evidence of Intuit's lack of customer focus.  The sad fact is that until the 2006 financial products were released Quicken had no real competitor in Canada; thus they had no incentive to improve.  Quicken lacks simple features I expect: downloading full transaction detail from Amex, importing dates in year-month-day format, creating invoices with more than 30 line items, creating investment alerts based upon the movement of the price of a stock or bond, etc.

Amex's charge card program is second to none, and as a business traveller they are the only real choice available to me.  As I noted before, prior to the recent 2006 MS Money release, Quicken had no true competitor in Canada; in Canada, Microsoft only released the low-end version of MS Money.  Thus, I become a customer of both companies.  The poor service of these companies compounds at their overlap point: where data makes its way from Amex into Quicken.  This is emphasised to me anytime Amex makes changes to their website; since those changes always break Quicken's automatic Amex transaction import feature. [2006-02-24: AMEX changed their website, see below, and broke Quicken import again.  Why am I not surprised!]

Amex Website Problems

Amex's Canadian website has three problems related to downloading data to Quicken:

• currency descriptors are in Spanish instead of English---this has been a problem for at least 5 years [2006-02-24: As part of the recent website update they did fix this problem.]
• QIF files downloaded from the website always include all transactions from the reporting period (there is no, "every transaction since my last download" option)---this has been a problem for at least 5 years
• the dates contained inside of the QIF file are in the form of DD/MM/YYYY; which Quicken cannot understand (Quicken only understands MM/DD/YYYY format)---this is a new problem introduced sometime since the 3^rd or 4^th  December, 2006 when Amex revamped their website [2006-02-24: some time between today and last Sunday afternoon AMEX tweaked their website and put the date format back to USA MM/DD/YYYY format.  This breaks banking booster and creates a big mess.  My workaround script, below, also now must be updated so that it doesn't mess with the date format; so, I've added comments and a couple of extra entries that can be uncommented as needed.] [2006-03-04: over the past week, since my last update, AMEX has updated their data export facility and put the dates back to DD/MM/YYYY mode; so I've toggle the code in the workaround script.]
• as Amex updates the transactions in the QIF file each day, they do not simply append to the bottom of the file

Problem Commentary

The QIF file always containing all transactions is a real problem and I wrote a Perl script (see below) to massage the QIF file and remove extraneous transactions.  For individuals unable to run Perl or otherwise massage the file, this problem creates extra work after a Quicken import but doesn't result in any errors in the data.  The fact that new transactions added to the file are not always added to the end means that the simple Perl script I wrote did not always produce correct results.

The issue of the incorrect date format is a real problem and cannot be easily worked-around by an average Quicken user.  As noted above, this is a new problem and was the catalyst that caused me to attempt to alert American Express this week that they had a problem with their website. 

The currency descriptor problem is an annoyance, and it is an indication of the lack of focus American Express has for customer service.  This type of problem demonstrates that Amex simply does not care about the quality of the work they do.  The Perl script I wrote does not attempt to fix this problem in the QIF data.

Problem Resolution

Run this short Perl script each time you download a new QIF file.  Supply both the most current QIF file and the previous one to the script.  The script will then emit only the new transactions to be imported.  The emitted transactions can be discarded after importing, and the most current QIF file saved for the next time you download (to be run against the newest downloaded transactions). [2007-11-21: I've posted a new script that uses the Finance::QIF module to parse the new file, compare each record to the previous version, and only emit new records. The old script is embedded in this new one (as comments).]

Click here to open the Perl script.