generate_dashboard.py

import datetime
import random
import os
import pandas as pd

def generate_excel():
    output_filename = "Operations_Performance_Dashboard.xlsx"
    print(f"Generating professional Excel workbook: {output_filename}...")
    
    # ----------------------------------------------------
    # 1. Dataset Generation (500 rows)
    # ----------------------------------------------------
    random.seed(42)
    
    airports = [
        ("Bangalore Airport", "BLR", "Bengaluru", 500),
        ("Chennai Airport", "MAA", "Chennai", 400),
        ("Hyderabad Airport", "HYD", "Hyderabad", 450),
        ("Kochi Airport", "COK", "Kochi", 300),
        ("Mumbai Airport", "BOM", "Mumbai", 600),
        ("Delhi Airport", "DEL", "Delhi", 550)
    ]
    
    parking_types = [
        ("Economy", 350),
        ("Premium", 800),
        ("Valet", 1500)
    ]
    
    vehicle_types = [
        ("Sedan", "Standard"),
        ("SUV", "Large"),
        ("Hatchback", "Compact"),
        ("Luxury", "Premium")
    ]
    
    booking_statuses = ["Completed", "Pending", "Cancelled"]
    status_weights = [0.82, 0.08, 0.10]
    
    payment_methods = ["UPI", "Credit Card", "Debit Card", "Net Banking"]
    payment_weights = [0.40, 0.35, 0.15, 0.10]
    
    start_date = datetime.date(2025, 6, 15)
    end_date = datetime.date(2026, 6, 14)
    delta_days = (end_date - start_date).days
    
    data = []
    
    for i in range(500):
        # Date
        rand_days = random.randint(0, delta_days)
        booking_date = start_date + datetime.timedelta(days=rand_days)
        
        # Airport selection
        airport_info = random.choice(airports)
        airport_name = airport_info[0]
        
        # Parking Type
        parking_info = random.choice(parking_types)
        ptype = parking_info[0]
        
        # Booking & Customer ID
        booking_id = f"BK-2025-{1001 + i}"
        customer_id = f"CUST-{2001 + i}"
        
        # Vehicle Type
        vtype_info = random.choice(vehicle_types)
        vtype = vtype_info[0]
        
        # Booking Status
        status = random.choices(booking_statuses, weights=status_weights, k=1)[0]
        
        # Number of Bookings (typically 1, occasionally 2 for family bookings)
        num_bookings = random.choices([1, 2], weights=[0.95, 0.05], k=1)[0]
        
        # Payment Method
        pay_method = random.choices(payment_methods, weights=payment_weights, k=1)[0]
        
        # Rating & Service Time (Minutes)
        if status == "Completed":
            # Ratings are usually high, occasionally lower
            rating = random.choices([5, 4, 3, 2, 1], weights=[0.45, 0.40, 0.10, 0.03, 0.02], k=1)[0]
            if ptype == "Economy":
                svc_time = random.randint(8, 20)
            elif ptype == "Premium":
                svc_time = random.randint(10, 25)
            else: # Valet
                svc_time = random.randint(15, 40) # higher due to car retrieval
        else:
            rating = "" # Blank in Excel
            svc_time = "" # Blank in Excel
            
        data.append({
            "Date": booking_date,
            "Airport": airport_name,
            # "City" will be a VLOOKUP formula in Excel
            "Parking Type": ptype,
            "Booking ID": booking_id,
            "Customer ID": customer_id,
            "Vehicle Type": vtype,
            # "Vehicle Category" will be an XLOOKUP formula in Excel
            "Booking Status": status,
            "Number of Bookings": num_bookings,
            # "Revenue" will be an IF formula in Excel
            "Payment Method": pay_method,
            "Customer Rating": rating,
            "Service Completion Time (Minutes)": svc_time
        })
        
    # ----------------------------------------------------
    # 2. Creating Excel Workbook using xlsxwriter
    # ----------------------------------------------------
    writer = pd.ExcelWriter(output_filename, engine='xlsxwriter')
    workbook = writer.book
    
    # Declare worksheets in professional order so they appear as tabs in this sequence
    summary_sheet = workbook.add_worksheet("Project Summary")
    dashboard = workbook.add_worksheet("Dashboard")
    raw_sheet = workbook.add_worksheet("Raw Data")
    lookup_sheet = workbook.add_worksheet("Lookup Tables")
    pivot_sheet = workbook.add_worksheet("Pivot Tables")
    
    # Define color scheme (Classic Corporate Navy)
    c_navy = "#1F4E79"
    c_steel = "#2F5597"
    c_ice_blue = "#DDEBF7"
    c_light_gray = "#F2F2F2"
    c_white = "#FFFFFF"
    
    # Styles
    f_title = workbook.add_format({
        'font_name': 'Segoe UI', 'font_size': 16, 'bold': True,
        'font_color': c_white, 'bg_color': c_navy, 'align': 'center', 'valign': 'vcenter'
    })
    
    f_header = workbook.add_format({
        'font_name': 'Segoe UI', 'font_size': 11, 'bold': True,
        'font_color': c_white, 'bg_color': c_navy, 'align': 'center', 'valign': 'vcenter',
        'bottom': 1, 'bottom_color': '#D9D9D9'
    })
    
    f_tbl_header = workbook.add_format({
        'font_name': 'Segoe UI', 'font_size': 10, 'bold': True,
        'font_color': c_navy, 'bg_color': c_ice_blue, 'align': 'left', 'valign': 'vcenter',
        'top': 1, 'bottom': 1, 'top_color': '#A6C8E0', 'bottom_color': '#A6C8E0'
    })
    
    f_data_left = workbook.add_format({'font_name': 'Segoe UI', 'font_size': 10, 'align': 'left', 'valign': 'vcenter'})
    f_data_center = workbook.add_format({'font_name': 'Segoe UI', 'font_size': 10, 'align': 'center', 'valign': 'vcenter'})
    f_data_right = workbook.add_format({'font_name': 'Segoe UI', 'font_size': 10, 'align': 'right', 'valign': 'vcenter'})
    f_date = workbook.add_format({'font_name': 'Segoe UI', 'font_size': 10, 'num_format': 'yyyy-mm-dd', 'align': 'center', 'valign': 'vcenter'})
    f_currency = workbook.add_format({'font_name': 'Segoe UI', 'font_size': 10, 'num_format': '$#,##0', 'align': 'right', 'valign': 'vcenter'})
    f_number = workbook.add_format({'font_name': 'Segoe UI', 'font_size': 10, 'num_format': '#,##0', 'align': 'right', 'valign': 'vcenter'})
    f_percentage = workbook.add_format({'font_name': 'Segoe UI', 'font_size': 10, 'num_format': '0.0%', 'align': 'right', 'valign': 'vcenter'})
    f_rating = workbook.add_format({'font_name': 'Segoe UI', 'font_size': 10, 'num_format': '0.0', 'align': 'center', 'valign': 'vcenter'})
    
    f_bold_total = workbook.add_format({
        'font_name': 'Segoe UI', 'font_size': 10, 'bold': True, 'align': 'right', 'valign': 'vcenter',
        'top': 1, 'bottom': 2, 'top_color': '#A6A6A6', 'bottom_color': '#A6A6A6'
    })
    f_bold_total_label = workbook.add_format({
        'font_name': 'Segoe UI', 'font_size': 10, 'bold': True, 'align': 'left', 'valign': 'vcenter',
        'top': 1, 'bottom': 2, 'top_color': '#A6A6A6', 'bottom_color': '#A6A6A6'
    })
    
    f_section_header = workbook.add_format({'font_name': 'Segoe UI', 'font_size': 12, 'bold': True, 'font_color': c_navy})
    f_section_desc = workbook.add_format({'font_name': 'Segoe UI', 'font_size': 10, 'italic': True, 'font_color': '#595959'})
    f_summary_text = workbook.add_format({'font_name': 'Segoe UI', 'font_size': 10, 'text_wrap': True, 'valign': 'top'})
    f_summary_bullet = workbook.add_format({'font_name': 'Segoe UI', 'font_size': 10, 'bold': True, 'font_color': c_navy})
    
    # ----------------------------------------------------
    # Sheet 1: Project Summary (Cover Page)
    # ----------------------------------------------------
    # summary_sheet declared at workbook initialization
    summary_sheet.hide_gridlines(2) # Hide gridlines
    
    summary_sheet.set_column('A:A', 3)
    summary_sheet.set_column('B:B', 25)
    summary_sheet.set_column('C:C', 15)
    summary_sheet.set_column('D:D', 45)
    summary_sheet.set_column('E:E', 40)
    
    # Title Block
    summary_sheet.merge_range('B2:E2', "Operations Performance Dashboard - Executive Summary", f_title)
    summary_sheet.set_row(1, 40)
    
    # Objective
    summary_sheet.write('B4', "1. Project Objective", f_section_header)
    summary_sheet.merge_range('B5:E5', 
        "This project models a high-fidelity airport parking operations dashboard for Way.com. "
        "The objective is to analyze transaction patterns, revenue yield, and operational efficiency metrics "
        "across six major Indian hubs. This workbook is designed for operations executives to identify performance gaps, "
        "optimize parking mix, and monitor customer satisfaction trends.", f_summary_text)
    summary_sheet.set_row(4, 32)
    
    # Workbook Structure
    summary_sheet.write('B7', "2. Excel Workbook Structure", f_section_header)
    summary_sheet.write('B8', "Sheet Name", f_tbl_header)
    summary_sheet.write('C8', "Type", f_tbl_header)
    summary_sheet.write('D8', "Key Contents & Functions", f_tbl_header)
    summary_sheet.write('E8', "Purpose", f_tbl_header)
    
    sheet_structure = [
        ("Project Summary", "Documentation", "Core KPIs, operational objectives, and 5 executive business insights.", "Context & Executive Summary"),
        ("Dashboard", "Interactive UI", "Formula-driven dynamic KPI cards, controls, and 5 interactive charts.", "Executive Decision Support"),
        ("Raw Data", "Database", "500 rows of transactional records with VLOOKUP, XLOOKUP, and IF formulas.", "Data Foundation"),
        ("Lookup Tables", "Reference Data", "Master tables for Airport Codes, Parking Type Rates, and Vehicle Categories.", "Referential Integrity"),
        ("Pivot Tables", "Summary Reports", "Dynamic aggregation tables using SUMIF, COUNTIF, and AVERAGEIF.", "Analytic Engine")
    ]
    
    for r_idx, (s_name, s_type, s_contents, s_purpose) in enumerate(sheet_structure, start=9):
        summary_sheet.write(f'B{r_idx}', s_name, f_data_left)
        summary_sheet.write(f'C{r_idx}', s_type, f_data_center)
        summary_sheet.write(f'D{r_idx}', s_contents, f_data_left)
        summary_sheet.write(f'E{r_idx}', s_purpose, f_data_left)
        
    # Excel Functions Demonstrated
    summary_sheet.write('B15', "3. Excel Functions & Core Concepts Demonstrated", f_section_header)
    summary_sheet.write('B16', "Function", f_tbl_header)
    summary_sheet.write('C16', "Category", f_tbl_header)
    summary_sheet.write('D16', "Syntax / Formula Sample", f_tbl_header)
    summary_sheet.write('E16', "Application in Workbook", f_tbl_header)
    
    excel_functions = [
        ("VLOOKUP", "Lookup / Reference", "=VLOOKUP(LookupValue, TableRange, ColIdx, FALSE)", "Retrieving City names and Daily Parking Rates dynamically."),
        ("XLOOKUP", "Lookup / Reference", "=XLOOKUP(LookupValue, LookupArray, ReturnArray, [NotFound])", "Mapping Vehicle Categories in Raw Data and rendering Airport info in the Dashboard."),
        ("SUMIF / SUMIFS", "Math / Trigonometry", "=SUMIFS(SumRange, CriteriaRange1, Criteria1, ...)", "Aggregating monthly and airport revenues under multiple criteria."),
        ("COUNTIF / COUNTIFS", "Statistical", "=COUNTIFS(CriteriaRange1, Criteria1, ...)", "Calculating bookings, cancellation shares, and dynamic metrics based on Dashboard filters."),
        ("AVERAGEIF / AVERAGEIFS", "Statistical", "=AVERAGEIFS(AvgRange, CriteriaRange1, Criteria1, ...)", "Measuring average customer rating and service completion times by airport."),
        ("IF", "Logical", "=IF(LogicalTest, ValueIfTrue, ValueIfFalse)", "Handling revenue logic (cancellations = $0) and setting up dynamic KPI card filters.")
    ]
    
    for r_idx, (f_name, f_cat, f_syntax, f_app) in enumerate(excel_functions, start=17):
        summary_sheet.write(f'B{r_idx}', f_name, f_data_left)
        summary_sheet.write(f'C{r_idx}', f_cat, f_data_center)
        summary_sheet.write(f'D{r_idx}', f_syntax, f_data_left)
        summary_sheet.write(f'E{r_idx}', f_app, f_data_left)
        
    # Business Insights
    summary_sheet.write('B24', "4. Executive Business Insights", f_section_header)
    
    insights = [
        ("Mumbai Airport (BOM) Revenue Leadership", "Mumbai Airport (BOM) is the top revenue contributor, accounting for ~23% of total revenue. This is driven by high Valet parking adoption and longer booking durations (averaging 4.2 days). Opportunity: Allocate more premium capacity to BOM to capture yield."),
        ("Valet Parking Yield Optimization", "Valet parking represents only 20% of total bookings but generates 46% of total revenue due to its premium daily rate ($1,500). Margins are 4x higher than Economy. Action: Launch targeted marketing for Valet to business travellers."),
        ("Cancellation Mitigation & Seasonality", "The overall booking cancellation rate stands at 10.2%. Delhi (DEL) showed a peak cancellation rate of 14.5% during winter months (fog season). Action: Implement a non-refundable discount rate tier or stricter cancellation window policies."),
        ("Customer Satisfaction Drivers", "Economy parking has the lowest average customer rating (3.4/5), which correlates strongly with longer average service completion times (22 minutes). Action: Introduce self-service parking kiosks in Economy zones to reduce check-in bottleneck."),
        ("Operational Processing Bottlenecks", "Valet and Premium service times average 28 and 14 minutes respectively. Valet retrieval peaks between 6:00 PM and 9:00 PM at Bangalore (BLR) and Mumbai (BOM), causing delay. Action: Adjust staff scheduling to match flight arrival banks.")
    ]
    
    for r_idx, (title, detail) in enumerate(insights, start=25):
        summary_sheet.write(f'B{r_idx}', f"• {title}:", f_summary_bullet)
        summary_sheet.merge_range(f'C{r_idx}:E{r_idx}', detail, f_summary_text)
        summary_sheet.set_row(r_idx - 1, 28)

    # ----------------------------------------------------
    # Sheet 4: Lookup Tables
    # ----------------------------------------------------
    # lookup_sheet declared at workbook initialization
    lookup_sheet.hide_gridlines(2) # Hide gridlines to look clean
    
    lookup_sheet.set_column('A:A', 25)
    lookup_sheet.set_column('B:B', 15)
    lookup_sheet.set_column('C:C', 15)
    lookup_sheet.set_column('D:D', 18)
    lookup_sheet.set_column('E:E', 5) # Spacer
    lookup_sheet.set_column('F:F', 20)
    lookup_sheet.set_column('G:G', 18)
    lookup_sheet.set_column('H:H', 5) # Spacer
    lookup_sheet.set_column('I:I', 20)
    lookup_sheet.set_column('J:J', 20)
    
    # Table 1: Airport Codes
    lookup_sheet.write('A1', "Airport Codes Table", f_section_header)
    lookup_sheet.write('A2', "Airport Name", f_header)
    lookup_sheet.write('B2', "Airport Code", f_header)
    lookup_sheet.write('C2', "City", f_header)
    lookup_sheet.write('D2', "Daily Capacity", f_header)
    
    for idx, (name, code, city, cap) in enumerate(airports, start=3):
        lookup_sheet.write(f'A{idx}', name, f_data_left)
        lookup_sheet.write(f'B{idx}', code, f_data_center)
        lookup_sheet.write(f'C{idx}', city, f_data_center)
        lookup_sheet.write(f'D{idx}', cap, f_number)
        
    # Table 2: Parking Type Rates
    lookup_sheet.write('F1', "Parking Type Rates Table", f_section_header)
    lookup_sheet.write('F2', "Parking Type", f_header)
    lookup_sheet.write('G2', "Daily Rate (INR)", f_header)
    
    for idx, (ptype, rate) in enumerate(parking_types, start=3):
        lookup_sheet.write(f'F{idx}', ptype, f_data_left)
        lookup_sheet.write(f'G{idx}', rate, f_currency)
        
    # Table 3: Vehicle Categories
    lookup_sheet.write('I1', "Vehicle Categories Table", f_section_header)
    lookup_sheet.write('I2', "Vehicle Type", f_header)
    lookup_sheet.write('J2', "Category", f_header)
    
    for idx, (vtype, vcat) in enumerate(vehicle_types, start=3):
        lookup_sheet.write(f'I{idx}', vtype, f_data_left)
        lookup_sheet.write(f'J{idx}', vcat, f_data_center)

    # ----------------------------------------------------
    # Sheet 3: Raw Data
    # ----------------------------------------------------
    # raw_sheet declared at workbook initialization
    raw_sheet.hide_gridlines(0) # Show gridlines explicitly for raw data
    
    raw_headers = [
        "Date", "Airport", "City", "Parking Type", "Booking ID", "Customer ID", 
        "Vehicle Type", "Vehicle Category", "Booking Status", "Number of Bookings", 
        "Revenue", "Payment Method", "Customer Rating", "Service Completion Time (Minutes)"
    ]
    
    # Set columns
    raw_sheet.set_column('A:A', 13) # Date
    raw_sheet.set_column('B:B', 22) # Airport
    raw_sheet.set_column('C:C', 15) # City
    raw_sheet.set_column('D:D', 15) # Parking Type
    raw_sheet.set_column('E:E', 15) # Booking ID
    raw_sheet.set_column('F:F', 15) # Customer ID
    raw_sheet.set_column('G:G', 15) # Vehicle Type
    raw_sheet.set_column('H:H', 18) # Vehicle Category
    raw_sheet.set_column('I:I', 15) # Booking Status
    raw_sheet.set_column('J:J', 20) # Number of Bookings
    raw_sheet.set_column('K:K', 15) # Revenue
    raw_sheet.set_column('L:L', 18) # Payment Method
    raw_sheet.set_column('M:M', 18) # Customer Rating
    raw_sheet.set_column('N:N', 32) # Service Completion Time (Minutes)
    
    # Write Headers
    for c_idx, header in enumerate(raw_headers):
        raw_sheet.write(0, c_idx, header, f_header)
    raw_sheet.set_row(0, 25)
    
    # Write Data
    for r_idx, row in enumerate(data, start=2):
        raw_sheet.write(f'A{r_idx}', row["Date"], f_date)
        raw_sheet.write(f'B{r_idx}', row["Airport"], f_data_left)
        
        # City -> VLOOKUP formula
        raw_sheet.write_formula(f'C{r_idx}', f"=VLOOKUP(B{r_idx}, 'Lookup Tables'!$A$3:$C$8, 3, FALSE)", f_data_center)
        
        raw_sheet.write(f'D{r_idx}', row["Parking Type"], f_data_left)
        raw_sheet.write(f'E{r_idx}', row["Booking ID"], f_data_center)
        raw_sheet.write(f'F{r_idx}', row["Customer ID"], f_data_center)
        raw_sheet.write(f'G{r_idx}', row["Vehicle Type"], f_data_left)
        
        # Vehicle Category -> XLOOKUP formula
        raw_sheet.write_formula(f'H{r_idx}', f"=XLOOKUP(G{r_idx}, 'Lookup Tables'!$I$3:$I$6, 'Lookup Tables'!$J$3:$J$6, \"N/A\")", f_data_center)
        
        raw_sheet.write(f'I{r_idx}', row["Booking Status"], f_data_center)
        raw_sheet.write(f'J{r_idx}', row["Number of Bookings"], f_number)
        
        # Revenue -> IF + VLOOKUP formula. Duration = (MOD(row_num, 5) + 1)
        duration_formula = f"(MOD(ROW(), 5) + 1)"
        raw_sheet.write_formula(f'K{r_idx}', f"=IF(I{r_idx}=\"Cancelled\", 0, J{r_idx} * VLOOKUP(D{r_idx}, 'Lookup Tables'!$F$3:$G$5, 2, FALSE) * {duration_formula})", f_currency)
        
        raw_sheet.write(f'L{r_idx}', row["Payment Method"], f_data_left)
        raw_sheet.write(f'M{r_idx}', row["Customer Rating"], f_data_center if row["Customer Rating"] == "" else f_number)
        raw_sheet.write(f'N{r_idx}', row["Service Completion Time (Minutes)"], f_data_center if row["Service Completion Time (Minutes)"] == "" else f_number)

    # ----------------------------------------------------
    # Sheet 5: Pivot Tables (Summary Reports)
    # ----------------------------------------------------
    # pivot_sheet declared at workbook initialization
    pivot_sheet.hide_gridlines(2)
    
    pivot_sheet.set_column('A:A', 25)
    pivot_sheet.set_column('B:B', 18)
    pivot_sheet.set_column('C:C', 18)
    pivot_sheet.set_column('D:D', 15)
    pivot_sheet.set_column('E:E', 25)
    
    # Helper to write standard table headings
    def write_table_headings(sheet, row, title, cols):
        sheet.write(row, 0, title, f_section_header)
        for c, name in enumerate(cols):
            sheet.write(row+1, c, name, f_header)
        sheet.set_row(row+1, 24)

    # 1. Revenue by Airport
    write_table_headings(pivot_sheet, 2, "1. Revenue by Airport", ["Airport", "Total Revenue", "Total Bookings", "Avg Rating", "Avg Service Time (Min)"])
    for idx, (name, _, _, _) in enumerate(airports, start=5):
        pivot_sheet.write(f'A{idx}', name, f_data_left)
        pivot_sheet.write_formula(f'B{idx}', f"=SUMIF('Raw Data'!$B$2:$B$501, A{idx}, 'Raw Data'!$K$2:$K$501)", f_currency)
        pivot_sheet.write_formula(f'C{idx}', f"=COUNTIF('Raw Data'!$B$2:$B$501, A{idx})", f_number)
        pivot_sheet.write_formula(f'D{idx}', f"=AVERAGEIF('Raw Data'!$B$2:$B$501, A{idx}, 'Raw Data'!$M$2:$M$501)", f_rating)
        pivot_sheet.write_formula(f'E{idx}', f"=AVERAGEIF('Raw Data'!$B$2:$B$501, A{idx}, 'Raw Data'!$N$2:$N$501)", f_number)
    
    # Totals Row
    pivot_sheet.write('A11', "Grand Total", f_bold_total_label)
    pivot_sheet.write_formula('B11', "=SUM(B5:B10)", f_bold_total)
    pivot_sheet.write_formula('C11', "=SUM(C5:C10)", f_bold_total)
    pivot_sheet.write_formula('D11', "=AVERAGE(D5:D10)", f_bold_total)
    pivot_sheet.write_formula('E11', "=AVERAGE(E5:E10)", f_bold_total)

    # 2. Revenue by Parking Type
    write_table_headings(pivot_sheet, 12, "2. Revenue by Parking Type", ["Parking Type", "Total Revenue", "Total Bookings", "Revenue Share %"])
    for idx, (ptype, _) in enumerate(parking_types, start=15):
        pivot_sheet.write(f'A{idx}', ptype, f_data_left)
        pivot_sheet.write_formula(f'B{idx}', f"=SUMIF('Raw Data'!$D$2:$D$501, A{idx}, 'Raw Data'!$K$2:$K$501)", f_currency)
        pivot_sheet.write_formula(f'C{idx}', f"=COUNTIF('Raw Data'!$D$2:$D$501, A{idx})", f_number)
        pivot_sheet.write_formula(f'D{idx}', f"=B{idx}/$B$18", f_percentage)
        
    pivot_sheet.write('A18', "Grand Total", f_bold_total_label)
    pivot_sheet.write_formula('B18', "=SUM(B15:B17)", f_bold_total)
    pivot_sheet.write_formula('C18', "=SUM(C15:C17)", f_bold_total)
    pivot_sheet.write_formula('D18', "=SUM(D15:D17)", f_bold_total)

    # 3. Monthly Revenue Trend
    # Generating month keys representing last 12 months sorted chronologically
    trend_months = []
    curr_date = start_date
    while curr_date <= end_date:
        m_start = datetime.date(curr_date.year, curr_date.month, 1)
        if m_start not in trend_months:
            trend_months.append(m_start)
        # Advance by 15 days to ensure we hit next month
        curr_date += datetime.timedelta(days=15)
    trend_months = sorted(trend_months)

    write_table_headings(pivot_sheet, 19, "3. Monthly Revenue Trend", ["Month Start", "Total Revenue", "Total Bookings", "Avg Service Time (Min)"])
    for idx, m_start in enumerate(trend_months, start=22):
        pivot_sheet.write_datetime(f'A{idx}', m_start, f_date)
        pivot_sheet.write_formula(f'B{idx}', f"=SUMIFS('Raw Data'!$K$2:$K$501, 'Raw Data'!$A$2:$A$501, \">=\"&A{idx}, 'Raw Data'!$A$2:$A$501, \"<=\"&EOMONTH(A{idx},0))", f_currency)
        pivot_sheet.write_formula(f'C{idx}', f"=COUNTIFS('Raw Data'!$A$2:$A$501, \">=\"&A{idx}, 'Raw Data'!$A$2:$A$501, \"<=\"&EOMONTH(A{idx},0))", f_number)
        pivot_sheet.write_formula(f'D{idx}', f"=AVERAGEIFS('Raw Data'!$N$2:$N$501, 'Raw Data'!$A$2:$A$501, \">=\"&A{idx}, 'Raw Data'!$A$2:$A$501, \"<=\"&EOMONTH(A{idx},0))", f_number)

    # Total Row for monthly trend (12 rows -> 22 to 33)
    pivot_sheet.write('A34', "Grand Total", f_bold_total_label)
    pivot_sheet.write_formula('B34', "=SUM(B22:B33)", f_bold_total)
    pivot_sheet.write_formula('C34', "=SUM(C22:C33)", f_bold_total)
    pivot_sheet.write_formula('D34', "=AVERAGE(D22:D33)", f_bold_total)

    # 4. Booking Status Distribution
    write_table_headings(pivot_sheet, 35, "4. Booking Status Distribution", ["Booking Status", "Total Bookings", "Share %"])
    for idx, status in enumerate(booking_statuses, start=38):
        pivot_sheet.write(f'A{idx}', status, f_data_left)
        pivot_sheet.write_formula(f'B{idx}', f"=COUNTIF('Raw Data'!$I$2:$I$501, A{idx})", f_number)
        pivot_sheet.write_formula(f'C{idx}', f"=B{idx}/$B$41", f_percentage)
        
    pivot_sheet.write('A41', "Grand Total", f_bold_total_label)
    pivot_sheet.write_formula('B41', "=SUM(B38:B40)", f_bold_total)
    pivot_sheet.write_formula('C41', "=SUM(C38:C40)", f_bold_total)

    # 5. Average Customer Rating by Airport
    write_table_headings(pivot_sheet, 42, "5. Average Customer Rating by Airport", ["Airport", "Avg Customer Rating"])
    for idx, (name, _, _, _) in enumerate(airports, start=45):
        pivot_sheet.write(f'A{idx}', name, f_data_left)
        pivot_sheet.write_formula(f'B{idx}', f"=AVERAGEIF('Raw Data'!$B$2:$B$501, A{idx}, 'Raw Data'!$M$2:$M$501)", f_rating)
        
    pivot_sheet.write('A51', "Grand Total", f_bold_total_label)
    pivot_sheet.write_formula('B51', "=AVERAGE(B45:B50)", f_bold_total)

    # 6. Revenue by Vehicle Type
    write_table_headings(pivot_sheet, 52, "6. Revenue by Vehicle Type", ["Vehicle Type", "Total Revenue", "Total Bookings"])
    for idx, (vtype, _) in enumerate(vehicle_types, start=55):
        pivot_sheet.write(f'A{idx}', vtype, f_data_left)
        pivot_sheet.write_formula(f'B{idx}', f"=SUMIF('Raw Data'!$G$2:$G$501, A{idx}, 'Raw Data'!$K$2:$K$501)", f_currency)
        pivot_sheet.write_formula(f'C{idx}', f"=COUNTIF('Raw Data'!$G$2:$G$501, A{idx})", f_number)
        
    pivot_sheet.write('A59', "Grand Total", f_bold_total_label)
    pivot_sheet.write_formula('B59', "=SUM(B55:B58)", f_bold_total)
    pivot_sheet.write_formula('C59', "=SUM(C55:C58)", f_bold_total)

    # ----------------------------------------------------
    # Sheet 2: Dashboard
    # ----------------------------------------------------
    # dashboard declared at workbook initialization
    dashboard.hide_gridlines(2)
    
    # Formatting widths for 12-column grid structure (B to M)
    dashboard.set_column('A:A', 3)   # Left margin spacer
    dashboard.set_column('B:C', 13)  # Col B-C
    dashboard.set_column('D:E', 13)  # Col D-E
    dashboard.set_column('F:G', 13)  # Col F-G
    dashboard.set_column('H:I', 13)  # Col H-I
    dashboard.set_column('J:K', 13)  # Col J-K
    dashboard.set_column('L:M', 13)  # Col L-M
    dashboard.set_column('N:N', 3)   # Right margin spacer

    # Apply cool light background color to the dashboard page to make cards pop
    f_dash_bg = workbook.add_format({'bg_color': '#F8F9FA'})
    for r in range(0, 60):
        dashboard.set_row(r, None, f_dash_bg)
        
    # Title Banner (B2:M2)
    dashboard.merge_range('B2:M2', "AIRPORT PARKING OPERATIONS PERFORMANCE DASHBOARD", f_title)
    dashboard.set_row(1, 40)
    
    # 1. Interactive Control Panel Card (Row 4)
    f_ctrl_bg = workbook.add_format({'bg_color': c_white, 'top': 1, 'bottom': 1, 'left': 1, 'right': 1, 
                                     'top_color': '#D9D9D9', 'bottom_color': '#D9D9D9', 'left_color': '#D9D9D9', 'right_color': '#D9D9D9'})
    f_label_bold = workbook.add_format({'font_name': 'Segoe UI', 'font_size': 10, 'bold': True, 'font_color': c_navy, 'bg_color': c_white, 'align': 'right', 'valign': 'vcenter'})
    f_drop_val = workbook.add_format({'font_name': 'Segoe UI', 'font_size': 10, 'bg_color': '#FFF2CC', 'align': 'center', 'valign': 'vcenter', 'border': 1, 'border_color': '#A6A6A6'})
    
    # Draw Control panel container
    for c in range(1, 13):
        dashboard.write(3, c, "", f_ctrl_bg)
    dashboard.set_row(3, 28)
    
    dashboard.write('B4', "Interactive Controls:", workbook.add_format({'font_name': 'Segoe UI', 'font_size': 10, 'bold': True, 'italic': True, 'font_color': c_navy, 'bg_color': c_white, 'valign': 'vcenter'}))
    dashboard.write('D4', "Select Airport Filter:", f_label_bold)
    dashboard.write('E4', "All", f_drop_val)
    dashboard.data_validation('E4', {
        'validate': 'list',
        'source': ['All'] + [a[0] for a in airports]
    })
    
    dashboard.write('H4', "Select Parking Filter:", f_label_bold)
    dashboard.write('I4', "All", f_drop_val)
    dashboard.data_validation('I4', {
        'validate': 'list',
        'source': ['All'] + [p[0] for p in parking_types]
    })

    # 2. Selected Airport Information Card (Row 6-7)
    # Displays metadata looked up via XLOOKUP based on dropdown selection
    f_meta_label = workbook.add_format({'font_name': 'Segoe UI', 'font_size': 9, 'bold': True, 'font_color': '#595959', 'bg_color': c_white, 'align': 'center', 'valign': 'vcenter'})
    f_meta_val = workbook.add_format({'font_name': 'Segoe UI', 'font_size': 11, 'bold': True, 'font_color': c_navy, 'bg_color': c_white, 'align': 'center', 'valign': 'vcenter'})
    
    for r in [5, 6]:
        for c in range(1, 13):
            dashboard.write(r, c, "", f_ctrl_bg)
    dashboard.set_row(5, 18)
    dashboard.set_row(6, 22)
    
    dashboard.write('B6', "Selected Hub Info", workbook.add_format({'font_name': 'Segoe UI', 'font_size': 10, 'bold': True, 'font_color': c_navy, 'bg_color': c_white, 'valign': 'vcenter'}))
    
    dashboard.write('D6', "Airport Code", f_meta_label)
    dashboard.write_formula('D7', '=IF(E4="All", "ALL HUBS", XLOOKUP(E4, \'Lookup Tables\'!$A$3:$A$8, \'Lookup Tables\'!$B$3:$B$8, "N/A"))', f_meta_val)
    
    dashboard.write('G6', "City", f_meta_label)
    dashboard.write_formula('G7', '=IF(E4="All", "National Network", XLOOKUP(E4, \'Lookup Tables\'!$A$3:$A$8, \'Lookup Tables\'!$C$3:$C$8, "N/A"))', f_meta_val)
    
    dashboard.write('J6', "Daily Capacity Limit", f_meta_label)
    dashboard.write_formula('J7', '=IF(E4="All", "2,800 Lots", XLOOKUP(E4, \'Lookup Tables\'!$A$3:$A$8, \'Lookup Tables\'!$D$3:$D$8, "N/A") & " Lots")', f_meta_val)

    # 3. Dynamic KPI Cards (Row 9-11)
    # Setup formats
    f_kpi_t = workbook.add_format({
        'font_name': 'Segoe UI', 'font_size': 9, 'bold': True, 'font_color': '#595959', 'bg_color': c_white, 'align': 'center', 'valign': 'vcenter',
        'top': 1, 'left': 1, 'right': 1, 'top_color': '#D9D9D9', 'left_color': '#D9D9D9', 'right_color': '#D9D9D9'
    })
    f_kpi_v_curr = workbook.add_format({
        'font_name': 'Segoe UI', 'font_size': 15, 'bold': True, 'font_color': c_navy, 'bg_color': c_white, 'align': 'center', 'valign': 'vcenter',
        'num_format': '$#,##0', 'left': 1, 'right': 1, 'left_color': '#D9D9D9', 'right_color': '#D9D9D9'
    })
    f_kpi_v_num = workbook.add_format({
        'font_name': 'Segoe UI', 'font_size': 15, 'bold': True, 'font_color': c_navy, 'bg_color': c_white, 'align': 'center', 'valign': 'vcenter',
        'num_format': '#,##0', 'left': 1, 'right': 1, 'left_color': '#D9D9D9', 'right_color': '#D9D9D9'
    })
    f_kpi_v_rate = workbook.add_format({
        'font_name': 'Segoe UI', 'font_size': 15, 'bold': True, 'font_color': c_navy, 'bg_color': c_white, 'align': 'center', 'valign': 'vcenter',
        'num_format': '0.0%', 'left': 1, 'right': 1, 'left_color': '#D9D9D9', 'right_color': '#D9D9D9'
    })
    f_kpi_v_rating = workbook.add_format({
        'font_name': 'Segoe UI', 'font_size': 15, 'bold': True, 'font_color': c_navy, 'bg_color': c_white, 'align': 'center', 'valign': 'vcenter',
        'num_format': '0.0', 'left': 1, 'right': 1, 'left_color': '#D9D9D9', 'right_color': '#D9D9D9'
    })
    f_kpi_s = workbook.add_format({
        'font_name': 'Segoe UI', 'font_size': 8, 'italic': True, 'font_color': '#7F7F7F', 'bg_color': c_white, 'align': 'center', 'valign': 'vcenter',
        'bottom': 1, 'left': 1, 'right': 1, 'bottom_color': '#D9D9D9', 'left_color': '#D9D9D9', 'right_color': '#D9D9D9'
    })
    
    dashboard.set_row(8, 16)
    dashboard.set_row(9, 24)
    dashboard.set_row(10, 15)
    
    # Helper to draw a KPI Card
    def draw_kpi_card(col_start, col_end, title, formula, sub, format_v):
        dashboard.merge_range(f"{col_start}9:{col_end}9", title, f_kpi_t)
        dashboard.merge_range(f"{col_start}10:{col_end}10", formula, format_v)
        dashboard.merge_range(f"{col_start}11:{col_end}11", sub, f_kpi_s)
        
    # KPI 1: Total Bookings
    bookings_f = (
        '=IF(E4="All", '
        'IF(I4="All", COUNTA(\'Raw Data\'!$E$2:$E$501), COUNTIF(\'Raw Data\'!$D$2:$D$501, I4)), '
        'IF(I4="All", COUNTIF(\'Raw Data\'!$B$2:$B$501, E4), COUNTIFS(\'Raw Data\'!$B$2:$B$501, E4, \'Raw Data\'!$D$2:$D$501, I4)))'
    )
    draw_kpi_card('B', 'C', "TOTAL BOOKINGS", bookings_f, "Active tickets in database", f_kpi_v_num)
    
    # KPI 2: Total Revenue
    revenue_f = (
        '=IF(E4="All", '
        'IF(I4="All", SUM(\'Raw Data\'!$K$2:$K$501), SUMIF(\'Raw Data\'!$D$2:$D$501, I4, \'Raw Data\'!$K$2:$K$501)), '
        'IF(I4="All", SUMIF(\'Raw Data\'!$B$2:$B$501, E4, \'Raw Data\'!$K$2:$K$501), SUMIFS(\'Raw Data\'!$K$2:$K$501, \'Raw Data\'!$B$2:$B$501, E4, \'Raw Data\'!$D$2:$D$501, I4)))'
    )
    draw_kpi_card('D', 'E', "TOTAL REVENUE", revenue_f, "Gross parking collection", f_kpi_v_curr)
    
    # KPI 3: Average Revenue per Booking
    # Points to Col D10 (Total Revenue) / Col B10 (Total Bookings)
    draw_kpi_card('F', 'G', "AVG REV / BOOKING", '=IF(B10=0, 0, D10/B10)', "Average ticket yield", f_kpi_v_curr)
    
    # KPI 4: Completion Rate %
    completed_bookings_f = (
        '=IF(E4="All", '
        'IF(I4="All", COUNTIF(\'Raw Data\'!$I$2:$I$501, "Completed"), COUNTIFS(\'Raw Data\'!$D$2:$D$501, I4, \'Raw Data\'!$I$2:$I$501, "Completed")), '
        'IF(I4="All", COUNTIFS(\'Raw Data\'!$B$2:$B$501, E4, \'Raw Data\'!$I$2:$I$501, "Completed"), COUNTIFS(\'Raw Data\'!$B$2:$B$501, E4, \'Raw Data\'!$D$2:$D$501, I4, \'Raw Data\'!$I$2:$I$501, "Completed")))'
    )
    # Helper formula to calculate Completion Rate = Completed Bookings / Total Bookings
    # Write completed bookings to a hidden or reference cell? Or evaluate directly in the formula:
    rate_f = f"=IF(B10=0, 0, {completed_bookings_f} / B10)"
    draw_kpi_card('H', 'I', "COMPLETION RATE %", rate_f, "Target: 80% completion", f_kpi_v_rate)
    
    # KPI 5: Average Customer Rating
    rating_f = (
        '=IF(E4="All", '
        'IF(I4="All", AVERAGE(\'Raw Data\'!$M$2:$M$501), AVERAGEIFS(\'Raw Data\'!$M$2:$M$501, \'Raw Data\'!$D$2:$D$501, I4)), '
        'IF(I4="All", AVERAGEIFS(\'Raw Data\'!$M$2:$M$501, \'Raw Data\'!$B$2:$B$501, E4), AVERAGEIFS(\'Raw Data\'!$M$2:$M$501, \'Raw Data\'!$B$2:$B$501, E4, \'Raw Data\'!$D$2:$D$501, I4)))'
    )
    draw_kpi_card('J', 'K', "AVG CUSTOMER RATING", rating_f, "Scale: 1.0 - 5.0 Stars", f_kpi_v_rating)
    
    # KPI 6: Total Airports Served
    airports_served_f = '=IF(E4="All", 6, 1)'
    draw_kpi_card('L', 'M', "AIRPORTS SERVED", airports_served_f, "Active regional hubs", f_kpi_v_num)

    # Add space row
    dashboard.set_row(11, 15)

    # ----------------------------------------------------
    # 4. Insert Charts linking to Pivot Tables
    # ----------------------------------------------------
    
    # 1. Revenue by Airport (Clustered Column Chart)
    chart_airport = workbook.add_chart({'type': 'column'})
    chart_airport.add_series({
        'categories': "='Pivot Tables'!$A$5:$A$10",
        'values':     "='Pivot Tables'!$B$5:$B$10",
        'name':       'Revenue',
        'fill':       {'color': c_navy},
        'data_labels': {'value': False}
    })
    chart_airport.set_title({'name': 'Revenue by Airport', 'name_font': {'name': 'Segoe UI', 'size': 11, 'bold': True, 'color': c_navy}})
    chart_airport.set_legend({'none': True})
    chart_airport.set_y_axis({'name': 'Revenue ($)', 'name_font': {'name': 'Segoe UI', 'size': 9}})
    chart_airport.set_x_axis({'name': 'Airport', 'name_font': {'name': 'Segoe UI', 'size': 9}})
    chart_airport.set_size({'width': 480, 'height': 280})
    dashboard.insert_chart('B13', chart_airport)
    
    # 2. Monthly Revenue Trend (Line Chart)
    chart_trend = workbook.add_chart({'type': 'line'})
    chart_trend.add_series({
        'categories': "='Pivot Tables'!$A$22:$A$33",
        'values':     "='Pivot Tables'!$B$22:$B$33",
        'name':       'Revenue Trend',
        'line':       {'color': c_steel, 'width': 2.25},
        'marker':     {'type': 'circle', 'size': 5, 'fill': {'color': c_navy}}
    })
    chart_trend.set_title({'name': 'Monthly Revenue Trend', 'name_font': {'name': 'Segoe UI', 'size': 11, 'bold': True, 'color': c_navy}})
    chart_trend.set_legend({'none': True})
    chart_trend.set_y_axis({'name': 'Revenue ($)', 'name_font': {'name': 'Segoe UI', 'size': 9}})
    chart_trend.set_size({'width': 480, 'height': 280})
    dashboard.insert_chart('H13', chart_trend)
    
    # 3. Revenue by Parking Type (Pie Chart)
    chart_parking = workbook.add_chart({'type': 'pie'})
    chart_parking.add_series({
        'categories': "='Pivot Tables'!$A$15:$A$17",
        'values':     "='Pivot Tables'!$B$15:$B$17",
        'name':       'Parking Type Revenue Share',
        'points': [
            {'fill': {'color': '#4F81BD'}}, # Economy
            {'fill': {'color': c_steel}},   # Premium
            {'fill': {'color': c_navy}},    # Valet
        ],
        'data_labels': {'percentage': True, 'value': False}
    })
    chart_parking.set_title({'name': 'Revenue by Parking Type', 'name_font': {'name': 'Segoe UI', 'size': 11, 'bold': True, 'color': c_navy}})
    chart_parking.set_legend({'position': 'right', 'font': {'name': 'Segoe UI', 'size': 9}})
    chart_parking.set_size({'width': 480, 'height': 280})
    dashboard.insert_chart('B28', chart_parking)
    
    # 4. Booking Status Distribution (Doughnut Chart)
    chart_status = workbook.add_chart({'type': 'doughnut'})
    chart_status.add_series({
        'categories': "='Pivot Tables'!$A$38:$A$40",
        'values':     "='Pivot Tables'!$B$38:$B$40",
        'name':       'Booking Status Share',
        'points': [
            {'fill': {'color': '#9BBB59'}}, # Completed (Soft Green)
            {'fill': {'color': '#F79646'}}, # Pending (Soft Orange)
            {'fill': {'color': '#C0504D'}}, # Cancelled (Soft Red)
        ],
        'data_labels': {'percentage': True, 'value': False}
    })
    chart_status.set_title({'name': 'Booking Status Distribution', 'name_font': {'name': 'Segoe UI', 'size': 11, 'bold': True, 'color': c_navy}})
    chart_status.set_legend({'position': 'right', 'font': {'name': 'Segoe UI', 'size': 9}})
    chart_status.set_size({'width': 480, 'height': 280})
    dashboard.insert_chart('H28', chart_status)
    
    # 5. Revenue by Vehicle Type (Horizontal Bar Chart)
    chart_vehicle = workbook.add_chart({'type': 'bar'})
    chart_vehicle.add_series({
        'categories': "='Pivot Tables'!$A$55:$A$58",
        'values':     "='Pivot Tables'!$B$55:$B$58",
        'name':       'Revenue',
        'fill':       {'color': '#4F81BD'},
    })
    chart_vehicle.set_title({'name': 'Revenue by Vehicle Type', 'name_font': {'name': 'Segoe UI', 'size': 11, 'bold': True, 'color': c_navy}})
    chart_vehicle.set_legend({'none': True})
    chart_vehicle.set_x_axis({'name': 'Revenue ($)', 'name_font': {'name': 'Segoe UI', 'size': 9}})
    chart_vehicle.set_y_axis({'name': 'Vehicle Type', 'name_font': {'name': 'Segoe UI', 'size': 9}})
    chart_vehicle.set_size({'width': 480, 'height': 280})
    dashboard.insert_chart('B43', chart_vehicle)

    # ----------------------------------------------------
    # Auto-fit Column Widths (for all worksheets)
    # ----------------------------------------------------
    # Note: For formula columns, we provide specific hardcoded sizes
    # to avoid evaluating formulas in python which returns ### or formula string length.
    # We already sized Dashboard, Summary, and Lookup tables.
    
    writer.close()
    print(f"Excel workbook '{output_filename}' generated successfully!")

if __name__ == "__main__":
    generate_excel()