[HOW TO] Convert Credit Card Statement to Excel

Introduction

I recent task that had to do was to convert my credit card statement to Excel. I can then use this excel spreadsheet to import into my accounting software (eg Xero, Myob, Quickbooks, etc)

Ok so easy - I thought this would be a simple thing to do - just open up the accounting software and hit import PDF right?

Unfortunately I had to spend a bit of time cleaning up the data first and in a structure that the accounting software understands.

In this post, I will go over my steps to do this and hopefully saves you time after reading this.

Generally the steps are as follows:

1. Download the PDF credit card statement from your bank
2. Upload it into PdfDodo and export the Excel file
3. Clean up the data and import it into our accounting software (QuickBooks, Xero, etc)

1. Download the PDF credit card statement from your bank

The first step to download the credit card statement PDF file from your bank.

This process will differ by bank - but will usually involve logging into your online banking website and going to the “Statements” section.

From there select a period and download the PDF you want to analyze.

Reviewing the Credit Card statement (Bank of America)

To accurately convert credit card statement, we first need to review of how it is structured.

Now each credit card statement will be different based on your issuer - in this case I took an example from a Bank of America statement.

Some observations

• There are 7 columns - “transaction date”, “posting date”, “description”, “reference number”, “account number”, “amount”, “total”
• Dates follow the format of <month>\<day>
• Unlike bank statements, with credit card statements payments into the credit card is represented as negative. Purchases are represented as positive.

The “posting date” on a credit card statement refers to the date on which a transaction is recorded to your account. This date may be different from the “transaction date,” which is the date you actually made the purchase or incurred the charge.

2. Upload it into PdfDodo and export the Excel file

This option is created by yours truly. Just sign up with an account and you try it out for free.

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:

As we can see it correctly matches the columns and rows - even the row with multiple lines!

After we have our Excel file - we can do things like formulas - getting the SUM or even generating a nice Pie Chart of our expenses.

2.1 Alternative Option - Using Microsoft Word

since Word 2013, you can mess around with PDFs directly in Microsoft Word? It’s pretty handy for tweaking a PDF or using it with other Office tools.

Here’s how you can do it:

1. Fire up Microsoft Word.
2. Hit “File” up in the top-left corner.
3. Click on “Open”.
4. Find where your PDF is saved.
5. Select your PDF and hit “Open”. Just a heads up: Word will create a copy while converting it, so your original PDF won’t be messed with.
6. Click “OK” to kick off the conversion.

Word does its best to keep the PDF’s layout the same, but don’t be surprised if a few things shift around. Once it’s converted, you can edit it just like any other Word doc.

Pros and Cons

• Conversion is a bit of hit and miss. If Word cannot convert - it will keep it as a image so you will end up entering it manually anyway.
• Sometimes the columns can be out of whack - eg 2 columns instead of 4.
• If you’re using Microsoft Word (2013 or later), you already have everything you need to convert files to PDF, no additional software required.
• It’s a straightforward process if you’re familiar with Word, and unlike some specialized PDF conversion tools, it’s completely free.
• The conversion happens offline, ensuring the security of your sensitive documents.

2.2 Alternative Option 2 - Using online tools

Another alternative option is to use online Excel converters such as: Smallpdf, iLovePDF, Zamzar

1. Upload your PDF file to the converter’s website.
2. Select the output format as Excel.
3. Click on ‘Convert’ or a similar button to start the conversion process.
4. Download the converted Excel file once the conversion process is complete.

Open the downloaded file in Microsoft Excel to verify the conversion and make any necessary adjustments.

Pros and Cons

• These tools have been around for a while but mainly focus on general conversion of PDF to Excel. They do not do a great job of converting bank or credit card statements
• Be careful when using online tools with sensitive or confidential information, as uploading files to third-party servers can pose a security risk.
• You might end up spending more time cleaning up the data than saving you time!

3. Clean up the data and import it into our accounting software

The last step is to clean the credit card transaction data that you have extracted and then import them into your accounting software of choice.

If you have used PdfDodo, then this cleansing step might not be needed!

If you used the alternative options, then prepare for a few hours to clean up the data.

Tips for a smooth conversion

• Some credit card statements display the transactions in reverse order - so the most recent transactions will be the first row and the oldest will be the last row within the period.
• Check that the date formats are correct - eg for American format it should be <month>\<day>
• Make sure that the year is correct when you have statements that go across years. For example in my Bank of America statement, it only displays the month and day. We will need to manually add the year.
• Make sure to remove any extra characters such as “CR” (credit) or “DR”. Some credit card statements will add these at the end of the “amount” to denote Credit or Debits.

Final thoughts

Overall I found converting credit card statements to Excel can be a simple task if you end up using PdfDodo.

This will get you 99% of the way there depending on your credit card provider.

Alternative ways to convert our credit card statement to Excel includes using Microsoft Word/ Exce or online tools like iLovePdf.

These tools are great for general purpose but falls short on bank or credit card statements!

After we have got the Excel sheet make sure to clean up the data and keep an eye out for things like:

• date formats
• adding in the year for each transaction
• removing extra characters (eg some credit statements add a “CR” or “DR” after each amount)