How do I import Transactions from a broker?
ANSWER:
Importing transactions from a broker into CCH Trust Accounts is only worthwhile where a large number of transactions come from a single broker, e.g. more than 200 a month. CCH used to suggest users ask brokers for data in the format that the CCH Trust Accounts Transaction Import module can read. The format is defined in the specification for the Transactions Import, called Trimport.doc, or the simplified version of it that covers most requirements, "Simplified Trimport.doc". These documents are shipped with the software and sit in the Trust Accounts programs folder.
Although these documents are useful, this approach often failed because brokers are reluctant to provide the data in the right format as they tend to see the trust work as small beer compared with the other work they do. This means that users have to make do with data files that the brokers already prepare for some other purpose. These files will probably be in CSV format, but won't be in the format that the Trust Accounts Transaction Import can read directly. CCH usually has to write a small program that converts the broker's format into the format that Trust Accounts uses to provide a smooth import routine. So if a user is thinking of importing data they should contact CCH first. CCH can offer advice and may already know about importing data from that broker. If not they can arrange to write the conversion program. There may be a charge for this. Also the user will need to have the Transaction Import module.
The next stage is to ask the broker for some sample data. The following is the sort of email to send to the broker:
"As discussed this morning, Practice A would like a file of transactions so that they can process them electronically. Hopefully one of your existing file formats will fit our needs, e.g. if you supply transaction details to pension companies or custodians. Essentially we are looking for details of purchases, sales, dividends and interest. Some brokers have 2 file formats, one for bargains and one for income. Other corporate actions we can process manually in our software. The file(s) would cover multiple clients identified by your account number and it would probably be in CSV format. On the bargains side we just need to know the (cash) amount paid and the accrued interest if any. On the income side it would be nice if you have the tax credit, but some brokers can only supply the amount paid, which could be net or gross. We use SEDOL or ISIN codes to identify our securities. Ideally both would be present.
I think the quickest approach would be for you to send sample data to me and we can liaise with our software supplier and let you know if there are any problems or queries."
When the sample data is received the user can send it to CCH who can check it contains the required fields and can write the conversion program.
If the conversion program is not going to be appropriate and the user wishes to manually extract the information for the brokers file, then a new macro enabled transaction import spreadsheet is now available. This makes the creation of the import CSV files considerably more straightforward, as the data only needs to be pasted into spreadsheet tabs set up for specific content, such as nominal ledger transactions, investment ledger transactions etc. Standing data CSV files such as security price information can also be created using the spreadsheet and subsequently imported into CCH Trust Accounts.
Please contact CCH if you are interested in using this spreadsheet.