Automotive Systems Training (AST) has some exciting news to share with all of you who interact with bank data. AST released an App for Excel to the Microsoft Store which was approved last week. You can purchase this app here at an introductory price.
This application is designed to give analysts (whether they be CPAs, CFOs or Business Intelligence Specialists) a secure, quick and effortless way to convert bank transaction files into usable Excel tables, CSV text blocks or validated XML. We have a support site where you can read through the knowledge base and post questions in our forums here.
We are big fans of Power Pivot and the data model as well as Power Query and Power View. Once the BAI or BTRS data is converted into an Excel table it is a snap to add it to the data model, pull down cash transactions from G/L detail and create any needed columns and relationships. From there I can use Pivot Tables and Charts to do all the analysis needed including a plain vanilla bank reconciliation.
This app is what Microsoft calls a Task Pane app which sits on the right side of the currently active worksheet like so (you can see column T of the active sheet):
The application, once it is purchased, can be inserted into any workbook at any time and saved in the workbook if so desired. Microsoft has specific instructions here.
Inserting the App
In the above screenshot a new workbook is opened and the app is inserted by selecting Insert -> Store and then searching for Bank File Conversion and clicking on Buy. A more in depth post about purchasing and inserting the app can be found on the support site here.
Once you have purchased the app, you will only have to select Insert -> My Apps -> Bank File Conversion to insert the app into any workbook that is currently active.
Download the Bank File from your Bank
In order to convert your bank data go to your banks site and download the month’s transactions in BAI2 or BTRS format. BTRS is the latest format which includes some very nice enhancements for batch transactions such as Lockbox and ACH Deposits. BTRS also supports structured (and unstructured) fedwire addenda. Whichever your bank supports, download the transactions for the month and save in your chosen folder. Once you have the file saved, it is time to convert and set the converted data in Excel tables.
The following are the steps outlined on the Home Tab of the app as you can see in the above screenshot. This post will walk you through the conversion steps in this post. However, there is a useful user guide on the support site that is highly recommended.
Step 1 – Set the File in the App
The app is setup in step by step format. To get started, on the Home tab, go to Step 1 and Browse for the file you just downloaded from your bank. After you have selected the file, the app quickly obscures any bank or account numbers in it and calculates a hash of the file contents. The user interface looks like the following as it obscures and calculates a hash:
Step 2 – Select the Conversion Output
Once the app has completed Step 1, move to Step 2 where you will check the boxes to have the app generate the converted output you require.
The selection in Step 2 tells the app what types of output to create during the conversion process. In order to use the app to set tables in Excel the Select CSV box must be checked. The real benefit to the Select CSV option is not that you get CSV text blocks for each record in the bank file, but rather that the app will be able to create tables and set selected record types in those tables using the drop-down selector (to be discussed soon in Step 5). If you do not check the box Select CSV, the drop-down selector will be inactive. Because most folks do bank reconciliations and analysis in Excel it is good to always Select CSV at a minimum.
Select XML on the other hand, creates an XML file. If you want to store your bank data in XML files, the benefits of which are considerable, then check the Select XML box. In a future post we will show you how to store you bank data in XML data type columns using the schema that comes with the app. There are many benefits to using SQL Server (containing your bank data in XML columns) as a data source for your Bank Data Analysis Excel workbook. A portion of the file is presented immediately below using XML Notepad:
In addition, the relatively new Power Query accepts XML files as data sources out of the box, where neither Excel nor Power Pivot does. If you have a chance to use this app with Power Pivot, Power Query and Power View, you will notice that the sky is the limit! The Select XML option will allow you to download your bank data in an XML file and also download the Schema for inserting into SQL Server. By the way, we believe that SQL Server Express, PostgreSQL and MySQL can handle XML data types as well.
Step 3 & 4 – Convert the File
Once you have decided on the output, move on to Step 3 and simply click on the Convert File button. Once clicked, in the Step 4 area, a spinner and the word converting will appear to tell you that the conversion is underway. Also, a progress bar will appear at the very top of the app.
When the conversion is done, the spinner will disappear and a message panel will slide up and tell you that the conversion was successful.
Step 5 – Set Excel Tables
If you checked the Select CSV box in Step 2, after the conversion is complete, the drop-down box in Step 5 will be available for you to set the desired records into Excel tables. Clicking on the down arrow in the Records Type button area will display the drop-down which will look like this for BTRS files:
The drop-down box will display a shorter list of record types for BAI files which will not have the 16 Detail with addenda records and the 16 Detail with batch records. In order to effectively set the selected record into an Excel table in the active worksheet, make sure the active cell is set where you want the upper left corner of the table to appear. If there is not enough room to set the table you will get an error like so:
Like the error message says, it is easier if you always set your tables on a new sheet starting in cell A1. Although it is not necessary to do so, it makes things cleaner for me. Once a table is set, it will look like this one immediately below, which is the table produced when selecting 16 Detail records:
Step 6 – Save XML File and CSV Text Block Output
Once you have set all the tables you need, you can access your other output on the appropriate tabs; CSV to save all the CSV text blocks as a text file, and XML to save the XML file to you hard drive. If your file is small enough you will have the option to copy the entire output to the clipboard or, in the case of CSV, copy each record type individually from text areas on the CSV tab.
Step 7 – Do It Again
If you have more files to convert simply click on the Convert Another File button and the app will reset and make itself ready for a new conversion. Only the app is reset, Excel itself remains in the same state you had it in before; no workbooks are closed, no sheets are altered or cleared; nothing happens in Excel except the inserted app has the file input box and output memory cleared. Make sure you have saved your output from the previous conversion before clicking the Convert Another File button; even if you forget though, it is a simple matter to convert the file again.
The first option, Turn off notifications (non-error) and warnings, allows you to eliminate two slide up notifications: the Success notification that occurs after a conversion is complete and the Warning message that appears when you click Convert Another File. That is all it does but it streamlines the conversion process if you have many files to do at once say to create daily XML files for inserting into SQL Server.
The second option, Turn off the obscuring of bank and account numbers, will do just that. The app obscures the important private numbers in the file prior to allowing the server to convert. This is to ensure the utmost in security. If your company or bank pre-processes the bank file and obscures these important numbers obviating the need for this process, simply check this box. If you check this box it will remain checked for the duration you have the Excel workbook open and if you save your workbook, they will be checked when you open the workbook the next time. In other words, these settings persist as long as you save your workbook. If you don’t save your workbook with the app inserted and re-insert the app in a different workbook the settings will not persist.
The second option setting should not be checked unless you pre-process your files. However, checking the box will speed up the app considerably on very large files. On normal sized files for small to medium size businesses, the time required to obscure a file is negligible.
The third option, Turn off file hash check before conversion, will stop an important process that makes sure the file you set in the input box is the exact structure that is converted. A standard feature of the app is to run the entire file through an algorithm that calculates a hash total. With large files, disabling the hash calculation will speed up the overall conversion process. However, the reason calculating a hash total is a standard feature of the app is to protect against data corruption. If your internet connection is inconsistent, subject to interference or any other anomaly, it is recommended that this feature remain enabled.
The Buzz tab gives you the opportunity to ask questions and make comments about the app. If you include an email address in the form on the Buzz tab we will get back to you.
Again please feel free to peruse the support site at www.bankfileconversions.com.
If you wish to purchase the app please do so at https://store.office.com
We know this app will save you time and increase your efficiency!