Data engineering · Pharma distribution
W-Connection — business process automation
30 Excel formats become one clean database. A corporate system automating reporting and business processes: sales, distribution, plan-vs-actual, KPIs and an AI consultant over company data.
View the case
What we have to deal with
- Industry
- Corporate sector
- Status
- In production
- Networks in the registry
- 30
- Parsers
- 18
- Months in the database
- 7
- Tables in the schema
- 14
Challenge
Company staff spent days manually assembling sales and distribution reports from different systems and spreadsheets, plan-vs-actual and KPIs were computed with errors and delays, and answers about the state of the business had to be chased across departments.
Solution
We built W-Connection — a single loop for the company's business processes: sales reports, numeric distribution, order allocation, plan-vs-actual and employee KPIs are assembled automatically, dashboards update in real time, and a built-in AI consultant answers employees' questions drawing on all company data.
The pipeline
A month imports in one click
The analyst drops in a month's folder and clicks a button. The system does the rest: parsers process every network, dictionaries enrich the rows, sanity checks catch anything suspicious. Run the import:
One network will change its file structure, and another has no parser
- Неман·
- Фармамир·
- АМТ·
- Саян·
- Эрай·
- Лекарь·
- Медсервис·
- Линь·
- Бимед·
- Заман·
0
rows in the database
The core principle
AI writes the code. The code processes the data
Numbers never pass through a language model and cannot be distorted by it. Only a description of the file structure goes into the prompt, and the finished parser runs deterministically at no cost.
Only when a format changes
The prompt gets the sheet structure, not the numbers
Every month
Deterministic, free, with a control reconciliation
01
A structure fingerprint
Every file is hashed by structure. If a network sends its report in a new shape, the system catches it before import and offers to generate a parser.
02
A parser is written once
From then on it processes files for months with no tokens and no surprises. AI joins only for new formats and name matching.
03
Numbers come from cells only
A project rule: no clever guessing in parsers. Every value is read from a specific cell and verified with checksums.
Data quality
Data quality is confirmed by reconciliation
The bar for a parser is strict: match the analyst's manual totals down to the last unit. All 18 parsers pass that check.
9,637
unmatched rows before the unification dictionary
252
left after rollout: a 99.9% match rate
Reconciled to zero
A parser without a control reconciliation against the reference is not considered done. That's a project rule, not a wish.
Sanity before writing
Volumes are compared with the previous month; foreign products and new structures are flagged before the data lands in the database.
Retroactive improvements
A late parser fix cleans already-imported months retroactively: one fix cut about 5,000 junk rows across three past months.
Reports
Consolidated analytics across all networks
Summary reports across all connected networks are built from the clean database in seconds: sales, month-over-month dynamics, stock, expiry dates and plan-vs-actual.
The system in numbers
fact rows in the database
networks in the registry
parsers with reconciliation
pharmacies in the dictionary
product names
files processed
Under the hood
The engineering behind it
Three Excel engines
openpyxl, xlrd and calamine: real files arrive corrupted, and a 1C xlsx without sharedStrings breaks the standard reading library.
18 tests against the reference
One test per parser: aggregates are checked against the analyst's manual totals. Every parser change runs through the whole suite.
A single-file database
A 763 MB SQLite file holds 2.1M rows and all the dictionaries. The user launches the system on Windows in two clicks, no servers.
A product UI on top of Streamlit
A dark sidebar, KPI cards with sparklines, status chips. Interface screenshots are verified automatically with Playwright.
Result
Preparing regular reports went from days to minutes, data stopped diverging between departments, and employees get business answers from the AI consultant in seconds instead of email threads and meetings.
Next project
W-CRM — CRM for medical representatives