Automated traffic-data reporting

The business need

A traffic-reporting professional needed reports from measurement files containing individual vehicle observations: time, direction, speed and vehicle class. The output had to follow an existing Excel layout and include a combined overview across measurement sites.

The calculations needed clear definitions, including how different periods and directions contribute to each percentage and speed statistic.

What I built

I built scripts that parse and normalize measurement files, calculate traffic metrics and generate an Excel workbook for each site. A related converter prepares another source’s CSV data in the required input format.

A separate summary script collects selected figures from the site reports into one cross-site workbook. The output preserves the report templates and localized labels used by the report preparer.

How the logic works

Turn observations into the required reporting periods

The reports group vehicle observations into 15-minute, hourly and daily tables, with weekday, weekend and weekly summaries. They include direction and vehicle-class counts, heavy-vehicle shares, average speeds and speeding measures relative to the supplied speed limit.

Calculate each statistic from the right observations

Period percentages use the relevant combined counts, rather than a simple average of daily percentages. A heavy-vehicle share for one direction uses total traffic in that same direction.

The 15th, 50th and 85th speed percentiles for a period are calculated from pooled vehicle speeds. Averaging daily percentile values would describe a different statistic.

Challenges handled

Input formats differ in their dates, direction labels and vehicle classes. Reporting intervals start from the measurement header, so the first interval does not depend on when the first vehicle was recorded.

The reporting logic accounts for incomplete weekday coverage and distinguishes certain unmeasured summary periods from measured zero traffic. The summary reader also handles named worksheet values and older report layouts without confusing their cell positions.

What the system provides

The scripts generate per-site reports and a combined key-data workbook with defined statistical calculations. The reporting script was in use, with further direction-specific measures and calculation refinements requested afterward.

The outputs are Excel workbooks for the report preparer to share with clients. The scripts work from measurement files and existing report templates.