[HOW TO] Import Bank of America transactions into QuickBooks

Introduction

One of the problems that I regularly encounter is entering bank transactions into QuickBooks.

Well..now you might be thinking to yourself - doesn’t QuickBooks already provide a bank feed already - why are we bothering with this manual method??

In my experience the bank feeds are not that great for all banks. Some reasons why you might not want to use them includes:

• Your transactions are more than 90 days. Bank feeds only support a 90 day period. Good luck with anything older than that!
• The bank account that you want transactions from is closed
• There are network connectivity issues when connecting with QuickBooks and the bank feed
• You do not want to hand over your username/ password to QuickBooks.

In this post I will go over my process of taking a Bank of America bank statement PDF, converting it to CSV and then finally importing it into QuickBooks.

Generally the steps involves:

1. Downloading the Bank of America statement PDF
2. Export the bank statement to CSV
3. Clean data for QuickBooks
4. Import into QuickBooks

Step 1: Download your Bank of America statement

The first step is to get your statement from Bank of America.

We need to go to the website and do the following:

• Sign into your account
• Choose the correct account from the “Accounts” menu or from your home screen.
• Click “Statements & Documents”
• Click on the month’s statement ‘blue link’ that you need to download
• Click “Download PDF”

Analysis of Bank of America statement

After we have downloaded out bank statement, we need to do a quick scan and analysis.

This will help with data cleansing step later.

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

On the first page:

And on the second page:

Some observations

• Bank transactions section has three columns:
  • Date
  • Description
  • Amount
• Transactions are separated into two tables: Deposits and other credits + Withdrawals and other Debits. Potential data cleaning issues due to separation.
• No rolling balance provided. May need to calculate rolling balance ourselves.

Step 2: Export the bank statement to CSV using Pdfdodo

The next step is to take the Bank of America PDF statement and convert it to CSV. 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

PdfDodo is a tool that is highly effective, with a 99% accuracy rate. It’s versatile, allowing users to work with both CSV and Excel files.

Additionally, it can handle scanned PDFs, not just those that are text-based.

Best of all, it’s free for limited use and doesn’t require installing bulky software. You just need a internet connection!

Step 3: Clean data for QuickBooks

The third step would be the most time consuming in my experience.

We will need to convert our CSV from Step 2 and update the format for QuickBooks.

Generally, QuickBooks will accept CSVs with a 3 columns or 4 columns structure.

With 3-column structure, we have to have the columns: “Date”, “Description” and “Amount”. The columns will have to be in that order.

An example is as below:

	A	B	C
1	Date	Description	Amount
2	1/1/2018	Example of a payment	-100.00
3	1/1/2018	Example of a deposit	200.00

For the 4-column structure that QuickBooks understands, you will need to have the columns: “Date”, “Description”, “Credit”, “Debit”

	A	B	C	D
1	Date	Description	Credit	Debit
2	1/1/2018	Example of a payment	100.00
3	1/1/2018	Example of a deposit		200.00

For our example, we will use Bank of America bank statements. Looking at the two table of bank transactions, we can see it we will need to fix up a few things:

1. Remove the totals from each of the transaction tables
2. Merge the two debits and credits table
3. Update the “Date” column to have correct format

1. Remove the totals from each of the transaction tables

From our analysis of the Bank of America statement, we can see that it contains rows with the “totals”.

For example - “Total deposits and other credits”, “Total Withdrawals and other debits” - we need to get rid of this since it will skew our results.

2. Merge the two debits and credits table

The Bank of America statement, unlike other banks, separate the transactions into two tables. One for credits and one of debits.

We need combine this into one table with “Date”, “Description” and “Amount”.

Assuming your first table is in cells A1:C10 and your second table in cells E1:G10 (for instance):

1. Select the range of the second table without the header (e.g., E2:G10).
2. Copy the selected range (Ctrl + C).
3. Go to the first cell right below your first table (e.g., A11).
4. Paste the data (Ctrl + V).
5. We then highlight the “Date” column and sort. Now, your tables are merged.

3. Date column to have correct format

Open the CSV in Excel and right click one of the date columns. Click “Format Cell” and then select the format that is "dd/mm/yyyy"

Step 4: Import into QuickBooks

The final step is to import our cleaned CSV into QuickBooks.

1. Go to Bookkeeping, select Transactions, then select Bank transactions (Take me there).
2. Select Upload transactions.
3. Select Drag and drop or select files and then select the file you downloaded from your bank. Then select Continue.
4. In the QuickBooks account ▼ dropdown, select the account you want to upload the transactions into. Then select Continue.
5. Follow the onscreen steps to match the columns on the file with the correct fields in QuickBooks. Then select Continue.
6. Select the transactions you would like to import. Then select Continue.
7. Select Yes.
8. When you’ve accepted your transactions, select Done.

Tip: If you’re new to QuickBooks, you may not have an account to upload the transactions into. If you don’t see one, select Add New ▼ from the dropdown to connect a new bank account.

Final thoughts

Overall importing the Bank of America statement to QuickBooks is not too bad. You will need to use PdfDodo to import convert the PDF file to CSV.

After that step, we should convert to a structure that QuickBooks accept - “Date”, “Description” and “Amount”.

This involves merging the transactions (debits and credits), convert the date format and removing rows with “Totals”.