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
AMOUNTfields 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.
Before you attempt to generate statements from the RDFs, have the following in place:
- Daily consumption of the standard set of RDFs plus any CDFs you intend to use.
- Storage of the RDF and CDF contents in your own data warehouse. See Building a datastore in About RDFs and CDFs 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.
Statements typically contain these elements:
- Program name
- Program contact information (phone/email)
- 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
- Type of transaction: payment, adjustment, settlement, fee
- Running balance
- 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
- ATM withdrawals
- Fees reversed
- Transactions grouped by MCC
This table shows where to find statement elements in the RDFs. Also see RDF Reference for a list of all RDF fields.
|Account holder name||Customer Master|
|Postal code||Customer Master|
|Account number||Customer Master|
|Starting or ending balance||Customer Master|
|Pending authorizations||Authorized Transactions|
|Interest paid this period||Posted Transactions|
|Annual percentage yield earned||Posted Transactions|
|Date of transaction||Posted Transactions|
|Type of transaction||Posted Transactions|
|Account status||Customer Master|
|Card status||Account Card|
|Balance ID||All RDFs|
|Card ID||All RDFs|
|Type of account||Customer Master|
|External account ID||Customer Master|
|Last transaction date||Customer Master|
|Bill-cycle date||Customer Master|
* By default, the RDFs do not include the transaction description that was used by the Create Account Transfer, Create Payment, or Create Adjustment endpoints. You may want to set up your own lookup file to correlate the
TRANSACTION CODE/TYPE with description strings, or you can request that the
TRANSACTION DESCRIPTION field be added to your Posted Transactions RDF.
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
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
PMxxtransaction codes, or to show all ATM withdrawals, select all
xxWtransaction 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
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.
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 BALANCEminus any pending transactions.
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.
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.
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.
Updated about 1 month ago