ynab-santander-xls.py

"""
A program that takes Mexican Santander's exported file, and outputs a YNAB
compatible CSV file
"""
import pandas as pd

RENT = "Immediate Obligations: Rent 1st"
TECMILENIO = "Immediate Obligations: Tecmilenio 1st"
GAS = "Immediate Obligations: Gas 13th"
AXTEL = "Immediate Obligations: Axtel 14th"
CLEANING = "Immediate Obligations: Cleaning"
CLOTHES_WASH = "Immediate Obligations: Clothes wash"
GROCERIES = "Immediate Obligations: Groceries"
LIGHT = "Immediate Obligations: Light"
SKRITTER = "Immediate Obligations: Skritter Month 14th"
HAIR_CUTS = "Immediate Obligations: Haircuts"
TRANSPORTATION = "True Expenses: Transportation"
EATING_OUT = "Just for Fun: Eating Out"
SODA = "Just for Fun: Soda/Trash Food"
FUN_MONEY = "Just for Fun: Fun Money"

def month_to_number(date_string):
    date = date_string.replace("Ene", "01")
    date = date.replace("Feb", "02")
    date = date.replace("Mar", "03")
    date = date.replace("Abr", "04")
    date = date.replace("May", "05")
    date = date.replace("Jun", "06")
    date = date.replace("Jul", "07")
    date = date.replace("Ago", "08")
    date = date.replace("Sep", "09")
    date = date.replace("Oct", "10")
    date = date.replace("Nov", "11")
    date = date.replace("Dic", "12")

    return date


def create_dict(payee, category="", memo=""):
    return {
        "Payee": payee,
        "Category": category,
        "Memo": memo
    }


def convert_payee(payee):
    if "7 ELEVEN" in payee:
        return create_dict("7eleven", SODA)

    elif "OXXO" in payee:
        return create_dict("Oxxo", SODA)

    elif "EXTRA" in payee:
        return create_dict("Extra", SODA)

    elif "RAPPI" in payee:
        return create_dict("Rappi", SODA)

    elif "CREDITO" in payee:
        return create_dict("Vexi credit card", SODA)

    elif "AY649" in payee:
        return create_dict("Rent", RENT)

    elif "TEC" in payee:
        return create_dict("Tecmilenio", TECMILENIO)

    elif "977100703762RFC" in payee:
        return create_dict("CFE", LIGHT)

    elif "1110542780354" in payee:
        return create_dict("Regio Gas", GAS)

    elif "CO401" in payee:
        return create_dict("Gas", GAS)

    elif "ALIADA" in payee:
        return create_dict("Aliada", CLEANING)

    elif "AXTEL" in payee:
        return create_dict("Axtel", AXTEL)

    elif "PIGNORIS" in payee:
        return create_dict("Clothes wash", CLOTHES_WASH)

    elif "BARBER SHOP" in payee:
        return create_dict("The Barber Shop", HAIR_CUTS)

    elif "SKRITTER" in payee:
        return create_dict("Skritter", SKRITTER)

    elif "SUPERAM" in payee:
        return create_dict("Super", GROCERIES)

    elif "WALMART" in payee:
        return create_dict("Walmart", GROCERIES)

    elif "CASA DE TONO" in payee:
        return create_dict("Casa Tono", EATING_OUT)

    elif "MCCARHTYS" in payee:
        return create_dict("McCarthys", EATING_OUT)

    elif "EL REGRESO" in payee:
        return create_dict("El Regreso", EATING_OUT)

    elif "EL ASADERO" in payee:
        return create_dict("El asadero", EATING_OUT)

    elif "HOOTERS" in payee:
        return create_dict("Hooters", EATING_OUT)

    elif "LA POSTA" in payee:
        return create_dict("La Posta", EATING_OUT)

    elif "NAGAOKA" in payee:
        return create_dict("Japanese food Nagaoka", EATING_OUT)

    elif "HAMBURGUESAS HM" in payee:
        return create_dict("Hamburguesas HM", EATING_OUT)

    elif "HOOKAH" in payee:
        return create_dict("Hookah Lounge", FUN_MONEY)

    elif "UBER" in payee:
        return create_dict("Uber", TRANSPORTATION)

    elif "GRIN" in payee:
        return create_dict("Grin", TRANSPORTATION)

    elif "ATM" in payee:
        return create_dict("ATM Widthrawal")

    elif "AMAZON" in payee:
        return create_dict("Amazon")

    elif "GLOBAL SYSTEMS" in payee:
        return create_dict("Globant")

    return create_dict(payee)


table = pd.read_html("K:\\Downloads\\export.xls",
                     header=3, keep_default_na=False)[0]

results = {
    "Date": [],
    "Payee": [],
    "Category": [],
    "Memo": [],
    "Outflow": [],
    "Inflow": []
}

for row in table.values:
    info = convert_payee(row[3])

    # We don't wanna add dinero creciente transactions
    if "DINERO CRECIENTE" not in info["Payee"]:
        results["Date"].append(month_to_number(row[0]))
        results["Payee"].append(info["Payee"])
        results["Category"].append(info["Category"])
        results["Memo"].append(info["Memo"])
        results["Outflow"].append(row[4])
        results["Inflow"].append(row[5])

# print(results)

dataFrame = pd.DataFrame(results)

dataFrame.to_csv("C:\\Users\\Zyst\\Desktop\\bank.csv",
                 encoding='utf-8',
                 index=False)