# Send retail-trade-report-generator to your agent Hand the extracted package to your coding agent with a concrete install brief instead of figuring it out manually. ## Fast path - Download the package from Yavira. - Extract it into a folder your agent can access. - Paste one of the prompts below and point your agent at the extracted folder. ## Suggested prompts ### New install ```text I downloaded a skill package from Yavira. Read SKILL.md from the extracted folder and install it by following the included instructions. Tell me what you changed and call out any manual steps you could not complete. ``` ### Upgrade existing ```text I downloaded an updated skill package from Yavira. Read SKILL.md from the extracted folder, compare it with my current installation, and upgrade it while preserving any custom configuration unless the package docs explicitly say otherwise. Summarize what changed and any follow-up checks I should run. ``` ## Machine-readable fields ```json { "schemaVersion": "1.0", "item": { "slug": "retail-trade-report-generator", "name": "retail-trade-report-generator", "source": "tencent", "type": "skill", "category": "数据分析", "sourceUrl": "https://clawhub.ai/wuminmin/retail-trade-report-generator", "canonicalUrl": "https://clawhub.ai/wuminmin/retail-trade-report-generator", "targetPlatform": "OpenClaw" }, "install": { "downloadUrl": "/downloads/retail-trade-report-generator", "sourceDownloadUrl": "https://wry-manatee-359.convex.site/api/v1/download?slug=retail-trade-report-generator", "sourcePlatform": "tencent", "targetPlatform": "OpenClaw", "packageFormat": "ZIP package", "primaryDoc": "SKILL.md", "includedAssets": [ "README_CN.md", "SKILL.md", "scripts/retail_trade_report_generator.py", "references/store_mapping.csv" ], "downloadMode": "redirect", "sourceHealth": { "source": "tencent", "status": "healthy", "reason": "direct_download_ok", "recommendedAction": "download", "checkedAt": "2026-04-23T16:43:11.935Z", "expiresAt": "2026-04-30T16:43:11.935Z", "httpStatus": 200, "finalUrl": "https://wry-manatee-359.convex.site/api/v1/download?slug=4claw-imageboard", "contentType": "application/zip", "probeMethod": "head", "details": { "probeUrl": "https://wry-manatee-359.convex.site/api/v1/download?slug=4claw-imageboard", "contentDisposition": "attachment; filename=\"4claw-imageboard-1.0.1.zip\"", "redirectLocation": null, "bodySnippet": null }, "scope": "source", "summary": "Source download looks usable.", "detail": "Yavira can redirect you to the upstream package for this source.", "primaryActionLabel": "Download for OpenClaw", "primaryActionHref": "/downloads/retail-trade-report-generator" }, "validation": { "installChecklist": [ "Use the Yavira download entry.", "Review SKILL.md after the package is downloaded.", "Confirm the extracted package contains the expected setup assets." ], "postInstallChecks": [ "Confirm the extracted package includes the expected docs or setup files.", "Validate the skill or prompts are available in your target agent workspace.", "Capture any manual follow-up steps the agent could not complete." ] } }, "links": { "detailUrl": "https://openagent3.xyz/skills/retail-trade-report-generator", "downloadUrl": "https://openagent3.xyz/downloads/retail-trade-report-generator", "agentUrl": "https://openagent3.xyz/skills/retail-trade-report-generator/agent", "manifestUrl": "https://openagent3.xyz/skills/retail-trade-report-generator/agent.json", "briefUrl": "https://openagent3.xyz/skills/retail-trade-report-generator/agent.md" } } ``` ## Documentation ### Overview This skill processes multiple weekly sales report Excel files to generate a consolidated Retail Trade Weekly Report with week-over-week (WoW) comparisons across different channels (DRP, DXS, License Store) and product types (Mobile Prepaid/Postpaid, FWA 4G/5G). ### Purpose Consolidate 12 Excel files (6 current week + 6 previous week) into a single comprehensive weekly report Calculate Average Daily Acquisition (ADA) metrics by region and channel Compute Week-over-Week (WoW) performance indicators Generate formatted Excel output with charts and color-coded performance indicators ### Required Files (12 total) Current Week (6 files): DRP_Channel_Sales_Report_DRP_M_DD-M_DD.xlsx DRP_Special_SIM_Monitor_Report_Daily_TECNO_M_DD-M_DD.xlsx License_Store_Performance_Monitor_Report_LS_M_DD-M_DD.xlsx DXS_Acquisition_Report_Mobile_Prepaid_M_DD-M_DD.xlsx DXS_Acquisition_Report_Mobile_Postpaid_M_DD-M_DD.xlsx DXS_Acquisition_Report_FWA_M_DD-M_DD.xlsx Previous Week (6 files with earlier dates): Same file types with earlier date ranges in filename ### Store Mapping CSV File containing Store Name to Region mapping with aliases support: Store Name,Region,Aliases SM Megamall,NCR,"Megamall|SM Mega|MEGAMALL" ... ### 1. File Identification Extract date ranges from filenames (format: M_DD-M_DD) Auto-group into current week vs previous week based on date comparison Validate all 12 required files are present ### 2. Data Extraction Rules DRP Channel Sales Report Header rows: Skip rows 0-7 Data rows: Start from row 8 Region field: Column 0 (AREA) Key columns: Column 1: MOBILE POSTPAID > TOTAL ACTIVATION Column 5: MOBILE PREPAID > TOTAL ACTIVATION Column 6: Double Data_Sum Column 9: 4G WiFi 980 SIM_Sum (FWA 4G) Column 10: Unli 5G WIFI 100Mbps Starter SIM_Sum (FWA 5G) Column 11: 5G WiFi 4990 SIM_Sum (FWA 5G) DRP TECNO Report Header rows: Skip rows 0-6 Data rows: Start from row 7 Region field: Column 0 (Activation Area) Key columns: Column 1: CARMON Activation (CAMON 40) Column 2: POVA Activation (POVA 7) Column 3: Total Activation (TECNO ADA = CAMON 40 + POVA 7) License Store Report Header rows: Skip rows 0-7 Data rows: Start from row 8 Store field: Column 0 (Store Name) - Requires mapping to Region Key columns: Column 1: Mobile Prepaid Column 3: Mobile Postpaid Column 29 (AD): DITO Home Prepaid 4G WiFi 980 SIM (FWA 4G) Need to find: Unli 5G WIFI 100Mbps Starter SIM (FWA 5G) Need to find: 5G WiFi 4990 SIM (FWA 5G) DXS Mobile Prepaid Report Header rows: Skip rows 0-7 Data rows: Start from row 8 Store field: Column 0 (DXS Name) - Requires mapping to Region Key column: Column 4: Total DXS Mobile Postpaid Report Header rows: Skip rows 0-7 Data rows: Start from row 8 Store field: Column 0 (DXS Name) - Requires mapping to Region Key column: Column 12: Total DXS FWA Report Header rows: Skip rows 0-7 Data rows: Start from row 8 Store field: Column 0 (DXS Name) - Requires mapping to Region Key columns: Column 1: DITO Home Prepaid 4G WiFi 980 (FWA 4G) Column 18: Total FWA 5G calculation: Total (Col 18) - 4G (Col 1) ### 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 ### Excel Structure Single Sheet: "Weekly Report" Sections: Report Header (Rows 1-2) Title: "Retail Trade Weekly Report" Date ranges: "Last Week: [dates] | This Week: [dates]" Channel Summary (Rows 4-9) Columns: Channel | Program name | This Week ADA | WoW | MoM Rows: DRP BAU, DRP TECNO, License Store, DXS, RT Total 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 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 Mobile Postpaid by Region (Rows 35-45) Same structure as Mobile Prepaid FWA 4G by Region (Rows 47-57) Same structure as Mobile Prepaid FWA 5G by Region (Rows 59-69) Same structure as Mobile Prepaid ### Formatting Rules Number Formatting ADA values: Integer format with thousand separators (e.g., "1,876") WoW percentages: Integer percentage (e.g., "21%", "-13%") Small ADA values (< 10): Show 1 decimal place (e.g., "0.6", "2.9") Color Coding WoW Positive (>0%): Green text (#008000) WoW Negative (<0%): Red text (#FF0000) WoW Zero (0%): Black text WoW N/A ("-"): Gray text (#808080) Cell Styling Headers: Bold, centered, light gray background (#F0F0F0) Region names: Bold Total rows: Bold, light blue background (#E6F2FF) Borders: Thin borders around all data cells ### Charts Chart 1: Channel Performance Comparison Type: Clustered Column Chart Data: This Week ADA by Channel (DRP BAU, DRP TECNO, License Store, DXS) Position: Right side of Channel Summary section Size: 6 columns wide x 15 rows tall Chart 2: Regional Mobile Prepaid Distribution Type: Stacked Column Chart Data: DRP ADA, DXS ADA, LS ADA by Region Position: Right side of Mobile Prepaid section Size: 6 columns wide x 15 rows tall Chart 3: WoW Trend - Top 3 Regions Type: Line Chart with Markers Data: WoW % for top 3 regions by RT Total ADA Position: Below main tables Size: 12 columns wide x 12 rows tall ### 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}") ### 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: All 12 input files identified and processed Date ranges correctly extracted and displayed All stores mapped to regions (log unmapped as "Others") All WoW calculations completed No negative ADA values (except in error logs) All formulas validated against sample data Charts render correctly Color coding applied to all WoW cells Total rows sum correctly Output file opens without errors ### Performance Considerations Expected processing time: 10-30 seconds for 12 files Memory usage: ~50-100 MB Large file handling: Files up to 10MB each supported Concurrent processing: Process files in parallel where possible ### Common Issues Issue: "File not found" error Solution: Verify all 12 files are uploaded and filenames match expected pattern Issue: Store name not mapping to region Solution: Check mapping CSV for typos, add aliases for common variations Issue: WoW showing "N/A" for all values Solution: Verify previous week files are correctly identified (earlier dates) Issue: Charts not displaying Solution: Check openpyxl version >= 3.0, verify chart data ranges Issue: Negative ADA values Solution: Check source data for errors, verify column indices ### Version History v1.0 (2026-02-02): Initial skill creation Support for 12-file weekly report generation WoW calculations with color coding Store-to-region mapping with aliases Three chart types for visualization ## Trust - Source: tencent - Verification: Indexed source record - Publisher: wuminmin - Version: 1.0.0 ## Source health - Status: healthy - Source download looks usable. - Yavira can redirect you to the upstream package for this source. - Health scope: source - Reason: direct_download_ok - Checked at: 2026-04-23T16:43:11.935Z - Expires at: 2026-04-30T16:43:11.935Z - Recommended action: Download for OpenClaw ## Links - [Detail page](https://openagent3.xyz/skills/retail-trade-report-generator) - [Send to Agent page](https://openagent3.xyz/skills/retail-trade-report-generator/agent) - [JSON manifest](https://openagent3.xyz/skills/retail-trade-report-generator/agent.json) - [Markdown brief](https://openagent3.xyz/skills/retail-trade-report-generator/agent.md) - [Download page](https://openagent3.xyz/downloads/retail-trade-report-generator)