In this tutorial, Linda and the host walk viewers through the process of importing a full year of historic bank transactions into QuickBooks Online (QBO) and then switching to live bank feeds. They start by advising users to download their bank statements from the financial institution in QBO or OFX format (CSV is possible but less detailed). The file is then uploaded via QuickBooks' Banking > Update > File Upload, linked to a temporary “QB Community” account, and imported. They recommend enabling check numbers and bank details for easier rule creation. After import, batch actions are used to accept the transactions, then the temporary account is disconnected to prevent QuickBooks from treating the import as an active feed. The user then reconnects the real bank feed, choosing how many days of historical data (7, 30, or 90) to pull, and excludes any duplicate transactions. The tutorial also covers the common issue of an automatically created Opening Balance Equity entry that must be removed or adjusted via a journal entry, noting when it is appropriate to keep it. Finally, they stress reconciling the account, clearing excluded items after reconciliation, and verifying that opening balance equity is zero before completing the setup.
Chapters
Hello, Linda! How are you doing on this fine weekend before— wow, the last weekend before QuickBooks— well, before you come out to visit for QuickBooks Connect, right?
That's it. You better have a clean house because I'm coming to town.
Yep. Well, I know we've been— I've been slacking on some of the videos here as we're getting things prepped up, but you've brought us a good one this time on my favorite topic, bank feeds.
Yeah, everybody's favorite topic. One thing, the one pain point that drives pro advisors or bookkeepers crazy is bank feeds. Just saying.
Really?
Yes.
Because people do them wrong.
No, no, no. It's like they change the connections and then you can't always make the update. You got to bother the client to make the update on some of the things for the new one and they break. Yeah. But anyway, I digress. All right. This one's by Nelson GB. And how do I add historic bank information into QuickBooks? I'd like to add my banking data for the entire year, but also have the automatic feeds moving forward for my bank accounts. Is there a way to integrate these transactions either in a distinct historic accounts or in another way?
Awesome. So what it sounds like is this is a— I'm trying to get caught up for the year and you get all of my data in up to this point, but then I want to switch over to the bank feeds through QuickBooks Online and have it continue to progress from there. Sound about right?
Going forward, yeah, because right now if you get 90 days, you're not going to get the 2018 in.
Yeah, that's true. Fortunately, this is actually very, very easy to do. Let's go over to QuickBooks here. I created just a fake account I called QB Community. To pull your transactions in like this, the step 1 thing to do is you need to go to your financial institution and download all the transactions. We're assuming from the beginning of this year, so whatever the period of time would be, from that beginning timeframe all the way up to current.
Okay.
Once you have all those, download them in a QBO or OFX file type is the best way to do it.
Easiest.
You can do it as a CSV, but you get better information as the QBO. So again, most financial institutions will let you do that, and it's a .qbo end. Um, then what you would do is you just go to your banking, and under banking, we'll go, uh, to the dropdown arrow by update, go file upload. Browse for that file you downloaded from the bank. And let's see here, we did here, and we'll pull up just this file here and click Open. Now in this next step, it's going to ask us to tell it which of the accounts we want it to be linked to. So we'll go to our new account here. QB Community, and we'll go next. Depending on how big the file is, this will cycle larger and larger and larger and then get it pulled in. And then boom, all of your transactions are going to be pulled into that specific account. So this one was a small file, something that we pulled in just real fast, but all of your data will be pulled in. I always recommend for the gear icon, you should always check off the check number and show bank details because I like to see the extra information that's being pulled in. This was a file we had made. That's a good tip. Yeah, it makes it a lot easier when— if you're going to go through and start trying to use like the bank rules to get through the data faster, you really need to see that bank detail to figure that out much quicker.
Right.
So now the next step would be you would go through and this one, we've got something pretty easy here. We can see these are all supposed to be for Shell here, right?
So, big Shell.
So real quickly, just go batch actions. We're going to modify. We're going to get these done and out of the way real fast. And we'll go apply. So now we can go accept these ones because we've set up what they're supposed before. And really the purpose of me showing you this is you want to get to the point where everything is completely cleared out of this bank feed area. Once that's done and everything's been accepted where you need to go, now you can go to that same account, okay? And you can click on the pencil. You're going to disconnect this account. first because we want to— when you import a file, it thinks it's connected to a bank feed. So we'll disconnect this on save. And now what you can do is you can go to your bank feeds, add an account. You would go to whatever your institution is and pull it up, make the connection. And once you've done that, and you're going to have to link what bank Or which account you're setting up through the bank to connect the 2 pieces together. So this is ongoing. I'm sorry, ongoing.
Ongoing. So you can keep it connected and flowing in. Yeah.
So as that's going through, you'd set it up. You'd have to do all of your details for the connection part of the process, which we can't really do at this moment. But there is one step right before you bring all the data in that's going to ask you how much time you want to bring in. It's either 7, 30, or 90 days. If you were not able to bring all of your transactions up to date into the account, let's say you only brought up, you know, you didn't have the last 30 days or so, you can fill in up to 90 days through the automatic bank feed connection. But anything that might be overlapping, we cleared out that whole area first. So when the transactions come back in, You could actually go through, check off any of them that you don't need because they'd be duplicated. You're going to go Exclude Selected.
Right, so if that said Shell, Shell, Shell, Shell, Shell, you already know you brought them in, you want to get them off there.
Exactly.
And then you might even want to exclude them from the excluded list too, to really make them gone.
Yeah, now what we always do is we wait to exclude them all the way. And what we're talking about is you have up here this For Review in QuickBooks and then Excluded. We wait to do that final excluded all the way until we've completed the reconciliations for that period of time.
Okay.
So that way, just in case you made a mistake and you excluded ones you could have used, you don't have to manually put those in. You could just undo the exclusion and then be able to use them. But once you're done with that reconciliation up to that part, absolutely clear out the excluded every month. Makes it to where you don't have to worry about them being in there. And you're good to go, nice and fresh, basically.
And then one other point too is that if you import the bank file and it doesn't give you an option of like 0 days and you've already brought in everything, it's going to bring in that beginning bank balance transaction, open and balance equity. You got to delete it. It'll come in as reconciled. You won't even see it on that bank feed screen, but you're going to need to get it off of there.
Very, very, very good point. So we're going to have— did I already miss it?
Opening balance equity. No, further down. I think there is one in there.
So under opening balance equity, there shouldn't— your opening balance equity is always supposed to be zero. If you import a file like we did to begin with, it is going to think whatever the beginning balance is that your bank stated, because it's kind of encoded into that file, it will automatically create an entry That will tell it that there was a balance. So generally you want to be able to take and remove that like you were just saying. So that should probably—
Because it'll never reconcile.
Exactly. So this is a test company file where we have tons of this stuff sitting inside of here, but these should actually all be cleared out. And even if it was a situation that you were just getting set up and you were pulling these in, this would be handled through an adjusting journal entry and hit your balance sheet appropriately so you could start the reconciliation process. So if you had— if there was— if this was created by accident, you would come in and you would remove it. What I usually recommend is I would put— until you know for sure exactly whether you need it or not, I would put a note To something like reduced the opening balance equity back to zero from $315.54, just so we've got— Yeah, so it's there, yeah. And that way, if there was a reason that you did actually need that information, you've still got it there. And it can be switched back around. Now, Linda, there are certain times when that opening balance equity should be there, right?
When?
So let's say the accounts that they're creating for the very first time, they're setting up in 2018, and there actually was $5,523 sitting in the account when you first started, then it should pull that $5,523 in as opening balance equity, but you would then make your adjusting entries to remove it, put it back to zero, and that way it can go into the account as actual, like, equity into the business itself.
Right. So yeah, you don't want to see it in that account, but if it's in there, it's just the fact that it comes in and it's reconciled is what throws people because it's not sitting in that bank feed. So you don't— you go to reconcile, I'm like, why am I off? I didn't have it, you It should have been a zero balance, or it should have been whatever balance was at year end, and it's sitting there and you just need to get rid of it. And it's kind of scary. People go to delete it and it says it's reconciled, you get that warning. So it's good to know that it's okay to get that off of there, because if you hook your bank account up and undo it and put it back on, it's just always going to keep coming back. So you got to get rid of it.
Yep. So 99.9% of the time, it's always got to get removed. So, so quick recap. Yes, you can Go to your financial institution, download all your transactions for the given period of time that you're trying to rebuild, if you will. Take note of what the last date is of transactions that are actually being pulled in. Once you have all that information, be able to clear out the bank feeds completely, then link your bank feed through QuickBooks Online. Eliminate any transactions that may have been duplicated because of a dating overlap. Okay? Once that's done, you can process the rest of the transactions. And then don't forget to go in and check that opening balance equity for any transactions that were auto-created that would be throwing off your reconciliation balances.
And reconcile.
Yeah, definitely reconcile. So anything else we forgot, Linda?
I don't think so. You probably confused the guy.
Hopefully we helped.
Because there's a lot of steps there, but hopefully if they follow it, I mean, we'll write it out in the words too, so it'll help. you know, walk you through the steps, but it's always good to have a visual, which is why we do these.
Absolutely. Well, on that note, here's wishing you a very successful week.
