You can import bank statements from text files or Excel spreadsheets directly into QuickBooks Online using Excel Transactions software. 

Connect your QuickBooks Online to Excel Transactions App from QBO AppStore by clicking the "Get App Now" button

You can get the free trial of the product in the QBO AppStore. 


How does it work?


Let's assume that we have a file which contains following bank statement.


                          

Date
Description
Check No
Amount
23/12/18
Subscription payment
12123733
100
23/12/18
Telephone Bill Payment
2121221 1
-900


Bank statement has deposits and Expenses.To import Deposits and Expenses (Checks),we need to mention 


1. Income Accounts 

2.Expense Accounts

3.Customer or Vendor Names


Let's modify the file to add above details.



Date
Description
Check No
Amount
Account
Payee
23/12/18
Subscription payment
12123733
100
Uncatergorized Income Account
John
23/12/18
Telephone Bill Payment
2121221 1
-900
Telephone Expense Account
Telephone Company 1



Now we can import this file using Excel Transactions.


If the transaction has POSITIVE Amount, it will be considered as Deposit.

If the transaction has NEGATIVE Amount, it will be considered as Expense/ Check.


If you want to change the Transaction Type, then you can add one more column Transaction Type and mention one of the below value

  1. Check
  2. Expense
  3. Deposit



Basic Import Steps



Click on "Upload File" Menu and upload your file.

  1. Select your file and spreadsheet which has the expense that you want to import
  2. Select the QBO entity as Bank Statement
  3. Set up the mapping of the columns in your expense import file to the corresponding fields in QuickBooks (Refer below Section)
  4. Click Import and your expenses will be directly added to your QuickBooks company





The important step in the import is to map your file headers to QuickBooks field in Step 3 of the import. Some key steps below:

  • You need to map your file headers to "Excel Transactions" fields.This mapping will enable us to import your transactions properly in to QuickBooks company.
  • In the mapping screen, we have the "Preview Mapping" button which can help you to visualize your current mappings as per the QBO screen with your file headers.
  • Please refer basics of field mappings for more info. If you have any confusion or doubts, feel free to drop an email to support@saasant.com.




Excel Transactions Fields


Field Name
Format
Description
Check No
21 Characters
Check Number or Reference number for the transaction.
Payee
Characters
Name of the vendor / Customer / Employee for this transaction.
Bank Account
Characters
Name of the Bank / Credit Card / Cash Account for this transaction.
Date
Date
The date entered by the user when this transaction occurred.
Description
4000 Characters
The Description about the Expense or Deposit Transaction.
Account
Characters
Reference to the Expense Account Name or Income Account Name associated with the transaction.
Amount
Decimal
The amount of the transaction.
POSITIVE Amount will be considered as Deposit Amount
NEGATIVE Amount will be considered as Expense/ Check
Credit Amount
Decimal
Deposit Amount
Debit Amount
Decimal
Expense/ Check Amount of the transaction
Transaction Type
Characters
Type of the transaction.
Allowed Values : Check, Expense, Deposit
Tax CodeCharacters
Tax Code associated with this transaction Ex: GST 5%
Global tax CalculationCharacters
Tax calculation model or logic for this transaction.
Allowed values are  TaxInclusive, TaxExcluded, NotApplicable.
Default value : NotApplicable