American Express Quick Reference Guide for
Data Files
To see content for a specific section or function, follow the appropriate link by clicking while holding down the CTL key.
Table of Contents
Document Purpose
Reports available as Data Files
Delivery Methods Available for Receiving Data Files
Descriptions and Reporting Options for Data Files
Great Tips for Handling CSV Data Files in Excel
Getting the Best Use of the Monthly Billed File (KR 1022) – Great Tips
Getting the Best Use of the Monthly (Cyclic )Aging Analysis (KR/KP 1300) – Great Tips
Where to go for Help
Document Purpose
Data Files contain a wealth of information. They are powerful tools because they provide clients the ability to manipulate and customize their data reporting which enables clients to meet multiple business objectives from one reporting product.
A Data File is an electronic format to receive your transactional, aging, and cardmember data. They are typically available within 24-72 hours after the cut of the cycle or day depending on the frequency of delivery.
Data Files are available in various formats and layouts to meet multiple reporting needs. This document contains each file available and the different options clients can order for set up. It was created by collecting best practices from resources throughout the organization.
Back to page 1
Reports that are available as Data Files
There are several reports that are now available as a Data File through American Express @ Work. The Data Files offer a great advantage to users that would like to sort and manipulate large amounts of data. As you move forward through the document, you will see information on suggested formats and delivery methods for manipulating the data for analysis and reporting.
Data File Name / American Express @ WorkComparable Report File
KR/KP-1205 CARDMEMBER LISTING REPORT / KR-2005
KR/KP-1300 MONTHLY (Cyclic) AGING ANALYSIS / KR-3000
KR/KP-1301 INTERIM AGING ANALYSIS / KR-3001
KR/KP-1100 INDUSTRY SUMMARY / KR-0100
KR-1022 MONTHLY (Cyclic) BILLED RECONCILIATION DATA / Transaction Reconciliation Reports
KR-1025* DAILY UNBILLED RECONCILIATION DATA / Transaction Reconciliation Reports
KR-1072 LINE ITEM DETAIL / Transaction Reconciliation Reports
KR-1075* LINE ITEM DETAIL-DAILY UNBILLED / Transaction Reconciliation Reports
*Fee Based Data Files
Simply click on the Data File for a description of the report and help on what it can be used for.
Back to page 1
Delivery Methods Available for Receiving Data Files
Delivery Methods Matrix
The following matrix provides a high-level summary of the delivery methods.
American Express @ Work / Electronic Transmissions· Secure delivery through @Work
· Download files from any PC
· One stop pick up for files and reports
· No Fees / · Secure, electronic delivery
· Best for frequent file delivery
· Hands “off” delivery
· Standard Options – no Fees
o Secure Internet File Transfer (SIFT)
o Secure Transfer Utility (STU)
o File Transfer Protocol (FTP)
· Custom Options Available for Fee
American Express @Work
American Express @ Work provides you with the ability to log into the website and locate both your Core Reports and Data Files.
Data files are available in two formats:
1. CSV (Comma Separated Value) file format
CSV in an executable (“.exe”) format can be launched into a file that is fully supported by headings for ease of use
and
2. ASCII (text file)
ASCII is a text file format and is typically used in other reporting systems. ASCII appears as a stream of information on the file, so it requires mapping in order to load it into a spreadsheet program or third-party reporting system.
NOTE: The following are available in ASCII only via this delivery method:
Ø KR-1025 (Card Daily Data Feed)
Ø KR-1072 (Monthly [Cyclic] Billed Line Item Detail [LID]
Ø KR-1075 (Daily Unbilled Line Item Detail [LID]
An email notification is sent to the recipient at the same time the Data Files are posted to @Work, notifying the recipient that the Data Files are available.
To learn more about how to download your Data Files from American Express @ Work to your desktop, click on this link (Link To Downloading Data Files).
Back to page 1
Electronic Transmissions
Electronic Transmissions is another method of receiving Data Files that allows the information to be transmitted to your location using different software. Your company will pull the files from American Express and can push files (such as payment files) to American Express. If you do not need to direct access to the Data Files (i.e. – it is loaded to your expense reporting system), this option may meet your needs.
Transmission Options
The following transmissions are recommended for frequent file pick up:
1. Custom Options – fee based on client requirements
– EDI
– Advantis Mailbox
– Frame Relay
– Connect Direct IP
2. Standard Options (See Matrix below for comparison of SIFT and STU)
– File Transfer Protocol (FTP) – Dial Up
– Secure Internet File Transfer (SIFT)
– Secure Transfer Utility (STU)
SIFT vs. STU Matrix
SIFT: Secure Internet File Transfer / STU: Secure Transfer UtilityWindows/PC-based users
· Cost: No additional fee for software
· Send and receive files with one key stroke
· Send multiple files
· Built-in compression
· Automatic Detach/Redirect of files
· Built-in scheduler
· 128 bit encryption / Server-based applications (Windows/NT, Unix)
· Cost: No additional fee for software
· No user-friendly interface (this is a feature): Technical user required to integrate STU into their server
· Your application to our application communication
· 128 bit encryption
File Formats:
Data Files are available via electronic transmissions in both ASCII and EBCDIC (mainframes only) formats.
NOTE: The following are available in ASCII only via this delivery method:
Ø KR-1072 (Monthly [Cyclic] Billed Line Item Detail [LID]
Ø KR-1075 (Daily Unbilled Line Item Detail [LID]
Questions: There are other options available. For more information and an implementation guide, please contact the Electronic Transmissions Team (ETT) at 1-800-337-7283.
Back to page 1
Descriptions and Reporting Options for Data Files
CARDMEMBER LISTING – KR/KP 1205
This file provides a listing of all Cardmembers and is available by cycle, day of the week, or for a specific day of the month. This file is available for Corporate Card and Corporate Purchasing Card products
What can this file be used for?
This file lists all or selected Cardmembers and their address information. It can be used to confirm that all Cardmembers use the Corporate Card or Corporate Purchasing Card, confirm cancelled accounts, track anniversary dates for review prior to renewal, update internal records and send maintenance changes to American Express.
There are a number of opportunities for Finance, HR, or Sourcing to help meet your goals, populate your HR system or travel agency profiles, monitor compliance, obtain the card numbers for cancellation of terminated employees, etc.
Enables you to sort by:
· BCA
· Cardmember Name
· Cardmember Identifier
· Employee ID
· Cost Center
· Address,
· State, etc.
Provides:
· Expiration dates
· Status dates
· Current balance due
· Provides Cancel Codes (“0” indicates an active account)
If the client has a consolidated travel program, they can use the KR-1205 data file as a crosscheck with their traveler profiles to identify any non-Cardholders.
How Should I Order the File?
Suggestions are:
· By Active, New Adds, New Deletes, Cancelled, etc.
· We recommend that you order it including both Active and Cancelled accounts so that you will see all accounts and the reasons for the cancellation. At the minimum, order it for Active and New Adds.
· Be sure to request “Last Name First,” so that it is easy to sort the file alphabetically.
· Request it at the Master Control Account or the Basic Control Account levels depending upon who the PA is and which organization the PA is responsible for.
· Monthly
· For more tips on how to order and use the file in Excel – click this link (Using this file in Excel)
Back to page 1
CORPORATE EXPRESS CASH BILLED DATA – KR 1600
This file provides a listing of ATM withdrawal activity (location, type, and amount) and a year-to-date (YTD) summary for all participants.
What can this file be used for?
Cash management as it lists both the specific location of ATM withdrawals as well as the year-to-date (YTD) cash dollars.
The KR 1600 Data File contains the following records:
Record 1 / Provides current activity on a Cardmember level:· Location
· Time
· Dollar amount
· Fees
Enables you to sort the ATM Withdrawals by:
· BCA
· Cardmember Name
· Cardmember Identifier
· Employee ID
· Cost Center
Record 2 / Provides the YTD total cash withdrawals and number of transactions for each Cardmember
Enables you to sort by:
· BCA
· Highest dollars
· Highest number of transactions
Use this information to monitor and control usage and potential abuse of the Card program.
How Should I Order the File?
Request it at the Master Control Account or the Basic Control Account levels
· For more tips on how to order and use the file in Excel – click this link (Using this file in Excel)
Back to page 1
MONTHLY (Cyclic) AGING ANALYSIS – KR/KP 1300
This file provides a listing of all Cardmembers’ aging. It reports all delinquent accounts as of the cycle cut-off. This file is available for all American Express products.
What can this file be used for?
This file examines all active and cancelled accounts depending on how it’s ordered. It can be requested on all accounts or only past due accounts (30 days through 120 days.)
This data file is extremely important for overall delinquency management. It allows Program Administrators the capability to keep statistics and identify which Cardmembers to focus on who directly impact your Company’s goals to reduce delinquent dollars and any potential credit losses. With the sorting options, you have the ability to drill down and review by the BCA, Cost Center, Cardmembers with highest balances, most delinquent, etc.
· Easily identify any past due Guaranteed Accounts by sorting on the number 8 in the Rev Code column
· Focus on the Cardmembers whose accounts are repeatedly delinquent. By changing the 13-Month Aging History column to individual columns (use the Text to Columns option under Data on your Tool Bar), the PA can count the number of times an account has been past due.
· Review high-balance accounts for out-of-pattern spend, personal use, infrequent reporting of expenses, etc.
· Report statistically by BCA the delinquent dollars, number of delinquent accounts, the bill age history, etc., for tracking improvement and reporting trends.
· Pinpoint specific accounts to review and focus on
· Gain a better picture of your Cardmember base for internal communication, review of compliance to policy, etc.
You can identify the accounts that are past due most often and then go into American Express @ Work Program Management using the Program Administrator Tool, “View Cardmembers Statements” where you can see up-to-date balances, the most recent charges/credits since the last billing statement, and the previous six months statements. You can also print copies of the statement. This is the same information that the employees can view and download on americanexpress.com through “Manage Your Card Account.”
Lists the:
· BCA, Cardmember Name, Cardmember Identifier, Employee ID Field, Cost Center, Cardmember Address, etc.
· Rev Code for monitoring guaranteed accounts, business executives, etc.
· Collection location and Status for Cancelled Collections/Reserve Accounts.
· Total dollars for a 12-month period for each Cardmember for monitoring spend/inactivity
· Previous 13 months of aging history to assist you in pinpointing potential credit problems
· Balance in each age bucket from Current through 120 Days past due
o NOTE: If you order the data file to include ALL accounts, you will have the most accurate picture of the balances including current through 120 Days, credit balances, zero balances, etc.
How Should I Order the File?
· Monthly
· Last Name First so you can sort alphabetically
· Request it at the Master Control Account or the Basic Control Account levels
· For more tips on how to order and use the file in Excel – click this link (Using this file in Excel)
· For even more tips on how to best use the KR/KP1300 Data File – click this link Link to Tips Back to page 1
INTERIM AGING ANALYSIS – KR/KP 1301
This interim file is set up to cut at your discretion – daily, day of month, weekly, etc. (see “How Should I Order the File” for additional options) so you can monitor Card spending and payment activity during the requested frequency. This allows managers to review accounts for payment and other activity between billing statements.
What can this file be used for?
You can use this file for internal dunning on 30 day or 60 day accounts. Verify Cardmember payments posted after cycle cut and prior to sending out internal dunning notifications. OR, you can use the KR 1300 data file.
How Should I Order the File?
Suggestions are:
· 15 days after your normal billing cycle
· Options include any day of the week
· Request it at the Master Control Account or the Basic Control Account levels depending upon who the PA is and which organization the PA is responsible for.
· For more tips on how to order and use the file in Excel – click this (Using this file in Excel)
Back to page 1
INDUSTRY SUMMARY – KR/KP 1100
This file includes six-month and YTD totals for spending. The Corporation can view the top industries and the monthly net billings for the current calendar or fiscal year.
What can this file be used for?
Provides industry spend by Service Establishment (SE) Name with spend for the current period, YTD and Prior Year.
Enables you to sort by:
· Industry type
· SE Name
· SE Number
· Current dollars and current number of transactions, and the same for the prior year and YTD dollars, and
· Number of transactions for each SE
Use the KR-1100 in conjunction with the KR-0100, Industry Spending Report