Spectrum Bank Reconciliation Cleared Checks Import from PDF Statements

Trimble Spectrum does not import a PDF bank statement. Its Cleared Checks Import reads an ASCII clearing file from the bank, matches each check number and amount, and marks those checks cleared in Cash Management. Convert the PDF statement to CSV, build the clearing file from the check lines in the layout your Clearing Data File Format uses, import it, then reconcile deposits on screen.

That is the short answer for a construction controller with a folder of PDF statements and a Spectrum check register full of open subcontractor payments. The rest of this article covers what the import reads, what it leaves to you, the errors it reports, and how to get a usable clearing file when the bank only gives you a PDF.

Can Spectrum import a bank statement?

Not the full statement. Spectrum help is explicit that you cannot import bank transactions. What it does have is Cleared Checks Import, part of Bank Reconciliation Entry in Cash Management. It reads the check numbers in a bank clearing file and marks those checks as cleared. Deposits are not read from the file, so they are reconciled by hand afterward.

For a contractor that is still most of the month. A busy operating account can carry several hundred checks to subcontractors, suppliers and equipment rental companies, and those are the lines that eat the day when you clear them one at a time. Deposits are usually a few dozen lines, and Spectrum has a bulk tool for them.

Why the bank clearing file is often missing

The import assumes your bank sends a cleared check file in ASCII text. Some banks do, usually as part of a positive pay or treasury management service. Plenty of construction companies do not have that file for every account. The payroll account sits at a second bank with no export. A joint check or project account opened for one job only produces a PDF. The treasury package that includes the cleared file was never purchased. And for any month older than what online banking keeps as downloadable activity, the statement PDF is the only record left.

The same gap shows up in other construction systems. Sage 300 CRE also clears checks from a text file, and we cover that route in Sage 300 CRE bank reconciliation with cleared checks from PDF statements. Trimble Viewpoint Vista, Spectrum's sister product, has its own auto clearing file, covered on our Viewpoint Vista bank reconciliation import page.

What the Spectrum clearing file has to contain

Spectrum defines the layout of the bank file in Clearing Data File Format Maintenance, a screen that stores the layout of the ASCII files your bank provides. Formats are company wide, so you set one up once and use it for several companies. Whatever layout you define, the import needs to find a check number and an amount on each row, and the account number has to match the bank account number in Account Code File Maintenance. That account match is what lets one file carry several bank accounts at once.

If your company already has a clearing format set up for a bank that sends files, the simplest approach is to build every converted file in exactly that layout. Then a statement from any bank imports the same way, and nobody has to touch the format screen again.

How to import cleared checks into Spectrum from a PDF statement

Plan on half an hour for the first statement while you set up the Excel columns, and a few minutes a month after that, because the same workbook can be reused with new rows pasted in.

1. Upload the PDF statement to the PDF bank statement to CSV converter. The same tool sits at the top of this article, and scanned or photographed statements go through OCR.
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. Arrange the columns in the order and width your Clearing Data File Format expects, with the bank account number repeated on every row, and save the sheet as a text file.
4. Save the file in the Spectrum data directory, which is where the import looks for it.
5. Open Bank Reconciliation Entry, fill in the bank account, GL date, statement date and ending balance, and click Import. On the Import Cleared Checks screen accept the defaults or enter your own parameters, and click OK.
6. Read the error report, then reconcile the deposits. The red check mark button next to the Deposits button clears several deposits at once using date and check number filters.
7. When the Difference fields in both the Statement Recon and GL Recon sections show $0.00, print the Bank Reconciliation Summary Listing and run Bank Reconciliation Update for the bank account code.

Errors the import reports

Spectrum prints an error report for two situations, and both are useful. The first is a check that cleared the bank but is not in the software, usually a check written outside Spectrum, a voided check that was reissued under a new number, or a check that belongs to a different bank account. The second is a check that cleared for a different amount than the one entered. The report prints both amounts and the dollar difference, which turns a transposed digit into a one minute fix instead of a reconciling item nobody can find at year end.

One more trap is in Spectrum's own notes. A reversed payment shows up as a negative deposit in Spectrum, while the bank may show it as a withdrawal on the statement. If the statement and the register disagree by exactly the amount of a reversal, that is the reason.

Is converting the statement worth paying for?

For a contractor that clears more than a hundred checks a month, yes. Clearing 300 checks by hand in Bank Reconciliation Entry is most of a working day for a senior accountant, and it is the same day every month. A converted statement turns that into an import and an error report. The cost side is small. 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 heavy construction operating account prints 20 to 60 pages a month, so a catch-up year on one account is roughly 250 to 700 pages. Plus is $149 a month for 10,000 pages and bulk upload of up to 50 statements at once, which suits a CPA firm or consultant finishing reconciliations for several Spectrum companies before an audit or a bonding review. You can test 15 pages with no credit card first.

The same statements often feed other cleanup work too. If the backlog is bigger than one system, our bookkeeping cleanup converter page covers converting a year of statements for catch-up work, and if your AP team is still keying subcontractor invoices before those checks are even printed, accounts payable automation takes that step off the desk as well.

Who this is for

Spectrum is sold to US contractors, from specialty subcontractors to general contractors and heavy civil firms, that run job cost, payroll and AP in one system. This route is for the controller or cash manager in one of those companies who has to reconcile an account with no bank clearing file, for the accountant catching up months that were never reconciled, and for the outside CPA or Spectrum consultant brought in to finish that work under a deadline. If your bank already sends a cleared check file for every account and every month, you do not need us for Spectrum. If even one account arrives only as a PDF, the converted file is the fastest way to clear it.