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
Friends, many 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. Don't worry – after doing it two or three times, it becomes a habit.
Ravindra Bagale's Tip – मराठी
मित्रांनो, बऱ्याच students ना वाटतं प्रत्येक monthly statement चा layout सारखाच असतो, आणि एखाद्या महिन्याच्या export मध्ये जास्तीची header row किंवा rename झालेला column आला की combine तुटतं. सुरू करण्याआधी दोन-तीन files शेजारी-शेजारी उघडा आणि फरक लिहून काढा. Cleaning पहिल्या file साठी नाही, सगळ्यात वाईट file साठी plan करा. घाबरू नका, दोन-तीन वेळा केलं की सवय होते.
Ravindra Bagale's Tip – हिंदी
दोस्तों, बहुत से students मान लेते हैं कि हर monthly statement का layout एक जैसा होता है, और किसी महीने के export में extra header row या rename हुआ column आते ही combine टूट जाता है. शुरू करने से पहले दो-तीन files साथ-साथ खोलो और फ़र्क लिख लो. Cleaning पहली file के लिए नहीं, सबसे ख़राब file के हिसाब से plan करो. घबराओ मत, दो-तीन बार करने पर आदत हो जाती है.