Transaction Categorization

Categorizes client transaction lines against HASP, SAP me2n, contract pricing, and FBL1N payment data, then writes a multi-sheet Excel workbook with a category (H04–H22), reason, and dated comment for every line.

Requirements

• Python 3.9+
• pandas, numpy, openpyxl

pip install pandas numpy openpyxl

Basic usage

python categorize_transactions.py INPUT.xlsx

INPUT.xlsx must contain:

• a transaction sheet (the first sheet that isn't named HASP, me2n, or FBL1N — or pass --tx-sheet to name it explicitly)
• a sheet named HASP

Output is written next to the input as INPUT_categorized.xlsx (override with --out).

Optional data sources

python categorize_transactions.py INPUT.xlsx \
    --contract-file contracts.xlsx \
    --me2n-file me2n_export.xlsx \
    --fbl1n-file fbl1n_export.xlsx \
    --out results.xlsx

• Contract pricing (--contract-file): enables the price checks (ERS adjustment, incorrect price, overpayment). Looked up by the matched HASP material, falling back to the transaction part number when the PO has no HASP match.
• me2n (--me2n-file): SAP purchasing export; drives the PO issue, blanket PO, POD requested, invoice requested, and credit note checks. Alternatively, include a sheet named me2n in the input workbook.
• FBL1N (--fbl1n-file): SAP vendor line items export; enriches the "Paid, Not Offset" comments with actual clearing dates and amounts paid, matched by invoice number. Alternatively, include a sheet named FBL1N in the input workbook.

All flags

Flag	Description
input_file	Source workbook with the transaction + HASP sheets (required)
--tx-sheet	Transaction sheet name (default: first non-reserved sheet)
--hasp-sheet	HASP sheet name (default: HASP)
--out	Output path (default: <input>_categorized.xlsx)
--contract-file	Contract pricing workbook
--contract-sheet	Contract pricing sheet name (default: first sheet)
--me2n-file	me2n SAP export workbook
--me2n-sheet	me2n sheet name (default: first sheet)
--fbl1n-file	FBL1N SAP export workbook
--fbl1n-sheet	FBL1N sheet name (default: first sheet)

Output workbook

Sheet	Contents
Categorized	Every transaction line with its Category, Reason, and Output Comment
Summary	Row counts by category and match type
Run_Summary	Run metrics (rows processed, lookups, category counts)
*_Normalized	The cleaned input data as the script saw it (for auditing)

Tests

python test_categorization.py