analysis.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Credit Analysis \n",
    "See README.md details"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "if not os.environ['OPENAI_API_KEY']:\n",
    "    raise Exception('Store your OpenAI Key in your enviroment')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import decimal\n",
    "import json\n",
    "\n",
    "# See docsumo.ipynb for code to access DocSumo.\n",
    "cached_response_path = (\n",
    "    \"docsumo_api_responses/docsumo_58080714c51a4764ad0181565cad604e_response.json\"\n",
    ")\n",
    "with open(cached_response_path) as f:\n",
    "    data = json.load(f)[\"data\"]\n",
    "\n",
    "# Docsumo gives tells us how confident it is in its predictions.\n",
    "# Only use confident predictions\n",
    "minimum_confidence = 0.8\n",
    "basic_information_dict = {\n",
    "    k: v['orig_value']\n",
    "    for k, v in data['Basic Information'].items()\n",
    "    if v and v['confidence'] > minimum_confidence and k\n",
    "}\n",
    "\n",
    "# Docsumo creates a seperate list for each page of the PDF\n",
    "line_items_by_page = data[\"Transactions\"]\n",
    "\n",
    "# Merge line items from each page into a single list\n",
    "raw_line_items = [item for sublist in line_items_by_page.values() for item in sublist]\n",
    "\n",
    "# Validate Docsumo's confidence in transaction data\n",
    "for row in raw_line_items:\n",
    "    for k, v in row.items():\n",
    "        if v[\"confidence\"] != 1.0:\n",
    "            raise Exception('Docsumo had issue, and is not sure about transaction data')\n",
    "\n",
    "\n",
    "# Strip out Docsumo meta data\n",
    "line_items = [{k: v[\"value\"] for k, v in item.items()} for item in raw_line_items]\n",
    "\n",
    "# Convert currency to Decimal\n",
    "for header in [\"debit\", \"credit\", \"balance\"]:\n",
    "    for item in line_items:\n",
    "        if item[header]:\n",
    "            item[header] = decimal.Decimal(str(item[header]))\n",
    "\n",
    "# Add an index, which we'll use to enrich data \n",
    "for i, line_item in enumerate(line_items):\n",
    "    line_item[\"index\"] = i\n",
    "\n",
    "# Remove empty columns\n",
    "for line_item in line_items:\n",
    "    del line_item['merchant']\n",
    "    del line_item['subcategory']\n",
    "    del line_item['type']\n",
    "\n",
    "\n",
    "# Calculate on how many days balance fell below 200.00\n",
    "days_balance_below_200 = len(\n",
    "    set(t['date'] for t in line_items if t['balance'] < decimal.Decimal('200.00'))\n",
    ")\n",
    "\n",
    "# Get average daily ending balance\n",
    "daily_ending_balances = {t['date']: t['balance'] for t in line_items}\n",
    "\n",
    "average_balance = sum(daily_ending_balances.values()) / len(daily_ending_balances) \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Month       Credits    Debits\n",
      "2018-04    10671.20   10671.00\n",
      "2018-05     9934.06    9583.00\n",
      "2018-06     2189.19    2196.60\n",
      "2018-07     8234.41    8554.56\n",
      "2018-08     7501.00    3540.00\n",
      "2018-09    26451.63   23234.89\n",
      "2018-10    68944.89   21062.61\n",
      "2018-11      408.90   55479.00\n",
      "2018-12    14380.33   14317.00\n",
      "2019-01     5610.00    2607.60\n",
      "2019-02     7567.34   10612.00\n",
      "2019-03     7847.81    7859.00\n",
      "\n"
     ]
    }
   ],
   "source": [
    "from collections import defaultdict\n",
    "\n",
    "# CEO asked for monthly deposits and withdrawals\n",
    "monthly_debits = defaultdict(lambda: decimal.Decimal(\"0.0\"))\n",
    "monthly_credits = defaultdict(lambda: decimal.Decimal(\"0.0\"))\n",
    "\n",
    "for item in line_items:\n",
    "    date_parts = item[\"date\"].split(\"/\")\n",
    "    month = date_parts[2] + \"-\" + date_parts[0]\n",
    "    if item[\"debit\"]:\n",
    "        monthly_debits[month] += item[\"debit\"]\n",
    "    else:\n",
    "        monthly_credits[month] += item[\"credit\"]\n",
    "\n",
    "months = sorted(list(set(monthly_debits.keys()).intersection(monthly_credits.keys())))\n",
    "print(\"Month       Credits    Debits\")\n",
    "monthly_data_str = \"\"\n",
    "for month in months:\n",
    "    monthly_data_str += f\"{month:8} {monthly_credits[month]:10.2f} {monthly_debits[month]:10.2f}\\n\"\n",
    "print(monthly_data_str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Pass information to Chat gpt\n",
    "import re\n",
    "from typing import Dict, List, Union\n",
    "from openai import OpenAI\n",
    "\n",
    "\n",
    "def extract_and_convert_json(input_str) -> Union[Dict, List]:\n",
    "    \"\"\"\n",
    "    Return the first valid JSON in the text block.\n",
    "    ChatGPT sometimes comments on the data, rather than returning JSON asked. \n",
    "    \"\"\"\n",
    "    # Regular expression to find a JSON substring.\n",
    "    # This pattern looks for the JSON structure starting with either a list ([) or an object ({)\n",
    "    # and continues until the corresponding closing bracket (]) or brace (}).\n",
    "    json_pattern = re.compile(r\"(\\{.*?\\}|\\[.*?\\])(?![^\\[]*\\])\", re.DOTALL)\n",
    "\n",
    "    # Search for JSON substring in the input string\n",
    "    matches = json_pattern.findall(input_str)\n",
    "    if matches:\n",
    "        # Match first JSON valid substring found\n",
    "        json_str = matches[0]\n",
    "        try:\n",
    "            # Convert the JSON string to a dict or list\n",
    "            json_data = json.loads(json_str)\n",
    "            return json_data\n",
    "        except json.JSONDecodeError as e:\n",
    "            print(f\"Error decoding JSON: {e}\")\n",
    "            return None\n",
    "    else:\n",
    "        raise Exception(\"No JSON found in input\")\n",
    "\n",
    "\n",
    "client = OpenAI()\n",
    "\n",
    "\n",
    "def get_likelihood_of_being_lender(transactions: List[Dict]) -> List[Dict[str, Union[int, float, str]]]:\n",
    "    \"\"\"Return the  likelihood that a transaction is with a lender.\n",
    "    Returns a list of dicts with an index for matching back to line_items.\n",
    "    \"\"\"\n",
    "\n",
    "    prompt_content = \"\"\"\n",
    "    You analyze a list of transactions. The transactions are for a person in India. \n",
    "    Rewiew the transactions. Return a JSON, a list of dicts with three keys:\n",
    "    - \"index\": the index of the transaction\n",
    "    - \"is_lender_likelihood\": likelihood [0-1.0] that transaction is with a lender\n",
    "    - \"reason\": reason for this estimate if estimate greater than 0. Limit the output of this str to 100 chars.\n",
    "\n",
    "    - It is possible none of the transactions are with a lender. \n",
    "\n",
    "    For example, when processing \n",
    "    ..., {\"index\": 2, \"debit\": \"1912.0\", \"credit\": None, \"balance\": None, \"description\": 'NACH/TP ACH Bajaj Finanac/88551679'}, ...\n",
    "    you would return \n",
    "    ..., {\"index\": 2, \"is_lender_likelihood\": 1.00, \"reason\": \"Bajaj Finserve is a popular micro finance lender. NACH/TP is commonly used for loan disbursements\"}, ...\n",
    "    \n",
    "    Your output must be JSON.  Do not add additional commentary because it will break the JSON parser.\n",
    "    \"\"\"\n",
    "\n",
    "    cleaned_transactions = [\n",
    "        {\n",
    "            \"index\": t[\"index\"],\n",
    "            \"debit\": str(t[\"debit\"]) if t[\"debit\"] else None,\n",
    "            \"credit\": str(t[\"credit\"]) if t[\"credit\"] else None,\n",
    "            \"balance\": str(t[\"balance\"]) if t[\"balance\"] else None,\n",
    "            \"description\": t[\"description\"],\n",
    "        }\n",
    "        for t in transactions\n",
    "    ]\n",
    "    transactions_json = json.dumps(cleaned_transactions)\n",
    "    # Print output so we can see progress\n",
    "    print(f\"get_likelihood_of_being_lender() called on {transactions_json[:5]}...\")\n",
    "\n",
    "    completion = client.chat.completions.create(\n",
    "        model=\"gpt-4\",\n",
    "        messages=[\n",
    "            {\n",
    "                \"role\": \"system\",\n",
    "                \"content\": \"You are system for categorizing information in bank statements .\",\n",
    "            },\n",
    "            {\"role\": \"user\", \"content\": prompt_content + transactions_json},\n",
    "        ],\n",
    "    )\n",
    "    print(\"Received response from OpenAI\")\n",
    "    try:\n",
    "        s = completion.choices[0].message.content.replace(\"\\n\", \"\")\n",
    "        return extract_and_convert_json(s)\n",
    "\n",
    "    except:\n",
    "        print(\"errored out returning completion\")\n",
    "        return completion\n",
    "\n",
    "\n",
    "# line_items[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# In batches of 10, use OpenAI to determine likelihood each transaction is with a lender\n",
    "num_subsets = len(line_items) // 10 + 1\n",
    "for i in range(num_subsets):\n",
    "    line_item_subset = line_items[10 * i : 10 * i + 10]\n",
    "    likelihoods = get_likelihood_of_being_lender(line_item_subset)\n",
    "    for obj in likelihoods:\n",
    "        line_items[obj[\"index\"]][\"is_lender_likelihood\"] = obj[\"is_lender_likelihood\"]\n",
    "        line_items[obj[\"index\"]][\"is_lender_likelihood_reason\"] = obj[\"reason\"]\n",
    "    print(f\"Added items to subset ({i} / {num_subsets}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# biggest transactions\n",
    "biggest_transactions = [\n",
    "    {\n",
    "        'description': t['description'], \n",
    "        'credit': t['credit'],\n",
    "        'debit': t['debit'],\n",
    "        'is_lender_likelihood': t['is_lender_likelihood'],\n",
    "        'size_relative_to_average_balance': float((t['credit'] if t['credit'] else t['debit']) / average_balance),\n",
    "    }\n",
    "for t in sorted(line_items, key=lambda t: t['credit'] if t['credit'] else t['debit'], reverse=True)\n",
    "][:5]\n",
    "biggest_transactions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from datetime import datetime\n",
    "\n",
    "# Compute total debits, credits to lenders\n",
    "credits_to_lenders = sum(i['credit'] for i in line_items if i['is_lender_likelihood'] and i['credit'])\n",
    "print('Credits to lenders', credits_to_lenders)\n",
    "debits_to_lenders = sum(i['debit'] for i in line_items if i['is_lender_likelihood'] and i['debit'])\n",
    "print('Debits to lenders', debits_to_lenders)\n",
    "non_lender_volume = sum(i['credit'] if i['credit'] else i['debit'] for i in line_items if not i['is_lender_likelihood'])\n",
    "lender_volume_to_nonlender_volume = (debits_to_lenders + credits_to_lenders) / non_lender_volume if non_lender_volume else 'N/A Automatic rejection no non-lender credits'\n",
    "print(f'lender_volume_to_nonlender_valume: {lender_volume_to_nonlender_volume:.4f}')\n",
    "\n",
    "period_start_date_str = data['Basic Information']['Start Date']['orig_value']\n",
    "period_start_date = datetime.strptime(period_start_date_str, '%m/%d/%Y').date()\n",
    "period_end_date_str = data['Basic Information']['End Date']['orig_value']\n",
    "period_end_date = datetime.strptime(period_end_date_str, '%m/%d/%Y').date()\n",
    "days_in_period = (period_end_date - period_start_date).days + 1\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_str_from_dict(d) -> str:\n",
    "    \"\"\"Convert nested dict with decimals to str.\n",
    "    Example: \n",
    "    Input: {'transactions': [{'credits': decimal.Decimal(\"1.23\")}]}\n",
    "    Output {'transactions': [{'credits': 1.23}]}\n",
    "    \"\"\"\n",
    "    \n",
    "    def _str_from_dict(obj):\n",
    "        if isinstance(obj, dict):\n",
    "            return {k: _str_from_dict(v) for k, v in obj.items()}\n",
    "        elif isinstance(obj, list):\n",
    "            return [_str_from_dict(x) for x in obj]\n",
    "        elif isinstance(obj, decimal.Decimal):\n",
    "            return round(float(obj), 4)\n",
    "        elif isinstance(obj, float):\n",
    "            return round(obj, 4)\n",
    "        else:\n",
    "            return obj\n",
    "        \n",
    "    processed_d = _str_from_dict(d)\n",
    "    return json.dumps(processed_d, indent=4)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Received response from OpenAI\n",
      "# Loan Applicant Assessment Report\n",
      "\n",
      "The following analysis has been conducted based on the data predominantly extracted from a single bank statement, which may not fully represent the financial behavior or creditworthiness of the applicant over a longer period of time. Therefore, the conclusion should be considered within this limited scope.\n",
      "\n",
      "## Largest Transactions\n",
      "- A credit of 47896.0 from JANALAKSHMI FINA with a 21.7062 size relative to average balance.\n",
      "- A debit of 47800.0 paid to NANGARE with a 21.6627 size relative to average balance.\n",
      "- A credit of 9000.0 from a Fund Transfer, has a 4.0787 size relative to average balance.\n",
      "- A debit of 8320.0 paid towards Reliance Energy Bill, with a 3.7706 size relative to average balance.\n",
      "- A debit of 8204.21 paid at FINNOVATION TECH SOLUT, with a 3.7181 size relative to average balance.\n",
      "\n",
      "## Lenders vs Non-Lenders Transactions\n",
      "The total credits received from lenders were 66080.0 while debits paid to lenders were 13144.0. The ratio of lender's transaction volume to non-lender's transaction volume is 0.3044. This suggests that transactions with lenders make up a significant part of this account's activity.\n",
      "\n",
      "## Account Balance\n",
      "There were 53 days when the balance fell below 200. Given that the total observation period was 365 days, this calculates to a low balance ratio of 0.1452. This means the account was low balance, i.e., below 200 dollars, for approximately 14.52% of the time in the given period.\n",
      "\n",
      "Based on these factors, the account holder appears to have a significant borrowing activity with a measure of irregular income or expenditure given the lower balance days. The loan decision should be made considering these factors and further information might be needed to make a more informed decision.\n"
     ]
    }
   ],
   "source": [
    "system_content = \"\"\"\n",
    "Your job is to write a report regarding whether to make a loan to a loan applicant.\n",
    "You are only given information from a single bank statement. This is limiting and you will comment on how this is limiting,\n",
    "but you will still draw a conclusions.\n",
    "\n",
    "You are given\n",
    "- the sum of debits and credits for each month\n",
    "- the total credits paid to lenders\n",
    "- the total debits paid to lenders\n",
    "- days in period\n",
    "- days with a balance below 200 dollars.\n",
    "\n",
    "A Debit is cash flowing out of the users account.\n",
    "A Credit is cash flowing into the account.\n",
    "\n",
    "The maximum length of the report should be 500 words.\n",
    "The report should be formatted as markdown.\n",
    "- Display a bulletted list of largest transactions.\n",
    "- Comments on the volume of transactions with lenders, relative to the total volume of transactions.\n",
    "- Do not include an images are references to outside urls.\n",
    "\n",
    "Your output must be JSON.  Do not add additional commentary because it will break the JSON parser.\n",
    "\"\"\"\n",
    "\n",
    "detail = {\n",
    "        \"monthly_transactions\": monthly_data_str,\n",
    "        \"total_credits_to_lenders\": credits_to_lenders,\n",
    "        \"total_debits_to_lenders\": debits_to_lenders,\n",
    "        \"lender_volume_to_nonlender_volume\": lender_volume_to_nonlender_volume,\n",
    "        \"days_in_period\": days_in_period,\n",
    "        \"days_balance_below_200\": days_balance_below_200,\n",
    "        \"low_balance_ratio\": days_balance_below_200 / days_in_period,\n",
    "        \"biggest_transactions\": biggest_transactions\n",
    "}\n",
    "\n",
    "detail_str = get_str_from_dict(detail)\n",
    "\n",
    "completion = client.chat.completions.create(\n",
    "    model=\"gpt-4\",\n",
    "    messages=[\n",
    "        {\n",
    "            \"role\": \"system\",\n",
    "            \"content\": system_content\n",
    "        },\n",
    "        {\n",
    "            \"role\": \"user\", \"content\": detail_str\n",
    "        },\n",
    "    ],\n",
    ")\n",
    "print(\"Received response from OpenAI\")\n",
    "try:\n",
    "    s = completion.choices[0].message.content\n",
    "\n",
    "except:\n",
    "    print(\"errored out returning completion\")\n",
    "    s = completion\n",
    "print(s)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/7r/0pxsk6n909s0q0y16qqthgp00000gn/T/ipykernel_16270/4014025121.py:8: DeprecationWarning: Importing display from IPython.core.display is deprecated since IPython 7.14, please import from IPython display\n",
      "  from IPython.core.display import display, HTML\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<h1>Loan Applicant Assessment Report</h1>\n",
       "<p>The following analysis has been conducted based on the data predominantly extracted from a single bank statement, which may not fully represent the financial behavior or creditworthiness of the applicant over a longer period of time. Therefore, the conclusion should be considered within this limited scope.</p>\n",
       "<h2>Largest Transactions</h2>\n",
       "<ul>\n",
       "<li>A credit of 47896.0 from JANALAKSHMI FINA with a 21.7062 size relative to average balance.</li>\n",
       "<li>A debit of 47800.0 paid to NANGARE with a 21.6627 size relative to average balance.</li>\n",
       "<li>A credit of 9000.0 from a Fund Transfer, has a 4.0787 size relative to average balance.</li>\n",
       "<li>A debit of 8320.0 paid towards Reliance Energy Bill, with a 3.7706 size relative to average balance.</li>\n",
       "<li>A debit of 8204.21 paid at FINNOVATION TECH SOLUT, with a 3.7181 size relative to average balance.</li>\n",
       "</ul>\n",
       "<h2>Lenders vs Non-Lenders Transactions</h2>\n",
       "<p>The total credits received from lenders were 66080.0 while debits paid to lenders were 13144.0. The ratio of lender's transaction volume to non-lender's transaction volume is 0.3044. This suggests that transactions with lenders make up a significant part of this account's activity.</p>\n",
       "<h2>Account Balance</h2>\n",
       "<p>There were 53 days when the balance fell below 200. Given that the total observation period was 365 days, this calculates to a low balance ratio of 0.1452. This means the account was low balance, i.e., below 200 dollars, for approximately 14.52% of the time in the given period.</p>\n",
       "<p>Based on these factors, the account holder appears to have a significant borrowing activity with a measure of irregular income or expenditure given the lower balance days. The loan decision should be made considering these factors and further information might be needed to make a more informed decision.</p>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import markdown\n",
    "# Convert Markdown to HTML\n",
    "\n",
    "text = completion.choices[0].message.content\n",
    "html_content = markdown.markdown(text)\n",
    "\n",
    "# Convert HTML to PDF\n",
    "from IPython.core.display import display, HTML\n",
    "display(HTML(html_content))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "# Use report lab to save html_content to 'credit_analysis.pdf'\n",
    "# Output retains the HTML formatting.\n",
    "from xhtml2pdf import pisa\n",
    "import io\n",
    "\n",
    "file_name = 'credit_analysis.pdf'\n",
    "\n",
    "# Create a file-like object to hold the pdf data\n",
    "pdf_file = io.BytesIO()\n",
    "\n",
    "# Convert the HTML to PDF\n",
    "pisa_status = pisa.CreatePDF(html_content, dest=pdf_file)\n",
    "\n",
    "# If there was an error, print it\n",
    "if pisa_status.err:\n",
    "    print(\"There was an error converting HTML to PDF\")\n",
    "\n",
    "# Write the PDF data to a file\n",
    "with open(file_name, 'wb') as output_file:\n",
    "    output_file.write(pdf_file.getvalue())\n",
    "\n",
    "# open file using system file viewer\n",
    "import os\n",
    "os.system(f'open \"{file_name}\"')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "venv",
   "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.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}