# 9.1 Files आणि त्यांची layout

Source: https://ravindrabagale.com/mr/powerbi/ch09-get-data-from-folder-monthly-bank-statements/9-1-the-files-and-their-layout.html
Language: mr (Marathi with English technical terms)

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 साठी त्यांची नोंद उपयोगी आहे.

रवींद्र बागले यांची tip

पहिल्या file वरून सगळ्या files सारख्याच आहेत असं समजू नका. दोन-तीन files उघडून extra headers किंवा बदललेली नावं नोंदवा. सर्वांत अवघड layout लक्षात घेऊन cleaning करा.