Collect tax data from pnc.csv transaction file(s)

get_tax_data.py

#!/usr/bin/env python
###############################################################
#                                                             #
# Author  : Kwesi Aguillera                                   #
#                                                             #
# Created : 03/30/2017                                        #
# Modified: 03/31/2017                                        #
#                                                             #
# Purpose : To catergorize and total expenses for filling     #
#           taxing for Gratipay                               #
#                                                             #
# Usage   : $ python get_tax_data.py <path/to/file(s)>        #
#                                                             #
###############################################################

import csv
import sys
import os
from decimal import Decimal as D


advert_list = ["FEDEXOFFICE"]
advert_total = D(0)

# lops - Legal or Professional Services
lops_vendors = ["TOR"]
lops_total = D(0)

# obe - Other Business Expenses
obe_vendors = ["GITHUBCOM","GETSENTRY","LIBRATO","HEROKU","DNSIMPLE","WWWFRESHDESKCOM","TRAVISCICOM","DIGITALOCEANCOM","NETDNA","TRANSIFEX","HACKERONE","IWANTMYNAME","LASTPASSCOM","SENTRY","PIPEDRIVE","XERO","HOEFLER","NEXTSP","USPS"]
obe_total = D(0)

# mae - Meals and Entertainment
mae_list = ["MAZARINE","CORIANDER","WALGREENS","CHIPOTLE","LOBSTER","WHOLEFDS","SQ","PARK","COUPA","JACK","SUPER","OLYMPUS","MARINA","Apteka","ACACIA","PROSPECT","CHAAT","S","SELC","SORABOL","HOMEROOM","MARLOWE"]
mae_total = D(0)

# ote - Overnight Travel Expenses
ote_list = ["UNITED","AIRBNB","SKYMAG"]
ote_total = D(0)

# lt - Local Transportation Expenses
lt_list = ["CALTRAIN","CONVENTION","BART-CLIPPER","BART-POWELL","BART"]
lt_total = D(0)

supplies_list = ["APPLE"]
supplies_total = D(0)


income = D(0)

uncaptured_list = []
uncatergorized = []

the_arg = sys.argv[1]
file_list = []
if os.path.isfile(the_arg):
    file_list.append(the_arg)
elif os.path.isdir(the_arg):
    for each in xrange(1,13):
        file_list.append("%s/%02d/pnc.csv" % (the_arg,each))

for dfile in file_list:
    print "Processing %s..." % (dfile)
    with open(dfile, 'rb') as csvfile:
        filereader = csv.reader(csvfile, delimiter=',')
        for row in filereader:
            desc = row[1].split(" ")
            if "PURCHASE" in desc:
                if desc[4].strip('*') in obe_vendors:
                    obe_total += D(row[2].strip('$'))
                elif desc[4].strip('*') in mae_list:
                    mae_total += D(row[2].strip('$'))
                elif desc[4].strip('*') in ote_list:
                    ote_total += D(row[2].strip('$'))
                elif desc[4].strip('*') in supplies_list:
                    supplies_total += D(row[2].strip('$'))
                elif desc[4].strip('*') in advert_list:
                    advert_total += D(row[2].strip('$'))
                elif desc[4].strip('*') in lops_vendors:
                    lops_total += D(row[2].strip('$'))
                elif desc[4].strip('*') in lt_list:
                    lt_total += D(row[2].strip('$'))
                else:
                    uncatergorized.append("%s - %s" % (desc[4], row[2]))
            elif "CREDIT" in desc:
                if "CARD" or "PAYPAL" in desc:
                    income += D(row[3].strip('$'))
                else:
                    uncatergorized.append("%s %s - %s" % (desc[3], desc[4], row[2]))
            elif "CHECK" in desc:
                uncatergorized.append("%s %s - %s" % (desc[0], desc[1], row[2]))
            elif "RECURRING" in desc:
                if desc[4].strip('*') in obe_vendors:
                    obe_total += D(row[2].strip('$'))
                else:
                    uncatergorized.append("%s - %s" % (desc[4], row[2]))
            elif "INTERNATIONAL" in desc:
                if "FEE" in desc:
                    obe_total += D(row[2].strip('$'))
                else:
                    uncatergorized.append("%s %s - %s" % (desc[0], desc[2], row[2]))
            elif "PAYMENT" in desc:
                if desc[6].strip('*') in obe_vendors:
                    obe_total += D(row[2].strip('$'))
                elif desc[6].strip('*') in mae_list:
                    mae_total += D(row[2].strip('$'))
                elif desc[6].strip('*') in ote_list:
                    ote_total += D(row[2].strip('$'))
                else:
                    uncatergorized.append("%s - %s" % (desc[6], row[2]))
            elif "ATM" in desc:
                if "FEE" in desc:
                    obe_total += D(row[2].strip('$'))
                else:
                    mae_total += D(row[2].strip('$'))
            elif "WITHDRAWAL" in desc:
                    uncatergorized.append("%s - %s" % (row[1], row[2]))
            else:
                if "Date" not in row:
                    uncaptured_list.append(row[2])

print

for each in uncaptured_list:
    print each
for each in uncatergorized:
    print each

print
print "Income - $%.2f\n" % (income)
print "Total Other Business Expenses - $%.2f\n" % (obe_total)
print "Total Meals and Entertainment Expenses - $%.2f\n" % (mae_total)
print "Total Overnight Travel Expenses - $%.2f\n" % (ote_total)
print "Total Supplies Expenses - $%.2f\n" % (supplies_total)
print "Total Advertising Expenses - $%.2f\n" % (advert_total)
print "Total Legal or Professional Services Expenses - $%.2f\n" % (lops_total)
print "Total Local Transportation Expenses - $%.2f\n" % (lt_total)