csv2ynab

#!/usr/bin/env python
# -*- coding: utf-8 -*-
# vim: sw=4:ts=4:expandtab


"""csv2ynab: Converts Revolut & Paypal csv files to an acceptable format for input to YNAB."""

from __future__ import absolute_import, print_function

#from sys import stdin, stdout
import sys
import csv
from argparse import RawTextHelpFormatter, ArgumentParser
import datetime
import traceback

VERSION = '0.2'

BASE_CURRENCY = 'GBP'

YNAB_FIELD_DATE = 'Date'
YNAB_FIELD_PAYEE = 'Payee'
YNAB_FIELD_CATEGORY = 'Category'
YNAB_FIELD_MEMO = 'Memo'
YNAB_FIELD_OUTFLOW = 'Outflow'
YNAB_FIELD_INFLOW = 'Inflow'
YNAB_HEADER = [YNAB_FIELD_DATE, YNAB_FIELD_PAYEE, YNAB_FIELD_CATEGORY, YNAB_FIELD_MEMO, YNAB_FIELD_OUTFLOW, YNAB_FIELD_INFLOW]

REVOLUT_FIELD_DATE = 'Completed Date'
REVOLUT_FIELD_PAYEE = 'Reference'
REVOLUT_FIELD_CATEGORY = 'Category'
REVOLUT_FIELD_OUTFLOW = 'Paid Out (GBP)'
REVOLUT_FIELD_INFLOW = 'Paid In (GBP)'
REVOLUT_IDENTIFIER_KEY = REVOLUT_FIELD_DATE

PAYPAL_FIELD_DATE = 'Date'
PAYPAL_FIELD_PAYEE = ' Name'
PAYPAL_FIELD_TYPE = ' Type'
PAYPAL_FIELD_MEMO = ' Item Title'
PAYPAL_FIELD_NET = ' Net'
PAYPAL_FIELD_CURRENCY = ' Currency'
PAYPAL_FIELD_STATUS = ' Status'
PAYPAL_FIELD_REF_TRANS = ' Reference Txn ID'
PAYPAL_FIELD_TRANS_ID = ' Transaction ID'
PAYPAL_IDENTIFIER_KEY = ' Counterparty Status'



parser = ArgumentParser(description="description: csv2ynab processes Revolut & PayPal csv files for import into YNAB",
                        prog='csv2ynab', usage='%(prog)s [options] <source> <dest>',
                        formatter_class=RawTextHelpFormatter)

parser.add_argument(dest='source', nargs='?', help='the .csv file to process')
parser.add_argument(dest='dest', nargs='?', help='the output file (defaults to stdout)')
parser.add_argument('-E', '--encoding', default='utf-8', help="File encoding (default: utf-8)")
parser.add_argument('-c', '--category', action='store_true', help='Fill in the category field', default=False)
parser.add_argument('-m', '--memo', action='store_true', help='Fill in the Memo field', default=False)
parser.add_argument('-n', '--nobank', action='store_true', help='Skip Bank entries', default=False)
parser.add_argument('-V', '--version', help="show version and exit", action='store_true', default=False)

args = parser.parse_args()


class baseCSV:

    def __init__(self, args, reader, writer):
        self.args = args
        self.reader = reader
        self.writer = writer

    def isBankTopUp(self, row):
        return False

    def absAmount(self, value):
        if value[:1] == '-':
            value = value[1:]
        return value

    def processCurrency(self, amount):
        """ Remove the comma in n,nnn.nn"""
        return amount.replace(',','')

    def writeHeader(self):
         self.writer.writeheader()


    def writeRow(self, y_row):
        # Remove ',' from currency amounts
        y_row[YNAB_FIELD_OUTFLOW] = self.processCurrency(y_row[YNAB_FIELD_OUTFLOW])
        y_row[YNAB_FIELD_INFLOW] = self.processCurrency(y_row[YNAB_FIELD_INFLOW])

        self.writer.writerow(y_row)

    def Convert(self):
        print("")



class RevolutCSV(baseCSV):

    def __init__(self, args, reader, writer):
        baseCSV.__init__(self, args, reader, writer)

    def isBankTopUp(self, row):
        payee = row[REVOLUT_FIELD_PAYEE]
        return payee[:6] == 'Top-Up' or payee[:11] == 'Auto Top-Up'

    def ProcessCurrency(self, amount):
        """ Remove the comma in n,nnn.nn"""
        return amount.replace(',','')

#        if amount == '':
#            return ''
#        return Decimal(amount)

    def Convert(self):
        self.writeHeader()

        for r_row in self.reader:

            if self.args.nobank and self.isBankTopUp(r_row):
                continue

            y_row = {}
            # Do Date
            r_date = r_row[REVOLUT_FIELD_DATE]
            y_date = datetime.datetime.strptime(r_date, '%d %B %Y')
            y_date = (datetime.date.strftime(y_date, "%d/%m/%Y"))
            y_row[YNAB_FIELD_DATE] = y_date

            # Payee
            y_row[YNAB_FIELD_PAYEE] = r_row[REVOLUT_FIELD_PAYEE]

            # Category

            if self.args.category:
                y_row[YNAB_FIELD_CATEGORY] = r_row[REVOLUT_FIELD_CATEGORY]
            else:
                y_row[YNAB_FIELD_CATEGORY] = ''

            # Memo
            if self.args.memo:
                y_row[YNAB_FIELD_MEMO] = r_row['Exchange Out'] + r_row['Exchange In']
            else:
                y_row[YNAB_FIELD_MEMO] = ''

            # Inflow, Outflow

            y_row[YNAB_FIELD_INFLOW] = self.ProcessCurrency(r_row[REVOLUT_FIELD_INFLOW])
            y_row[YNAB_FIELD_OUTFLOW] = self.ProcessCurrency(r_row[REVOLUT_FIELD_OUTFLOW])


            self.writeRow(y_row)


class PaypalCSV(baseCSV):
    def __init__(self, args, reader, writer):
        baseCSV.__init__(self, args, reader, writer)

    def isCompleted(self, row):
        return row[PAYPAL_FIELD_STATUS] == 'Completed'

    def isBankTopUp(self, row):
        return row[PAYPAL_FIELD_PAYEE] == 'Bank Account' and row[PAYPAL_FIELD_TYPE] == 'Add Money from a Bank Account'

    def isTemporaryHold(self, row):
        return row[PAYPAL_FIELD_PAYEE] == 'PayPal' and row[PAYPAL_FIELD_TYPE] == "Temporary Hold" and \
            not self.isCompleted(row)

    def isPendingBalancePayment(self, row):
        return row[PAYPAL_FIELD_PAYEE] == 'PayPal' and row[PAYPAL_FIELD_TYPE] == "Pending Balance Payment" and \
            not self.isCompleted(row)

    def isCurrencyConversion(self, row):
        return row[PAYPAL_FIELD_TYPE] == 'Currency Conversion'

    def isBaseCurrency(self, row):
        return row[PAYPAL_FIELD_CURRENCY] == BASE_CURRENCY

    def Convert(self):
        self.writeHeader()
        trans_id = '?????????????'

        for p_row in self.reader:

            if self.isPendingBalancePayment(p_row):
                continue

            if self.args.nobank and self.isBankTopUp(p_row):
                continue

            if self.isTemporaryHold(p_row):
                continue

            if self.isCurrencyConversion(p_row):
                if self.isBaseCurrency(p_row):
                    trans_id = p_row[PAYPAL_FIELD_REF_TRANS]
                    base_amount = p_row[PAYPAL_FIELD_NET]
                continue


            y_row = {}
            # Do Date
            y_row[YNAB_FIELD_DATE] = p_row[PAYPAL_FIELD_DATE]

            y_row[YNAB_FIELD_PAYEE] = p_row[PAYPAL_FIELD_PAYEE]
            y_row[YNAB_FIELD_CATEGORY] = ''

            if self.args.memo:
                y_row[YNAB_FIELD_MEMO] = p_row[PAYPAL_FIELD_MEMO]
            else:
                y_row[YNAB_FIELD_MEMO] = ''

            y_row[YNAB_FIELD_OUTFLOW] = ''
            if p_row[PAYPAL_FIELD_TRANS_ID] == trans_id:
                y_row[YNAB_FIELD_INFLOW] = base_amount
                y_row[YNAB_FIELD_MEMO] = y_row[YNAB_FIELD_MEMO] + \
                    " (" + self.absAmount(p_row[PAYPAL_FIELD_NET]) + p_row[PAYPAL_FIELD_CURRENCY] + ")"
                trans_id = '?????????????'
                base_amount = ''
            else:
                y_row[YNAB_FIELD_INFLOW] = p_row[PAYPAL_FIELD_NET]

            self.writeRow(y_row)



def run():  # noqa: C901
    """Parses the CLI options and runs the main program
    """

    if args.version:
        print('v%s' % VERSION)
        exit(0)


#    with open(args.source, newline='') as source:
        # Check we've got a header line

    try:
        source = open(args.source, encoding=args.encoding)  # if args.source else sys.stdin

        if not csv.Sniffer().has_header(source.read(1024)):
            print ("ERROR: s does not appear to have a header line.", file=sys.stderr)
            exit(1)
        source.seek(0)

        # Get some basic csv file info
        dialect = csv.Sniffer().sniff(source.read(1024))

        source.seek(0)

        # Start reading the file
        reader = csv.DictReader(source, delimiter=dialect.delimiter)

    except Exception as err:  # pylint: disable=broad-except
        source.close() if args.source else parser.print_help()
        exit(err)

    try:
        dest = open(args.dest, 'w', encoding=args.encoding) if args.dest else sys.stdout

        writer = csv.DictWriter(dest, YNAB_HEADER)

        if REVOLUT_IDENTIFIER_KEY in reader.fieldnames:
            csv_processor = RevolutCSV(args, reader, writer)
        elif PAYPAL_IDENTIFIER_KEY in reader.fieldnames:
            csv_processor = PaypalCSV(args, reader, writer)
        else:
            raise RuntimeError("Unrecognised file format.")

        csv_processor.Convert()

    finally:
        source.close() if args.source else None
        dest.close() if args.dest else None



if __name__ == '__main__':
    run()