Viewpoint Vista Bank Reconciliation Import from PDF Statements

Can Viewpoint Vista import a PDF bank statement?

No. Trimble Viewpoint Vista clears Cash Management entries from an auto clearing file, a plain text file in which every row carries the bank account number, the check number and the amount, with the cleared date as an optional fourth field. It does not read a PDF statement. To clear a month from a PDF, convert the statement to CSV with the tool above, keep the check lines, add the account number, and load the result through the Imports module.

The form names, the required fields and the clearing behavior on this page come from the Vista help on help.trimble.com. Our part is the file. The converter reads the statement, digital or scanned, and writes the date, description, check number and amount of every line to CSV or Excel, so the controller is not ticking three hundred subcontractor checks one at a time.

Where the bank file stops and the PDF starts

Vista expects the bank to send a text file of cleared items, and for a primary operating account at a large bank that is often true. The rest of the picture looks different in most construction companies. The payroll account sits at a second bank that offers no cleared check export. The joint check or project account opened for one job only ever produces a PDF. The cleared file service comes bundled with positive pay or a treasury package the company never bought. And when an auditor, a surety or a new controller asks for the reconciliations nobody finished last year, the only record of those months is the statement archive in online banking.

What the Vista auto clearing file needs

Vista asks for three required fields and one optional field. Everything else on the statement, such as running balances, deposit detail and descriptions, is not part of the file. The table shows where each value comes from when you start from a PDF statement.

Because the check number is a required field, the file is a check clearing tool. Checks and numbered electronic payments clear from it. Deposits, bank fees, interest and transfers that carry no number are cleared on the CM Clear Entries grid afterward, which is usually a few dozen clicks rather than a few hundred.

How to clear Vista entries from a PDF bank statement

The first month takes about half an hour, most of it spent on the import template, which you only build once. After that each statement is a few minutes.

1. Upload the PDF statement with the box at the top of this page. Scanned and photographed statements go through OCR the same way.
2. Tie the rows to the beginning and ending balances printed on the statement, then export CSV or Excel.
3. In Excel, filter to the check lines. Build the columns your template uses, for example account number, check number, amount and date cleared, and save a CSV. A header row is fine, because the template can skip it.
4. Once only, open IM Template Header and create a template with the Import Form CMIMPORTCLEAR. Pick the Delimited file format with a comma delimiter, set the Starting Line Number to skip the header row, and map the fields in Template Detail.
5. Run IM Import with that template to load the CSV into a work file, check it, then run IM Upload to move it into the Cleared Entries table (CMCE).
6. Open CM Clear Entries, enter the CM account, the Display Through Date and the Default Date Cleared, select the transaction types under Check Items to Display, and click Refresh.
7. Click Initialize. In CM Clear Initialize select Use Auto-Clearing File, enter the upload date and the Clear Outstanding Entries On or Before date, and click OK.
8. Print the error report, clear the leftover items and the deposits on the grid, and finish the statement.

What happens to rows that do not clear

Vista removes every successfully cleared row from the Cleared Entries table. Rows with an invalid check number, a different amount or another error stay in the table and show up on the report printed after initialization, where you can clear them by hand on the main screen. In practice the errors fall into three groups.

• Check not found. The check was voided and reissued, or it belongs to a different CM account. Look it up before forcing it.
• Amount differs. A check was entered in Vista for one amount and the bank paid another, often a transposed digit. This is a real reconciling item, which is exactly why you want the file to surface it.
• Formatting. A dollar sign, a thousands comma or a stray letter in the check number. Clean the column in Excel and run IM Import again.

Who uses this

Vista runs accounting at mid sized and large general contractors, subcontractors and heavy civil firms across the US, the companies that write hundreds of checks a month to subs and suppliers. The people converting statements for it are construction controllers closing every CM account on time, AP and cash managers who clear checks at month end, and the Vista consultants and CPA firms brought in to finish a backlog of reconciliations before an audit, a bonding review or a move to Vista from another system. They all want the same result: the outstanding check list matches the bank, and the difference is zero.

What a year of statements costs to convert

OFXStatement Starter is $49 a month, or $24.50 a month on the annual plan, for 2,500 pages from one shared pool, with statements up to 25 pages each. A construction operating account with heavy check volume often prints 20 to 60 pages a month, so a catch-up year on one account runs roughly 250 to 700 pages, and several accounts still fit in one Starter month. Plus is $149 a month for 10,000 pages, removes the per statement page cap and adds bulk upload of up to 50 files at once, which suits a firm with several Vista companies. You can test 15 pages without a credit card before you pay.

Compare that with clearing a 400 check month by hand. That is most of a day for someone senior, every month, and the error report Vista prints is the most useful control in the process.