Problem Statement

Bank statements often come with long, messy narration that are difficult to categorize. These narration entries typically contain important transaction details, but they’re buried in inconsistent formats—especially in digital transaction types like UPI, IMPS, NEFT, or transfers.

Manually extracting meaningful information from these narration fields is:

1. Time-consuming and repetitive.
2. Error-prone due to inconsistencies and hidden characters like line breaks.
3. Difficult to standardize, especially when team members interpret narrations differently.
4. Not scalable as data volumes grow.

There was also no structured way to maintain or update commonly used remarks for transactions, leading to confusion and redundancy in reporting or analysis.

💡 Solution Overview

To address this challenge, we built a VBA-powered smart Excel tool that uses automation logic and minimal AI assistance to streamline the workflow from raw narration data to clean, categorized insights.

VBA codes generated with the help of AI for

1. Extracting the values from the Narration
2. Auto generate the final remarks to categorise the transaction. Like, Income , expense, Drawings, Unsecured Loan.
3. Generate list of unique remark
4. Replace the old remarks with the new remarks.

The tool includes the following key functionalities:

1. Narration Cleaning & Extraction
2. Automatically reads narration strings from the dataset (Column C).
3. Extracts key identifiers (like UPI IDs) based on structured patterns (e.g., extracting the value after the 5th slash).  (See Clean description Column in image2:)
4. Removes hidden characters like line breaks or extra spaces to ensure clean, one-line outputs.

Image 1:   Sample Bank Statement:

Image 2: Bank Statement after processing

1. Auto-Generated Remarks
2. Based on the extracted value, the tool auto-generates a placeholder remark such as:
3. "Remark for <Extracted Value>". (See Final remark Column  in image2:)
4. This reduces manual typing while maintaining consistent phrasing across the sheet.
5. User-Controlled Remark Mapping
6. A separate sheet allows users to define custom mappings:
7. Column A = old remarks
8. Column B = updated, user-defined remarks.
9. The macro checks for matches and replaces remarks accordingly.
10. This gives users centralized control without manual search-and-replace tasks.
11. Unique Remark Summary
12. The tool can also generate a list of unique remarks, useful for tagging, review, or reporting.
13. This can be displayed in a new column or a dedicated sheet, depending on user preference.
14. Generate a summary :

Based on the final remark sheet you can create a final summary, which give you overview of all transactions of Bank Statement in the Summary Format.

Use of AI/Automation

While the core tool is built using traditional Excel VBA, AI was used strategically during development to enhance design, logic, and productivity:

1. Prompting Guidance:
2. Instead of writing full code manually, we used prompt-based collaboration (like ChatGPT) to guide macro logic:
3. "How can I extract the value after the 5th slash in a string in Excel?" or
4. "What’s the best way to clean line breaks in Excel cells via VBA?"
5. Iterative Design Support:
6. Prompts were used to refine logic, add flexibility (e.g., toggling output location), and anticipate edge cases.

Note: The tool's intelligence lies not just in the macros themselves, but in how effectively prompts were crafted to get the right responses and modular solutions. No AI-generated code was used directly without proper testing and adjustment.

Benefits

Guidance: Effective Prompting for Excel Automation

To replicate or extend such tools using AI assistance (like ChatGPT), here’s a simplified prompting approach that worked well:

1. Be specific:
2. ❌ "Clean data"
3. ✅ "How to remove hidden line breaks and trim spaces in Excel cells via VBA?"
4. Use context:
5. ❌ "Extract from text"
6. ✅ "Extract the value after the 5th '/' in a narration string in Excel Column C"
7. Iterate and adapt:
8. Start with one goal (e.g., extract data).
9. Then build on it (e.g., auto-generate remarks, manage user inputs).

Conclusion:

AI has made coding more accessible to non-developers like Chartered Accountants, empowering us to automate complex Excel tasks without in-depth programming expertise — simply by asking the right questions.

Leveraging this capability, we developed a tool that transforms unstructured bank narration data into clean, structured, and actionable insights. The result is a significant improvement in both efficiency and data quality, with far less manual effort.

The solution not only reduces repetitive tasks but also empowers users to maintain and customize remarks dynamically. It lays the foundation for further enhancements like reporting automation, dashboard integration, and even AI-powered categorization models in the future.