Import Old Bank & Credit Card Transactions from PDF Statements into QuickBooks Online
QuickBooks Online (QBO) downloads the last 90 days of transactions when you first connect an account. This is great when adding a relatively new account. But what if you create a new QBO account for a business that has been open for many months or over a year that you want to "catch up" on? Some banks will only allow you to do download up to a certain amount of transactions: 90 days, 6 months, only a year. You can
to scan PDF statements into QBO, but that can be very expensive. It may be cheaper to just pay a bookkeeper to key in the data! But if you are looking for a free way, except for your time, of getting these transactions imported into QBO, I have a solution for you.
Download your bank or credit card statements in PDF format, if you don't already have them.
Open each statement using Nitro PDF Reader. It's free.
Convert to a text (.TXT) file. This should open up automatically in Notepad on a PC using the default settings.
Create a new Excel worksheet with columns: Date, Amount, Description.
Depending on the format of each bank or credit card's statement, you can usually start by pasting in groups into each column. In my test with Capital One, my text file listed the transactions in numerical sequence, but the date was together with the description separate from the amounts. So I just keyed in the dates manually from the pasted descriptions and formatted the column as *M/DD/YYYY. For the amount column, I formatted as a general number to remove the $ sign. But I had to create another column temporarily to change charges to negative amount (=B2 * -1), for example, and then copy down. Then, I copied and pasted only the values back into the original column. Payments and credits have to be positive amounts. Finally, I just highlighted and hit backspace to remove the sequence number and dates from the description, but it wouldn't hurt to leave them.
Save Excel file in CSV format.
Import into QBO using File Upload option in dropdown menu. Add the account or select the account if you already have it in QBO. It should recognize the Date and Description columns, but you may have to tell QBO which column to use for the Amount. Keep the selection for 1 column. Click Next and verify what will be imported. Click Next again and you're done!
I prefer to do one statement at a time copying the Excel template and saving for each month. That way, I can confirm the imported transactions show correctly in Banking after each import. And if I make a mistake, I can exclude and delete the last ones I imported, fix the file, and import again. But if you verify the transactions in Step 7 before the final Next, you should not have to do this.
Then, all you have to do is categorize and add or match to existing transactions, such as payments from a checking account already in QBO. You can speed things up by creating rules for transactions with similar descriptions so that QBO recognizes them and automatically adds them.
