Website Open Orders Report Generator

A Python script that automatically fetches open orders data from Yahoo email, processes it, and generates a formatted Excel report with enhanced readability and proper formatting.

Features

• ✅ Automatically fetches order data from Yahoo email
• ✅ Extracts data between separator markers from email body
• ✅ Adds sequential numbering to each order
• ✅ Formats dates to include weekday names (e.g., "Saturday, 2025-12-16")
• ✅ Generates professionally formatted Excel reports
• ✅ Applies consistent styling with headers, borders, and alignment
• ✅ Automatically adjusts column widths to fit content
• ✅ Formats currency values with EGP suffix
• ✅ Creates reports with descriptive filenames
• ✅ Supports HTML email signatures with logo
• ✅ Includes automated email sending functionality

Prerequisites

Before running the script, ensure you have Python 3.x installed on your system and Yahoo email access with App Password enabled.

Required Packages

Install the following Python packages using pip:

pip install pandas openpyxl

Or install from the provided requirements file:

pip install -r Requirements.txt

Installation

1. Clone or download this repository to your local machine
2. Navigate to the project directory
3. Install the required dependencies as mentioned above
4. Set up Yahoo email App Password for programmatic access
5. Configure environment variables for email authentication

Configuration

Environment Variables

Set the following environment variables:

export YAHOO_EMAIL="[YOUR_YAHOO_EMAIL]"
export YAHOO_APP_PASSWORD="[YOUR_APP_PASSWORD]"

Script Configuration

The script contains configurable options at the top:

• YAHOO_EMAIL: Your Yahoo email address
• YAHOO_APP_PASSWORD: Your Yahoo App Password
• OUTPUT_PATH: Directory where reports will be saved
• Various email fetching and processing options

Usage

1. Ensure your Yahoo email contains order data with the format:
   • Subject: "Website Open Orders"
   • Sender: "[ORDER_DATA_SENDER]"
   • Data between "Open Orders Data Start" and "Open Orders Data End" markers
2. Run the script:

   python WebsiteOpenOrders.py

3. Find the generated report in the Reports directory with the filename format: Website Open Orders _ DD-MM-YYYY.xlsx

Input Data Format

The script expects order data in the following CSV format within an email:

No.,Order ID,Created At,Amount
1,[ORDER_ID],[DATE_TIME],[AMOUNT]
2,[ORDER_ID],[DATE_TIME],[AMOUNT]

Output

The script generates a professionally formatted Excel file with:

• Sequential numbering column (#)
• Order number
• Date with weekday (e.g., "Saturday, 2025-12-16")
• Time of order creation
• Amount with EGP currency formatting

The Excel file includes:

• Header styling with green background
• Proper alignment (center for most fields, right-aligned for amounts)
• Borders (thick for headers and outer edges, dotted for inner cells)
• Automatic column width adjustment
• Proper date and time formatting
• Currency formatting for amounts
• Professional table formatting with alternating row colors
• Auto-filter functionality on all columns

Email Integration

The script integrates with Yahoo email to:

• Fetch emails with subject "Website Open Orders"
• Extract CSV data between separator markers
• Process only the most recent matching email
• Clean HTML tags from email content

Email Sending (Optional)

The script can be configured to send generated reports via email with:

• HTML formatted body with signature
• Embedded logo image in signature
• Customizable recipient list
• Professional signature with icons

Troubleshooting

Common Issues:

1. Authentication error: Ensure Yahoo App Password is correctly configured
2. Email not found: Verify email subject and sender match expected patterns
3. Permission error: Check that you have write permissions to the Reports directory
4. Package import error: Verify that all dependencies are properly installed

Dependencies:

If you encounter issues with dependencies, try:

pip install --upgrade pandas openpyxl

File Structure

WebsiteOpenOrders/
├── WebsiteOpenOrders.py          # Main script with email integration
├── Requirements.txt              # Python dependencies
├── Reports/                      # Output directory for generated reports
│   └── Website Open Orders _ DD-MM-YYYY.xlsx
├── SignatureLogo.png            # Optional logo for email signature
└── README.md                   # This file

Customization

The script can be customized by modifying:

• Email fetching criteria (sender, subject, date range)
• Data extraction patterns (separator markers)
• Output formatting and styling options
• Email sending configuration (recipients, message template)
• Report styling elements (fonts, colors, borders)

Security

• Store Yahoo App Password securely as environment variables
• Do not commit credentials to version control
• Review email content before processing

Files Requiring Personalization

The following files contain placeholder values that need to be replaced with your actual information:

1. WebsiteOpenOrders.py:

   • Email addresses and domain names
   • Personal information in email signatures
   • Recipient email addresses for report distribution

2. WebSiteOpenOrdersGetDataWorkFlow.flow:

   • Recipient email address in the workflow configuration
   • Any domain-specific configurations

3. EduFunWebsiteOpenOrdersAgent.sh:

   • Script name and any hardcoded paths

4. Data_Needed.md:

   • All email addresses and domain names
   • Personal contact information

5. README.md:

   • All placeholder values marked with brackets

Always ensure to replace placeholder values with your actual information before deploying to production environments.

License

This project is open source and available under the MIT License (see LICENSE file for details).