[HOW TO] Convert Bank of America statement to Microsoft Excel

Introduction

One bank that gives statements that are tricky to import is Bank of America.

This is because they do not have one single table of bank transactions, but they split this up into two tables.

One table for credits and deposits and the other table for debits.

In this post I will go over how we can extract the data out of Bank of America statements into Excel. We can then use this to clean up the transactions.

Analysis of Bank of America statement

First we must have a look at a typical Bank of America statement and how it is structured.

I have redacted the private information for one of my statements:

Some observations

• The bank transactions section only has three columns: “Date”, “Description” and “Amount”
• Bank transactions are separated - one for credits/ deposits and another for the debits. This could pose problems when cleaning the data.
• There is no rolling balance like in other banks. This would mean that we might need to calculate it ourselves.

👉 Using PdfDodo to extract data to Excel

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!

Benefits

• This works well and have high accuracy (99% rate).
• Allows you to work with CSV or Excel if required
• Works with scanned PDFs not just text-based PDFs
• Free for limited use and no need to install heavy software.

Alternative Option 1: Use Microsoft Excel

Should you have the latest Excel version and want to pull out information from your text PDF bank records, this is another alternative option.

Now keep in mind that this method is good for text PDFs, not for scanned files. So for example, if you have scanned your bank statement, then it will not work!

One thing to note is that this will only work on the latest version of Excel with Enterprise subscription. Past versions, such as 2019 or 2016, do not accommodate this function.

It is completely available in the Enterprise version, but may have limitations on simpler plans.

Steps to Export Data from PDF to Excel:

• Step 1. Open up Excel.
• Step 2. Go to the “Data” tab.
• Step 3. Select: Get Data > From File > From PDF.
• Step 4. Choose your Bank of America bank statement PDF file, then click “Import”.

The Navigator panel will display, showcasing tables and pages from your PDF with a preview.

• Step 5. Highlight the table you want and click “Load” to bring it into your Excel sheet.

You can then clean up the data from after this stage.

Limitations:

• Requires you to have the latest Excel installed. Older versions of Excel such as 2019, 2016 does not work. Additionally will be available if you have the Enterprise Office subscription. Might be limited on lower tiers.
• From my experience, I found it to be close but not 100% accurate.
• Does not work with scanned PDFs

Alternative Option 2: 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

Cleaning the data in Excel

To merge two tables with a “Date” column in Excel, you can use the “VLOOKUP” function or “INDEX” and “MATCH” functions. Here are the steps for both methods:

Using VLOOKUP

Open Excel and put both tables in the same sheet or different sheets.

Assume Table1 is in range A1:B10 and Table2 is in range D1:E10, and you want to merge them based on the Date column.

In cell F1 (or any other cell where you want to start the merged table), type the header of the table. It should include all the columns from Table1 and Table2.

In cell F2, type the formula:

=VLOOKUP($A2, $D$1:$E$10, 2, FALSE)

This formula will search for the value in cell A2 (the date from Table1) in the Date column of Table2 (D1:D10), and return the corresponding value from the second column of Table2 (E1:E10).

Copy this formula across all the rows and columns of the merged table.

Using INDEX and MATCH

Open Excel and put both tables in the same sheet or different sheets.

Assume Table1 is in range A1:B10 and Table2 is in range D1:E10, and you want to merge them based on the Date column.

In cell F1 (or any other cell where you want to start the merged table), type the header of the table. It should include all the columns from Table1 and Table2.

In cell F2, type the formula:

=INDEX($E$1:$E$10, MATCH($A2, $D$1:$D$10, 0))

This formula will search for the value in cell A2 (the date from Table1) in the Date column of Table2 (D1:D10), and return the corresponding value from the column E1:E10.

Copy this formula across all the rows and columns of the merged table.

Tips for a Smooth Conversion

• Review After Conversion: Always check the CSV post-conversion. The original format of the PDF, especially if complex, might cause misalignments or data loss.
• Prioritize Privacy: If using online tools, remember that bank statements contain sensitive data. Always ensure that any platform you use respects user data privacy.
• Backup Original Files: Always keep a backup of the original bank statements. They might be needed for verification or in case the conversion process omits any data.

Final thoughts

Overall, the Bank of America bank statement needs a bit of manual work to extract and convert their PDF to Excel.

The main problem is that they break up the credits and debits into two tables.

Additionally because of this, they do not show the rolling balance.

Using PdfDodo, we can extract this data into Excel. If you need to combine these statements, then you can use VLOOKUP function or a combination of the INDEX and MATCH functions.

Alternative options include using Microsoft Excel - but you will need the latest version and Enterprise subscription. Another way is to use Adobe Acrobat Pro. This generally works but has a hefty price tag.