IEX data-prep automation

The question

Every scheduling cycle starts with data from NICE IEX: the forecast and the staffing requirements that follow from it. Before a schedule can be built, every scheduler has to export that data, break it up, clean it and arrange it into the layout schedules are planned in, by hand, every cycle.

The question was whether that preparation could be automated, so schedulers spend their time on scheduling decisions instead of spreadsheet work.

Data & constraints

• Inputs: IEX exports of forecast and requirement data. They arrive as one large block of data that always needs work before it’s usable.
• Tools: Excel only. The tool had to run where schedulers already work, with nothing new to install.
• Users: schedulers, not developers. It had to be usable without knowing how it works inside.
• Confidentiality: no volumes, requirements, team names or screenshots are shown here.

Approach

• Power Query imports the raw export and does the cleaning that used to be manual, so every step is recorded and repeatable.
• VBA does the rest: it arranges the cleaned data into the layout schedulers need and runs the steps in order, so preparation is a single run instead of a manual rebuild.

I chose Excel because that’s where the schedulers already work: no new software, and the logic stays visible to the people who use it.

Result

My time per scheduling cycle fell by 70%, measured on my own cycles before and after the tool.

It hasn’t been rolled out to the other schedulers yet, so the result so far is for one person. The preparation is now the same every cycle instead of depending on who does it.

Limitations & next steps

• Measured on one person so far. Next: roll it out to the other schedulers and measure the time saved across the team.
• It depends on the IEX export layout. If the export changes, the queries need updating.
• It lives in Excel, so versions are managed by hand.
• Later: move the preparation into a database with SQL, which I’m learning, and report on it in Power BI.