Bank statement import

You can download a bank statement in CSV file format from your bank, and then import the file into the Bank Acc. Reconciliation page. After you have imported the file data, you can perform reconciliation tasks, such as automatically marking checks as cleared and populating the date the checks were cleared at your bank.

Sparkrock provides a default bank statement import template. If required, you can modify the template to match your bank download file layout. The benefit of modifying the default template is that you do not have to make any layout changes before you import your bank statement data into Sparkrock 365.

To set up a custom bank statement import template, you must perform the following tasks:

  1. Verify the bank statement import data exchange definition field mapping.

  2. Import the bank statement import data exchange definition template.

  3. Add the bank statement import template to the Bank Export/Import Setup.

  4. Link the bank account to the bank statement import template.

Verify bank statement import data exchange definition field mapping

The data exchange definition template specifies the fields in the bank statement file that are to be imported and how the fields in the file map to the fields in Sparkrock 365. When you modify the data exchange definition template, you must ensure that the respective fields from your bank file are mapped to the fields in the following table.

Field ID Field Caption Optional
5 Transaction Date No
14 Check No. Yes
7 Statement Amount If debits and credits are in the same column, no.
If debits and credits are in separate columns, yes.

Import the bank statement import data exchange definition template

After you have verified the bank statement import data exchange definition field mapping, you can import the bank statement import data exchange definition template.

To import the bank statement import data exchange definition template

  1. To download the default template, select the following link:

    Import SR Check Template

  2. In Sparkrock 365, choose Lightbulb that opens the Tell Me feature., enter data exchange definitions, and then choose the related link.

    The Data Exchange Definitions page opens.

  3. On the action bar, choose Import/Export > Import Data Exchange Definition.

    The Import dialog box appears.

  4. Choose Choose, select the file that you want to import, and then open the file.

Add the bank statement import template to the Bank Export/Import Setup

After you import the bank statement import data exchange definition template, add the template to the Bank Export/Import Setup.

To add the bank statement import template to the Bank Export/Import Setup

  1. Choose Lightbulb that opens the Tell Me feature., enter bank export/import setup, and then choose the related link.

    The Bank Export/Import Setup page opens.

  2. On the action bar, choose New.

    A new row appears.

  3. In Code, specify a code to represent your bank statement import.

  4. In Name, enter a name for this import.

  5. In Direction, specify Import.

  6. In Processing Codeunit ID, specify 1270.

  7. In Data Exch. Def. Code, specify your data exchange definition.

  8. Select the Prevent Non-Latin Characters checkbox.

  9. If required, specify values in other fields.

You must link the bank statement import template that you are going to use to your bank account.

  1. Choose Lightbulb that opens the Tell Me feature., enter bank accounts, and then choose the related link.

    The Bank Accounts page opens.

  2. Select the bank account for which you want to link the bank statement import template, and then on the action bar, choose Manage > Edit.

    The Bank Account Card page opens.

  3. On the Transfer FastTab, in Bank Statement Import Format, add the import template that you want to use.

Import file example

To view a bank statement import template with example data, select the following link:

Bank Statement Import with Example

To use this template, you must export the data from your bank statement, and then copy and paste the data into the columns provided in the file under the respective heading. You can delete the existing data, but do not delete the heading row in the file. You must save the file in a CSV format before performing the import. During the import process, lines without values in the Check No. column are skipped.

Date format

The import file example comes with a predefined date format of MM/dd/yyyy. To make copying of the data easier, the default date format can be changed on the Data Exchange Definitions page to match your bank's date format.

If you change the date format, you must also change the locale settings in the Excel template.

To change the date format

  1. Choose Lightbulb that opens the Tell Me feature., enter data exchange definitions, and then choose the related link.

    The Data Exchange Definitions page opens.

  2. Select the data exchange definition that you want to modify, and then on the action bar, choose Edit.

    The Data Exchange Definition page opens.

  3. Change the Data Format to match the date format from your bank.

    Note: The Data Format field is case sensitive. Make sure to use MM to indicate months, instead of mm, which represents minutes.

Locale

The import file example comes with a predefined locale of English (United States). For information about how to change the locale in Excel, see the Excel documentation.

Importing a bank statement

For information about how to add bank statement lines by importing a bank statement, go to Add bank statement lines by importing a bank statement.

See also

Manage and reconcile your bank accounts

Feedback

To send feedback about this page, select the following link:

feedback-product-docs@sparkrock.com