# Dieses Skript liest die von Easybank exportierten .csv-Dateien ein und konvertiert sie zu Ghostfolio-kompatiblen .csv-Dateien

import pandas
import csv

# Datei einlesen
file = input('Name of Input-File (without .csv): ') + ".csv"

# Header und Datenzeile für Ghostfolio.csv anlegen
header = ['Date','Code','DataSource','Currency','Price','Quantity','Action','Fee','accountId']
data = [None,None,'YAHOO','EUR',None,'1','dividend','0.00','3054673f-165a-4302-b340-8133a1667e80']

# Bankauszug (ohne Header) in Dataframe schreiben
raw_df = pandas.read_csv(file, sep=';', header=None)

# Nichtbenötigte Spalten entfernen
df = raw_df.drop(raw_df.columns[[0,3,5]], axis=1)

# Header für Importfile schreiben
with open('ghostfolio_import.csv', 'a', encoding='UTF8', newline='') as f:
        writer = csv.writer(f)
        writer.writerow(header)

# Durch alle Zeilen der Ausgangsdatei durchiterieren und dann Zeile passend in Importfile schreiben
for i in range(df.shape[0]):
    # Aktiencode feststellen
    # Altlasten
    if 'TELENOR' in str((df.iloc[i, 0])):
        data[1] = "TEQ.F"
    elif 'WOODSIDE' in str((df.iloc[i, 0])):
        data[1] = "WOP0.F" 
    elif 'SHELL' in str((df.iloc[i, 0])):
        data[1] = "R6C0.F"
    elif 'HSBC' in str((df.iloc[i, 0])):
        data[1] = "HBC2.F"
    elif 'CIBC' in str((df.iloc[i, 0])):
        data[1] = "CAI.F"
    elif 'SCOTIA' in str((df.iloc[i, 0])):
        data[1] = "BKN.F"
    elif 'BARRATT' in str((df.iloc[i, 0])):
        data[1] = "3BA.F"
    elif 'WOODSIDE' in str((df.iloc[i, 0])):
        data[1] = "WOP0.F"
    elif 'CNOOC' in str((df.iloc[i, 0])):
        data[1] = "NC2B.F"
    elif 'VOEST' in str((df.iloc[i, 0])):
        data[1] = "VAS.F"
    elif 'STARBUCKS' in str((df.iloc[i, 0])):
        data[1] = "SRB.F"
    elif 'SOUTH' in str((df.iloc[i, 0])):
        data[1] = "32Z.F"
    elif 'PHILIPS' in str((df.iloc[i, 0])):
        data[1] = "PHIA.F"                 
    # Portfolio
    elif 'ABBVIE' in str((df.iloc[i, 0])):
        data[1] = "4AB.F"
    elif 'RTX' in str((df.iloc[i, 0])):
        data[1] = "5UR.F"
    elif 'MERCK' in str((df.iloc[i, 0])):
        data[1] = "6MK.F"
    elif 'ORGANON' in str((df.iloc[i, 0])):
        data[1] = "7XP.F"
    elif 'AIRBUS' in str((df.iloc[i, 0])):
        data[1] = "AIR.F"
    elif 'BHP' in str((df.iloc[i, 0])):
        data[1] = "BHP1.F"
    elif 'CATERPILLAR' in str((df.iloc[i, 0])):
        data[1] = "CAT1.F"
    elif 'COCA-COLA' in str((df.iloc[i, 0])):
        data[1] = "CCC3.F"
    elif 'CISCO' in str((df.iloc[i, 0])):
        data[1] = "CIS.F"
    elif 'DEUTSCHE POST' in str((df.iloc[i, 0])):
        data[1] = "DHL.F"
    elif 'JOHNSON + JOHNSON' in str((df.iloc[i, 0])):
        data[1] = "JNJ.F"
    elif 'KRAFT HEINZ' in str((df.iloc[i, 0])):
        data[1] = "KHNZ.F"
    elif 'LEGAL' in str((df.iloc[i, 0])):
        data[1] = "LGI.F"
    elif 'LOCKHEED MARTIN' in str((df.iloc[i, 0])):
        data[1] = "LOM.F"
    elif 'MERCEDES-BENZ' in str((df.iloc[i, 0])):
        data[1] = "MBG.F"
    elif 'MCDONALDS' in str((df.iloc[i, 0])):
        data[1] = "MDO.F"
    elif '3M' in str((df.iloc[i, 0])):
        data[1] = "MMM.F"
    elif 'PFIZER' in str((df.iloc[i, 0])):
        data[1] = "PFE.F"
    elif 'RHEINMETALL' in str((df.iloc[i, 0])):
        data[1] = "RHM.F"
    elif 'DISTRI' in str((df.iloc[i, 0])):
        data[1] = "RYE.F"
    elif 'ROYAL' in str((df.iloc[i, 0])):
        data[1] = "RYE.F"
    elif 'SIEMENS' in str((df.iloc[i, 0])):
        data[1] = "SIE.F"
    elif 'AT + T' in str((df.iloc[i, 0])):
        data[1] = "SOBA.F"
    elif 'UNILEVER' in str((df.iloc[i, 0])):
        data[1] = "UNVA.F"
    elif 'BANK OF CHINA' in str((df.iloc[i, 0])):
        data[1] = "W8V.F"
    else:
        data[1] = "MISSING"
    
    # Datum feststellen
    data[0] = str((df.iloc[i, 1]))
    #Dividende feststellen
    dividend = str(df.iloc[i, 2]).replace('+', '').replace(',', '.')
    data[4] = float(dividend)

    # Daten-Zeile in Import-File schreiben
    with open('ghostfolio_import.csv', 'a', encoding='UTF8', newline='') as f:
        writer = csv.writer(f)
        writer.writerow(data)