[HOW TO] Import Bank of America transactions into QuickBooks
Step by step guide to import your Bank of America transactions into QuickBooks.
Oct 9, 2023 | Read time 8 minutes📗 Table of contents
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:
- Downloading the Bank of America statement PDF
- Export the bank statement to CSV
- Clean data for QuickBooks
- 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:
- Remove the totals from each of the transaction tables
- Merge the two debits and credits table
- 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):
- Select the range of the second table without the header (e.g., E2:G10).
- Copy the selected range (Ctrl + C).
- Go to the first cell right below your first table (e.g., A11).
- Paste the data (Ctrl + V).
- 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.
- Go to Bookkeeping, select Transactions, then select Bank transactions (Take me there).
- Select Upload transactions.
- Select Drag and drop or select files and then select the file you downloaded from your bank. Then select Continue.
- In the QuickBooks account â–¼ dropdown, select the account you want to upload the transactions into. Then select Continue.
- Follow the onscreen steps to match the columns on the file with the correct fields in QuickBooks. Then select Continue.
- Select the transactions you would like to import. Then select Continue.
- Select Yes.
- 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”.
👋 About the Author
G'day! I am Ken Ito a software engineer based in Australia. I have worked as a software engineer for more than 10 years ranging from roles such as tech lead, data analyst to individual contributor. I have experience in creating code (python, javascript) to extract data from PDFs for ETL and financial statement analysis for accounting purposes.
During those years, I have worked for Government departments, companies like NTT (Nippon Telegraph and Telephone), Bupa and not-for-profit organizations.
[HOW TO] Convert Credit Card Statement to Excel
Sep 10, 2023Guide to convert your credit card statement to Excel format.
[HOW TO] Download Metro Bank Statement in CSV
Oct 5, 2023Tips on getting the Metro Bank statement as CSV
[FIXED] Xero import Bank Statement CSV errors
Oct 4, 2023Top 5 tips to fix Xero import bank statement errors.