Creating Statements from the RDFs

Galileo offers JSON statements for its clients, but only for DDA, GPR, and savings accounts. (See Statements for more information.) If you want to produce customized statements that go beyond what Galileo offers, you can build your own statements using the daily raw data files (RDFs) that you get from Galileo.

When assembling statements from the RDFs, keep the following in mind:

• You must consult with your bank to comply with all of their statement requirements.
• No single RDF file contains all of the data to use for a statement: You must draw on the datastore that you have accumulated from daily RDFs.
• Reporting periods are most commonly based on calendar month. However, you can use the billing cycle date as the first date of the statement. For example, if the cycle date is the fifth of the month, your statement's start date would be the fifth of the previous month and the ending date would be the fourth of the current month.
• A Galileo calendar day may not correspond to the day that you use for statements. For example, if you are in the U.S. Eastern time zone (GMT -0500), your day begins two hours earlier than Galileo system time during standard time and three hours earlier during daylight saving.
• The RDF's AMOUNT fields are always in the currency in which the account is based. Card networks automatically convert foreign currency amounts (both authorizations and settlements) into the account's local currency.

Prerequisites

Before you attempt to generate statements from the RDFs, have the following in place:

• Daily consumption of the Base Processing RDFs plus any other RDFs you intend to use.
• Storage of the RDF contents in your own data warehouse. See Building a datastore in About the Raw Data Files for more information.
• An understanding of differential file methodology, which means that each daily RDF contains the data for the previous day only—each file's contents must be cumulatively added to the datastore.
• The ability to query data across databases and perform basic calculations such as finding all transactions in a date range that have the same transaction code.
• Data storage capacity as specified by your bank—typically seven years.

Statement format

Statements typically contain these elements:

• Header
  • Program name
  • Program contact information (phone/email)
  • Logo
• Body
  • Account holder name and address
  • Account statement details
    • Account number and/or masked PAN
    • Statement period
  • Account summary
    • The summary can be as simple as total debits, total credits, and fees for the period.
  • Interest summary — Interest-bearing accounts must report interest on a monthly basis, not a bill-cycle basis.
    • Interest paid this period
    • Annual percentage yield earned
  • List of posted transactions
    • Date of transaction
    • Description
    • Type of transaction: payment, adjustment, settlement, fee
    • Amount
    • Running balance
• Footer
  • Bank-required disclosures
  • Instructions for reporting errors or making inquiries, including your mailing address and other contact information.

Other elements that you may want to include are:

• Beginning balance (statement date-range start)
• Ending balance (statement date-range end)
• Pending authorizations
• Deposits
• ATM withdrawals
• Purchases
• Reversals
• Adjustments
• Fees
• Fees reversed
• Transactions grouped by MCC
• Disputes

RDF fields to use

This table shows where to find statement elements in the RDFs. Also see Base RDFs for a list of all RDF fields.

Queries and calculations

This section provides guidelines on performing queries or calculations for various statement elements.

If you need to join two tables in a query, join on these two fields:

• UNIQUE PROGRAM ID
• GALILEO ACCOUNT ID

Specific transaction types

To calculate totals or to present lists of specific transaction types, perform these searches in your Posted Transactions RDF datastore.

• TRANSACTION DATE/TIME — Statement date range
• TRANSACTION CODE/TYPE — Selected activity type or transaction type (otype). For example, to show all deposits, select all PMxx transaction codes, or to show all ATM withdrawals, select all xxW transaction codes. Use the Funds Flow document that Galileo supplied to you for all possible transaction codes for your program. Also see Classifying transactions in the About Transactions guide and the Transaction codes table in the Transaction Types enumeration for more information.
• TRANSACTION AMOUNT — Show in a list or as a sum

Pending authorizations

To show pending authorizations as of the statement date, compare the list of transactions in the Authorized Transactions RDF with the Posted Transactions RDF using AUTHORIZATION CODE + NETWORK CODE to match transactions. Any unmatched authorizations during your selected timespan can be displayed as pending.

Beginning and ending balances

In your Customer Master datastore, use the balance fields as follows:

• CURRENT BALANCE — The total of all posted transactions as of 23:59:59 on the day before the RDF file was generated. Pending transactions are not subtracted out.
• AVAILABLE BALANCE — The CURRENT BALANCE minus any pending transactions.

Running balance

If you want to show the running balance in your transaction list, begin with CURRENT BALANCE (ledger balance) for the start of the statement period, and as you list each transaction in chronological order, calculate the running total. This approach is not recommended if your transaction list is broken into separate categories such as deposits (payments), adjustments, fees, or MCC.

Keep in mind that a running balance thus calculated will not show the available balance, so an account holder may wonder why a transaction was denied when the running balance on the statement appears to show sufficient funds. Galileo checks the available balance when deciding whether to approve a transaction, not the ledger balance, and so funds may not have been available at a particular time because of a hold.

Transaction lists per card

To present transactions from multiple cards, use the CARD IDENTIFIER (CAD) to select the individual card, and use either the PRN or the GALILEO ACCOUNT ID (balance ID) to identify cards belonging to the same account. Instead of exposing the CAD or the balance ID to the account holder, use the masked PAN to identify individual cards.

Grouping by merchant category

As desired, use the MERCHANT CATEGORY CODE to break out transactions by merchant type. You will usually need to include multiple codes per category, such as both 5541 and 5542 for gas stations.