Accounting System Integration

There is great efficiency to be found in moving data from one system to another without requiring human intervention. As such, a common request from clients is to automate the entry of data from one system into the client’s accounting system.

1) Load data from a title and escrow company’s transactional system into QuickBooks,
2) Load data from time and attendance system into payroll, and
3) Load sales and labor data from POS into accounting and payroll systems.

Skills: Project management, “automation by any means necessary”
Tools: SQL, Windows scripting, QuickBooks, MS Office VBA

Industry Group Reporting

A trucking and logistics client was a member of an industry group that shared some operational data among the members. This data took several days for an employee to compile from various reports, queries, and other sources around the company. They were looking for a way to compile that information more quickly and efficiently.

Harkening back to my Excel programming days at McDonald’s, I built a automated the spreadsheet to compile the data at the click of a button. There were several data points that still needed to be manually figured and entered. But the result was many saved hours of work on a monthly basis.

Skills: MS Office automation, database queries
Tools: Excel, MS Office VBA, SQL

Daily Operational Reports

A retail client with 10+ locations required their store managers to run some sales reports from the POS daily and fax them into home office. The data from those faxes was then keyed into a spreadsheet which was then distributed to management. The POS vendor was polling the stores daily and summarizing the data in a warehouse that was available for reporting. The problem was that the reporting tool was very inflexible and couldn’t summarize the data in a useful format.

During conversations with the POS vendor I discovered that they could replicate the sales data to an internal SQL Server database. Once we initiated that process it was just a matter of building the operational reports using SQL Server Reporting Services. A simple scheduler program distributed the reports daily to everyone that needed them.

Skills: Business analysis, database design and development, reporting
Tools: SQL Server, SQL Server Reporting Services

3rd Party Integration

Multiple projects over the years have involved integrating my clients’ systems with vendors. Each project is different but follows a very similar pattern:

1) Define the data needed and the format of the exchange,
2) Build whatever tools are needed to facilitate the exchange,
3) Test the exchange, and
4) Setup ongoing monitoring tools to ensure the exchange is successful.

I’ve setup exchanges with multiple banks, an LMS vendor, and even a marketing agency. Each effort improves the capabilities of the client without requiring additional staff attention.

Skills: Project management, systems integration, database design and development
Tools: SQL Server, FTP, web services, Windows batch file scripting