When you're overseeing a fleet of 60+ trucks across Europe, real-time transport coordination can feel like playing 3D chess… blindfolded. Especially when you're juggling:
- live vehicle tracking
- adherence to strict driver rest times
- punctual deliveries
- and not waking drivers up at 3AM because dispatch forgot they were sleeping
Initially, we tried to manage all of this manually. That included… yes:
👉 brainstorming 3-shift dispatch coverage
👉 manually checking on drivers
👉 setting reminders not to forget to wake up drivers who had to roll out before sunrise
It was clear we needed automation, but without the budget for a complex TMS upgrade, we went the practical route:
🔧 Build a pilot solution using Excel, Power Query, VBA, our GPS system (WebEye), and external routing APIs.
🎯 Our objective was clear:
Track whether each vehicle can meet its next pickup/drop-off deadline — and alert dispatch before delays happen.
🧩 How we did it:
- Extract transport tasks from our TMS into a .CSV file (no live interface available).
- Use Power Query to clean and load the relevant data: license plate, locations, scheduled times, customer info.
- Pull real-time GPS coordinates from WebEye API using authentication and match them to transport tasks.
- Match pickup/drop-off city coordinates using Google Maps API and store them in a dynamic reference sheet.
- Route planning via Google Maps initially – later replaced by OpenRouteService, which supports truck-specific routing.
- Calculate ETA vs. target time to flag risky deliveries and visualize them in a clear dispatcher dashboard.
- Add macro-based buttons to refresh only what’s needed: data, GPS, route, etc. And yes, even an auto-refresh loop for a wall display in the control room.
📉 What went wrong (and how we fixed it):
- Google Maps ETA was inaccurate for 40-ton trucks → switched to OpenRouteService for better truck routing.
- Drivers occasionally changed task order → detected via location mismatch, resolved with a quick call.
- Excel performance slowed down with large data → optimized queries and modularized updates.
📈 What we gained:
- Early warnings for risky deliveries
- Fewer missed deadlines
- No need for night-shift dispatchers
- Real-time visibility at a fraction of a commercial solution's cost
- And surprisingly... fewer grumpy drivers 😄
This pilot showed that even with limited tools, you can build a functional visibility layer, improve planning accuracy, and reduce stress on dispatchers. It’s not perfect, but it proved the value of automation and paved the way for smarter investment decisions down the line.
👉 Have you built something similar? Or are you also riding the fine line between "Excel wizardry" and full-blown software? I'd love to hear your war stories.