Extract Chase bank statement to excel

Introduction

I recently had a client from Texas requesting to convert their Chase bank statements to Excel format.

They have a export business and would need this data for their tax and bookkeeping purposes.

This can be a bit challenging to convert to Excel since the date formats are out of whack - so we will need a manual step to clean it up in Excel.

In this post, I will go over a few ways to extract your Chase bank statement to Excel or CSV format and approach to clean up the data.

Analysis of the Chase bank statement

To get a accurate extraction of the bank transactions, we first need to have a look at the Chase bank statement.

I have removed the personal identifying information of a example bank statement from Chase bank:

Some observations

• The transactions table have typical columns that are consistent with other banks - “Date”, “Description”, “Amount”, “Balance”
• Unlike other banks, they only have one column for the “Amount”. Most banks usually have a “Debit” and “Credit” columns. This makes our job easier since we know “Debits” would be negative and “Credits” as positive
• Unfortunately the date does not include the year and follows the US format of <Month>/<Day>. So we will need to figure out the Year and update it accordingly in our Excel file.

Option 1: Using PdfDodo

To extract the data from the PDF bank statement from Chase to Excel, we can use PdfDodo.

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 “Transactions” table - this should contain all of your Chase bank transactions and hit “Excel”

Benefits

• A free tool for swift PDF to Excel conversion and analysis. If you are more of a power user, then there are paid plans to choose from.
• Effective for both scanned PDF bank statements and those directly received from the bank.
• Capability to process multiple bank statements at once.
• Ensures exceptional precision (99%) by utilizing OCR and AI technologies, and includes an additional data cleansing step.
• Compatible with encrypted and password-protected PDF statements.
• Adherence to GDPR standards, with data safeguarded through SSL and multiple layers of encryption, even for users accessing the service at no cost.

Option 2: Use Microsoft Word

Microsoft Word (2013 and later versions) has the ability to open and edit PDF documents.

Here’s how you can use it to convert PDF to Excel:

• Open PDF in Word: Open Microsoft Word and go to ‘File’ -> ‘Open’ and select the PDF file you want to convert. Word will then convert the PDF to an editable Word document. This process may take some time depending on the size and complexity of the PDF.

• Edit Content: Once the PDF is opened in Word, you can edit the content as needed. Sometimes the conversion is not perfect, especially for complex PDFs with images, tables, etc., so you may need to adjust the formatting.

• Copy and Paste into Excel: After editing the content in Word, you can copy and paste the relevant data into an Excel spreadsheet. Again, you may need to adjust the formatting, as the copy and paste process may not preserve the original formatting perfectly.

This method can be useful for simple PDFs, but for more complex PDFs, especially those with a lot of tables or images, it may be more efficient to use a dedicated PDF to Excel converter or OCR software. Also, this method may not work well for scanned PDFs, as the content may not be accurately converted to editable text.

Cleaning up the transactions in Excel

Now the problem with the Microsoft excel file is that the “Date” column does not contain the year. It only contains the “Month” and “Day”.

Additionally, the column is in “Text” format. A simple change of the column to a Date format will not work.

To fix this we need to fire up Excel and use the DATE function to add the year to those dates.

The steps to do this is as follows:

• Create a new column next to the column with the dates. We will use this column as the “cleaned” date column.
• In the new column, use the DATE function to add the year to the dates. The DATE function takes three arguments: year, month, and day.

So if you have the date “07/28” in cell A2 and you want to add the year 2023, you would enter the following formula in cell B2:

=DATE(2023, LEFT(A2, FIND("/", A2) - 1), MID(A2, FIND("/", A2) + 1, 2))

This formula takes the year 2023 and then extracts the month and day from cell A2 and combines them to create a new date.

Explanation of the formula

• LEFT(A2, FIND("/", A2) - 1) extracts the month from the text. In this case, we find the first "/" and take take the left section.
• MID(A2, FIND("/", A2) + 1, 2) extracts the day from the text. In our case, we extract 2 characters from the middle of a text string. This assumes that our text has 2 characters after the slash ("/")
• FIND("/", A2) returns the position of the first “/” character in the text in cell A2.
• DATE(2023, …, …) constructs a full date using the year 2023.

• After entering the formula, you can drag it down to fill the rest of the cells in the column. If you have a lot of dates and they span multiple years, you may need to adjust the formula accordingly for each year.

Tips for efficient and accurate results

• Achieving a full 100% conversion is not possible without manual intervention. However, PdfDodo can effortlessly help you accomplish more than 90% of the task.
• Exercise caution with dates as the year is not specified, necessitating manual entry. This is especially important when dealing with statements that span multiple years.
• It appears that the “Amount” columns lack currency symbols, so make sure to add them to your Excel spreadsheet.
• Don’t forget to consider security and privacy, as we are working with financial statements. PdfDodo is committed to safeguarding your data by encrypting it and maintaining comprehensive GDPR compliance.

Final thoughts

In general, the bank statement from Chase bank is relatively straightforward compared to those from other banks.

We can use PdfDodo to convert the PDF bank statement into Excel or CSV format, which will successfully convert approximately 99% of the data.

Nonetheless, there are certain aspects that will require manual correction:

• The dates are not accompanied by the year. So we will need to clean it up using a combination of the DATE, LEFT, and MID functions.
• The “Amount” column do not specify the currency, so you will need to format these columns as currency in Excel if you are working with international clients.

Alternatively, you can extract your bank statement transaction data using Microsoft Word or Adobe’s online PDF converter.

However, these options are less dependable, do not function with scanned PDFs, and consequently, will necessitate additional data cleanup.