HR Vendor Report Converter

Problem

Compensation and benefits vendor reports often come from Aon, Mercer, WTW, KornFerry, and IPAS with inconsistent field names, job codes, percentile notation, units, and regional definitions. Internal HRIS or comp analyses usually require a fixed template such as X Job Code, Base 50th, and TTDC 65th; manual alignment can drop fields, misalign jobs, or break on Mercer dual-row headers and Aon newline headers.

How It Works

The skill first checks for mapping-config.json or an Excel mapping workbook. If absent, it runs a first-time setup and normally asks for:
- Output template: defines the target columns and internal format;
- Field mapping table: maps internal fields to vendor fields;
- Job matching table: bridges company job codes to vendor job codes;
- Aging factor table (optional): adjusts market data by country.

It treats company master data as the base table, joins vendor report data via LEFT JOIN, then applies configured renaming, job matching, percentile normalization, K/M/B unit handling, missing-value -- output, and validation. If script dependencies are unavailable, it can fall back to direct Excel processing using the same configuration.

Boundaries

It is intended to convert vendor Excel reports into a standardized internal Excel format, not to make compensation decisions. Vendor job matching is not strictly one-to-one, so job descriptions should be checked; Mercer India STI definitions can differ from Mercer Global; - and empty values in mapping tables are treated as unavailable, while missing source values are output as --.