prompts/workflow/code_generation.txt

REVENUE EXCEL REPORT GENERATION TASK

=== YOUR MISSION ===
Create a professional Excel report from arranged_financial_data.json focusing ONLY on revenue data.
Generate a business-ready revenue analysis report with 100% success rate.
You are using gemini-2.5-flash with thinking budget optimization and RestrictedPythonTools for automatic path correction and package management.

=== WHAT TO CREATE ===
• Professional Excel file with revenue-focused worksheets
• Clean, business-ready formatting for executives
• Focus exclusively on revenue analysis and visualization
• File ready for immediate business use

=== MANDATORY EXECUTION SEQUENCE ===

**STEP 1: Environment Setup (30 seconds)**
```python
# RestrictedPythonTools automatically installs packages when needed
# Just use run_python_code() - packages will be auto-installed
import pandas as pd
import openpyxl
print("Packages will be auto-installed by RestrictedPythonTools")
```

**STEP 2: Revenue Data Loading (30 seconds)**
- read_file('arranged_financial_data.json')
- Parse and validate revenue data structure
- Count revenue categories and data points
- Log: "Revenue data loaded: X categories, Y revenue points"

**STEP 3: Revenue Excel Script Creation (3 minutes)**
Create 'generate_revenue_report.py' with this EXACT structure:

```python
#!/usr/bin/env python3
import os
import sys
import json
import pandas as pd
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Border, Side, Alignment
from datetime import datetime
import logging

# Configure logging
logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')
logger = logging.getLogger(__name__)

def main():
    try:
        # Load revenue data
        logger.info('Loading revenue data from arranged_financial_data.json')
        with open('arranged_financial_data.json', 'r', encoding='utf-8') as f:
            revenue_data = json.load(f)
        
        # Create professional workbook
        logger.info('Creating revenue analysis workbook')
        wb = Workbook()
        wb.remove(wb.active)  # Remove default sheet
        
        # Define professional styling
        header_font = Font(bold=True, color='FFFFFF', size=12)
        header_fill = PatternFill(start_color='1F4E79', end_color='1F4E79', fill_type='solid')
        data_font = Font(size=11)
        
        # Process each revenue category
        revenue_categories = ['Company_Overview', 'Total_Revenue', 'Segment_Revenue', 'Regional_Revenue', 'Data_Quality']
        
        for category_name in revenue_categories:
            if category_name in revenue_data:
                logger.info(f'Creating worksheet: {category_name}')
                category_data = revenue_data[category_name]
                ws = wb.create_sheet(title=category_name)
                
                # Add professional headers
                headers = ['Revenue Item', 'Amount', 'Currency/Unit', 'Period', 'Confidence Score']
                for col, header in enumerate(headers, 1):
                    cell = ws.cell(row=1, column=col, value=header)
                    cell.font = header_font
                    cell.fill = header_fill
                    cell.alignment = Alignment(horizontal='center', vertical='center')
                
                # Add revenue data
                data_rows = category_data.get('data', [])
                for row_idx, data_row in enumerate(data_rows, 2):
                    ws.cell(row=row_idx, column=1, value=data_row.get('item', '')).font = data_font
                    ws.cell(row=row_idx, column=2, value=data_row.get('value', '')).font = data_font
                    ws.cell(row=row_idx, column=3, value=data_row.get('unit', '')).font = data_font
                    ws.cell(row=row_idx, column=4, value=data_row.get('period', '')).font = data_font
                    ws.cell(row=row_idx, column=5, value=data_row.get('confidence', '')).font = data_font
                
                # Auto-size columns for professional appearance
                for column in ws.columns:
                    max_length = 0
                    column_letter = column[0].column_letter
                    for cell in column:
                        try:
                            if len(str(cell.value or '')) > max_length:
                                max_length = len(str(cell.value or ''))
                        except:
                            pass
                    adjusted_width = min(max(max_length + 2, 15), 50)
                    ws.column_dimensions[column_letter].width = adjusted_width
                
                # Add borders for professional look
                thin_border = Border(
                    left=Side(style='thin'),
                    right=Side(style='thin'), 
                    top=Side(style='thin'),
                    bottom=Side(style='thin')
                )
                
                for row in ws.iter_rows(min_row=1, max_row=len(data_rows)+1, min_col=1, max_col=5):
                    for cell in row:
                        cell.border = thin_border
        
        # Save with professional filename
        timestamp = datetime.now().strftime('%Y%m%d_%H%M%S')
        filename = f'Revenue_Analysis_Report_{timestamp}.xlsx'
        wb.save(filename)
        logger.info(f'Revenue report saved as: {filename}')
        
        # Verify file creation and quality
        if os.path.exists(filename):
            file_size = os.path.getsize(filename)
            if file_size > 5000:  # Minimum 5KB
                logger.info(f'SUCCESS: Revenue report created successfully')
                logger.info(f'File: {filename} ({file_size:,} bytes)')
                logger.info(f'Worksheets: {len(wb.sheetnames)}')
                print(f'REVENUE_REPORT_SUCCESS: {filename}')
                return filename
            else:
                raise Exception(f'File too small ({file_size} bytes), likely corrupted')
        else:
            raise Exception('Excel file was not created')
            
    except FileNotFoundError as e:
        logger.error(f'Revenue data file not found: {str(e)}')
        sys.exit(1)
    except json.JSONDecodeError as e:
        logger.error(f'Invalid JSON in revenue data: {str(e)}')
        sys.exit(1)
    except Exception as e:
        logger.error(f'Error creating revenue report: {str(e)}')
        import traceback
        logger.error(f'Traceback: {traceback.format_exc()}')
        sys.exit(1)

if __name__ == '__main__':
    result = main()
    print(f'COMPLETED: {result}')
```

**STEP 4: Script Execution with RestrictedPythonTools (2 minutes)**
- Use run_python_code([complete_script]) for direct execution with auto-healing
- OR save_python_file('generate_revenue_report.py', [complete_script]) + run_shell_command('python generate_revenue_report.py')
- RestrictedPythonTools automatically handles path correction and directory constraints
- Automatic package installation and error recovery built-in
- If execution fails, RestrictedPythonTools will attempt automatic recovery

**STEP 5: Excel File Verification (CRITICAL - 30 seconds)**
- list_files() to check if Excel file exists in directory
- If Excel file NOT found in list_files(), retry script execution immediately
- run_shell_command('ls -la *Revenue*.xlsx') for detailed file info
- run_shell_command('du -h *Revenue*.xlsx') to verify file size > 5KB
- NEVER report success without Excel file confirmed in list_files()

=== REVENUE REPORT SPECIFICATIONS ===

**File Structure:**
- Filename: Revenue_Analysis_Report_YYYYMMDD_HHMMSS.xlsx
- 5 worksheets focusing exclusively on revenue data
- Professional corporate formatting throughout

**Worksheet Details:**
1. **Company_Overview** - Company info, document metadata
2. **Total_Revenue** - Consolidated revenue figures and totals
3. **Segment_Revenue** - Revenue by business segment/division
4. **Regional_Revenue** - Revenue by geographic region
5. **Data_Quality** - Confidence scores and data validation

**Professional Formatting:**
- Headers: Bold white text on navy blue background (#1F4E79)
- Data: Clean 11pt font with professional alignment
- Borders: Thin borders around all data cells
- Columns: Auto-sized for optimal readability (15-50 characters)
- Layout: Business-ready presentation format

=== ERROR HANDLING PROCEDURES ===

**Package Installation Issues:**
- Try: pip install --user openpyxl pandas
- Try: python3 -m pip install openpyxl pandas  
- Try: pip install --no-cache-dir openpyxl

**Revenue Data Loading Issues:**
- Verify arranged_financial_data.json exists
- Check JSON syntax and structure
- Ensure revenue categories are present

**Excel Generation Issues:**
- Log exact openpyxl error messages
- Try simplified formatting if complex formatting fails
- Check file write permissions in directory
- Verify Python version compatibility

**File Verification Issues:**
- Check file exists and has reasonable size (>5KB)
- Verify Excel file can be opened without corruption
- Confirm all expected worksheets are present

=== SUCCESS CRITERIA ===
Revenue Excel generation is successful ONLY if:
✓ openpyxl package installed without errors
✓ Revenue data loaded and parsed successfully
✓ Python script executed without errors  
✓ Excel file created with proper filename format
✓ File size > 5KB indicating data was written
✓ All 5 revenue worksheets present and populated
✓ Professional formatting applied consistently
✓ File opens without corruption in Excel

=== PROFESSIONAL FEATURES ===
Your Excel report MUST include:
- **Corporate Design**: Professional navy blue headers with white text
- **Business Layout**: Clean, executive-ready formatting
- **Data Integrity**: All original revenue values preserved exactly
- **User Experience**: Auto-sized columns, proper alignment, clear borders
- **File Management**: Timestamped filename for version control
- **Quality Assurance**: Comprehensive error handling and validation

=== FINAL VALIDATION CHECKLIST ===
Before reporting success, verify:
□ All required packages installed successfully
□ Revenue data JSON loaded and parsed correctly
□ Python script saved and executed without errors
□ Excel file created with timestamped filename
□ File size indicates successful data population (>5KB)
□ All 5 revenue worksheets present and properly named
□ Revenue data populated correctly in each worksheet
□ Professional formatting applied consistently
□ No execution errors or warnings in output
□ File can be opened by Excel applications

Execute now. Focus EXCLUSIVELY on revenue data visualization. Create a professional, publication-ready revenue analysis report for business executives.