reconciliation_pipeline.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Fetch and preprocess data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Helper functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Define helper functions for the main script\n",
    "import pandas as pd\n",
    "\n",
    "# a function that takes a dictionary of lists of dataframes and sums up the row counts of all dataframes\n",
    "def sum_row_count(dict):\n",
    "    counts = []\n",
    "    for key in dict.keys():\n",
    "        for dflist in dict[key].values():\n",
    "            counts.append(len(dflist))\n",
    "    return sum(counts)\n",
    "\n",
    "def assert_reconcilation(dataset, transaction_df, reconciliation_dict):\n",
    "    if len(transaction_df) == sum_row_count(reconciliation_dict[dataset]['reconciliation']):\n",
    "        print(f'All {dataset} transactions accounted for in reconciliation_dict')\n",
    "    else:\n",
    "        print(f'{len(transaction_df) - sum_row_count(reconciliation_dict[dataset][\"reconciliation\"])} {dataset} transactions not accounted for in reconciliation_dict')\n",
    "    return len(transaction_df) - sum_row_count(reconciliation_dict[dataset][\"reconciliation\"])\n",
    "\n",
    "def matchByDateAndValue(df1, df1cols, df2, df2cols, timeDelta='10 days', valueDelta=0.1, absolute=False):\n",
    "    a = df1[df1cols]\n",
    "    b = df2[df2cols]\n",
    "    matched = []\n",
    "\n",
    "    def naive_transaction_matcher(x):\n",
    "        possible = b.drop(index=matched)\n",
    "        possibleByDate = (\n",
    "            possible[df2cols[0]] - x[df1cols[0]]).abs().sort_values(ascending=True)\n",
    "        possibleByDate = possibleByDate[possibleByDate < pd.Timedelta(\n",
    "            timeDelta)]\n",
    "        if len(possibleByDate) == 0:\n",
    "            return None\n",
    "        possible = possible.loc[possibleByDate.index]\n",
    "        possibleValues = possible[df2cols[1]].abs(\n",
    "        ) if absolute else possible[df2cols[1]]\n",
    "        targetValue = abs(x[df1cols[1]]) if absolute else x[df1cols[1]]\n",
    "        possibleByAmount = (\n",
    "            possibleValues - targetValue).abs().sort_values(ascending=True)\n",
    "        possibleByAmount = possibleByAmount[possibleByAmount <\n",
    "                                            possible.loc[possibleByAmount.index][df2cols[1]] * valueDelta]\n",
    "        if len(possibleByAmount) == 0:\n",
    "            return None\n",
    "        id = int(possibleByAmount.index[0])\n",
    "        matched.append(id)\n",
    "        return id\n",
    "    indexes = pd.DataFrame(a.apply(\n",
    "        naive_transaction_matcher, axis=1).dropna().astype('int'), columns=[\"df2key\"])\n",
    "    return df1.join(indexes).join(df2, on=\"df2key\")\n",
    "\n",
    "from fuzzywuzzy import fuzz\n",
    "def matchByDateAmountString(bank_transactions, platform_transactions, s1='bank.Detail', s2='oppositeAccount.name', min_similarity=80, max_days=2, max_diff=0.05):\n",
    "    # Function to return a dataframe with bank transaction index as key and a list of tuples with platform transaction index and similarity score as value\n",
    "\n",
    "    def is_date_within_range(bank_date, platform_date, max_days):\n",
    "        return abs((bank_date - platform_date).days) <= max_days\n",
    "\n",
    "    def is_amount_within_range(bank_transaction, platform_amount, max_diff):\n",
    "        bank_amount = bank_transaction['bank.Debit'] if not (pd.isna(bank_transaction['bank.Debit']) or bank_transaction['bank.Debit'] == 0 ) else bank_transaction['bank.Credit']\n",
    "        return abs(bank_amount - abs(platform_amount))/bank_amount <= max_diff if bank_amount != 0 else 0\n",
    "\n",
    "    matches = {}\n",
    "    for index, bank_row in bank_transactions.iterrows():\n",
    "        match_list = []\n",
    "        for platform_index, platform_row in platform_transactions.iterrows():\n",
    "\n",
    "            if not is_date_within_range(bank_row['bank.Post Date'], platform_row['createdAt'], max_days):\n",
    "                continue\n",
    "            \n",
    "            if not is_amount_within_range(bank_row, platform_row['amountInHostCurrency.value'], max_diff):\n",
    "                continue\n",
    "\n",
    "            # Calculate the similarity score\n",
    "            similarity_score = fuzz.partial_ratio(bank_row[s1].lower(), platform_row[s2].lower())\n",
    "\n",
    "            if similarity_score >= min_similarity:\n",
    "                platform_id = platform_transactions.loc[platform_index]['id']\n",
    "                match_list.append((platform_id, similarity_score))\n",
    "\n",
    "        # Sort the matches by similarity score, in descending order\n",
    "        match_list.sort(key=lambda x: x[1], reverse=True)\n",
    "\n",
    "        # Ensure only matches with exactly one match are added to not add ambiguous matches\n",
    "        if len(match_list) == 1:\n",
    "            matches[bank_row['bank.accountindex']] = match_list\n",
    "\n",
    "    # Convert the matches dictionary to a dataframe\n",
    "    matches_df = pd.DataFrame([(k, v[0][0]) for k, v in matches.items() if len(v) > 0], columns=['bank_accountindex', 'platform_id'])\n",
    "\n",
    "    return matches_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Fetch Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Fetch data from Open Collective, Wise, Stripe and PayPal."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import libraries\n",
    "\n",
    "import requests\n",
    "import json\n",
    "import time\n",
    "\n",
    "# Set display options\n",
    "pd.set_option('display.max_columns', None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [],
   "source": [
    "# load API tokens from .env file\n",
    "import os\n",
    "from dotenv import load_dotenv\n",
    "load_dotenv()\n",
    "octoken = os.getenv('OCTOKEN')\n",
    "wisetoken = os.getenv('WISETOKEN')\n",
    "\n",
    "# set the profile id of the target Wise profile\n",
    "wiseprofile = os.getenv('WISEPROFILE')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get data from Open Collective"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Set the host on the platform to get data for, and the period."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Host variables\n",
    "host = 'opensource'\n",
    "dateFrom = '2022-12-31T22:59:59.999Z'\n",
    "dateTo = '2023-12-31T22:59:59.999Z'\n",
    "year = '2023'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [],
   "source": [
    "# if you want to reload the data from the API, set this to True, otherwise it will load from the cached file\n",
    "reload_data = False"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Get data from the Open Collective API."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [],
   "source": [
    "# load transactions from open collective or from local file\n",
    "\n",
    "def fetch_transactions():\n",
    "    url = f\"https://opencollective.com/api/graphql/v2?personalToken={octoken}\"\n",
    "\n",
    "    # Your GraphQL query\n",
    "    query = \"\"\"\n",
    "    query TransactionsTable(\n",
    "      $hostAccount: AccountReferenceInput,\n",
    "      $limit: Int!,\n",
    "      $offset: Int!,\n",
    "      $type: TransactionType,\n",
    "      $paymentMethodType: [PaymentMethodType],\n",
    "      $dateFrom: DateTime,\n",
    "      $dateTo: DateTime,\n",
    "      $kind: [TransactionKind],\n",
    "      $includeIncognitoTransactions: Boolean,\n",
    "      $includeGiftCardTransactions: Boolean,\n",
    "      $includeChildrenTransactions: Boolean,\n",
    "      $orderBy: ChronologicalOrderInput,\n",
    "      $group: String,\n",
    "      $includeHost: Boolean,\n",
    "      $order: OrderReferenceInput\n",
    "    ) {\n",
    "      transactions(\n",
    "        host: $hostAccount\n",
    "        limit: $limit\n",
    "        offset: $offset\n",
    "        type: $type\n",
    "        paymentMethodType: $paymentMethodType\n",
    "        dateFrom: $dateFrom\n",
    "        dateTo: $dateTo\n",
    "        kind: $kind\n",
    "        includeIncognitoTransactions: $includeIncognitoTransactions\n",
    "        includeGiftCardTransactions: $includeGiftCardTransactions\n",
    "        includeChildrenTransactions: $includeChildrenTransactions\n",
    "        includeDebts: true\n",
    "        orderBy: $orderBy\n",
    "        group: $group\n",
    "        includeHost: $includeHost\n",
    "        order: $order\n",
    "      ) {\n",
    "        totalCount\n",
    "        offset\n",
    "        limit\n",
    "        nodes {\n",
    "          createdAt\n",
    "          id\n",
    "          merchantId\n",
    "          group\n",
    "          type\n",
    "          kind\n",
    "          description\n",
    "          isRefunded\n",
    "          isRefund\n",
    "          refundTransaction {\n",
    "            id\n",
    "          }\n",
    "          isOrderRejected\n",
    "          amount {\n",
    "            currency\n",
    "            value\n",
    "          }\n",
    "          amountInHostCurrency {\n",
    "            currency\n",
    "            value\n",
    "          }\n",
    "          netAmount {\n",
    "            currency\n",
    "            value\n",
    "          }\n",
    "          netAmountInHostCurrency {\n",
    "            currency\n",
    "            value\n",
    "          }\n",
    "          paymentProcessorFee {\n",
    "            currency\n",
    "            value\n",
    "          }\n",
    "          balanceInHostCurrency {\n",
    "            currency\n",
    "            value\n",
    "          }\n",
    "          account {\n",
    "            id\n",
    "            legacyId\n",
    "            name\n",
    "            slug\n",
    "            isIncognito\n",
    "            type\n",
    "            ... on AccountWithParent {\n",
    "              parent {\n",
    "                id\n",
    "                name\n",
    "                slug\n",
    "                type\n",
    "              }\n",
    "            }\n",
    "          }\n",
    "          oppositeAccount {\n",
    "            id\n",
    "            legacyId\n",
    "            name\n",
    "            slug\n",
    "            isIncognito\n",
    "            type\n",
    "          }\n",
    "          order {\n",
    "            id\n",
    "            description\n",
    "            memo\n",
    "            pendingContributionData {\n",
    "              memo\n",
    "            }\n",
    "          }\n",
    "          paymentMethod {\n",
    "            name\n",
    "            service\n",
    "            sourcePaymentMethod {\n",
    "              id\n",
    "            }\n",
    "            type\n",
    "          }\n",
    "          expense {\n",
    "            id\n",
    "            type\n",
    "            description\n",
    "            invoiceInfo\n",
    "            tags\n",
    "            virtualCard {\n",
    "              id\n",
    "            }\n",
    "            payee {\n",
    "              id\n",
    "              name\n",
    "              slug\n",
    "              type\n",
    "            }\n",
    "          }\n",
    "          payoutMethod {\n",
    "            type\n",
    "          }\n",
    "          giftCardEmitterAccount {  \n",
    "            id\n",
    "            name\n",
    "            slug\n",
    "          }\n",
    "        }\n",
    "      }\n",
    "    }\n",
    "    \"\"\"\n",
    "\n",
    "    headers = {\n",
    "        \"Authorization\": f\"Bearer {octoken}\",\n",
    "        \"Content-Type\": \"application/json\"\n",
    "    }\n",
    "\n",
    "    # Initial variables setup\n",
    "    variables = {\n",
    "        \"hostAccount\": {\"slug\": host},\n",
    "        \"includeIncognitoTransactions\": True,\n",
    "        \"includeChildrenTransactions\": True,\n",
    "        \"limit\": 10000,  # Adjust if necessary but keep a sensible number to avoid server strain\n",
    "        \"offset\": 0,  # Will be adjusted for each subsequent request\n",
    "        \"dateFrom\": dateFrom,\n",
    "        \"dateTo\": dateTo,\n",
    "        \"orderBy\": {\"field\": \"CREATED_AT\", \"direction\": \"DESC\"},\n",
    "        \"includeHost\": True\n",
    "    }\n",
    "\n",
    "    all_transactions = []  # To hold all transactions\n",
    "    while True:\n",
    "\n",
    "        # Try catch around json_data = response.json() to avoid errors\n",
    "\n",
    "        max_retries = 10  # Setting the maximum number of retries\n",
    "        retries = 0  # Initial retry count\n",
    "        response = ''\n",
    "\n",
    "        while retries < max_retries:\n",
    "            try:\n",
    "                # Make the HTTP request\n",
    "                response = requests.post(\n",
    "                    url, json={'query': query, 'variables': variables}, headers=headers)\n",
    "                json_data = response.json()\n",
    "                break\n",
    "            except Exception as e:\n",
    "                print(\"Request failed. Attempt:\", retries + 1)\n",
    "                print(e)\n",
    "                retries += 1\n",
    "                time.sleep(10)\n",
    "\n",
    "        if retries == max_retries:\n",
    "            print(\"Maximum retry attempts reached. Exiting.\")\n",
    "        # Extract data\n",
    "        transactions = json_data['data']['transactions']['nodes']\n",
    "        all_transactions.extend(transactions)\n",
    "\n",
    "        # Pagination: Update offset\n",
    "        variables['offset'] += variables['limit']\n",
    "\n",
    "        # Check if all transactions are fetched\n",
    "        if len(all_transactions) >= json_data['data']['transactions']['totalCount']:\n",
    "            break\n",
    "\n",
    "        # print progress\n",
    "        print(f'Fetched {len(all_transactions)} transactions')\n",
    "        # sleep for 1 seconds to avoid server strain\n",
    "        time.sleep(1)\n",
    "\n",
    "    return all_transactions\n",
    "\n",
    "# if account has property parent, replace account with parent\n",
    "def replace_account_with_parent(transaction):\n",
    "    if 'parent' in transaction['account']:\n",
    "        transaction['account'] = transaction['account']['parent']\n",
    "    return transaction\n",
    "\n",
    "def post_process_transactions(all_transactions):\n",
    "    return list(map(replace_account_with_parent, all_transactions))\n",
    "\n",
    "if reload_data:\n",
    "  all_transactions = fetch_transactions()\n",
    "  # dump all transactions to a json file\n",
    "  post_process_transactions(all_transactions)\n",
    "  with open(f'data/{host}/{year}/{host}_{year}_all_platform_transactions.json', 'w') as f:\n",
    "    json.dump(all_transactions, f, indent=2)\n",
    "  # convert the json file to a dataframe\n",
    "  df_platform = pd.json_normalize(all_transactions)\n",
    "  # save dataframe as pickle\n",
    "  df_platform.to_pickle(\n",
    "      f'data/{host}/{year}/df_{host}_{year}_all_platform_transactions.pkl')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Get data from the Wise API."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Define the base URL for the Wise API\n",
    "BASE_URL = 'https://api.transferwise.com/v1/transfers'\n",
    "\n",
    "def fetch_all_transfers(profile_id=None, status=None, source_currency=None, \n",
    "                        target_currency=None, created_date_start=None, \n",
    "                        created_date_end=None, limit=100):\n",
    "    headers = {\n",
    "        'Authorization': f'Bearer {wisetoken}'\n",
    "    }\n",
    "    offset = 0\n",
    "    all_transfers = []\n",
    "\n",
    "    while True:\n",
    "        # Prepare query parameters\n",
    "        query_params = {'limit': limit, 'offset': offset}\n",
    "        if profile_id:\n",
    "            query_params['profile'] = profile_id\n",
    "        if status:\n",
    "            query_params['status'] = status\n",
    "        if source_currency:\n",
    "            query_params['sourceCurrency'] = source_currency\n",
    "        if target_currency:\n",
    "            query_params['targetCurrency'] = target_currency\n",
    "        if created_date_start:\n",
    "            query_params['createdDateStart'] = created_date_start\n",
    "        if created_date_end:\n",
    "            query_params['createdDateEnd'] = created_date_end\n",
    "\n",
    "        # Make the GET request to the Wise API\n",
    "        response = requests.get(BASE_URL, headers=headers, params=query_params)\n",
    "        data = response.json()\n",
    "\n",
    "        # Break if there are no more results\n",
    "        if not data:\n",
    "            break\n",
    "\n",
    "        for transfer in data:\n",
    "            all_transfers.append({\n",
    "                'id': transfer['id'],\n",
    "                'user': transfer['user'],\n",
    "                'targetAccount': transfer['targetAccount'],\n",
    "                'sourceAccount': transfer['sourceAccount'],\n",
    "                'quote': transfer['quote'],\n",
    "                'quoteUuid': transfer['quoteUuid'],\n",
    "                'status': transfer['status'],\n",
    "                'reference': transfer['reference'],\n",
    "                'rate': transfer['rate'],\n",
    "                'created': transfer['created'],\n",
    "                'business': transfer['business'],\n",
    "                'transferRequest': transfer['transferRequest'],\n",
    "                'details.reference': transfer.get('details', {}).get('reference'),\n",
    "                'hasActiveIssues': transfer['hasActiveIssues'],\n",
    "                'sourceCurrency': transfer['sourceCurrency'],\n",
    "                'sourceValue': transfer['sourceValue'],\n",
    "                'targetCurrency': transfer['targetCurrency'],\n",
    "                'targetValue': transfer['targetValue'],\n",
    "                'customerTransactionId': transfer['customerTransactionId']\n",
    "            })\n",
    "            \n",
    "        # Update the offset for the next iteration\n",
    "        offset += limit\n",
    "\n",
    "    return all_transfers\n",
    "\n",
    "if reload_data:\n",
    "    transfers = fetch_all_transfers(profile_id=wiseprofile, created_date_start=dateFrom, created_date_end=dateTo)\n",
    "    # create transfers dataframe\n",
    "    df_wise = pd.DataFrame()\n",
    "    for transfer in transfers:\n",
    "        # add to dataframe with concat\n",
    "        df_wise = pd.concat([df_wise, pd.DataFrame([transfer])])\n",
    "    # save dataframe as pickle\n",
    "    df_wise.to_pickle(f'data/{host}/{year}/df_{host}_{year}_wise_transactions.pkl')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load Cached Data\n",
    "\n",
    "Loading data from files with Stripe and Paypal transactions, and loading cached data from Wise and Paypal if available."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Data sources:\n",
    " * Platform data is retrieved from the Open Collective API\n",
    " * Wise data is retrieved from the Wise API\n",
    " * Stripe data is downloaded from https://dashboard.stripe.com/reports/balance Balance change from activity\n",
    "   * \"Balance change from activity\" with Report format \"Itemized\" and Reporting category \"All\"\n",
    " * Paypal data is downloaded from https://www.paypal.com/reports/dlog\n",
    "   * \"Activity report\" with Transaction type \"Balance affecting\" in CSV format\n",
    " * Bank data are itemized CSV exports\n",
    "   * Columns names in export need to be: \"Post Date\", \"Customer Ref\", \"Debit\", \"Credit\", \"BAI Description\", \"Description\", \"Detail\", \"Account Number\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [],
   "source": [
    "# import datasets from local files\n",
    "\n",
    "import glob\n",
    "\n",
    "# load open collective data\n",
    "df_platform = pd.read_pickle(f'data/{host}/{year}/df_{host}_{year}_all_platform_transactions.pkl')\n",
    "\n",
    "# load stripe data\n",
    "df_stripe = pd.read_csv(f'data/{host}/{year}/{host}_{year}_stripe_transactions.csv', low_memory=False)\n",
    "\n",
    "# load wise data\n",
    "df_wise = pd.read_pickle(f'data/{host}/{year}/df_{host}_{year}_wise_transactions.pkl')\n",
    "\n",
    "# load paypal data\n",
    "dir = f'data/{host}/{year}/paypal'\n",
    "df_paypal = pd.concat([pd.read_csv(f) for f in glob.glob(f'{dir}/*.csv')], ignore_index=True)\n",
    "df_paypal = df_paypal.dropna(axis=1, how='all')\n",
    "\n",
    "# load bank data\n",
    "dir = f'data/{host}/{year}/oscbank_1'\n",
    "df_bank_1 = pd.concat([pd.read_csv(f) for f in glob.glob(f'{dir}/*.csv')], ignore_index=True)\n",
    "df_bank_1 = df_bank_1.dropna(axis=1, how='all')\n",
    "\n",
    "dir = f'data/{host}/{year}/oscbank_2'\n",
    "df_bank_2 = pd.concat([pd.read_csv(f) for f in glob.glob(f'{dir}/*.csv')], ignore_index=True)\n",
    "df_bank_2 = df_bank_2.dropna(axis=1, how='all')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Sanitizing Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Explicitly keep only the columns we need. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Keep only the columns we need in Stripe\n",
    "\n",
    "df_stripe = df_stripe[[\n",
    "    'balance_transaction_id', \n",
    "    'created_utc', \n",
    "    'created', \n",
    "    'available_on_utc',\n",
    "    'available_on',\n",
    "    'currency',\n",
    "    'gross',\n",
    "    'fee',\n",
    "    'net',\n",
    "    'reporting_category',\n",
    "    'source_id',\n",
    "    'description',\n",
    "    'customer_facing_amount',\n",
    "    'customer_facing_currency',\n",
    "    'automatic_payout_id',\n",
    "    'automatic_payout_effective_at_utc',\n",
    "    'automatic_payout_effective_at',\n",
    "    'customer_id',\n",
    "    'customer_description',\n",
    "    'charge_id',\n",
    "    'payment_intent_id',\n",
    "    'charge_created_utc',\n",
    "    'charge_created',\n",
    "    'payment_method_type',\n",
    "    'card_brand',\n",
    "    'card_funding',\n",
    "    'card_country',\n",
    "    'payment_metadata[from]',\n",
    "    'payment_metadata[to]'\n",
    "]]\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Strip trailing white spaces from column names."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Strip trailing whitespace from column names\n",
    "\n",
    "df_bank_1.columns = df_bank_1.columns.str.strip()\n",
    "df_bank_2.columns = df_bank_1.columns.str.strip()\n",
    "df_stripe.columns = df_stripe.columns.str.strip()\n",
    "df_wise.columns = df_wise.columns.str.strip()\n",
    "df_paypal.columns = df_paypal.columns.str.strip()\n",
    "df_platform.columns = df_platform.columns.str.strip()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Convert created at columns to datetime."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Convert date formats \n",
    "\n",
    "df_platform['createdAt'] = pd.to_datetime(df_platform['createdAt']).dt.tz_convert(None)\n",
    "\n",
    "df_stripe['created_utc'] = pd.to_datetime(df_stripe['created_utc'], utc=True).dt.tz_convert(None)\n",
    "\n",
    "df_wise['created'] = pd.to_datetime(df_wise['created'], utc=True).dt.tz_convert(None)\n",
    "\n",
    "df_bank_1['Post Date'] = pd.to_datetime(df_bank_1['Post Date'], utc=True).dt.tz_convert(None)\n",
    "df_bank_2['Post Date'] = pd.to_datetime(df_bank_2['Post Date'], utc=True).dt.tz_convert(None)\n",
    "\n",
    "df_paypal['Datetime'] = pd.to_datetime(df_paypal['Date'] + ' ' + df_paypal['Time'], utc=True)\n",
    "tz_offset = {\n",
    "    'PST': -8,\n",
    "    'PDT': -7,\n",
    "}\n",
    "\n",
    "import datetime\n",
    "def fix_tz(row):\n",
    "    tz=datetime.timezone(datetime.timedelta(hours=tz_offset[row['TimeZone']]))\n",
    "    row['Datetime'] = pd.to_datetime(row['Datetime'].replace(tzinfo=tz)).tz_convert(None)\n",
    "    return row\n",
    "    \n",
    "df_paypal = df_paypal.apply(fix_tz, axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Sanitize Paypal data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Sanitize Paypal data by removing commas from numbers and removing commas from Note column\n",
    "\n",
    "df_paypal['Net'] = df_paypal['Net'].replace(',', '', regex=True).astype(dtype=float)\n",
    "df_paypal['Gross'] = df_paypal['Gross'].replace(',', '', regex=True).astype(dtype=float)\n",
    "df_paypal['Fee'] = df_paypal['Fee'].replace(',', '', regex=True).astype(dtype=float)\n",
    "df_paypal['Balance'] = df_paypal['Balance'].replace(',', '', regex=True).astype(dtype=float)\n",
    "df_paypal['Note'] = df_paypal[~df_paypal['Note'].isna()]['Note'].str.replace(',', '')\n",
    "df_paypal['Type'] = df_paypal['Type'].str.strip()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Filtering out Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Filter out failed, cancelled and pending transactions from Stripe, Paypal and Wise as these have not affected the balance.\n",
    "\n",
    "TODO: If we switch the Stripe datasource to only use the payouts file, this step is probably not necessary as that file only includes transactions that have affected the balance."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [],
   "source": [
    "# only keep rows in paypal with status in ['Completed', 'Processed']\n",
    "# df_paypal = df_paypal[df_paypal['Status'].isin(['Completed', 'Processed'])]\n",
    "df_paypal = df_paypal[df_paypal['Balance Impact'] != 'Memo']\n",
    "\n",
    "# remove cancelled from Wise\n",
    "df_wise = df_wise[df_wise['status'] != 'cancelled']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Combining"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Combine the bank files into one dataframe. We can do this because the account number is stated in a column of the bank exports."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_banks = pd.concat([df_bank_1, df_bank_2]).reset_index()\n",
    "\n",
    "# create a column accountindex that concatenates bank.Account Number and bank.index to create a unique identifier for each bank transaction\n",
    "df_banks['accountindex'] = df_banks['Account Number'].astype(str) + '-' + df_banks['index'].astype(str)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Renaming"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Rename columns to not confuse them after merging with other data sources later."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Prefix wise columns with wise. and rename id to wise.id\n",
    "df_wise = df_wise.set_index('id', drop=False).rename_axis(None).add_prefix('wise.')\n",
    "# Prefix stripe columns with stripe. and rename id to stripe.id\n",
    "df_stripe = df_stripe.set_index('balance_transaction_id', drop=False).rename_axis(None).add_prefix('stripe.')\n",
    "# Prefix paypal columns with paypal. and rename id to paypal.id. Paypal Transaction ID is not a unique identifier, so we keep the index.\n",
    "df_paypal = df_paypal.rename_axis(None).add_prefix('paypal.')\n",
    "# Prefix bank columns with bank. and rename index to bank.id\n",
    "df_banks = df_banks.rename_axis(None).add_prefix('bank.')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Reconcile transactions"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We are now ready to start matching transactions on Wise, Stripe, Paypal and bank accounts with known events on the platform.\n",
    "\n",
    "Our goal is to be able to reconcile as many transactions as we can automatically. \n",
    "\n",
    "For accounting purposes the most important thing is to use the information on the platform to explain transactions on Wise, Stripe, Paypal and bank accounts. \n",
    "\n",
    "At the end of this section we will end up with the following outputs:\n",
    "\n",
    "  * **Wise Reconciled**: Wise transactions matched with their corresponding platform transaction or a corresponding balance transfer from the bank.\n",
    "  * **Wise Remainder**: Wise transactions for which no platform transaction or bank transaction was found.\n",
    "  * **Stripe Reconciled**: Stripe transactions matched with their corresponding platform transaction or a corresponding balance transfer to the bank.\n",
    "  * **Stripe Remainder**: Stripe transactions for which no platform transaction or bank transaction was found.\n",
    "  * **Paypal Reconciled**: Paypal transactions matched with their corresponding platform transaction or a corresponding balance transfer to or from the bank.\n",
    "  * **Paypal Remainder**: Paypal transactions for which no platform transaction or bank transaction was found.\n",
    "  * **Bank Reconciled**: Bank transactions matched with corresponding platform transactions or balance transfers to/from Wise, Stripe or Paypal.\n",
    "  * **Bank Remainder**: Bank transactions that have not been reconciled with the platform, Stripe, Wise or Paypal.\n",
    "  * **Platform Reconciled**: Platform transactions that have been reconciled. \n",
    "  * **Platform Remainder**: Platform transactions for which no corresponding transaction has been found on Wise, Stripe, Paypal or bank accounts."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create reconciliation dict"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We will save our reconciliation results in a dictionary."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [],
   "source": [
    "reconciliation_dict = {\n",
    "    'wise': {\n",
    "        'all': df_wise,\n",
    "        'reconciliation': {\n",
    "            'reconciled_platform': {},\n",
    "            'reconciled_bank': {},\n",
    "            'unreconciled': {}\n",
    "        }\n",
    "    },\n",
    "    'stripe': {\n",
    "        'all': df_stripe,\n",
    "        'reconciliation': {\n",
    "            'reconciled_platform': {},\n",
    "            'reconciled_bank': {},\n",
    "            'reconciled_other': {},\n",
    "            'unreconciled': {}\n",
    "        },\n",
    "    },\n",
    "    'paypal': {\n",
    "        'all': df_paypal,\n",
    "        'reconciliation': {\n",
    "            'reconciled_platform': {},\n",
    "            'reconciled_bank': {},\n",
    "            'reconciled_other': {},\n",
    "            'unreconciled': {}\n",
    "        },\n",
    "\n",
    "    },\n",
    "    'bank': {\n",
    "        'all': df_banks,\n",
    "        'reconciliation': {\n",
    "            'reconciled_platform': {},\n",
    "            'reconciled_wise': {},\n",
    "            'reconciled_stripe': {},\n",
    "            'reconciled_paypal': {},\n",
    "            'reconciled_other': {},\n",
    "            'unreconciled': {}\n",
    "        },\n",
    "    },\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Wise reconciliation"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Reconcile Wise with platform"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Wise is pretty easy to reconcile since it should only be money out for expense and invoice payments and occational returns the funds could not be sent. We start by looking into the transactions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">wise.sourceValue</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>sum</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>wise.status</th>\n",
       "      <th>wise.sourceCurrency</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>funds_refunded</th>\n",
       "      <th>USD</th>\n",
       "      <td>54</td>\n",
       "      <td>338773.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">outgoing_payment_sent</th>\n",
       "      <th>EUR</th>\n",
       "      <td>3</td>\n",
       "      <td>1719.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GBP</th>\n",
       "      <td>1</td>\n",
       "      <td>46.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>USD</th>\n",
       "      <td>3405</td>\n",
       "      <td>12600866.08</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                          wise.sourceValue             \n",
       "                                                     count          sum\n",
       "wise.status           wise.sourceCurrency                              \n",
       "funds_refunded        USD                               54    338773.20\n",
       "outgoing_payment_sent EUR                                3      1719.00\n",
       "                      GBP                                1        46.86\n",
       "                      USD                             3405  12600866.08"
      ]
     },
     "execution_count": 93,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_wise.groupby(['wise.status', 'wise.sourceCurrency']).agg({'wise.sourceValue': ['count', 'sum']})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Merge the Wise transactions table with the potentially Wise-related platform transactions table, joining on the merchantId on the platform with the Wise id from the Wise transaction table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Successfully matched 3403 Wise transactions with platform transactions\n",
      "Unreconciled Stripe transactions: 60 out of 3463\n"
     ]
    }
   ],
   "source": [
    "df_wise_platform = pd.merge(df_platform.dropna(subset=['merchantId']), df_wise.dropna(subset=['wise.id']).astype({'wise.id': 'string'}), left_on='merchantId', right_on='wise.id', how='inner')\n",
    "df_wise_platform.drop_duplicates(subset=['wise.id'], inplace=True)\n",
    "\n",
    "print(f'Successfully matched {len(df_wise_platform)} Wise transactions with platform transactions')\n",
    "\n",
    "# remainder of Stripe transactions by filter out on the unique balance_transaction_id\n",
    "df_left_wise = df_wise[~df_wise['wise.id'].astype({'wise.id': 'string'}).isin(df_wise_platform['wise.id'])]\n",
    "print(f'Unreconciled Stripe transactions: {len(df_left_wise)} out of {len(df_wise)}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Reconcile Wise with bank"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Setting the known Wise trace account reference that appears on bank statements"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [],
   "source": [
    "WISE_TRACE_ACCOUNT = 'XXXXXX7206'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finding the bank transactions that are known to have been transfers to the Wise account."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_banks_wise = df_banks[df_banks['bank.Detail'].str.contains(WISE_TRACE_ACCOUNT)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finding the Wise transactions that correspond to the known Wise balance transfers from the bank accounts.\n",
    "\n",
    "Because the bank transactions also cover transfer fees the amounts are not exact.\n",
    "\n",
    "We use a function to find incoming balance transfers to the Wise account within 2 days and within 95% of the amount transferred from the bank.\n",
    "\n",
    "TODO: When accounting we need to account for the balance transfer fees. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wise transactions with a corresponding Bank transaction: 34\n",
      "Wise transactions without either a corresponding Platform transaction or a Bank transaction: 26 out of 3463\n",
      "Percentage of Wise transactions that have a corresponding transaction either on the Platform or on the Bank: 99.25%\n"
     ]
    }
   ],
   "source": [
    "# importing a function that can match rows in two dataframes by date and value\n",
    "\n",
    "df_wise_bank = matchByDateAndValue(df_left_wise, ['wise.created', 'wise.sourceValue'], df_banks_wise, ['bank.Post Date', 'bank.Debit'] , '2 days', 0.05).dropna(subset=['df2key'])\n",
    "df_left_wise = df_left_wise[~df_left_wise['wise.id'].isin(df_wise_bank['wise.id'])]\n",
    "print(f\"Wise transactions with a corresponding Bank transaction: {len(df_wise_bank)}\")\n",
    "print(f\"Wise transactions without either a corresponding Platform transaction or a Bank transaction: {len(df_left_wise)} out of {len(df_wise)}\")\n",
    "print(f\"Percentage of Wise transactions that have a corresponding transaction either on the Platform or on the Bank: {round((len(df_wise_bank)+len(df_wise_platform))/len(df_wise)*100, 2)}%\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Analyzing Wise remainder"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">wise.sourceValue</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>sum</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>wise.status</th>\n",
       "      <th>wise.sourceCurrency</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>funds_refunded</th>\n",
       "      <th>USD</th>\n",
       "      <td>18</td>\n",
       "      <td>263010.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>outgoing_payment_sent</th>\n",
       "      <th>USD</th>\n",
       "      <td>8</td>\n",
       "      <td>361296.12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                          wise.sourceValue           \n",
       "                                                     count        sum\n",
       "wise.status           wise.sourceCurrency                            \n",
       "funds_refunded        USD                               18  263010.86\n",
       "outgoing_payment_sent USD                                8  361296.12"
      ]
     },
     "execution_count": 98,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_left_wise.groupby(['wise.status', 'wise.sourceCurrency']).agg({'wise.sourceValue': ['count', 'sum']})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Adding Wise to reconciliation dict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {},
   "outputs": [],
   "source": [
    "reconciliation_dict['wise']['reconciliation']['reconciled_bank']['wise_bank'] = df_wise_bank\n",
    "reconciliation_dict['wise']['reconciliation']['reconciled_platform']['wise_platform'] = df_wise_platform\n",
    "reconciliation_dict['wise']['reconciliation']['unreconciled']['unreconciled_wise'] = df_left_wise"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "All wise transactions accounted for in reconciliation_dict\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 100,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "assert_reconcilation('wise', df_wise, reconciliation_dict)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Stripe reconciliation"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "OSC used Stripe in 2023 for incoming contributions and for virtual cards. That makes reconcilliation of Stripe slightly complicated. \n",
    "\n",
    "We start by analyzing the transactions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">stripe.gross</th>\n",
       "      <th>stripe.fee</th>\n",
       "      <th>stripe.net</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>sum</th>\n",
       "      <th>sum</th>\n",
       "      <th>sum</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>stripe.reporting_category</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>charge</th>\n",
       "      <td>131783</td>\n",
       "      <td>4480808.61</td>\n",
       "      <td>425873.98</td>\n",
       "      <td>4054934.63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>charge_failure</th>\n",
       "      <td>18</td>\n",
       "      <td>-3618.00</td>\n",
       "      <td>43.05</td>\n",
       "      <td>-3661.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dispute</th>\n",
       "      <td>47</td>\n",
       "      <td>-38726.33</td>\n",
       "      <td>705.00</td>\n",
       "      <td>-39431.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dispute_reversal</th>\n",
       "      <td>19</td>\n",
       "      <td>190.13</td>\n",
       "      <td>-270.00</td>\n",
       "      <td>460.13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>fee</th>\n",
       "      <td>680</td>\n",
       "      <td>-3569.72</td>\n",
       "      <td>0.00</td>\n",
       "      <td>-3569.72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>issuing_authorization_hold</th>\n",
       "      <td>2293</td>\n",
       "      <td>-251849.40</td>\n",
       "      <td>0.00</td>\n",
       "      <td>-251849.40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>issuing_authorization_release</th>\n",
       "      <td>2294</td>\n",
       "      <td>251813.40</td>\n",
       "      <td>0.00</td>\n",
       "      <td>251813.40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>issuing_dispute</th>\n",
       "      <td>6</td>\n",
       "      <td>290.14</td>\n",
       "      <td>0.00</td>\n",
       "      <td>290.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>issuing_transaction</th>\n",
       "      <td>2222</td>\n",
       "      <td>-213310.19</td>\n",
       "      <td>0.00</td>\n",
       "      <td>-213310.19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>refund</th>\n",
       "      <td>508</td>\n",
       "      <td>-22306.58</td>\n",
       "      <td>0.00</td>\n",
       "      <td>-22306.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>topup</th>\n",
       "      <td>8</td>\n",
       "      <td>191000.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>191000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>topup_reversal</th>\n",
       "      <td>1</td>\n",
       "      <td>-5000.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>-5000.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              stripe.gross             stripe.fee  stripe.net\n",
       "                                     count         sum        sum         sum\n",
       "stripe.reporting_category                                                    \n",
       "charge                              131783  4480808.61  425873.98  4054934.63\n",
       "charge_failure                          18    -3618.00      43.05    -3661.05\n",
       "dispute                                 47   -38726.33     705.00   -39431.33\n",
       "dispute_reversal                        19      190.13    -270.00      460.13\n",
       "fee                                    680    -3569.72       0.00    -3569.72\n",
       "issuing_authorization_hold            2293  -251849.40       0.00  -251849.40\n",
       "issuing_authorization_release         2294   251813.40       0.00   251813.40\n",
       "issuing_dispute                          6      290.14       0.00      290.14\n",
       "issuing_transaction                   2222  -213310.19       0.00  -213310.19\n",
       "refund                                 508   -22306.58       0.00   -22306.58\n",
       "topup                                    8   191000.00       0.00   191000.00\n",
       "topup_reversal                           1    -5000.00       0.00    -5000.00"
      ]
     },
     "execution_count": 101,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_stripe.groupby('stripe.reporting_category').agg({'stripe.gross': ['count', 'sum'], 'stripe.fee': ['sum'], 'stripe.net': ['sum']})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Reconcile contributions"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For each row in in the Stripe table, we find the corresponding row in df_platform, matching the Stripe id on the platform merchantId.\n",
    "\n",
    "Note: It turns out that quite a large number of Stripe contributions do not get assigned STRIPE as paymentMethod.service on the platform. We need to merge on merchantId without first filtering out known Stripe transactions from the platform."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Successfully matched 131332 Stripe transactions with platform contributions\n",
      "Unreconciled Stripe transactions: 8547 out of 139879\n"
     ]
    }
   ],
   "source": [
    "df_stripe_contributions = df_stripe[df_stripe['stripe.reporting_category'] == 'charge']\n",
    "df_stripe_platform_contributions = pd.merge(df_platform.dropna(subset=['merchantId']), df_stripe_contributions.dropna(subset=['stripe.source_id']), left_on='merchantId', right_on='stripe.source_id', how='inner')\n",
    "df_stripe_platform_contributions.drop_duplicates(subset=['stripe.balance_transaction_id'], inplace=True)\n",
    "\n",
    "print(f'Successfully matched {len(df_stripe_platform_contributions)} Stripe transactions with platform contributions')\n",
    "\n",
    "# remainder of Stripe transactions by filter out on the unique balance_transaction_id\n",
    "df_left_stripe = df_stripe[~df_stripe['stripe.balance_transaction_id'].isin(df_stripe_platform_contributions['stripe.balance_transaction_id'])]\n",
    "print(f'Unreconciled Stripe transactions: {len(df_left_stripe)} out of {len(df_stripe)}')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Because paymentMethod is missing for a portion of Stripe contributions on the platform, we can't be sure about if there are platform transactions that have been created by a contribution that was supposed to go through Stripe but that never ended up on Stripe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unreconciled Stripe transactions: 8547 out of 139879\n"
     ]
    }
   ],
   "source": [
    "print(f'Unreconciled Stripe transactions: {len(df_left_stripe)} out of {len(df_stripe)}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Reconcile refunds"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Getting the list of transactions that have been refunded in Stripe\n",
    "df_stripe_refunds = df_left_stripe[df_left_stripe['stripe.reporting_category'] == 'refund']\n",
    "# Get refunded fees from contributions from Stripe where stripe.reporting_category is fee and net is positive\n",
    "df_stripe_refunded_fees = df_stripe[(df_stripe['stripe.reporting_category'] == 'fee') & (df_stripe['stripe.net'] > 0)]\n",
    "# Concatenate the two dataframes\n",
    "df_stripe_all_refunds = pd.concat([df_stripe_refunds, df_stripe_refunded_fees])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Successfully matched 432 Stripe transactions with refund transactions on platform\n"
     ]
    }
   ],
   "source": [
    "# Matching the refunds with the platform contributions on stripe.charge_id\n",
    "df_stripe_platform_contrubution_refunds = pd.merge(df_platform.dropna(subset=['merchantId']), df_stripe_all_refunds.dropna(subset=['stripe.charge_id']), left_on='merchantId', right_on='stripe.charge_id', how='inner')\n",
    "df_stripe_platform_contrubution_refunds.drop_duplicates(subset=['stripe.balance_transaction_id'], inplace=True)\n",
    "print(f'Successfully matched {len(df_stripe_platform_contrubution_refunds)} Stripe transactions with refund transactions on platform')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unreconciled Stripe transactions: 8115 out of 139879\n"
     ]
    }
   ],
   "source": [
    "df_left_stripe = df_left_stripe[~df_left_stripe['stripe.balance_transaction_id'].isin(df_stripe_platform_contrubution_refunds['stripe.balance_transaction_id'])]\n",
    "print(f'Unreconciled Stripe transactions: {len(df_left_stripe)} out of {len(df_stripe)}')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Reconcile fees"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Successfully identified 467 Stripe transactions as application fees\n",
      "Unreconciled Stripe transactions: 7648 out of 139879\n"
     ]
    }
   ],
   "source": [
    "df_stripe_application_fees = df_left_stripe[df_left_stripe['stripe.reporting_category'] == 'fee']\n",
    "df_left_stripe = df_left_stripe[~df_left_stripe['stripe.balance_transaction_id'].isin(df_stripe_application_fees['stripe.balance_transaction_id'])]\n",
    "print(f'Successfully identified {len(df_stripe_application_fees)} Stripe transactions as application fees')\n",
    "print(f'Unreconciled Stripe transactions: {len(df_left_stripe)} out of {len(df_stripe)}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Reconcile Stripe virtual card transactions"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Filter out hold and release transactions that balance out"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Start by cancelling out issuing_authorization_hold with corresponding issuing_authorization_release as these do not affect the balance. \n",
    "\n",
    "Hold occurs when a virtual card is used to make a purchase, generating an authorization request. If approved, the amount is held from the Issuing balance until the authorization is captured, voided, or expires without capture. When an authorization is captured, the status is set to 'closed', and the held purchase amount is released.\n",
    "\n",
    "We find the transactions that balance out and put them aside."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {},
   "outputs": [],
   "source": [
    "# get all virtual card transactions from Stripe\n",
    "df_stripe_issuing = df_left_stripe[df_left_stripe['stripe.reporting_category'].isin(['issuing_authorization_hold', 'issuing_authorization_release', 'issuing_dispute', 'issuing_transaction'])]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Filtered out 4586 hold & release transactions\n",
      "Net sum of filtered out hold & release transactions: 0.0\n",
      "Unreconciled Stripe transactions: 3062 out of 139879\n"
     ]
    }
   ],
   "source": [
    "# filter out all issuing_authorization_hold that have a corresponding issuing_authorization_release with the same stripe.source_id\n",
    "df_stripe_issuing_authorization_hold = df_stripe_issuing[df_stripe_issuing['stripe.reporting_category'] == 'issuing_authorization_hold']\n",
    "df_stripe_issuing_authorization_release = df_stripe_issuing[df_stripe_issuing['stripe.reporting_category'] == 'issuing_authorization_release']\n",
    "\n",
    "# get all rows where stripe.source_id is in both dataframes so the two balance out\n",
    "df_stripe_issuing_authorization_ids = pd.merge(df_stripe_issuing_authorization_hold, df_stripe_issuing_authorization_release, on='stripe.source_id', how='inner')\n",
    "df_stripe_issuing_authorization_ids.drop_duplicates(subset=['stripe.balance_transaction_id_x'], inplace=True)\n",
    "\n",
    "df_stripe_authorization = df_stripe_issuing[df_stripe_issuing['stripe.source_id'].isin(df_stripe_issuing_authorization_ids['stripe.source_id'])]\n",
    "\n",
    "# filter out all rows where stripe.source_id is in df_stripe_issuing_authorization_ids\n",
    "df_left_stripe = df_left_stripe[~df_left_stripe['stripe.balance_transaction_id'].isin(df_stripe_authorization['stripe.balance_transaction_id'])]\n",
    "\n",
    "# confirm that the sum of the net column of all rows in df_stripe_issuing_authorization_hold is 0, round to avoid floating point errors\n",
    "print(f'Filtered out {len(df_stripe_authorization)} hold & release transactions')\n",
    "print(f'Net sum of filtered out hold & release transactions: {round(df_stripe_authorization[\"stripe.net\"].sum(),3)}')\n",
    "print(f'Unreconciled Stripe transactions: {len(df_left_stripe)} out of {len(df_stripe)}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Reconcile completed virtual card transactions"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "NOTE: For some reason there are virtual card transactions that do not have an expense.virtualCard.id"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get stripe transactions where stripe.reporting_category is issuing_transaction\n",
    "df_stripe_issuing_transaction = df_left_stripe[df_left_stripe['stripe.reporting_category'] == 'issuing_transaction']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Successfully matched 2180 Stripe transactions with virtual card transactions on platform\n"
     ]
    }
   ],
   "source": [
    "# Merge the two dataframes on merchantId and source_id\n",
    "df_stripe_platform_virtual_card = pd.merge(df_platform.dropna(subset=['merchantId']), df_stripe_issuing_transaction.dropna(subset=['stripe.source_id']), left_on='merchantId', right_on='stripe.source_id', how='inner')\n",
    "df_stripe_platform_virtual_card.drop_duplicates(subset=['stripe.balance_transaction_id'], inplace=True)\n",
    "print(f'Successfully matched {len(df_stripe_platform_virtual_card)} Stripe transactions with virtual card transactions on platform')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unreconciled Stripe transactions: 882 out of 139879\n"
     ]
    }
   ],
   "source": [
    "# filter out all rows from df_left_stripe where stripe.balance_transaction_id is in df_stripe_platform_virtual_card\n",
    "df_left_stripe = df_left_stripe[~df_left_stripe['stripe.balance_transaction_id'].isin(df_stripe_platform_virtual_card['stripe.balance_transaction_id'])]\n",
    "print(f'Unreconciled Stripe transactions: {len(df_left_stripe)} out of {len(df_stripe)}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Reconcile disputes and dispute reversals"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Successfully matched 63 Stripe transactions with disputes on platform\n"
     ]
    }
   ],
   "source": [
    "# Find disputes in Stripe by filtering out all rows where stripe.reporting_category is dispute or dispute_reversal\n",
    "df_stripe_disputes = df_left_stripe[df_left_stripe['stripe.reporting_category'].isin(['dispute', 'dispute_reversal'])]\n",
    "\n",
    "# Merge platform transactions with disputes on stripe.charge_id\n",
    "df_stripe_platform_disputes = pd.merge(df_platform.dropna(subset=['merchantId']) , df_stripe_disputes.dropna(subset=['stripe.charge_id']), left_on='merchantId', right_on='stripe.charge_id', how='inner')\n",
    "df_stripe_platform_disputes.drop_duplicates(subset=['stripe.balance_transaction_id'], inplace=True)\n",
    "\n",
    "print(f'Successfully matched {len(df_stripe_platform_disputes)} Stripe transactions with disputes on platform')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unreconciled Stripe transactions: 819 out of 139879\n"
     ]
    }
   ],
   "source": [
    "# filter out all rows from df_left_stripe where stripe.balance_transaction_id is in df_stripe_platform_disputes\n",
    "df_left_stripe = df_left_stripe[~df_left_stripe['stripe.balance_transaction_id'].isin(df_stripe_platform_disputes['stripe.balance_transaction_id'])]\n",
    "print(f'Unreconciled Stripe transactions: {len(df_left_stripe)} out of {len(df_stripe)}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Reconcile virtual card topups"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Successfully matched 9 Stripe transactions with bank topups\n"
     ]
    }
   ],
   "source": [
    "df_stripe_topups = df_left_stripe[df_left_stripe['stripe.reporting_category'] == 'topup']\n",
    "df_stripe_topup_reversals = df_left_stripe[df_left_stripe['stripe.reporting_category'] == 'topup_reversal']\n",
    "\n",
    "# find bank transactions that match the topups\n",
    "df_stripe_bank_topups = matchByDateAndValue(df_stripe_topups, ['stripe.created_utc', 'stripe.net'], df_banks, ['bank.Post Date', 'bank.Debit'], '2 days', 0.05).dropna(subset=['df2key'])\n",
    "df_stripe_bank_topup_reversals = matchByDateAndValue(df_stripe_topup_reversals, ['stripe.created_utc', 'stripe.net'], df_banks, ['bank.Post Date', 'bank.Credit'], '2 days', 0.05, absolute=True).dropna(subset=['df2key'])\n",
    "\n",
    "print(f'Successfully matched {len(df_stripe_bank_topups) + len(df_stripe_bank_topup_reversals)} Stripe transactions with bank topups')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unreconciled Stripe transactions: 810 out of 139879\n"
     ]
    }
   ],
   "source": [
    "df_left_stripe = df_left_stripe[~df_left_stripe['stripe.balance_transaction_id'].isin(df_stripe_bank_topups['stripe.balance_transaction_id'])]\n",
    "df_left_stripe = df_left_stripe[~df_left_stripe['stripe.balance_transaction_id'].isin(df_stripe_bank_topup_reversals['stripe.balance_transaction_id'])]\n",
    "print(f'Unreconciled Stripe transactions: {len(df_left_stripe)} out of {len(df_stripe)}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Analyzing Stripe remainder"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">stripe.gross</th>\n",
       "      <th>stripe.fee</th>\n",
       "      <th>stripe.net</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>sum</th>\n",
       "      <th>sum</th>\n",
       "      <th>sum</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>stripe.reporting_category</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>charge</th>\n",
       "      <td>451</td>\n",
       "      <td>7828.00</td>\n",
       "      <td>635.01</td>\n",
       "      <td>7192.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>charge_failure</th>\n",
       "      <td>18</td>\n",
       "      <td>-3618.00</td>\n",
       "      <td>43.05</td>\n",
       "      <td>-3661.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dispute</th>\n",
       "      <td>3</td>\n",
       "      <td>-75.00</td>\n",
       "      <td>45.00</td>\n",
       "      <td>-120.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>issuing_authorization_hold</th>\n",
       "      <td>1</td>\n",
       "      <td>-36.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>-36.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>issuing_dispute</th>\n",
       "      <td>6</td>\n",
       "      <td>290.14</td>\n",
       "      <td>0.00</td>\n",
       "      <td>290.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>issuing_transaction</th>\n",
       "      <td>42</td>\n",
       "      <td>21188.86</td>\n",
       "      <td>0.00</td>\n",
       "      <td>21188.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>refund</th>\n",
       "      <td>289</td>\n",
       "      <td>-293.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>-293.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           stripe.gross           stripe.fee stripe.net\n",
       "                                  count       sum        sum        sum\n",
       "stripe.reporting_category                                              \n",
       "charge                              451   7828.00     635.01    7192.99\n",
       "charge_failure                       18  -3618.00      43.05   -3661.05\n",
       "dispute                               3    -75.00      45.00    -120.00\n",
       "issuing_authorization_hold            1    -36.00       0.00     -36.00\n",
       "issuing_dispute                       6    290.14       0.00     290.14\n",
       "issuing_transaction                  42  21188.86       0.00   21188.86\n",
       "refund                              289   -293.00       0.00    -293.00"
      ]
     },
     "execution_count": 117,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# group df_left_stripe by stripe.reporting_category, count and sum gross, fee and net\n",
    "\n",
    "df_left_stripe.groupby('stripe.reporting_category').agg({'stripe.gross': ['count', 'sum'], 'stripe.fee': ['sum'], 'stripe.net': ['sum']})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Adding Stripe to reconciliation dict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "metadata": {},
   "outputs": [],
   "source": [
    "reconciliation_dict['stripe']['reconciliation']['reconciled_platform']['stripe_platform_contributions'] = df_stripe_platform_contributions\n",
    "reconciliation_dict['stripe']['reconciliation']['reconciled_platform']['stripe_platform_contrubution_refunds'] = df_stripe_platform_contrubution_refunds\n",
    "reconciliation_dict['stripe']['reconciliation']['reconciled_platform']['stripe_platform_virtual_card'] = df_stripe_platform_virtual_card\n",
    "reconciliation_dict['stripe']['reconciliation']['reconciled_platform']['stripe_platform_disputes'] = df_stripe_platform_disputes\n",
    "\n",
    "reconciliation_dict['stripe']['reconciliation']['reconciled_other']['stripe_application_fees'] = df_stripe_application_fees\n",
    "reconciliation_dict['stripe']['reconciliation']['reconciled_other']['stripe_authorization'] = df_stripe_authorization\n",
    "\n",
    "reconciliation_dict['stripe']['reconciliation']['reconciled_bank']['stripe_bank_topups'] = df_stripe_bank_topups\n",
    "reconciliation_dict['stripe']['reconciliation']['reconciled_bank']['stripe_bank_topup_reversals'] = df_stripe_bank_topup_reversals\n",
    "\n",
    "reconciliation_dict['stripe']['reconciliation']['unreconciled']['unreconciled_stripe'] = df_left_stripe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "All stripe transactions accounted for in reconciliation_dict\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 119,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "assert_reconcilation('stripe', df_stripe, reconciliation_dict)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Paypal reconciliation"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We start by looking into Paypal transactions.\n",
    "\n",
    "Paypal is more complicated than Stripe and Wise, especially when it comes to payouts in other currencies."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">paypal.Gross</th>\n",
       "      <th>paypal.Fee</th>\n",
       "      <th>paypal.Net</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>sum</th>\n",
       "      <th>sum</th>\n",
       "      <th>sum</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>paypal.Type</th>\n",
       "      <th>paypal.Status</th>\n",
       "      <th>paypal.Currency</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Bank Deposit to PP Account</th>\n",
       "      <th>Completed</th>\n",
       "      <th>USD</th>\n",
       "      <td>1</td>\n",
       "      <td>75000.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>75000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Cancellation of Hold for Dispute Resolution</th>\n",
       "      <th>Denied</th>\n",
       "      <th>USD</th>\n",
       "      <td>55</td>\n",
       "      <td>1818.41</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1818.41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Chargeback</th>\n",
       "      <th>Completed</th>\n",
       "      <th>USD</th>\n",
       "      <td>29</td>\n",
       "      <td>-350.05</td>\n",
       "      <td>0.00</td>\n",
       "      <td>-350.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Chargeback Reversal</th>\n",
       "      <th>Completed</th>\n",
       "      <th>USD</th>\n",
       "      <td>5</td>\n",
       "      <td>22.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>22.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">Express Checkout Payment</th>\n",
       "      <th rowspan=\"4\" valign=\"top\">Completed</th>\n",
       "      <th>BRL</th>\n",
       "      <td>1</td>\n",
       "      <td>100.00</td>\n",
       "      <td>-7.89</td>\n",
       "      <td>92.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EUR</th>\n",
       "      <td>82</td>\n",
       "      <td>1145.06</td>\n",
       "      <td>-84.81</td>\n",
       "      <td>1060.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GBP</th>\n",
       "      <td>9</td>\n",
       "      <td>207.50</td>\n",
       "      <td>-13.87</td>\n",
       "      <td>193.63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>USD</th>\n",
       "      <td>8439</td>\n",
       "      <td>272301.68</td>\n",
       "      <td>-16367.16</td>\n",
       "      <td>255934.52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"11\" valign=\"top\">General Currency Conversion</th>\n",
       "      <th rowspan=\"11\" valign=\"top\">Completed</th>\n",
       "      <th>AUD</th>\n",
       "      <td>21</td>\n",
       "      <td>5629.16</td>\n",
       "      <td>0.00</td>\n",
       "      <td>5629.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BRL</th>\n",
       "      <td>2</td>\n",
       "      <td>494.87</td>\n",
       "      <td>0.00</td>\n",
       "      <td>494.87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CAD</th>\n",
       "      <td>9</td>\n",
       "      <td>1576.67</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1576.67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DKK</th>\n",
       "      <td>1</td>\n",
       "      <td>861.90</td>\n",
       "      <td>0.00</td>\n",
       "      <td>861.90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EUR</th>\n",
       "      <td>71</td>\n",
       "      <td>86619.72</td>\n",
       "      <td>0.00</td>\n",
       "      <td>86619.72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GBP</th>\n",
       "      <td>21</td>\n",
       "      <td>4467.11</td>\n",
       "      <td>0.00</td>\n",
       "      <td>4467.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NOK</th>\n",
       "      <td>2</td>\n",
       "      <td>1567.74</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1567.74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NZD</th>\n",
       "      <td>3</td>\n",
       "      <td>761.31</td>\n",
       "      <td>0.00</td>\n",
       "      <td>761.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PLN</th>\n",
       "      <td>1</td>\n",
       "      <td>875.22</td>\n",
       "      <td>0.00</td>\n",
       "      <td>875.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SEK</th>\n",
       "      <td>15</td>\n",
       "      <td>10086.80</td>\n",
       "      <td>0.00</td>\n",
       "      <td>10086.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>USD</th>\n",
       "      <td>146</td>\n",
       "      <td>-111374.82</td>\n",
       "      <td>0.00</td>\n",
       "      <td>-111374.82</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">General Payment</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">Completed</th>\n",
       "      <th>BRL</th>\n",
       "      <td>1</td>\n",
       "      <td>-586.98</td>\n",
       "      <td>0.00</td>\n",
       "      <td>-586.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>USD</th>\n",
       "      <td>13</td>\n",
       "      <td>-795.43</td>\n",
       "      <td>-165.83</td>\n",
       "      <td>-961.26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>General Withdrawal</th>\n",
       "      <th>Completed</th>\n",
       "      <th>USD</th>\n",
       "      <td>5</td>\n",
       "      <td>-194635.81</td>\n",
       "      <td>0.00</td>\n",
       "      <td>-194635.81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Hold on Available Balance</th>\n",
       "      <th rowspan=\"3\" valign=\"top\">Completed</th>\n",
       "      <th>EUR</th>\n",
       "      <td>56</td>\n",
       "      <td>-2764.98</td>\n",
       "      <td>0.00</td>\n",
       "      <td>-2764.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GBP</th>\n",
       "      <td>2</td>\n",
       "      <td>-54.42</td>\n",
       "      <td>0.00</td>\n",
       "      <td>-54.42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>USD</th>\n",
       "      <td>1165</td>\n",
       "      <td>-844872.92</td>\n",
       "      <td>0.00</td>\n",
       "      <td>-844872.92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Hold on Balance for Dispute Investigation</th>\n",
       "      <th>Pending</th>\n",
       "      <th>USD</th>\n",
       "      <td>53</td>\n",
       "      <td>-1800.09</td>\n",
       "      <td>0.00</td>\n",
       "      <td>-1800.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"14\" valign=\"top\">Mass Pay Payment</th>\n",
       "      <th rowspan=\"10\" valign=\"top\">Completed</th>\n",
       "      <th>AUD</th>\n",
       "      <td>21</td>\n",
       "      <td>-5520.72</td>\n",
       "      <td>-108.44</td>\n",
       "      <td>-5629.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CAD</th>\n",
       "      <td>9</td>\n",
       "      <td>-1545.76</td>\n",
       "      <td>-30.91</td>\n",
       "      <td>-1576.67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DKK</th>\n",
       "      <td>1</td>\n",
       "      <td>-845.00</td>\n",
       "      <td>-16.90</td>\n",
       "      <td>-861.90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EUR</th>\n",
       "      <td>128</td>\n",
       "      <td>-88635.22</td>\n",
       "      <td>-666.36</td>\n",
       "      <td>-89301.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GBP</th>\n",
       "      <td>23</td>\n",
       "      <td>-4432.86</td>\n",
       "      <td>-88.67</td>\n",
       "      <td>-4521.53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NOK</th>\n",
       "      <td>2</td>\n",
       "      <td>-1537.00</td>\n",
       "      <td>-30.74</td>\n",
       "      <td>-1567.74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NZD</th>\n",
       "      <td>3</td>\n",
       "      <td>-746.38</td>\n",
       "      <td>-14.93</td>\n",
       "      <td>-761.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PLN</th>\n",
       "      <td>1</td>\n",
       "      <td>-858.06</td>\n",
       "      <td>-17.16</td>\n",
       "      <td>-875.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SEK</th>\n",
       "      <td>15</td>\n",
       "      <td>-9889.01</td>\n",
       "      <td>-197.79</td>\n",
       "      <td>-10086.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>USD</th>\n",
       "      <td>1362</td>\n",
       "      <td>-701323.29</td>\n",
       "      <td>-5239.69</td>\n",
       "      <td>-706562.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Pending</th>\n",
       "      <th>EUR</th>\n",
       "      <td>1</td>\n",
       "      <td>-40.00</td>\n",
       "      <td>-0.80</td>\n",
       "      <td>-40.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>USD</th>\n",
       "      <td>39</td>\n",
       "      <td>-10518.47</td>\n",
       "      <td>-210.37</td>\n",
       "      <td>-10728.84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Unclaimed</th>\n",
       "      <th>EUR</th>\n",
       "      <td>3</td>\n",
       "      <td>-13.65</td>\n",
       "      <td>-0.66</td>\n",
       "      <td>-14.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>USD</th>\n",
       "      <td>19</td>\n",
       "      <td>-5318.34</td>\n",
       "      <td>-15.25</td>\n",
       "      <td>-5333.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Mass Pay Reversal</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">Completed</th>\n",
       "      <th>EUR</th>\n",
       "      <td>1</td>\n",
       "      <td>4.55</td>\n",
       "      <td>0.22</td>\n",
       "      <td>4.77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>USD</th>\n",
       "      <td>8</td>\n",
       "      <td>2101.86</td>\n",
       "      <td>2.00</td>\n",
       "      <td>2103.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mobile Payment</th>\n",
       "      <th>Completed</th>\n",
       "      <th>USD</th>\n",
       "      <td>3</td>\n",
       "      <td>1.08</td>\n",
       "      <td>-0.05</td>\n",
       "      <td>1.03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Payment Refund</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">Completed</th>\n",
       "      <th>EUR</th>\n",
       "      <td>1</td>\n",
       "      <td>-50.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>-50.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>USD</th>\n",
       "      <td>101</td>\n",
       "      <td>-7864.99</td>\n",
       "      <td>55.59</td>\n",
       "      <td>-7809.40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Payment Reversal</th>\n",
       "      <th>Completed</th>\n",
       "      <th>USD</th>\n",
       "      <td>1</td>\n",
       "      <td>-5.00</td>\n",
       "      <td>0.74</td>\n",
       "      <td>-4.26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Reversal of General Account Hold</th>\n",
       "      <th rowspan=\"3\" valign=\"top\">Completed</th>\n",
       "      <th>EUR</th>\n",
       "      <td>59</td>\n",
       "      <td>2764.98</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2764.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GBP</th>\n",
       "      <td>2</td>\n",
       "      <td>54.42</td>\n",
       "      <td>0.00</td>\n",
       "      <td>54.42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>USD</th>\n",
       "      <td>1594</td>\n",
       "      <td>844872.92</td>\n",
       "      <td>0.00</td>\n",
       "      <td>844872.92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Subscription Payment</th>\n",
       "      <th rowspan=\"3\" valign=\"top\">Completed</th>\n",
       "      <th>EUR</th>\n",
       "      <td>255</td>\n",
       "      <td>1995.00</td>\n",
       "      <td>-184.34</td>\n",
       "      <td>1810.66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GBP</th>\n",
       "      <td>16</td>\n",
       "      <td>110.00</td>\n",
       "      <td>-11.44</td>\n",
       "      <td>98.56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>USD</th>\n",
       "      <td>49539</td>\n",
       "      <td>715642.03</td>\n",
       "      <td>-57179.22</td>\n",
       "      <td>658462.81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">User Initiated Currency Conversion</th>\n",
       "      <th rowspan=\"3\" valign=\"top\">Completed</th>\n",
       "      <th>EUR</th>\n",
       "      <td>4</td>\n",
       "      <td>-500.01</td>\n",
       "      <td>0.00</td>\n",
       "      <td>-500.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GBP</th>\n",
       "      <td>4</td>\n",
       "      <td>-218.86</td>\n",
       "      <td>0.00</td>\n",
       "      <td>-218.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>USD</th>\n",
       "      <td>8</td>\n",
       "      <td>782.92</td>\n",
       "      <td>0.00</td>\n",
       "      <td>782.92</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                          paypal.Gross  \\\n",
       "                                                                                 count   \n",
       "paypal.Type                                 paypal.Status paypal.Currency                \n",
       "Bank Deposit to PP Account                  Completed     USD                        1   \n",
       "Cancellation of Hold for Dispute Resolution Denied        USD                       55   \n",
       "Chargeback                                  Completed     USD                       29   \n",
       "Chargeback Reversal                         Completed     USD                        5   \n",
       "Express Checkout Payment                    Completed     BRL                        1   \n",
       "                                                          EUR                       82   \n",
       "                                                          GBP                        9   \n",
       "                                                          USD                     8439   \n",
       "General Currency Conversion                 Completed     AUD                       21   \n",
       "                                                          BRL                        2   \n",
       "                                                          CAD                        9   \n",
       "                                                          DKK                        1   \n",
       "                                                          EUR                       71   \n",
       "                                                          GBP                       21   \n",
       "                                                          NOK                        2   \n",
       "                                                          NZD                        3   \n",
       "                                                          PLN                        1   \n",
       "                                                          SEK                       15   \n",
       "                                                          USD                      146   \n",
       "General Payment                             Completed     BRL                        1   \n",
       "                                                          USD                       13   \n",
       "General Withdrawal                          Completed     USD                        5   \n",
       "Hold on Available Balance                   Completed     EUR                       56   \n",
       "                                                          GBP                        2   \n",
       "                                                          USD                     1165   \n",
       "Hold on Balance for Dispute Investigation   Pending       USD                       53   \n",
       "Mass Pay Payment                            Completed     AUD                       21   \n",
       "                                                          CAD                        9   \n",
       "                                                          DKK                        1   \n",
       "                                                          EUR                      128   \n",
       "                                                          GBP                       23   \n",
       "                                                          NOK                        2   \n",
       "                                                          NZD                        3   \n",
       "                                                          PLN                        1   \n",
       "                                                          SEK                       15   \n",
       "                                                          USD                     1362   \n",
       "                                            Pending       EUR                        1   \n",
       "                                                          USD                       39   \n",
       "                                            Unclaimed     EUR                        3   \n",
       "                                                          USD                       19   \n",
       "Mass Pay Reversal                           Completed     EUR                        1   \n",
       "                                                          USD                        8   \n",
       "Mobile Payment                              Completed     USD                        3   \n",
       "Payment Refund                              Completed     EUR                        1   \n",
       "                                                          USD                      101   \n",
       "Payment Reversal                            Completed     USD                        1   \n",
       "Reversal of General Account Hold            Completed     EUR                       59   \n",
       "                                                          GBP                        2   \n",
       "                                                          USD                     1594   \n",
       "Subscription Payment                        Completed     EUR                      255   \n",
       "                                                          GBP                       16   \n",
       "                                                          USD                    49539   \n",
       "User Initiated Currency Conversion          Completed     EUR                        4   \n",
       "                                                          GBP                        4   \n",
       "                                                          USD                        8   \n",
       "\n",
       "                                                                                      \\\n",
       "                                                                                 sum   \n",
       "paypal.Type                                 paypal.Status paypal.Currency              \n",
       "Bank Deposit to PP Account                  Completed     USD               75000.00   \n",
       "Cancellation of Hold for Dispute Resolution Denied        USD                1818.41   \n",
       "Chargeback                                  Completed     USD                -350.05   \n",
       "Chargeback Reversal                         Completed     USD                  22.00   \n",
       "Express Checkout Payment                    Completed     BRL                 100.00   \n",
       "                                                          EUR                1145.06   \n",
       "                                                          GBP                 207.50   \n",
       "                                                          USD              272301.68   \n",
       "General Currency Conversion                 Completed     AUD                5629.16   \n",
       "                                                          BRL                 494.87   \n",
       "                                                          CAD                1576.67   \n",
       "                                                          DKK                 861.90   \n",
       "                                                          EUR               86619.72   \n",
       "                                                          GBP                4467.11   \n",
       "                                                          NOK                1567.74   \n",
       "                                                          NZD                 761.31   \n",
       "                                                          PLN                 875.22   \n",
       "                                                          SEK               10086.80   \n",
       "                                                          USD             -111374.82   \n",
       "General Payment                             Completed     BRL                -586.98   \n",
       "                                                          USD                -795.43   \n",
       "General Withdrawal                          Completed     USD             -194635.81   \n",
       "Hold on Available Balance                   Completed     EUR               -2764.98   \n",
       "                                                          GBP                 -54.42   \n",
       "                                                          USD             -844872.92   \n",
       "Hold on Balance for Dispute Investigation   Pending       USD               -1800.09   \n",
       "Mass Pay Payment                            Completed     AUD               -5520.72   \n",
       "                                                          CAD               -1545.76   \n",
       "                                                          DKK                -845.00   \n",
       "                                                          EUR              -88635.22   \n",
       "                                                          GBP               -4432.86   \n",
       "                                                          NOK               -1537.00   \n",
       "                                                          NZD                -746.38   \n",
       "                                                          PLN                -858.06   \n",
       "                                                          SEK               -9889.01   \n",
       "                                                          USD             -701323.29   \n",
       "                                            Pending       EUR                 -40.00   \n",
       "                                                          USD              -10518.47   \n",
       "                                            Unclaimed     EUR                 -13.65   \n",
       "                                                          USD               -5318.34   \n",
       "Mass Pay Reversal                           Completed     EUR                   4.55   \n",
       "                                                          USD                2101.86   \n",
       "Mobile Payment                              Completed     USD                   1.08   \n",
       "Payment Refund                              Completed     EUR                 -50.00   \n",
       "                                                          USD               -7864.99   \n",
       "Payment Reversal                            Completed     USD                  -5.00   \n",
       "Reversal of General Account Hold            Completed     EUR                2764.98   \n",
       "                                                          GBP                  54.42   \n",
       "                                                          USD              844872.92   \n",
       "Subscription Payment                        Completed     EUR                1995.00   \n",
       "                                                          GBP                 110.00   \n",
       "                                                          USD              715642.03   \n",
       "User Initiated Currency Conversion          Completed     EUR                -500.01   \n",
       "                                                          GBP                -218.86   \n",
       "                                                          USD                 782.92   \n",
       "\n",
       "                                                                          paypal.Fee  \\\n",
       "                                                                                 sum   \n",
       "paypal.Type                                 paypal.Status paypal.Currency              \n",
       "Bank Deposit to PP Account                  Completed     USD                   0.00   \n",
       "Cancellation of Hold for Dispute Resolution Denied        USD                   0.00   \n",
       "Chargeback                                  Completed     USD                   0.00   \n",
       "Chargeback Reversal                         Completed     USD                   0.00   \n",
       "Express Checkout Payment                    Completed     BRL                  -7.89   \n",
       "                                                          EUR                 -84.81   \n",
       "                                                          GBP                 -13.87   \n",
       "                                                          USD              -16367.16   \n",
       "General Currency Conversion                 Completed     AUD                   0.00   \n",
       "                                                          BRL                   0.00   \n",
       "                                                          CAD                   0.00   \n",
       "                                                          DKK                   0.00   \n",
       "                                                          EUR                   0.00   \n",
       "                                                          GBP                   0.00   \n",
       "                                                          NOK                   0.00   \n",
       "                                                          NZD                   0.00   \n",
       "                                                          PLN                   0.00   \n",
       "                                                          SEK                   0.00   \n",
       "                                                          USD                   0.00   \n",
       "General Payment                             Completed     BRL                   0.00   \n",
       "                                                          USD                -165.83   \n",
       "General Withdrawal                          Completed     USD                   0.00   \n",
       "Hold on Available Balance                   Completed     EUR                   0.00   \n",
       "                                                          GBP                   0.00   \n",
       "                                                          USD                   0.00   \n",
       "Hold on Balance for Dispute Investigation   Pending       USD                   0.00   \n",
       "Mass Pay Payment                            Completed     AUD                -108.44   \n",
       "                                                          CAD                 -30.91   \n",
       "                                                          DKK                 -16.90   \n",
       "                                                          EUR                -666.36   \n",
       "                                                          GBP                 -88.67   \n",
       "                                                          NOK                 -30.74   \n",
       "                                                          NZD                 -14.93   \n",
       "                                                          PLN                 -17.16   \n",
       "                                                          SEK                -197.79   \n",
       "                                                          USD               -5239.69   \n",
       "                                            Pending       EUR                  -0.80   \n",
       "                                                          USD                -210.37   \n",
       "                                            Unclaimed     EUR                  -0.66   \n",
       "                                                          USD                 -15.25   \n",
       "Mass Pay Reversal                           Completed     EUR                   0.22   \n",
       "                                                          USD                   2.00   \n",
       "Mobile Payment                              Completed     USD                  -0.05   \n",
       "Payment Refund                              Completed     EUR                   0.00   \n",
       "                                                          USD                  55.59   \n",
       "Payment Reversal                            Completed     USD                   0.74   \n",
       "Reversal of General Account Hold            Completed     EUR                   0.00   \n",
       "                                                          GBP                   0.00   \n",
       "                                                          USD                   0.00   \n",
       "Subscription Payment                        Completed     EUR                -184.34   \n",
       "                                                          GBP                 -11.44   \n",
       "                                                          USD              -57179.22   \n",
       "User Initiated Currency Conversion          Completed     EUR                   0.00   \n",
       "                                                          GBP                   0.00   \n",
       "                                                          USD                   0.00   \n",
       "\n",
       "                                                                          paypal.Net  \n",
       "                                                                                 sum  \n",
       "paypal.Type                                 paypal.Status paypal.Currency             \n",
       "Bank Deposit to PP Account                  Completed     USD               75000.00  \n",
       "Cancellation of Hold for Dispute Resolution Denied        USD                1818.41  \n",
       "Chargeback                                  Completed     USD                -350.05  \n",
       "Chargeback Reversal                         Completed     USD                  22.00  \n",
       "Express Checkout Payment                    Completed     BRL                  92.11  \n",
       "                                                          EUR                1060.25  \n",
       "                                                          GBP                 193.63  \n",
       "                                                          USD              255934.52  \n",
       "General Currency Conversion                 Completed     AUD                5629.16  \n",
       "                                                          BRL                 494.87  \n",
       "                                                          CAD                1576.67  \n",
       "                                                          DKK                 861.90  \n",
       "                                                          EUR               86619.72  \n",
       "                                                          GBP                4467.11  \n",
       "                                                          NOK                1567.74  \n",
       "                                                          NZD                 761.31  \n",
       "                                                          PLN                 875.22  \n",
       "                                                          SEK               10086.80  \n",
       "                                                          USD             -111374.82  \n",
       "General Payment                             Completed     BRL                -586.98  \n",
       "                                                          USD                -961.26  \n",
       "General Withdrawal                          Completed     USD             -194635.81  \n",
       "Hold on Available Balance                   Completed     EUR               -2764.98  \n",
       "                                                          GBP                 -54.42  \n",
       "                                                          USD             -844872.92  \n",
       "Hold on Balance for Dispute Investigation   Pending       USD               -1800.09  \n",
       "Mass Pay Payment                            Completed     AUD               -5629.16  \n",
       "                                                          CAD               -1576.67  \n",
       "                                                          DKK                -861.90  \n",
       "                                                          EUR              -89301.58  \n",
       "                                                          GBP               -4521.53  \n",
       "                                                          NOK               -1567.74  \n",
       "                                                          NZD                -761.31  \n",
       "                                                          PLN                -875.22  \n",
       "                                                          SEK              -10086.80  \n",
       "                                                          USD             -706562.98  \n",
       "                                            Pending       EUR                 -40.80  \n",
       "                                                          USD              -10728.84  \n",
       "                                            Unclaimed     EUR                 -14.31  \n",
       "                                                          USD               -5333.59  \n",
       "Mass Pay Reversal                           Completed     EUR                   4.77  \n",
       "                                                          USD                2103.86  \n",
       "Mobile Payment                              Completed     USD                   1.03  \n",
       "Payment Refund                              Completed     EUR                 -50.00  \n",
       "                                                          USD               -7809.40  \n",
       "Payment Reversal                            Completed     USD                  -4.26  \n",
       "Reversal of General Account Hold            Completed     EUR                2764.98  \n",
       "                                                          GBP                  54.42  \n",
       "                                                          USD              844872.92  \n",
       "Subscription Payment                        Completed     EUR                1810.66  \n",
       "                                                          GBP                  98.56  \n",
       "                                                          USD              658462.81  \n",
       "User Initiated Currency Conversion          Completed     EUR                -500.01  \n",
       "                                                          GBP                -218.86  \n",
       "                                                          USD                 782.92  "
      ]
     },
     "execution_count": 120,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# group df_left_stripe by reporting_category, count and sum gross, fee and net\n",
    "\n",
    "df_paypal.groupby(['paypal.Type', 'paypal.Status', 'paypal.Currency']).agg({'paypal.Gross': ['count', 'sum'], 'paypal.Fee': ['sum'], 'paypal.Net': ['sum']})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###  Reconciling holds and hold reversals"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Filtered out 2931 Paypal transactions that are currency conversions or holds\n",
      "Unreconciled Paypal transactions: 60447 out of 63431\n"
     ]
    }
   ],
   "source": [
    "hold_types = ['Reversal of General Account Hold', 'Hold on Available Balance', 'Hold on Balance for Dispute Investigation', 'Cancellation of Hold for Dispute Resolution\t']\n",
    "df_paypal_holds = df_paypal[df_paypal['paypal.Type'].isin(hold_types)]\n",
    "df_left_paypal = df_paypal[~df_paypal['paypal.Transaction ID'].isin(df_paypal_holds['paypal.Transaction ID'])]\n",
    "\n",
    "print(f'Filtered out {len(df_paypal_holds)} Paypal transactions that are currency conversions or holds')\n",
    "\n",
    "print(f'Unreconciled Paypal transactions: {len(df_left_paypal)} out of {len(df_paypal)}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Reconciling currency conversions"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "When you make a payout from PayPal in another currency than the currency of the PayPal account, it results in three transactions:\n",
    "* A debit from PayPal of the amount in USD\n",
    "* A credit to PayPal of the amount in the target currency\n",
    "* A payment to the recipient in the target currency\n",
    "\n",
    "Essentially, what happens is that you sell USD to PayPal from your USD account, then PayPal transfers you EUR into your EUR account, and finally you use that to pay the recipient. \n",
    "\n",
    "We do not want to do our accounting in multiple currencies, so we will ignore the currency conversion transactions and only concider the transaction in USD. We must however make a note of that OSC holds small amounts of foreign currency in its Paypal acccount becuase sometimes the payouts bounce and then they get refunded in the target currency. We leave it up to the accountants to choose what to do with these small balances of foreign currency."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We filter out transactions from the Paypal export that relate to internal currency exchange."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Filtered out 308 Paypal transactions that are currency conversions or holds\n",
      "Unreconciled Paypal transactions: 60139 out of 63431\n"
     ]
    }
   ],
   "source": [
    "conversion_types = ['General Currency Conversion', 'User Initiated Currency Conversion']\n",
    "df_paypal_currency_conversions = df_left_paypal[df_left_paypal['paypal.Type'].isin(conversion_types)]\n",
    "df_left_paypal = df_left_paypal[~df_left_paypal['paypal.Transaction ID'].isin(df_paypal_currency_conversions['paypal.Transaction ID'])]\n",
    "\n",
    "print(f'Filtered out {len(df_paypal_currency_conversions)} Paypal transactions that are currency conversions or holds')\n",
    "\n",
    "print(f'Unreconciled Paypal transactions: {len(df_left_paypal)} out of {len(df_paypal)}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Reconciling Paypal contributions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_platform_contributions = df_platform[df_platform['kind'] == 'CONTRIBUTION']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Successfully matched 58315 rows out of 60139 Paypal transactions with a platform transaction\n",
      "Unreconciled Paypal transactions: 1824 out of 63431\n"
     ]
    }
   ],
   "source": [
    "df_paypal_platform_contributions = pd.merge(df_platform_contributions.dropna(subset=['merchantId']), df_left_paypal.dropna(subset=['paypal.Transaction ID']), left_on='merchantId', right_on='paypal.Transaction ID', how='inner')\n",
    "df_paypal_platform_contributions.drop_duplicates(subset=['paypal.Transaction ID'], inplace=True)\n",
    "print(f'Successfully matched {len(df_paypal_platform_contributions)} rows out of {len(df_left_paypal)} Paypal transactions with a platform transaction')\n",
    "df_left_paypal = df_left_paypal[~df_left_paypal['paypal.Transaction ID'].isin(df_paypal_platform_contributions['paypal.Transaction ID'])]\n",
    "print(f'Unreconciled Paypal transactions: {len(df_left_paypal)} out of {len(df_paypal)}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Reconciling Paypal expenses"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Successfully matched 1607 rows out of 1824 Paypal transactions with a platform transaction\n",
      "Unreconciled Paypal transactions: 217 out of 63431\n"
     ]
    }
   ],
   "source": [
    "df_paypal_platform_expenses = pd.merge(df_platform.dropna(subset=['merchantId']), df_left_paypal.dropna(subset=['paypal.Transaction ID']), left_on='merchantId', right_on='paypal.Transaction ID', how='inner')\n",
    "df_paypal_platform_expenses.drop_duplicates(subset=['paypal.Transaction ID'], inplace=True)\n",
    "print(f'Successfully matched {len(df_paypal_platform_expenses)} rows out of {len(df_left_paypal)} Paypal transactions with a platform transaction')\n",
    "df_left_paypal = df_left_paypal[~df_left_paypal['paypal.Transaction ID'].isin(df_paypal_platform_expenses['paypal.Transaction ID'])]\n",
    "print(f'Unreconciled Paypal transactions: {len(df_left_paypal)} out of {len(df_paypal)}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Reconcile Paypal bank topups and withdrawals "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Successfully matched 6 Paypal transactions with bank topups\n",
      "Unreconciled Paypal transactions: 211 out of 63431\n"
     ]
    }
   ],
   "source": [
    "df_paypal_withdrawals = df_left_paypal[df_left_paypal['paypal.Type'] == 'General Withdrawal']\n",
    "df_paypal_deposits = df_left_paypal[df_left_paypal['paypal.Type'] == 'Bank Deposit to PP Account']\n",
    "\n",
    "# find bank transactions that match the topups\n",
    "df_paypal_bank_withdrawals = matchByDateAndValue(df_paypal_withdrawals, ['paypal.Datetime', 'paypal.Net'], df_banks, ['bank.Post Date', 'bank.Credit'], '5 days', 0.05, absolute=True).dropna(subset=['df2key'])\n",
    "df_paypal_bank_deposits = matchByDateAndValue(df_paypal_deposits, ['paypal.Datetime', 'paypal.Net'], df_banks, ['bank.Post Date', 'bank.Debit'], '5 days', 0.05, absolute=True).dropna(subset=['df2key'])\n",
    "\n",
    "print(f'Successfully matched {len(df_paypal_bank_withdrawals) + len(df_paypal_bank_deposits)} Paypal transactions with bank topups')\n",
    "\n",
    "df_left_paypal = df_left_paypal[~df_left_paypal['paypal.Transaction ID'].isin(df_paypal_bank_withdrawals['paypal.Transaction ID'])]\n",
    "df_left_paypal = df_left_paypal[~df_left_paypal['paypal.Transaction ID'].isin(df_paypal_bank_deposits['paypal.Transaction ID'])]\n",
    "print(f'Unreconciled Paypal transactions: {len(df_left_paypal)} out of {len(df_paypal)}')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Analyzing Paypal remainder"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">paypal.Gross</th>\n",
       "      <th>paypal.Fee</th>\n",
       "      <th>paypal.Net</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>sum</th>\n",
       "      <th>sum</th>\n",
       "      <th>sum</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>paypal.Type</th>\n",
       "      <th>paypal.Status</th>\n",
       "      <th>paypal.Currency</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Cancellation of Hold for Dispute Resolution</th>\n",
       "      <th>Denied</th>\n",
       "      <th>USD</th>\n",
       "      <td>2</td>\n",
       "      <td>18.32</td>\n",
       "      <td>0.00</td>\n",
       "      <td>18.32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Chargeback</th>\n",
       "      <th>Completed</th>\n",
       "      <th>USD</th>\n",
       "      <td>29</td>\n",
       "      <td>-350.05</td>\n",
       "      <td>0.00</td>\n",
       "      <td>-350.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Chargeback Reversal</th>\n",
       "      <th>Completed</th>\n",
       "      <th>USD</th>\n",
       "      <td>5</td>\n",
       "      <td>22.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>22.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Express Checkout Payment</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">Completed</th>\n",
       "      <th>EUR</th>\n",
       "      <td>1</td>\n",
       "      <td>50.00</td>\n",
       "      <td>-2.89</td>\n",
       "      <td>47.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>USD</th>\n",
       "      <td>29</td>\n",
       "      <td>2329.00</td>\n",
       "      <td>-117.61</td>\n",
       "      <td>2211.39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">General Payment</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">Completed</th>\n",
       "      <th>BRL</th>\n",
       "      <td>1</td>\n",
       "      <td>-586.98</td>\n",
       "      <td>0.00</td>\n",
       "      <td>-586.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>USD</th>\n",
       "      <td>13</td>\n",
       "      <td>-795.43</td>\n",
       "      <td>-165.83</td>\n",
       "      <td>-961.26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">Mass Pay Payment</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">Completed</th>\n",
       "      <th>EUR</th>\n",
       "      <td>2</td>\n",
       "      <td>28.01</td>\n",
       "      <td>0.00</td>\n",
       "      <td>28.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>USD</th>\n",
       "      <td>9</td>\n",
       "      <td>49.06</td>\n",
       "      <td>0.00</td>\n",
       "      <td>49.06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pending</th>\n",
       "      <th>USD</th>\n",
       "      <td>1</td>\n",
       "      <td>-19.80</td>\n",
       "      <td>-0.40</td>\n",
       "      <td>-20.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Unclaimed</th>\n",
       "      <th>EUR</th>\n",
       "      <td>1</td>\n",
       "      <td>-4.55</td>\n",
       "      <td>-0.22</td>\n",
       "      <td>-4.77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>USD</th>\n",
       "      <td>7</td>\n",
       "      <td>-2609.06</td>\n",
       "      <td>-1.75</td>\n",
       "      <td>-2610.81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Mass Pay Reversal</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">Completed</th>\n",
       "      <th>EUR</th>\n",
       "      <td>1</td>\n",
       "      <td>4.55</td>\n",
       "      <td>0.22</td>\n",
       "      <td>4.77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>USD</th>\n",
       "      <td>8</td>\n",
       "      <td>2101.86</td>\n",
       "      <td>2.00</td>\n",
       "      <td>2103.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mobile Payment</th>\n",
       "      <th>Completed</th>\n",
       "      <th>USD</th>\n",
       "      <td>3</td>\n",
       "      <td>1.08</td>\n",
       "      <td>-0.05</td>\n",
       "      <td>1.03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Payment Refund</th>\n",
       "      <th>Completed</th>\n",
       "      <th>USD</th>\n",
       "      <td>46</td>\n",
       "      <td>-3101.99</td>\n",
       "      <td>53.77</td>\n",
       "      <td>-3048.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Payment Reversal</th>\n",
       "      <th>Completed</th>\n",
       "      <th>USD</th>\n",
       "      <td>1</td>\n",
       "      <td>-5.00</td>\n",
       "      <td>0.74</td>\n",
       "      <td>-4.26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Subscription Payment</th>\n",
       "      <th>Completed</th>\n",
       "      <th>USD</th>\n",
       "      <td>52</td>\n",
       "      <td>3224.55</td>\n",
       "      <td>-176.81</td>\n",
       "      <td>3047.74</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                          paypal.Gross  \\\n",
       "                                                                                 count   \n",
       "paypal.Type                                 paypal.Status paypal.Currency                \n",
       "Cancellation of Hold for Dispute Resolution Denied        USD                        2   \n",
       "Chargeback                                  Completed     USD                       29   \n",
       "Chargeback Reversal                         Completed     USD                        5   \n",
       "Express Checkout Payment                    Completed     EUR                        1   \n",
       "                                                          USD                       29   \n",
       "General Payment                             Completed     BRL                        1   \n",
       "                                                          USD                       13   \n",
       "Mass Pay Payment                            Completed     EUR                        2   \n",
       "                                                          USD                        9   \n",
       "                                            Pending       USD                        1   \n",
       "                                            Unclaimed     EUR                        1   \n",
       "                                                          USD                        7   \n",
       "Mass Pay Reversal                           Completed     EUR                        1   \n",
       "                                                          USD                        8   \n",
       "Mobile Payment                              Completed     USD                        3   \n",
       "Payment Refund                              Completed     USD                       46   \n",
       "Payment Reversal                            Completed     USD                        1   \n",
       "Subscription Payment                        Completed     USD                       52   \n",
       "\n",
       "                                                                                    \\\n",
       "                                                                               sum   \n",
       "paypal.Type                                 paypal.Status paypal.Currency            \n",
       "Cancellation of Hold for Dispute Resolution Denied        USD                18.32   \n",
       "Chargeback                                  Completed     USD              -350.05   \n",
       "Chargeback Reversal                         Completed     USD                22.00   \n",
       "Express Checkout Payment                    Completed     EUR                50.00   \n",
       "                                                          USD              2329.00   \n",
       "General Payment                             Completed     BRL              -586.98   \n",
       "                                                          USD              -795.43   \n",
       "Mass Pay Payment                            Completed     EUR                28.01   \n",
       "                                                          USD                49.06   \n",
       "                                            Pending       USD               -19.80   \n",
       "                                            Unclaimed     EUR                -4.55   \n",
       "                                                          USD             -2609.06   \n",
       "Mass Pay Reversal                           Completed     EUR                 4.55   \n",
       "                                                          USD              2101.86   \n",
       "Mobile Payment                              Completed     USD                 1.08   \n",
       "Payment Refund                              Completed     USD             -3101.99   \n",
       "Payment Reversal                            Completed     USD                -5.00   \n",
       "Subscription Payment                        Completed     USD              3224.55   \n",
       "\n",
       "                                                                          paypal.Fee  \\\n",
       "                                                                                 sum   \n",
       "paypal.Type                                 paypal.Status paypal.Currency              \n",
       "Cancellation of Hold for Dispute Resolution Denied        USD                   0.00   \n",
       "Chargeback                                  Completed     USD                   0.00   \n",
       "Chargeback Reversal                         Completed     USD                   0.00   \n",
       "Express Checkout Payment                    Completed     EUR                  -2.89   \n",
       "                                                          USD                -117.61   \n",
       "General Payment                             Completed     BRL                   0.00   \n",
       "                                                          USD                -165.83   \n",
       "Mass Pay Payment                            Completed     EUR                   0.00   \n",
       "                                                          USD                   0.00   \n",
       "                                            Pending       USD                  -0.40   \n",
       "                                            Unclaimed     EUR                  -0.22   \n",
       "                                                          USD                  -1.75   \n",
       "Mass Pay Reversal                           Completed     EUR                   0.22   \n",
       "                                                          USD                   2.00   \n",
       "Mobile Payment                              Completed     USD                  -0.05   \n",
       "Payment Refund                              Completed     USD                  53.77   \n",
       "Payment Reversal                            Completed     USD                   0.74   \n",
       "Subscription Payment                        Completed     USD                -176.81   \n",
       "\n",
       "                                                                          paypal.Net  \n",
       "                                                                                 sum  \n",
       "paypal.Type                                 paypal.Status paypal.Currency             \n",
       "Cancellation of Hold for Dispute Resolution Denied        USD                  18.32  \n",
       "Chargeback                                  Completed     USD                -350.05  \n",
       "Chargeback Reversal                         Completed     USD                  22.00  \n",
       "Express Checkout Payment                    Completed     EUR                  47.11  \n",
       "                                                          USD                2211.39  \n",
       "General Payment                             Completed     BRL                -586.98  \n",
       "                                                          USD                -961.26  \n",
       "Mass Pay Payment                            Completed     EUR                  28.01  \n",
       "                                                          USD                  49.06  \n",
       "                                            Pending       USD                 -20.20  \n",
       "                                            Unclaimed     EUR                  -4.77  \n",
       "                                                          USD               -2610.81  \n",
       "Mass Pay Reversal                           Completed     EUR                   4.77  \n",
       "                                                          USD                2103.86  \n",
       "Mobile Payment                              Completed     USD                   1.03  \n",
       "Payment Refund                              Completed     USD               -3048.22  \n",
       "Payment Reversal                            Completed     USD                  -4.26  \n",
       "Subscription Payment                        Completed     USD                3047.74  "
      ]
     },
     "execution_count": 127,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# group df_left_stripe by reporting_category, count and sum gross, fee and net\n",
    "\n",
    "df_left_paypal.groupby(['paypal.Type', 'paypal.Status', 'paypal.Currency']).agg({'paypal.Gross': ['count', 'sum'], 'paypal.Fee': ['sum'], 'paypal.Net': ['sum']})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Adding Paypal to reconciliation dict"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "TODO: Fix paypal transaction discrepancy \n",
    "TODO: Separate Paypal df_paypal_platform by contribution, expense, fees, etc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "metadata": {},
   "outputs": [],
   "source": [
    "reconciliation_dict['paypal']['reconciliation']['reconciled_platform']['paypal_platform_contributions'] = df_paypal_platform_contributions\n",
    "reconciliation_dict['paypal']['reconciliation']['reconciled_platform']['paypal_platform_expenses'] = df_paypal_platform_expenses\n",
    "\n",
    "reconciliation_dict['paypal']['reconciliation']['reconciled_bank']['paypal_bank_withdrawals'] = df_paypal_bank_withdrawals\n",
    "reconciliation_dict['paypal']['reconciliation']['reconciled_bank']['paypal_bank_deposits'] = df_paypal_bank_deposits\n",
    "\n",
    "reconciliation_dict['paypal']['reconciliation']['reconciled_other']['paypal_holds'] = df_paypal_holds\n",
    "reconciliation_dict['paypal']['reconciliation']['reconciled_other']['paypal_currency_conversions'] = df_paypal_currency_conversions\n",
    "\n",
    "reconciliation_dict['paypal']['reconciliation']['unreconciled']['unreconciled_paypal'] = df_left_paypal"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "53 paypal transactions not accounted for in reconciliation_dict\n",
      "All duplicates are hold transactions. After removing hold duplicates:\n",
      "All paypal transactions accounted for in reconciliation_dict\n"
     ]
    }
   ],
   "source": [
    "if assert_reconcilation('paypal', df_paypal, reconciliation_dict) != 0:\n",
    "    df_paypal_drop_duplicates = df_paypal.drop_duplicates(subset=['paypal.Transaction ID'], inplace=False)\n",
    "    # Check so all transactions in df_paypal - df_paypal_drop_duplicates have Type in hold_types\n",
    "    duplicates_are_hold = len(df_paypal[~df_paypal['paypal.Transaction ID'].isin(df_paypal_drop_duplicates['paypal.Transaction ID'])][~df_paypal[~df_paypal['paypal.Transaction ID'].isin(df_paypal_drop_duplicates['paypal.Transaction ID'])]['paypal.Type'].isin(hold_types)]) == 0\n",
    "    if duplicates_are_hold:\n",
    "        print('All duplicates are hold transactions. After removing hold duplicates:')\n",
    "        assert_reconcilation('paypal', df_paypal_drop_duplicates, reconciliation_dict)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Internal host transfer reconciliation"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We filter out the internal transfers between collectives and between the host and collectives and funds and collectives.\n",
    "We put these transactions aside for later, they will be used for accounting."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create a df_internal_host_transactions dataframe that contains all platform transactions with paymentMethod.service == 'OPENCOLLECTIVE'\n",
    "df_internal_host_transactions = df_platform[df_platform['paymentMethod.service'] == 'OPENCOLLECTIVE']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Bank reconciliation"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Filter out all platform transactions that could be bank transactions"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We now want to reconcile bank account transactions with platform transactions.\n",
    "\n",
    "Identify platform transactions that might be bank related as follows:\n",
    "* Filter out all transaction kinds except contributions, expenses and added funds\n",
    "* Filter out known Wise, Paypal and Stripe transactions\n",
    "* Filter out platform transactions between collectives and from funds"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_platform_bank_related = df_platform[(df_platform['kind'] == 'CONTRIBUTION') | (df_platform['kind'] == 'EXPENSE') | (df_platform['kind'] == 'ADDED_FUNDS')]\n",
    "\n",
    "# filter out all platform transactions that are already reconciled with Wise, Stripe or Paypal\n",
    "for key, value in reconciliation_dict.items():\n",
    "    for key2, value2 in value['reconciliation']['reconciled_platform'].items():\n",
    "        df_platform_bank_related = df_platform_bank_related[~df_platform_bank_related['id'].isin(value2['id'])]\n",
    "\n",
    "# filter out transactions in df_platform_bank_related where paymentMethod.service == 'OPENCOLLECTIVE', these are internal transactions\n",
    "df_platform_bank_related = df_platform_bank_related[~df_platform_bank_related['id'].isin(df_internal_host_transactions['id'])]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Mark reconciled bank transactions from Wise, Stripe and Paypal reconciliation "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We have already reconciled some transactions with the bank and Wise, Stripe and Paypal"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unreconciled bank transactions: 611 out of 660\n"
     ]
    }
   ],
   "source": [
    "df_bank_stripe_topups = pd.concat([value for key, value in reconciliation_dict['stripe']['reconciliation']['reconciled_bank'].items()])\n",
    "df_bank_wise = pd.concat([value for key, value in reconciliation_dict['wise']['reconciliation']['reconciled_bank'].items()])\n",
    "df_bank_paypal = pd.concat([value for key, value in reconciliation_dict['paypal']['reconciliation']['reconciled_bank'].items()])\n",
    "\n",
    "df_left_bank = df_banks[~df_banks['bank.accountindex'].isin(df_bank_stripe_topups['bank.accountindex'])]\n",
    "df_left_bank = df_left_bank[~df_left_bank['bank.accountindex'].isin(df_bank_wise['bank.accountindex'])]\n",
    "df_left_bank = df_left_bank[~df_left_bank['bank.accountindex'].isin(df_bank_paypal['bank.accountindex'])]\n",
    "\n",
    "print(f'Unreconciled bank transactions: {len(df_left_bank)} out of {len(df_banks)}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Idenfify Stripe payouts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Filtered out 19 bank transactions that are Stripe transfers\n",
      "Unreconciled bank transactions: 592 out of 660\n"
     ]
    }
   ],
   "source": [
    "# find bank transactions with detail that contains 'STRIPE TRANSFER', trimming whitespace before matching\n",
    "df_bank_stripe_transfers = df_left_bank[df_left_bank['bank.Detail'].str.replace(r'\\s+', '', regex=True).str.contains('STRIPETRANSFER')]\n",
    "\n",
    "print(f'Filtered out {len(df_bank_stripe_transfers)} bank transactions that are Stripe transfers')\n",
    "\n",
    "df_left_bank = df_left_bank[~df_left_bank['bank.accountindex'].isin(df_bank_stripe_transfers['bank.accountindex'])]\n",
    "print(f'Unreconciled bank transactions: {len(df_left_bank)} out of {len(df_banks)}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Match transactions on date, amount and string comparison"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Match transactions on bank and platform that happen within a 10 day window, where the values are within 2.5%, and there is a high scoring substring match between the Detail column of the bank transaction and the oppositeAccount.name of the platform transaction."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "In first run, matched 164 bank transactions with platform transactions\n",
      "In second run, matched 3 bank transactions with platform transactions\n",
      "Successfully matched 167 bank transactions with platform transactions\n",
      "Unreconciled bank transactions: 425 out of 660\n"
     ]
    }
   ],
   "source": [
    "df_matches = matchByDateAmountString(df_left_bank, df_platform_bank_related, s1='bank.Detail', s2='oppositeAccount.name', min_similarity=80, max_days=10, max_diff=0.025)\n",
    "# Merge the matches dataframe with df_left_bank\n",
    "df_bank_matched = pd.merge(df_left_bank, df_matches, left_on='bank.accountindex', right_on='bank_accountindex', how='inner')\n",
    "# Merge the df_bank_platform_matched with df_platform_bank_related\n",
    "df_bank_platform_matched = pd.merge(df_platform_bank_related, df_bank_matched, right_on='platform_id',  left_on='id', how='inner')\n",
    "# Drop the temporary columns used for merging\n",
    "df_bank_platform_matched.drop(columns=['bank_accountindex', 'platform_id'], inplace=True)\n",
    "df_bank_platform_matched.drop_duplicates(subset=['bank.accountindex'], inplace=True)\n",
    "df_left_bank = df_left_bank[~df_left_bank['bank.accountindex'].isin(df_bank_platform_matched['bank.accountindex'])]\n",
    "print(f'In first run, matched {len(df_bank_platform_matched)} bank transactions with platform transactions')\n",
    "\n",
    "# Run matching again on remainder, but this time decrease the time window. \n",
    "# Because the matchByDateAmountString only returns a match if it finds exactly one match, we should find more in the second run.\n",
    "# We decrease the time window to avoid ambiguous matches.\n",
    "df_left_platform_bank_related = df_platform_bank_related[~df_platform_bank_related['id'].isin(df_bank_platform_matched['id'])]\n",
    "df_matches_2 = matchByDateAmountString(df_left_bank, df_left_platform_bank_related, s1='bank.Detail', s2='oppositeAccount.name', min_similarity=80, max_days=10, max_diff=0.025)\n",
    "df_bank_matched_2 = pd.merge(df_left_bank, df_matches_2, left_on='bank.accountindex', right_on='bank_accountindex', how='inner')\n",
    "df_bank_platform_matched_2 = pd.merge(df_left_platform_bank_related, df_bank_matched_2, right_on='platform_id',  left_on='id', how='inner')\n",
    "df_bank_platform_matched_2.drop(columns=['bank_accountindex', 'platform_id'], inplace=True)\n",
    "df_bank_platform_matched_2.drop_duplicates(subset=['bank.accountindex'], inplace=True)\n",
    "df_left_bank = df_left_bank[~df_left_bank['bank.accountindex'].isin(df_bank_platform_matched_2['bank.accountindex'])]\n",
    "print(f'In second run, matched {len(df_bank_platform_matched_2)} bank transactions with platform transactions')\n",
    "\n",
    "# concatenate the two dataframes\n",
    "df_bank_platform_matched = pd.concat([df_bank_platform_matched, df_bank_platform_matched_2])\n",
    "df_bank_platform_matched.drop_duplicates(subset=['bank.accountindex'], inplace=True)\n",
    "\n",
    "print(f'Successfully matched {len(df_bank_platform_matched)} bank transactions with platform transactions')\n",
    "print(f'Unreconciled bank transactions: {len(df_left_bank)} out of {len(df_banks)}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Adding bank to reconciliation dict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "metadata": {},
   "outputs": [],
   "source": [
    "reconciliation_dict['bank']['reconciliation']['reconciled_wise']['bank_wise'] = df_bank_wise\n",
    "reconciliation_dict['bank']['reconciliation']['reconciled_stripe']['bank_stripe_topups'] = df_bank_stripe_topups\n",
    "reconciliation_dict['bank']['reconciliation']['reconciled_stripe']['bank_stripe_transfers'] = df_bank_stripe_transfers\n",
    "reconciliation_dict['bank']['reconciliation']['reconciled_paypal']['bank_paypal'] = df_bank_paypal\n",
    "\n",
    "reconciliation_dict['bank']['reconciliation']['reconciled_platform']['bank_platform_matched'] = df_bank_platform_matched\n",
    "reconciliation_dict['bank']['reconciliation']['unreconciled']['unreconciled_bank'] = df_left_bank"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 136,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "All bank transactions accounted for in reconciliation_dict\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 136,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "assert_reconcilation('bank', df_banks, reconciliation_dict)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Platform ledger validation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 137,
   "metadata": {},
   "outputs": [],
   "source": [
    "# filtering out reconciled transactions from df_left_bank\n",
    "df_left_platform = df_platform\n",
    "for key, value in reconciliation_dict.items():\n",
    "    for key2, value2 in value['reconciliation']['reconciled_platform'].items():\n",
    "        df_left_platform = df_left_platform[~df_left_platform['id'].isin(value2['id'])]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "metadata": {},
   "outputs": [],
   "source": [
    "# filtering out internal transactions from df_left_platform\n",
    "df_left_platform = df_left_platform[~df_left_platform['id'].isin(df_internal_host_transactions['id'])]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "metadata": {},
   "outputs": [],
   "source": [
    "# filter out host fees and host fee share and host fee share debt transactions from df_left_platform\n",
    "df_left_platform = df_left_platform[~df_left_platform['kind'].isin(['HOST_FEE', 'HOST_FEE_SHARE', 'HOST_FEE_SHARE_DEBT'])]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>type</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>kind</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>BALANCE_TRANSFER</th>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CONTRIBUTION</th>\n",
       "      <td>699</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EXPENSE</th>\n",
       "      <td>305</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PAYMENT_PROCESSOR_COVER</th>\n",
       "      <td>728</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PAYMENT_PROCESSOR_DISPUTE_FEE</th>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PLATFORM_TIP_DEBT</th>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PREPAID_PAYMENT_METHOD</th>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                               type\n",
       "                              count\n",
       "kind                               \n",
       "BALANCE_TRANSFER                  4\n",
       "CONTRIBUTION                    699\n",
       "EXPENSE                         305\n",
       "PAYMENT_PROCESSOR_COVER         728\n",
       "PAYMENT_PROCESSOR_DISPUTE_FEE    18\n",
       "PLATFORM_TIP_DEBT                11\n",
       "PREPAID_PAYMENT_METHOD           13"
      ]
     },
     "execution_count": 140,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_left_platform.groupby('kind').agg({'type': ['count']})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 141,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unreconciled platform transactions: 1778 out of 845230\n"
     ]
    }
   ],
   "source": [
    "print(f'Unreconciled platform transactions: {len(df_left_platform)} out of {len(df_platform)}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Total remainder"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 142,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>type</th>\n",
       "      <th>unreconciled_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Stripe</td>\n",
       "      <td>810</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Wise</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Paypal</td>\n",
       "      <td>211</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Bank</td>\n",
       "      <td>425</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     type  unreconciled_count\n",
       "0  Stripe                 810\n",
       "1    Wise                  26\n",
       "2  Paypal                 211\n",
       "3    Bank                 425"
      ]
     },
     "execution_count": 142,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create a dataframe with the unreconciled transaction categories and counts\n",
    "\n",
    "df_unreconciled_counts = pd.DataFrame([\n",
    "    ['Stripe', len(df_left_stripe)],\n",
    "    ['Wise', len(df_left_wise)],\n",
    "    ['Paypal', len(df_left_paypal)],\n",
    "    ['Bank', len(df_left_bank)]\n",
    "], columns=['type', 'unreconciled_count'])\n",
    "\n",
    "df_unreconciled_counts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 143,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Sum of unreconciled counts: 1472\n"
     ]
    }
   ],
   "source": [
    "print(f'Sum of unreconciled counts: {df_unreconciled_counts[\"unreconciled_count\"].sum()}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Report"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 144,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Reconciliation summary\n",
      "\n",
      "Wise transactions: 3463\n",
      "Wise transactions reconciled with platform: 3403\n",
      "Wise transactions reconciled with bank: 34\n",
      "Wise transactions unreconciled: 26\n",
      "\n",
      "Stripe transactions: 139879\n",
      "Stripe transactions reconciled with platform: 134007\n",
      "Stripe transactions reconciled with bank: 9\n",
      "Stripe transactions unreconciled: 810\n",
      "\n",
      "Paypal transactions: 63431\n",
      "Paypal transactions reconciled with platform: 59922\n",
      "Paypal transactions reconciled with bank: 6\n",
      "Paypal transactions unreconciled: 211\n",
      "\n",
      "Bank transactions: 660\n",
      "Bank transactions reconciled with stripe: 28\n",
      "Bank transactions reconciled with wise: 34\n",
      "Bank transactions reconciled with paypal: 6\n",
      "Bank transactions with reconciliation suggestions with platform: 167\n",
      "Bank transactions unreconciled: 425\n",
      "\n",
      "Total transactions on Wise, Stripe, Paypal and Banks: 207433\n",
      "Total transactions reconciled: 197616\n",
      "Total transactions unreconciled: 1472\n",
      "Percentage unreconciled: 0.71%\n"
     ]
    }
   ],
   "source": [
    "# Print a summary of the reconciliation\n",
    "print('Reconciliation summary')\n",
    "print('')\n",
    "\n",
    "print(f'Wise transactions: {len(df_wise)}')\n",
    "print(f'Wise transactions reconciled with platform: {len(df_wise_platform)}')\n",
    "print(f'Wise transactions reconciled with bank: {len(df_wise_bank)}')\n",
    "print(f'Wise transactions unreconciled: {len(df_left_wise)}')\n",
    "\n",
    "print('')\n",
    "\n",
    "print(f'Stripe transactions: {len(df_stripe)}')\n",
    "print(f'Stripe transactions reconciled with platform: {len(df_stripe_platform_contributions) + len(df_stripe_platform_contrubution_refunds) + len(df_stripe_platform_virtual_card) + len(df_stripe_platform_disputes)}')\n",
    "print(f'Stripe transactions reconciled with bank: {len(df_stripe_bank_topups) + len(df_stripe_bank_topup_reversals)}')\n",
    "print(f'Stripe transactions unreconciled: {len(df_left_stripe)}')\n",
    "\n",
    "print('')\n",
    "\n",
    "print(f'Paypal transactions: {len(df_paypal)}')\n",
    "print(f'Paypal transactions reconciled with platform: {len(df_paypal_platform_contributions) + len(df_paypal_platform_expenses)}')\n",
    "print(f'Paypal transactions reconciled with bank: {len(df_paypal_bank_withdrawals) + len(df_paypal_bank_deposits)}')\n",
    "print(f'Paypal transactions unreconciled: {len(df_left_paypal)}')\n",
    "\n",
    "print('')\n",
    "\n",
    "print(f'Bank transactions: {len(df_banks)}')\n",
    "print(f'Bank transactions reconciled with stripe: {len(df_bank_stripe_topups) + len(df_bank_stripe_transfers)}')\n",
    "print(f'Bank transactions reconciled with wise: {len(df_bank_wise)}')\n",
    "print(f'Bank transactions reconciled with paypal: {len(df_bank_paypal)}')\n",
    "print(f'Bank transactions with reconciliation suggestions with platform: {len(df_bank_platform_matched)}')\n",
    "print(f'Bank transactions unreconciled: {len(df_left_bank)}')\n",
    "\n",
    "print('')\n",
    "\n",
    "print(f'Total transactions on Wise, Stripe, Paypal and Banks: {len(df_wise) + len(df_stripe) + len(df_paypal) + len(df_banks)}')\n",
    "print(f'Total transactions reconciled: {len(df_wise_platform) + len(df_wise_bank) + len(df_stripe_platform_contributions) + len(df_stripe_platform_contrubution_refunds) + len(df_stripe_platform_virtual_card) + len(df_stripe_platform_disputes) + len(df_stripe_bank_topups) + len(df_stripe_bank_topup_reversals) + len(df_paypal_platform_contributions) + len(df_paypal_platform_expenses) + len(df_paypal_bank_withdrawals) + len(df_paypal_bank_deposits) + len(df_bank_stripe_topups) + len(df_bank_stripe_transfers) + len(df_bank_wise) + len(df_bank_paypal) + len(df_bank_platform_matched)}')\n",
    "print(f'Total transactions unreconciled: {len(df_left_wise) + len(df_left_stripe) + len(df_left_paypal) + len(df_left_bank)}')\n",
    "print(f'Percentage unreconciled: {round((len(df_left_wise) + len(df_left_stripe) + len(df_left_paypal) + len(df_left_bank))/(len(df_wise) + len(df_stripe) + len(df_paypal) + len(df_banks))*100, 2)}%')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Export to CSV"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 145,
   "metadata": {},
   "outputs": [],
   "source": [
    "reportDir = './reports/opensource'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 146,
   "metadata": {},
   "outputs": [],
   "source": [
    "def process_nested_dict_to_df(nested_dict):\n",
    "    frames = []\n",
    "    for key, value in nested_dict.items():\n",
    "        if isinstance(value, pd.DataFrame):\n",
    "            if not value.empty:  # Check if the DataFrame is not empty\n",
    "                # Use .copy() to ensure you're working with a copy of the DataFrame\n",
    "                temp_df = value.copy()\n",
    "                temp_df['_reconciliation_category'] = key\n",
    "                frames.append(temp_df)\n",
    "        else:\n",
    "            frames.extend(process_nested_dict_to_df(value))\n",
    "    return frames\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 147,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Process the nested dictionary and concatenate all dataframes into one\n",
    "frames = process_nested_dict_to_df(reconciliation_dict['wise']['reconciliation'])\n",
    "wise_export = pd.concat(frames, ignore_index=True)\n",
    "# reorder the columns so that columns starting with wise are first\n",
    "wise_export = wise_export[sorted(wise_export.columns, key=lambda x: x.startswith('wise.'), reverse=True)]\n",
    "\n",
    "# assert that length of wise_export is equal to length of df_wise\n",
    "assert len(wise_export) == len(df_wise)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 148,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Process the nested dictionary and concatenate all dataframes into one\n",
    "frames = process_nested_dict_to_df(reconciliation_dict['stripe']['reconciliation'])\n",
    "stripe_export = pd.concat(frames, ignore_index=True)\n",
    "# reorder the columns so that columns starting with stripe are first\n",
    "stripe_export = stripe_export[sorted(stripe_export.columns, key=lambda x: x.startswith('stripe.'), reverse=True)]\n",
    "\n",
    "# assert that length of stripe_export is equal to length of df_stripe\n",
    "assert len(stripe_export) == len(df_stripe)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 149,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Process the nested dictionary and concatenate all dataframes into one\n",
    "frames = process_nested_dict_to_df(reconciliation_dict['paypal']['reconciliation'])\n",
    "paypal_export = pd.concat(frames, ignore_index=True)\n",
    "# reorder the columns so that columns starting with paypal are first\n",
    "paypal_export = paypal_export[sorted(paypal_export.columns, key=lambda x: x.startswith('paypal.'), reverse=True)]\n",
    "\n",
    "# assert that length of paypal_export is equal to length of df_paypal\n",
    "assert len(paypal_export) == len(df_paypal_drop_duplicates)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 150,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Process the nested dictionary and concatenate all dataframes into one\n",
    "frames = process_nested_dict_to_df(reconciliation_dict['bank']['reconciliation'])\n",
    "bank_export = pd.concat(frames, ignore_index=True)\n",
    "# reorder the columns so that columns starting with bank are first\n",
    "bank_export = bank_export[sorted(bank_export.columns, key=lambda x: x.startswith('bank.'), reverse=True)]\n",
    "\n",
    "# assert that length of bank_export is equal to length of df_banks\n",
    "assert len(bank_export) == len(df_banks)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 152,
   "metadata": {},
   "outputs": [],
   "source": [
    "# save exports to csv \n",
    "wise_export.to_csv(f'{reportDir}/wise_reconciliation.csv', index=False)\n",
    "stripe_export.to_csv(f'{reportDir}/stripe_reconciliation.csv', index=False)\n",
    "paypal_export.to_csv(f'{reportDir}/paypal_reconciliation.csv', index=False)\n",
    "bank_export.to_csv(f'{reportDir}/bank_reconciliation.csv', index=False)\n",
    "df_platform.to_csv(f'{reportDir}/all_platform_transactions.csv', index=False)\n",
    "\n",
    "# save unreconciled counts to csv\n",
    "df_unreconciled_counts.to_csv(f'{reportDir}/unreconciled_counts.csv', index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "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.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}