Executive Summary

A Python + Selenium solution that converts manual SIP/insurance reminders into an automated, personalized, and zero-cost workflow. It reads raw Excel data, generates client-specific WhatsApp messages with bank, date, and amount details, sends via WhatsApp Web, and writes back status and timestamps to Excel.

Industry Context and Background

Many Chartered Accountants in industry are actively involved in Mutual Fund Distribution and Insurance Consulting, either directly or through associations with large Mutual Fund Distributors. Timely SIP (Systematic Investment Plan) and insurance premium reminders are crucial to prevent failed debits and penalties, and to assist clients in disciplined financial planning.

However, Chartered Accountants often face the challenge of sending these personalized reminders without dedicated software, especially when such software is costly or unavailable. Existing tools often fail to provide customized, client-specific communication free of cost.

Problem Statement

The manual sending of SIP and insurance reminders via WhatsApp or other means is time-consuming, error-prone, and non-scalable. Each client has unique information (bank accounts, investor name, installment amounts), which makes simple bulk messaging ineffective and costly when outsourced.

Why Existing Solutions Fail

• Platforms used by large Mutual Fund Distributors require multiple manual logins for client messaging and reporting.

• Direct platforms offered by BSE/NSE do not provide reminder solutions.

• Broadcast/API tools send generic messages, often charge per message, and are unsuitable for personalized SIP reminders.

Result: No truly automated, free, and personalized solution currently exists for managing SIP reminders tailored to individual clients.

Problem in Simple Words

• Each client differs in banking, SIP Amount and Deduction date details.

• Simple bulk messaging fails to provide personalized communication.

• Manual reminders waste hours of valuable CA time and are not scalable.

AI-Powered Solution Overview

The solution uses Python with Selenium automation to send personalized WhatsApp messages automatically:

• Reads raw SIP data exported from exchange portals as Excel files (Raw Data File).

• Groups multiple SIPs per client to calculate total amounts due.

• Auto-prepares personalized reminder messages with client-specific data (name, date, amount, and bank) and dynamic greeting.

• Sends messages via WhatsApp Web using a persistent Chrome profile (QR once).

• Updates Excel with message status (Sent/Failed) and timestamp for tracking, and colors statuses (green/red).

Why This Tool is Different

• Built-in personalization for each client’s exact SIP details.

• No need for multiple manual logins or duplicate efforts.

• Free of cost with no dependency on third-party platforms or paid APIs.

• Works without proprietary MFD software, making it accessible to all CAs.

• Scalable for large client volumes and customizable as per user needs.

Technologies Used

• Python libraries: pandas, selenium, openpyxl, pyperclip

• Browser automation: Selenium controlling Google Chrome with ChromeDriver

• Data: Excel files for SIP details and contact information

• AI: ChatGPT for code generation and debugging assistance

Implementation Details

• Data Loading Import SIP and contact Excel files, normalize names, and consolidate SIP amounts due per client per date.

• Message Creation: Generate dynamic messages with greetings based on system time, formatted amounts, and client details.

• Due Date Filtering: Send reminders only for SIPs due within two days to avoid premature messaging.

• Status Management: Preserve previously sent/failed statuses and update new timestamps.

• Automation: Launch WhatsApp Web using a persistent Chrome profile; navigate to chats; paste-and-send via clipboard.

• Error & Logging: Log each action; handle missing/invalid contacts gracefully; color-code statuses in Excel.

Controls, Limitations & Risk Mitigation

• Keep Chrome Driver aligned with Chrome version; handle QR login once with persistent profile.

• Verify contact numbers; invalid/WhatsApp-inactive numbers are marked Failed automatically.

Outcomes and Benefits

• Major reduction in manual effort and time.

• Improved accuracy and fewer missed SIP debits.

• Higher client satisfaction through timely, personalized communication.

• Economical solution using free tools and open-source libraries.

Summary and Conclusion

This Python-powered system replaces manual reminder chaos with automated precision, delivering efficiency and client delight without extra cost. It shows how practical AI+automation can elevate advisory workflows.

"From Manual Chaos to Automated Precision – That's the Transformation."

APPENDIX A: SAMPLE RAW DATA (EXCERPT)

RAW SIP LIST DOWNLOADED FROM EXCHANGE PORTAL

APPENDIX B: SAMPLE CONTACT DETAILS (EXCERPT)

CONTACT MASTER (MASKED NUMBERS FOR PRIVACY) – LAST NUMBER NOT ON WHATSAPP

APPENDIX C: SAMPLE OUTPUT EXCEL PREVIEW

REMINDER STATUS SHEET (MASKED CONTACT FOR PRIVACY)

APPENDIX D: BRIEF PYTHON CODE SUMMARY

• Reads SIP and contacts Excel files using pandas

• Cleans and normalizes client names; groups SIPs by client/date

• Creates formatted reminder messages with dynamic greetings

• Uses Selenium + ChromeDriver to automate WhatsApp Web messaging

• Updates Excel with send status and timestamp; applies color formatting with openpyxl

• Robust error handling and logging; configurable paths and parameters

APPENDIX E: PROMPT USED (EXCERPT)

Requirements on Your Laptop

1. Python installed (preferably 3.9 or above)
2. Install necessary Python libraries
3. Open CMD Prompt - pip install pandas selenium openpyxl
4. Google Chrome browser installed
5. Chrome Driver installed (required for Selenium to control Chrome)
7. What is Selenium?
8. Selenium is a Python library (and toolkit) that allows us to control a web browser automatically.
10. Instead of you manually clicking, typing, or pressing buttons on WhatsApp Web, Selenium does it for you.
12. Think of Selenium as a robot that drives your browser.
14. What is Chrome Driver?
15. ChromeDriver is a bridge between Selenium and Google Chrome.