Preparing Amex Data for a QFX Converter Resource

When banking institutions send your bank activity as a CSV file, it is necessary to restructure this information for a QFX Converter Resource, such as the csv2qfx Convert, so that it can then be successfully uploaded into R365. 

Users who use American Express as one of their Banking Institutions can use the following process to prepare a file for a successful import into a Converter Resource. This process can also be a beneficial reference for Users with different banking institutions to help prepare their files accordingly.

Step 1: Export and open the desired data from Amex

Step 2: Separate the information in Column A

The date and the day are both noted in Column A. To split them into two different columns, highlight Column A, and then click 'Text to Columns' listed under the 'Data' tab in the top header. 

A pop-up window will appear where you will view the highlighted dates. From here, you will need to select 'Fixed width' for the file type and then click 'Next'. 

The following window will have you preview the line break between the date and the day. Ensure that this is true and then click 'Next'.

In the final window, make sure that the column data format is set to 'General' and then click 'Finish'. This will split the date and the day into two separate columns (columns A and B).

Step 3: Insert a row above the first row of data

Insert a new row by right-clicking cell A1 and selecting 'Insert'. A pop-up window will appear where you will select 'Entire row'. This will create a new top row.

Step 4: Filter top row

Set a filter on the top row by clicking 'Sort & Filter' and then 'Filter'. This will add filter tags to each cell in the top row. 

Step 5: Set up debits and credits

Name columns F and G 'Debit' and 'Credit', respectively, and then filter negative values in Column H.

If your file contains data in this cell, name the columns next to the transaction amount column 'Debit' and 'Credit' before adding a filter.

In the 'Credit' column, write the filter =H3*-1. This will convert negative amounts to positive. Drag the formula for all negative values by clicking the dot on the bottom, right-hand corner of the formula box and pulling it down the column for all rows. This will auto-populate the column.

Click over to the 'Debit' column and type in '0' for the first open cell. Once more, click the bottom, right-hand corner of the box and pull it down the column for all rows.

Repeat this process for debits by filtering for positive values and then typing in the formula =H2 in the first open cell under the 'Debit' column. Once more, drag the formula for all negative values by clicking the dot on the bottom, right-hand corner of the formula box and pulling it down the column for all rows, as shown below. This will auto-populate the column. 

Once the 'Debit' column is populated, click over to the 'Credit' column and type in '0' for the first open cell. Populate the additional cells by dragging the bottom, right-hand corner of the box down the column for all rows.

Remove the filter by clicking the filter button and then 'Clear Filter From"(Column H)"'. All debits and credits will then be displayed. 

Step 6: Prepare new Excel form

Open a new Excel spreadsheet and paste the following headers in the following order: Account, Comment, Debit, Credit, Date, and Name. 

Copy over the description, debits, credits, and date from the Amex file and then save the file as a CSV.

You are not required to enter in any information for the 'Account' and 'Name' columns, but the header names are necessary for a successful import.

Step 7: Import into your Converter Resource

Once you open your Converter Resource, map the column accordingly in the bottom drop-down. Column names from the file should be matched appropriately, as displayed below:

• Account - Type

• Comment - Memo

• Debit - Debits

• Credit - Credits

• Date - Date

• Name - Transaction ID

Once complete, click 'Create qfx'.