Automating The Trade Report Process
I really like automating processes and Open Source software is great for that. Here is one such incident.
Background: when you are managing other peoples’ stock portfolios, you don’t just login to E*Trade as them and buy/sell stocks. You trade the stocks through a trading system (like E*Trade but there are many others), the trading system sends you a report of the trades you made, then you contact the client’s bank (called the “custodial bank”) with the trade information.
Of course, every trading system has a different report format: one is a CSV, the other is in Excel format; one system will denote A stocks with ‘-A’ and another with ‘.A’, and so on. Then the money manager (or her flunky) needs to review and sign each page. Each custodial bank has their own format in which they will accept the trade information and the data must be faxed, not sent over the Intrawebs. Finally, the information has to be stored locally and sent to the back office for processing.
That is a lot of room for errors, human and otherwise!
My project was to make this whole process as painless as possible for my client, the money manager. Except for the faxing part (which we never got around to doing), I made it very painless. Here’s what I did:
I created an icon on her desktop that she could drag-n-drop the trade report onto. The icon was for a batch file that SCPed the file to the server and then executed a Perl program via SSH.
The Perl program used the very handy Spreadsheet::ParseExcel to parse the spreadsheet and Spreadsheet::WriteExcel to generate a new spreadsheet. While these two modules worked great, there was one ‘gotcha’ that took me awhile to figure out, namely, once you have wrtiten the spreadsheet with WriteExcel, you can’t modify it! It is a write-only “medium” so any processing you need to do (like modifying a cell table) has to be done after you ParseExcel and before you WriteExcel.
With that caveat in mind, it was pretty simple to create different output formats depending on the destination bank; I put all of the differing information into a hash. I also put the spreadsheet into landscape mode and added a fax cover sheet. Done!
The PITA of the whole press was adding the money manager’s signature to the file. Cleaning up a scanned version of her signature, getting it sized right and placed appropriately on each page took about five hours. It probably would have taken less time if I knew what I was doing in the GIMP but there it is.
We never did get the faxing part done; that was simply going to be emailing a PDF or TIFF to an eFax account.
The client was pretty impressed with how well everything worked out; she dragged a file onto an icon and a few minutes later it printed out multiple copies in different formats each with a filled-out fax cover sheet and her signature already on each page.
Tags: automation financial software Perl
