Power BI · मराठी आवृत्ती
9.1 Files आणि त्यांची layout
Practice folder C:\Finance\BankStatements\ मध्ये Jan.xlsx, Feb.xlsx, Mar.xlsx ठेवा. प्रत्येक file मध्ये Statement नावाची sheet आहे.
Jan.xlsx: आधीचा 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 *** |
चार title rows, Opening / Closing Balance, end marker, dd-mm-yyyy dates, भारतीय commas असलेले amounts आणि वेगवेगळे Debit / Credit columns 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 | … | … | … | … | … | … | … |
Net Amount = Credit − Debit. खर्च negative आणि जमा positive दिसेल. Opening / Closing balance हे transactions नसल्यामुळे वेगळे ठेवा; reconciliation साठी त्यांची नोंद उपयोगी आहे.