Spendenbescheinigungen erstellen mit LaTeX und pandas
Nach Buchungen auswerten mit Python Pandas hier der zweite Artikel zum Thema pandas.
Ausgangspunkt bilden die Formulare der Finanzdirektion, die ich in LaTeX umgesetzt habe; ihr findet sie unter https://github.com/UweZiegenhagen/spendenquittungen-mit-latex.
Wenn man dieses Formular mit entsprechenden Jinja2 Variablen anreichert (dazu später mehr), erhält man Sammelbestaetigung_Geldzuwendung (PDF) (TeX Code)
Die Aufgabe, die sich jetzt für das automatische Befüllen mittels pandas stellt, ist die folgende:
- Lies die Stammdaten ein und bereite sie auf
- Lies die Buchungen (aus Quicken 2015) ein und bereite sie auf
- Befüll das entsprechende LaTeX-Template
- Erzeuge die fertige PDF Datei
Hier der Quellcode nebst Erläuterung:
jinja2
nutzen wir als Template-Engine,os
für die pdflatex-Aufrufe,codecs
für ein wenig UTF8-GewuselcleanPLZ
undprepareAddress
sind einfache Hilfsfunktionen. Die erste reinigt die PLZ (pandas nimmt an, das hier Floats drinstehen), die zweite erzeugt den Adressstring für die Ausgabekardinal
erzeugt das Zahlwort, siehe dazu den verlinkten Beitrag vom Finanzamt.- Richtig los geht es ab „##################### Prepare Stammdaten #####################“:
- Ich lade die Stammdaten in einen Dataframe und ersetzte fehlende Werte durch “
- Die PLZ wird bereinigt (geht sicher auch einfacher), ehemalige Mitglieder (mit Status ‚E‘) werden entfernt
- Die Buchungen werden geladen, fehlende Werte durch 0 bzw. “ ersetzt.
- Relevant sind nur Buchungstypen wie Mitgliedsbeitrag und Aufnahmegebühr, alles andere wird entfernt.
- Die Jinja2 Komponente wird konfiguriert
- Für jeden Stammdatensatz werden die Stammdaten aufbereitet und die Buchungen eingesammelt.
- Ein wenig Python/pandas Magie bereitet die daten auf und schreibt alles in das Template
- Dieses Template wird dann in eine TeX Datei geschrieben und nach PDF übersetzt.
Hier der Link zur Zip-Datei mit allen benötigten Dateien: AlleDateien
Update vom 18.01.2021: Der Python-Code in der Datei hat nicht mehr funktioniert. In der folgenden Datei daher das Code-Update.
import pandas as pd # pandas selbst import jinja2 import os import codecs def cleanPLZ(stringToClean): return stringToClean.replace('.0','') # Eine Funktion, die die Adresse vorbereitet # keine überflüssigen Leerzeichen, wenn Feld nicht gefüllt ist def prepareAddress(id, vorname, name, strasse, plz, ort): address = '' # + str(id) + ': ' if len(vorname)==0: address = address + name else: address = address + vorname + ' ' + name if len(strasse)>0: address = address + ", " + strasse if len(plz)>0: address = address + ", " + plz + ' ' + ort return address # Zerlege die Gesamtsumme in einzelne Bestandteile, um Zahlwort auszugeben # Siehe http://www.steuer-schutzbrief.de/fileadmin/downloads/BMF-Schreiben/BMF-Schreiben-Zuwendungsbestaetigung-2012-08-30.pdf def kardinal(summenstring,separator,indicator): zahlen = {"1" : "Eins", "2":"Zwei", "3":"Drei", "4":"Vier","5":"Fünf","6":"Sechs","7":"Sieben","8":"Acht","9":"Neun","0":"Null"} zahlwort = '' zahl = summenstring.split(',')[0] for i in zahl: zahlwort = zahlwort + zahlen[i]+ separator return indicator + separator + zahlwort + indicator # Diverse Konfigurationsvariablen # http://chrisalbon.com/python/pandas_list_unique_values_in_column.html # Set ipython's max row display pd.set_option('display.max_row', 10000) # Set iPython's max column width to 50 pd.set_option('display.max_columns', 50) # A set number format to 2 digits pd.set_option('display.float_format', lambda x: '%.2f' % x) # http://stackoverflow.com/questions/20625582/how-to-deal-with-this-pandas-warning # komische Fehlermeldung beim Drop von Spalten loswerden pd.options.mode.chained_assignment = None # default='warn' ##################### Prepare Stammdaten ##################### # lade Daten stammdaten = pd.read_excel('Stammdaten.xlsx', 'Tabelle1') # Remove NaN values by " for strings stammdaten['Vorname'].fillna(value='',inplace=True) stammdaten['Name'].fillna(value='',inplace=True) stammdaten['Adresszusatz'].fillna(value='',inplace=True) stammdaten['Strasse'].fillna(value='',inplace=True) stammdaten['PLZ'].fillna(value='',inplace=True) stammdaten['Ort'].fillna(value='',inplace=True) stammdaten['eMail'].fillna(value='',inplace=True) stammdaten['Mitgliedsart'].fillna(value='',inplace=True) # convert PLZ to string # apply str function first, then run cleanPLZ on the string stammdaten['PLZ']= stammdaten.PLZ.apply(str) stammdaten['PLZ']= stammdaten.PLZ.apply(cleanPLZ) # entferne ehemalige Mitglieder stammdaten = stammdaten[stammdaten.Mitgliedsart != 'E'] ################################ Prepare Buchungen # lade die Buchungen buchungen = pd.read_excel('Buchungen.xlsx', 'Tabelle1') buchungen[['Klasse']] = buchungen[['Klasse']].astype(str) buchungen[['Betrag']] = buchungen[['Betrag']].astype(float) # Change format of 'Buchungstag' to datetime buchungen['Buchungstag'] = pd.to_datetime(buchungen['Buchungstag'],dayfirst=True) # Remove NaN values by " for strings or 0 for numbers buchungen['Vorgang'].fillna(value=0,inplace=True) buchungen['Empfänger'].fillna(value='',inplace=True) buchungen['Verwendungszweck'].fillna(value='',inplace=True) buchungen['Kategorie'].fillna(value='',inplace=True) buchungen['Klasse'].fillna(value='',inplace=True) buchungen['Relevant']=True buchungen.Kategorie.str.match('^Aufnahmegebühr|Zweckspende|Mitgliedsbeitrag|Spende$') # entferne irrelevante Buchungen buchungen = buchungen[buchungen.Relevant != False] # http://stackoverflow.com/questions/20937538/how-to-display-pandas-dataframe-using-a-format-string-for-columns #pd.options.display.float_format = '{:,.2f} EUR'.format class CommaFloatFormatter: def __mod__(self, x): return str(x).replace('.',',') latex_jinja_env = jinja2.Environment( block_start_string = '\BLOCK{', block_end_string = '}', variable_start_string = '\VAR{', variable_end_string = '}', comment_start_string = '\#{', comment_end_string = '}', line_statement_prefix = '%-', line_comment_prefix = '%#', trim_blocks = True, autoescape = False, loader = jinja2.FileSystemLoader(os.path.abspath('.')) ) # Laden des Templates aus einer Datei template = latex_jinja_env.get_template('Sammelbestaetigung_Geldzuwendung.tex') for index, row in stammdaten.iterrows(): print("ID:",row["ID"]) address = prepareAddress(row["ID"],row['Vorname'],row['Name'],row['Strasse'],row['PLZ'],row['Ort']) print(address) beitraege = buchungen[buchungen.Klasse.str.contains('^' + str(row["ID"]) + '$')] beitraege.drop('Klasse',axis=1,inplace=True) beitraege.drop('Verwendungszweck',axis=1,inplace=True) beitraege.drop('Relevant',axis=1,inplace=True) beitraege.drop('Empfänger',axis=1,inplace=True) beitraege.drop('Konto',axis=1,inplace=True) beitraege.drop('Vorgang',axis=1,inplace=True) gesamtsumme = beitraege.sum()[0] beitraege['Buchungstag'] = beitraege['Buchungstag'].apply(lambda x: x.strftime('%d-%m-%Y')) texbuchungen = beitraege.applymap(lambda x: str(x).replace('.',',0')).to_latex(index=False) texbuchungen = beitraege.to_latex(index=False) summe = str(gesamtsumme).replace('.',',0') + ' EUR' # kardinal(summe,'-','xxx') dokument = template.render(Spender=address, ID=row['ID'],Summe=summe,kardinal=kardinal(summe,'-','xxx'),Buchungen=texbuchungen) with codecs.open(''+str(row['ID']) + ".tex", "w","utf-8") as letter: letter.write(dokument); letter.close(); os.system("pdflatex -interaction=batchmode " + str(row['ID']) + ".tex") os.system("del *.log") os.system("del *.aux") |