paffrater2ofx.py

#!/usr/bin/env python
# encoding: utf-8
"""
paffrater.py - Dieses Progrtamm liesst Volksbank Kontoauszüge und konvertiert sie in OFX.
OFX wurde mit xero.com getestet.

Created by Maximillian Dornseif on 2010-06-05.
Copyright (c) 2010, 2013 HUDORA. All rights reserved.
"""


import datetime
import hashlib
#import optparse
import os
import re
import sys
import time
import xml.etree.ElementTree as ET


def write_ofx(account, vorgaenge, inputname):
    transaction_guid = '%s-%s' % (account, inputname)
    root = ET.Element('OFX')
    signonmsgsrsv1 = ET.SubElement(root, 'SIGNONMSGSRSV1')
    sonrs = ET.SubElement(signonmsgsrsv1, 'SONRS')
    status = ET.SubElement(signonmsgsrsv1, 'STATUS')
    ET.SubElement(status, 'CODE').text = '0'
    ET.SubElement(status, 'SEVERITY').text = 'INFO'
    ET.SubElement(sonrs, 'DTSERVER').text =  datetime.datetime.now().strftime('%Y%m%d')
    ET.SubElement(sonrs, 'LANGUAGE').text = 'ENG'

    bankmsgsrsv1 = ET.SubElement(root, 'BANKMSGSRSV1')
    stmttrnrs = ET.SubElement(bankmsgsrsv1, 'STMTTRNRS')
    ET.SubElement(stmttrnrs, 'TRNUID').text = transaction_guid
    status = ET.SubElement(stmttrnrs, 'STATUS')
    ET.SubElement(status, 'CODE').text = '0'
    ET.SubElement(status, 'SEVERITY').text = 'INFO'

    stmtrs = ET.SubElement(stmttrnrs, 'STMTRS')
    ET.SubElement(stmtrs, 'CURDEF').text = 'EUR'
    bankacctfrom = ET.SubElement(stmtrs, 'BANKACCTFROM')
    ET.SubElement(bankacctfrom, 'BANKID').text = account.split('/')[0]
    ET.SubElement(bankacctfrom, 'ACCTID').text = account.split('/')[-1]
    ET.SubElement(bankacctfrom, 'ACCTTYPE').text = 'CHECKING'
    banktranlist = ET.SubElement(stmtrs, 'BANKTRANLIST')
    deduper = set()
    for line in sorted(vorgaenge, reverse=True):
        sortkey, amount, date, absender, guid, bookingcode, verwendungszweck, quellblz, quellkonto, description = line
        if guid in deduper:
            continue
        deduper.add(guid)
        if absender.startswith('EC-POS EMV  '):
            # Euro Lastschrift umsortieren
            # EUR 105,68KURS1,0000000 KURS VOM 01.01.99 MAFD RAEREN AM26.03.13 11.13 V.PHARMA 32 BEL
            m = re.match(r'.*KURS VOM 01\.01\.99 MAFD (.*) AM[0-9. ]+(.*)', verwendungszweck)
            if m:
                absender = "%s %s (%s)" % (m.group(2), m.group(1), verwendungszweck)
        stmttrn = ET.SubElement(banktranlist, 'STMTTRN')
        ET.SubElement(stmttrn, 'TRNTYPE').text = 'CREDIT'  # CREDIT DEBIT
        # DtPosted Date item was posted, datetime
        ET.SubElement(stmttrn, 'DTPOSTED').text = "%s" % date
        # Amount, mit '.' getrennt
        ET.SubElement(stmttrn, 'TRNAMT').text = unicode(amount)
        # That is, the <FITID> value must be unique within the account and Financial Institution
        # (independent of the service provider).
        ET.SubElement(stmttrn, 'FITID').text = guid.replace('*', '.')
        verwendungszweck = verwendungszweck.strip()
        # extract references like WL0000000 SFYX0000
        checknum = ''
        m = re.search(r'(WL\d\d\d\d\d\d\d\d?|SFYX\d\d\d\d)', verwendungszweck)
        if m:
            checknum = m.group(0)
            # reference/Check number, A-12
            ET.SubElement(stmttrn, 'CHECKNUM').text = checknum
        # PAYEE
        ET.SubElement(stmttrn, 'NAME').text = absender.strip()
        # Format: A-255 for <MEMO>, used in V1 message sets A <MEMO> provides additional information
        # about a transaction.
        ET.SubElement(stmttrn, 'MEMO').text = (' '.join([verwendungszweck, description, checknum]))[:254].strip()

    header = """OFXHEADER:100
DATA:OFXSGML
VERSION:102
SECURITY:NONE
ENCODING:UNICODE
CHARSET:UTF-8
COMPRESSION:NONE
OLDFILEUID:NONE
NEWFILEUID:NONE

"""
    body = ET.tostring(root, encoding='utf-8')
    fname = 'auszug_%s_%s.ofx' % (datetime.date.today(), account.replace('/','.'))
    print "writing %s" % fname
    fd = open(fname, 'w')
    fd.write(header)
    fd.write(body)
    fd.close()
    return header, body


import csv

def unicode_csv_reader(unicode_csv_data, dialect=csv.excel, **kwargs):
    # csv.py doesn't do Unicode; encode temporarily as UTF-8:
    csv_reader = csv.reader(unicode_csv_data,
                            dialect=dialect, **kwargs)
    for row in csv_reader:
        # decode ISO-8859 back to Unicode, cell by cell:
        yield [unicode(cell, 'ISO-8859-1') for cell in row]


if __name__ == '__main__':
    # for fname in sys.argv[1:]:
    with open("/Users/md/Downloads/alleUmsaetzeKTO3401425011_20131208.csv") as csvfile:
        spamreader = unicode_csv_reader(csvfile, delimiter=';', quotechar='"')
        vorgaenge = []
        for row in spamreader:
            if row[0] == 'Kontonummer':
                continue
            Kontonummer, Buchungstag, Wertstellung, partner, Buchungstext, VWZ1,VWZ2,VWZ3,VWZ4,VWZ5,VWZ6,VWZ7,VWZ8,VWZ9,VWZ10,VWZ11,VWZ12,VWZ13,VWZ14, Betrag,Kontostand,Waehrung = row
            # sortkey, amount, date, absender, guid, bookingcode, verwendungszweck, quellblz, quellkonto, description = line
            verwendugszweck = ' '.join([VWZ4,VWZ3,VWZ5,VWZ6,VWZ7,VWZ8,VWZ9,VWZ10,VWZ11,VWZ12,VWZ13,VWZ14])
            partner = ' '.join([VWZ1,VWZ2])
            if partner.startswith('Nicole'):
                partner = ' '.join([VWZ1,VWZ2])
                verwendugszweck = ' '.join([VWZ4,VWZ3,VWZ5,VWZ6,VWZ7,VWZ8,VWZ9,VWZ10,VWZ11,VWZ12,VWZ13,VWZ14])

            guid = hashlib.md5(repr(row)).hexdigest()
            day, month, year = Wertstellung.split('.') # 19.10.2013
            date = "%s%s%s" % (year, month, day)
            vorgaenge.append((
                "sortkey",
                Betrag.replace(',', '.'),
                date,
                partner,
                guid,
                "bookingcode",
                Buchungstext,
                "quellblz",
                "quellkonto",
                verwendugszweck
                ))
    write_ofx("0/3401425011", vorgaenge, "/Users/md/Downloads/alleUmsaetzeKTO3401425011_20131208.csv")