Bank Statement Automation Course: From PDF/CSV to Expense Dashboard in Excel

Every month the same job: download the bank statement, clean it, work out where the money went, tick it against the books. This course automates all of it in Excel: clean the export, read UPI narrations, categorise every transaction with a keyword table, reconcile with your books, combine months with Power Query and finish with an expense dashboard.

Built around a realistic statement: salary by NEFT, home-loan EMI by NACH and 64 UPI payments to Swiggy, BigBasket, Jio, IRCTC, a kirana shop and more. Each lesson has a practice workbook with automatic checks.

Useful for individuals tracking expenses, accountants doing bank reconciliation and small businesses. Works best with Microsoft 365.

1. 1Bank Statement Lesson 1: Get Your Statement Into ExcelWhich format to download (XLS, CSV or PDF), how to open it in Excel without breaking dates and amounts, and the first…
2. 2Bank Statement Lesson 2: Clean Text Dates and AmountsTurn text dates like 04/05/2026 and amounts like "1,23,456.00" into real dates and numbers, and build one signed Amount column from Withdrawal…
3. 3Bank Statement Lesson 3: Categorise Every Transaction With a Keyword TableAuto-categorise bank transactions (Food, Groceries, Bills, Travel, EMI, Salary) with a small Rules table and one SEARCH-based formula you can extend any…
4. 4Bank Statement Lesson 4: Read UPI Narrations (Payee, UPI ID, Reference)Split UPI narrations like UPI/DR/412345678901/SWIGGY/YESB/swiggy@ybl/Payment into reference number, payee name, bank and UPI ID with TEXTSPLIT and TEXTAFTER.
5. 5Bank Statement Lesson 5: Monthly Summary and Cash FlowMonthly income and spending, savings rate, lowest balance, average UPI spend and the biggest debits: a cash-flow summary from your statement with…
6. 6Bank Statement Lesson 6: Bank Reconciliation in ExcelMatch your books with the bank statement using COUNTIFS on amount and date, allow for posting delays, and list unmatched entries for…
7. 7Bank Statement Lesson 7: Combine Monthly Statements With Power QueryDrop each month's CSV into a folder and refresh: Power Query combines, cleans and appends them automatically, with India date locale and…
8. 8Bank Statement Lesson 8: Project – Monthly Expense DashboardFinal project: category totals, top category, most expensive month, food share and savings rate on a one-page expense dashboard with charts.

Want reusable functions for this? See the Excel LAMBDA Library course.