{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# Extract info from PDFs — v2\n", "\n", "follows four stages: **ingest → preprocess → pattern-match → validate**, and adds explicit source traceability (`source_file`, `page_number`) and a validation layer, as recommended there.\n", "\n", "Target document: `Sample2025 Credit Card Statements.pdf` — a synthetic U.S. Bank-style statement:\n", "* Two dates per transaction (`Post Date`, `Trans Date`), each `MM/DD` **without a year**.\n", "* A reference number column.\n", "* A single amount per line (no cashback column).\n", "* Transactions split across two sections — *Purchases and Other Debits* and *Payments and Other Credits* — which is the only place the sign of each amount is implied.\n", "* Transactions continue across two separate pages." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Import libraries" ] }, { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [], "source": [ "import re\n", "from pathlib import Path\n", "from datetime import date, datetime\n", "\n", "import pypdf\n", "import pandas as pd" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## View PDF text\n", "### View actual PDF" ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Page 1:\n", "\n", "1010101010101010101010101111110000001011101000110000100110101001111101000000110110010100001001011010100011101100001010011000100000110010010010010111110110111011110011000011111001001100101000100100110110000000100100100000101010000100000010111000001100111011010010100000110000001011100011011100000010001001100000110011100110111010101101001100110001011110111101011000100110111011001011111111111111111111\n", "Open Date: 12/17/2024 Closing Date: 01/15/2025 Account: **** **** **** 8897\n", "Page 1 of 3\n", "1-866-485-4545\n", "CRESC CITY HARBOR DST (CPN 001643647)\n", "Earned This Statement $23.24\n", "Reward Dollars Available $4,767.07\n", "For details, see your rewards summary.\n", "Previous Balance + $4,248.00\n", "Payments - $4,248.00\n", "Other Credits $0.00\n", "Purchases + $2,324.22\n", "Balance Transfers $0.00\n", "Advances $0.00\n", "Other Debits $0.00\n", "Fees Charged $0.00\n", "Interest Charged $0.00\n", "Credit Line $14,000.00\n", "Available Credit $11,675.78\n", "Days in Billing Period 30\n", ".\n", ".\n", "New Balance $2,324.22\n", "Minimum Payment Due $1,163.00\n", "Payment Due Date 02/11/2025\n", "Cash Rewards\n", "Activity Summary\n", "January 2025 Statement\n", "Payment\n", "Options:\n", "U.S. Bank\n", "U.S. Bank Community Card Cardmember Service\n", "New Balance = $2,324.22\n", "Past Due $0.00\n", "Minimum Payment Due $1,163.00\n", "Please detach and send coupon with check payable to: U.S. Bank\n", "Mail payment coupon Pay online at Pay by phone Pay at your local\n", "with a check usbank.com 1-866-485-4545 U.S. Bank branch\n", "USB 8 BUS 35 10\n", "CR\n", "TATDDDADDDATTADFFFDTTDATTADFTADTDDFDATDAADDTFFTATTAFADDDDDFFFAFAD DDATTDFAATFDAFDTFTDDTFAFFDATFFTDTTDDFTFAFFDFDDTDADATAAFTAFFDTDFAA\n", "000002469 01 SP 000638891958084 P Y\n", "CRESC CITY HARBOR DST\n", "ACCOUNTS PAYABLE\n", "101 CITIZENS DOCK RD\n", "CRESCENT CITY CA 95531-4435\n", "CPN 001643647\n", "Account Number\n", "Payment Due Date\n", "New Balance\n", "Minimum Payment Due to pay by phone\n", " to change your address\n", "Amount Enclosed\n", "**** **** **** 8897\n", "2/11/2025\n", "$2,324.22\n", "$1,163.00\n", "24-Hour Cardmember Service: 1-866-485-4545\n", "$\n", "P.O. Box 790408\n", "St. Louis, MO 63179-0408\n", "0055928400010088970001163000002324221\n", "\n", "\n", "####################################################################################################\n", "\n", "Page 2:\n", "\n", "Account information:\n", "Dollar amount:\n", "Description of Problem:\n", "What To Do If You Think You Find A Mistake On Your Statement\n", "Your Rights If You Are Dissatisfied With Your Credit Card Purchases\n", "Important Information Regarding Your Account\n", "INTEREST CHARGE:\n", "INTEREST CHARGE \"DPR\" \"ADB\"\n", "ADB ADB\n", "ADB\n", "ADB\n", "Payment Information:\n", "Credit Reporting:\n", "If you think there is an error on your statement, please call us at the telephone number on the front of this statement, or write to us at:\n", "Cardmember Service, P.O. Box 6335, Fargo, ND 58125-6335.\n", "In your letter or call, give us the following information:\n", " Your name and account number.\n", " The dollar amount of the suspected error.\n", " If you think there is an error on your bill, describe what you believe is wrong and why you believe it is a mistake.\n", "You must contact us within 60 days after the error appeared on your statement. While we investigate whether or not there has been an error,\n", "the following are true:\n", "We cannot try to collect the amount in question, or report you as delinquent on that amount.\n", "The charge in question may remain on your statement, and we may continue to charge you interest on that amount. But, if we determine\n", "that we made a mistake, you will not have to pay the amount in question or any interest or other fees related to that amount.\n", "While you do not have to pay the amount in question, you are responsible for the remainder of your balance.\n", "We can apply any unpaid amount against your credit limit.\n", "If you are dissatisfied with the goods or services that you have purchased with your credit card, and you have tried in good faith to correct the\n", "problem with the merchant, you may have the right not to pay the remaining amount due on the purchase.\n", "To use this right, all of the following must be true:\n", "1. The purchase must have been made in your home state or within 100 miles of your current mailing address, and the purchase price must\n", "have been more than $50. (Note: Neither of these are necessary if your purchase was based on an advertisement we mailed to you, or if we\n", "own the company that sold you the goods or services.)\n", "2. You must have used your credit card for the purchase. Purchases made with cash advances from an ATM or with a check that accesses\n", "your credit card account do not qualify.\n", "3. You must not yet have fully paid for the purchase.\n", "If all of the criteria above are met and you are still dissatisfied with the purchase, contact us in writing at: Cardmember Service, P.O. Box\n", "6335, Fargo, ND 58125-6335\n", "While we investigate, the same rules apply to the disputed amount as discussed above. After we finish our investigation, we will tell you our\n", "decision. At that point, if we think you owe an amount and you do not pay we may report you as delinquent.\n", "1. Method of Computing Balance Subject to Interest Rate: We calculate the periodic rate or interest portion of the\n", " by multiplying the applicable Daily Periodic Rate ( ) by the Average Daily Balance ( ) (including new\n", "transactions) of the Purchase, Advance and Balance Transfer categories subject to interest, and then adding together the resulting interest\n", "from each category. We determine the separately for the Purchases, Advances and Balance Transfer categories. To get the in\n", "each category, we add together the daily balances in those categories for the billing cycle and divide the result by the number of days in the\n", "billing cycle. We determine the daily balances each day by taking the beginning balance of those Account categories (including any billed but\n", "unpaid interest, fees, credit insurance and other charges), adding any new interest, fees, and charges, and subtracting any payments or\n", "credits applied against your Account balances that day. We add a Purchase, Advance or Balance Transfer to the appropriate balances for\n", "those categories on the later of the transaction date or the first day of the statement period. Billed but unpaid interest on Purchases, Advances\n", "and Balance Transfers is added to the appropriate balances for those categories each month on the statement date. Billed but unpaid\n", "Advance Transaction Fees are added to the Advance balance of your Account on the date they are charged to your Account. Any billed but\n", "unpaid fees on Purchases, credit insurance charges, and other charges are added to the Purchase balance of the Account on the date they\n", "are charged to the Account. Billed but unpaid fees on Balance Transfers are added to the Balance Transfer balance of the Account on the\n", "date they are charged to the Account. In other words, billed and unpaid interest, fees, and charges will be included in the of your\n", "Account that accrues interest and will reduce the amount of credit available to you.To the extent credit insurance charges, overlimit fees,\n", "Annual Fees, and/or Travel Membership Fees may be applied to your Account, such charges and/or fees are not included in the ADB\n", "calculation for Purchases until the first day of the billing cycle following the date the credit insurance charges, overlimit fees, Annual Fees\n", "and/or Travel Membership Fees (as applicable) are charged to the Account. Prior statement balances subject to an interest-free period that\n", "have been paid on or before the payment due date in the current billing cycle are not included in the calculation.\n", "2. We will accept payment via check, money order, the internet (including mobile and online) or phone or previously\n", "established automatic payment transaction. You must pay us in U.S. Dollars. If you make a payment from a foreign financial institution, you\n", "will be charged and agree to pay any collection fees added in connection with that transaction. The date you mail a payment is different than\n", "the date we receive the payment. The payment date is the day we receive your check or money order at U.S. Bank National Association, P.O.\n", "Box 790408, St. Louis, MO 63179-0408 or the day we receive your internet or phone payment. All payments by check or money order\n", "accompanied by a payment coupon and received at this payment address will be credited to your Account on the day of receipt if received by\n", "5:00 p.m. CT on any banking day. Payments sent without the payment coupon or to an incorrect address will be processed and credited to\n", "your Account within 5 banking days of receipt. Payments sent without a payment coupon or to an incorrect address may result in a delayed\n", "credit to your Account, additional interest charges, fees, and/or Account suspension. The deadline for on-time internet and phone payments\n", "varies, but generally must be made before 5:00 p.m. CT to 8 p.m. CT depending on what day and how the payment is made. Please contact\n", "Cardmember Service for internet, phone, and mobile crediting times specific to your Account and your payment option. Banking days are all\n", "calendar days except Saturday, Sunday and federal holidays. Payments due on a Saturday, Sunday or federal holiday and received on those\n", "days will be credited on the day of receipt. There is no prepayment penalty if you pay your balance at any time prior to your payment due\n", "date.\n", "3. We may report information on your Account to Credit Bureaus. Late payments, missed payments or other defaults on\n", "your Account may be reflected in your credit report.\n", "u\n", "u\n", "u\n", "u\n", "u\n", "u\n", "u\n", "\n", "####################################################################################################\n", "\n", "Page 3:\n", "\n", "1010101010101010101010101111110001001011101000110000100110101001111101001011110110010100001001001010100011110000011111011000100000110011000010010111110111000111110011000011000100101100101001010010010110000000101110100000101010000110100000111000001100101101000010100110111110100111100011001110011011001001000001011110110110110011000110110100110010011111100011011000100100111001101011111111111111111111\n", "12/17/2024 - 01/15/2025\n", "CRESC CITY HARBOR DST (CPN 001643647) 1-866-485-4545\n", "Page 2 of 3\n", "$4,743.83\n", "$0.00\n", "Triple Rwds For Cell Phone/Service Prov. $0.00\n", "Triple Rewards For Gas Stations $0.00\n", "Triple Rewards For Office Supply Stores $0.00\n", "Rewards for all other purchases $0.00\n", "Cash Rewards $23.24\n", "Login at usbank.com\n", "or call 1-866-485-4545\n", "U.S. Bank Rewards Card\n", "Statement Credit\n", "Direct Deposit to U.S. Bank\n", "Checking\n", "Savings\n", "Money Market\n", "Paying Interest: You have a 24 to 30 day interest-free period for Purchases provided you have paid your\n", "previous balance in full by the Payment Due Date shown on your monthly Account statement. In order to\n", "avoid additional INTEREST CHARGES on Purchases, you must pay your new balance in full by the\n", "Payment Due Date shown on the front of your monthly Account statement.\n", "There is no interest-free period for transactions that post to the Account as Advances or Balance Transfers\n", "except as provided in any Offer Materials. Those transactions are subject to interest from the date they post\n", "to the Account until the date they are paid in full.\n", "Skip the mailbox. Switch to e-statements and securely access your statements online. Get started at\n", "usbank.com/login.\n", "HANKS,KRISTINA M\n", "January 2025 Statement\n", "Cardmember Service\n", "Rewards Available Last Statement\n", "Redemption Activity\n", "Reward Dollars Earned This Statement\n", "Total Earned $23.24\n", "Total Reward Dollars Available $4,767.07\n", "To Redeem:\n", "Redemption Options:\n", "Purchases and Other Debits\n", "Cash Rewards Summary\n", "Important Messages\n", "Transactions\n", "Continued on Next Page\n", "Credit Limit $5000\n", "Post\n", "Date\n", "Trans\n", "Date Ref # Transaction Description Amount Notation\n", "Total for Account **** **** **** 4509 $2,324.22\n", "12/19 12/18 5389 USPS PO 0518780457 CRESCENT CITY CA $73.00\n", "12/23 12/19 1157 ELK VALLEY FUEL MART CRESCENT CITY CA $42.00\n", "12/23 12/20 3538 CANVA* I04372-0513440 CAMDEN DE $300.00\n", "12/27 12/26 1664 ADOBE *ADOBE 4085366000 CA $19.99\n", "12/30 12/28 7434 Amazon.com*ZE55N46D0 Amzn.com/bill WA $68.00\n", "12/30 12/29 4396 DOCKWA.COM NEWPORT RI $1,062.50\n", "12/31 12/30 4688 USPS PO 0518780457 CRESCENT CITY CA $0.73\n", "01/02 12/30 7924 ELK VALLEY FUEL MART CRESCENT CITY CA $20.00\n", "01/06 01/03 3208 TMOBILE*AUTO PAY 800-937-8997 WA $318.00\n", "01/06 01/03 8139 ELK VALLEY FUEL MART CRESCENT CITY CA $55.00\n", "01/08 01/07 8402 INTUIT *QBooks Online CL.INTUIT.COM CA $235.00\n", "01/08 01/06 4402 ELK VALLEY FUEL MART CRESCENT CITY CA $45.00\n", "01/13 01/10 2328 ELK VALLEY FUEL MART CRESCENT CITY CA $40.00\n", "01/15 01/14 6260 ELK VALLEY FUEL MART CRESCENT CITY CA $45.00\n", "\n", "####################################################################################################\n", "\n", "Page 4:\n", "\n", "12/17/2024 - 01/15/2025\n", "CRESC CITY HARBOR DST (CPN 001643647) 1-866-485-4545\n", "Page 3 of 3\n", "BILLING ACCOUNT ACTIVITY\n", "**\n", "January 2025 Statement\n", "Cardmember Service\n", "Payments and Other Credits\n", "Transactions\n", "2025 Totals Year-to-Date\n", "Interest Charge Calculation\n", "Contact Us\n", "End of Statement\n", "Balance Type\n", "Balance\n", "By Type\n", "Balance\n", "Subject to\n", "Interest Rate Variable\n", "Interest\n", "Charge\n", "Annual\n", "Percentage\n", "Rate\n", "Expires\n", "with\n", "Statement\n", "CR\n", "You may change your email marketing preferences at any time in the Privacy section of usbank.com. Note that confidential, personal or financial\n", "information will never be sent or requested in an email from U.S. Bank.\n", "Earn more rewards: update your\n", "email address at usbank.com.\n", "Dont miss out on exclusive reward offers and important updates.\n", "Make sure we have your current email address by updating your profile\n", "at usbank.com and opting into marketing messages.\n", "**BALANCE TRANSFER $0.00 $0.00 YES $0.00 18.24%\n", "**PURCHASES $2,324.22 $0.00 YES $0.00 18.24%\n", "**ADVANCES $0.00 $0.00 YES $0.00 28.24%\n", "Voice: 1-866-485-4545 Cardmember Service U.S. Bank usbank.com\n", "TDD: 1-888-352-6455 P.O. Box 6353 P.O. Box 790408\n", "Fax: 1-866-807-9053 Fargo, ND 58125-6353 St. Louis, MO 63179-0408\n", "CR\n", "CRESC CITY HARBOR DST\n", "Post\n", "Date\n", "Trans\n", "Date Ref # Transaction Description Amount Notation\n", "Total for Account **** **** **** 8897 $4,248.00\n", "Your Annual Percentage Rate (APR) is the annual interest rate on your account.\n", "01/08 01/08 ET PAYMENT THANK YOU $4,248.00\n", "Total Fees Charged in 2025 $0.00\n", "Total Interest Charged in 2025 $0.00\n", "APR for current and future transactions.\n", "Phone Questions Mail payment coupon\n", "with a check\n", "Online\n", "\n", "####################################################################################################\n", "\n" ] } ], "source": [ "PDF_PATH = Path('Sample2025 Credit Card Statements.pdf')\n", "\n", "\n", "# Function to print the content of a PDF document\n", "def print_pdf_content(pdf_path):\n", " with open(pdf_path, 'rb') as file:\n", " reader = pypdf.PdfReader(file)\n", " for page_num in range(len(reader.pages)):\n", " print(f\"Page {page_num + 1}:\\n\")\n", " page = reader.pages[page_num]\n", " page_text = page.extract_text()\n", " print(page_text)\n", " print(\"\\n\" + \"#\" * 100 + \"\\n\") # Print a separator between pages\n", "\n", "\n", "# Now call the function to print the content of the document\n", "print_pdf_content(PDF_PATH)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Functions definition\n", "### Document ingestion" ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [], "source": [ "def extract_pdf_pages(pdf_path: Path) -> list[dict]:\n", " \"\"\"Extract page-level text while retaining source metadata for traceability.\"\"\"\n", " reader = pypdf.PdfReader(str(pdf_path))\n", " pages = []\n", " for page_number, page in enumerate(reader.pages, start=1):\n", " pages.append(\n", " {\n", " \"source_file\": pdf_path.name,\n", " \"page_number\": page_number,\n", " \"raw_text\": page.extract_text() or \"\",\n", " }\n", " )\n", " return pages" ] }, { "cell_type": "markdown", "id": "03dee538", "metadata": {}, "source": [ "### Statement metadata\n", "\n", "Transaction lines only carry `MM/DD`, so the statement's `Open Date` / `Closing Date` and account number are pulled from page 1 first." ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [], "source": [ "PERIOD_PATTERN = re.compile(\n", " r\"Open Date:\\s*(?P\\d{2}/\\d{2}/\\d{4})\\s*\"\n", " r\"Closing Date:\\s*(?P\\d{2}/\\d{2}/\\d{4})\",\n", ")\n", "\n", "ACCOUNT_PATTERN = re.compile(r\"Account:\\s*\\*{4}\\s*\\*{4}\\s*\\*{4}\\s*(?P\\d{4})\")\n", "\n", "\n", "def extract_statement_metadata(pages: list[dict]) -> dict:\n", " \"\"\"Pull statement period and account number from page 1 for year resolution and traceability.\"\"\"\n", " first_page_text = pages[0][\"raw_text\"]\n", "\n", " period_match = PERIOD_PATTERN.search(first_page_text)\n", " account_match = ACCOUNT_PATTERN.search(first_page_text)\n", "\n", " period_start = (\n", " datetime.strptime(period_match.group(\"open\"), \"%m/%d/%Y\").date()\n", " if period_match else None\n", " )\n", " period_end = (\n", " datetime.strptime(period_match.group(\"close\"), \"%m/%d/%Y\").date()\n", " if period_match else None\n", " )\n", "\n", " return {\n", " \"period_start\": period_start,\n", " \"period_end\": period_end,\n", " \"account_last4\": account_match.group(\"last4\") if account_match else None,\n", " }\n", "\n", "\n", "def resolve_year(month_day: str, period_start: date, period_end: date) -> int:\n", " \"\"\"A transaction only carries MM/DD. Pick whichever candidate year keeps\n", " the date inside the statement period (statements can cross a year end).\"\"\"\n", " month, day = (int(part) for part in month_day.split(\"/\"))\n", " for year in {period_start.year, period_end.year}:\n", " candidate = date(year, month, day)\n", " if period_start <= candidate <= period_end:\n", " return year\n", " return period_end.year" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Text normalization" ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [], "source": [ "def normalize_text(value: str) -> str:\n", " \"\"\"Standardize PDF text before applying transaction patterns.\"\"\"\n", " value = value.replace(\"\\u00a0\", \" \")\n", " value = value.replace(\"\\r\", \"\\n\")\n", " value = re.sub(r\"[ \\t]+\", \" \", value)\n", " return value.strip()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Transaction pattern\n", "\n", "Example lines the pattern needs to match:\n", "```\n", "12/19 12/18 5389 USPS PO 0518780457 CRESCENT CITY CA $73.00\n", "01/08 01/08 ET PAYMENT THANK YOU $4,248.00\n", "```\n", "Two `MM/DD` dates, a reference code (digits or letters like `ET`), a free-text description, and a single dollar amount that may include a thousands comma. `SECTION_MARKERS` is used to track which of the two transaction sections (*Purchases* vs *Payments*) a line falls under, since that determines the sign." ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [], "source": [ "TRANSACTION_PATTERN = re.compile(\n", " r\"(?P\\d{2}/\\d{2})\\s+\"\n", " r\"(?P\\d{2}/\\d{2})\\s+\"\n", " r\"(?P[A-Za-z0-9]+)\\s+\"\n", " r\"(?P.*?)\\s+\"\n", " r\"\\$(?P[\\d,]+\\.\\d{2})\",\n", " flags=re.IGNORECASE,\n", ")\n", "\n", "SECTION_MARKERS = {\n", " \"Purchases and Other Debits\": \"Purchase\",\n", " \"Payments and Other Credits\": \"Payment/Credit\",\n", "}" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Parsing and traceability" ] }, { "cell_type": "code", "execution_count": 7, "metadata": {}, "outputs": [], "source": [ "def parse_transactions(pages: list[dict], metadata: dict) -> pd.DataFrame:\n", " records = []\n", " current_section = None\n", "\n", " for page in pages:\n", " for line in normalize_text(page[\"raw_text\"]).split(\"\\n\"):\n", " for marker, label in SECTION_MARKERS.items():\n", " if marker in line:\n", " current_section = label\n", "\n", " match = TRANSACTION_PATTERN.search(line)\n", " if not match:\n", " continue\n", "\n", " data = match.groupdict()\n", " year = resolve_year(\n", " data[\"post_date\"], metadata[\"period_start\"], metadata[\"period_end\"]\n", " )\n", " post_date = pd.to_datetime(\n", " f\"{data['post_date']}/{year}\", format=\"%m/%d/%Y\", errors=\"coerce\"\n", " )\n", "\n", " amount = float(data[\"amount\"].replace(\",\", \"\"))\n", " section = current_section or \"Unclassified\"\n", " signed_amount = -amount if section == \"Payment/Credit\" else amount\n", "\n", " records.append(\n", " {\n", " \"source_file\": page[\"source_file\"],\n", " \"page_number\": page[\"page_number\"],\n", " \"account_last4\": metadata[\"account_last4\"],\n", " \"post_date\": post_date,\n", " \"trans_date\": data[\"trans_date\"],\n", " \"ref\": data[\"ref\"],\n", " \"description\": data[\"description\"].strip(),\n", " \"amount\": signed_amount,\n", " \"type\": section,\n", " \"matched_text\": match.group(0).strip(),\n", " }\n", " )\n", "\n", " return pd.DataFrame.from_records(records)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Validation layer" ] }, { "cell_type": "code", "execution_count": 8, "metadata": {}, "outputs": [], "source": [ "def validate_transactions(df: pd.DataFrame) -> pd.DataFrame:\n", " validated = df.copy()\n", "\n", " validated[\"is_complete\"] = (\n", " validated[[\"post_date\", \"description\", \"amount\"]].notna().all(axis=1)\n", " )\n", " validated[\"is_duplicate\"] = validated.duplicated(\n", " subset=[\"source_file\", \"post_date\", \"ref\", \"description\", \"amount\"],\n", " keep=False,\n", " )\n", " validated[\"requires_review\"] = (\n", " ~validated[\"is_complete\"] | validated[\"is_duplicate\"]\n", " )\n", "\n", " return validated" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Running the pipeline against `Sample2025 Credit Card Statements.pdf`" ] }, { "cell_type": "code", "execution_count": 9, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "{'period_start': datetime.date(2024, 12, 17), 'period_end': datetime.date(2025, 1, 15), 'account_last4': '8897'}" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "pages = extract_pdf_pages(PDF_PATH)\n", "metadata = extract_statement_metadata(pages)\n", "metadata" ] }, { "cell_type": "code", "execution_count": 10, "metadata": {}, "outputs": [ { "data": { "text/plain": [ " source_file ... matched_text\n", "0 Sample2025 Credit Card Statements.pdf ... 12/19 12/18 5389 USPS PO 0518780457 CRESCENT C...\n", "1 Sample2025 Credit Card Statements.pdf ... 12/23 12/19 1157 ELK VALLEY FUEL MART CRESCENT...\n", "2 Sample2025 Credit Card Statements.pdf ... 12/23 12/20 3538 CANVA* I04372-0513440 CAMDEN ...\n", "3 Sample2025 Credit Card Statements.pdf ... 12/27 12/26 1664 ADOBE *ADOBE 4085366000 CA $1...\n", "4 Sample2025 Credit Card Statements.pdf ... 12/30 12/28 7434 Amazon.com*ZE55N46D0 Amzn.com...\n", "5 Sample2025 Credit Card Statements.pdf ... 12/30 12/29 4396 DOCKWA.COM NEWPORT RI $1,062.50\n", "6 Sample2025 Credit Card Statements.pdf ... 12/31 12/30 4688 USPS PO 0518780457 CRESCENT C...\n", "7 Sample2025 Credit Card Statements.pdf ... 01/02 12/30 7924 ELK VALLEY FUEL MART CRESCENT...\n", "8 Sample2025 Credit Card Statements.pdf ... 01/06 01/03 3208 TMOBILE*AUTO PAY 800-937-8997...\n", "9 Sample2025 Credit Card Statements.pdf ... 01/06 01/03 8139 ELK VALLEY FUEL MART CRESCENT...\n", "10 Sample2025 Credit Card Statements.pdf ... 01/08 01/07 8402 INTUIT *QBooks Online CL.INTU...\n", "11 Sample2025 Credit Card Statements.pdf ... 01/08 01/06 4402 ELK VALLEY FUEL MART CRESCENT...\n", "12 Sample2025 Credit Card Statements.pdf ... 01/13 01/10 2328 ELK VALLEY FUEL MART CRESCENT...\n", "13 Sample2025 Credit Card Statements.pdf ... 01/15 01/14 6260 ELK VALLEY FUEL MART CRESCENT...\n", "14 Sample2025 Credit Card Statements.pdf ... 01/08 01/08 ET PAYMENT THANK YOU $4,248.00\n", "\n", "[15 rows x 10 columns]" ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "transactions_df = parse_transactions(pages, metadata)\n", "transactions_df" ] }, { "cell_type": "code", "execution_count": 11, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "requires_review\n", "False 15\n", "Name: count, dtype: int64" ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "validated_df = validate_transactions(transactions_df)\n", "validated_df['requires_review'].value_counts()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Reconciliation against statement control totals\n", "\n", "Totals printed on the statement itself (`Purchases + $2,324.22`, `Payments - $4,248.00`) are extracted independently and compared against the sums of the parsed transactions." ] }, { "cell_type": "code", "execution_count": null, "id": "cba38255", "metadata": {}, "outputs": [ { "data": { "text/plain": [ " control_total extracted_total difference\n", "Purchases 2324.22 2324.22 -0.0\n", "Payments 4248.00 4248.00 0.0" ] }, "execution_count": 12, "metadata": {}, "output_type": "execute_result" } ], "source": [ "PURCHASES_PATTERN = re.compile(r\"Purchases\\s*\\+\\s*\\$(?P[\\d,]+\\.\\d{2})\")\n", "PAYMENTS_PATTERN = re.compile(r\"Payments\\s*-\\s*\\$(?P[\\d,]+\\.\\d{2})\")\n", "\n", "first_page_text = pages[0][\"raw_text\"]\n", "control_purchases = float(PURCHASES_PATTERN.search(first_page_text).group(\"purchases\").replace(\",\", \"\"))\n", "control_payments = float(PAYMENTS_PATTERN.search(first_page_text).group(\"payments\").replace(\",\", \"\"))\n", "\n", "extracted_purchases = transactions_df.loc[transactions_df[\"type\"] == \"Purchase\", \"amount\"].sum()\n", "extracted_payments = -transactions_df.loc[transactions_df[\"type\"] == \"Payment/Credit\", \"amount\"].sum()\n", "\n", "reconciliation = pd.DataFrame(\n", " {\n", " \"control_total\": [control_purchases, control_payments],\n", " \"extracted_total\": [extracted_purchases, extracted_payments],\n", " },\n", " index=[\"Purchases\", \"Payments\"],\n", ")\n", "reconciliation[\"difference\"] = (reconciliation[\"control_total\"] - reconciliation[\"extracted_total\"]).round(2)\n", "reconciliation" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Both rows reconcile to a `$0.00` difference — every purchase and payment line on the statement was captured and none were double-counted." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Export" ] }, { "cell_type": "code", "execution_count": 13, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "'Exported 15 transactions to Sample2025_transactions_extracted.csv'" ] }, "execution_count": 13, "metadata": {}, "output_type": "execute_result" } ], "source": [ "output_path = Path(\"Sample2025_transactions_extracted.csv\")\n", "validated_df.to_csv(output_path, index=False)\n", "f\"Exported {len(validated_df)} transactions to {output_path}\"" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "name": "python", "version": "3.13" } }, "nbformat": 4, "nbformat_minor": 5 }