Appreciating BAI Files [Part 2]
--By Christian T. van Dijk
President, Integra Mortgage Solutions
A Primer for Bank Statement Automation Part 2: Breaking Down the Standard
A BAI file is nothing more than a text file. More specifically, it is a comma delimited structured text file (or flat file) designed for machine-to-machine communications; in other words, it is not intended to be human readable. However, interpreting the contents of these files can prove very useful during the process of an implementation. Below is a breakdown of the general structure of a BAI file along with what I consider important functional details that can help save a lot of time in deploying or troubleshooting a solution.
01 – File Header. This is the first line in a BAI file and there is only one of these segments in the file. The 01 record holds two important pieces of information: (a) the main 9-digit ABA or routing number of the bank transmitting the contents, and (b) the date the file was created. In practice, it is a good idea to consume the contents of the file under the bank identified in this segment; in other words, use the ABA number in this line to structure the record in whatever mechanism is begin used to store this data. The reason why this consideration may be a good idea is described in segment 02 – Group Header.
02 – Group Header Much like with the 01 record, the Group Header also contains identifying information of the publishing financial institution as well as a date for the file, but there is an important distinction in what these values mean. The ABA (or routing) number included in this segment pertains to the “ultimate” or actual party transmitting the information. What gets tricky is that there can be more than one of these records for a given bank. For example, a bank may present regular checking activity within one ABA number but record all EFT (i.e. wire and ACH’s) under a separate ABA number denoting a different processing center for the bank. This would mean that there could be more than one ABA number within this segment to distinguish activity for one bank – this does not play nice with trying to organize information into logical records. Long story short, I recommend using the 01 record to classify bank statement information. There is one exception (albeit, rare) that could make a good case for retrieving the bank identifier from this record: certain large banks are able to publish BAI data for very small banks not capable of transmitting BAI files. These banks, via a service from the larger bank, can publish financial information in within a common BAI file alongside the issuing bank by leveraging the 02 record to distinguish the records. On a closing note, pay attention to the date provided in this statement for it designates the date of the transactions within the file (with one rare exception as described in line 16). Technically speaking, this record also contains some additional designators that help describe the BAI file (i.e. if file is sent as a correction or represents a Final file), but, in my personal experience of over the past 10+ years working with these files, I have never seen these line segment populated with meaningful values.
03 – Account Identifier. The 03 record denotes the first consumable transaction record within the file. Each bank account included in the BAI file will begin with an 03 record (and end with a 49 record). One important check in BAI files is that all bank accounts under scope for automated bank statements are included in the Production file. Verifying these accounts are present by identifying these within the 03 records can save a future headache (partially consumed files are not easy to complete). This same verification principal is useful when opening new accounts at the bank. Many times, financial institutions do not automatically include a bank account as part of the BAI transmission unless this service is specifically requested when opening the account. It is always a good idea to perform a quick check of the 03 record to ensure new accounts are reporting correctly. One other important consideration where inspection of the 03 segment is useful is in checking if bank accounts within the file contain leading zeros. I recommend dropping these from the transmission because Excel (think exports, not imports) does not play well with numbers considered as non-significant digits even when these are intended to mean something. The bank should be able to accommodate this request. The 03 record also contains balance information about the particular bank account it is representing. The overall breadth of summary codes seems to vary by financial institution, but arguably the most important are 015 – Closing Ledger, 010 – Opening Ledger and 040 – Opening Available.
16 – Transaction Detail. The 16 record follows the 03 record if there are any transactions recorded for the bank account for the transaction date captured in the file (i.e. if there are no transactions recorded for the bank account, this segment will be omitted and show 49 following the 03 record). The immediate code following 16 is the 3-digit BAI code designated for the transaction, such as 475 for Check Paid. Each transaction type will have its own 16 record. Apart from the amount of the transaction, an important piece of information within the 16 record is the “Customer Reference Number”. This segment helps link the check number issued with the Check Paid transaction in the BAI file, for example, and is indispensable for dead simple book-to-bank reconciliation activities. An often overlooked link is that of Wire or ACH number issued by the customer (i.e. the reference number produced by the system at the customer in a machine-to-machine exchange with the bank) to return as the Customer Reference Number in the BAI file for that type of transaction. This last step may take a bit of coordination (many banks have separate teams for BAI reporting and EFT payments that will need to communicate) but is well worth the effort in terms of an expedited account reconciliation. Lastly, the 16 record contains addenda information (i.e. the transaction message) that may prove valuable in identifying payments or determining the nature of a given transaction. On a technical note, the 16 record can be rather long which is why the BAI standard calls for an 88 record to serve as a continuation record to support additional information. This is typically where addenda information is stored. In very rare occasions, a financial institution may opt to publish more than one day’s information within a Prior Day file (i.e. a whole month’s worth of data within a single file, typically performed on special conversion activities). When this is the (again, very rare) case, banks may assign a specific Value Date to each transaction published under a special designation (fund type =V) within the file. Even though this capability is supported within the BAI specification, it defeats the intended use of the file to present settled activity for a given business date.
49 – Account Trailer. All bank accounts (i.e. 03 records) in a BAI file contain a 49 record, also known as a Summary record. From a structural perspective, all 03 records must contain a 49 record, and all 16 (and 88) records between the 03 record, and the 49 record refers to the account identified in the 03 record (i.e. it is all the same group or record cluster). One last interesting bit of information about this record is that it contains a check-digit or data integrity check specifying the expected total number of records included for an account counting lines between the 03 record to the 49 record. The computation of this value involves an algebraic formula that is proprietary to BAI and is unknown to me. This check fulfilled a far more relevant function in the early days of BAI transmissions when these were facilitated by modems (i.e. analog transcoding) that needed a check to verify the file received was an identical copy of the file transmitted (i.e. contents was pushed in lines vs. as a whole as it is generally done today in digital transmission mechanisms).
98 – Group Trailer.The 98 record structures the contents of each 02 record contained in the file; i.e. there must be one 98 record for each 02 record included in the file. As with the 49 record, the 98 record also contains a check-digit or data integrity value. One (possibly) interesting segment within this record is the one presenting the total number of 03 records (or bank accounts) included within the record cluster. Use this as a quick check that the file contains the expected number of bank accounts within the transmission, but be cautious as ere may be more than one 98 record in the file (if there are multiple 02 records). The final segment within the record presents the total count of 03, 16, 88 and 49 records included within the record cluster (i.e. between and including the 02 and 98 records).
99 – File Trailer. The 99 record is the final segment of a BAI file. As with the 49 and 98 record, this record also contains a check-digit representing the total number of records included in the file. One segment that may be useful within this record is the one presenting the number of Group Headers (or 02 records) within the file. The final segment within the record presents the total number of lines within the BAI file.
Let’s put it all together in a fictitious but structurally sound example:
The BAI file presented above is issued by a fictitious bank – the routing number provided in the File Header (123123123) is not linked to any financial institution (the American Bankers Association, or ABA, controls and issues these 9-digit identifiers). The BAI file presents Prior Day data (compare dates in 01 and 02 records) for activity recorded on 05/22/2014 (file issued on 5/23 at 7:14am). The BAI file also contains information for a single routing number (per the count of 02 records). This is confirmed by the count of Group Header records provided in the 99 record).
The file contains information for two bank accounts (count of 03 records). This can be confirmed by inspecting the 98 record which also shows a total of 2 Account Identifiers. The first bank account, 000123456789, shows an amount of $4,681.31 as the account’s Closing Ledger balance (015), Opening Ledger balance (010) and Opening Available balance (040). The account has no transaction activity (per the absence of 16 records). This is confirmed by the additional balance codes included in the record – the account shows a balance for Total Credits (100) and Total Debits (400) of $0.00.
The second bank account, 000987654321, is a bit more interesting in terms of activity. The file shows the bank account had two transactions (count of 16 records): a Commercial Deposit (301) for $1,500.00 with three lines of addenda information, and a Check Paid (475) for $250.00 linked to check number 000000012345 (the Customer Reference Number). Balance information provided for the bank account confirms this activity by showing the difference (cash increase of $1,250.00) between the Closing Ledger (015) and Opening Ledger (010) balances provided in the file (account had a starting balance of $100.00). This is further confirmed by the balances provided for Total Credits (100) and Total Debits (400) matching the amounts for each transaction recorded.
Lastly, notice those leading zeros in front of account numbers? Ask the bank to have these removed to save some headaches in storing records or pushing information to Excel.
If you want to know more about BAI files, below are links to the entire series:
Part 2: Breaking Down the BAI File Structure.