TradeRepublic_PDF_Converter.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "TradeRepublic_PDF_Converter.ipynb",
      "provenance": [],
      "collapsed_sections": [],
      "authorship_tag": "ABX9TyM9U+8SZvJkB7T1fbqCKtnW",
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/Thukyd/TradeRepublic_Portfolio/blob/master/TradeRepublic_PDF_Converter.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "XooSrEXI9Vii",
        "colab_type": "text"
      },
      "source": [
        "# **0.4 | TradeRepublic Portfolio** \n",
        "# *PDF Converter for Portfolio Performance & Investing.com*\n",
        "# a) Description\n",
        "This tool should help to keep the overview of the transactions within TradeRepublic. In TR there is the possibility to export all orders as PDF. For testing purposes the import and export is currently only possible with G-Drive. Alternatives are on the todo list. \n",
        "\n",
        "\n",
        "## Output A: Master Sheet\n",
        "The master sheet lists all previous transactions from the PDFs. \n",
        "\n",
        "## Output B: Delta Sheet\n",
        "The delta sheet contains all new transactions that have been added since the script was last executed. These are intended to be imported into Portfolio Performance and Investing.com. Delta sheets are dated.\n",
        "\n",
        "## Output C: Portfolio Sheet\n",
        "The portfolio sheet contains an overview of all open positions with basic information as average purchase price. The three fields \"Stop Preis\", \"Limit Preis\" and \"Strategie\" are for notes, which are kept at each execution of the script, if the corresponding position is still in the portfolio. \n",
        "\n",
        "## Scope \n",
        "*   optimized for Google Colab (https://colab.research.google.com/)\n",
        "*   export optimized for \"Portfolio Performance\" (https://www.portfolio-performance.info/) and \"Investing.com\" (https://de.investing.com/)\n",
        "*   for further information about the import at \"Investing.com\", see: https://www.investing-support.com/hc/en-us/articles/360000265217-Import-Portfolio-Holdings \n",
        "\n",
        "\n",
        "# b) Configuration\n",
        "## G-Drive Path\n",
        "*   TradeRepublic statements will be imported via GoogleDrive. All PDF files shoule be in a single folder. You need to configure the path to your G-Drive before usage (see: \"Configurations - to be defined by user\")\n",
        "\n",
        "# c) Options to customize\n",
        "- for different data sources, see: https://colab.research.google.com/notebooks/io.ipynb\n",
        "- in order to create different data structures, take a look at \"Examples for extracted fields\". \n",
        "\n",
        "# d) Further Information\n",
        "## Handling of costs statements\n",
        "*   \"Kosten des Wertpapierkaufs/verkaufs\" are be considered.\n",
        "*   \"Kosten während der Haltedauer (pro Jahr)\" are not extracted and therefore do not appear the sheets. \n",
        "\n",
        "## Deposits & Withdrawls\n",
        "* deposits and withdrawals to the depot are not recorded in PDF format. Therefore they are not taken into account and must be entered by hand if necessary.\n",
        "\n",
        "## \"Order\" in Trade Republic documents\n",
        "* each TradeRepublic document has got a \"order\" number. This is extracted and stored in the field \"Notiz\". It serves to prevent duplicate entries.  \n",
        "\n",
        "# e) Backlog\n",
        "## Open\n",
        "* create sheets for portfolio performance in .csv format\n",
        "* offer alternatives to G-Drive import/export\n",
        "\n",
        "## To be fixed\n",
        "- ...\n",
        "\n",
        "# f) Changelog \n",
        "## 0.1\n",
        "* extract G-Drive folder of TradeRepublic PDFs\n",
        "* create data structure (for Portfolio Performance or other purposes)\n",
        "* generate master sheet of all transactions\n",
        "* generate delta sheet for new transactions (base for TradeRepublic import)\n",
        "\n",
        "## 0.2\n",
        "- fixed | sort extracted transactions by date\n",
        "- fixed | double entries of table lables in delta sheet\n",
        "\n",
        "## 0.3\n",
        "- add ticker symbol via finnhub api (requires your own api key)\n",
        "- fixed | calculate purchase price\n",
        "- add values to sheet (\"Kaufkurs\", \"Gebühren\")\n",
        "- data structure for Investing.com import\n",
        "\n",
        "## 0.4\n",
        "- full refactoring (new class structure)\n",
        "- add current portfolio as output option\n",
        "- allow data enrichment of extracted data\n",
        "- note already extracted PDFs"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "MtKU_ZXzVvpQ",
        "colab_type": "code",
        "cellView": "both",
        "colab": {}
      },
      "source": [
        "\n",
        "#@markdown ### PDF Source\n",
        "input_source = \"G-Drive\" #@param [\"G-Drive\"] {allow-input: true}\n",
        "\n",
        "#@markdown ### GDrive Folder\n",
        "# GDRIVE\n",
        "# path to gdrive folder with your TradeRepublic pdfs\n",
        "\n",
        "# e..g \"/content/drive/My Drive/MY_TR_FOLDER/\"\n",
        "path_gdrive = \"/content/drive/My Drive/MY_TR_FOLDER/\" #@param {type:\"string\"}\n",
        "\n",
        "#@markdown ### Output Destination\n",
        "\n",
        "output_destination = \"Google-Sheets\" #@param [\"Google-Sheets\"] {allow-input: true}\n",
        "\n",
        "#@markdown ### Data Enrichment by OnvistaAPI (optional) \n",
        "# formatted name of asset\n",
        "# ticker symbol\n",
        "# wkin\n",
        "# asset type\n",
        "  \n",
        "onvista = True #@param {type:\"boolean\"}\n",
        "\n",
        "#@markdown ### Optimized for Investing.com or Porfolio Performance\n",
        "\n",
        "export_optimized_for = \"Portfolio Performance\" #@param [\"Portfolio Performance\", \"Investing.com\"] {allow-input: true}\n",
        "\n",
        "\n",
        "#@markdown ### Create overview table of current portfolio (next to Delta & Master)\n",
        "\n",
        "portfolio_sheet = True #@param {type:\"boolean\"}\n",
        "\n",
        "\n",
        "config = {\n",
        "    \"input_source\" : input_source,\n",
        "    \"output_destination\" : output_destination,\n",
        "    \"path_gdrive\" : path_gdrive,\n",
        "    \"onvista\" : onvista,\n",
        "    \"export_optimized_for\" : export_optimized_for,\n",
        "    \"portfolio_sheet\" : portfolio_sheet\n",
        "}\n"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "uTLgvO0ErLmB",
        "colab_type": "text"
      },
      "source": [
        "# Helper Methods\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ovmPmkrpIQrL",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "class HelperMethods:\n",
        "  def __init__(self):\n",
        "    # requirement for get_date_today(), convert_str_to_date()\n",
        "    from datetime import date, datetime\n",
        "    self.date = date\n",
        "    self.datetime = datetime\n",
        "    # pandas\n",
        "    import pandas as pd\n",
        "    self.pd = pd\n",
        "\n",
        "\n",
        "  def get_date_today(self):\n",
        "    today = self.date.today()\n",
        "    return today.strftime(\"%Y-%m-%d\")\n",
        "\n",
        "  def convert_str_to_date(self, input_string):\n",
        "    output = self.datetime.strptime(input_string, \"%d.%m.%Y\").date()\n",
        "    return output\n",
        "\n",
        "  def convert_date_to_str(self, input_date):\n",
        "    output = input_date.strftime(\"%d.%m.%Y\")\n",
        "    return output\n",
        "\n",
        "  def convert_german_decimal_to_float(self, german_decimal_str):\n",
        "    decimal_point = german_decimal_str.replace(\",\", \".\") # input: 20.000,00 ; output: 20.000.00\n",
        "    result = decimal_point.replace(\".\", \"\", decimal_point.count(\".\") -1) # input: 20.000.00 ; output: 20000.00\n",
        "    return float(result)\n",
        "\n",
        "  def convert_float_to_german_decimal(self, number):\n",
        "    # build format string\n",
        "    format_str = \"{{:,.{}f}}\".format(2)\n",
        "    # make number string\n",
        "    number_str = format_str.format(number)\n",
        "    # replace chars\n",
        "    return number_str.replace(',', 'X').replace('.', ',').replace('X', '.')\n",
        "    # https://stackoverflow.com/questions/55616520/is-there-a-simple-and-preferred-way-of-german-number-string-formatting-in-python/55616843\n",
        "\n",
        "  def calculate_weighted_average(self, dataframe, column_for_average, column_for_weight):\n",
        "    a = dataframe[column_for_average]\n",
        "    w = dataframe[column_for_weight]\n",
        "    # convert german decimal to float\n",
        "    a1 = a.apply( lambda x : self.convert_german_decimal_to_float(x) )\n",
        "    # calulate average\n",
        "    weighted_average = (a1 * w).sum() / w.sum()\n",
        "    # convert float to german decimal \n",
        "    weighted_average = self.convert_float_to_german_decimal(weighted_average)\n",
        "    return weighted_average\n",
        "\n",
        "  def abs_sum_dataframe_with_german_decimals(self, dataframe_column):\n",
        "    df = dataframe_column.apply( lambda x : self.convert_german_decimal_to_float(x) )\n",
        "    df = abs( df.sum() ) # absolute sum \n",
        "    return self.convert_float_to_german_decimal(df)\n",
        "\n",
        "  def get_diff_between_2_dataframes(self, df1, df2, which=None):\n",
        "    \"\"\"\n",
        "      Find Rows Which Are Not common Between Two dataframes - by subset \"Order ID\"\n",
        "        https://kanoki.org/2019/07/04/pandas-difference-between-two-dataframes/\n",
        "\n",
        "      Remember: \n",
        "        If \"Order ID\" was not used as a subset to filter, \n",
        "        it would no longer be possible to edit and complete the sheet (notes, WKN or other missing information).\n",
        "        All changes would be overwritten with the next execution of the script.\n",
        "    \"\"\"\n",
        "    return self.pd.concat([df1,df2]).drop_duplicates(subset=[\"Order ID\"], keep=False)\n",
        "\n",
        "  def sort_dataframe_column_by_label(self, my_dataframe, label):\n",
        "    return my_dataframe.reindex(columns=label)\n",
        "\n",
        "  def sort_dataframe_rows_by_date(self, my_dataframe):\n",
        "    # convert to str to datetime\n",
        "    my_dataframe[\"Datum\"] = self.pd.to_datetime(my_dataframe[\"Datum\"], infer_datetime_format=True)\n",
        "    # sort by date\n",
        "    my_dataframe = my_dataframe.sort_values(by = \"Datum\")\n",
        "    # change format\n",
        "    my_dataframe[\"Datum\"] = my_dataframe[\"Datum\"].dt.strftime(\"%d.%m.%Y\")\n",
        "    # convert datetime to str\n",
        "    my_dataframe[\"Datum\"] = my_dataframe[\"Datum\"].astype(str)\n",
        "    # remove NaT artefacts after conversion\n",
        "    my_dataframe = my_dataframe[my_dataframe.Datum != \"NaT\"]\n",
        "    return my_dataframe\n",
        "  \n",
        "  # helper method to assign the proper sign for each transaction\n",
        "  def get_transaction_sign(self, amount, order_type):\n",
        "    if order_type == \"Kauf\" or order_type == \"Buy\":  \n",
        "      return amount\n",
        "    else:  \n",
        "      return -amount"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "5KXiY-FMrg-w",
        "colab_type": "text"
      },
      "source": [
        "# Input\n",
        "- defines the source of the PDFs to be extracted"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "MhB_YcLwRZfx",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "class Input:\n",
        "  def __init__(self, config):\n",
        "    self.config = config\n",
        "    if self.config[\"input_source\"] == \"G-Drive\":\n",
        "      self.connect_google_drive()\n",
        "\n",
        "  def connect_google_drive(self):\n",
        "    \"\"\"option to use google drive as source\"\"\"\n",
        "    # Load Google Drive helper\n",
        "    from google.colab import drive\n",
        "    # This will prompt for authorization\n",
        "    drive.mount(\"/content/drive\")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Jo6cgSUErxzY",
        "colab_type": "text"
      },
      "source": [
        "# Sheet Source\n",
        "- defines the source of the previously created sheets"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "6WFgs3d53_bw",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "class Sheet_Source:\n",
        "  def __init__(self, config):\n",
        "    #Install gsread\n",
        "    !pip install --upgrade --quiet gspread\n",
        "    import gspread\n",
        "    # authenticate gsheet access\n",
        "    from google.colab import auth\n",
        "    auth.authenticate_user()\n",
        "    from oauth2client.client import GoogleCredentials\n",
        "    # get access credentials for gsheet\n",
        "    self.gc = gspread.authorize(GoogleCredentials.get_application_default())\n",
        "    self.config = config\n",
        "    # g sheet as output & source of existing sheets\n",
        "    if self.config[\"output_destination\"] == \"Google-Sheets\":\n",
        "      self.get_extracted_pdf()\n",
        "\n",
        "  def get_extracted_pdf(self):\n",
        "    try: # yes => need to check which data is new and append \n",
        "      spreadsheet = self.gc.open(\"TradeRepublic_Extracted_Files\")\n",
        "      worksheet_extracted = spreadsheet.sheet1 # get worksheet for document\n",
        "      extracted_sheet = self.pd.DataFrame(worksheet_extracted.get_all_records())\n",
        "\n",
        "      self.config[\"list_extracted_pdfs\"] = extracted_sheet\n",
        "    except: # no => you can just fill in the extracted data as master sheet\n",
        "      self.config[\"list_extracted_pdfs\"] = []"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Q2q0pHA1r8gS",
        "colab_type": "text"
      },
      "source": [
        "# Extract PDFs\n",
        "- performs the extraction of the PDFs\n",
        "- only previously unknown documents are extracted"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "qnDV_FajMXb_",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "class ExtractPdfs:\n",
        "  def __init__(self, config):\n",
        "    self.config = config\n",
        "    # PyPDF2 for PDF extraction\n",
        "    !pip install PyPDF2\n",
        "    import PyPDF2\n",
        "    # requirement for pdf-folder extraction\n",
        "    import os\n",
        "    import glob\n",
        "\n",
        "    self.PyPDF2 = PyPDF2\n",
        "    self.glob = glob\n",
        "    self.os = os\n",
        "\n",
        "    # config G-Drive\n",
        "    if self.config[\"input_source\"] == \"G-Drive\":\n",
        "      if self.config[\"path_gdrive\"] == \"/content/drive/My Drive/YOUR_FOLDER/\":\n",
        "        print(\"Please provide the Google Drive path for your TradeRepublic PDFs folder\")\n",
        "      elif self.config[\"path_gdrive\"] == \"\":\n",
        "        print(\"Please provide the Google Drive path for your TradeRepublic PDFs folder\")\n",
        "      else:\n",
        "        self.path = self.config[\"path_gdrive\"]\n",
        "    \n",
        "  def start(self):\n",
        "    \"\"\"\n",
        "      Checks for relevant PDF in folder and extracts full text\n",
        "        Parameters\n",
        "        ----------\n",
        "        path : str\n",
        "            relative path for folder\n",
        "\n",
        "        Output\n",
        "        ------\n",
        "        raw_text_list : list\n",
        "          A list for all raw pdf extractions - one item is one pdf\n",
        "          [\n",
        "            \"Wertpapierordenummer\\n\\...\",\n",
        "            ...\n",
        "          ]\n",
        "    \"\"\"\n",
        "    raw_text_list = []\n",
        "    for filename in self.glob.glob(self.os.path.join(self.path, '*.pdf')):\n",
        "      with open(filename, 'rb') as fin: # open in readonly mode\n",
        "\n",
        "        # extract only pdfs which are not extracted already\n",
        "        if filename in self.config[\"list_extracted_pdfs\"]:\n",
        "          print(\"schon vorhanden\", filename)\n",
        "          continue\n",
        "        else:\n",
        "          self.config[\"list_extracted_pdfs\"].append(filename)\n",
        "\n",
        "        # read and extract pdf infos\n",
        "        pdf_reader = self.PyPDF2.PdfFileReader(fin)\n",
        "        # extract first page\n",
        "        extr_page = pdf_reader.getPage(0)\n",
        "        text = extr_page.extractText()\n",
        "\n",
        "        # relevance criteria\n",
        "        if \").\" in filename: # try to find pdf duplicates - eg. \"filename (1).pdf\" instead of \"filename.pdf\"\n",
        "            #print(\"Processing PDF | DUPLICATE   | \\\"WERTPAPIERABRECHNUNGÜBERSICHT\\\" |  \" + filename)\n",
        "            continue\n",
        "        elif \"WERTPAPIERABRECHNUNGÜBERSICHT\" in text:  ## all non duplicate,\"Wertpapergeschäftsorder\"\n",
        "            #print(\"Processing PDF | RELEVANT    | \\\"WERTPAPIERABRECHNUNGÜBERSICHT\\\" |  \" + filename)\n",
        "            pass\n",
        "        else: # non duplicate, irelevant files\n",
        "            #print(\"Processing PDF | IRELEVANT   |                            |  \" + filename)\n",
        "            continue\n",
        "      \n",
        "        raw_text_list.append(text)\n",
        "\n",
        "    return raw_text_list"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "UmPmDLeAsPXA",
        "colab_type": "text"
      },
      "source": [
        "# Process Text\n",
        "- processes the extracted PDFs into a temporary data structure "
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "FmTSxzxAEpYr",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "class ProcessText:\n",
        "  def __init__(self):\n",
        "    # Regex for text processing\n",
        "    import re\n",
        "    self.re = re\n",
        "    # HelperMethods()\n",
        "    self.HelperMethods = HelperMethods()\n",
        "  \n",
        "  def get_list_of_order_dicts(self, raw_text_list):\n",
        "    \"\"\"\n",
        "      Processes raw text of pdfs and returns a record of orders\n",
        "        Parameters\n",
        "        ----------\n",
        "        raw_text_list : list\n",
        "          A list for all raw pdf extractions - one item is one pdf\n",
        "          [\n",
        "            \"Wertpapierordenummer\\n\\...\",\n",
        "            \"...\"\n",
        "          ]\n",
        "\n",
        "        Output\n",
        "        ------\n",
        "        list_of_order_dicts : list\n",
        "          A list of dictionaries of all orders. The order is determined by the date of the transaction.\n",
        "\n",
        "          [\n",
        "            {\n",
        "              'Datum': '16.06.2020',\n",
        "              'Wertpapiername': 'Alibaba',\n",
        "              ...\n",
        "            },\n",
        "            {...}\n",
        "          ]\n",
        "    \"\"\"\n",
        "    list_of_order_dicts = []\n",
        "    for raw_text in raw_text_list:\n",
        "      # simplify processing in some cases by splitting pdf in header & body\n",
        "      split_criteria = \"WERTPAPIERABRECHNUNGÜBERSICHT\"\n",
        "      text_header, text_body = raw_text.split(split_criteria)\n",
        "\n",
        "      current_order = {\n",
        "      \"Datum\" : self.order_date(text_header),\n",
        "      \"Isin\" : self.isin(text_body),\n",
        "      \"Typ\": self.type(text_body),\n",
        "      \"Wert\" : self.value(text_body),\n",
        "      \"Buchungswährung\" : self.currency(text_body), \n",
        "      \"Stück\" : self.amount(text_body),\n",
        "      \"Wertpapiername\" : self.name(text_body),\n",
        "      \"Order ID\" : self.order_id(text_header),\n",
        "      \"Gebühren\" : self.costs(text_body)\n",
        "      }\n",
        "      list_of_order_dicts.append(current_order)\n",
        "    return list_of_order_dicts\n",
        "\n",
        "  \n",
        "  def order_date(self, text_header):\n",
        "    \"\"\"extracts the document date of header (transaction date)\"\"\"\n",
        "    pattern = \"DATUM\\d{2}.\\d{2}.\\d{4}\"\n",
        "    transaction_date = self.re.findall(pattern, text_header)\n",
        "    date = self.re.split(\"DATUM\", transaction_date[0])\n",
        "    return date[1]\n",
        "  \n",
        "  def order_id(self, text_header):\n",
        "    \"\"\"extracts the internal order id by TradeRepublic\"\"\"\n",
        "    pattern = \"(?<=ORDER)(.*?)(?=AUSFÜHRUNG)\"\n",
        "    id = self.re.findall(pattern, text_header)\n",
        "    return id[0]\n",
        "  \n",
        "  \n",
        "  def isin(self, text_body):\n",
        "    \"\"\"TradeRepublic uses ISIN only - ticker symbol or WSIN have to be added in later processing\"\"\"\n",
        "    isin_pattern = \"(?<=ISIN: )(BE|BM|FR|BG|VE|DK|HR|DE|JP|HU|HK|JO|BR|XS|FI|GR|IS|RU|LB|PT|NO|TW|UA|TR|LK|LV|LU|TH|NL|PK|PH|RO|EG|PL|AA|CH|CN|CL|EE|CA|IR|IT|ZA|CZ|CY|AR|AU|AT|IN|CS|CR|IE|ID|ES|PE|TN|PA|SG|IL|US|MX|SK|KR|SI|KW|MY|MO|SE|GB|GG|KY|JE|VG|NG|SA|MU)([0-9A-Z]{9})([0-9])\"\n",
        "    matches = self.re.findall(isin_pattern, text_body )\n",
        "    item = \"\".join(matches[0])\n",
        "    return item\n",
        "\n",
        "  def name(self, text_body):\n",
        "    pattern = \"(?<=KURSBETRAG)(.*?)(?=ISIN)\"\n",
        "    name = self.re.findall(pattern, text_body)\n",
        "    return name[0]\n",
        "\n",
        "  def costs(self, text_body):\n",
        "    \"\"\"\n",
        "      extracts costs for order in TradeRepublic\n",
        "      - running costs (TER etc.) won't be considered\n",
        "      - takes first appeance of of costs in pdf\n",
        "      - buy operations => pdf consists of buying costs, running costs & selling costs | only buying costs considered\n",
        "      - sell operation => pdf consists of selling costs | selling costs considered\n",
        "    \"\"\"\n",
        "    pattern = \"(?<=Fremdkostenzuschlag)(.*?)(?= )\"\n",
        "    costs = self.re.findall(pattern, text_body)\n",
        "    return costs[0]\n",
        "\n",
        "  def currency(self, text_body):\n",
        "    pattern = \"(?<= )(\\S*)(?=BUCHUNGVERRECHNUNGSKONTO)\"\n",
        "    currency = self.re.findall(pattern, text_body)\n",
        "    return currency[0]\n",
        "\n",
        "  def amount(self, text_body):\n",
        "    ISIN = self.isin(text_body)\n",
        "    pattern = \"(?<=\" + ISIN + \")(.*?)(?= Stk.)\"\n",
        "    amount = self.re.findall(pattern, text_body)\n",
        "    return amount[0]\n",
        "\n",
        "  def type(self, text_body):\n",
        "    pattern = \"(Kauf|Verkauf)\"\n",
        "    order_type = self.re.findall(pattern, text_body)\n",
        "    return order_type[0]\n",
        "\n",
        "  def value(self, text_body):\n",
        "    currency = self.currency(text_body)\n",
        "    pattern = \"(?<=GESAMT)(.*)(?= \"+ currency +\"ABRECHNUNGPOSITIONBETRAG)\"\n",
        "    value = self.re.findall(pattern, text_body)\n",
        "    return value[0]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "d1d9eBdosk9g",
        "colab_type": "text"
      },
      "source": [
        "# Enrich Data\n",
        "- - enriches the data structure with additional information from third-party sources or calculations."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "O63bg-ICrODd",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "class EnrichData:\n",
        "  def __init__(self, config, list_of_order_dicts):\n",
        "    import requests\n",
        "    self.requests = requests\n",
        "    self.list_of_order_dicts = list_of_order_dicts\n",
        "    # HelperMethods()\n",
        "    self.HelperMethods = HelperMethods()\n",
        "    self.config = config\n",
        "\n",
        "  def start_enrichment_by_config(self):\n",
        "    for order_dict in self.list_of_order_dicts:\n",
        "      # add order price for investing.com import\n",
        "      order_dict[\"Kaufkurs\"] = self.order_price_for_investing_com(order_dict[\"Wert\"], order_dict[\"Stück\"])\n",
        "\n",
        "      # overwrite name, add wkin, ticker-symbol and asset type by onvista api\n",
        "      if self.config[\"onvista\"] == True:\n",
        "        self.information_by_onvista_api(order_dict)\n",
        "\n",
        "      # Investing.com | translate order to english\n",
        "      if self.config[\"export_optimized_for\"] == \"Investing.com\":\n",
        "        order_dict[\"Typ\"] = self.order_type_for_investing_com(order_dict[\"Typ\"])\n",
        "\n",
        "      # Portfolio Perforrmance | add sign to order value\n",
        "      if self.config[\"export_optimized_for\"] == \"Portfolio Performance\":\n",
        "        order_dict[\"Wert\"] = self.order_sign_for_portfolio_performance(order_dict[\"Typ\"], order_dict[\"Wert\"])\n",
        "\n",
        "    return list_of_order_dicts\n",
        "\n",
        "  def information_by_onvista_api(self, order_dict):\n",
        "    \"\"\"\n",
        "      Enriches stock information by onvista\n",
        "        Parameters\n",
        "        ----------\n",
        "        order_dict : dict\n",
        "          {\n",
        "              'Datum': '16.06.2020',\n",
        "              'Wertpapiername': 'Alibaba',\n",
        "              ...\n",
        "          }\n",
        "          \n",
        "        Change in order_dict\n",
        "        ------\n",
        "        Wertpapiername : str\n",
        "          Name of Stock\n",
        "        Ticker-Symbol : str\n",
        "          International Symbol (crucial for stock data)\n",
        "        WKN : str\n",
        "          Wertpapierkennnummer \n",
        "        Vermögenswert : str\n",
        "          Type of Asset\n",
        "\n",
        "    \"\"\"\n",
        "    query = order_dict[\"Isin\"] \n",
        "    r = self.requests.get(\"https://www.onvista.de/onvista/boxes/assetSearch.json?doSubmit=Suchen&portfolioName=&searchValue=\" + query)\n",
        "    result = r.json()[\"onvista\"][\"results\"][\"asset\"][0]\n",
        "\n",
        "    order_dict[\"Wertpapiername\"] = result[\"shortname\"]\n",
        "    order_dict[\"Ticker-Symbol\"] = result[\"symbol\"]\n",
        "    order_dict[\"WKN\"] = result[\"nsin\"] # WKIN\n",
        "    order_dict[\"Vermögenswert\"] = result[\"assettype\"]\n",
        "    \n",
        "  def order_type_for_investing_com(self, order_type):\n",
        "    \"\"\"\n",
        "      The import at Investing.com needs \"Buy\" & \"Sell\" as input instead of \"Kauf\" & \"Verkauf\".\n",
        "        Parameters\n",
        "        ----------\n",
        "        order_type : str\n",
        "          \"Kauf\"/\"Verkauf\" statement\n",
        "\n",
        "        Output\n",
        "        ------\n",
        "        order_type : str\n",
        "          \"Buy\"/\"Sell\" statement - used in new field!\n",
        "    \"\"\"\n",
        "    if order_type == \"Kauf\":\n",
        "      order_type = \"Buy\"\n",
        "    if order_type == \"Verkauf\":\n",
        "      order_type = \"Sell\"\n",
        "    return order_type\n",
        "\n",
        "  def order_price_for_investing_com(self, order_value, order_amount):\n",
        "    \"\"\"\n",
        "      The purchase price for the stock for The import at Investing.com needs \n",
        "        Parameters\n",
        "        ----------\n",
        "        order_type : str\n",
        "          \"Kauf\"/\"Verkauf\" statement\n",
        "\n",
        "        Output\n",
        "        ------\n",
        "        order_type : str\n",
        "          \"Buy\"/\"Sell\" statement - used in new field!\n",
        "    \"\"\"\n",
        "    # transform string to float\n",
        "    float_value = self.HelperMethods.convert_german_decimal_to_float(order_value)\n",
        "    float_amount = float(order_amount)\n",
        "    # calculate price of single stock\n",
        "    calculated_price = float_value / float_amount\n",
        "    # convert calculated float into string again\n",
        "    order_price = self.HelperMethods.convert_float_to_german_decimal(calculated_price)\n",
        "    return order_price\n",
        "\n",
        "\n",
        "  def order_sign_for_portfolio_performance(self, order_type, order_value):\n",
        "    \"\"\"\n",
        "      In TradeRepublic statements all total values for buy & sell are in positive numbers.\n",
        "      For the conversion to Portfolio Performance (PP) this value has got to take the type of transaction in account.\n",
        "      It needs to have a sign. Negative numbers for \"buy\" and positive numbers for \"sell\".\n",
        "        Parameters\n",
        "        ----------\n",
        "        order_type : str\n",
        "          \"Kauf\"/\"Verkauf\" statement\n",
        "        order_value : str\n",
        "          Total value of order\n",
        "\n",
        "        Output\n",
        "        ------\n",
        "         order_type : str\n",
        "          \"Kauf\"/\"Verkauf\" statement\n",
        "        order_value : str\n",
        "          Total value of order with sign\n",
        "    \"\"\"\n",
        "    if order_type == \"Kauf\":\n",
        "      return \"-\" + order_value\n",
        "    else: \n",
        "      return order_value"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "36nZdv-Osx_H",
        "colab_type": "text"
      },
      "source": [
        "# Data Integration\n",
        "- prepares the export to the target source\n",
        "- extends the Mastersheet\n",
        "- determines the delta sheet (new transactions)\n",
        "- determines the current portfolio and the shares of the positions held in it"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Kp4iJB85UTam",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "class DataIntegration:\n",
        "  def __init__(self, config, list_of_dicts_extracted_data):\n",
        "    # read/write config\n",
        "    self.config = config\n",
        "    # provides extracted & enriched data - list of dictionaries\n",
        "    self.extracted_list = list_of_dicts_extracted_data\n",
        "    # HelperMethods()\n",
        "    self.HelperMethods = HelperMethods()\n",
        "    # pandas\n",
        "    import pandas as pd\n",
        "    self.pd = pd\n",
        "\n",
        "    # CONFIGURATIONS\n",
        "    self.master_and_delta_lables = [\"Datum\", \"Typ\", \"Wertpapiername\", \"Stück\",\t\"Kaufkurs\",\t\"Wert\",\t\"Isin\",\t\"WKN\",\"Ticker-Symbol\", \"Gebühren\", \"Notiz\", \"Vermögenswert\",\"Steuern\", \"Buchungswährung\",\t\"Order ID\", \"Icon\"]\n",
        "\n",
        "    # do you need a portfolio sheet next to master & delta sheet\n",
        "    if self.config[\"portfolio_sheet\"] == True:\n",
        "      self.portfolio_lables = [\"Datum\", \"Isin\", \"WKN\", \"Ticker-Symbol\", \"Vermögenswert\", \"Wertpapiername\", \"Buchungswährung\", \"Kaufpreis\", \"Stück\", \"Ø Kaufkurs\", \"Stop Kurs\", \"Limit Kurs\", \"Strategie\"]\n",
        "\n",
        "    # g sheet as output \n",
        "    if self.config[\"output_destination\"] == \"Google-Sheets\":\n",
        "      self.init_gsheet()\n",
        "    \n",
        "  def init_gsheet(self):\n",
        "    #Install gsread\n",
        "    !pip install --upgrade --quiet gspread\n",
        "    import gspread\n",
        "    # authenticate gsheet access\n",
        "    from google.colab import auth\n",
        "    auth.authenticate_user()\n",
        "    from oauth2client.client import GoogleCredentials\n",
        "    # get access credentials for gsheet\n",
        "    self.gc = gspread.authorize(GoogleCredentials.get_application_default())\n",
        "  \n",
        "  def get_dataframes_for_gsheet_export(self):\n",
        "    \"\"\"\n",
        "    Returns rows which need to be added to delta & master sheet\n",
        "      checks which entries are new & puts them into dataframe for export\n",
        "    \n",
        "      Output\n",
        "        ------\n",
        "        data_update_sheets : dict\n",
        "          Dictionary of dataframes which can be exported to master and delta sheet, eg.\n",
        "          {\n",
        "            \"master\" : DataframeForMasterUpdate,\n",
        "            \"delta\" : DataframeForDeltaUpdate,\n",
        "            \"portfolio\" : \"\"\n",
        "          }\n",
        "    \"\"\"\n",
        "    # check if sheets are exisiting (write result in config) & get existing data from sheets\n",
        "    dataframe_master = self.get_gsheet_master()\n",
        "    dataframe_delta = self.get_gsheet_delta()\n",
        "    data_update_sheets = {\n",
        "          \"master\" : dataframe_master,\n",
        "          \"delta\" : dataframe_delta\n",
        "      }\n",
        "\n",
        "    # append portfolio if needed\n",
        "    if self.config[\"portfolio_sheet\"] == True:\n",
        "      dataframe_portfolio = self.get_gsheet_portfolio(dataframe_master)\n",
        "      # config for sheet update\n",
        "      data_update_sheets[\"portfolio\"] = dataframe_portfolio\n",
        "\n",
        "    # return the data for exports\n",
        "    return data_update_sheets\n",
        "\n",
        "  def get_extracted_data(self):\n",
        "    \"\"\"Converts extracted & enriched data (list of dictionaries) into a pandas dataframe\"\"\"\n",
        "    extracted_data_dataframe = self.pd.DataFrame(self.extracted_list)\n",
        "    return extracted_data_dataframe\n",
        "\n",
        "  def get_gsheet_master(self):\n",
        "    # get new data from pdfs\n",
        "    extracted_data = self.get_extracted_data()\n",
        "\n",
        "    # check if master sheet exists\n",
        "    try: # yes => need to check which data is new and append \n",
        "      spreadsheet = self.gc.open(\"TradeRepublic_Master\")\n",
        "      worksheet_master = spreadsheet.sheet1 # get worksheet for document\n",
        "      master_sheet = self.pd.DataFrame(worksheet_master.get_all_records())\n",
        "\n",
        "      # get what new data do you have \n",
        "      new_dataframe = self.HelperMethods.get_diff_between_2_dataframes(master_sheet, extracted_data)\n",
        "      # delta => save difference for delta sheet\n",
        "      self.new_delta = new_dataframe\n",
        "\n",
        "      # append the new data to master sheet\n",
        "      new_dataframe = master_sheet.append(new_dataframe)\n",
        "      # update config file\n",
        "      self.config[\"master_sheet_exists\"] = True\n",
        "    except: # no, you can just fill in the extracted data as master sheet\n",
        "      new_dataframe = extracted_data\n",
        "      # delta => save difference for delta sheet\n",
        "      self.new_delta = extracted_data\n",
        "      self.config[\"master_sheet_exists\"] = False\n",
        "    \n",
        "    # sort columns by label\n",
        "    dataframe_master = self.HelperMethods.sort_dataframe_column_by_label(new_dataframe, self.master_and_delta_lables)\n",
        "    #remove \"NAN\" \n",
        "    dataframe_master.fillna('', inplace=True)\n",
        "    # sort rows (by date)\n",
        "    dataframe_master  = self.HelperMethods.sort_dataframe_rows_by_date(dataframe_master)\n",
        "\n",
        "    return dataframe_master\n",
        "\n",
        "  def get_gsheet_delta(self):\n",
        "    # get new data from pdfs\n",
        "    extracted_data = self.get_extracted_data()\n",
        "    # check if delta sheet exists\n",
        "    today = HelperMethods().get_date_today()\n",
        "    try: # yes => need to check which data is new does not exist in current sheet already\n",
        "      spreadsheet = self.gc.open(\"TradeRepublic_Delta_\" + today)\n",
        "      worksheet_delta = spreadsheet.sheet1\n",
        "      delta_sheet = self.pd.DataFrame(worksheet_delta.get_all_records())\n",
        "      # was created in get_gsheet_master and represents the diference of the extracted data and the master sheet => the delta!\n",
        "      new_delta = self.new_delta\n",
        "      # append the new data to delta sheet\n",
        "      dataframe_delta = delta_sheet.append(new_delta)\n",
        "\n",
        "      # update config file\n",
        "      self.config[\"delta_sheet_exists\"] = True\n",
        "    except:\n",
        "      # was created in get_gsheet_master and represents the diference of the extracted data and the master sheet => the delta!\n",
        "      dataframe_delta = self.new_delta\n",
        "      self.config[\"delta_sheet_exists\"] = False\n",
        "    # sort column by label\n",
        "    dataframe_delta = self.HelperMethods.sort_dataframe_column_by_label(dataframe_delta, self.master_and_delta_lables)\n",
        "    #remove \"NAN\" \n",
        "    dataframe_delta.fillna('', inplace=True)\n",
        "    # sort rows (by date)\n",
        "    dataframe_delta  = self.HelperMethods.sort_dataframe_rows_by_date(dataframe_delta)\n",
        "    return dataframe_delta\n",
        "\n",
        "  def get_gsheet_portfolio(self, dataframe_master):\n",
        "    try:\n",
        "      # does the sheet already exists?\n",
        "      spreadsheet = self.gc.open(\"TradeRepublic_Portfolio\")\n",
        "      worksheet_portfolio = spreadsheet.sheet1 # get worksheet for document\n",
        "      exisiting_portfolio = self.pd.DataFrame(worksheet_portfolio.get_all_records())\n",
        "      # prepare current portfolio\n",
        "      new_portfolio = self.current_portfolio_by_master_sheet(dataframe_master)\n",
        "      # merge extracted & new portfolio\n",
        "      dataframe_portfolio = new_portfolio.merge(exisiting_portfolio[[\"Isin\", \"Stop Kurs\", \"Limit Kurs\", \"Strategie\"]], on='Isin', how=\"left\")\n",
        "      dataframe_portfolio = dataframe_portfolio.drop(columns=[\"Stop Kurs_x\", \"Limit Kurs_x\", \"Strategie_x\"])\n",
        "      dataframe_portfolio = dataframe_portfolio.rename(columns={\"Stop Kurs_y\" : \"Stop Kurs\", \"Limit Kurs_y\" : \"Limit Kurs\", \"Strategie_y\": \"Strategie\"})\n",
        "      dataframe_portfolio.fillna('', inplace=True)\n",
        "      self.config[\"portfolio_sheet_exists\"] = True\n",
        "       \n",
        "    except: \n",
        "      # portfolio does not exists so it can be created from scratch\n",
        "      dataframe_portfolio = self.current_portfolio_by_master_sheet(dataframe_master)\n",
        "      dataframe_portfolio.fillna('', inplace=True)\n",
        "      self.config[\"portfolio_sheet_exists\"] = False\n",
        "\n",
        "    return dataframe_portfolio\n",
        "\n",
        "  def current_portfolio_by_master_sheet(self, dataframe_all_orders):\n",
        "    \"\"\"\n",
        "      Calcultes the amount of stock in current porfolio and it's average purchase price \n",
        "          Parameters\n",
        "          ----------\n",
        "          dataframe_all_orders : pd.dataframe\n",
        "            all orders\n",
        "\n",
        "          Output\n",
        "          ------\n",
        "          ----------\n",
        "          dataframe_portfolio : pd.dataframe\n",
        "            current holdings with key infrmation as average purchase price etc.\n",
        "    \"\"\"\n",
        "    current_portfolio = self.pd.DataFrame( columns= self.portfolio_lables) # emtpy dataframe with correct labeling\n",
        "\n",
        "    # filter down uniqe stocks by isin\n",
        "    unique_stocks = dataframe_all_orders.drop_duplicates(subset=['Isin'])\n",
        "    # loop through unique stocks and handover to calculate the current holding in the portfolio\n",
        "    for index, row in unique_stocks.iterrows():\n",
        "      current_isin = row[\"Isin\"]\n",
        "      orders_for_current_isin = dataframe_all_orders [ dataframe_all_orders['Isin'] == current_isin ] # filter down orders of current isin\n",
        "      # calculate the current number of shares in the portfolio\n",
        "      portfolio_current_stock = self.calculate_stock_in_portfolio( orders_for_current_isin )\n",
        "      current_portfolio = self.pd.concat( [current_portfolio, portfolio_current_stock] ) # append to dataframe for to portfolio\n",
        "    return current_portfolio\n",
        "\n",
        "\n",
        "  def summarize_portfolio_orders(self, dataframe_orders):\n",
        "    \"\"\"\n",
        "     Calculates on the basis of transactions how many shares are currently in the portfolio, their total value and average purchase price\n",
        "      Parameters\n",
        "      ----------\n",
        "      dataframe_orders : pd.dataframe\n",
        "        transaction for a single share value\n",
        "\n",
        "      Output\n",
        "      ------\n",
        "      dataframe_stock_in_portfolio : pd.dataframe\n",
        "        returns a single row in the \"self.portfolio_lables\" indexed dataframe\n",
        "    \"\"\"\n",
        "    \n",
        "    # calcualte weighted average for purchase price\n",
        "    weighted_average = self.HelperMethods.calculate_weighted_average(dataframe_orders, \"Kaufkurs\", \"Stück\")\n",
        "    # get the total amount of stocks\n",
        "    order_amount = dataframe_orders[\"total_holdings\"].iloc[-1] # cell in last row\n",
        "    # sum value and turn into positive number \"Gezahlter Wert\" ~ or something similar\n",
        "    order_value = self.HelperMethods.abs_sum_dataframe_with_german_decimals(dataframe_orders[\"Wert\"])\n",
        "\n",
        "    dataframe_stock_in_portfolio = dataframe_orders.tail(1)\n",
        "    dataframe_stock_in_portfolio[\"Stück\"] = order_amount\n",
        "    dataframe_stock_in_portfolio[\"Ø Kaufkurs\"] = weighted_average\n",
        "    dataframe_stock_in_portfolio[\"Kaufpreis\"] = order_value\n",
        "    # reindex with portfolio lables\n",
        "    dataframe_stock_in_portfolio = dataframe_stock_in_portfolio.reindex(self.portfolio_lables, axis=\"columns\")\n",
        "    return dataframe_stock_in_portfolio\n",
        "\n",
        "  def calculate_stock_in_portfolio(self, dataframe_orders_single_stock):\n",
        "    \"\"\"\n",
        "      Checks the amount of stocks left for each share value\n",
        "        if non => return\n",
        "        if still stocks left & never completley sold => return calculated remaining shares\n",
        "        if still stocks left & in between sold all => filter down relevant once, return calculated remaining shares  \n",
        "\n",
        "\n",
        "        Parameters\n",
        "        ----------\n",
        "        dataframe_orders_single_stock : pd.dataframe\n",
        "          contains all orders for a specific stock\n",
        "\n",
        "        Output\n",
        "        ------\n",
        "        \"\" : pd.dataframe\n",
        "          returns dataframe for total holdings of a single share value\n",
        "            if non left => return\n",
        "            if still stocks left & never completley sold => return calculated remaining shares\n",
        "            if still stocks left & in between sold all => filter down relevant once, return calculated remaining shares  \n",
        "          \n",
        "    \"\"\"\n",
        "    # \"change of stock amount\" add the sign to the amount of stocks for each order; sell 5 => -5; buy 5 => 5;\n",
        "    dataframe_orders_single_stock[\"Stück\"] = dataframe_orders_single_stock[\"Stück\"].astype(int) \n",
        "    dataframe_orders_single_stock[\"change_of_stock_amount\"] = dataframe_orders_single_stock.apply( lambda row : self.HelperMethods.get_transaction_sign( row[\"Stück\"] , row[\"Typ\"] ) , axis=1)\n",
        "\n",
        "    # sum up current stocks in portfolio\n",
        "    dataframe_orders_single_stock[\"change_of_stock_amount\"] = dataframe_orders_single_stock[\"change_of_stock_amount\"].astype(float)\n",
        "    dataframe_orders_single_stock[\"total_holdings\"] = dataframe_orders_single_stock[\"change_of_stock_amount\"].cumsum()\n",
        "    \n",
        "    \n",
        "    # check last row for current amount of stock in portfolio which contains the current holding of each stock\n",
        "    last_row = dataframe_orders_single_stock.tail(1)\n",
        "    if 0 in last_row[\"total_holdings\"].values:\n",
        "      # there are not stocks left anymore => kick out the stock\n",
        "      return \n",
        "\n",
        "    else:\n",
        "      # Check if stock was sold out in between, but bought again into current portfolio\n",
        "        # a) stock was never sold completley => all orders are relevant for calculation\n",
        "      if 0 not in dataframe_orders_single_stock[\"total_holdings\"].values:\n",
        "        orders_for_current_portfolio =  self.summarize_portfolio_orders(dataframe_orders_single_stock)\n",
        "        return orders_for_current_portfolio\n",
        "\n",
        "        # b) stock was sold compleltly in between => orders should be filtered since last complete sell\n",
        "      else:\n",
        "        # filter to the last time the the stock was sold completely\n",
        "          # Reverse the column C then use Series.ne + Series.cummin to create a boolean mask\n",
        "        boolean_mask = dataframe_orders_single_stock.loc[::-1, \"total_holdings\"].ne(0).cummin()[::-1]\n",
        "          # then use this mask to filter the rows in datframe:\n",
        "        last_remaining = dataframe_orders_single_stock[boolean_mask]\n",
        "        \n",
        "        # calulate the current holdings based - only considering relevant oders in the books\n",
        "        relevant_orders_for_current_portfolio = self.summarize_portfolio_orders(last_remaining)\n",
        "        return relevant_orders_for_current_portfolio"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Tvy3sg4qtco8",
        "colab_type": "text"
      },
      "source": [
        "# Output\n",
        "- performs the export of the specified sheets"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "u0VGc7GAJAiU",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "class Output:\n",
        "  def __init__(self, config, dataframe):\n",
        "    #Install gsread\n",
        "    !pip install --upgrade --quiet gspread\n",
        "    import gspread\n",
        "    # authenticate gsheet access\n",
        "    from google.colab import auth\n",
        "    auth.authenticate_user()\n",
        "    from oauth2client.client import GoogleCredentials\n",
        "    # get access credentials for gsheet\n",
        "    self.gc = gspread.authorize(GoogleCredentials.get_application_default())\n",
        "    # pandas\n",
        "    import pandas as pd\n",
        "    self.pd = pd\n",
        "\n",
        "    self.config = config\n",
        "\n",
        "    # update list of already extracted pdfs\n",
        "    self.update_extracted_pdf()\n",
        "\n",
        "    self.update_master(dataframe[\"master\"])\n",
        "    self.update_delta(dataframe[\"delta\"])\n",
        "    if self.config[\"portfolio_sheet\"] == True:\n",
        "      self.update_portfolio(dataframe[\"portfolio\"])\n",
        "\n",
        "\n",
        "  def update_extracted_pdf(self):\n",
        "    extracted_pdfs_dataframe = self.pd.DataFrame(self.config[\"list_extracted_pdfs\"])\n",
        "    \n",
        "    try:\n",
        "      spreadsheet = self.gc.open(\"TradeRepublic_Extracted_Files\")\n",
        "      worksheet_extracted = spreadsheet.sheet1 # get worksheet for document\n",
        "      worksheet_extracted.clear() # alten Worksheet löschen\n",
        "\n",
        "    except:\n",
        "      spreadsheet = self.gc.create(\"TradeRepublic_Extracted_Files\")\n",
        "      worksheet_extracted = spreadsheet.sheet1 # get worksheet for document\n",
        "\n",
        "    worksheet_extracted.update(\n",
        "        extracted_pdfs_dataframe.values.tolist() # value\n",
        "    )\n",
        "\n",
        "\n",
        "  def update_master(self, master_data):\n",
        "\n",
        "    master_update_dataframe = self.pd.DataFrame(master_data)\n",
        "\n",
        "    if self.config[\"master_sheet_exists\"]:\n",
        "      spreadsheet = self.gc.open(\"TradeRepublic_Master\")\n",
        "      worksheet_master = spreadsheet.sheet1 # get worksheet for document\n",
        "      worksheet_master.clear() # alten Worksheet löschen\n",
        "\n",
        "    else:\n",
        "      spreadsheet = self.gc.create(\"TradeRepublic_Master\")\n",
        "      worksheet_master = spreadsheet.sheet1 # get worksheet for document\n",
        "\n",
        "    worksheet_master.update(\n",
        "        [master_update_dataframe.columns.values.tolist()] # label\n",
        "        +\n",
        "        master_update_dataframe.values.tolist() # value\n",
        "    )\n",
        "\n",
        "  def update_delta(self, delta_data):\n",
        "    delta_update_dataframe = self.pd.DataFrame(delta_data)\n",
        "    today = HelperMethods().get_date_today()\n",
        "\n",
        "    if self.config[\"delta_sheet_exists\"]:\n",
        "      spreadsheet = self.gc.open(\"TradeRepublic_Delta_\" + today)\n",
        "      worksheet_delta = spreadsheet.sheet1 # get worksheet for document\n",
        "      worksheet_delta.clear() # alten Worksheet löschen\n",
        "\n",
        "    else:\n",
        "      spreadsheet = self.gc.create(\"TradeRepublic_Delta_\" + today)\n",
        "      worksheet_delta = spreadsheet.sheet1 # get worksheet for document\n",
        "\n",
        "    worksheet_delta.update(\n",
        "        [delta_update_dataframe.columns.values.tolist()] # label\n",
        "        +\n",
        "        delta_update_dataframe.values.tolist() # value\n",
        "    )\n",
        "\n",
        "  def update_portfolio(self, portfolio_data):\n",
        "    portfolio_update_dataframe = self.pd.DataFrame(portfolio_data)\n",
        "\n",
        "    if self.config[\"portfolio_sheet_exists\"]:\n",
        "      spreadsheet = self.gc.open(\"TradeRepublic_Portfolio\")\n",
        "      worksheet_portfolio = spreadsheet.sheet1 # get worksheet for document\n",
        "      worksheet_portfolio.clear() # alten Worksheet löschen\n",
        "\n",
        "    else:\n",
        "      spreadsheet = self.gc.create(\"TradeRepublic_Portfolio\")\n",
        "      worksheet_portfolio = spreadsheet.sheet1 # get worksheet for document\n",
        "\n",
        "    worksheet_portfolio.update(\n",
        "        [portfolio_update_dataframe.columns.values.tolist()] # label\n",
        "        +\n",
        "        portfolio_update_dataframe.values.tolist() # value\n",
        "    )"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "yFSoxijzt1KL",
        "colab_type": "text"
      },
      "source": [
        "# Script Execution"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "mxxGb7wLJbBW",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "# a) define input source\n",
        "Input(config)\n",
        "\n",
        "# b) define sheet source\n",
        "Sheet_Source(config)\n",
        "\n",
        "# c) extract pdfs\n",
        "raw_text_list = ExtractPdfs(config).start()\n",
        "\n",
        "# d) process text\n",
        "list_of_order_dicts = ProcessText().get_list_of_order_dicts(raw_text_list)\n",
        "\n",
        "# e) enrich data\n",
        "enriched_list_of_order_dicts = EnrichData(config, list_of_order_dicts).start_enrichment_by_config()\n",
        "\n",
        "# f) integrate data\n",
        "dict_data_for_export = DataIntegration(config, enriched_list_of_order_dicts).get_dataframes_for_gsheet_export()\n",
        "\n",
        "# g) export sheets\n",
        "Output(config, dict_data_for_export)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "EmWOdSfnJPxD",
        "colab_type": "text"
      },
      "source": [
        "# Execution results\n",
        "\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "dyDvtfmVPyNb",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "dict_data_for_export[\"master\"]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Ej1awTSqP03y",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "dict_data_for_export[\"delta\"]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "3FB-gFcczdQy",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "dict_data_for_export[\"portfolio\"]"
      ],
      "execution_count": null,
      "outputs": []
    }
  ]
}