Complex PDF Paperwork Automation Project
One of the most complex functions of scripting in the PDF environment is the popup dialog window, but it also the one of the most useful and fulfilling functions for automation tasks.
Every independent investment advisor was required to work under an investment dealer to satisfy compliance and supervision regulations. Investment dealers would try to convince advisors to move to their dealerships by presenting superior value propositions. While moves were very tempting for advisors, client retention and revenue stream interruptions were major concerns.
Take the Acrobat Pro/JavaScript eLearning Course Acrobat Like a ProYou see, while nobody "owned" the clients and they were free to do business with any firm they chose, their accounts stayed with the current dealer after the advisor moved on. It was up to advisors to convince their clients to follow them to the new dealer and they were prohibited from discussing it with the clients until the move was officially completed. Advisors who switched dealers started out with zero revenue on day one. The revenue didn’t flow again until the clients signed the paperwork and moved their accounts to the advisor’s new home.
As you can imagine, the decision to switch dealers was a big one that could quickly turn into a financial disaster without proper planning, tools, and execution. Not only did the recruiting dealers need a superior value proposition, but they needed a way to help their new advisors "re-paper" the accounts and get the documents signed as quickly as possible. A lot of them over-promised and severely under-delivered in this area, leaving their new recruits bitter, regretful, and scrambling to re-establish their incomes. Some of these dealers had transition teams that would fly across the country to spend a couple of weeks in the advisor’s office, supervising temporary data entry workers who filled out the PDFs.
AN EASY TRANSITION
For me, it was an easy transition from the day-to-day forms population system I developed to creating an automated system for completing the "onboarding" paperwork. It worked on the same principal: looping through the rows of a tab delimited text file to populate the forms required for clients to move their accounts from one investment dealer to another. I have investment dealer clients that use my system, and I had advisor clients who switched to dealers that didn’t have a repapering automation system in place.
POPUP DIALOG WINDOWS
One of the most complex functions of scripting in the PDF environment is the popup dialog window, but it also the one of the most useful and fulfilling functions for automation tasks. It gathers information from the user in one window. It can contain text fields, dropdown menus, list boxes, check boxes, radio buttons, buttons, and even images. I learned early how to create these using a program called Acrodialogs from Windjack Solutions. Unfortunately, this was a plugin for early versions of Acrobat Professional and Adobe discontinued the use of these types of plugins several versions ago. I learned how to script dialogs from using Acrodialogs and manipulating the code to suit my needs, then saving scripts for future use, and eventually creating my own simplified program that writes the scripts for me based on selected inputs.
DIALOG INFORMATION FOR POPULATING FORMS
The image above is the user interface for my investment dealer transition software for populating the onboarding paperwork. It collects the following information from the user:
Investment advisor name and rep code with a button to change advisors from a pre-loaded list.
An optional text string to add to file names for identification purposes (example:Â "KYC_Form").
A row number text field for first row of spreadsheet data (defaults to 2 because 1 is the header row).
A row number text field for the last row of data from the spreadsheet. The first row/last row entries allow a range of rows from the spreadsheet (example: 250-500).
An option to send the documents to the default printer as they are populated.
A button to connect to the database (tab-delimited text file saved from the spreadsheet).
A button to connect to the target output folder.
A button ("FILL FORMS") to start the process.Â
Steps 6 and 7 are enabled in order, to prevent errors. The final button also checks for correct numbers in the start and end row fields. The database and output folder locations are displayed as they are selected.
CHALLENGING SITUATIONS
Not every project fits an existing template. Oftentimes a custom solution is required, and changes need to be made on the fly. In these cases, I’ve been hired to complete the project myself instead of selling or renting a software solution. I love a good challenge. The project I’m about to describe was one of the most challenging and also one of the most rewarding. It was an investment-advisor-switching-dealers project as described previously, but with a lot of unique circumstances.
Firstly, it was not just one advisor switching, but an entire office of 10 independent advisors. To add more complexity to the mix, about half the clients were English speaking clients and the other half spoke French. There was a set of paperwork for each language. The destination investment dealer actually hired me to complete this project for the incoming advisors. All the paperwork was to be completed from a master spreadsheet of client data. They were then going to deliver the electronic output to a printing company who would then print it on carbon copy paper and mail it out to the clients with self-addressed stamped envelopes.
Their idea turned out to be genius as the retention rate came in at close to 100% and the revenue disruption was minimal. A large part of their success came from the carefully crafted cover letter that accompanied the packages. They made it very easy for the clients to understand their options and sign the paperwork if they chose to follow their advisors to the new dealer.
When they first presented this to me, I didn’t think it was going to work because I believed it was wrought with compliance and regulatory issues. When you set up a new account at the dealer you need to complete Know Your Client (KYC) documentation containing detailed information about occupation, income, net worth, risk tolerance, etc. Periodic KYC updates are part of the regulatory process so how could all of this information be up to date? And mailing this information out to clients without actually conducting a review and explaining what they were signing presented its own set of problems. Most of the client data contained on a KYC form could not be shared with the new dealer until the client moved and advisors are supposed to destroy any personal records of this data as soon as they are no longer affiliated with their dealer. The only data advisors are allowed to retain is referred to as a "Christmas card list" (that is, name, address, and phone number). So advisors are supposed to forget everything and start from scratch as if they are meeting the client for the first time.   Having clients sign blank forms, or even forms that are not completely filled, is a huge regulatory and potentially career ending no-no. So how did they do it?
NOMINEE VS CLIENT NAME ACCOUNTS
There are two types of account structures in that world:
Nominee (investments from multiple companies held in the dealer account on behalf of the client).
Client Name (the individual investments are held directly with the investment companies in the client’s name).
Nominee has several advantages over client name but the ease with which the accounts can be moved to another dealer is not one of them. For nominee accounts, a new account must be set up at the new dealer and the assets (investments) transferred from one account to the other. It would be very difficult to do this without client meetings and KYC reviews before the clients could sign the paperwork.
THE PLAN
There’s a one-page form called a "Dealer/Rep Change" for the purpose of changing representatives and/or dealers that can be used with client name accounts, since the account is with the investment company, not the dealer. The investment companies will act on this form signed by the client and change the representative. The revenue is immediately directed to the new representative’s dealer, along with access to all information on the account. It’s quick and simple. Investment companies are not responsible for overseeing KYC, suitability, etc.
"Don’t you still need KYC forms and account paperwork?" I asked.
"Yes, eventually, but the immediate concern is minimizing the revenue disruption to the new representatives, and our (the dealer’s) share of that revenue" was the response.
All accounts will be frozen by the dealer, meaning no trades can be placed, until the KYC forms have been completed and signed. This does not freeze the revenue. Genius. This could not work with nominee accounts and it just so happened that all accounts were in client name.
THE CHALLENGE
The following is a list of materials for the project:
A master spreadsheet containing client names, addresses, investment company names and account numbers, types of accounts, representative names and dealer/rep codes.
One row per client but clients would be repeated several times if they had multiple account numbers (for different account types and different investment companies).
Two template cover letters (one in English and one in French).
A template dealer/rep change form.
Signature images for all 10 advisors (representatives).
I programmed Adobe Acrobat Pro to do the following with the materials above:
Determine whether the client spoke English or French based on a cell value in the spreadsheet and select the correct cover letter.
Import the client’s name and address into the letter, aligned so the letters could be automatically folded by the printing company and inserted into window envelopes.
Import the correct representative’s name into the signature line.
Import the correct representative’s signature image into the letter.
Import the first account number, account type, client name, representative name, and dealer/rep code into the dealer/rep change form and add this page to the cover letter.
Flatten the first two pages so there is no field value conflict when other pages are added.
Determine whether the client’s name in the next row of the spreadsheet matched the previous name. If so, add another dealer/rep change form, import the relevant data, and flatten the page.
Repeat until a new client is found, add a mark to the bottom of the last page for the printing company’s automation, then save the previous pages as a separate PDF named for the client.
Each client PDF would contain the cover letter and a dealer/rep change form for all accounts with a special mark on the last page.
The PDFs were combined into one PDF.
The printing company printed, folded, stuffed, and mailed out the packages to the clients, using the mark on the last page of each package to know where to separate them.
A follow up with the dealer later confirmed flawless execution and near 100% retention rate with no significant revenue disruption. This entire project was programmed in Acrobat Pro using JavaScript, which I taught myself from online forums and the Acrobat JavaScript reference guide. Although the project was specific to the independent investment advice industry, the principles were used to program similar tasks in several other industries, including but not limited to: legal, accounting, and building trades.
I think it’s important for me to tell my story before getting into specific programming examples, scripts, tips, tricks, and proprietary secrets. Thank you for your patience as I lay out the path to my paperwork automation addiction. I promise I’ll eventually get to the meat. Take a look at what’s in the queue.
See you soon…