✅ Reconciliation complete!
Download Unreconciled Transactions {% endif %} """ def reconcile_clearing_account(input_file, output_file): # Load Excel df = pd.read_excel(input_file, engine="openpyxl") # Ensure required columns required_cols = [ "Transaction date", "Invoice/Payment ID", "Customer Name", "Invoice/Payment Description", "Amount Debit", "Amount Credit", "Category", "Created By" ] for col in required_cols: if col not in df.columns: raise ValueError(f"Missing column in input file: {col}") # Replace NaN with 0 df["Amount Debit"] = df["Amount Debit"].fillna(0) df["Amount Credit"] = df["Amount Credit"].fillna(0) # 🔹 Group strictly by Transaction date, Invoice/Payment ID, Customer Name grouped = df.groupby( ["Transaction date", "Invoice/Payment ID", "Customer Name"], as_index=False ).agg({ "Amount Debit": "sum", "Amount Credit": "sum", "Invoice/Payment Description": "first", "Category": "first", "Created By": "first" }) # Calculate balance grouped["Balance"] = grouped["Amount Debit"] - grouped["Amount Credit"] # Flag exceptions def flag_exception(row): if row["Amount Debit"] > 0 and row["Amount Credit"] == 0: return "DR only" elif row["Amount Credit"] > 0 and row["Amount Debit"] == 0: return "CR only" elif row["Balance"] != 0: return "Unreconciled" else: return "OK" grouped["Exception Flag"] = grouped.apply(flag_exception, axis=1) # Filter unreconciled or flagged unreconciled = grouped[grouped["Exception Flag"] != "OK"] # Save results unreconciled.to_excel(output_file, index=False) @app.route("/", methods=["GET", "POST"]) def upload_file(): download_link = None if request.method == "POST": file = request.files["file"] if file: filepath = os.path.join(UPLOAD_FOLDER, file.filename) file.save(filepath) output_file = os.path.join(UPLOAD_FOLDER, "unreconciled_transactions.xlsx") reconcile_clearing_account(filepath, output_file) download_link = "/download/unreconciled_transactions.xlsx" return render_template_string(HTML_TEMPLATE, download_link=download_link) @app.route("/download/