CM Auto Clear Entries Import

Created by Kristen Cowl, Modified on Tue, 18 Mar at 2:49 PM by Jess Miller

Save the bank transactions you need as an excel spreadsheet.
Copy the Bank Transaction Template spreadsheet into the same folder as the bank transactions excel spreadsheet. Rename it as Bank Transactions - Month.
Open both spreadsheets side by side.
Copy the transaction dates over to the Transaction Date column and the posted dates over to the Posted Date column if applicable. Then click on cell L2 under Transaction Date Conversion and drag it down to the last line with a date. Then copy column L and paste it in column D. Your spreadsheet should now look like this.
Then put in the company number under the Company column and CM account number under the CM Account column and drag each column down to the last row with data. Under the Month column, put in the date for the first date of the month you are reconciling. Then click on that cell and hold the Ctrl key down and drag it to the last row with data. If there is data for check references, copy them over to the Check Reference column and if there is descriptions, copy them to the Description column. Also, copy the credit amounts and debit amounts into the Debit and Credit columns or the amount into the Amount column, depending on what information is on the bank transaction spreadsheet. Your spreadsheet should now look similar to this.
Save the updated spreadsheet as a CSV (Comma delimited) spreadsheet. Close both spreadsheets.
Open Vista and go to Imports>Programs>IM Import
Put in your import ID and choose the CM Bank Transactions Template. Click browse to find the bank transactions spreadsheet. Enter 2 for the Starting Line Number. Click Execute.
To check that the information was imported correctly, click Open Work Edit and review the Imported Value. Check that the dates and other information is formatted correctly. Then close this screen.
Click Open IM Upload. Then click Upload. Once the Upload Successful box pops up, you can close out of all IM Import windows.
Go to Cash Management>Programs>CM Clear Entries. Put in the correct CM Account and click Refresh. If there are already entries cleared, you can click Undo. Then click the Auto Clear Entries button and Refresh. This will clear all the entries that match entries on the bank transactions spreadsheet.
To review the cleared entries, uncleared entries and entries not on the bank statement, go to Cash Management>Reports>CM Bank Reconciliation and pull it for the CM account you are working on. The transactions under Not in Bank Statement should equal the total of your variance and those will be the outstanding ones that need to be entered.
When more transactions get entered, you can just re-click the Auto Clear Entries button and refresh and the new transactions should clear.