How to convert pdf bank statement to Excel format

Introduction

A common problem I have when trying to figure out expenses is that my bank always keeps statements in PDFs.

Usually with the banks that I am with - they usually give you CSV or Excel format within a certain date range. Anything old will be shoved into PDFs.

For example my bank only recently allowed me to opt into a “online” version only - before I was still getting bank statements in the mail. Sigh.

Now the problem with this is that these PDFs are extremely hard to get data out of.

You can not just copy and paste data into excel and you are done!

Some issues I have came across:

• Failed to copy data from scanned PDFs
• Copying data got all the columns jumbled up.
• Multi-page tables will have extra or missing columns when copying the data.
• Random unicode characters that pop up when copying

In this post, I will go over ways to convert accurately our PDF bank statement that we have downloaded from our bank and convert it to Excel format.

The options depend on your situation - is your PDF scanned, are you after accuracy, do you want to use existing Microsoft Excel and Word, etc.

Essentially the options we can consider:

1. Using PdfDodo - created by yours truly
2. Excel export data from PDF
3. Using Microsoft Word!
4. Using Adobe Online

Note

While most banks will allow you to download CSV files of your transactions, this usually is limited. For example with HSBC bank, you are limited to download CSV format for the past 12 months.

If you need data for more than 12 months ago, they only have PDF options available.

Option 1: Using PdfDodo

One option is to use PdfDodo to convert your PDF bank statements to Excel format.

To do this 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.

Step 5: Find the table that you are interested in and hit “Excel”

Benefits

• A free service that you can use for quick PDF to Excel format analysis.
• Works for PDF bank statements that are scanned an ones that came from the bank!
• Able to convert bulk bank statements
• High accuracy (99%) since it uses OCR and AI technology to also have a extra step to cleanse the data!
• Works with PDF statements that are encrypted and password protected.
• GDPR compliant and your data is protected behind SSL and multi layers of encryption even for free users.

Note: PdfDodo uses AI and OCR technology

OCR (Optical Character Recognition) is the technology used to convert different types of documents, such as scanned paper documents, PDF files, or images captured by a digital camera, into editable and searchable data.

Through a combination of image preprocessing, feature detection, and machine learning algorithms, OCR systems identify these patterns and match them to corresponding characters in a predefined set.

Option 2: Convert within Excel

If you already have Excel installed and want a quick and dirty way to export data from your PDF bank statement, then consider using this option.

One thing to remember is that this works with text based PDFs and not scanned ones.

Additionally, you will need the latest Excel - older versions such as 2019, 2016 will not work. Also it depends on the subscription you have with Microsoft 365. You definitely have this option in the Enterprise edition - but limited on lower subscription plans.

Step 1: Open up Excel

Step 2: Click on the “Data” tab

Step 3: Click on: Get data > From file > From PDF

Step 4: Select the PDF file and select Import

Then you will see a Navigator panel with the tables and pages in your PDF, along with a preview

Step 5: Select the table and click on ‘’Load’’. The table is imported to the Excel sheet.

Benefits

• Quick and dirty way to get started if you already have Excel installed on your machine

Limitations

• Cannot convert in bulk and requires a fair amount of data cleansing after import
• Only for newer versions of Excel (Microsoft 365) and particular Office subscriptions (Enterprise). If you are still using older versions like 2019, 2016, etc this option is not available.
• Does not work for scanned PDF bank statements.
• Does not work with encrypted or password protected PDF bank statements

Option 3: Using Microsoft Word

Yes you heard that right - you can open PDF files in Word!

One way to convert your PDF bank statement to Excel is to use the power of Microsoft Word!

Generally, we can open up our PDF bank statement and Word will try to convert the document to a .doc format.

You can then find your table of transactions and copy and paste all the row and columns into Excel.

Limitations

• Do I really need to say more? It works with simple text based PDFs and not scanned PDFs
• Data can contain random characters that are unicode so be careful when you are copying and pasting to Excel.

I would usually copy this and paste it into notepad first. This way we can see the dodgy unicode characters (if any).

Option 4: Using Adobe Online

We can go over to Adobe Online to do our bank statement to Excel conversion.

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

Now this obviously should of been the most favourable option - but it comes short due to a few reasons.

• Takes a while to analyze even a small document.
• Not sure if your data is protected - they could be selling the data to the highest bidder.
• No way to quickly get in touch with someone for help.
• Does not work with encrypted or password protected PDF bank statements

Additional Tips

• Reference or Check Numbers - For transactions like checks, there might be a reference number. Excel Format: If purely numerical, ‘Number’ format is okay. If it includes letters (e.g., a combination of letters and numbers for transaction IDs), use ‘Text’ to prevent Excel from making unwanted changes.
• Dates - This records when a transaction was made. Excel Format: Ensure this is set as a ‘Date’ format in Excel. For consistency, decide on a single format such as “DD/MM/YYYY” or “MM/DD/YYYY.”
• Security Concerns: Always ensure that the software or online tools you’re using are from a trusted source. Financial data is sensitive.
• Backup: Make a backup of your original bank statement in case you need to refer back to it.
• Formulas: Take advantage of Excel’s functions and formulas to analyze your bank statement data. For instance, you can use SUM or AVERAGE functions to get insights about your expenses or income.

Summary

In this post we went over how to convert your PDF bank statement to Excel format. There are 4 options that we can try depending on your needs.

• Using PdfDodo to extract Excel data
• Get Data from PDF using Excel
• Using Microsoft Word to convert PDF to .doc and then copy/paste data to Excel
• Using Adobe Online

The type of option that is suitable for you depends on your situation.

Are you after accuracy and saving time, do you already have the Office suite and want a quick and dirty option, or do you just want a online option for single use.

A free option that is accurate and can save you time is to use PdfDodo. This app uses OCR and AI technology to make sure that it works for text based PDFs and scanned PDFs.

If you already have the Microsoft Office Suite with the latest versions and do not care about accuracy - then consider using Excel or the Microsoft Word option.