Skip to main content

Bank File Conversion

Automotive Systems Training
Securely convert bank files from BAI2 & BTRS to Excel 2013 Tables, CSV and XML for ease of analysis.
  • IDEAL FOR: Converting, analyzing, reporting and storing bank account data. Treasury, finance, accounting, audit and business intelligence professionals, as well as database administrators in banking intensive industries, will benefit from this tool.
  • Bank transactions are some of the most important and voluminous transactions of businesses today. Until now it has been difficult to import banking data into Excel in a useful and easily analyzed manner. This app provides you with multiple ways in which to query, analyze and present your banking data within Excel 2013 and other programs that support CSV and XML data sources.
  • FILES: Most America banks currently report banking data using the BAI V2 specification published in 2005. However, there is a later version published in 2012 called BTRS (Balance and Transaction Reporting Standard). BTRS has improved features for reporting batch transactions (lockbox and ACH) and wire transactions (addenda). Both the BAI2 and the BTRS standards are supported by this app.
  • SECURITY: * Your data is never saved to any storage devices. This includes hard drives or any other medium, whether on your computer or on servers, virtual or otherwise. Your data remains only in memory until the conversion is complete when it is erased and returned to your workbook. Only you have the ability to save your converted data. * The data present in your file in the bank number and account number fields is obscured by the app immediately. The first 4 digits of account numbers are retained so you will be able to distinguish between accounts in multiple account files. * All communication between the app and the server is done using Secure Sockets Layer (SSL) technology. SSL is the foremost security protocol on the internet. It establishes an encrypted link between the server and browser during a session that maintains the privacy of the communication.
  • OUTPUT: As part of the conversion, the output is augmented with the following important data: - Your file name. - The date the file was last modified. - A sequence number representing the record's position in the original file. - The currency code. - A description corresponding to the transaction code. - The summary code the transaction belongs to and a corresponding description of the code. - The sign of the transaction (debit or credit) - All unstructured text is combined in a 'Descriptive Text' field regardless of length. Records can be output in a variety of ways including: - Individual Excel 2013 Tables by type of bank record – Only output the records you need. Use simple auto-filter analysis or more advanced use in Pivot Tables or Power Pivot Data Models. - CSV text – Copy and paste into any application that accepts clipboard. Each BAI record type is separated into their own CSV text block. - Validated XML file – The file is accompanied by fully deterministic schema. Use an XML data type column in SQL Server and query with XQuery. Data-mine validated XML in SQL Server using SSAS and SSRS.
  • USES: The app is an essential tool for reconciling, analyzing, reporting on or storing banking data. Analyze the amounts, types, and timing of cash transaction. Suggested uses include: * Accounting - Simplify the bank reconciliation process. * Treasury Management –Study cash flow and transaction volume data and verify bank availability (float) calculations using the data analysis features of Excel 2013. * A/R and Cash Application – Quickly and easily filter and analyze lockbox and batch ACH transactions in BTRS files. * Auditing –Join non-related tables with tables created with this app to verify compliance with policy, procedures and internal control. * Business Intelligence – Prepare visualizations on converted bank data with Pivot Charts.

Please visit The app requires Internet Explorer 10 or better or the latest version of Firefox or Chrome.

Add-in capabilities
When this add-in is used, it
  • Can read and make changes to your document
  • Can send data over the Internet