ETC Toll-Card Executive Dashboard in Excel

An Excel template for turning raw ETC toll-card (Electronic toll collection) logs into a clear, decision-ready view of spend, routes, vehicles, and drivers.

What changed

The starting point was a flat export: long transaction rows, inconsistent card formats and no dependable filter context. The rebuilt model separates raw input, mapping and cleaned data, then connects both the dashboard and report to the same refreshable model.

Before

• Raw BINA_IMPORT transaction logs were difficult to scan.

• Leading zeros in card numbers caused vehicle matches to fail.

• Saved totals could become stale after new data was pasted.

• The report was not connected to the dashboard filter context.

After

• 224 recognized records: 204 passages and 8 deposits in the sample.

• €478.00 deposited and €463.11 spent, reconciled from cleaned data.

• Month, Vehicle and Card slicers drive the spend visuals.

• An A4 report follows the active selection and exports as a one-page PDF.

Dark executive dashboard: the one-screen view for daily decisions.

How the model works

Power Query keeps the observed source fields, parses Croatian dates and comma or dot decimals, converts costs to positive expense values, normalizes every ETC card to 12-character text, joins the card to vehicle and driver master data, and removes exact duplicate fingerprints. A maintained correction table handles observed place-name encoding defects.

1. Paste the next complete BINA report below the existing Excel Table.

2. Confirm the VOZILA mapping table contains the current card, plate, model and driver values.

3. Select Refresh All, or use the workbook refresh button.

4. Review the quality status, then use the slicers to explore spend.

A4 report for sharing

The light report is designed for a clean landscape A4 print. It keeps the active filter context, financial KPIs, passage count, monthly trend, top routes, vehicle summary and refresh timestamp together on one page.

A4 report proof: a static, print-ready snapshot of the current filter selection.

Setup in four steps

1. Open the macro-enabled workbook in Microsoft 365 Excel for Windows.

2. Enter the required opening balance in the named input before using balance or PDF export.

3. Paste new BINA_IMPORT rows at the bottom of the source table.

4. Refresh, filter, and export the current report selection when needed.

Methodology

Total Deposited follows Month only so it stays account-level under Vehicle and Card filters. Total Spent responds to Month, Vehicle and Card. Current Balance is the overall opening balance plus all deposits minus all spending, so it remains independent of slicers. If the opening balance is blank, the workbook shows a clear warning instead of inventing a balance.

One clean source, one shared model, two decision-ready views.

Built with Codex

The rebuild combined spreadsheet authoring, Excel dashboard visual design, data-quality analysis, dashboard and visualization workflows, plus PDF rendering and layout QA. The result is a practical template that stays editable in Excel instead of becoming a static screenshot.

What is included (by request)

• Dark executive dashboard with KPI cards, transparent charts and filter context.

• Synchronized Month, Vehicle and Card slicers.

• Refreshable Power Query cleaning and mapping pipeline.

• Append-friendly source tables and quality checks.

• Interactive IZVJESTAJ_PDF sheet configured for A4 landscape export.

• VBA actions for refresh, filter reset and PDF export.

Use the template as a starting point for your own ETC, fleet or road-toll reporting workflow. The sample values above are shown for illustration and should be rechecked after each refresh.