Creating Statements from the RDFs

Galileo offers pre-made statements to its clients, but only for certain account types and only with a predetermined format. (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 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 the Raw Data Files (RDFs) 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 MCCMCC - Merchant category code. Indicates the type of merchant that acquired the payment, such as supermarket, auto supply, bakery. The codes are defined in ISO 18245.
  • Disputes

RDF fields to use

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

ElementRDFField name
Account holder nameCustomer MasterPRIMARY CARDHOLDER'S FIRST NAME
PRIMARY CARDHOLDER'S LAST NAME
AddressCustomer MasterADDRESS LINE 1
ADDRESS LINE 2
CityCustomer MasterCITY
StateCustomer MasterSTATE
Postal codeCustomer MasterZIP CODE
Account numberCustomer Master
Account Card
Posted Transactions
PRN
PANAccount CardCARD NUMBER
Starting or ending balanceCustomer MasterCURRENT BALANCE
AVAILABLE BALANCE
Deposits
ATM withdrawals
Purchases
Adjustments
Fees
Disputes
Reversals
Posted TransactionsTRANSACTION DATE/TIME
TRANSACTION CODE/TYPE
TRANSACTION AMOUNT
Pending authorizationsAuthorized TransactionsTRANSACTION DATE/TIME
TRANSACTION CODE
AUTHORIZATION AMOUNT
Interest paid this periodPosted TransactionsTRANSACTION DATE/TIME
TRANSACTION CODE/TYPE
TRANSACTION AMOUNT
Annual percentage yield earnedPosted TransactionsTRANSACTION DATE/TIME
TRANSACTION CODE/TYPE
TRANSACTION AMOUNT
Date of transactionPosted TransactionsTRANSACTION DATE/TIME
DescriptionPosted TransactionsMERCHANT DESCRIPTION
TRANSACTION DESCRIPTION*
Type of transactionPosted TransactionsTRANSACTION CODE/TYPE
AmountPosted TransactionsTRANSACTION AMOUNT
Account statusCustomer MasterSTATUS
Card statusAccount CardCARD STATUS
Balance IDAll RDFsGALILEO ACCOUNT ID
Card IDAll RDFsCARD IDENTIFIER
Type of accountCustomer MasterTYPE OF ACCOUNT
PRODUCT ID
External account IDCustomer Master
Posted Transactions
EXTERNAL ACCOUNT NUMBER
Last transaction dateCustomer MasterLAST TRANSACTION DATE
Bill-cycle dateCustomer MasterBILL CYCLE DAY
MCCPosted TransactionsMERCHANT CATEGORY CODE

* 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.

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. For example, to show all deposits, select all PMxx transaction codes, or to show all ATM withdrawals, select all xxW transaction codes. Use the curated transaction-codes list 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 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 (CADCAD - Card identifier (card_id). A Galileo-generated identifier for a card. This number is used internally and is not presented to customers, merchants, or card networks. You can use the CAD instead of the PAN if you are not PCI compliant. Retrieve the CAD from the RDFs or the responses for Get Account Cards or Get Card.) 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.


Did this page help you?