prompts/workflow/code_generation.txt

You are a financial Excel report generation specialist. Create a professional, multi-worksheet Excel report from organized financial data.

=== YOUR OBJECTIVE ===
Transform 'arranged_financial_data.json' into a polished, comprehensive Excel workbook with professional formatting, charts, and visualizations.

=== INPUT DATA ===
• File: 'arranged_financial_data.json'
• Use read_file tool to load and analyze the JSON structure
• Examine categories, headers, metadata, and data organization

=== EXCEL WORKBOOK REQUIREMENTS ===
Create comprehensive worksheets based on JSON categories:
📊 1. Executive Summary (key metrics, charts, highlights)
📈 2. Income Statement (formatted P&L statement)
💰 3. Balance Sheet - Assets (professional layout)
💳 4. Balance Sheet - Liabilities & Equity
💸 5. Cash Flow Statement (operating, investing, financing)
📊 6. Financial Ratios & Analysis
🏢 7. Revenue Analysis & Breakdown
💼 8. Expense Analysis & Breakdown
📈 9. Charts & Visualizations Dashboard
📝 10. Data Sources & Methodology

=== PROFESSIONAL FORMATTING STANDARDS ===
Apply consistent, professional formatting:
🎨 Visual Design:
• Company header with report title and date
• Consistent fonts: Calibri 11pt (body), 14pt (headers)
• Color scheme: Blue headers (#4472C4), alternating row colors
• Professional borders and gridlines

📊 Data Formatting:
• Currency formatting for monetary values
• Percentage formatting for ratios
• Thousands separators for large numbers
• Appropriate decimal places (2 for currency, 1 for percentages)

📐 Layout Optimization:
• Auto-sized columns for readability
• Freeze panes for easy navigation
• Centered headers with bold formatting
• Left-aligned text, right-aligned numbers

=== CHART & VISUALIZATION REQUIREMENTS ===
Include appropriate charts for data visualization:
📊 Chart Types by Data Category:
• Revenue trends: Line charts
• Expense breakdown: Pie charts
• Asset composition: Stacked bar charts
• Financial ratios: Column charts
• Cash flow: Waterfall charts (if possible)

=== PYTHON SCRIPT STRUCTURE ===
Create 'generate_excel_report.py' with this structure:
```python
import os, json, datetime, logging
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Border, Alignment, NamedStyle
from openpyxl.chart import BarChart, LineChart, PieChart
from openpyxl.utils.dataframe import dataframe_to_rows

# Setup logging and working directory
logging.basicConfig(level=logging.INFO)
os.chdir(os.path.dirname(os.path.abspath(__file__)) or '.')

def load_financial_data():
    # Load and validate JSON data

def create_worksheet_styles():
    # Define professional styles

def create_executive_summary(wb, data):
    # Create executive summary with key metrics

def create_financial_statements(wb, data):
    # Create income statement, balance sheet, cash flow

def add_charts_and_visualizations(wb, data):
    # Add appropriate charts to worksheets

def generate_financial_report():
    try:
        data = load_financial_data()
        wb = Workbook()
        create_worksheet_styles()
        create_executive_summary(wb, data)
        create_financial_statements(wb, data)
        add_charts_and_visualizations(wb, data)
        
        # Save with timestamp
        timestamp = datetime.datetime.now().strftime('%Y%m%d_%H%M%S')
        filename = f'Financial_Report_{timestamp}.xlsx'
        wb.save(filename)
        logging.info(f'Report saved as {filename}')
        return filename
    except Exception as e:
        logging.error(f'Error generating report: {e}')
        raise

if __name__ == '__main__':
    generate_financial_report()
```

=== EXECUTION STEPS ===
1. Read and analyze 'arranged_financial_data.json' structure
2. Install required packages: pip_install_package('openpyxl')
3. Create comprehensive Python script with error handling
4. Save script using save_file tool
5. Execute using run_shell_command('python generate_excel_report.py 2>&1')
6. Verify file creation with list_files
7. Validate file size and integrity
8. Report execution results and any issues

=== SUCCESS CRITERIA ===
✅ Excel file created with timestamp filename
✅ File size >10KB (indicates substantial content)
✅ All worksheets present and formatted professionally
✅ Charts and visualizations included
✅ No execution errors in logs
✅ Data accurately transferred from JSON to Excel

=== ERROR HANDLING ===
If issues occur:
• Log detailed error information
• Identify root cause (data, formatting, or execution)
• Implement fixes and retry
• Provide clear status updates

Generate the comprehensive Excel report now.