amexcsv2qif

#!/usr/bin/env python

from __future__ import print_function

import formatVersions
import os
import sys
import csv
import argparse, textwrap

def readCategories(filename):
   """Read the category data from a file"""
   cats = {}
   with open(filename, 'r') as csvfile:
      csvreader = csv.reader(csvfile, delimiter=',', quotechar='"')
      for i, row in enumerate(csvreader):
         if len(row) == 0:
            # Ignore blank lines
            continue
         if len(row) < 2:
            print("Syntax error in line %d: Need 2 fields separated "
                  "by a comma: vendor, category!" % i, file=sys.stderr)
            print(row, file=sys.stderr)
            continue
         vendor = row[0].strip()
         if vendor.startswith("GglPay "):
            vendor = vendor.replace("GglPay ",'')
         category = row[1].strip()
         if vendor and category:
            cats[vendor] = category
         else:
            if not vendor:
               print("Error in line %d: vendor field is "
                     "empty (before the comma)!" % i, file=sys.stderr)
            else:
               print("Error in line %d: category field for "
                     "vendor='%s' is empty (after the comma)!" % (i, vendor), file=sys.stderr)
            continue
   return cats


parser = argparse.ArgumentParser(
    prog='amex2qif',
    formatter_class=argparse.RawTextHelpFormatter,
    description="""Convert American Express CSV export files to QIF format.\n
The formats are:
   old     (0)=>date, ref, payee, card-holder-name, card-number, memo, amount
   new     (1)=>date, payee, card-holder-name, card-holder-number-end, amount
   new2    (2)=>Date, Description, Category, type, Amount
   default (3)=>date, ref, amount, payee, memo
           (4)=>Date, Description, Amount, 'Extended Details',
                  'Appears On Your Statement As Address', City/State,
                  'Zip Code', Country, Reference, Category

""",
    epilog=textwrap.fill("""NOTE: If you provide a 'categories.txt' file in the 
same directory as the amex2qif program, it will automatically be used.  Each line
should have the vendor name, a comma, and then the category to be used when
the payee matches that vendor.  Empty lines are ignored.  A default version of
this file can be kept in the directory of the amex2qif executable.  If a copy
of this file is found in the directory that this command is run in, it
overrides the version in the executable directory.
"""))

parser.add_argument('csvfilename', type=str)
parser.add_argument('--nomemo', action="store_true", default=False,
                    help="Disable importing of Memo field")
parser.add_argument('--format', default='default', choices=['default', 'old', 'new', 'new2', '0', '1', '2', '3', '4'],
                    help="Select the format. Do amexcsv2qif --help for detials.")
parser.add_argument('--printcats', action="store_true", default=False,
                    help="Print the categories read from the 'categories.txt' file and exit.")
parser.add_argument('--header', action="store_true", default=False,
                    help="The first line of the CSV file contains column headers")
parser.add_argument('--complain', action="store_true", default=False,
                    help="Complain about unknown Payees (to stderr)")


args = parser.parse_args()

legacyVersionMap = {
   'old': 0,
   'new': 1,
   'new2': 2,
   'default': 3
}

formatVersion = args.format
if formatVersion in legacyVersionMap:
   formatVersion = legacyVersionMap[formatVersion]

rowHandlerName = 'FormatVersion%s' % formatVersion
if rowHandlerName not in dir(formatVersions):
      raise RuntimeError("No row handler defined for format %s" % args.format)

rowHandler = getattr(formatVersions, rowHandlerName)

edir = os.path.dirname(os.path.realpath(__file__))

catmap = {}

# Read the category map
if os.path.exists('categories.txt'):
   # Local version overrides one in executable directory
   catmap = readCategories('categories.txt')
elif os.path.exists(os.path.join(edir, 'categories.txt')):
   catmap = readCategories(os.path.join(edir, 'categories.txt'))
else:
   catmap = {}

if args.printcats:
   print()
   print("CATEGORIES:")
   vendlen = max([len(v) for v in catmap])
   fmt = "  %%%ds: %%s" % vendlen
   for vend in sorted(catmap.keys()):
      cat = catmap[vend]
      print(fmt % (vend, cat))
   print
   sys.exit()

unknown_payees = set()


allRows = []
with open(args.csvfilename, 'r') as csvfile:
   csvreader = csv.reader(csvfile, delimiter=',', quotechar='"')
   for i, row in enumerate(csvreader):
      if len(row) == 0:
         # Ignore blank lines
         continue
      if i == 0 and args.header:
         continue
      if len(row) < 5:
         print("Error in line %d: only %d field(s)!" % (i, len(row)), file=sys.stderr)
         continue

      allRows.append(row)


print("!Type:CCard")
for (date, ref, payee, customer, amount, memo) in rowHandler(allRows):

   category = ''

   for p, cat in catmap.items():
      if payee.upper().startswith(p.upper()):
         category = cat
      elif payee.startswith('GglPay '):
         if payee.replace('GglPay ','').upper().startswith(p.upper()):
            category = cat

   if category == '':
      unknown_payees.add(payee.upper())

   if customer:
      if memo and customer not in memo:
         memo += " (%s)" % customer

   if memo and ref:
      memo += " Ref: %s" % ref

   if payee.startswith('AMZN Mktp US'):
         payee = 'AMZN Mktp US'
   if payee.startswith('Amazon.com'):
         payee = 'Amazon.com'
   if payee.startswith('CULINART GROUP'):
         payee = 'CULINART GROUP'

   print("D%s" % date)
   print("T%.2f" % amount)
   print("P%s" % payee)
   print("L%s" % category)
   if memo and not args.nomemo:
      print("M%s" % memo)
   print("^")


if args.complain:
    print("\nUnrecognized Payees:", file=sys.stderr)
    for payee in sorted([s for s in unknown_payees]):
        print("   %s" % payee, file=sys.stderr)