itau-to-notion.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "2277ad84-132b-4514-bcaa-4cdf1ff04968",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The number of worksheets is 1\n",
      "Worksheet name(s): ['Lançamentos']\n",
      "Lançamentos 112 4\n"
     ]
    }
   ],
   "source": [
    "import re\n",
    "import xlrd\n",
    "\n",
    "STATEMENT_FILE = 'Fatura-Excel.xls'\n",
    "\n",
    "book = xlrd.open_workbook(STATEMENT_FILE)\n",
    "\n",
    "print(\"The number of worksheets is {0}\".format(book.nsheets))\n",
    "print(\"Worksheet name(s): {0}\".format(book.sheet_names()))\n",
    "sh = book.sheet_by_index(0)\n",
    "print(\"{0} {1} {2}\".format(sh.name, sh.nrows, sh.ncols))\n",
    "# for rx in range(sh.nrows):\n",
    "#     print(sh.row(rx))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "a1bdc669-c7d5-4fb6-9635-c83e335e737e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Defined Cell types\n",
    "XL_CELL_EMPTY = 0\n",
    "XL_CELL_TEXT = 1\n",
    "XL_CELL_NUMBER = 2\n",
    "\n",
    "# Column info location\n",
    "DATE_COLUMN = 0\n",
    "TEXT_COLUMN = 1\n",
    "VALUE_COLUMN = 3\n",
    "\n",
    "# Regex for identifying date dd/mm/yyyy\n",
    "date_regex = re.compile(r'^([0-2][0-9]|(3)[0-1])(\\/)(((0)[0-9])|((1)[0-2]))(\\/)\\d{4}$')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "c94d6c2c-3499-4a53-9593-6ad8d4c5621c",
   "metadata": {},
   "outputs": [],
   "source": [
    "people = ['VITOR HENRIQUE DE MORAES E', 'LARA JEVEAUX ALVES']\n",
    "\n",
    "expenses = {key: list([]) for key in people}\n",
    "\n",
    "# learning moment:\n",
    "# expenses = dict.fromkeys(people, [])\n",
    "# when supplying a mutable object as default value, a deep copy is made. Therefore, appending values\n",
    "# in the original list will take places in all the values of keys. In this case they were duplicated \n",
    "\n",
    "def formatExpenseDict(date, text, value):\n",
    "    return {'date': date, 'text': text, 'value': value} \n",
    "\n",
    "# Scan all rows and group expenses by person\n",
    "current_person = ''\n",
    "for rx in range(sh.nrows):\n",
    "    first_cell = sh.cell_value(rowx=rx, colx=0)\n",
    "    if any(person in first_cell for person in people):\n",
    "        current_person = first_cell.rsplit(' -')[0]\n",
    "        continue;\n",
    "    \n",
    "    if (current_person == ''):\n",
    "        continue;\n",
    "    \n",
    "    first_cell_type = sh.cell_type(rowx=rx, colx=DATE_COLUMN)\n",
    "    if (first_cell_type == XL_CELL_TEXT and date_regex.search(first_cell) != None):\n",
    "        # group expenses when row is valid\n",
    "        date_cell = first_cell\n",
    "        text_cell = sh.cell_value(rowx=rx, colx=TEXT_COLUMN)\n",
    "        value_cell = sh.cell_value(rowx=rx, colx=VALUE_COLUMN)\n",
    "        \n",
    "        expense = formatExpenseDict(date_cell, text_cell, value_cell)\n",
    "        expenses[current_person].append(expense)\n",
    "        # print(current_person, expense)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "f8093ce2-15c7-4f28-af53-43d29fa0c4b1",
   "metadata": {},
   "outputs": [],
   "source": [
    "from datetime import date\n",
    "\n",
    "# REMOVING EXPENSES BEFORE SELECTED DATE\n",
    "\n",
    "IGNORE_EXPENSES_BEFORE_DATE = '12/08/2022'\n",
    "\n",
    "def transform_date(str_date = '31/12/2021'):\n",
    "    day, month, year = str_date.rsplit('/')\n",
    "    return date(int(year), int(month), int(day))\n",
    "\n",
    "last_date = transform_date(IGNORE_EXPENSES_BEFORE_DATE)\n",
    "\n",
    "for person, expenses_list in expenses.items():\n",
    "    indexes_to_remove = []\n",
    "    for idx, expense in enumerate(expenses_list):\n",
    "        current_date = transform_date(expense['date'])\n",
    "        if (current_date < last_date):\n",
    "            indexes_to_remove.append(idx)\n",
    "    \n",
    "    # the removal operation had to be separated because it wasn't fully functional inside the loop above\n",
    "    # it is reversed in order to not mess up with the indexes\n",
    "    for jdx in reversed(indexes_to_remove):\n",
    "        expenses[person].pop(jdx)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "e64dd2cd-ece5-42c4-8a40-e76e80f89956",
   "metadata": {},
   "outputs": [],
   "source": [
    "# CLEAR REFUNDS DISGUISED AS EXPENSES\n",
    "for person, expenses_list in expenses.items():\n",
    "    for idx, expense in enumerate(expenses_list):\n",
    "         if (expense['value'] < 0):\n",
    "            refund = expense\n",
    "            # searches refunded expense retroactively, expeting it to be near and above\n",
    "            for jdx in reversed(range(idx)):\n",
    "                if (abs(expense['value']) == abs(expenses_list[jdx]['value'])):\n",
    "                    refundedExpense = expenses_list[jdx]\n",
    "                    expenses[person].remove(refund) \n",
    "                    expenses[person].remove(refundedExpense)\n",
    "                    break"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "e3d09812-3f09-4b1f-a9e2-03fde45a8a23",
   "metadata": {},
   "outputs": [],
   "source": [
    "investiments = ['monis']\n",
    "\n",
    "# REMOVE INVESTIMENTS FROM EXPENSES\n",
    "for person, expenses_list in expenses.items():\n",
    "    for idx, expense in enumerate(expenses_list):\n",
    "        if any(investiment in expense['text'].lower() for investiment in investiments):\n",
    "            expenses[person].pop(idx)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "f696c61a-d6c3-4284-99aa-af746c32194d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0 VITOR HENRIQUE DE MORAES E {'date': '10/01/2023', 'text': 'Shop Cao Del-ct Y 02/02', 'value': 55.9}\n",
      "1 VITOR HENRIQUE DE MORAES E {'date': '11/01/2023', 'text': 'Mk Moda Infa-ct  L02/04', 'value': 99.69}\n",
      "2 VITOR HENRIQUE DE MORAES E {'date': '11/01/2023', 'text': 'Bm Import At-ct O 02/02', 'value': 125.5}\n",
      "3 VITOR HENRIQUE DE MORAES E {'date': '21/01/2023', 'text': 'Decathlon Vi-ct El02/04', 'value': 101.25}\n",
      "4 VITOR HENRIQUE DE MORAES E {'date': '30/01/2023', 'text': 'Vila Velha Boulevard S', 'value': 99.9}\n",
      "5 VITOR HENRIQUE DE MORAES E {'date': '30/01/2023', 'text': 'Extrabom Boulevard', 'value': 156.43}\n",
      "6 VITOR HENRIQUE DE MORAES E {'date': '30/01/2023', 'text': 'Precolandia -ct', 'value': 79.6}\n",
      "7 VITOR HENRIQUE DE MORAES E {'date': '31/01/2023', 'text': 'Anuidade Diferenci06/12', 'value': 54.16}\n",
      "8 VITOR HENRIQUE DE MORAES E {'date': '03/02/2023', 'text': 'Tribbu Fit F-ct', 'value': 28.0}\n",
      "9 VITOR HENRIQUE DE MORAES E {'date': '03/02/2023', 'text': 'Pag*88beertr-ct', 'value': 69.5}\n",
      "10 VITOR HENRIQUE DE MORAES E {'date': '04/02/2023', 'text': 'Extrabom Bou-ct Rd', 'value': 398.03}\n",
      "11 VITOR HENRIQUE DE MORAES E {'date': '05/02/2023', 'text': 'Pag*suelynbe-ct Viana', 'value': 15.0}\n",
      "12 VITOR HENRIQUE DE MORAES E {'date': '09/02/2023', 'text': 'Pag*rnfastfo-ct', 'value': 36.0}\n",
      "13 VITOR HENRIQUE DE MORAES E {'date': '11/02/2023', 'text': 'Wendel Fialh-ct  Silv', 'value': 47.5}\n",
      "14 VITOR HENRIQUE DE MORAES E {'date': '11/02/2023', 'text': 'Pag*veraluci-ct Ger', 'value': 12.8}\n",
      "15 VITOR HENRIQUE DE MORAES E {'date': '11/02/2023', 'text': 'Nutrivita Pr-ct Sament', 'value': 36.66}\n",
      "16 VITOR HENRIQUE DE MORAES E {'date': '12/02/2023', 'text': 'Extrabom Bou-ct Rd', 'value': 122.38}\n",
      "17 VITOR HENRIQUE DE MORAES E {'date': '12/02/2023', 'text': 'Pag*acaidosp-ct S', 'value': 25.0}\n",
      "18 VITOR HENRIQUE DE MORAES E {'date': '12/02/2023', 'text': 'Ilha Do Cara-ct Jo Far', 'value': 221.76}\n",
      "19 VITOR HENRIQUE DE MORAES E {'date': '12/02/2023', 'text': 'Los Neto Ros-ct R 01/02', 'value': 60.0}\n",
      "20 VITOR HENRIQUE DE MORAES E {'date': '13/02/2023', 'text': 'Supermercado-ct', 'value': 47.57}\n",
      "21 VITOR HENRIQUE DE MORAES E {'date': '18/02/2023', 'text': 'Administradora Geral D', 'value': 10.5}\n",
      "22 VITOR HENRIQUE DE MORAES E {'date': '25/11/2022', 'text': 'Ww Now      *4087304/10', 'value': 400.42}\n",
      "23 VITOR HENRIQUE DE MORAES E {'date': '25/11/2022', 'text': 'Amazon Marketplace04/10', 'value': 106.07}\n",
      "24 VITOR HENRIQUE DE MORAES E {'date': '26/11/2022', 'text': 'Amazon Marketplace04/04', 'value': 50.22}\n",
      "25 VITOR HENRIQUE DE MORAES E {'date': '05/12/2022', 'text': 'Parc=103doterra   03/03', 'value': 357.34}\n",
      "26 VITOR HENRIQUE DE MORAES E {'date': '09/12/2022', 'text': 'Pg *plumerya      03/04', 'value': 153.45}\n",
      "27 VITOR HENRIQUE DE MORAES E {'date': '09/12/2022', 'text': 'C6pay  *jbochnerjo03/04', 'value': 245.0}\n",
      "28 VITOR HENRIQUE DE MORAES E {'date': '18/12/2022', 'text': 'Amazon Br         03/03', 'value': 58.01}\n",
      "29 VITOR HENRIQUE DE MORAES E {'date': '19/12/2022', 'text': 'Cielle Design Eir 03/03', 'value': 296.66}\n",
      "30 VITOR HENRIQUE DE MORAES E {'date': '21/12/2022', 'text': 'Pag*lavoro        03/05', 'value': 377.33}\n",
      "31 VITOR HENRIQUE DE MORAES E {'date': '02/01/2023', 'text': 'Amazon Marketplace', 'value': -0.04}\n",
      "32 VITOR HENRIQUE DE MORAES E {'date': '02/01/2023', 'text': 'Amazon Marketplace02/03', 'value': 56.48}\n",
      "33 VITOR HENRIQUE DE MORAES E {'date': '09/01/2023', 'text': 'Parc=103mercadopag02/03', 'value': 48.93}\n",
      "34 VITOR HENRIQUE DE MORAES E {'date': '09/01/2023', 'text': 'Parc=103mercadopago*mer', 'value': -0.02}\n",
      "35 VITOR HENRIQUE DE MORAES E {'date': '30/01/2023', 'text': 'Ubersmi*uber Smiles C', 'value': 80.0}\n",
      "36 VITOR HENRIQUE DE MORAES E {'date': '01/02/2023', 'text': 'Ifood  *ifd*saibert F', 'value': 58.0}\n",
      "37 VITOR HENRIQUE DE MORAES E {'date': '03/02/2023', 'text': 'Smiles Fidel*comprademi', 'value': 100.03}\n",
      "38 VITOR HENRIQUE DE MORAES E {'date': '05/02/2023', 'text': 'Milhas*luc*ml     01/12', 'value': 149.0}\n",
      "39 VITOR HENRIQUE DE MORAES E {'date': '09/02/2023', 'text': 'Ifood  *ifd*gunma Sus', 'value': 94.4}\n",
      "40 VITOR HENRIQUE DE MORAES E {'date': '10/02/2023', 'text': 'Amazon.com.br', 'value': 32.1}\n",
      "41 VITOR HENRIQUE DE MORAES E {'date': '11/02/2023', 'text': 'Mercadopago*distribuido', 'value': 77.07}\n",
      "42 VITOR HENRIQUE DE MORAES E {'date': '11/02/2023', 'text': 'Appmax*gla*24198', 'value': 39.9}\n",
      "43 VITOR HENRIQUE DE MORAES E {'date': '11/02/2023', 'text': 'Appmax*gla*24198', 'value': 97.0}\n",
      "44 VITOR HENRIQUE DE MORAES E {'date': '12/02/2023', 'text': 'Farm Rio Ecomm    01/03', 'value': 360.06}\n",
      "45 VITOR HENRIQUE DE MORAES E {'date': '13/02/2023', 'text': 'Conta Vivo', 'value': 23.0}\n",
      "46 VITOR HENRIQUE DE MORAES E {'date': '13/02/2023', 'text': 'Parc=103smiles Fid01/03', 'value': 593.3}\n",
      "47 VITOR HENRIQUE DE MORAES E {'date': '24/02/2023', 'text': 'Spotify', 'value': 24.9}\n"
     ]
    }
   ],
   "source": [
    "for person, expenses_list in expenses.items():\n",
    "    for idx, expense in enumerate(expenses_list):\n",
    "        print(idx, person, expense)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a3c2c68e-3546-4521-8066-098d5c731ec1",
   "metadata": {},
   "source": [
    "## Convert to CSV, for Notion\n",
    "\n",
    "Now that expenses are parsed, the last step involves:\n",
    "- Translating texts for a more semantic representation\n",
    "- Atributing the remaining csv fields, such as categories and transfers\n",
    "- Then joining the expenses to generate the csv file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "eb471d0e-687f-47cc-9b1a-a9d3f1d864d8",
   "metadata": {},
   "outputs": [],
   "source": [
    "categories = {'transporte': 'Transporte',\n",
    "              'viagens': 'Viagens',\n",
    "              'pessoal': 'Pessoal',\n",
    "              'alimentacao': 'Alimentação',\n",
    "              'lazer': 'Lazer',\n",
    "              'casa': 'Casa',\n",
    "              'saude':'Saúde',\n",
    "              'juju': 'Juju',\n",
    "              'streaming': 'Streaming',\n",
    "              'lanches': 'Lanches',\n",
    "              'bar': 'Bar/Bebida'\n",
    "             }\n",
    "\n",
    "translate_dict = {'99*99': {'text': '99 Pay', 'category': categories['casa']},\n",
    "                'vivo': {'text': 'Vivo', 'category': categories['pessoal']},\n",
    "                'tbra': {'text': 'Vivo', 'category': categories['pessoal']},\n",
    "                'uber': {'text': 'Uber', 'category': categories['transporte']},\n",
    "                'posto ilha': {'text': 'Gasolina', 'category': categories['transporte']},\n",
    "                'shellbox': {'text': 'Gasolina', 'category': categories['transporte']},\n",
    "                'esfiha': {'text': 'Esfiha', 'category': categories['lanches']},\n",
    "                'bakery': {'text': 'Padaria', 'category': categories['alimentacao']},\n",
    "                'cr comercial': {'text': 'Padaria', 'category': categories['alimentacao']},\n",
    "                'pastel': {'text': 'Pastel', 'category': categories['lanches']},\n",
    "                'ifood': {'text': 'Ifood', 'category': categories['alimentacao']},\n",
    "                'carone': {'text': 'Supermercado', 'category': categories['alimentacao']},\n",
    "                'extrabom': {'text': 'Supermercado', 'category': categories['alimentacao']},\n",
    "                'supermercado-ct im': {'text': 'Supermercado', 'category': categories['alimentacao']},\n",
    "                'embutidos lo': {'text': 'Queijo Feira', 'category': categories['alimentacao']},\n",
    "                '40 sab': {'text': 'Sorvete', 'category': categories['lanches']},\n",
    "                'clubew': {'text': 'Wine', 'category': categories['lazer']},\n",
    "                'spotify': {'text': 'Spotify', 'category': categories['streaming']},\n",
    "                'farma': {'text': 'Farmácia', 'category': categories['saude']},\n",
    "                'drogaria': {'text': 'Farmácia', 'category': categories['saude']},\n",
    "                'petz': {'text': 'Juju', 'category': categories['juju']},\n",
    "                'wendel fialh': {'text': 'Material Construção', 'category': categories['casa']},\n",
    "                'rnfastfood': {'text': 'Ifood', 'category': categories['lanches']},\n",
    "                'rnfastfo': {'text': 'Churrasquinho Praça', 'category': categories['lanches']},\n",
    "                'tribbu': {'text': 'Açaí', 'category': categories['lanches']},\n",
    "                'beertr': {'text': 'Rota Beer', 'category': categories['bar']},\n",
    "                 }"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "e22944fd-5081-483d-96c4-6ee72dcd4292",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_translate_item(item_text):\n",
    "    not_found_item = {'text': item_text, 'category': ''}\n",
    "    dict_item = [value for key, value in translate_dict.items() if key in item_text.lower()]\n",
    "    \n",
    "    return dict_item[0] if len(dict_item) else not_found_item\n",
    "\n",
    "def get_payer():\n",
    "    return 'Vitor'\n",
    "\n",
    "def get_who_transfers(payer, buyer):\n",
    "    return ''\n",
    "    # return 'Não' if (payer.lower() in buyer.lower()) else 'Sim'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "eec06814-7431-4061-8cda-92cf1b8aa220",
   "metadata": {},
   "outputs": [],
   "source": [
    "header = ['Mes', 'Pagamento', 'Item', 'Categoria', 'Quem pagou', 'Valor', 'Quem transfere?', 'Cartão']\n",
    "data = []\n",
    "\n",
    "for buyer, expenses_list in expenses.items():\n",
    "    for idx, expense in enumerate(expenses_list):\n",
    "        translated_item = get_translate_item(expense['text'])\n",
    "        \n",
    "        month = '01'\n",
    "        pay_date = transform_date(expense['date']).strftime(\"%m/%d/%Y\")\n",
    "        description = translated_item['text']\n",
    "        value = expense['value']\n",
    "        payer = get_payer()\n",
    "        category = translated_item['category']\n",
    "        who_transfers = get_who_transfers(payer, buyer)\n",
    "        card = 'PDA'\n",
    "        \n",
    "        expense_row = [month, pay_date, description, category, payer, value, who_transfers, card]\n",
    "        data.append(expense_row)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "641ca4db-4b11-4aab-9afa-e1cd1a1bdf41",
   "metadata": {},
   "outputs": [],
   "source": [
    "import csv\n",
    "\n",
    "with open('expenses.csv', 'w', encoding='UTF8', newline='') as f:\n",
    "    writer = csv.writer(f)\n",
    "\n",
    "    # write the header\n",
    "    writer.writerow(header)\n",
    "\n",
    "    # write multiple rows\n",
    "    writer.writerows(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5c9f9bc3-6269-4473-934b-37abf7fc1e25",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}