retail-trade-report-generator

Retail Trade Weekly Report Generator - Skill Documentation

Overview

Purpose

Input Requirements

Required Files (12 total)

Previous Week (6 files with earlier dates): Same file types with earlier date ranges in filename

Store Mapping CSV

Store Name,Region,Aliases
SM Megamall,NCR,"Megamall|SM Mega|MEGAMALL"
...

Data Processing Logic

1. File Identification

2. Data Extraction Rules

#### DRP Channel Sales Report

#### DRP TECNO Report

#### License Store Report

#### DXS Mobile Prepaid Report

#### DXS Mobile Postpaid Report

#### DXS FWA Report

3. Store Name to Region Mapping

# Build mapping dictionary from CSV
store_mapping = {}
for row in mapping_csv:
    main_name = row['Store Name']
    region = row['Region']
    aliases = row['Aliases'].split('|') if row['Aliases'] else []
    
    # Add main name and all aliases to mapping
    store_mapping[main_name.upper()] = region
    for alias in aliases:
        store_mapping[alias.strip().upper()] = region
Apply fuzzy matching for unmatched stores
def map_store_to_region(store_name):
    # Exact match (case-insensitive)
    if store_name.upper() in store_mapping:
        return store_mapping[store_name.upper()]
    
    # Fuzzy match using substring search
    for key in store_mapping:
        if key in store_name.upper() or store_name.upper() in key:
            return store_mapping[key]
    
    # Default to "Others" if no match
    return "Others"

4. Regional Aggregation

Standard Regions: NCR, SLZ, NLZ, CLZ, EVIS, WVIS, MIN, Others

For each product type and region:

# DRP data: Direct mapping (already by region)
DRP_ADA = drp_data[region][product_column]
DXS data: Aggregate stores by region
DXS_ADA = sum(dxs_data[store][product_column] 
              for store in dxs_data 
              if map_store_to_region(store) == region)
LS data: Aggregate stores by region
LS_ADA = sum(ls_data[store][product_column] 
             for store in ls_data 
             if map_store_to_region(store) == region)
Total for region
RT_Total_ADA = DRP_ADA + DXS_ADA + LS_ADA

5. WoW Calculation

WoW = (current_week_value - previous_week_value) / previous_week_value
Formatting rules:
- Display as percentage (e.g., "21%", "-13%")
- Round to nearest integer
- Handle division by zero: display "-" if previous_week_value == 0
- Handle cases where current = 0 and previous > 0: show "-100%"

6. Special Calculations

#### FWA 5G Components

# DRP FWA 5G
DRP_FWA_5G = Column_10 + Column_11
DXS FWA 5G
DXS_FWA_5G = Total - Column_1_4G
LS FWA 5G
LS_FWA_5G = Unli_5G_WIFI_100Mbps + WiFi_4990_SIM

#### TECNO ADA

TECNO_ADA = CAMON_40 + POVA_7

Output Format

Excel Structure

Sections: 1. Report Header (Rows 1-2) - Title: "Retail Trade Weekly Report" - Date ranges: "Last Week: [dates] | This Week: [dates]"

2. Channel Summary (Rows 4-9) - Columns: Channel | Program name | This Week ADA | WoW | MoM - Rows: DRP BAU, DRP TECNO, License Store, DXS, RT Total

3. Mobile Prepaid by Region (Rows 11-21) - Columns: Region | RT Total ADA | WoW | DXS ADA | WoW | LS ADA | WoW | DRP ADA | WoW - Rows: 8 regions + Total

4. DRP Prepaid Program (Rows 23-33) - Columns: Region | Double Data ADA | WoW | TECNO ADA | WoW | CAMON 40 | WoW | POVA 7 | WoW - Rows: 8 regions + Total

5. Mobile Postpaid by Region (Rows 35-45) - Same structure as Mobile Prepaid

6. FWA 4G by Region (Rows 47-57) - Same structure as Mobile Prepaid

7. FWA 5G by Region (Rows 59-69) - Same structure as Mobile Prepaid

Formatting Rules

#### Number Formatting

#### Color Coding

#### Cell Styling

Charts

Chart 1: Channel Performance Comparison

Chart 2: Regional Mobile Prepaid Distribution

Chart 3: WoW Trend - Top 3 Regions

Error Handling

Missing Files

if len(current_week_files) != 6:
    raise ValueError(f"Expected 6 current week files, found {len(current_week_files)}")
if len(previous_week_files) != 6:
    raise ValueError(f"Expected 6 previous week files, found {len(previous_week_files)}")

Unmapped Stores

unmapped_stores = []
for store in all_stores:
    if map_store_to_region(store) == "Others":
        # Log warning but continue processing
        unmapped_stores.append(store)
if unmapped_stores:
    print(f"Warning: {len(unmapped_stores)} stores mapped to 'Others' region")

Data Quality Checks

# Check for negative values
if any_value < 0:
    print(f"Warning: Negative value found in {file}:{column}")
Check for missing regions
expected_regions = {"NCR", "SLZ", "NLZ", "CLZ", "EVIS", "WVIS", "MIN", "Others"}
missing_regions = expected_regions - set(actual_regions)
if missing_regions:
    print(f"Warning: Missing regions: {missing_regions}")

Implementation Notes

Python Libraries

import pandas as pd
import openpyxl
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.chart import BarChart, LineChart, Reference
import re
from datetime import datetime

Key Functions

#### 1. File Parser

def extract_date_from_filename(filename):
    """Extract date range from filename like 'Report_1_11-1_17.xlsx'"""
    pattern = r'_(\d+)_(\d+)-(\d+)_(\d+)\.xlsx'
    match = re.search(pattern, filename)
    if match:
        start_month, start_day, end_month, end_day = match.groups()
        return (int(start_month), int(start_day), int(end_month), int(end_day))
    return None
def identify_file_type(filename):
    """Identify file type from filename"""
    if 'DRP_Channel_Sales' in filename:
        return 'DRP'
    elif 'TECNO' in filename:
        return 'TECNO'
    elif 'License_Store' in filename:
        return 'LS'
    elif 'Mobile_Prepaid' in filename:
        return 'DXS_Prepaid'
    elif 'Mobile_Postpaid' in filename:
        return 'DXS_Postpaid'
    elif 'FWA' in filename:
        return 'DXS_FWA'
    return 'Unknown'

#### 2. Data Extractor

def extract_drp_data(filepath):
    """Extract DRP channel sales data"""
    df = pd.read_excel(filepath, sheet_name='Sheet0', header=None)
    
    # Find data start row (usually row 8)
    data_start = 8
    
    # Extract by region
    regions_data = {}
    for idx in range(data_start, len(df)):
        region = df.iloc[idx, 0]
        if pd.isna(region) or region == 'Total':
            continue
            
        regions_data[region] = {
            'mobile_postpaid': df.iloc[idx, 1],
            'mobile_prepaid': df.iloc[idx, 5],
            'double_data': df.iloc[idx, 6],
            'fwa_4g': df.iloc[idx, 9],
            'fwa_5g': df.iloc[idx, 10] + df.iloc[idx, 11]
        }
    
    return regions_data
def extract_dxs_data(filepath, product_type):
    """Extract DXS acquisition data"""
    df = pd.read_excel(filepath, sheet_name='Sheet1', header=None)
    
    # Determine column based on product type
    if product_type == 'prepaid':
        value_col = 4
    elif product_type == 'postpaid':
        value_col = 12
    elif product_type == 'fwa':
        return extract_dxs_fwa_data(df)
    
    stores_data = {}
    for idx in range(8, len(df)):
        store = df.iloc[idx, 0]
        if pd.isna(store) or store in ['Grand Total', '-']:
            continue
        
        value = df.iloc[idx, value_col]
        if pd.notna(value):
            stores_data[store] = value
    
    return stores_data
def extract_dxs_fwa_data(df):
    """Extract FWA data with 4G/5G split"""
    stores_data = {}
    for idx in range(8, len(df)):
        store = df.iloc[idx, 0]
        if pd.isna(store) or store in ['Grand Total', '-']:
            continue
        
        fwa_4g = df.iloc[idx, 1] if pd.notna(df.iloc[idx, 1]) else 0
        total = df.iloc[idx, 18] if pd.notna(df.iloc[idx, 18]) else 0
        fwa_5g = total - fwa_4g
        
        stores_data[store] = {
            'fwa_4g': fwa_4g,
            'fwa_5g': fwa_5g
        }
    
    return stores_data

#### 3. Region Aggregator

def aggregate_by_region(stores_data, mapping_dict, regions):
    """Aggregate store data by region"""
    regional_totals = {region: 0 for region in regions}
    
    for store, value in stores_data.items():
        region = map_store_to_region(store, mapping_dict)
        if isinstance(value, dict):
            # Handle nested data (e.g., FWA with 4G/5G)
            for key in value:
                if key not in regional_totals:
                    regional_totals[key] = {region: 0 for region in regions}
                regional_totals[key][region] += value[key]
        else:
            regional_totals[region] += value
    
    return regional_totals

#### 4. WoW Calculator

def calculate_wow(current, previous):
    """Calculate week-over-week percentage change"""
    if previous == 0 or pd.isna(previous):
        return "-"
    
    if current == 0 or pd.isna(current):
        return "-100%"
    
    wow = ((current - previous) / previous) * 100
    return f"{int(round(wow))}%"

#### 5. Excel Formatter

def apply_formatting(ws, start_row, start_col, end_row, end_col):
    """Apply formatting to Excel worksheet"""
    # Define styles
    header_fill = PatternFill(start_color="F0F0F0", end_color="F0F0F0", fill_type="solid")
    total_fill = PatternFill(start_color="E6F2FF", end_color="E6F2FF", fill_type="solid")
    
    green_font = Font(color="008000")
    red_font = Font(color="FF0000")
    gray_font = Font(color="808080")
    bold_font = Font(bold=True)
    
    thin_border = Border(
        left=Side(style='thin'),
        right=Side(style='thin'),
        top=Side(style='thin'),
        bottom=Side(style='thin')
    )
    
    # Apply to cells
    for row in ws.iter_rows(min_row=start_row, max_row=end_row, 
                            min_col=start_col, max_col=end_col):
        for cell in row:
            cell.border = thin_border
            
            # Color code WoW values
            if isinstance(cell.value, str) and '%' in cell.value:
                try:
                    pct_value = int(cell.value.replace('%', ''))
                    if pct_value > 0:
                        cell.font = green_font
                    elif pct_value < 0:
                        cell.font = red_font
                except:
                    if cell.value == '-':
                        cell.font = gray_font
def add_chart(ws, chart_type, data_range, position, title):
    """Add chart to worksheet"""
    if chart_type == 'column':
        chart = BarChart()
    elif chart_type == 'line':
        chart = LineChart()
    
    chart.title = title
    chart.style = 10
    chart.height = 10
    chart.width = 15
    
    data = Reference(ws, min_col=data_range[0], min_row=data_range[1],
                     max_col=data_range[2], max_row=data_range[3])
    chart.add_data(data, titles_from_data=True)
    
    ws.add_chart(chart, position)

Usage Example

from retail_trade_report_skill import generate_weekly_report
Input files directory
input_dir = "/mnt/user-data/uploads/"
Store mapping CSV
mapping_file = "/mnt/user-data/uploads/store_mapping.csv"
Generate report
output_file = generate_weekly_report(
    input_dir=input_dir,
    mapping_csv=mapping_file,
    output_path="/mnt/user-data/outputs/Retail_Trade_Weekly_Report.xlsx"
)
print(f"Report generated: {output_file}")

Validation Checklist

Before finalizing output:

Performance Considerations

Troubleshooting

Common Issues

Issue: "File not found" error

Issue: Store name not mapping to region

Issue: WoW showing "N/A" for all values

Issue: Charts not displaying

Issue: Negative ADA values

Version History

Common Issues

Issue: "File not found" error

Issue: Store name not mapping to region

Issue: WoW showing "N/A" for all values

Issue: Charts not displaying

Issue: Negative ADA values