Diamond stock aggregation: 2.5 hours of Excel down to 2 minutes
Automation that merges supplier stock files with mismatched columns, applies margins, filters to each client's specs, and reports on what is not selling.
What was breaking
- Every supplier sent stock in their own Excel format. Column names, ordering and units differed on every file, so merging them was manual work before any selling could happen.
- Margins had to be applied by hand after the merge, then the list filtered down to what each individual client had asked for.
- The whole cycle took about 2.5 hours and had to be repeated every time supplier stock changed, which meant clients were often quoted from a stale list.
- Nobody could see how long a stone had been sitting. Slow-moving inventory was invisible until someone went looking for it.
- Part-payments were tracked in memory and in messages, so overdue balances were found late.
How we approached it
Normalised the supplier files
A mapping layer reads each supplier's format and resolves it to one internal schema, so mismatched column names and orderings stop being a manual problem.
Automated margins and client filters
Margin rules are applied on merge, and each client's spec — shape, size, colour, clarity, price band — is stored as a filter that produces their list on demand.
Added inventory age analytics
Every stone carries its intake date, so the system reports what has been held longest and what that represents in tied-up capital.
Tracked time-to-sell by cut
Sale dates are measured against intake dates and grouped by cut, turning a gut feeling about what moves into a number the buying decision can use.
Flagged overdue part-payments
Payment schedules are recorded against the sale, and anything past its due date is flagged automatically rather than remembered.
Results
- Stock aggregation went from roughly 2.5 hours to under 2 minutes, so client lists are generated from current stock instead of yesterday's.
- Ageing analytics surfaced 12 stones held 6+ months, representing roughly Rs 14 lakh in locked capital that had not been visible before.
- Average time-to-sell is now tracked by cut: rounds around 14 days, fancy cuts around 67 days, which changes what is worth buying.
- Overdue part-payments are flagged by the system instead of being chased from memory.
Built with
Timeline
Built around the aggregation bottleneck first, analytics added once the data was clean
Got a similar problem?
We do this as ai automation work. Tell us what your process looks like today and we will tell you what it would take.