Motor carriers running Truckmate, McLeod, TMW Systems, or custom SQL Server-based transportation management systems face recurring manual labor every Monday at 4:00 PM Eastern when the Department of Energy releases its weekly diesel fuel price report. Strong Tower Consulting has designed and implemented production-grade automated fuel surcharge (FSC) indexation engines that eliminate this weekly ritual while ensuring 100% compliance with shipper contracts and accurate driver settlement calculations.
Our architecture ingests the official EIA OpenData API, maps national and PADD-level ultra-low sulfur diesel prices to carrier-specific multi-tier tariff tables, recalculates every open load and invoice, and posts updated line-haul and FSC amounts through EDI 210 transactions without human intervention.
The Business Problem
Most carriers still rely on spreadsheet exports from the DOE website, manual data entry into their TMS fuel surcharge tables, and batch rating jobs that run only when someone remembers to trigger them. This creates three costly problems:
- Revenue leakage when FSC is under-applied on outbound freight bills
- Overpayment to owner-operators when settlement FSC does not match billed FSC
- Audit exposure when shippers demand proof that the exact published DOE price was used on a given load date
Core Architecture Components
The solution is built as a scheduled, idempotent Microsoft SQL Server Integration Services (SSIS) package or Azure Function that executes at 4:15 PM Eastern every Monday. The process follows these steps:
- EIA API Ingestion: Secure HTTPS GET request to the official DOE EIA OpenData endpoint (
https://api.eia.gov/v2/petroleum/weekly/diesel/data/) using the carrier's registered API key. The payload is parsed and stored in a normalized staging table with columns for report_date, padd_region, price_per_gallon, and source_api. - PADD-to-Region Mapping: A configurable mapping table translates the five Petroleum Administration for Defense Districts (PADD 1A, 1B, 1C, 2, 3, 4, 5) plus national average into the carrier's specific pricing regions. This table is maintained by operations and supports both flat national FSC and lane-specific regional FSC.
- Tariff Matrix Application: A multi-tier FSC rate table stores the carrier's contractual brackets (e.g., $0.00-$1.49 = 0%, $1.50-$1.99 = 18%, $2.00-$2.49 = 22%, etc.). The engine performs a range lookup and calculates the exact FSC percentage or per-mile adder that applies to each load based on the fuel price effective on the load's ship date.
- Touchless Recalculation: Open loads, dispatched loads, and unbilled loads in the TMS (Truckmate, McLeod, or TMW) are identified via SQL views. The rating engine is called in batch mode to re-rate only the fuel surcharge line item while preserving the original line-haul rate. Updated values are written back to the load and billing tables.
- EDI 210 Regeneration: For loads already transmitted via EDI 204/214, an updated 210 is generated with the corrected FSC amount and a reference note indicating "FSC adjusted per DOE EIA weekly report of [date]".
- Driver Settlement Sync: The same FSC percentage is applied to owner-operator and company-driver fuel surcharge calculations so that settlement statements match customer invoices exactly.
| Fuel Price Range | FSC Percentage | FSC Per Mile (at $2.80 base rate) | Application Rule |
|---|---|---|---|
| $0.00 - $1.49 | 0.0% | $0.000 | Base rate only |
| $1.50 - $1.99 | 18.0% | $0.504 | Applied to line-haul |
| $2.00 - $2.49 | 22.0% | $0.616 | Applied to line-haul |
| $2.50 - $2.99 | 26.0% | $0.728 | Applied to line-haul |
| $3.00 and above | 30.0% + $0.02 per $0.10 increment | Variable | Escalator clause |
Integration Points with Common TMS Platforms
The architecture has been successfully deployed against:
- Truckmate: Direct SQL Server writes to the LOAD and BILLING tables with subsequent call to the native rating service.
- McLeod LoadMaster: Utilization of the McLeod API and stored procedures to trigger the rating engine and update the Order Header and Order Detail tables.
- TMW Suite / Trimble: Integration through TMW's Enterprise Framework and custom SQL Server views that feed the optimization and billing modules.
EDI transaction sets (204, 214, 210) are generated through the carrier's existing translation software (SPS Commerce, TrueCommerce, or in-house Gentran/IBM Sterling) using updated data from the TMS.
Independent by Design
Independent by design. Strong Tower Consulting, LLC and our sister company Exspeedite LLC are separately organized entities under common ownership. We never require you to purchase or license any software to receive our consulting services. The architecture described above can be implemented inside your existing TMS environment using only the tools you already own.
Operational Results
Carriers who have deployed this solution report:
- Complete elimination of four to six hours of weekly manual FSC maintenance
- Reduction in revenue leakage of 0.4% to 1.1% of gross revenue
- Zero disputes with shippers regarding fuel surcharge calculations during the past two years
- Perfect alignment between customer invoices and driver/owner-operator settlements
FAQ
How do you handle fuel surcharges when a load ships on Sunday but the new DOE price is published Monday?
Our architecture stores the weekly price with its effective date (typically the Monday of publication) and uses a carrier-defined lag rule. Most clients apply the new price to all loads with a ship date on or after the Tuesday following publication. The mapping table allows different rules per customer contract.
Can this solution support both national average and regional PADD-based fuel surcharges simultaneously?
Yes. The system maintains separate price series and tariff matrices. A load is evaluated against the pricing region assigned to its origin or destination terminal. This is particularly valuable for carriers with significant East Coast versus Midwest or California operations.
How does the system handle detention, layover, and accessorial charges that reference the same fuel index?
The recalculation engine identifies all charge codes linked to the FSC index through a metadata table. Any charge configured with an FSC multiplier is automatically updated during the Monday evening batch process, ensuring consistency across the entire invoice.
What happens if the EIA API is unavailable on Monday afternoon?
The process includes retry logic with exponential backoff. If the API remains unavailable, the system falls back to the most recent successful price and flags the run for review. An alert is sent to designated operations and IT staff. Historical prices are retained indefinitely for audit purposes.
Strong Tower Consulting has been helping motor carriers optimize their technology and processes since 2008. Our deep experience with Truckmate, McLeod, TMW, SQL Server data models, and transportation-specific accounting gives us the ability to deliver production-ready fuel surcharge automation that survives TMS upgrades and contract changes.
Ready to eliminate weekly fuel surcharge maintenance and capture every dollar your contracts entitle you to? Start with a free architecture assessment or contact us at 763-244-1373.