⚡ FBR Iris MIS Excel Cleaner & Formatter

An ultra-fast 1-click suite to clean, format, and automate FBR Iris & Tax MIS "Payment Details" Excel reports.

Features • Quick Start • Excel Macro Setup • Desktop GUI • Offline Web Tool • Python CLI

---

🛑 The Problem with Raw MIS Downloads

When downloading Payment Details MIS reports from FBR Iris or tax portals:

1. Cluttered Unwanted Columns: Columns like Payment Id, Due Date, Payment Source, and Claimed take up screen space and are redundant for reconciliation.
2. "Number Stored as Text": Amounts (e.g. '10,280'), Taxable amounts, and Tax Years are formatted as raw text strings with commas, breaking Excel calculations and sorting.
3. Missing Totals & Dynamic Subtotals: You have to manually add formulas at the bottom every single time. Moreover, standard SUM() fails to update when filtering by Tax Section.
4. Unformatted Layout: No gridlines, unformatted numbers, and narrow columns causing ### overflow.

---

✨ Key Features & Output Comparison

Raw Downloaded File	Cleaned & Formatted Output
❌ 13 cluttered columns	✅ 9 clean columns in exact standard sequence
❌ Numbers stored as text ('10,280')	✅ True numeric format with accounting style (#,##0)
❌ No AutoFilters or gridlines	✅ AutoFilter enabled + thin grid borders + bold headers
❌ No totals or static SUM	✅ Live =SUBTOTAL(9, ...) formulas that update dynamically on filter!
❌ Text clipped / ### display	✅ Auto-fitted column widths with clean padding

---

🚀 4 Ways to Use

Choose the method that best fits your workflow:

┌─────────────────────────────────────────────────────────────────────────┐
│                      MIS EXCEL CLEANER SUITE                            │
├───────────────────┬───────────────────┬────────────────┬────────────────┤
│  ⭐ Excel Macro   │  💻 Desktop GUI   │  🌐 Web Tool   │  ⚡ Python CLI │
│  (Ctrl+Shift+C)   │  (Drag & Drop)    │  (In Browser)  │  (Batch Ops)   │
└───────────────────┴───────────────────┴────────────────┴────────────────┘

---

1. ⭐ Built-in Excel Macro Shortcut (Ctrl + Shift + C)

Clean any MIS file directly inside Microsoft Excel with one keyboard shortcut in 0.05 seconds.

One-Time 30-Second Setup (PERSONAL.XLSB):

1. Open Microsoft Excel.
2. Press Alt + F11 to open the Visual Basic for Applications (VBA) Editor.
3. In the left panel (Project Explorer), click on VBAProject (PERSONAL.XLSB).

   Note: If PERSONAL.XLSB is not visible, click View > Macros > Record Macro, select "Personal Macro Workbook", click OK, and immediately click "Stop Recording".

4. In the top menu bar, click File > Import File... (or press Ctrl + M).
5. Select MIS_Cleaner_Macro.bas.
6. Press Ctrl + S to save, then close the VBA window.
7. In Excel, press Alt + F8, select SetupShortcut, and click Run (this activates Ctrl + Shift + C).

Daily Usage:

1. Open any downloaded MIS Payment Details.xls file in Excel.
2. Press Ctrl + Shift + C on your keyboard.
3. ✨ Done! The entire sheet is cleaned, formatted, and totaled instantly.

---

2. 💻 Modern Desktop GUI App

A standalone desktop interface with Drag & Drop support and batch folder processing.

How to Launch:

• Double-click Run_MIS_Cleaner_GUI.bat or run:

python mis_cleaner_gui.py

Features:

• Drag & Drop single or multiple .xls / .xlsx files.
• Select an entire folder of client downloads to batch clean everything in 1 click.
• Built-in "Open in Excel" button to review results immediately.

---

3. 🌐 100% Offline Browser Tool (cleaner.html)

Zero installation required. Works in Google Chrome, Microsoft Edge, Firefox, and Brave completely offline.

How to Launch:

• Double-click Open_Offline_Web_Cleaner.bat or open cleaner.html in your browser.

Features:

• Drag and drop MIS files into the browser.
• Instant table preview of cleaned data and calculated totals.
• 1-Click download of clean .xlsx files.
• 100% Client-Side: No data is ever uploaded to any server.

---

4. ⚡ Python CLI & Automation Engine

For command-line power users and automated accounting scripts.

Installation:

git clone https://github.com/YOUR_USERNAME/mis-excel-cleaner.git
cd mis-excel-cleaner
pip install -r requirements.txt

Usage:

# Clean a single file:
python clean_mis.py "Payment Details (3).xls"

# Batch clean an entire directory of client files:
python clean_mis.py "C:\Users\Username\Downloads\MIS_Exports"

---

📊 Detailed Column Mapping

The cleaner automatically handles variations in header casing and spaces:

Original MIS Column	Action	Output Position	Output Type
Payment Id	❌ Removed	-	-
CPR No	✅ Kept	Column 1 (A)	Text
Withholding Agent Reg No	✅ Kept	Column 2 (B)	Numeric / Text
Wa Name	✅ Kept	Column 3 (C)	Text
Section	✅ Kept	Column 4 (D)	Text
Tax Month	✅ Kept	Column 5 (E)	Integer
Tax Year	✅ Kept	Column 6 (F)	Integer
Taxable Amount	✅ Kept	Column 7 (G)	Accounting Number (#,##0)
Paid Amount	✅ Kept	Column 8 (H)	Accounting Number (#,##0)
Due Date	❌ Removed	-	-
Payment Date	✅ Kept	Column 9 (I)	Text / Date
Payment Source	❌ Removed	-	-
Claimed	❌ Removed	-	-

---

🔒 Privacy & Security

Tax data contains sensitive taxpayer information (NTN, CNIC, income, and withholding amounts). This project adheres to a strict Offline-First Security Architecture:

• All processing is performed 100% locally on your machine.
• No network requests, external telemetry, or cloud dependencies.
• Compatible with strict corporate and accounting firm data protection standards.

---

🤝 Contributing

Contributions are welcome! Please read CONTRIBUTING.md for details on submitting pull requests, reporting issues, and suggesting enhancements.

---

📜 License

This project is licensed under the MIT License.