9. Get Data from Folder: Monthly Bank Statements Walkthrough
9.1 The Files and Their Layout
Folder: C:\Finance\BankStatements\ containing Jan.xlsx, Feb.xlsx, Mar.xlsx (sheet name Statement in each).
Each file looks like this (Jan.xlsx, messy export):
| A | B | C | D | E | F |
|---|---|---|---|---|---|
| Example Bank – Statement of Account | |||||
| Account Holder: Ravindra Bagale | |||||
| Account No: XXXXXX4321 · Branch: Kothrud, Pune | |||||
| Period: 01-01-2025 to 31-01-2025 | |||||
| Date | Narration | Ref No | Debit | Credit | Balance |
| Opening Balance | 52,600.00 | ||||
| 01-01-2025 | UPI/DR/Blinkit/Groceries/YESB | UPI500123 | 486.00 | 52,114.00 | |
| 02-01-2025 | NEFT/CR/SALARY JAN/EXAMPLE TECH PVT LTD | NEFT00987 | 65,000.00 | 1,17,114.00 | |
| 05-01-2025 | IMPS/DR/Shraddha Bagale/Rent share | IMPS4455 | 8,000.00 | 1,09,114.00 | |
| 07-01-2025 | ATM WDL/Kothrud Pune | ATM7788 | 2,000.00 | 1,07,114.00 | |
| 10-01-2025 | UPI/DR/Amazon Now/Fruits/HDFC | UPI500456 | 312.50 | 1,06,801.50 | |
| 12-01-2025 | UPI/CR/Salman/Trip split | UPI500789 | 1,500.00 | 1,08,301.50 | |
| Closing Balance | 1,08,301.50 | ||||
| *** End of Statement *** |
Quirks to fix: 4 title rows; an Opening Balance and a Closing Balance line; an end marker; dates in dd-mm-yyyy; amounts as text with Indian commas (1,17,114.00); Debit and Credit in separate columns with blanks.
The goal – clean combined table:
| Month | Date | Narration | Ref No | Debit | Credit | Net Amount | Mode |
|---|---|---|---|---|---|---|---|
| Jan | 01-01-2025 | UPI/DR/Blinkit/Groceries/YESB | UPI500123 | 486.00 | 0 | −486.00 | UPI |
| Jan | 02-01-2025 | NEFT/CR/SALARY JAN/… | NEFT00987 | 0 | 65,000.00 | 65,000.00 | NEFT |
| Jan | 05-01-2025 | IMPS/DR/Shraddha Bagale/Rent share | IMPS4455 | 8,000.00 | 0 | −8,000.00 | IMPS |
| Jan | 07-01-2025 | ATM WDL/Kothrud Pune | ATM7788 | 2,000.00 | 0 | −2,000.00 | ATM |
| Feb | … | … | … | … | … | … | … |
Ravindra Bagale's Tip
Mitrano, khup students assume every monthly statement has the same layout, and the combine breaks when one month's export has an extra header row or a renamed column. Open two or three files side by side before you start and write down the differences. Plan your cleaning for the worst file, not the first one. Ghabru naka, don-teen vela kela ki savay hote.