HOW TO - Convert Amex Statement to Excel

Introduction

One problem that I recently came across is converting a Amex (American Express) credit card PDF statement to Excel.

I occasionally like to look at my statements to figure out my expenses. The problem is that these statements come in PDF format.

American Express does provide ways to export it to CSV, but its only limited within a date range.

In this post I will go over the tips and tricks to get a accurate and quick conversion of Amex statements to Excel.

Analysis of the American Express Statement

We first need to analyze our Amex statement to see the import sections - eg the transactions, columns, etc.

Consider the following example credit card statement (I have redacted the personal information):

The second page with the continued transactions looks like:

Observations

• There are no columns - but we can gather that there are essentially 4 columns. The first column is the transaction “Date”. The second column is the “Description”. The third column is the “Reference Code” - we probably do not need to worry about this too much. And the last column is the “Amount” being debited or credited.
• The date format is <month>/<day>/<year>
• Credits are represented as negative numbers in the “Amount” column and debits are represented as positive.
• There are rows that contain random spaces or tabs.

👉 Use PdfDodo to extract the data to Excel

To get the data from our Amex credit card statement and into Excel, we can use PdfDodo to do this.

To do start the CSV conversion, we go through the following steps:

• Step 1: Get an account with PdfDodo
• Step 2: Go to the home page and click “Upload Document”

• Step 3: After the document has uploaded - click “Start Analysis”. This will usually take a few seconds.

• Step 4: Click on the uploaded document link to see the results.

After PdfDodo has completed analysis, it will list out all the data that it thinks is in a table format.

Now we can see our result and download the Excel or CSV file:

We can see that PdfDodo correctly converts this - unfortunately the multiple line formatting is not there yet.

Benefits

Now the good thing about this is that it helps you with:

• Gets you 99% of the way there and saves time
• Works with password protected and scanned Amex credit card statements!

Alternative: Convert PDF to Word using Microsoft Word

Now if you do not want to use PdfDodo, another option is to convert the PDF credit statement using Microsoft Word.

We then can use the converted PDF to doc and copy/paste the results into Excel.

To do this, you can follow the steps below:

• Step 1: Open up Microsoft Word.
• Step 2: Select “File” -> “Open”. Then go to the Amex PDF statement.
• Step 3: Word will come up with the following warning:

(“Word will now convert your PDF to an editable Word document. This may take a while. The resulting Word document will be optimized to allow you to edit the text, so it might not look exactly like the original PDF, especially if the original file contained lots of graphics.")

Just click “Ok” and let Word continue with the conversion.

If successful, Word will transform the PDF into a Word document.

• Step 4: Locate your transaction table and copy the rows and columns, then paste them into Excel.

Surprisingly, Microsoft Word did a pretty good job of making it appear like the original PDF:

However on closer inspection, there are a few problems:

• Some of the information is jumbled up. For example, the personal information displaying on the top of the page but below the balance summary.

Most importantly, when copying/pasting the transactions table it seems to not be 4 columns but contains columns within columns in Excel:

This will not be much help for us.

Limitations

This method is good if you already have Microsoft Word, but has a few drawbacks:

• Method is ineffective for scanned PDF bank statements
• Does not work with encrypted PDFs

Alternative 2: Convert within Excel

If you’ve got Excel already up and running on your computer and you’re after a no-fuss way to pull data from your PDF bank statements, this little trick could be just what you’re looking for.

Just a heads up, this only works with text-based PDFs - so scanned docs are a no-go.

Also, you’re gonna need the latest version of Excel for this to work. Those using older versions like 2019 or 2016, sorry, but you’re out of luck. Oh, and depending on your Microsoft 365 subscription, you might find this feature is only available in the Enterprise edition.

• Step 1: Launch Excel.
• Step 2: Navigate to the “Data” tab.
• Step 3: Follow this path: Get data > From file > From PDF.
• Step 4: Choose the PDF file and hit ‘Import’.

You’ll then encounter the Navigator panel, displaying the tables and pages from your PDF with a preview.

• Step 5: Pick the table you want and click on ‘Load’. The selected table will be imported into your Excel worksheet

Pros and Cons

• A swift and simple initiation process if you have Excel set up on your computer.
• Will not work with password protected PDFs. You will first need to remove the password and run the extraction again.
• Processing Amex statements might need extra time for data cleansing.
• This method is compatible only with recent editions of Excel (Microsoft 365) and specific Office Enterprise subscriptions. It won’t work with older versions such as 2019 or 2016.
• Not great results for scanned Amex PDF statements.

Option 4: Using Adobe Online

The final option that you can take is with Adobe Online.

Do the following steps:

1. Click the Select a file button above, or drag and drop a PDF into the drop zone.
2. Select the PDF you want to convert to the XLSX file format.
3. Watch Acrobat automatically convert your PDF to Excel.
4. Sign in to download or share your converted file.

Problems with this

Not particularly my favorite choice due to the following reasons:

• Need to create an account with Adobe
• Does not work with password protected Amex PDF statements
• Results vary and there are limits on the amount you can upload.
• The website does not clearly state how the data is protected/ secured.

Cleaning the data in Excel

From my experience, the Amex statements do not need that much cleansing.

If you use PdfDodo, then you pretty much get 4 columns with “Date”, “Description”, “Reference Code” and “Amount”. You will need to add the column headers yourself.

However, if you went down the path of using Microsoft Word/ Excel - then prepare a few hours to clean up the nested columns that it produces

Final thoughts

Converting the Amex PDF statement to Excel was not too bad.

If you are using PdfDodo, it will get you 99% of the way there. All you need to do is to add the column headers since this is missing from the Amex statements.

Now if you decide to use Microsoft Word/Excel you will have to deal with the nested columns that it comes up with.

Adobe Online is another option, but I found the results to be hit and miss. It also is not optimized for bank or credit card statements. So if you have a large document, you still need to go through each page and copy the tables.