Custom Software Apps & SharePoint Consulting

Trade Data Capture and Validation

A Case Study in Application Integration

The Problem

This client is one of Texas’ largest wholesale energy brokers.

Prior to engaging Entrance Software, this client used QuickBooks to capture deal information, invoice customers, and calculate the commission splits for brokers.
The process required manually computing the commission and broker split for several hundred deals each month, each with at least two counterparties and multiple brokers.

Once the calculations were complete, commission and invoice information was exported from QuickBooks into an Excel spreadsheet. This Excel spreadsheet was imported into the company’s Solomon accounting system using Solomon’s existing import functionality.

The error rate from this process was so high that they lost tens of thousands of dollars a week to billing errors, data re-entry, and time spent handling customer complaints.

The Solution

Our client was not ready to completely replace the QuickBooks based system, but needed a way to reduce the number of data-entry errors that the system created.

Entrance Software developed a .Net-based utility that reads an Excel spreadsheet generated each week from QuickBooks. The utility checks each field in the exported Excel spreadsheet for “sanity” (“is this dollar value within the expected range”), validity (i.e. is this date in the correct format), and completeness. The application also computes the commission split and verifies that the total commission paid to brokers equals the total commission due to the company. Errors are logged and tagged for manual review.

Once the data is validated, the utility uploads the entire set of invoice and commission splits to Solomon.

The Results

Entrance Software designed and wrote this application in one month. The new, automated process dramatically reduced the number of errors on customer bills, the number of errors introduced into the accounting system, and the amount of time spent importing and correcting accounting data each month.

Company management reports that this project paid for itself in the first month. Direct savings amounted to over $30,000 of billing errors caught in the first month. The increase in customer satisfaction is harder to measure, but no less real: over 100 errors were caught before being sent to customers.

Share this post with your friends

Skip to content