Should I be able to upload current bank balance (as well as transactions) from my bank via a csv file?

Hi there, @andy-mcwilliam.

Yes, you can manually import data into your account to have an updated bank balance in QuickBooks. To manually upload bank transactions, your CSV file must be formatted correctly to successfully import it. 
 

Here are the things that you need to check:

1. QuickBooks has 3 fields: Date, Description and Amount to import bank transactions. Some UK banks provide .CSV transactions in 3 columns, and others in 4 columns (separating Amount into Debit and Credit fields). Both formats work for QuickBooks, if fields are mapped correctly.
2. Mandatory fields should be filled in. For Amount fields with no value (normally happens to 4-column .CSV files), enter 0 (zero).
3. Remove all symbols in the Amount field. For example, currency sign or comma.
4. If your Amount field is in one column, use the minus - sign  to depict money out rather than brackets ( ).
5. Remove non-transactional info in the file (ex. account numbers, opening, closing balance).
6. Save your changes.

Once verified, you can now import bank transaction, here's how:

1. In the left menu, click Banking and go to the Banking menu.
2. Choose File upload. If you’ve never connected an account, you can select Upload transactions manually. You can also begin your upload from the Import Data page (select Settings ⚙, under Tools select Import Data, and select Bank Data).
3. Pick Browse and choose the file you downloaded from your bank. If you haven't downloaded your CSV file yet, follow the onscreen instructions in the Upload file window to download your transactions and then select the file you downloaded.
4. Select Open, then select Next.
5. From the QuickBooks Account drop-down list, select the bank or credit card account then select Next.
6. Choose a Date, date format, Description, and Account. Note: You can choose 1 column or 2 columns.
7. Hit Next.
8. Select the checkboxes of the transactions to import and select Next then Yes.
9. Select Let's go! when the Import completed notification appears.

For more information about the process above, check these articles:

• Import bank transactions using Excel CSV files

You can get back to me anytime you have additional questions in QuickBooks by posting your response below. I'll do whatever it takes to ensure your concerns are addressed. Enjoy the rest of your day.

Hi Mirriam, Thanks for your response. So I've followed your instructions. But the only way I can get QB to accept the CSV file is if I strip out all of the balance info. Hence my original question. I attach an example CSV file from my bank (screenshot since csv upload not supported here). However, to get QB to accept this, I have to strip out the first 6 rows. I then map Date to Column 1, Description to Column 4, and Amount to Columns 6 and 5. This then works in terms of importing the transactions, but it does not import the balance. Is it supposed to?  Thanks, Andy

The bank balance currently reported in QB is the correct figure for 25 March. (Probably the last time QB was able to upload automatically from my bank.) But although transactions have been correctly uploaded since then, the balance has not been. Hence, this creates an apparent 'error' for QB. How can this be resolved? 

I appreciate you for providing complete details and a screenshot above, andy-mcwilliam. This will surely lead me to the right response about your concern regarding importing bank balance in QuickBooks.

When you import the CSV file to QuickBooks, you'll have to remove non-transactional information in it such as account numbers, opening, and closing balance. After that, you still need to check your transactions in the For Review tab, then add them.

Please note that your bank balance will automatically be updated when there is a live bank connection. It'll not change when you're uploading transactions using the CSV file into your account.

1. Go to the Banking menu.
2. Select the bank account.
3. The Bank Balance and In QuickBooks amount will be displayed inside the blue tile.

I've attached a screenshot below for your reference.

Additionally, you can also your current bank balance in your Chart of Accounts in the Accounting menu.

To know more what are the actions that you can perform in the Banking menu to manage your imported transactions, you can refer to this article for the detailed information: Assign, categorise, edit, and add your downloaded banking transactions. It also contains steps on how to add attachments to them.

I'd also recommend reconciling your bank account every month. This will help you detect errors and keep your it up to date. For the detailed steps, kindly check out this article: Reconcile an account in QuickBooks Online.

Please let me know if you have other concerns. I'm just around t help.

Hi, I really appreciate all the help you're trying to give me, but we're not quite there yet.

I have no transactions left to Review in the Banking tab, and the QB Balance matches the current balance reported by my bank. But the Bank Balance reported by QB in the Blue Box (3 in your screenshot above) is showing the wrong value, and saying 'Updated on 25/3/2020'. It seems to me that that Blue Box figure can only be updated by upload from the bank, but that the csv file process does not carry that info. Am I correct? If so, we will just have to live with that issue until our bank gets round to implementing Open Banking, which could be a while in the current circumstances. Thanks.

Andy

Another possible approach to resolve this, would be QB to upgrade the csv upload process. One option would be to allow the user to manually input the bank balance on the same page where the relevant QB account is selected. Secondly, my bank csv provides a running balance in Column 8. If most/all bank files contain such a column, then you could get users to map to that, and tell you whether to pick the top or bottom row as the current balance. Shouldn't be too hard for you guys....

Andy

I've got the same issue. Frustrating!