📎 This article includes 1 downloadable practice file ↓
Every bank’s net banking lets you download the statement in several formats. Pick the right one and half the cleaning disappears.
Which format?
| Format | Verdict |
|---|---|
| XLS / XLSX | Best if offered. Still check dates and amounts; many banks export them as text. |
| CSV | Good, but don’t double-click it: Excel may flip dd/mm dates. Import it (below). |
| Last resort. Data > Get Data > From File > From PDF works on most digital (not scanned) statements. Password-protected PDFs: open and “Print to PDF” without the password first, on your own statement only. |
Import a CSV safely
Data > From Text/CSV > pick the file > Transform Data. In Power Query, right-click the date column > Change Type > Using Locale > Date, English (India). Close & Load. Dates now stay dd/mm.
First checks, every month
Transactions: =COUNTA(A2:A71)
Total debits: =SUM(C2:C71)
Total credits: =SUM(D2:D71)
Closing: =INDEX(E2:E71, ROWS(E2:E71))
Opening: =E2 + C2 - D2
Proof: Opening + credits - debits = ClosingCommon mistakes
- Opening the CSV by double-click: 04/05/2026 becomes 5 April or stays text, depending on your PC’s settings.
- Header rows on top: banks add account details above the table. Delete them or skip them in Power Query.
Practice
Download the workbook below. The Stmt sheet is already clean; answer the first checks. The statement is made up but follows real Indian bank formats (NEFT, NACH, UPI narrations). The Check column turns green when your formula gives the right answer.
📎 Practice files for this article
- 📗Lesson 1 practice workbook70-transaction statement + 7 check questions.⬇ XLSX · 16 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.