Credit Card Transaction Import Automation – Part 1

I have this great client (Champion Technologies out of Baton Rouge, LA) giving me some very interesting projects lately. They are in the business of automation, and have recently asked me to automate their credit card transaction import process.

This will be a multi-part blog entry describing the process we go through in automation process.


Initial conversation:
We had an extended gotomeeting (90 minutes) to talk about what needed to be done for this project. Since the client had already built a partially automated solution for this using a combination of excel spreadsheets (we love those don’t we?), macros, and some concatenated sql statements in text files… he had a pretty good idea of the requirements for the process. He walked me through the basics of what is already being done.

The first thing I do when attacking a project like this is go back through the recorded gotomeeting and take notes. Here are some of the notes I’ve taken:

Big Picture Requirement:
1. accounting user downloads a csv file from the credit card company and puts it in a designated folder somewhere on the internal network

2. accounting user clicks some kind of button (custom info centers will come in handy for this one!)

3. the process sucks up the file and imports it into Vision employee expense reports, exposing those reports to employees as “in progress” so they can go in and fill in the details on each transaction

4. once completed (the details are filled in) a report will be run to make sure that all transactions in the csv file(s) have been imported and have details added to them. Once all transactions are ready, the expense reports can be submitted and posted.

5. when the credit card bill is paid by the company, the automated entries by Vision for the payment are removed, and replaced by a detailed set of transactions that go down to the individual project level, possibly transaction level. This will show cash outflows by project for those expenses.

Some of the implied and explicit “gotcha” requirements:
– if a user imports a csv file twice, make sure that those transactions are not duplicated
– if a user imports once single transaction twice, make sure it’s not duplicated
– if a user splits an expense entry into two so they can share it between two or more projects… make sure the reconcile still works
– what happens if the accounting department un-posts the individual expense transactions? do they unpost the payments as well?

— end —


Comments or questions for Loren?

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s