Get Fees - for accounting for PayPal fees properly in QuickBooks

getfees.py

#!/usr/bin/python
# Get Fees - for accounting for PayPal fees properly in QuickBooks
# Originally Written by Jose Diaz for HYPOXIC
# Distributed by Will Bradley (will@zyphon.com) under a CC-BY license.
#
# To use, change the "Pay Pal Account" value towards the bottom of the file
# to the name of the Quickbooks account you want PayPal feeds to go into.
#
# Run this Python script with the filename of the PayPal .iif file to use,
# and redirect its output to a file. Example: getfees.py > fees.iif

import sys
import os
import copy

gDebug = 0
global gAmountIndexInSPL
global gAmountIndexInTRNS
global gAccntIndexInTRNS
global gAccntIndexInSPL


class MyError(Exception):
    def __init__(self, value):
        self.value = value
    def __str__(self):
        return  repr(self.value)
def Usage():
    print """Usage:
    getfees file.iif
Extract info from file.iif for any fields named "Fee"
            """

def GetPositionOfColumns(fileName):
    # Read until the first TRNS or SPL is found
    # I am not sure if the files always have these lines at the beginning or in
    # which order. To make it safe, look for them any place and in any order in the
    # file. Then set the four globals gXXXXIndexInYYYY
    global gAmountIndexInSPL
    global gAmountIndexInTRNS
    global gAccntIndexInTRNS
    global gAccntIndexInSPL
    f=open(fileName,"rt")
    foundTrns = False
    foundSpl  = False
    done = False
    while not done:
        l = f.readline()
        if not l:
            raise MyError("Unexpected end of file. Format error: need !SPL and !TRNS ACCNT lines")
        lk = l.split("\t")
        lk = [i.rstrip("\r\n") for i in lk]
        if "!SPL"==l[:4]:
            foundSpl = True
            # See where
            if "AMOUNT" in lk:
                gAmountIndexInSPL = lk.index("AMOUNT")
                if gDebug:
                    print "SPL.AMOUNT is at column ", str(gAmountIndexInSPL)
            else:
                raise MyError("AMOUNT keyword not found in !SPL line")
            if "ACCNT" in lk:
                gAccntIndexInSPL  = lk.index("ACCNT")
                if gDebug:
                    print "SPL.ACCNT is at column ", str(gAccntIndexInSPL)
            else:
                raise MyError("ACCNT keyword not found in !SPL line")
        if "!TRNS"==l[:5]:
            foundTrns = True
            # See where
            if "AMOUNT" in lk:
                gAmountIndexInTRNS = lk.index("AMOUNT")
                if gDebug:
                    print "TRNS.AMOUNT is at column ", str(gAmountIndexInTRNS)
            else:
                raise MyError("AMOUNT keyword not found in !TRNS line")
            if "ACCNT" in lk:
                gAccntIndexInTRNS  = lk.index("ACCNT")
                if gDebug:
                    print "TRNS.ACCNT is at column ", str(gAccntIndexInTRNS)
            else:
                raise MyError("ACCNT keyword not found in !TRNS line")
        if foundSpl and foundTrns:
            done = True
    f.close()
    return

def HandleTransaction(firstLine, f):
    global gDebug
    done = False
    firstTime = True
    outputIfNoFeeFound = ""
    foundFee = False
    feeValue = 0
    splLineWithFee = ""
    while not done:
        if firstTime:
            firstTime = False
            l = firstLine
        else:
            l = f.readline()
        outputIfNoFeeFound += l
        if l:
            #if gDebug:
            #    print "Processing ", l
            if "ENDTRNS" == l[:7]:
                done = True
                continue
            lk = l.split("\t")
            lk = [i.rstrip("\r\n") for i in lk]
            #print lkprint
            if "SPL"==l[:3] and "Fee" in lk:
                splLineWithFee = l
                foundFee = True
                #Remember the amount
                feeValue = float(lk[gAmountIndexInSPL].rstrip("\r\n"))
                if gDebug:
                    print "Found Fee of %f" % (feeValue,) 
        else:
            raise MyError("Invalid format: unterminated TRNS/ENTRNS")
    if foundFee:
        if gDebug:
            print "  Processing Fee" 
        #Recreate a new transactiprinton record which contains the Fee info only
        #Recreate a new transaction header
        oldTransaction = firstLine.rstrip("\r\n").split("\t") 
        newTransaction = copy.copy(oldTransaction)  #make a deep copy
        newTransaction[gAccntIndexInTRNS] = '"Pay Pal Account"'
        newTransaction[gAmountIndexInTRNS] = str(-1.0 * feeValue)
        newSpl = splLineWithFee.rstrip("\r\n").split("\t")
        newSpl[gAccntIndexInSPL] = '"CC Processing"'
        newSpl[gAmountIndexInSPL] = str(feeValue)
        print "\t".join(newTransaction)
        print "\t".join(newSpl)
        print "ENDTRNS"
    else:
        print outputIfNoFeeFound.rstrip("\r\n")
    return

def main(argv):
    if len(argv) != 1:
        Usage();
        return
    # Make sure file exists
    if not os.path.isfile(argv[0]):
        print "File", argv[0],"does not exist"
        Usage()
        return
    # First process the header that tells us which columns are used to store
    # the data we are interested in. Save this values in globals gXXXXIndex
    GetPositionOfColumns(argv[0])
    f=open(argv[0],"rt")
    done = False
    while not done:
        l=f.readline()
        if not l:
            done = True
            continue
        #There are 2 possiblities, we get a new transaction (TRANS...) or not
        if l[:4] == "TRNS":
            HandleTransaction(l,f)
        else:
            print l.rstrip("\r\n")
    return

if __name__ == "__main__":    
    main(sys.argv[1:])