Hamburger Hamburger

python Library

familienKonto

Filtert die Umsätze eines CSV-Kontoauszugs an Hand von Schlüsselworten und erzeugt daraus eine doppelte Buchführung, die sich in Excel importieren lässt.

Die Schlüsselworte werden mittels catMarker.json formatiert:

{
    "Wohnen":[
	"Strom",
	"WASSER",
	"Hausratversicherung"
    ],
    "Leben":[
	"tegut",
	"ALDI"
	],
    "Luxus":[  ],
    "Einkommen":[  ]
	}
#import  csv
import  tkinter as  tk
from tkinter import  filedialog
import  pandas
import  json

"""
1. show all transactions in your browser, export them to a CSV file
2. Open CSV by Excel, 
   check: the first 12 lines are header
3. Filter all transactions to the structure 'account' by:
   python familienKonto.py
"""


# ============================================ #
def  readCSV(fileName, headLines=12, delimiter=";"):
    print("Reading: ",fileName)
    print("Dropping first "+str(headLines)+" lines")
    #accountRaw = pandas.read_csv(fileName, sep=delimiter, skiprows=headLines, encoding='ANSI')
    accountRaw = pandas.read_csv(fileName, sep=delimiter, skiprows=headLines, encoding='latin1')
        
    #check names of columns and find sender and receiver
    colNames = list(accountRaw.keys())
    for  i,cn in enumerate(colNames):
        if cn.find("uftraggeber")>0:
            AuftraggeberMarker = cn
        if cn.find("Zahlungspflichtiger")>0:
            ZahlungspflichtigerMarker = cn


    prepos = ZahlungspflichtigerMarker
    #prepos = AuftraggeberMarker

    try:
        foo = accountRaw[prepos][0]
    except:
        print("prepos is:",prepos)
        print("account keys are:",colNames)
        exit()
        
    #process all lines of data   
    NoData = len(accountRaw[colNames[0]])
    account = []
    for  n in range(NoData):
        thisValue = accountRaw['Umsatz'][n]
        thisSign  = accountRaw[' '][n] #the sign got a blank identifier
        thisDate  = accountRaw['Buchungstag'][n]
        if pandas.isna( accountRaw[prepos][n] ):
            #detect missing values and drop line
            print("drop line:", accountRaw['Vorgang/Verwendungszweck'][n], thisValue)
            continue #drop this line
        else:
            #adapt number format German to international, drop thousends delimter, replace decimal sign
            thisValue = thisValue.replace(".", "")
            thisValue = thisValue.replace(",", ".")
            #pick relevant fields
            thisAccount={}
            thisAccount["date"] = thisDate
            thisAccount["month"] = thisDate[3:5]
            thisDesc = str( accountRaw[prepos][n] )+"\t"
            thisDesc = thisDesc + accountRaw['Vorgang/Verwendungszweck'][n].replace("\n", "")
     
            if thisSign=="S":
                thisAccount["value"] = -1.0*float(thisValue)
            else:
                thisAccount["value"] = +1.0*float(thisValue)
                
            thisAccount["desc"] = thisDesc
            
            #put fields of this accounting entry to list of all
            account.append(thisAccount)

    return account

# ============================================ #
def  cathegorizeTransactions(account):
    """
    Cathegorize the transactions in parts with catNames.
    For each cathegory there may be multipe catMarkers.
    Transactions that cannot be cathegorized are printed to extend the filters.
    
    Sorting strings are imported from 'catMarker.json'
    """

    #first read markers for  cathegories
    with open("catMarker.json") as   json_file:
        catMarker = json.load(json_file)
    catNames = catMarker.keys()

    print(" ###########################")

    #check for  each accounting entry, in which cathegory = virtaul account it matches
    cathegories = []
    for  i,a in enumerate(account):
        thisDesc = "_"+a["desc"]
        cathegories.append( {} )
        catsFound=0
        for  cN in catNames:
            cathegories[i][cN]=0
            for  cM in catMarker[cN]:
                if thisDesc.find(cM)>0:
                    catsFound = catsFound+1
                    value = a["value"]
                    cathegories[i][cN] = -1*value

        #print entries that can not be matched yet
        if catsFound==0:
            #cathegories[i]["unklar"]=1
            print(thisDesc)

    #print(cathegories)
    return cathegories

# ============================================ #
def  exportFilteredData(account, cathegories, fileName="familienFinanzen.txt"):
    """
    Export all transactions to a new TXT file,
    acomplished by the cathegorie indicators.
    You may want to import  this to Excel again
    (Try: Refresh all Data) and convert the values.
    Then copy the values only to the balance and add
    manual changes as  required.
    for  each account, the cathegories order the value 
    to a specific person 
    """
    
    fp = open(fileName, 'w', encoding='utf-8')
    aKeys = account[0].keys()
    try:
        cKeys = cathegories[0].keys()
    except:
        print(cathegories)
        exit()
        
    thisTrans = ""
    for  a in aKeys:
        thisTrans = thisTrans+str(a)+"\t"
    for  c in cKeys:
        thisTrans = thisTrans+c+"\t"
    fp.write(thisTrans+"\n")
    
    NoData = len(account)
    for  d in range(NoData):
        thisA = account[d]
        thisC = cathegories[d]
        splitArne2Daniela = False
        if thisA["desc"].find("undesagentur fuer")>0:
            splitArne2Daniela = True
            
        if splitArne2Daniela:
            thisC["Arne"] = -0.5*thisA["value"]
            thisC["Dani"] = 0 
            thisTrans =  account2txt(thisA,thisC)
            fp.write(thisTrans+"\n")
            thisA["desc"] = "split"
            thisC["Dani"] = -0.5*thisA["value"]
            thisC["Arne"] = 0
            thisA["value"] = 0
            thisTrans =  account2txt(thisA,thisC)
            fp.write(thisTrans+"\n")
        else:
            thisTrans =  account2txt(thisA,thisC)
            fp.write(thisTrans+"\n")
            
    fp.close()
    print("Filtered data written to: ",fileName)

# ============================================ #
def  account2txt(account,cathegories):
    """ convert one account plus its cathegories to a CSV line """
    cKeys = cathegories.keys()
    thisTrans = ""
    thisTrans = thisTrans+str(account["date"])+"\t"
    thisTrans = thisTrans+str(account["month"])+"\t"
    thisTrans = thisTrans + '{0:1.2f}'.format(account["value"]) + "\t"
    desc = account["desc"].replace("\t", "_")
    thisTrans = thisTrans + desc + "\t"
    
    for  c in cKeys:
        thisTrans = thisTrans + str(cathegories[c])+"\t"
        
    #replace decimal sign to operate Excel on German system
    #thisTrans.replace(".", ",")
        
    return thisTrans


# ============================================ #
def  browse_file(file_label, ArnesMain_button ):
    file_path = filedialog.askopenfilename(filetypes=[("CSV files", "*.csv")])
    if file_path:
        file_label.config(text="Selected file: " + file_path)
        ArnesMain_button.config(state=tk.NORMAL)
        ArnesMain(file_path)

# ============================================ #
def  runGUI():
    # Create the main window
    root = tk.Tk()
    root.title("CSV File Selector")

    ArnesMain_button = tk.Button(root, text="", state=tk.DISABLED)

    # Create widgets
    file_label = tk.Label(root, text="No file selected")
    browse_button = tk.Button(root, text="Browse", command=browse_file(file_label, ArnesMain_button ) )


    # Place widgets in the window
    file_label.pack(pady=10)
    browse_button.pack(pady=10)
    ArnesMain_button.pack(pady=10)

    # Start the GUI event loop
    #root.mainloop()

    root.destroy()  # Close the Tkinter window

# ============================================ #
def  ArnesMain( fileName ):
    #read accounts from CSV
    if isinstance( fileName, list) and len(fileName)>0:
        print(fileName)
        print("Read set of CSV files")
        exit()
        account = []
        for  f in fileName:
            print("reading:",f )
            thisA = readCSV( f )
            account.append( thisA )
    else:
        account = readCSV( fileName )
    
    #sort entries to real and virtual accounts
    cathegories = cathegorizeTransactions(account)
        
    #export filtered data to TXT
    exportFilteredData(account,cathegories)
    
# ============================================ #    
if __name__ == "__main__":

    fileName = []
    #fileName.append( "Umsaetze_DE69672300004119675601_2024.03.01.csv" )
    
    if len( fileName )>0:
        ArnesMain( fileName )
        
    else:
        try:
            runGUI()
        
        except:
            ArnesMain( fileName )

Index of Library

1ArnesKonto.py
2CIEcolorCoordinates_Spectrum.py
3MatlabStructures.py
4ModelicaExecution.py
5ModelicaMatFilter.py
6OperateAllFiles.py
7dictionaryStatistics.py
8familienKonto.py
9makeDoc.py
10plotTimeSeries.py
11readData2DictOfArrays.py
12replaceInFile.py
13showPointOnCIExy.py
14svg2pdf.py
15testNumpyMatrix.py
16writeDictOfArrays.py
17writeTSV.py

Der gesamte Sourcecode darf gemäß GNU General Public License weiterverbreitet werden.