EXAMPLES OF PROJECTS

1. Conceived, designed and wrote a VBA / Reflection Basic program that uploads journal entries from Excel into a mainframe accounting system (ADP with Reflection terminal emulator), by simply pressing an Excel toolbar button. The program is in wide use throughout the company. It is simple to use, extremely flexible, and contains strong control features.

2. Created an Excel management tool that monitors performance of the Canadian and US fixed income desk. It tracks bond purchases and sales, calculates accrued interest and automatically records coupon interest received and paid. Repo and reverse repo financing expenses and incomes are also tracked, as are gains and losses on bond futures contracts.

Because the system is able to track figures on both trade date and settlement date bases, management is provided with an accurate matching of revenues and expenses not previously available. Likewise, figures are accurately reconciled to accounting records for the first time.

The user now spends five minutes entering data manually compared to as much as several hours in a previous model. VBA routines do the rest, including extracting necessary information from text reports, creating a daily file of unsettled trades, preparation of daily balance sheet figures, margin calculations, income and expenses, reconciliation to accounting records, and preparation of reports (including sophisticated pivot tables) for management.

3. Analyzed the integrity of an SQL database of all transactions processed by ADP since inception of the company. Developed Access queries to identify extraneous as well as missing transactions in the database.

Learned the layout of ADP's text files from which the SQL Server was being loaded. Determined the reasons for the extra and missing transactions. Prepared a written technical report of my findings, which explained in detail how the system was currently working and what needed to be corrected, including necessary revisions to the new loading rules.

Supervised the correction process. This was a complex study which was well received by the client. Data integrity tests prove that the new loading rules are working.

4. Conceived, designed, programmed and implemented several Excel, Access and VBA solutions for tracking and reconciling clearing and inter-company accounts. The VBA programs query an SQL Server database to extract current balances and transactions, then combine them with unreconciled transactions being carried forward.

They manipulate advanced Excel features so that the files are set up for exceptionally easy reconciliation. Control totals, combined with conditional formatting, allow the users to immediately detect if matched items do not balance and if important information is accidentally removed.

Users have been very happy with the performance of these systems, which have saved several hours per day compared to earlier manual methods.

5. Designed the process that keeps the general ledgers of separate companies in balance in an accounting system that does not recognize individual companies. This involved writing VBA custom functions that identify to which company particular accounts belong and using Microsoft Access queries that indicate when the companies go out of balance.

Then a flexible and systematic procedure was developed to identify which transactions cause out of balances.

6. Developed a month end process that identifies any transactions that cause the trade date adjusted trial balance to be out of balance, and reconciles it to the settlement date trial balance. A VBA routine queries the SQL Server database to extract the unsettled trades and backdated entries that make up the difference in the two sets of figures.

Because the accounting system backdates entries based on fairly complex rules that depend on a combination of batch code, date and description, a VBA custom function is used to identify them.

The program opens a text file that contains the adjusted trial balance and translates it into Excel. Then it combines the ATB, the trial balance, the unsettled trade and the backdated transactions into one Excel file. It ensures that the four sources of data are consistent with each other.

Finally it determines each currency where any company is out of balance and prepares a pivot table for that currency ready for the user's final reconciliation. The entire process used to take several hours and is now done in a few minutes.

7. Trained the current users of all the aforementioned projects.