Bank statement in CSV format
Recently I’ve started exploration of the options to handle custom bank statement formats i.e. formats which are not supported by standard SAP functionality (e.g. MT940, BAI or Multicash). In my previous blog post I’ve provided insights into handling of bank statements in XML-format. This post explores another option, which can used to import bank statement in any bank-specific format. I’ll use bank statement in CSV format as an example for this post.
Before I dive deeper into technical details, I’d like to mention an important aspect regards bank statements in CSV format: usually they lack header-level information and contain only line items. As a result, these bank statements do not contain beginning / ending balances, Dr./Cr. turnover, bank statement number etc. Therefore, all these details should be established as part of custom logic. I’ll provide some recommendations on how to tackle these problems below.
Screenshot of bank statement in CSV format can be found below. This is an example of real bank statement, but the values were slightly adjusted to avoid disclosure of any confidential details.
2. Overview of development objects and configuration activities
Define new format of bank statement in CSV format and provide filter value for BAdI e.g. ZID.CSV. Use customizing view VFIEB_MAPP_XCTRL or the following menu path to perform this activity:
SPRO → Financial Accounting (new) → Bank Accounting → Business Transactions → Payment Transactions → Electronic Bank Statement → XML Format and Bank-Specific Formats.
Next two steps deal with implementation of two BAdIs. Use the following menu path to find more information about these BAdIs:
SPRO → Financial Accounting (new) → Bank Accounting → Business Transactions → Payment Transactions → Electronic Bank Statement → XML Format and Bank-Specific Formats → Business Add-Ins (BAdIs) → BAdI: Split and Parse / BAdI: Mapping the Bank Statement to Internal Structures.
If you want to import any bank-specific bank statement you either have to create XSLT-transformation (for XML-formats) or implement BAdI FIEB_GET_BANK_STMTS_X (enhancement spot ES_FIEB_GET_BANK_STMTS_X). Screenshot of BAdI implementation for this enhancement spot can be found below. Please note bank statement format name is used as filter value for BAdI.
Implementation of method SPLIT is mandatory. Within this method you should split CSV-data set into several bank statements if it contains information for several bank accounts and several dates. You can also use this method if you want to remove some unnecessary data from bank statement. If your CSV-file contains bank statement for one day only, implementation of this method can be as short as one line of code. See sample implementation below:
method if_fieb_get_bank_stmts_x~split. " Split CSV-dataset into separate bank statements, or " Adjust CSV-dataset if it contains unnecessary data append i_string to et_string. endmethod.
Implementation of second BAdI FIEB_MAPPING_X (enhancement spot ES_FIEB_MAPPING_X) is more complicated. Screenshot of BAdI implementation for this enhancement spot can be found below. Please note that implementation of this BAdI uses filter value from customizing of custom bank statement format.
Method MAPP_BANK_STATEMENT parses bank statement and transforms it into internal table with type TTY_FEB_STATEMENT. This internal table contains two item components:
- FEBKO – which stores requisites of bank statement header;
- ITEMS_FEB, which in turn consists of internal tables storing line item data (i.e. FEBEP), note to payee (i.e. VWEZW) and clearing data (i.e. FEBCL).
See also my previous post for additional explanations on this data type.
Sample source code for implementation of method MAPP_BANK_STATEMENT can be found below.
method if_fieb_mapping_x~mapp_bank_statement. data: ls_stmt_header type zcl_ebs_csv_routines=>ty_stmt_head, ls_ebs_line type zcl_ebs_csv_routines=>ty_ebs_line, lt_ebs_table type zcl_ebs_csv_routines=>tt_ebs_table, ls_febre type febre_ty, ls_item type item_feb. field-symbols: <stmt> like line of ct_acct_statement. " Convert CSV-data to internal table and fill bank statement header lt_ebs_table = zcl_ebs_csv_routines=>convert_csv_to_itab( i_string ). ls_stmt_header = zcl_ebs_csv_routines=>prepare_stmt_header( lt_ebs_table ). append initial line to ct_acct_statement assigning <stmt>. move-corresponding ls_stmt_header to <stmt>-febko. <stmt>-febko-bankkey = ls_stmt_header-bankl. <stmt>-febko-bankacc = ls_stmt_header-iban. " Process bank statement line items loop at lt_ebs_table into ls_ebs_line. clear: ls_item, ls_febre. " Fill bank statement items into table FEBEP ls_item-febep-bvdat = ls_ebs_line-stmt_date. ls_item-febep-budat = ls_ebs_line-stmt_date. ls_item-febep-valut = ls_ebs_line-stmt_date. ls_item-febep-kwaer = ls_ebs_line-currency. ls_item-febep-kwbtr = ls_ebs_line-amount. ls_item-febep-vgext = ls_ebs_line-btc_code. ls_item-febep-pablz = ls_ebs_line-bank_key. ls_item-febep-piban = ls_ebs_line-iban. ls_item-febep-partn = ls_ebs_line-name. ls_item-febep-epvoz = ls_ebs_line-sign. ls_item-febep-sgtxt = ls_ebs_line-note. " Fill note to payee into table FEBRE ls_febre-vwezw = ls_ebs_line-note. append ls_febre to ls_item-vwezw. append ls_item to <stmt>-items_feb. endloop. endmethod.
Most of the implementation logic for parsing of CSV-data set and its conversion to internal table was implemented in external class ZCL_EBS_CSV_ROUTINES. Considering a lot of source code and custom logic within this class, I’ll not share for the moment. I’ll considering sharing it on GitHub though using abapGit.
Anyway, the methods of this class perform the following functions:
- Split CSV-dataset into internal table;
- Prepare bank statement header (i.e. FEBKO-table);
- Map line items of bank statement to internal structures FEBEP and VWEZW.
Note: if you experience issues with splitting of CSV-dataset into lines, check if OSS-note 2423051 (Format X: Line break in bank-specific formats) is installed in your system.
Preparation of bank statement header involves several steps. You can use the following recommendations to implement your own logic:
- Extract reference to your company’s bank key / bank account from first line item;
- Select house bank / house bank account ID based on bank key / bank account;
- Select the latest bank statement for combination of house bank / account ID / statement currency;
- Calculate Dr. / Cr. turnover based on current bank statement line items;
- Take ending balance of previous bank statement as beginning balance of current bank statement and calculate ending balance based on Dr. / Cr. turnover;
- Set bank statement number – e.g. you might take number of days between bank statement date and beginning of calendar year as bank statement number. Assuming that there will be only one bank statement per day, this value can be quite accurate.
Once you implemented BAdIs, you can attempt to load bank statement. Go to FF_5 and choose “XML or Bank-Specific Format” option. Once you select it, additional drop-down list will appear, where you can select your custom CSV-format. Provide the reference to file path and fill the rest of the screen as usual.
The results of the upload can be found below:
This post doesn’t describe basic configuration of bank statement, which is also required for bank statement processing e.g. setup of house bank / account IDs, setup of posting rules, transaction types for bank statement etc. Customizing in these areas is standard for any bank statement and well explained.
3. Concluding notes
I’ve seen several projects requiring import of CSV-bank statements and in each case, it was resolved via development of standalone program. Custom program for this requirement is also a valid option, but why should we neglect other options, which as it turns out might be time-saving and more aligned with standard SAP environment?
The advantages of proposed approach are obvious: development efforts are limited primarily to parsing of CSV-dataset and mapping of resulting data to standard structures of bank statements. Standard SAP programs will take care of everything else i.e. reading of files from workstation, checking validity of house bank accounts, saving of bank statements into database, error handling etc.
There is also an advantage for end-user: all bank statements regardless of their formats (MT940 or CSV) can be imported in the same way via FF_5 and subsequently processed via FEBAN. Thus, you wouldn’t have to explain which (custom) transaction / program to use for each type of bank statement. All processing steps will be uniform. Besides, any automatic upload is more save than manual processing of bank statements via FF67.
Finally, if you proceed with this approach you avoid creation of custom transaction codes for import of bank statement and subsequently avoid changes to authorization profiles / roles.
I hope that you have learn something useful once you reached this sentence. I’m looking forward to your comments and remarks.