Tencent SkillHub · Data Analysis

retail-trade-report-generator

Generates a consolidated weekly retail trade report by processing 12 Excel sales files, mapping stores to regions, calculating ADA metrics, WoW comparisons,...

skill openclawclawhub Free

0 Downloads

0 Stars

0 Installs

0 Score

High Signal

Generates a consolidated weekly retail trade report by processing 12 Excel sales files, mapping stores to regions, calculating ADA metrics, WoW comparisons,...

⬇ 0 downloads ★ 0 stars Unverified but indexed

Install for OpenClaw

Quick setup

Download the package from Yavira.
Extract the archive and review SKILL.md first.
Import or place the package into your OpenClaw setup.

Requirements

Target platform: OpenClaw
Install method: Manual import
Extraction: Extract archive
Prerequisites: OpenClaw
Primary doc: SKILL.md

Package facts

Download mode: Yavira redirect
Package format: ZIP package
Source platform: Tencent SkillHub
What's included: README_CN.md, SKILL.md, scripts/retail_trade_report_generator.py, references/store_mapping.csv

Validation

Use the Yavira download entry.
Review SKILL.md after the package is downloaded.
Confirm the extracted package contains the expected setup assets.

Install with your agent

Agent handoff

Hand the extracted package to your coding agent with a concrete install brief instead of figuring it out manually.

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.

New install

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

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.

Open Send to Agent page Open JSON manifest Open Markdown brief

Trust & source

Release facts

Source: Tencent SkillHub
Verification: Indexed source record
Version: 1.0.0

Provenance

Publisher: wuminmin
Source page: View original listing
Canonical URL: Open canonical page

Documentation

ClawHub primary doc Primary doc: SKILL.md 23 sections Open source page

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

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

Category context

Data access, storage, extraction, analysis, reporting, and insight generation.

Source: Tencent SkillHub

Largest current source with strong distribution and engagement signals.

Package contents

Included in package

2 Docs1 Scripts1 Files

SKILL.md Primary doc
README_CN.md Docs
scripts/retail_trade_report_generator.py Scripts
references/store_mapping.csv Files