Get the Right QBO Bank Balance by Editing Your <BALAMT>

by

As a ProAdvisor, if you’ve ever had a client ask why the Bank Balance on the bank feed page doesn’t match the “In QuickBooks” amount, you know it can be embarrassing.

First, you assume it's because of open transactions. But in some cases, after you reconcile it, there still is a variance.

Maybe it’s an account that's closed and has a zero balance in QBO, but still shows a “Bank Balance?”

Maybe it’s because someone clicked through the warning message (shown below) in the past?

As long as the transactions are being uploaded manually or the account is no longer linked to a bank, there's a good chance you can correct the issue.

A word of caution – the three tips shown below are relatively sophisticated procedures involving the editing of a text file within notepad. If you're uncertain concerning these procedures, the steps to properly edit notepad files or don't consider yourself computer savvy, we suggest you contact a skilled ProAdvisor to assist you with these procedures.

For several weeks I’ve been using the process I'm about to show you in Tip 1 successfully. 

Just a couple of days ago, I needed to use the Tip 2 process.

While I have used Tip 3 only once on a closed account, I can vividly remember a few instances where I would have loved to have been able to use this process.

Tip 1

When using 2QBOPro to import Stripe and Alipay transactions as a bank feed in QBO, the comparison numbers in the bank feed for “Bank Balance” versus “In QuickBooks” start to look very strange.

If importing by Alipay Settlement or by Stripe Payout (sales and refunds only, no transfers), the Alipay or Stripe bank account can be Reconciled to zero each time.

When you open the .qbo file created by 2QBOPro in Notepad, you'll get a file that looks like the Pre-edit version shown below.

Now type CTRL+End that will take you to the bottom of the file so you can find and change the <BALAMT> to zero (shown below):

Then save the file when it is imported zero in QBO for the Bank Balance. Just add the Transfer(s) in the Register and both will show as $0.00.

Tip 2

For an account where transactions are being uploaded from a .pdf statement processed by 2QBOPro (as opposed to linking to the bank), you can enter the bank balance if you know the ending balance by entering it in the <BALAMT> of the .qbo file. The balance you edit into the file will show up as the “Bank Balance” in QBO.

Tip 3

It also is possible to change the “Bank Balance” shown in QBO without uploading any transactions if you can obtain a .qbo file for that account (or if you have the bank account number to create one using 2QBOPro).

Opening the .qbo file in Notepad, carefully remove all transactions (found between sets of <STMTTRN> and </STMTTRN>) except one, and then change the <TRNAMT> on that one to zero.

Now change the <BALAMT> to the desired value ($2000.00 in the picture above); then upload the .qbo file as usual.

You probably would not need any of these tips with an account that's actively linked to a bank, because each "bank feeds import" should match the current bank balance.

Remember, before attempting to use these tips, be absolutely certain of the amount you're importing (via your edited changes). Remember, there's usually a legitimate variance between “Bank Balance” versus “In QuickBooks” due to open transactions.

About the author:

Wayne Steelman is the owner of Wayne Steelman Business Services, and an Advanced Certified QuickBooks ProAdvisor.

Applying small business experience and a lifetime fascination with double entry bookkeeping, Wayne enjoys solving QuickBooks puzzles, so business owners can do more of what they enjoy, and so their CPA can more easily analyze their numbers and prepare their taxes.

Editor's note: Use these tips at your own risk, neither Insightful Accountant, the Editor, or the Author assume or accept any responsibility for any irregularities that may result from either the proper or improper use of these procedures.