#!/usr/bin/env python3 """ tax_category_mysql.py (v4 — accountant-grade output schema) Open-banking transaction classifier for UK SMEs. Connects to MySQL, classifies transactions, writes results back. ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ CLASSIFICATION LOGIC ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ DEBITS (money out): Ordered heuristic rule table (EXPENSE_RULES) — 22 expense categories. Rule 0 catches neobank/transfer descriptions (Monzo, Revolut, Starling…) before they can fall through to "Other business expenses". Rule 5 catches HMRC interest & penalties (NON-DEDUCTIBLE, CTA 2009 s.1303) before the generic HMRC catch-all. Rule 6 catches fines & regulatory penalties (NON-DEDUCTIBLE, CTA 2009 s.1298). Rule 14 catches professional body subscriptions (HMRC EIM32900). Rule 19 catches client entertaining (NON-DEDUCTIBLE for CT, VAT input blocked). Smart fallback: detects any neobank name or transfer keyword in unmatched descriptions and flags as "Transfer / owner drawings" rather than expense. CREDITS (money in): Ordered rule table (INCOME_RULES). Specific rules (R&D credit, VAT repayment, BBL) come before generic ones. COVID support schemes (SEISS, CJRS/furlough) are taxable trading receipts. ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ OUTPUT COLUMNS (all original columns preserved; these are added) ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ direction "expense" | "income" | "balance_sheet" | "unknown" tax_category Unified classification value; new categories include "HMRC interest & penalties", "Fines & penalties (non-deductible)", "Professional subscriptions", and "Client entertaining (non-deductible)" expense_tax_category Category for debit rows (None for credits) income_category Category for credit rows (None for debits) confidence 0.0–1.0 AI confidence score confidence_label "high" (≥0.85) | "medium" (0.70–0.84) | "low" (<0.70) needs_review True → accountant must act before posting False → AI confident; post as-is (still auditable) review_priority "urgent" | "normal" | "low" | "" review_reason Plain-English reason for the review flag (empty = none) classification_note Which keyword / rule triggered the AI decision is_deductible "yes" | "no" | "review" | "n/a" yes → allowable expense, reduces taxable profit no → not allowable (CT, VAT, drawings, fines) review → uncertain (capex?, mixed use?, personal?) n/a → income rows vat_treatment_hint standard_rated | zero_rated | exempt | outside_scope | blocked_input | mixed | unknown balance_sheet_item True = balance-sheet movement; exclude from P&L classified_at_utc ISO 8601 UTC timestamp location UK city/town extracted from description+merchant, or None merchant_normalised Clean human-readable merchant name (e.g. "Amazon Marketplace") payment_channel direct_debit | standing_order | faster_payment | bacs_transfer | chaps_transfer | card | atm | unknown is_recurring True if transaction is a scheduled/recurring payment counterparty_type hmrc | bank | payroll | lender | owner | card_processor | marketplace | utility | insurer | supplier | unknown mcc_hint Approximate ISO 18245 Merchant Category Code (4-digit string) invoice_ref Extracted invoice/PO/order reference from description, or None REVIEW PRIORITY RULES: urgent → balance-sheet items (excl. routine CT/VAT), any "review" category, transfers/drawings, amounts > £10,000 normal → confidence < 0.70, equipment capex flag, amounts £5,000–£10,000 low → confidence 0.70–0.84 (blank) → high confidence, standard P&L, no flags ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ CONFIGURATION ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ Required .env vars: MYSQL_HOST, MYSQL_PORT, MYSQL_DB, MYSQL_USER, MYSQL_PASSWORD TIMESTAMP_COLUMN column name holding transaction date/time MIN_CLASSIFICATION_YEAR only process rows from this year onwards Usage: python tax_category_mysql.py \\ --source-table dbo.BankTransactions \\ --target-table dbo.BankTransactions_Classified \\ [--if-exists replace|append|fail] \\ [--limit 500] \\ [--include-debug] Robust writing: Chunked writes → fallback to row-by-row on chunk failure Failed rows written to __write_errors """ from __future__ import annotations import os import re import argparse from datetime import datetime, timezone from typing import Optional, Tuple, List import numpy as np import pandas as pd from dotenv import load_dotenv from sqlalchemy import create_engine, text from sqlalchemy.engine import Engine from tqdm import tqdm TABLE_NAME_RE = re.compile(r"^([a-zA-Z_][a-zA-Z0-9_]*\.)?([a-zA-Z_][a-zA-Z0-9_]*)$") # --------------------------------------------------------------------------- # EXPENSE RULES — heuristic # # Each entry: (category, [keywords...], confidence) # Rules are tested IN ORDER. First matching keyword in any entry wins. # # Accounting ordering principles enforced here: # 1. Staff/payroll before general HMRC (HMRC PAYE ≠ "Other business expenses") # 2. Corporation tax before general HMRC (CT is a balance-sheet item, not P&L) # 3. VAT payment before general HMRC (balance-sheet, not P&L) # 4. HMRC interest & penalties BEFORE generic bank charges (NON-DEDUCTIBLE) # 5. Fines & penalties BEFORE generic catch-alls (NON-DEDUCTIBLE, CTA 2009 s.1298) # 6. GOOGLE ADS before GOOGLE (advertising ≠ office software) # 7. Specific insurer names before generic INSURANCE keyword # 8. Specific utility providers before generic ELECTRICITY/GAS keywords # 9. Professional subscriptions AFTER accountancy & legal (similar but distinct) # 10. Client entertaining BEFORE subsistence (non-deductible; VAT input blocked) # --------------------------------------------------------------------------- ExpenseRule = Tuple[str, List[str], float] EXPENSE_RULES: list[ExpenseRule] = [ # ── 0. Internal transfers & owner drawings ─────────────────────────────── # MUST be rule 0 — fintech bank names in a DEBIT description almost always # mean an own-account transfer or owner drawing, NOT a P&L expense. # "Sent from Monzo" / "Sent from Revolut" are the exact strings these apps # embed as payment references when the account holder moves their own money. ("Transfer / owner drawings (review)", [ # Monzo "SENT FROM MONZO", "FROM MONZO", "TO MONZO", "MONZO BANK", "MONZO TRANSFER", "MONZO PAYMENT", # Revolut "SENT FROM REVOLUT", "FROM REVOLUT", "TO REVOLUT", "REVOLUT TRANSFER", "REVOLUT PAYMENT", # Starling "SENT FROM STARLING", "FROM STARLING", "TO STARLING", "STARLING TRANSFER", "STARLING PAYMENT", # Wise / TransferWise "WISE TRANSFER", "TRANSFERWISE", "WISE COM", "WISE PAYMENT", # Other UK neobanks "CASHPLUS TRANSFER", "ANNA MONEY TRANSFER", "COCONUT TRANSFER", "TIDE TRANSFER", "ATOM BANK TRANSFER", "MONESE TRANSFER", "METTLE BANK", "METTLE TRANSFER", "COUNTINGUP TRANSFER", "ZEMPLER BANK", "SUITS ME TRANSFER", # Generic own-account / owner movements "OWN ACCOUNT TRANSFER", "INTERNAL TRANSFER", "DIRECTORS DRAWINGS", "DIRECTOR DRAWINGS", "OWNERS DRAWINGS", "OWNER DRAWINGS", "PERSONAL WITHDRAWAL", "PERSONAL TRANSFER", # ATM / cash "ATM WITHDRAWAL", "CASH MACHINE WITHDRAWAL", "CASH WITHDRAWAL", "ATM CASH", # Crypto exchanges — likely personal, not business "COINBASE", "BINANCE", "KRAKEN CRYPTO", ], 0.55), # ── 1. Staff costs ────────────────────────────────────────────────────── # Must come FIRST — "HMRC PAYE" must not fall into general "HMRC" bucket. # "AVIVA PENSION" must not fall into general "AVIVA INSURANCE" bucket. ("Staff costs", [ # HMRC PAYE (also referenced by HMRC's two banking offices) # Use full "HMRC PAYE CUMBERNAULD/SHIPLEY" — bare "HMRC CUMBERNAULD" also # handles CT/VAT in some older feeds and would wrongly classify as Staff costs. "HMRC PAYE", "HMRC PAYE CUMBERNAULD", "HMRC PAYE SHIPLEY", "PAYE TAX", "PAYE PAYMENT", # Employer NIC "EMPLOYERS NI", "EMPLOYERS NATIONAL INSURANCE", "CLASS 1 NIC", "EMPLOYER NIC", "EMPLOYERS NIC", "EMPLOYER NI CONTRIBUTION", # Auto-enrolment pension providers "NEST PENSION", "NEST PENSIONS", "NEST LTD", "NEST CONTRIB", "PEOPLES PENSION", "THE PEOPLES PENSION", "NOW PENSIONS", "NOW PENS", "SMART PENSION", "ROYAL LONDON PENSION", "ROYAL LONDON GROUP", "AVIVA PENSION", "AVIVA PENSIONS", "STANDARD LIFE PENSION", "STANDARD LIFE CONTRIB", "SCOTTISH WIDOWS PENSION", "SCOTTISH WIDOWS", "AEGON PENSION", "AEGON CONTRIB", "PRUDENTIAL PENSION", "FRIENDS LIFE PENSION", "PHOENIX PENSION", "CLERICAL MEDICAL PENSION", "LEGAL GENERAL PENSION", "L G PENSION", "ZURICH PENSION", # Payroll software / bureaux "SALARY PAYMENT", "WAGES PAYMENT", "PAYROLL PAYMENT", "BACS SALARY", "BRIGHTPAY", "SAGE PAYROLL", "MOOREPAY", "ADP PAYROLL", "CERIDIAN PAYROLL", "CINTRA PAYROLL", # Subcontractors / CIS "SUBCONTRACTOR PAYMENT", "CIS PAYMENT", "CIS DEDUCTION", "CONSTRUCTION INDUSTRY SCHEME", "CIS SUBCONTRACTOR", # Staffing agencies "REED STAFFING", "HAYS RECRUITMENT", "MANPOWER", "ADECCO", "RANDSTAD", "BLUE ARROW", "OFFICE ANGELS", "BROOK STREET", "ROBERT HALF", "MICHAEL PAGE", "GI GROUP", "KELLY SERVICES", "SEARCH CONSULTANCY", # Employee benefits "EMPLOYEE BENEFITS", "STAFF BENEFITS", "BUPA HEALTH", "VITALITY HEALTH", "AXA HEALTH", "HEALTH SHIELD", "MEDICASH", ], 0.95), # ── 2. Corporation tax ────────────────────────────────────────────────── # Balance-sheet item. Not a deductible P&L expense. # Must be before generic HMRC check. ("Corporation tax", [ "CORP TAX", "CORPORATION TAX", "HMRC CT", "CT PAYMENT", "CT INSTALMENT", "HMRC COTAX", "HMRC CO TAX", "CT61", "COTAX PAYMENT", ], 0.97), # ── 3. VAT payment ────────────────────────────────────────────────────── # Balance-sheet movement. Not a P&L cost. # Must be before generic HMRC check. ("VAT payment", [ "HMRC VAT", "VAT PAYMENT", "VAT RETURN PAYMENT", "HMRC VATPAY", "VAT QUARTER", "HMRC DD VAT", "VAT DEFERRAL", "VALUE ADDED TAX PAYMENT", ], 0.97), # ── 4. Self Assessment / income tax ───────────────────────────────────── ("Self assessment / income tax", [ "SELF ASSESSMENT", "SA PAYMENT", "HMRC SELF ASSESSMENT", "PERSONAL TAX PAYMENT", "SA302", "HMRC SA", "PAYMENT ON ACCOUNT", "INCOME TAX PAYMENT", "HMRC BRISTOL", ], 0.95), # ── 5. Dividends paid ──────────────────────────────────────────────────── # BALANCE-SHEET ITEM — appropriation of retained profit; NOT a P&L expense. # Non-deductible for CT (CTA 2009 s.1305 by exclusion from deductible expenses). # Must appear before transfer/drawings rule to route dividends to the correct # equity account (Retained Earnings), not Directors Loan Account. ("Dividends paid (balance sheet)", [ "DIVIDEND PAYMENT", "DIVIDEND PAID", "INTERIM DIVIDEND", "FINAL DIVIDEND PAYMENT", "SHAREHOLDER DIVIDEND", "DIRECTOR DIVIDEND", "DIVIDEND DISTRIBUTION", "DIVIDEND DECLARED", "MEMBER DIVIDEND", # LLP / co-op distributions ], 0.91), # ── 6. Loan repayments ────────────────────────────────────────────────── # BALANCE-SHEET ITEM — capital element reduces the loan liability. # Interest element IS deductible (CTA 2009 Part 5) but most bank feeds show # a single composite payment. Flag for accountant to apportion capital vs interest. # Must appear before generic Bank charges so "LOAN REPAYMENT BARCLAYS" is # caught here, not classified as a bank fee. ("Loan repayment (balance sheet)", [ "LOAN REPAYMENT", "LOAN INSTALMENT", "LOAN PAYMENT", "MORTGAGE REPAYMENT", "MORTGAGE PAYMENT", "MORTGAGE INSTALMENT", "FINANCE REPAYMENT", "HP REPAYMENT", "HIRE PURCHASE PAYMENT", "LEASE REPAYMENT", "FINANCE LEASE PAYMENT", "PCP PAYMENT", # personal contract purchase (company car) # Named UK business lenders "FUNDING CIRCLE REPAYMENT", "IWOCA REPAYMENT", "CAPITAL ON TAP REPAYMENT", "FLEXIMIZE REPAYMENT", "BBL REPAYMENT", "BOUNCE BACK REPAYMENT", "CBILS REPAYMENT", "RECOVERY LOAN REPAYMENT", "BRITISH BUSINESS BANK REPAYMENT", "BARCLAYS LOAN REPAYMENT", "LLOYDS LOAN REPAYMENT", "NATWEST LOAN REPAYMENT", "HSBC LOAN REPAYMENT", "STARLING LOAN REPAYMENT", "TIDE LOAN REPAYMENT", "ALDERMORE REPAYMENT", "SHAWBROOK REPAYMENT", "CLOSE BROTHERS REPAYMENT", "OAKNORTH REPAYMENT", ], 0.90), # ── 7. HMRC interest & penalties ──────────────────────────────────────── # NON-DEDUCTIBLE (CTA 2009 s.1303). Must be before generic HMRC catch-all # and before generic bank charges rule. ("HMRC interest & penalties", [ # Interest on overdue tax (CTA 2009 s.1303) — these are interest charges, not penalties "HMRC INTEREST", "HMRC INTEREST CHARGE", "HMRC LATE PAYMENT INTEREST", "HMRC SURCHARGE", "HMRC SURCHARGE NOTICE", "HMRC LATE PAYMENT SURCHARGE", # NOTE: "HMRC LATE FILING" and "HMRC PENALTY" are in the Fines rule (next) — do NOT add here ], 0.96), # ── 6. Fines & penalties (non-deductible) ─────────────────────────────── # NON-DEDUCTIBLE (CTA 2009 s.1298). Not a P&L expense — mark as expense # but non-deductible. ("Fines & penalties (non-deductible)", [ # HMRC statutory penalties (NOT interest — those are in the rule above) "HMRC PENALTY", "HMRC LATE FILING PENALTY", "HMRC LATE FILING", "HMRC FAILURE TO NOTIFY", "HMRC INACCURACY PENALTY", "HMRC CIVIL PENALTY", "SA PENALTY", "CT PENALTY", # Companies House late-filing penalties "COMPANIES HOUSE PENALTY", "COMPANIES HOUSE LATE FILING", "LATE FILING PENALTY", "CH PENALTY", # Regulatory fines "ICO FINE", "ICO PENALTY", "FCA FINE", "FCA PENALTY", "CMA FINE", "HSE FINE", "HSE PENALTY", "ENVIRONMENT AGENCY FINE", # Traffic / parking (on company vehicle account) "PENALTY CHARGE NOTICE", "PCN PAYMENT", "PENALTY NOTICE", "PARKING FINE", "PARKING PENALTY", "FIXED PENALTY NOTICE", "DVLA PENALTY", "DVLA FINE", # Court fines "COURT FINE", "COURT ORDER PAYMENT", "MAGISTRATES COURT FINE", # Generic "REGULATORY PENALTY", "REGULATORY FINE", "COUNCIL PENALTY", "HEALTH SAFETY FINE", "PRA FINE", ], 0.94), # ── 7. Bank & financial charges ───────────────────────────────────────── # Specific fee descriptions before generic CHARGE / FEE. ("Bank, credit card and other financial charges", [ # Bank-specific fees "BARCLAYS SERVICE FEE", "HSBC SERVICE CHARGE", "NATWEST MONTHLY FEE", "LLOYDS BANK FEE", "STARLING FEE", "MONZO FEE", "TIDE FEE", "REVOLUT FEE", "METRO BANK FEE", "HALIFAX BANK FEE", "FIRST DIRECT FEE", "NATIONWIDE FEE", "COOP BANK FEE", "TSB BANK FEE", "CLYDESDALE BANK FEE", "VIRGIN MONEY FEE", "SANTANDER FEE", # Card machine / payment processor fees "STRIPE FEE", "PAYPAL FEE", "SQUARE FEE", "SUMUP FEE", "IZETTLE FEE", "ZETTLE FEE", "WORLDPAY FEE", "DOJO FEE", "BARCLAYCARD FEE", "PAYMENTSENSE FEE", "NUVEI FEE", "EVO PAYMENTS FEE", "ELAVON FEE", "ADYEN FEE", "OPAYO FEE", # Generic bank charges "BANK CHARGE", "BANK FEE", "MONTHLY ACCOUNT FEE", "OVERDRAFT FEE", "OVERDRAFT CHARGE", "OVERDRAFT INTEREST", "UNPAID ITEM FEE", "RETURNED PAYMENT FEE", "RETURNED ITEM", "BACS RETURNED", "DIRECT DEBIT RETURNED", "CHAPS FEE", "SWIFT FEE", "SEPA FEE", "ANNUAL CARD FEE", "CREDIT CARD FEE", "CREDIT CARD INTEREST", "INTEREST ON CREDIT CARD", "INTERNATIONAL PAYMENT FEE", "FOREIGN EXCHANGE FEE", "FX CHARGE", "CURRENCY CONVERSION FEE", "LATE PAYMENT CHARGE", "ARRANGEMENT FEE", "COMMITMENT FEE", "FACILITY FEE", "MERCHANT SERVICE FEE", "TRANSACTION FEE", "INVOICE FINANCE FEE", "FACTORING FEE", ], 0.92), # ── 8. Insurance ───────────────────────────────────────────────────────── # Named UK business insurers before generic INSURANCE keyword. ("Insurance cost", [ "HISCOX", "SIMPLY BUSINESS", "MARKEL", "POLICYBEE", "AVIVA INSURANCE", "AXA INSURANCE", "DIRECT LINE BUSINESS", "ZURICH INSURANCE", "RSA INSURANCE", "ALLIANZ", "AON INSURANCE", "QBE INSURANCE", "CHUBB", "BUPA INSURANCE", "NFU MUTUAL", "LV INSURANCE", "LIVERPOOL VICTORIA", "AGEAS INSURANCE", "INTACT INSURANCE", "ADMIRAL BUSINESS", "HOWDEN INSURANCE", "TOWERGATE INSURANCE", "RIPE INSURANCE", "SUPERSCRIPT INSURANCE", "QDOS INSURANCE", "KINGSBRIDGE INSURANCE", # Coverage types (generic but unambiguous) "PUBLIC LIABILITY INSURANCE", "PROFESSIONAL INDEMNITY INSURANCE", "EMPLOYERS LIABILITY INSURANCE", "BUILDINGS INSURANCE", "CONTENTS INSURANCE", "CYBER INSURANCE", "FLEET INSURANCE", "VAN INSURANCE", "BUSINESS INSURANCE", "INSURANCE PREMIUM", "INSURANCE RENEWAL", "KEY PERSON INSURANCE", "KEY MAN INSURANCE", "DIRECTORS OFFICERS INSURANCE", "PRODUCT LIABILITY INSURANCE", "TRADE CREDIT INSURANCE", "GOODS IN TRANSIT INSURANCE", "INCOME PROTECTION INSURANCE", ], 0.93), # ── 9. Rent, rates & utilities ─────────────────────────────────────────── # Named UK suppliers before generic ELECTRICITY / GAS / WATER. ("Rent, rates & power", [ # Electricity/gas suppliers "OCTOPUS ENERGY", "OCTOPUS ELECTRIC", "BRITISH GAS BUSINESS", "BRITISH GAS", "EDF ENERGY", "EON NEXT", "E ON ENERGY", "EON ENERGY", "SCOTTISH POWER", "SCOTTISH POWER BUSINESS", "SSE BUSINESS", "SSE ENERGY", "ECOTRICITY", "GOOD ENERGY", "OVO ENERGY", "OVO BUSINESS", "UTILITA ENERGY", "UTILITA", "BULB ENERGY", "NPOWER BUSINESS", "SHELL ENERGY", "SHELL ENERGY BUSINESS", "SO ENERGY", "OUTFOX THE MARKET", # Water suppliers "ANGLIAN WATER", "THAMES WATER", "SEVERN TRENT WATER", "YORKSHIRE WATER", "UNITED UTILITIES", "SOUTHERN WATER", "NORTHUMBRIAN WATER", "AFFINITY WATER", "WELSH WATER", "SCOTTISH WATER", "NI WATER", "SEVERN TRENT", # Rates & rent "BUSINESS RATES", "COUNCIL TAX", "GROUND RENT", "SERVICE CHARGE MGMT", "PROPERTY MANAGEMENT FEE", "LETTING AGENT FEE", # Flexible / serviced offices "WEWORK", "WE WORK", "REGUS", "IWG", "SPACES OFFICES", "INSTANT OFFICES", # Storage "STORAGE UNIT RENTAL", "SELF STORAGE", # Generic (after specific) "RENT PAYMENT", "RENTAL PAYMENT", "LANDLORD PAYMENT", "ELECTRICITY BILL", "GAS BILL", "WATER BILL", "UTILITY PAYMENT", "WASTE COLLECTION", "REFUSE COLLECTION", ], 0.90), # ── 10. Advertising & marketing ────────────────────────────────────────── # GOOGLE ADS must appear BEFORE GOOGLE (which maps to Phone & Office). ("Advertising & Marketing", [ # Paid search / social ads "GOOGLE ADS", "GOOGLE ADWORDS", "FACEBOOK ADS", "FACEBOOK ADVERTISING", "META ADS", "META ADVERTISING", "INSTAGRAM ADS", "TIKTOK ADS", "TIKTOK FOR BUSINESS", "LINKEDIN ADS", "LINKEDIN MARKETING SOLUTIONS", "TWITTER ADS", "X ADVERTISING", "BING ADS", "MICROSOFT ADVERTISING", "AMAZON ADVERTISING", "PINTEREST ADS", "SNAPCHAT ADS", "SNAPCHAT ADVERTISING", "REDDIT ADS", "REDDIT ADVERTISING", "SPOTIFY ADS", "YOUTUBE ADS", "TABOOLA", "OUTBRAIN", # Email & marketing automation "MAILCHIMP", "KLAVIYO", "DOTDIGITAL", "CAMPAIGN MONITOR", "ACTIVECAMPAIGN", "CONSTANT CONTACT", "MAILGUN", "SENDGRID", "BREVO", "SENDINBLUE", # Social media management "HOOTSUITE", "BUFFER", "SPROUT SOCIAL", "LATER SOCIAL", # SEO tools "SEMRUSH", "AHREFS", "MOZ SUBSCRIPTION", "MAJESTIC SEO", # PR tools "MUCK RACK", "CISION", # Print / design "VISTAPRINT", "MOO COM", "INSTANTPRINT", "CANVA PRO", # Directory & trade listings "YELL COM", "CHECKATRADE", "RATED PEOPLE", "TRUSTATRADER", "BARK COM", "MYBUILDER", # Web presence (domain/hosting treated as marketing) "GODADDY", "NAMECHEAP", "SITEGROUND", "BLUEHOST", "WIX SUBSCRIPTION", "SQUARESPACE SUBSCRIPTION", "SHOPIFY SUBSCRIPTION", # Agencies "MARKETING AGENCY", "PR AGENCY", ], 0.90), # ── 11. Phone & office costs ───────────────────────────────────────────── # Telecom, software subscriptions, postage, stationery. # GOOGLE WORKSPACE must appear before GOOGLE in this list — although GOOGLE ADS # has already been captured in rule 10 above, this guards against plain "GOOGLE". ("Phone & Office costs", [ # Mobile & broadband "VODAFONE BUSINESS", "VODAFONE", "O2 BUSINESS", "O2", "EE BUSINESS", "EE MOBILE", "THREE BUSINESS", "THREE MOBILE", "GIFFGAFF", "BT BUSINESS", "BT BROADBAND", "SKY BUSINESS", "SKY BROADBAND", "VIRGIN MEDIA BUSINESS", "VIRGIN MEDIA", "PLUSNET", "TALK TALK", "NOW BROADBAND", # Microsoft (365 before bare MICROSOFT) "MICROSOFT 365", "MICROSOFT OFFICE", "MS 365", "OFFICE 365", "MICROSOFT AZURE", "MICROSOFT", # Google (Workspace before bare GOOGLE) "GOOGLE WORKSPACE", "GOOGLE ONE", "GOOGLE", # Apple "APPLE ICLOUD", "APPLE ONE", "APPLE BUSINESS", # Adobe "ADOBE CREATIVE CLOUD", "ADOBE ACROBAT", "ADOBE", # Project & collaboration tools "NOTION", "ATLASSIAN", "JIRA", "CONFLUENCE", "TRELLO", "ASANA", "MONDAY COM", "BASECAMP", "MIRO", "LOOM", "FIGMA", # Customer support / helpdesk "FRESHDESK", "ZENDESK", "INTERCOM", # Scheduling "CALENDLY", # Stock images / media "SHUTTERSTOCK", "GETTY IMAGES", "ADOBE STOCK", "ISTOCK", # Code hosting "GITHUB", "GITLAB", "BITBUCKET", # File storage & comms "DROPBOX BUSINESS", "DROPBOX", "SLACK", "ZOOM", "WEBEX", "GOTOMEETING", "MICROSOFT TEAMS", # Cloud infrastructure "AWS", "AMAZON WEB SERVICES", "GOOGLE CLOUD", "CLOUDFLARE", "DIGITAL OCEAN", "LINODE", "VULTR", # AI tools "OPENAI", "ANTHROPIC", # CRM / sales "HUBSPOT", "SALESFORCE", "PIPEDRIVE", "ZOHO", # Accounting software (if subscription not already in lookup) "XERO SUBSCRIPTION", "QUICKBOOKS SUBSCRIPTION", "XERO", "QUICKBOOKS", "SAGE SOFTWARE", "SAGE BUSINESS", "FREEAGENT", "CLEARBOOKS", "DEXT", "AUTOENTRY", "HUBDOC", "RECEIPT BANK", # Billing / subscription management "CHARGEBEE", "RECURLY", # Forms & e-signature "TYPEFORM", "SURVEYMONKEY", "DOCUSIGN", "PANDADOC", "HELLOSIGN", # Security "LASTPASS", "1PASSWORD", "NORDVPN", # Couriers & postage "ROYAL MAIL", "PARCELFORCE", "DPD ONLINE", "EVRI", "HERMES DELIVERY", "DHL", "FEDEX", "UPS", "YODEL", "COLLECT PLUS", "WHISTL", "AMAZON LOGISTICS", # Stationery & office supplies "RYMAN STATIONERY", "VIKING OFFICE", "STAPLES", "WHSMITH", "AMAZON BUSINESS", "PRINTER INK", "PRINTER CARTRIDGE", "TONER CARTRIDGE", "OFFICE SUPPLIES", "STATIONERY SUPPLIES", ], 0.88), # ── 12. Car, van & travel ──────────────────────────────────────────────── ("Car, van and travel expenses", [ # Fuel (named stations before generic FUEL) "BP OIL", "BP FUEL", "BP GARAGE", "BP PETROL", "SHELL PETROL", "SHELL FUEL", "TEXACO", "GULF FUEL", "ESSO", "TOTAL ENERGIES FUEL", "MORRISONS FUEL", "MORRISONS PETROL", "TESCO FUEL", "SAINSBURY FUEL", "ASDA FUEL", "ASDA PETROL", "JET FUEL", "GULF PETROL", # Vehicle servicing "MOT TEST", "CAR SERVICE", "VEHICLE SERVICE", "VEHICLE REPAIR", "HALFORDS", "KWIKFIT", "ATS EUROMASTER", "NATIONAL TYRES", "FORMULA ONE AUTOCENTRES", "RAPID FIT", # Breakdown / rescue "RAC RESCUE", "AA RESCUE", "AA BREAKDOWN", # Parking, congestion & tolls "NCP PARKING", "Q PARK", "JUSTPARK", "RINGGO", "CONGESTION CHARGE", "ULEZ CHARGE", "ROAD TOLL", "DART CHARGE", "M6 TOLL", "DARTFORD CROSSING", "CLEAN AIR ZONE", # Rideshare, taxi & micro-mobility "UBER", "BOLT RIDE", "ADDISON LEE", "TAXI", "MINICABIT", "GETT TAXI", "FREE NOW", "LIME BIKE", "SANTANDER CYCLES", "BERYL BIKE", # Rail "TRAINLINE", "TRANSPORT FOR LONDON", "TFL", "NATIONAL RAIL", "AVANTI WEST COAST", "GWR TRAINS", "LNER", "CROSSCOUNTRY", "THAMESLINK", "SOUTHEASTERN", "TRANSPENNINE", "CALEDONIAN SLEEPER", "SCOTRAIL", "NORTHERN RAIL", "WEST MIDLANDS TRAINS", "CHILTERN RAILWAYS", "MERSEYRAIL", "GATWICK EXPRESS", "HEATHROW EXPRESS", "STANSTED EXPRESS", # Coach & bus "EUROSTAR", "MEGABUS", "NATIONAL EXPRESS", "FLIXBUS", # Flights "EASYJET", "RYANAIR", "BRITISH AIRWAYS", "VIRGIN ATLANTIC", "JET2", "WIZZ AIR", "TUI AIRWAYS", "NORWEGIAN AIR", "FLYBE", "AER LINGUS", "KLM", "AIR FRANCE", "LUFTHANSA", "SWISS AIR", # Hotels "PREMIER INN", "TRAVELODGE", "HOLIDAY INN", "MARRIOTT", "HILTON", "IBIS HOTEL", "NOVOTEL", "RADISSON", "IHG HOTEL", "BEST WESTERN", "DOUBLETREE", "CROWNE PLAZA", # Booking platforms "BOOKING COM", "HOTELS COM", # Car hire / sharing "ENTERPRISE RENT A CAR", "HERTZ", "AVIS", "EUROPCAR", "SIXT", "ENTERPRISE CAR CLUB", "ZIPCAR", "CO WHEELS", # Vehicle related "VEHICLE LEASE", "CAR LEASE", "VAN HIRE", "VAN LEASE", "DVLA VEHICLE TAX", "ROAD TAX", ], 0.88), # ── 13. Accountancy & legal ────────────────────────────────────────────── ("Accountancy & legal", [ # Regulators & registrars "COMPANIES HOUSE", "COMPANIES HSE", "COMPANIES HOUSE WEBFILING", "INTELLECTUAL PROPERTY OFFICE", "IPO", "TRADEMARK REGISTRY", "UKIPO", "PATENT FILING", "TRADE MARK REGISTRY", # Data protection "ICO FEE", "ICO RENEWAL", "DATA PROTECTION FEE", "INFORMATION COMMISSIONER", # Financial services regulation "FCA REGISTRATION", "FCA FEE", # Professional bodies "ICAEW", "ACCA SUBSCRIPTION", "CIMA SUBSCRIPTION", "LAW SOCIETY", # Service descriptions "CHARTERED ACCOUNTANT", "ACCOUNTANCY FIRM", "ACCOUNTANT FEES", "BOOKKEEPER", "BOOKKEEPING SERVICE", "PAYROLL BUREAU", "PAYROLL SERVICE", "TAX CONSULTANT", "TAX RETURN SERVICE", "SOLICITOR", "LAW FIRM", "LEGAL FEES", "LEGAL ADVICE", "NOTARY FEES", "NOTARY PUBLIC", # Debt recovery "DEBT COLLECTION", "DEBT RECOVERY", "CREDIT CONTROL SERVICE", ], 0.90), # ── 14. Professional subscriptions ────────────────────────────────────── # DEDUCTIBLE if wholly/exclusively for the trade (HMRC EIM32900). ("Professional subscriptions", [ "CIPD", "CHARTERED INSTITUTE PERSONNEL", "RICS", "ROYAL INSTITUTION CHARTERED SURVEYORS", "RIBA", "ROYAL INSTITUTE BRITISH ARCHITECTS", "BCS", "BRITISH COMPUTER SOCIETY", "GMC SUBSCRIPTION", "GENERAL MEDICAL COUNCIL", "NMC SUBSCRIPTION", "NURSING MIDWIFERY", "GDC SUBSCRIPTION", "GENERAL DENTAL COUNCIL", "BMA SUBSCRIPTION", "CILEX", "BAR COUNCIL SUBSCRIPTION", "CISI SUBSCRIPTION", "CFA SUBSCRIPTION", "CIM SUBSCRIPTION", "CHARTERED INSTITUTE MARKETING", "CIOB", "CHARTERED INSTITUTE BUILDING", "IET SUBSCRIPTION", "ICE SUBSCRIPTION", "CILT", "CHARTERED INSTITUTE LOGISTICS", "CIPS", "CHARTERED INSTITUTE PROCUREMENT", "CIH", "CHARTERED INSTITUTE HOUSING", "INSTITUTE OF DIRECTORS", "IOD MEMBERSHIP", "CHAMBER OF COMMERCE", "CHAMBERS OF COMMERCE", "FSB MEMBERSHIP", "FEDERATION SMALL BUSINESSES", "TRADE ASSOCIATION FEE", "TRADE BODY SUBSCRIPTION", "GUILD MEMBERSHIP", ], 0.88), # ── 15. Training & development ─────────────────────────────────────────── ("Training & development", [ "UDEMY", "COURSERA", "LINKEDIN LEARNING", "SKILLSHARE", "PLURALSIGHT", "CODECADEMY", "TRAINING COURSE", "CPD COURSE", "CPD TRAINING", "CPD WEBINAR", "PROFESSIONAL DEVELOPMENT", "AWS CERTIFICATION", "GOOGLE CERTIFICATION", "MICROSOFT CERTIFICATION", "REED COURSES", "PITMAN TRAINING", "EVENTBRITE", "SEMINAR FEE", "CONFERENCE REGISTRATION", "EXAM FEE", "QUALIFICATION FEE", "CERTIFICATION FEE", ], 0.87), # ── 16. Stock, materials & cost of sales ───────────────────────────────── ("Stock & materials / Cost of sales", [ # Trade counters "SCREWFIX", "TOOLSTATION", "TRAVIS PERKINS", "JEWSON", "WICKES", "BUILDBASE", "SELCO BUILDERS", "SELCO", "PLUMBCENTER", "WOLSELEY", "REXEL", "CEF", "CITY ELECTRICAL FACTORS", "RS COMPONENTS", # Decorating / paint "DULUX DECORATOR", "CROWN PAINTS", "JOHNSTONES PAINT", "GRAFTON GROUP", # B2B / wholesale "WHOLESALE SUPPLIER", "TRADE ACCOUNT", "RAW MATERIALS", "BOOKER CASH", "MAKRO", "COSTCO BUSINESS", "BIDFOOD", "BRAKES", # Import platforms "ALIBABA", "ALIEXPRESS", "EBAY BUSINESS", ], 0.82), # ── 17. Equipment (flag for capital expenditure review) ────────────────── # Items above £1,000 may be capital expenditure (not P&L expense). # Accountant should decide: expense via Annual Investment Allowance or capitalise. ("Equipment (review for capital expenditure)", [ "APPLE STORE", "APPLE COM", "DELL TECHNOLOGIES", "HP INC", "LENOVO", "SAMSUNG DIRECT", "ACER DIRECT", "ASUS STORE", "MICROSOFT SURFACE", "CANON DIRECT", "NIKON DIRECT", "SONY DIRECT", "EPSON DIRECT", "BROTHER DIRECT", "LOGITECH", "CISCO", "NETGEAR", "TP LINK", "SCAN COMPUTERS", "EBUYER", "CURRYS BUSINESS", "JOHN LEWIS TECH", "IT EQUIPMENT", "TECH SUPPLIER", ], 0.75), # ── 18. Repairs & maintenance ──────────────────────────────────────────── ("Repairs & maintenance", [ "MAINTENANCE CONTRACT", "BUILDING REPAIR", "PROPERTY REPAIR", "BOILER SERVICE", "CLEANING CONTRACT", "CLEANING SERVICES LTD", "FACILITIES MANAGEMENT", "PEST CONTROL", "RENTOKIL", "DYNO ROD", "DYNOROD", "GROUNDS MAINTENANCE", "VEOLIA", "BIFFA", "WINDOW CLEANING SERVICE", "LIFT MAINTENANCE", "FIRE SAFETY SERVICE", "FIRE ALARM TEST", "PAT TESTING", "AIR CONDITIONING SERVICE", "HVAC MAINTENANCE", "PLUMBING REPAIR", "ELECTRICAL REPAIR", ], 0.82), # ── 19. Client entertaining (non-deductible) ───────────────────────────── # NOT deductible (CTA 2009 s.1298). Input VAT BLOCKED (s.24 VATA 1994). # Must appear BEFORE subsistence — alcohol deliveries & event tickets are not # subsistence even if they occur on a business trip. ("Client entertaining (non-deductible)", [ # Alcohol / off-licences "MAJESTIC WINE", "BERRY BROS", "LAITHWAITES", "VIRGIN WINES", "NAKED WINES", # Tickets & events "TICKETMASTER", "STUBHUB", "SEE TICKETS", "SKIDDLE", "EVENTIM", "DICE FM", "THEATRE TICKETS", # Golf (archetypal client entertainment) "GOLF CLUB", "GOLF COURSE", "GOLF MEMBERSHIP", "CORPORATE GOLF", "GOLF CORPORATE", # both word orders seen in bank feeds "GOLF DAY", "GOLF SOCIETY", "GOLF EVENT", # Sports / corporate hospitality "CORPORATE HOSPITALITY", "CORPORATE ENTERTAINMENT", "WEMBLEY STADIUM", "TWICKENHAM STADIUM", "LORDS CRICKET", "THE OVAL CRICKET", # Description-based signals — CRITICAL: these catch "CLIENT LUNCH" style references # that appear in the payment description/merchant field even without venue name matching. "CLIENT LUNCH", "CLIENT DINNER", "CLIENT MEAL", "CLIENT BREAKFAST", "CLIENT DRINKS", "CLIENT ENTERTAINMENT", "CUSTOMER LUNCH", "CUSTOMER DINNER", "PROSPECT LUNCH", "PROSPECT DINNER", "BUSINESS ENTERTAINING", "WINING AND DINING", "CLIENT ENTERTAINING", "CUSTOMER ENTERTAINING", # Private dining / hospitality keywords "PRIVATE DINING", "HOSPITALITY PACKAGE", ], 0.72), # ── 20. Subsistence ────────────────────────────────────────────────────── # Meals/drinks for sole trader or employees while travelling on business. # NOTE: Client entertaining is NOT deductible for Corporation Tax. # Client entertaining is captured in rule 19 above. ("Subsistence", [ # Coffee shops "COSTA COFFEE", "STARBUCKS", "CAFE NERO", "PRET", "PRET A MANGER", "CAFFE NERO", "NERO", # Fast food "GREGGS", "SUBWAY", "MCDONALD", "BURGER KING", "KFC", "EAT NATURAL", "ITSU", "LEON", "WASABI", "FIVE GUYS", "NANDOS", "WAGAMAMA", "WETHERSPOON", "J D WETHERSPOON", # Supermarkets / food on the go "BOOTS FOOD", # Delivery (sole-trader subsistence while working away) "DELIVEROO", "UBER EATS", "JUST EAT", # Supermarkets (food bought while on a business trip) "SAINSBURY", "TESCO EXPRESS", "TESCO METRO", "WAITROSE", "MARKS SPENCER FOOD", ], 0.72), # ── 21. Catch-all subscription (lower confidence) ──────────────────────── ("Phone & Office costs", [ "SUBSCRIPTION", "MONTHLY PLAN", "ANNUAL PLAN", "SOFTWARE LICENCE", "SAAS LICENCE", "MONTHLY SUBSCRIPTION", ], 0.60), # ── 22. Generic rent/utilities fallback ────────────────────────────────── ("Rent, rates & power", [ "RENT TO", "LANDLORD", "LETTINGS AGENT", "ELECTRICITY", "GAS SUPPLY", "WATER RATES", "UTILITIES", ], 0.70), ] # Categories that are balance-sheet movements (not P&L items). # Used to set balance_sheet_item=True on expense rows. BALANCE_SHEET_EXPENSE_CATS: frozenset[str] = frozenset([ "Corporation tax", "VAT payment", "Self assessment / income tax", "Transfer / owner drawings (review)", # balance-sheet: equity / directors loan "Transfer / payment (review needed)", # unknown destination — keep off P&L until confirmed "Loan repayment (balance sheet)", # capital repayment reduces loan liability "Dividends paid (balance sheet)", # appropriation of retained profit, not P&L ]) # --------------------------------------------------------------------------- # VAT TREATMENT HINTS — expense categories # # Tells an accountant how to treat input VAT on each category. # Values: standard_rated | zero_rated | exempt | outside_scope | # blocked_input | mixed | unknown # # Key UK rules: # • Rail/bus/TFL → zero-rated supply (no input VAT to reclaim) # • Insurance → exempt (no input VAT) # • Bank charges → exempt # • Wages/PAYE → outside scope # • CT/VAT/SA → outside scope # • Car fuel → standard BUT 50% blocked for non-commercial mixed-use # • Entertainment → blocked (s.24 VATA 1994) # • Rent → exempt unless landlord has opted to tax # --------------------------------------------------------------------------- _VAT_TREATMENT_EXPENSE: dict[str, str] = { "Transfer / owner drawings (review)": "outside_scope", "Transfer / payment (review needed)": "unknown", "Staff costs": "outside_scope", "Corporation tax": "outside_scope", "VAT payment": "outside_scope", "Self assessment / income tax": "outside_scope", "HMRC interest & penalties": "outside_scope", "Fines & penalties (non-deductible)": "outside_scope", "Bank, credit card and other financial charges": "exempt", "Insurance cost": "exempt", "Rent, rates & power": "exempt", # flag: could be standard if landlord opted-to-tax "Advertising & Marketing": "standard_rated", "Phone & Office costs": "standard_rated", "Car, van and travel expenses": "mixed", # rail=zero, fuel=standard (50% block on cars) "Accountancy & legal": "standard_rated", "Professional subscriptions": "standard_rated", "Training & development": "standard_rated", "Stock & materials / Cost of sales": "standard_rated", "Equipment (review for capital expenditure)": "standard_rated", "Repairs & maintenance": "standard_rated", "Client entertaining (non-deductible)": "blocked_input", "Subsistence": "mixed", # restaurant=standard, cold food=zero "Dividends paid (balance sheet)": "outside_scope", # equity distribution, not a supply "Loan repayment (balance sheet)": "exempt", # financial service: Sch.9 Grp.5 VATA 1994 "Other business expenses": "unknown", } # VAT treatment for income categories _VAT_TREATMENT_INCOME: dict[str, str] = { "rd_tax_credit": "outside_scope", "vat_repayment": "outside_scope", "corporation_tax_repayment": "outside_scope", "loan_proceeds": "outside_scope", "capital_introduced": "outside_scope", "internal_transfers": "outside_scope", "card_processor_payouts": "standard_rated", # assumes underlying sales are standard "marketplace_sales_payouts": "standard_rated", "refunds_received": "standard_rated", # mirrors original expense "interest_income": "exempt", "grants_and_support": "outside_scope", # most UKRI/Innovate UK grants are outside scope "tax_repayments": "outside_scope", "rental_income": "exempt", # unless opted to tax "dividends_received": "outside_scope", "customer_receipts_sales": "standard_rated", # assume standard — needs verification "cash_deposits": "unknown", "other_income_unclear": "unknown", } # --------------------------------------------------------------------------- # DEDUCTIBILITY — for CT600 / SA103 # # "yes" → allowable business expense; reduces taxable profit # "no" → not allowable (CT/VAT themselves, drawings, entertainment, fines) # "review" → may or may not be allowable; accountant must confirm # (capital equipment via AIA, mixed-use cars, rent with personal element) # --------------------------------------------------------------------------- _IS_DEDUCTIBLE_EXPENSE: dict[str, str] = { "Transfer / owner drawings (review)": "no", # equity movement, not an expense "Transfer / payment (review needed)": "review", # unknown — could be anything "Staff costs": "yes", "Corporation tax": "no", # the charge itself is not deductible "VAT payment": "no", # balance-sheet movement "Self assessment / income tax": "no", # personal tax, not deductible "HMRC interest & penalties": "no", # CTA 2009 s.1303 — specifically disallowed "Fines & penalties (non-deductible)": "no", # CTA 2009 s.1298 — penalties are never deductible "Bank, credit card and other financial charges": "yes", "Insurance cost": "yes", "Rent, rates & power": "yes", "Advertising & Marketing": "yes", "Phone & Office costs": "yes", "Car, van and travel expenses": "yes", # subject to mixed-use adjustment "Accountancy & legal": "yes", "Professional subscriptions": "yes", # HMRC EIM32900 — if wholly/exclusively for trade "Training & development": "yes", "Stock & materials / Cost of sales": "yes", "Equipment (review for capital expenditure)": "review", # may be capital → AIA or WDA, not revenue "Repairs & maintenance": "yes", "Client entertaining (non-deductible)": "no", # CTA 2009 s.1298 — client entertaining blocked "Subsistence": "yes", # business meals only; entertainment is blocked "Dividends paid (balance sheet)": "no", # appropriation of profit, not a deductible expense "Loan repayment (balance sheet)": "review", # capital element=no; interest element=yes (CTA 2009 Pt 5); apportion "Other business expenses": "review", # unknown — needs confirmation } # Large amount threshold above which equipment spend may be capital expenditure. # HMRC does not set a de minimis in statute, but in practice most accountants # treat single items > £500 as potentially capital, and > £1,000 as likely capital. _CAPEX_REVIEW_AMOUNT: float = 1_000.0 # Above this, an unusual debit warrants an urgent review flag regardless of category. _LARGE_AMOUNT_URGENT: float = 10_000.0 _LARGE_AMOUNT_NORMAL: float = 5_000.0 # --------------------------------------------------------------------------- # INCOME RULES # # Each entry: (category, issue_reason, [keywords...], confidence, is_balance_sheet) # is_balance_sheet=True → exclude from turnover / P&L; it is a balance-sheet move. # # Rules tested IN ORDER. First matching keyword wins. # Put most specific rules first (R&D credit before generic tax repayment, etc.) # --------------------------------------------------------------------------- IncomeRule = Tuple[str, str, List[str], float, bool] INCOME_RULES: list[IncomeRule] = [ # ── 1. R&D Tax Credit ───────────────────────────────────────────────────── # Before generic HMRC repayment check. # Can be treated as above-the-line credit or below-the-line — confirm with accountant. ("rd_tax_credit", "R&D tax credit — treatment depends on scheme (SME / RDEC); confirm with accountant", ["HMRC R D CREDIT", "R D TAX CREDIT", "RDTC", "RESEARCH DEVELOPMENT CREDIT", "RDEC PAYMENT"], 0.93, False), # ── 2. VAT repayment ───────────────────────────────────────────────────── # Balance-sheet movement — reduce VAT liability, not income. ("vat_repayment", "VAT refund from HMRC — balance-sheet movement; do NOT include in turnover", ["HMRC VAT REPAY", "HMRC VAT REFUND", "VAT REPAYMENT", "VAT REFUND HMRC"], 0.97, True), # ── 3. Corporation tax repayment ───────────────────────────────────────── ("corporation_tax_repayment", "CT repayment from HMRC — balance-sheet movement; not P&L income", ["HMRC CT REPAY", "CORP TAX REPAYMENT", "CORPORATION TAX REFUND", "CT REFUND"], 0.97, True), # ── 4. Bounce Back Loan / CBILS / Recovery Loan ────────────────────────── # Still appearing in bank feeds. Highest confidence — balance sheet liability. ("loan_proceeds", "Bounce Back Loan / CBILS / Recovery Loan — balance-sheet liability; NOT income", ["BOUNCE BACK LOAN", "BBL PAYMENT", "CBILS", "RECOVERY LOAN SCHEME", "BRITISH BUSINESS BANK"], 0.98, True), # ── 5. Card processor / acquirer payouts ───────────────────────────────── # Core trading income for most B2C SMEs. ("card_processor_payouts", "", ["STRIPE PAYOUT", "STRIPE TRANSFER", "STRIPE PAYMENT", "SHOPIFY PAYOUT", "SHOPIFY PAYMENTS", "PAYPAL TRANSFER", "PAYPAL PAYOUT", "SUMUP PAYOUT", "SQUARE PAYOUT", "SQUARE TRANSFER", "IZETTLE PAYOUT", "ZETTLE PAYOUT", "WORLDPAY SETTLEMENT", "WORLDPAY PAYOUT", "DOJO SETTLEMENT", "DOJO PAYOUT", "BARCLAYCARD SETTLEMENT", "CARDNET SETTLEMENT", "ELAVON SETTLEMENT", "ADYEN PAYOUT", "KLARNA SETTLEMENT", "KLARNA PAYOUT", "OPAYO PAYOUT", "PAYMENTSENSE PAYOUT", "NUVEI PAYOUT", "EVO PAYMENTS", "PAYPOINT SETTLEMENT", "CARDNET PAYOUT", "TAKEPAYMENTS PAYOUT", "HANDEPAY PAYOUT", "VERIFONE SETTLEMENT", "GOCARDLESS PAYOUT", "GOCARDLESS PAYMENT", # direct debit collections "CHECKOUT COM PAYOUT", "BRAINTREE PAYOUT", "GLOBAL PAYMENTS SETTLEMENT", "FIRST DATA SETTLEMENT", "TOTAL PROCESSING PAYOUT", "RYFT PAYOUT", ], 0.94, False), # ── 6. Marketplace sales payouts ───────────────────────────────────────── # Distinct from card processors — these are ecommerce platform settlements. ("marketplace_sales_payouts", "", ["AMAZON PAYMENTS", "AMAZON SELLER CENTRAL", "AMAZON DISBURSEMENT", "EBAY PAYOUT", "EBAY MANAGED PAYMENTS", "ETSY PAYOUT", "ETSY PAYMENT", "NOTONTHEHIGHSTREET", "WOOCOMMERCE PAYOUT", "WOOCOMMERCE", "DEPOP PAYOUT", "VINTED PAYOUT", "FOLKSY PAYOUT", "ONBUY PAYOUT", "FRUUGO PAYOUT", "WISH PAYOUT", "ZALANDO PAYOUT", "ASOS MARKETPLACE", "GUMTREE PROCEEDS", ], 0.93, False), # ── 7. Supplier refunds & chargebacks won ──────────────────────────────── # Credit these against the original expense category in the accounts. ("refunds_received", "Supplier refund — credit against original expense category, not gross income", ["REFUND FROM", "SUPPLIER REFUND", "VENDOR REFUND", "CREDIT NOTE RECEIVED", "CHARGEBACK WON", "DISPUTE WON", "REVERSAL", "CREDIT NOTE", "OVERPAYMENT REFUND", "INSURANCE CLAIM SETTLEMENT", "WARRANTY CLAIM", ], 0.86, False), # ── 8. Interest income ──────────────────────────────────────────────────── ("interest_income", "", ["INTEREST CREDITED", "GROSS INTEREST", "NET INTEREST", "SAVINGS INTEREST", "BANK INTEREST", "INTEREST EARNED", "INTEREST PAYMENT FROM", ], 0.95, False), # ── 9a. Government COVID support schemes ───────────────────────────────── # SEISS and CJRS (furlough) are taxable trading receipts — post to income. ("grants_and_support", "Government COVID / business support payment — taxable trading receipt; confirm exact scheme with accountant", ["SEISS GRANT", "SEISS PAYMENT", "SELF EMPLOYMENT INCOME SUPPORT", "CJRS CLAIM", "CJRS PAYMENT", "CJRS GRANT", # all observed BACS formats "FURLOUGH GRANT", "FURLOUGH PAYMENT", "FURLOUGH SCHEME", "JOB RETENTION SCHEME", "CORONAVIRUS JOB RETENTION", "CORONAVIRUS SUPPORT", "COVID GRANT", "COVID SUPPORT", "HMRC SEISS", "HMRC CJRS", "RESTART GRANT", "RECOVERY GRANT", "KICKSTART SCHEME", ], 0.90, False), # ── 9. Grants & public support ──────────────────────────────────────────── ("grants_and_support", "Grant income — confirm tax treatment; some grants are taxable trading receipts", ["INNOVATE UK", "UKRI GRANT", "COUNCIL GRANT", "LOCAL AUTHORITY GRANT", "ENTERPRISE GRANT", "GROWTH GRANT", "ARTS COUNCIL GRANT", "NATIONAL LOTTERY GRANT", "LOCAL ENTERPRISE PARTNERSHIP", "SMART GRANT", "ENTERPRISE PARTNERSHIP GRANT", "GROWTH HUB GRANT", "ARTS COUNCIL ENGLAND", "LOTTERY HERITAGE FUND", "LEADER GRANT", "LOCAL GROWTH FUND", ], 0.88, False), # ── 10. General HMRC repayments (after specific ones above) ─────────────── ("tax_repayments", "HMRC repayment — confirm exact type (VAT / CT / SA) with accountant", ["HMRC REPAYMENT", "HMRC REFUND", "TAX REPAYMENT", "TAX REFUND", "SELF ASSESSMENT REPAYMENT", ], 0.82, False), # ── 11. Finance / bank loans ───────────────────────────────────────────── ("loan_proceeds", "Loan proceeds — balance-sheet liability; not trading income", ["LOAN ADVANCE", "LOAN DRAWDOWN", "FINANCE ADVANCE", "FINANCE FACILITY", "CAPITAL ON TAP", "IWOCA", "FUNDING CIRCLE", "FLEXIMIZE", "TIDE LENDING", "STARLING LOAN", "REVOLUT CREDIT FACILITY", "CASHFLOW ADVANCE", "BANK LOAN DRAWDOWN", ], 0.90, True), # ── 12. Director's loan / capital introduced ────────────────────────────── ("capital_introduced", "Owner/director equity or loan — balance-sheet movement; not trading income", ["DIRECTOR LOAN", "DIRECTORS LOAN", "CAPITAL INTRODUCED", "CAPITAL INJECTION", "SHAREHOLDER LOAN", "OWNER FUNDS TRANSFER", "PERSONAL FUNDS", ], 0.90, True), # ── 13. Rental income ───────────────────────────────────────────────────── ("rental_income", "Property rental income — may need separate tax schedule; confirm with accountant", ["RENTAL INCOME", "RENT RECEIVED", "TENANCY PAYMENT", "LETTING INCOME", "PROPERTY INCOME", ], 0.88, False), # ── 14. Dividends received ──────────────────────────────────────────────── ("dividends_received", "Dividend income — non-trading; confirm tax treatment with accountant", ["DIVIDEND RECEIVED", "DIVIDEND PAYMENT", "INTERIM DIVIDEND", "FINAL DIVIDEND", ], 0.92, False), # ── 15. Internal transfers ──────────────────────────────────────────────── # CRITICAL: exclude from P&L. Use specific phrases — "TRANSFER" alone is too # broad and would catch legitimate customer payment references. ("internal_transfers", "Own-account transfer — exclude from P&L; not income", ["INTERNAL TRANSFER", "OWN ACCOUNT TRANSFER", "FROM SAVINGS ACCOUNT", "TO CURRENT ACCOUNT", "TRANSFER BETWEEN ACCOUNTS", "SWEEP TRANSFER", ], 0.92, True), # ── 16. Customer receipts — trading income ──────────────────────────────── # BACS/CHAPS/FPS from customers. Broad but only reached after specific rules. ("customer_receipts_sales", "", ["PAYMENT RECEIVED", "BACS CREDIT", "BACS RECEIPT", "FASTER PAYMENT FROM", "FASTER PAYMENT IN", "BANK GIRO CREDIT", "CHAPS RECEIPT", "CHAPS CREDIT", "WIRE TRANSFER IN", "INVOICE PAYMENT", "INV PMT", "SALES RECEIPT", "CUSTOMER PAYMENT", "CLIENT PAYMENT", "CLIENT RECEIPT", "REMITTANCE", "REMITTANCE ADVICE", "BANK TRANSFER FROM", ], 0.78, False), # ── 17. Cash deposits ───────────────────────────────────────────────────── ("cash_deposits", "Cash deposit — verify source: sales proceeds, personal funds, or capital?", ["CASH DEPOSIT", "CASH PAID IN", "BRANCH DEPOSIT", "COUNTER CREDIT", "NIGHT SAFE DEPOSIT", ], 0.60, False), ] # Income categories that are balance-sheet movements. # Consistent with the is_balance_sheet flag in INCOME_RULES. BALANCE_SHEET_INCOME_CATS: frozenset[str] = frozenset([ "vat_repayment", "corporation_tax_repayment", "loan_proceeds", "capital_introduced", "internal_transfers", ]) # --------------------------------------------------------------------------- # Safety / parsing helpers # --------------------------------------------------------------------------- def validate_table_name(name: str) -> None: if not TABLE_NAME_RE.match(name): raise ValueError(f"Unsafe table name: {name!r}. Use: table or db.table") def parse_db_table(full: str) -> tuple[Optional[str], str]: if "." in full: db, table = full.split(".", 1) return db, table return None, full def split_by_timestamp_env( df: pd.DataFrame, ) -> tuple[pd.DataFrame, pd.DataFrame, str, int]: """ Returns (df_recent, df_old, ts_col, min_year). Reads TIMESTAMP_COLUMN and MIN_CLASSIFICATION_YEAR from .env. """ load_dotenv() ts_col = os.getenv("TIMESTAMP_COLUMN") if not ts_col: raise ValueError( "Missing .env var TIMESTAMP_COLUMN (example: TIMESTAMP_COLUMN=timestamp)" ) min_year_str = os.getenv("MIN_CLASSIFICATION_YEAR", "2025") try: min_year = int(min_year_str) except ValueError as e: raise ValueError( f"MIN_CLASSIFICATION_YEAR must be an integer, got: {min_year_str!r}" ) from e if ts_col not in df.columns: raise ValueError( f"Timestamp column '{ts_col}' not found. " f"Available columns: {list(df.columns)}" ) temp = df.copy() temp[ts_col] = pd.to_datetime(temp[ts_col], errors="coerce") year = temp[ts_col].dt.year df_recent = temp[year >= min_year].copy() df_old = temp[(year.isna()) | (year < min_year)].copy() return df_recent, df_old, ts_col, min_year # --------------------------------------------------------------------------- # Text helpers # --------------------------------------------------------------------------- def normalize_text(s: str) -> str: """Uppercase, strip UK noise and company suffixes, collapse whitespace.""" if not s: return "" s = str(s).upper().strip() # Remove country/currency noise s = re.sub(r"\b(GBP|GREAT\sBRITAIN|UNITED\sKINGDOM|UK)\b", " ", s) # Remove company-type suffixes that add noise without aiding classification. # These are stripped BEFORE special-char removal so word boundaries work. s = re.sub(r"\b(LIMITED|LTD|PLC|INC|CORP|LLC|CIC|CIO|LLP|LP)\b", " ", s) # Strip common transaction-reference noise (e.g. "REF 4829103", "DDR 123456") s = re.sub(r"\bREF\s*\d+\b", " ", s) s = re.sub(r"\b(DDR|STO|FP)\s+\d+\b", " ", s) # Keep only alphanumeric and spaces for clean matching s = re.sub(r"[^A-Z0-9\s]", " ", s) return re.sub(r"\s+", " ", s).strip() def build_classification_text(description: str, merchant: str) -> str: """Concatenate description + merchant for classification.""" desc = "" if pd.isna(description) else str(description).strip() merch = "" if pd.isna(merchant) else str(merchant).strip() return f"{desc} {merch}".strip() # --------------------------------------------------------------------------- # UK location extraction # --------------------------------------------------------------------------- # Ordered from longest/most-specific to shortest to avoid prefix collisions # (e.g. "STRATFORD-UPON-AVON" before "STRATFORD", "SOUTHAMPTON" before "SOUTH"). # Non-UK cities that occasionally appear in UK bank feeds are also included. _UK_LOCATIONS: list[tuple[str, str]] = [ # ── Multi-word / hyphenated — must come before their short forms ────── ("STRATFORD-UPON-AVON", "Stratford-upon-Avon"), ("STRATFORD UPON AVON", "Stratford-upon-Avon"), ("NEWCASTLE UPON TYNE", "Newcastle upon Tyne"), ("NEWCASTLE-UPON-TYNE", "Newcastle upon Tyne"), ("KINGSTON UPON THAMES", "Kingston upon Thames"), ("WESTON SUPER MARE", "Weston-super-Mare"), ("WESTON-SUPER-MARE", "Weston-super-Mare"), ("STOKE ON TRENT", "Stoke-on-Trent"), ("STOKE-ON-TRENT", "Stoke-on-Trent"), ("LEAMINGTON SPA", "Leamington Spa"), ("ROYAL TUNBRIDGE WELLS", "Royal Tunbridge Wells"), ("TUNBRIDGE WELLS", "Tunbridge Wells"), ("SUTTON COLDFIELD", "Sutton Coldfield"), ("HAYWARDS HEATH", "Haywards Heath"), ("HEMEL HEMPSTEAD", "Hemel Hempstead"), ("WELWYN GARDEN CITY", "Welwyn Garden City"), ("MILTON KEYNES", "Milton Keynes"), ("BISHOPS STORTFORD", "Bishop's Stortford"), ("BISHOP STORTFORD", "Bishop's Stortford"), ("BOGNOR REGIS", "Bognor Regis"), ("LEIGH ON SEA", "Leigh-on-Sea"), ("WESTCLIFF ON SEA", "Westcliff-on-Sea"), ("THORPE BAY", "Thorpe Bay"), ("CANVEY ISLAND", "Canvey Island"), ("BARKING DAGENHAM", "Barking & Dagenham"), ("RICHMOND UPON THAMES", "Richmond upon Thames"), ("ST HELENS", "St Helens"), ("ST ALBANS", "St Albans"), ("ST IVES", "St Ives"), ("WEST BROMWICH", "West Bromwich"), ("EAST KILBRIDE", "East Kilbride"), ("EAST LONDON", "East London"), ("SOUTH LONDON", "South London"), ("NORTH LONDON", "North London"), ("WEST LONDON", "West London"), ("EAST SUSSEX", "East Sussex"), ("WEST SUSSEX", "West Sussex"), ("SOUTH SHIELDS", "South Shields"), ("NORTH SHIELDS", "North Shields"), ("SOUTH WALES", "South Wales"), ("NORTH WALES", "North Wales"), ("NORTH YORKSHIRE", "North Yorkshire"), ("WEST YORKSHIRE", "West Yorkshire"), ("SOUTH YORKSHIRE", "South Yorkshire"), ("EAST YORKSHIRE", "East Yorkshire"), ("GREATER MANCHESTER", "Greater Manchester"), ("PORT TALBOT", "Port Talbot"), ("MERTHYR TYDFIL", "Merthyr Tydfil"), ("RHONDDA", "Rhondda"), ("VALE OF GLAMORGAN", "Vale of Glamorgan"), ("BRIDGEND", "Bridgend"), ("LLANELLI", "Llanelli"), ("NEATH", "Neath"), ("PONTYPOOL", "Pontypool"), ("PONTYPRIDD", "Pontypridd"), ("ABERDARE", "Aberdare"), ("CAERPHILLY", "Caerphilly"), ("NEWBRIDGE", "Newbridge"), ("AMMANFORD", "Ammanford"), ("HAVERFORDWEST", "Haverfordwest"), ("MILFORD HAVEN", "Milford Haven"), ("PEMBROKE DOCK", "Pembroke Dock"), ("CARDIGAN", "Cardigan"), ("ABERYSTWYTH", "Aberystwyth"), ("MACHYNLLETH", "Machynlleth"), ("LLANDUDNO", "Llandudno"), ("COLWYN BAY", "Colwyn Bay"), ("RHYL", "Rhyl"), ("PRESTATYN", "Prestatyn"), ("BUCKLEY", "Buckley"), ("CONNAH QUAY", "Connah's Quay"), # ── Single-word UK cities and large towns ──────────────────────────── ("LONDON", "London"), ("BIRMINGHAM", "Birmingham"), ("MANCHESTER", "Manchester"), ("LIVERPOOL", "Liverpool"), ("LEEDS", "Leeds"), ("SHEFFIELD", "Sheffield"), ("BRISTOL", "Bristol"), ("EDINBURGH", "Edinburgh"), ("GLASGOW", "Glasgow"), ("LEICESTER", "Leicester"), ("COVENTRY", "Coventry"), ("BRADFORD", "Bradford"), ("BELFAST", "Belfast"), ("NOTTINGHAM", "Nottingham"), ("KINGSTON", "Kingston"), ("PLYMOUTH", "Plymouth"), ("WOLVERHAMPTON", "Wolverhampton"), ("SOUTHAMPTON", "Southampton"), ("DERBY", "Derby"), ("SWANSEA", "Swansea"), ("ABERDEEN", "Aberdeen"), ("WESTMINSTER", "Westminster"), ("PORTSMOUTH", "Portsmouth"), ("YORK", "York"), ("PETERBOROUGH", "Peterborough"), ("SUNDERLAND", "Sunderland"), ("MIDDLESBROUGH", "Middlesbrough"), ("CAMBRIDGE", "Cambridge"), ("OXFORD", "Oxford"), ("BRIGHTON", "Brighton"), ("HULL", "Hull"), ("EXETER", "Exeter"), ("GLOUCESTER", "Gloucester"), ("CARDIFF", "Cardiff"), ("NEWPORT", "Newport"), ("WREXHAM", "Wrexham"), ("BANGOR", "Bangor"), ("INVERNESS", "Inverness"), ("DUNDEE", "Dundee"), ("PERTH", "Perth"), ("STIRLING", "Stirling"), ("FALKIRK", "Falkirk"), ("LIVINGSTON", "Livingston"), ("PAISLEY", "Paisley"), ("MOTHERWELL", "Motherwell"), ("HAMILTON", "Hamilton"), ("AIRDRIE", "Airdrie"), ("KILMARNOCK", "Kilmarnock"), ("DUNFERMLINE", "Dunfermline"), ("KIRKCALDY", "Kirkcaldy"), ("GLENROTHES", "Glenrothes"), ("LOUGHBOROUGH", "Loughborough"), ("LUTON", "Luton"), ("NORTHAMPTON", "Northampton"), ("WATFORD", "Watford"), ("READING", "Reading"), ("SLOUGH", "Slough"), ("SWINDON", "Swindon"), ("IPSWICH", "Ipswich"), ("NORWICH", "Norwich"), ("CHELMSFORD", "Chelmsford"), ("COLCHESTER", "Colchester"), ("SOUTHEND", "Southend"), ("BASILDON", "Basildon"), ("HARLOW", "Harlow"), ("STEVENAGE", "Stevenage"), ("HERTFORD", "Hertford"), ("BEDFORD", "Bedford"), ("WORCESTER", "Worcester"), ("HEREFORD", "Hereford"), ("SHREWSBURY", "Shrewsbury"), ("TELFORD", "Telford"), ("STAFFORD", "Stafford"), ("LICHFIELD", "Lichfield"), ("BURTON", "Burton"), ("WALSALL", "Walsall"), ("DUDLEY", "Dudley"), ("WIGAN", "Wigan"), ("BOLTON", "Bolton"), ("BURY", "Bury"), ("SALFORD", "Salford"), ("OLDHAM", "Oldham"), ("ROCHDALE", "Rochdale"), ("STOCKPORT", "Stockport"), ("TAMESIDE", "Tameside"), ("TRAFFORD", "Trafford"), ("BURNLEY", "Burnley"), ("BLACKBURN", "Blackburn"), ("BLACKPOOL", "Blackpool"), ("LANCASTER", "Lancaster"), ("CARLISLE", "Carlisle"), ("BARROW", "Barrow"), ("KENDAL", "Kendal"), ("WAKEFIELD", "Wakefield"), ("HUDDERSFIELD", "Huddersfield"), ("DONCASTER", "Doncaster"), ("BARNSLEY", "Barnsley"), ("ROTHERHAM", "Rotherham"), ("GRIMSBY", "Grimsby"), ("SCUNTHORPE", "Scunthorpe"), ("LINCOLN", "Lincoln"), ("GRANTHAM", "Grantham"), ("BOSTON", "Boston"), ("SKEGNESS", "Skegness"), ("MANSFIELD", "Mansfield"), ("CHESTERFIELD", "Chesterfield"), ("MATLOCK", "Matlock"), ("BUXTON", "Buxton"), ("CREWE", "Crewe"), ("CHESTER", "Chester"), ("MACCLESFIELD", "Macclesfield"), ("WARRINGTON", "Warrington"), ("BIRKENHEAD", "Birkenhead"), ("RUNCORN", "Runcorn"), ("WIDNES", "Widnes"), ("SOUTHPORT", "Southport"), ("WALLASEY", "Wallasey"), ("BOOTLE", "Bootle"), ("REDDITCH", "Redditch"), ("BROMSGROVE", "Bromsgrove"), ("TAMWORTH", "Tamworth"), ("NUNEATON", "Nuneaton"), ("RUGBY", "Rugby"), ("WARWICK", "Warwick"), ("LEAMINGTON", "Leamington"), ("STRATFORD", "Stratford"), ("CHELTENHAM", "Cheltenham"), ("CIRENCESTER", "Cirencester"), ("STROUD", "Stroud"), ("BATH", "Bath"), ("TAUNTON", "Taunton"), ("YEOVIL", "Yeovil"), ("BRIDGWATER", "Bridgwater"), ("WESTON", "Weston"), ("CLEVEDON", "Clevedon"), ("TORQUAY", "Torquay"), ("PAIGNTON", "Paignton"), ("NEWTON ABBOT", "Newton Abbot"), ("BARNSTAPLE", "Barnstaple"), ("TRURO", "Truro"), ("PENZANCE", "Penzance"), ("FALMOUTH", "Falmouth"), ("NEWQUAY", "Newquay"), ("BODMIN", "Bodmin"), ("LAUNCESTON", "Launceston"), ("SALISBURY", "Salisbury"), ("DORCHESTER", "Dorchester"), ("WEYMOUTH", "Weymouth"), ("BOURNEMOUTH", "Bournemouth"), ("POOLE", "Poole"), ("EASTBOURNE", "Eastbourne"), ("WORTHING", "Worthing"), ("CRAWLEY", "Crawley"), ("HORSHAM", "Horsham"), ("GUILDFORD", "Guildford"), ("WOKING", "Woking"), ("FARNHAM", "Farnham"), ("ALDERSHOT", "Aldershot"), ("BASINGSTOKE", "Basingstoke"), ("WINCHESTER", "Winchester"), ("EASTLEIGH", "Eastleigh"), ("FAREHAM", "Fareham"), ("GOSPORT", "Gosport"), ("HAVANT", "Havant"), ("CHICHESTER", "Chichester"), ("LEWES", "Lewes"), ("HASTINGS", "Hastings"), ("RAMSGATE", "Ramsgate"), ("MARGATE", "Margate"), ("FOLKESTONE", "Folkestone"), ("DOVER", "Dover"), ("CANTERBURY", "Canterbury"), ("MAIDSTONE", "Maidstone"), ("TONBRIDGE", "Tonbridge"), ("SEVENOAKS", "Sevenoaks"), ("DARTFORD", "Dartford"), ("GRAVESEND", "Gravesend"), ("ROCHESTER", "Rochester"), ("CHATHAM", "Chatham"), ("GILLINGHAM", "Gillingham"), ("SITTINGBOURNE", "Sittingbourne"), ("FAVERSHAM", "Faversham"), ("ASHFORD", "Ashford"), ("NEWBURY", "Newbury"), ("WOKINGHAM", "Wokingham"), ("BRACKNELL", "Bracknell"), ("WINDSOR", "Windsor"), ("MAIDENHEAD", "Maidenhead"), ("MARLOW", "Marlow"), ("HIGH WYCOMBE", "High Wycombe"), ("AYLESBURY", "Aylesbury"), ("BANBURY", "Banbury"), ("BICESTER", "Bicester"), ("ABINGDON", "Abingdon"), ("WITNEY", "Witney"), ("THAME", "Thame"), ("HENLEY", "Henley"), ("HARROGATE", "Harrogate"), ("SCARBOROUGH", "Scarborough"), ("WHITBY", "Whitby"), ("RICHMOND", "Richmond"), ("DARLINGTON", "Darlington"), ("HARTLEPOOL", "Hartlepool"), ("STOCKTON", "Stockton"), ("DURHAM", "Durham"), ("CHESTER LE STREET", "Chester-le-Street"), ("GATESHEAD", "Gateshead"), ("HEXHAM", "Hexham"), ("MORPETH", "Morpeth"), ("BERWICK", "Berwick"), ("ALNWICK", "Alnwick"), ("WORKINGTON", "Workington"), ("WHITEHAVEN", "Whitehaven"), ("PENRITH", "Penrith"), ("DUMFRIES", "Dumfries"), ("GALLOWAY", "Galloway"), ("AYR", "Ayr"), ("GREENOCK", "Greenock"), ("DUMBARTON", "Dumbarton"), ("CLYDEBANK", "Clydebank"), ("COATBRIDGE", "Coatbridge"), ("WISHAW", "Wishaw"), ("BELLSHILL", "Bellshill"), ("BATHGATE", "Bathgate"), ("ARBROATH", "Arbroath"), ("MONTROSE", "Montrose"), ("FORFAR", "Forfar"), ("KIRRIEMUIR", "Kirriemuir"), ("BRECHIN", "Brechin"), ("ELGIN", "Elgin"), ("FORRES", "Forres"), ("NAIRN", "Nairn"), ("AVIEMORE", "Aviemore"), ("FORT WILLIAM", "Fort William"), ("OBAN", "Oban"), ("CAMPBELTOWN", "Campbeltown"), ("PORTREE", "Portree"), ("STORNOWAY", "Stornoway"), ("LERWICK", "Lerwick"), ("KIRKWALL", "Kirkwall"), ("LONDONDERRY", "Londonderry"), ("DERRY", "Derry"), ("ARMAGH", "Armagh"), ("LISBURN", "Lisburn"), ("NEWRY", "Newry"), ("OMAGH", "Omagh"), ("ENNISKILLEN", "Enniskillen"), ("COLERAINE", "Coleraine"), ("ANTRIM", "Antrim"), ("BALLYMENA", "Ballymena"), ("NEWTOWNABBEY", "Newtownabbey"), ("LARNE", "Larne"), # ── Commonly seen non-UK locations in UK SME feeds ──────────────────── ("DUBLIN", "Dublin"), ("AMSTERDAM", "Amsterdam"), ("PARIS", "Paris"), ("BERLIN", "Berlin"), ("BRUSSELS", "Brussels"), ("NEW YORK", "New York"), ("SAN FRANCISCO", "San Francisco"), ("LOS ANGELES", "Los Angeles"), ] # Pre-compile regex patterns: word-boundary match on the uppercase location token. # Stored as (pattern, display_name) pairs. _UK_LOCATION_PATTERNS: list[tuple[re.Pattern[str], str]] = [ (re.compile(r"(? Optional[str]: """ Attempt to extract a UK city/town (or common non-UK city) from a transaction description+merchant string. Returns the display-name string of the first matched location, or None. The search is performed on the uppercased text so it is case-insensitive. Patterns are ordered longest-first to avoid short-prefix false matches. """ if not text: return None upper = text.upper() for pattern, display in _UK_LOCATION_PATTERNS: if pattern.search(upper): return display return None # --------------------------------------------------------------------------- # TRANSACTION ENRICHMENT # # Six additional columns inspired by open-banking enrichment APIs (Finexer, # Plaid Enrich, Yapily Data+). All enrichment is computed locally — no # external API calls required. # # New columns produced: # merchant_normalised Human-readable merchant name cleaned from raw description # payment_channel direct_debit | standing_order | faster_payment | # bacs_transfer | chaps_transfer | card | atm | unknown # is_recurring True if the transaction recurs on a schedule # counterparty_type hmrc | bank | payroll | lender | owner | card_processor | # marketplace | utility | insurer | supplier | unknown # mcc_hint Approximate ISO 18245 Merchant Category Code (4 digits) # invoice_ref Extracted invoice / PO / reference number (or None) # --------------------------------------------------------------------------- # ── Merchant normalisation ────────────────────────────────────────────────── # Each entry: (compiled regex on UPPERCASE original text, clean display name). # Ordered most-specific first. The first match wins. _MERCHANT_PATTERNS: list[tuple[re.Pattern[str], str]] = [ # Amazon variants (re.compile(r"AMZN\s*MKTP\s*UK"), "Amazon Marketplace"), (re.compile(r"AMZN\s*PRIME"), "Amazon Prime"), (re.compile(r"AMAZON\s*WEB\s*SERVICES"), "Amazon Web Services (AWS)"), (re.compile(r"AMAZON\s*BUSINESS"), "Amazon Business"), (re.compile(r"AMAZON\s*ADVERTISING"), "Amazon Advertising"), (re.compile(r"AMAZON"), "Amazon"), # Apple (re.compile(r"APPLE\.COM/BILL"), "Apple Subscription"), (re.compile(r"ITUNES\.COM|ITUNES"), "Apple / iTunes"), (re.compile(r"APPLE\s*STORE"), "Apple Store"), (re.compile(r"APPLE"), "Apple"), # Google (re.compile(r"GOOGLE\s*ADS|GOOGLE\s*ADWORDS"), "Google Ads"), (re.compile(r"GOOGLE\s*WORKSPACE|GOOG\s*GSUITE"), "Google Workspace"), (re.compile(r"GOOGLE\s*CLOUD"), "Google Cloud"), (re.compile(r"GOOGLE\s*STORAGE|GOOGLE\s*ONE"), "Google Storage"), (re.compile(r"GOOGLE"), "Google"), # Microsoft (re.compile(r"MICROSOFT\s*365|MS\s*365|OFFICE\s*365"), "Microsoft 365"), (re.compile(r"MICROSOFT\s*AZURE"), "Microsoft Azure"), (re.compile(r"MICROSOFT"), "Microsoft"), # Meta / Facebook (re.compile(r"FACEBOOK\s*ADS|META\s*ADS"), "Meta Ads"), (re.compile(r"FACEBOOK|META\s*PLATFORMS"), "Meta"), # Stripe / PayPal / payment processors (re.compile(r"STRIPE"), "Stripe"), (re.compile(r"PAYPAL\s*\*?([A-Z0-9 ]+)"), "PayPal"), (re.compile(r"SQ\s*\*"), "Square"), (re.compile(r"SUMUP"), "SumUp"), (re.compile(r"IZETTLE|ZETTLE"), "Zettle (PayPal)"), (re.compile(r"WORLDPAY"), "Worldpay"), (re.compile(r"DOJO"), "Dojo"), (re.compile(r"GOCARDLESS"), "GoCardless"), # Uber / ride & food (re.compile(r"UBER\s*EATS"), "Uber Eats"), (re.compile(r"UBER\s*\*?\s*TRIP|UBER\s*BV"), "Uber"), (re.compile(r"UBER"), "Uber"), (re.compile(r"DELIVEROO"), "Deliveroo"), (re.compile(r"JUST\s*EAT"), "Just Eat"), # Streaming / subscriptions (re.compile(r"NETFLIX"), "Netflix"), (re.compile(r"SPOTIFY"), "Spotify"), (re.compile(r"ADOBE\s*CREATIVE|ADOBE\s*CC"), "Adobe Creative Cloud"), (re.compile(r"ADOBE\s*ACROBAT"), "Adobe Acrobat"), (re.compile(r"ADOBE"), "Adobe"), (re.compile(r"DROPBOX"), "Dropbox"), (re.compile(r"SLACK"), "Slack"), (re.compile(r"ZOOM\s*VIDEO|ZOOM"), "Zoom"), (re.compile(r"NOTION"), "Notion"), (re.compile(r"ATLASSIAN|JIRA"), "Atlassian / Jira"), (re.compile(r"GITHUB"), "GitHub"), (re.compile(r"MAILCHIMP"), "Mailchimp"), (re.compile(r"XERO"), "Xero"), (re.compile(r"QUICKBOOKS"), "QuickBooks"), (re.compile(r"HUBSPOT"), "HubSpot"), (re.compile(r"SALESFORCE"), "Salesforce"), (re.compile(r"OPENAI"), "OpenAI"), (re.compile(r"ANTHROPIC"), "Anthropic"), # HMRC — must match specific variants before generic HMRC (re.compile(r"HMRC\s*(?:PAYE|CUMBERNAULD|SHIPLEY)"), "HMRC (PAYE)"), (re.compile(r"HMRC\s*(?:COTAX|CT)"), "HMRC (Corporation Tax)"), (re.compile(r"HMRC\s*VAT"), "HMRC (VAT)"), (re.compile(r"HMRC\s*SELF\s*ASSESSMENT|HMRC\s*SA"), "HMRC (Self Assessment)"), (re.compile(r"HMRC"), "HMRC"), # UK utilities (re.compile(r"OCTOPUS\s*ENERGY"), "Octopus Energy"), (re.compile(r"BRITISH\s*GAS"), "British Gas"), (re.compile(r"EDF\s*ENERGY"), "EDF Energy"), (re.compile(r"THAMES\s*WATER"), "Thames Water"), (re.compile(r"SEVERN\s*TRENT"), "Severn Trent Water"), (re.compile(r"SCOTTISH\s*POWER"), "Scottish Power"), # UK banks (fee payments) (re.compile(r"BARCLAYS"), "Barclays"), (re.compile(r"LLOYDS\s*BANK|LLOYDS\s*TSB"), "Lloyds Bank"), (re.compile(r"NATWEST"), "NatWest"), (re.compile(r"HSBC"), "HSBC"), (re.compile(r"SANTANDER"), "Santander"), (re.compile(r"STARLING\s*BANK|STARLING"), "Starling Bank"), (re.compile(r"MONZO"), "Monzo"), (re.compile(r"REVOLUT"), "Revolut"), (re.compile(r"TIDE"), "Tide"), # Pension providers (re.compile(r"NEST\s*(?:PENSION|PENSIONS|CONTRIB|LTD)"), "NEST Pension"), (re.compile(r"PEOPLES\s*PENSION|THE\s*PEOPLES\s*PENSION"), "The People's Pension"), (re.compile(r"AVIVA\s*PENSION"), "Aviva Pension"), (re.compile(r"SCOTTISH\s*WIDOWS"), "Scottish Widows"), (re.compile(r"STANDARD\s*LIFE"), "Standard Life"), # Travel (re.compile(r"TRAINLINE"), "Trainline"), (re.compile(r"TRANSPORT\s*FOR\s*LONDON|TFL"), "Transport for London (TfL)"), (re.compile(r"PREMIER\s*INN"), "Premier Inn"), (re.compile(r"TRAVELODGE"), "Travelodge"), (re.compile(r"EASYJET"), "easyJet"), (re.compile(r"RYANAIR"), "Ryanair"), (re.compile(r"BRITISH\s*AIRWAYS"), "British Airways"), # Trade / suppliers (re.compile(r"SCREWFIX"), "Screwfix"), (re.compile(r"TOOLSTATION"), "Toolstation"), (re.compile(r"TRAVIS\s*PERKINS"), "Travis Perkins"), (re.compile(r"COMPANIES\s*HOUSE"), "Companies House"), ] def get_merchant_normalised(raw_text: str) -> Optional[str]: """ Return a clean merchant display name extracted from the raw transaction description/merchant string. Returns None if no pattern matches. Uses the original (un-normalised) text to preserve casing signals that the uppercase normalised version loses (e.g. 'SQ *Merchant Name'). """ upper = raw_text.upper() for pattern, clean_name in _MERCHANT_PATTERNS: if pattern.search(upper): return clean_name return None # ── Payment channel detection ─────────────────────────────────────────────── # Ordered: most specific first. _CHANNEL_RULES: list[tuple[str, list[str]]] = [ ("direct_debit", ["DIRECT DEBIT", "D D ", "DDR", "/D D", "DD REF", "DD MANDATE"]), ("standing_order", ["STANDING ORDER", "S O ", "STNDING ORDER", "STG ORDER"]), ("chaps_transfer", ["CHAPS"]), ("bacs_transfer", ["BACS CREDIT", "BACS PAYMENT", "BACS TRANSFER", "BACS SALARY", "BACS WAGES", "BACS IN", "BACS OUT", "BACS"]), ("faster_payment", ["FASTER PAYMENT", "FPS IN", "FPS OUT", "FPS CREDIT", "FASTER PAYMENTS", "FPSIN", "FPSOUT"]), ("atm", ["ATM WITHDRAWAL", "CASH MACHINE", "ATM CASH", "CASH WITHDRAWAL", "ATM ", "ATM$"]), ("card", ["CARD PAYMENT", "CARD PURCHASE", "CONTACTLESS", "CHIP AND PIN", "VISA PURCHASE", "MASTERCARD", "AMEX PAYMENT"]), ] def get_payment_channel(norm_text: str) -> str: """Detect payment channel from normalised transaction description.""" for channel, keywords in _CHANNEL_RULES: for kw in keywords: if kw in norm_text: return channel return "unknown" # ── Recurring payment detection ───────────────────────────────────────────── _RECURRING_SIGNALS: frozenset[str] = frozenset([ "DIRECT DEBIT", "DDR", "STANDING ORDER", "SUBSCRIPTION", "MONTHLY PLAN", "ANNUAL PLAN", "MONTHLY FEE", "ANNUAL FEE", "RECURRING", "AUTO RENEWAL", "AUTO RENEW", "AUTORENEWAL", "MONTHLY SUBSCRIPTION", "ANNUAL SUBSCRIPTION", ]) def is_recurring_payment(norm_text: str) -> bool: """Return True if the transaction appears to be a recurring/scheduled payment.""" return any(sig in norm_text for sig in _RECURRING_SIGNALS) # ── Counterparty type ─────────────────────────────────────────────────────── # Maps from the tax category string to a counterparty type label. _COUNTERPARTY_TYPE_MAP: dict[str, str] = { # HMRC "Corporation tax": "hmrc", "VAT payment": "hmrc", "Self assessment / income tax": "hmrc", "HMRC interest & penalties": "hmrc", "Fines & penalties (non-deductible)": "hmrc_or_regulator", # Staff / payroll "Staff costs": "payroll_or_hmrc", # Finance "Bank, credit card and other financial charges": "bank", "Loan repayment (balance sheet)": "lender", # Equity "Transfer / owner drawings (review)": "owner", "Transfer / payment (review needed)": "unknown", "Dividends paid (balance sheet)": "director_or_shareholder", # Insurance "Insurance cost": "insurer", # Property / utilities "Rent, rates & power": "utility_or_landlord", # Suppliers / trade "Stock & materials / Cost of sales": "trade_supplier", "Equipment (review for capital expenditure)":"equipment_supplier", "Repairs & maintenance": "maintenance_supplier", # Advertising / software / professional "Advertising & Marketing": "marketing_supplier", "Phone & Office costs": "software_or_telecom", "Accountancy & legal": "professional_adviser", "Training & development": "training_provider", "Professional subscriptions": "professional_body", # Travel / subsistence / entertaining "Car, van and travel expenses": "travel_or_transport", "Subsistence": "food_and_drink", "Client entertaining (non-deductible)": "entertainment_or_hospitality", # Income side "card_processor_payouts": "card_processor", "marketplace_sales_payouts": "marketplace", "interest_income": "bank", "rd_tax_credit": "hmrc", "vat_repayment": "hmrc", "corporation_tax_repayment": "hmrc", "loan_proceeds": "lender", "capital_introduced": "owner", "internal_transfers": "own_account", "grants_and_support": "government_or_grant_body", "dividends_received": "investee_company", "rental_income": "tenant", "customer_receipts_sales": "customer", "cash_deposits": "unknown", "other_income_unclear": "unknown", } def get_counterparty_type(category: str) -> str: """Return a counterparty type label for the given tax category.""" return _COUNTERPARTY_TYPE_MAP.get(category, "supplier") # ── MCC hint (ISO 18245 Merchant Category Codes) ──────────────────────────── # Approximate MCC codes mapped from our tax categories. # These are indicative only — actual MCC is set by the card network and may # differ. Useful for benchmarking, VAT validation, and UI display. _MCC_HINT_MAP: dict[str, str] = { "Corporation tax": "9311", # Tax Payments (Govt) "VAT payment": "9311", "Self assessment / income tax": "9311", "HMRC interest & penalties": "9311", "Fines & penalties (non-deductible)": "9399", # Govt Services - NEC "Staff costs": "7321", # Consumer Credit / Payroll "Bank, credit card and other financial charges": "6012", # Financial Institutions "Loan repayment (balance sheet)": "6012", "Dividends paid (balance sheet)": "6012", "Insurance cost": "6300", # Insurance - NEC "Rent, rates & power": "4900", # Utilities "Advertising & Marketing": "7311", # Advertising Services "Phone & Office costs": "7372", # Computer/Data Processing "Car, van and travel expenses": "4111", # Local Transport "Accountancy & legal": "8931", # Accounting/Bookkeeping "Professional subscriptions": "8699", # Member Organisations "Training & development": "8299", # Educational Services "Stock & materials / Cost of sales": "5065", # Electronic Parts & Equip "Equipment (review for capital expenditure)": "5734", # Computer/Software Stores "Repairs & maintenance": "7699", # Repair Shops - NEC "Client entertaining (non-deductible)": "5812", # Eating Places "Subsistence": "5812", "Transfer / owner drawings (review)": "6012", "Transfer / payment (review needed)": "6012", "Other business expenses": "5999", # Misc Retail - NEC # Income categories "card_processor_payouts": "7399", # Business Services - NEC "marketplace_sales_payouts": "5999", "interest_income": "6012", "rd_tax_credit": "9311", "vat_repayment": "9311", "corporation_tax_repayment": "9311", "loan_proceeds": "6012", "capital_introduced": "6012", "grants_and_support": "9399", "rental_income": "6513", # Apartment / Real Estate "dividends_received": "6012", "customer_receipts_sales": "5999", } def get_mcc_hint(category: str) -> Optional[str]: """Return an approximate ISO 18245 MCC code for the given tax category.""" return _MCC_HINT_MAP.get(category) # ── Invoice / PO reference extraction ────────────────────────────────────── # Extract structured references from the ORIGINAL (un-normalised) description. # We match against the raw text before normalise_text strips punctuation. _INVOICE_REF_RE = re.compile( r"\b(?:" r"INV[-#\s]?\d{3,}" # INV-1234 / INV#1234 / INV 1234 / INV1234 r"|PO[-#\s]?\d{3,}" # PO-1234 / PO#1234 / PO 1234 r"|SI[-#\s]?\d{3,}" # Sales Invoice number r"|CN[-#\s]?\d{3,}" # Credit Note number r"|QT[-#\s]?\d{3,}" # Quote number r"|REF[:\s-]+\d{4,}" # REF: 12345 / REF-12345 / REF 12345 (4+ digits) r"|ORDER[-#\s]?\d[\d\-]{2,}" # ORDER-1234 / ORDER-2025-001 (allows dashes in number) r")", re.IGNORECASE, ) def extract_invoice_ref(raw_text: str) -> Optional[str]: """ Extract an invoice, PO, or order reference number from the ORIGINAL (un-normalised) transaction description. Returns the first match or None. """ if not raw_text: return None m = _INVOICE_REF_RE.search(raw_text) return m.group(0).strip() if m else None # --------------------------------------------------------------------------- # MySQL connection # --------------------------------------------------------------------------- def make_mysql_engine(db_name: Optional[str] = None) -> Engine: load_dotenv() host = os.environ["MYSQL_HOST"] port = int(os.getenv("MYSQL_PORT", "3306")) db = db_name or os.environ["MYSQL_DB"] user = os.environ["MYSQL_USER"] pwd = os.environ["MYSQL_PASSWORD"] url = f"mysql+pymysql://{user}:{pwd}@{host}:{port}/{db}?charset=utf8mb4" return create_engine( url, future=True, pool_pre_ping=True, pool_recycle=1800, connect_args={ "connect_timeout": 30, "read_timeout": 300, "write_timeout": 300, }, ) # --------------------------------------------------------------------------- # Heuristic classification functions (use the rule tables above) # --------------------------------------------------------------------------- _TRANSFER_SIGNALS: frozenset[str] = frozenset([ "TRANSFER", "SENT FROM", "SENT TO", "FASTER PAYMENT", "BACS TRANSFER", "CHAPS TRANSFER", "FPS OUT", "BACS OUT", ]) _NEOBANK_NAMES: frozenset[str] = frozenset([ "MONZO", "REVOLUT", "STARLING", "WISE", "TRANSFERWISE", "CASHPLUS", "ANNA MONEY", "COCONUT", "TIDE", ]) def apply_expense_heuristic(norm_text: str) -> tuple[str, float, str]: """ Iterate EXPENSE_RULES in order; return (category, confidence, note). If no rule matches, a secondary analysis runs before falling back to 'Other business expenses': - Any neobank name (Monzo, Revolut, Starling, Wise …) in a debit almost certainly means an owner drawing or internal transfer. - Generic transfer-pattern keywords flag the row for manual review rather than silently labelling it a P&L expense. """ for category, keywords, confidence in EXPENSE_RULES: for kw in keywords: if kw in norm_text: return category, confidence, f"heuristic: matched '{kw}'" # ── Secondary analysis for unmatched debits ────────────────────────────── # 1. Neobank name present → very likely owner drawing / internal transfer. for bank in _NEOBANK_NAMES: if bank in norm_text: return ( "Transfer / owner drawings (review)", 0.45, f"heuristic: neobank name '{bank}' in debit — " "probable owner drawing or own-account transfer; " "not a P&L expense", ) # 2. Transfer-pattern signals → unknown destination, flag for review. for sig in _TRANSFER_SIGNALS: if sig in norm_text: return ( "Transfer / payment (review needed)", 0.30, f"heuristic: transfer keyword '{sig}' — " "verify payee and purpose before posting to P&L", ) # 3. True catch-all. return ( "Other business expenses", 0.35, "heuristic: no rule matched — manual review recommended", ) def apply_income_rules( norm_text: str, ) -> tuple[str, str, float, bool, str]: """ Iterate INCOME_RULES in order. Returns (category, issue_reason, confidence, is_balance_sheet, note). """ for category, issue_reason, keywords, confidence, is_bs in INCOME_RULES: for kw in keywords: if kw in norm_text: return ( category, issue_reason, confidence, is_bs, f"income_rule: matched '{kw}'", ) return ( "other_income_unclear", ( "Unclassified credit — no matching rule found. " "Likely sources: customer payment, grant, asset sale, loan drawdown, or personal funds. " "Manual classification required before posting to P&L." ), 0.30, # 0.30 = honest 'no idea' — aligns with expense fallback (0.35) and transfer-signal catch (0.30) False, "income_rule: no rule matched — fallback to other_income_unclear", ) # --------------------------------------------------------------------------- # Classification helper functions # --------------------------------------------------------------------------- def get_confidence_label(score: float) -> str: """Convert a 0–1 confidence score to a human-readable label.""" if score >= 0.85: return "high" if score >= 0.70: return "medium" return "low" def get_vat_treatment(category: str, is_expense: bool) -> str: """Return the most likely VAT treatment for a given category.""" lookup = _VAT_TREATMENT_EXPENSE if is_expense else _VAT_TREATMENT_INCOME return lookup.get(category, "unknown") def get_is_deductible(category: str) -> str: """Return CT/SA deductibility for an expense category ('yes'/'no'/'review').""" return _IS_DEDUCTIBLE_EXPENSE.get(category, "review") def get_review_priority( category: str, score: float, is_balance_sheet: bool, amount: Optional[float] = None, ) -> str: """ Return a review priority string for the accountant review queue. Priority levels: urgent — accountant must confirm before posting (balance-sheet items, ambiguous transfers, amounts > £10,000) normal — AI is uncertain or amount warrants a second look low — AI is reasonably confident but accountant may want to glance (empty) — high confidence, standard P&L expense/income, no special flag """ abs_amount = abs(amount) if amount is not None else 0.0 # Urgent: transfers/drawings and balance-sheet items other than routine CT/VAT _routine_bs = {"Corporation tax", "VAT payment"} if is_balance_sheet and category not in _routine_bs: return "urgent" if "review" in category.lower(): return "urgent" if abs_amount >= _LARGE_AMOUNT_URGENT: return "urgent" # Non-deductible categories always need accountant confirmation _non_deductible_flag = { "Fines & penalties (non-deductible)", "HMRC interest & penalties", "Client entertaining (non-deductible)", } if category in _non_deductible_flag: return "normal" # not urgent but must be confirmed before posting # Normal: uncertain category, capex risk, or moderately large amount if category == "Equipment (review for capital expenditure)": return "normal" if abs_amount < _CAPEX_REVIEW_AMOUNT else "urgent" if abs_amount >= _LARGE_AMOUNT_NORMAL: return "normal" if score < 0.70: return "normal" # Low: borderline confidence if score < 0.85: return "low" return "" def get_review_reason( category: str, score: float, is_balance_sheet: bool, is_deductible: str, amount: Optional[float] = None, ) -> str: """ Return a plain-English reason for why this transaction is flagged for review. Returns empty string if no review is needed. """ reasons: list[str] = [] abs_amount = abs(amount) if amount is not None else 0.0 _routine_bs = {"Corporation tax", "VAT payment"} if is_balance_sheet and category not in _routine_bs: reasons.append( "Balance-sheet movement — confirm posting account " "(not a P&L expense/income)" ) if category in {"Fines & penalties (non-deductible)", "HMRC interest & penalties"}: reasons.append( "Non-deductible for CT/SA — do NOT include in taxable profit calculation (CTA 2009 s.1298/s.1303)" ) if category == "Client entertaining (non-deductible)": reasons.append( "Client entertaining — NOT deductible for CT (CTA 2009 s.1298) and input VAT is blocked (VATA 1994 s.24). " "Confirm whether this is employee-only subsistence (deductible) or client entertainment (not)." ) if category == "Loan repayment (balance sheet)": reasons.append( "Loan repayment — capital element reduces balance-sheet liability (not P&L); " "interest element IS deductible (CTA 2009 Part 5). " "Obtain repayment schedule from lender to apportion capital vs interest." ) if category == "Dividends paid (balance sheet)": reasons.append( "Dividend payment — appropriation of retained profit; NOT a P&L expense (CTA 2009 s.1305). " "Post as debit to Retained Earnings / Dividends account. " "Ensure dividend paperwork (board minutes + dividend voucher) is in place." ) if "Transfer / owner drawings" in category: reasons.append( "Possible owner drawing or own-account transfer — " "post to Directors Loan Account or equity, not P&L" ) if "Transfer / payment (review needed)" in category: reasons.append( "Transfer-like description with no matching rule — " "identify payee before posting" ) if category == "Equipment (review for capital expenditure)": if abs_amount >= _CAPEX_REVIEW_AMOUNT: reasons.append( f"Amount £{abs_amount:,.0f} exceeds £{_CAPEX_REVIEW_AMOUNT:,.0f} — " "likely capital expenditure; consider Annual Investment Allowance " "or Written Down Allowance instead of revenue expense" ) else: reasons.append( "Possible capital equipment purchase — confirm revenue vs capital" ) if is_deductible == "no": reasons.append("Not deductible for CT/SA — exclude from taxable profit calculation") if is_deductible == "review": reasons.append("Deductibility uncertain — confirm with accountant") if abs_amount >= _LARGE_AMOUNT_URGENT and category not in _routine_bs: reasons.append( f"Large amount (£{abs_amount:,.0f}) — verify business purpose and supporting invoice" ) elif abs_amount >= _LARGE_AMOUNT_NORMAL and category not in _routine_bs: reasons.append( f"Amount £{abs_amount:,.0f} warrants invoice check" ) if score < 0.70: reasons.append( f"Low AI confidence ({score:.0%}) — description did not clearly match " "a known category; manual classification recommended" ) return "; ".join(reasons) # --------------------------------------------------------------------------- # Main classifier # --------------------------------------------------------------------------- def classify_transactions( tx_df: pd.DataFrame, include_debug: bool, ) -> pd.DataFrame: """ Classify bank transactions into UK tax categories. Debits → expense_tax_category via EXPENSE_RULES heuristic Credits → income_category via INCOME_RULES table All original columns are preserved unchanged. Added columns (always present): direction — "expense" | "income" | "balance_sheet" | "unknown" tax_category — unified value (fills from expense or income side) expense_tax_category — category for debit rows; None for credits income_category — category for credit rows; None for debits confidence — 0.0–1.0 AI confidence score confidence_label — "high" | "medium" | "low" needs_review — True if accountant action is required review_priority — "urgent" | "normal" | "low" | "" review_reason — plain-English reason for the review flag classification_note — which keyword / rule triggered the AI decision is_deductible — "yes" | "no" | "review" (CT/SA deductibility) vat_treatment_hint — standard_rated | zero_rated | exempt | outside_scope | blocked_input | mixed | unknown balance_sheet_item — True for CT, VAT, loans, transfers, drawings classified_at_utc — ISO 8601 UTC timestamp location — UK city/town extracted from description+merchant, or None Debug-only columns (--include-debug): norm_desc — normalised text used for matching """ for col in ("description", "type", "merchant"): if col not in tx_df.columns: raise ValueError(f"Source table must have a column named '{col}'") # Detect optional amount column for capex / large-amount flags amount_col: Optional[str] = next( (c for c in ("amount", "Amount", "AMOUNT") if c in tx_df.columns), None ) out = tx_df.copy() out["description"] = out["description"].fillna("").astype(str) out["merchant"] = out["merchant"].fillna("").astype(str) out["type"] = out["type"].fillna("").astype(str).str.lower().str.strip() out["classification_text"] = out.apply( lambda r: build_classification_text(r["description"], r["merchant"]), axis=1, ) # Initialise all output columns with safe defaults out["direction"] = "unknown" out["tax_category"] = None out["expense_tax_category"] = None out["income_category"] = None out["confidence"] = None out["confidence_label"] = None out["needs_review"] = True out["review_priority"] = "normal" out["review_reason"] = "transaction type unknown — expected 'debit' or 'credit'" out["classification_note"] = None out["is_deductible"] = None out["vat_treatment_hint"] = None out["balance_sheet_item"] = False out["classified_at_utc"] = datetime.now(timezone.utc).isoformat() out["location"] = out["classification_text"].apply(extract_location) # ── Enrichment columns (computed once, vectorised where possible) ───────── # merchant_normalised: human-readable merchant name out["merchant_normalised"] = out["classification_text"].apply(get_merchant_normalised) # payment_channel: BACS/FPS/DD/SO/card/ATM — computed from normalised text _norm_all = out["classification_text"].map(normalize_text) out["payment_channel"] = _norm_all.map(get_payment_channel) out["is_recurring"] = _norm_all.map(is_recurring_payment) # invoice_ref: extracted from ORIGINAL description (before normalisation strips punctuation) out["invoice_ref"] = out["description"].apply(extract_invoice_ref) # counterparty_type and mcc_hint are category-dependent — set after loop out["counterparty_type"] = None out["mcc_hint"] = None debit_mask = out["type"] == "debit" credit_mask = out["type"] == "credit" unknown_mask = ~(debit_mask | credit_mask) # ──────────────────────────────────────────────────────────────────────── # DEBITS — EXPENSE_RULES heuristic # ──────────────────────────────────────────────────────────────────────── debits = out.loc[debit_mask].copy() if len(debits) > 0: norms = debits["classification_text"].map(normalize_text).tolist() amounts: list[Optional[float]] = ( debits[amount_col].tolist() if amount_col else [None] * len(debits) ) for i, (nd, raw_amount) in enumerate( tqdm(zip(norms, amounts), total=len(norms), desc="Classifying debits") ): try: amount_f: Optional[float] = float(raw_amount) # type: ignore[arg-type] except (TypeError, ValueError): amount_f = None cat, conf, note = apply_expense_heuristic(nd) is_bs = cat in BALANCE_SHEET_EXPENSE_CATS deduct = get_is_deductible(cat) priority = get_review_priority(cat, conf, is_bs, amount_f) reason = get_review_reason(cat, conf, is_bs, deduct, amount_f) direction = "balance_sheet" if is_bs else "expense" idx = debits.index[i] debits.at[idx, "direction"] = direction debits.at[idx, "tax_category"] = cat debits.at[idx, "expense_tax_category"] = cat debits.at[idx, "confidence"] = conf debits.at[idx, "confidence_label"] = get_confidence_label(conf) debits.at[idx, "needs_review"] = bool(priority) debits.at[idx, "review_priority"] = priority debits.at[idx, "review_reason"] = reason debits.at[idx, "classification_note"] = note debits.at[idx, "is_deductible"] = deduct debits.at[idx, "vat_treatment_hint"] = get_vat_treatment(cat, is_expense=True) debits.at[idx, "balance_sheet_item"] = is_bs debits.at[idx, "counterparty_type"] = get_counterparty_type(cat) debits.at[idx, "mcc_hint"] = get_mcc_hint(cat) if include_debug: debits["norm_desc"] = norms # ──────────────────────────────────────────────────────────────────────── # CREDITS — INCOME_RULES table # ──────────────────────────────────────────────────────────────────────── credits = out.loc[credit_mask].copy() if len(credits) > 0: norms_c = credits["classification_text"].map(normalize_text).tolist() amounts_c: list[Optional[float]] = ( credits[amount_col].tolist() if amount_col else [None] * len(credits) ) for i, (nd, raw_amount) in enumerate( tqdm( zip(norms_c, amounts_c), total=len(norms_c), desc="Classifying credits", ) ): try: amount_f = float(raw_amount) # type: ignore[arg-type] except (TypeError, ValueError): amount_f = None cat, issue_reason, score, is_bs, note = apply_income_rules(nd) priority = get_review_priority(cat, score, is_bs, amount_f) # Merge the rule's own issue_reason with the priority-based reason rule_reason = issue_reason amount_reason = get_review_reason(cat, score, is_bs, "yes", amount_f) combined_reason = "; ".join( r for r in (rule_reason, amount_reason) if r ) direction = "balance_sheet" if is_bs else "income" idx = credits.index[i] credits.at[idx, "direction"] = direction credits.at[idx, "tax_category"] = cat credits.at[idx, "income_category"] = cat credits.at[idx, "confidence"] = score credits.at[idx, "confidence_label"] = get_confidence_label(score) credits.at[idx, "needs_review"] = bool(priority or issue_reason) credits.at[idx, "review_priority"] = priority if priority else ( "normal" if issue_reason else "" ) credits.at[idx, "review_reason"] = combined_reason credits.at[idx, "classification_note"] = note credits.at[idx, "is_deductible"] = "n/a" # income rows don't have deductibility credits.at[idx, "vat_treatment_hint"] = get_vat_treatment(cat, is_expense=False) credits.at[idx, "balance_sheet_item"] = is_bs credits.at[idx, "counterparty_type"] = get_counterparty_type(cat) credits.at[idx, "mcc_hint"] = get_mcc_hint(cat) if include_debug: credits["norm_desc"] = norms_c # ──────────────────────────────────────────────────────────────────────── # UNKNOWN type — flag everything for manual review # ──────────────────────────────────────────────────────────────────────── unknowns = out.loc[unknown_mask].copy() if len(unknowns) > 0: unknowns["direction"] = "unknown" unknowns["needs_review"] = True unknowns["review_priority"] = "urgent" unknowns["review_reason"] = ( "Transaction type is not 'debit' or 'credit' — " "check data feed; no classification attempted" ) unknowns["classification_note"] = "no classification attempted: unknown type" # Rebuild in original order parts = [] if len(debits) > 0: parts.append(debits) if len(credits) > 0: parts.append(credits) if len(unknowns) > 0: parts.append(unknowns) if not parts: return out.iloc[0:0].copy() return pd.concat(parts, ignore_index=False).sort_index() # --------------------------------------------------------------------------- # Safe writer # --------------------------------------------------------------------------- def safe_write_large_dataframe( df: pd.DataFrame, engine: Engine, target_table: str, if_exists: str, chunksize: int = 2000, ) -> None: error_table = f"{target_table}__write_errors" total = len(df) first_chunk = True mode_first = if_exists for start in range(0, total, chunksize): end = min(start + chunksize, total) chunk = df.iloc[start:end].copy() mode = mode_first if first_chunk else "append" try: with engine.begin() as conn: chunk.to_sql( name=target_table, con=conn, if_exists=mode, index=False, method="multi", chunksize=1000, ) first_chunk = False except Exception as e: print( f"Chunk {start}-{end} failed. " f"Falling back to row-by-row. Reason: {type(e).__name__}: {e}" ) if first_chunk and mode == "replace": with engine.begin() as conn: chunk.iloc[0:0].to_sql( name=target_table, con=conn, if_exists="replace", index=False, ) first_chunk = False failed_rows = [] for ridx in range(len(chunk)): one = chunk.iloc[ridx : ridx + 1] try: with engine.begin() as conn: one.to_sql( name=target_table, con=conn, if_exists="append", index=False, method="multi", ) except Exception as row_e: rec = one.copy() rec["write_error"] = f"{type(row_e).__name__}: {row_e}" failed_rows.append(rec) if failed_rows: failed_df = pd.concat(failed_rows, ignore_index=True) with engine.begin() as conn: failed_df.to_sql( name=error_table, con=conn, if_exists="append", index=False, method="multi", chunksize=500, ) print(f"Wrote {len(failed_df)} failed rows into {error_table}") # --------------------------------------------------------------------------- # Main # --------------------------------------------------------------------------- def main() -> None: parser = argparse.ArgumentParser( description="Classify debit/credit transactions and write results to MySQL." ) parser.add_argument("--source-table", required=True) parser.add_argument("--target-table", required=True) parser.add_argument( "--if-exists", default="replace", choices=["replace", "append", "fail"] ) parser.add_argument("--limit", type=int, default=0) parser.add_argument("--include-debug", action="store_true") parser.add_argument("--write-chunksize", type=int, default=2000) args = parser.parse_args() validate_table_name(args.source_table) validate_table_name(args.target_table) engine_in = make_mysql_engine() target_db, target_tbl = parse_db_table(args.target_table) engine_out = make_mysql_engine(db_name=target_db) if target_db else engine_in with engine_in.connect() as conn: conn = conn.execution_options(isolation_level="AUTOCOMMIT") limit_clause = f" LIMIT {args.limit}" if args.limit > 0 else "" query = text(f"SELECT * FROM {args.source_table}{limit_clause}") tx_df = pd.read_sql_query(query, conn) print(f"Loaded {len(tx_df):,} transactions.") tx_recent, tx_old, ts_col, min_year = split_by_timestamp_env(tx_df) if len(tx_old) > 0: print( f"{len(tx_old):,} rows excluded " f"({ts_col} year < {min_year} or unparseable). They will NOT be written." ) if len(tx_recent) == 0: print( f"No rows with {ts_col} year >= {min_year} found. " "Nothing to classify/write." ) return classified_df = classify_transactions( tx_df=tx_recent, include_debug=args.include_debug, ) safe_write_large_dataframe( df=classified_df, engine=engine_out, target_table=target_tbl, if_exists=args.if_exists, chunksize=args.write_chunksize, ) print( f"Wrote {len(classified_df):,} rows " f"({ts_col} year >= {min_year}) to {args.target_table}." ) if __name__ == "__main__": main()