WhatsApp Data Extraction to Excel Automation

Stage 1: Setup Tools and Environment
1. Required Tools:
- For WhatsApp Automation:
- `WhatsApp Business API` (official solution) or `Python` with **Selenium** (to automate WhatsApp Web).
- Simpler Alternative: Use **no-code tools** like **Power Automate** or **Zapier** (for basic tasks).
- For Data Processing:
- `Pandas` library (Python) to handle Excel files.
- Excel Template:
- Design a pre-formatted Excel template with columns like:
`[Customer Name, Amount, Date, Order ID, Responsible Employee]`.

---

Stage 2: Extract Data from WhatsApp*
1. Extraction Method:
- Using Python:
- Launch WhatsApp Web via **Selenium** and manually scan the QR code (once).
- Read messages from specific chats using XPath or UI elements.
- Code Example:
```python
from selenium import webdriver
from selenium.webdriver.common.by import By

driver = webdriver.Chrome()
driver.get("https://web.whatsapp.com")
# Wait for manual QR scan, then locate messages
messages = driver.find_elements(By.CLASS_NAME, "_1Gy50")
last_message = messages[-1].text # Extract the latest message
```

2. Filter Messages:
- Identify messages containing keywords like "Order" or "Customer ID".

---

Stage 3: Data Processing
1. Extract Key Data:
- Use **Regex** to extract:
- Phone numbers: `r'\d{10}'`
- Amounts: `r'\d+ SAR'`
- Dates: `r'\d{4}-\d{2}-\d{2}'`

2. Format Adjustments:
- Standardize dates to `DD/MM/YYYY`.
- Add static symbols (e.g., convert `500` to `500 SAR`).

---

Stage 4: Save Data to Excel
1. Prepared Excel Template:
- Include static columns auto-filled for every order, such as:
- Order ID: Generated via `=ROW()-1` (or programmatically).
- Responsible Employee: Fixed value like "Sales - Riyadh Branch".
- Registration Date: `=TODAY()` (or `datetime.now()` in Python).

2. Add New Data:
- Use *Pandas* to append rows without disrupting formatting:
```python
import pandas as pd

new_data = {
"Customer Name": "Mohammed Ahmed",
"Amount": "500 SAR",
"Date": "05/10/2023",
"Responsible Employee": "Sales - Riyadh Branch" # Static data
}

df = pd.read_excel("Orders.xlsx")
df = df.append(new_data, ignore_index=True)
df.to_excel("Orders.xlsx", index=False)
```

---

Stage 5: Full Automation
1. Task Scheduling:
- Run the script hourly via:
- **Windows**: Task Scheduler.
- **Mac/Linux**: Cron Jobs.

2. Error Handling:
- Use `try-except` in Python to log errors in a text file:
```python
try:
# Extraction and processing code
except Exception as e:
with open("errors.log", "a") as f:
f.write(f"Error: {str(e)}\n")
```

---

Stage 6: System Testing
1. Initial Testing:
- Send test WhatsApp messages and verify:
- Correct entry into Excel.
- Proper formatting compliance.

2. Future Enhancements:
- Add new Excel fields (e.g., Order Status, Notes).
- Integrate with other tools (e.g., Telegram notifications).

--Critical Notes:
- Ensure compliance with WhatsApp’s privacy policy and terms of service.
- For heavy usage, switch to *WhatsApp Business API* to avoid account restrictions.

---

Instructions for the Specialist:
- Follow the workflow above to implement the automation system.
- Test thoroughly and ensure all static data fields (e.g., employee name, branch) are preconfigured.
- Prioritize error logging and data security.