 QuickBooks Online 2023 Update Bank Feed Practice Data Get ready to earn the skills needed to boost your bank books on up with QuickBooks Online 2023 Here we are in our QuickBooks Online test company file using the accountant view as opposed to the business view. You can toggle between the two views by going to the cog up top, switching the view on down below. We've been thinking about an e-commerce type of situation selling inventory but not on ground in a store rather online in the cloud with the help and use of third party platforms such as a Shopify or Amazon for example. Our goal now to pull that information from these third party platforms to QuickBooks Online in such a way that we can build financial statements. In prior presentations we talked about different options to pull that information in including number one the use of bank feeds, number two having a manual entry process of reports that are generated on the third party platforms and integrating bank feeds. Number three using the QuickBooks Online commerce connect to the sales channels and using bank feeds and number four having third party apps such as A2X and bank feeds. No matter which of these we use typically we will have a connection to the bank feeds that will be part of our integrations. So let's support account instruction by clicking the link below giving you a free month membership to all of the content on our website broken out by category further broken out by course each course then organized in a logical reasonable fashion making it much more easy to find what you need than can be done on a YouTube page. We also include added resources such as Excel practice problems PDF files and more like QuickBooks backup files when applicable. So once again click the link below for a free month membership to our website and all the content on it. Let's think about how to turn on the bank feeds here we have a whole nother course or section on bank feeds that you can take a look at. But we'll think about how to set up the bank feeds fairly quickly and how we can pull data in and we can think about how to set up a practice data that we will be using here importing it from a CSV file that we will create from an Excel worksheet sounds complicated but it's not that bad. So when we're trying to set up the bank feeds clearly we need to have a bank type of account that's going to be in our chart of accounts. So one way you could do this is you could go down to the accounting on the left hand side and we want to have our chart of accounts up top. And we need to have the banking type of accounts set up so if you don't have a checking account already set up as we do here. Then you can go up and say you want a new account and you want to make sure that it's going to be a bank type of account. So it's going to be a bank type of account that will have set up here and then you can name it basically the checking account. If you so choose and then you might want to set up another one in our example file. We're also going to imagine that we have a PayPal type of account which you can connect in a similar fashion to the bank feeds. The PayPal is a little bit different because when you actually connect the PayPal you can either use like a bank feeds type of situation. Or you can have a little bit different of an integration the PayPal app which might be useful to break out fees on the PayPal side. Once we have our accounts set up then we can basically connect the bank feeds to them or this is one way that we can put the bank feeds together. The other way is to set up the bank feeds and then create the accounts as you're setting the bank feeds up. Now these are already connected as you can see here so I don't have the option to connect to the bank feeds from here. I'm going to go up to the banking now and when you go into the banking section once you have the bank feeds set up you'll have your little tags up here for whichever accounts have been set up for the bank feeds. So if you don't have any bank feeds set up you can link the accounts up top so we have the option to link either with a link or we can upload from a file. What we're going to do is upload data from a file but first I just want to show the linking option here just so you can see it. If I linked the bank then we can go through here and we can choose our bank and it's a pretty straightforward method to link to the bank and then pull that information into the system. Note however with PayPal we've got this PayPal and then we've got the PayPal Connect. So you might want to just do a little bit more research and see if the PayPal Connect which is kind of the cooler option. It's got more stuff meaning it breaks out a little bit different than a PayPal from a checking account at normal kind of banks and it breaks out some of the fees which is nice. So that could be a good tool but it also adds a level of complication. It would be cleaner and possibly easier to just use a bank feed that's like all the other bank feeds which you can basically use PayPal as. Note PayPal is a little bit different than other payment processing tools like if you used a stripe or some other kind of payment processing tool. Usually all you're using that tool for is to facilitate the flow of the money from the third party website like a Shopify to your bank account. Basically that's what you're using it for. But with PayPal obviously it's acting more and more like it maybe not so obviously for a lot of people but more and more it's acting like a checking account. So now instead of just facilitating the receipt of the payments from our Shopify or our Amazon store we're using it for other types of things which means we need to connect to it more like a checking account. And it's getting better and better kind of having that connection option like a checking account. So those are the two that we're going to set up now instead of connecting because this is a practice problem I'd like to just upload the data. So I'm going to upload the file and I'm just going to create some test files in Excel over here to upload. So this is just a mock of the information that you would get from the bank account. I'm just going to put this one transaction in it at first and pull this information into our QuickBooks online just so we have the data in it. And we can see what it actually kind of looks like from a bank feeds standpoint. So the data from a bank feed if you just open up Excel and type in a header date amount and then I'm going to say bank memo or description. This is the bank text which is usually going to be populated with something that has like either a customer or vendor name. If you're doing electronic transfers which is typically the case instead of doing checks and whatnot because you're in an online business situation. And then I'm going to put the date down here. You're going to have to format to get that date right to make it a date field which you can find here in the numbers group and make it into a short date. That's what I'm using here. And then I saved it as a checking account bank feed one. Now I can't upload an Excel document to QuickBooks online but I can upload a CSV file which is like a comma sliced file. And all you do is switch an Excel file to that type of file which will open like an Excel file without like all the cool stuff that Excel can do. It's really a comma differentiated data series. You can go to the file up top and save as browse and I'm not going to save it as an Excel file. I'm going to switch that and I want to save it to a CSV comma deliminated file. So now it's just going to strip Excel of all the of all the bells and whistles and have just a plain file. So if I if I look at what that looks like then I'm going to close this back out and say this is the shop. This is not this one. Let's take a look at what it looks like. So here's the Excel file and here's the comma sliced file. And if I right click on the properties of this file and say I want to take a look at the properties then it's a dot CSV file as opposed to this file. If I right click and take a look at the properties which is a dot XLSX file. I'm going to do the same thing for a PayPal one now. I'm going to make date amount bank memo. This is the date. This is the amount and this is the memo that I'm going to put in. I'm going to save it also as a dot CSV file file up top save as browse. We want to make it not Excel but a dot CSV comma deliminated. There we have it. All right, I'm going to close it up. So now there's our two files. I'm going to upload those to our two bank type of accounts that we have set up respectively. So we'll say over here. So now I'm in. Let's do the checking account. So I'm on the checking account. If you don't have anything in here, that's okay because then you will assign the account when you have the upload. So I'm going to hit the drop down here and I'm going to say that we want to upload from a file. I'm not going to link to the bank but upload from a file instead. So here's our uploading process. We can have a CSV file or any of these other kinds of files. We're using the CSV file. All right, let's open it up and I'm going to say there's the location. So the one I want is the checking account. There it is. Looks good. Continue and then it's going to choose the account. I'm going to put it into my checking account. This is where we tell it which bank feed account we're going to put it into and then continue. And then it says, is the first row in your file a header? Yes, because that's where we put the date, the header stuff. And then column one, how many columns show amounts? We only have one column instead of having one for increases and one for decreases. I would show decreases with a negative number but we only have one deposit at this point. The date format I use was month, month, day, day, year, year, year, year. And then the date is coming from the date column. This is mapping. This is simply mapping if I open this up to the headers. So here's our headers. So it's just saying, yeah, that should be the date, amount and bank membo. So I'm going to go, all right, header, date. That's column one. And then the second column, he couldn't pick it up because I named it something different than description, which is what it was looking for. But I'll just map it to bank memo and then amount is going to be the amount column, which makes sense. So date, description, amount, date, bank membo, amount, okay. Pull it in. There's our one transaction. We're going to pull it in. Boom. And QuickBooks will import one transaction. That's what we like to see. Import complete. Next step, access transactions. All right, let's see it. Mui B to the N. So we're going to go on. That's BN, BN. So there's our bank transaction. So that's, so now we can kind of look at what it actually looks like in a bank feeds without pulling it in from the bank itself. Let's do the same thing for PayPal over here. Now we already have a couple transactions in PayPal because I just want to see and show how the PayPal, because I'm using the PayPal commerce, is slightly different than the bank feed link. But for now, we want to go ahead and add again. If you don't have PayPal set up, same situation. That's okay because you're just going to upload from the file if you want to follow along with this. And then when you do so, you'll be able to choose the account of the PayPal that we set up, the bank account. So we're going to upload the PayPal CSV file, CVS file, CSV. It's file CSV file. That's what a comma, comma, slice, comma, whatever. And that's the one we're going to say, okay. And then here's where you choose the account. So this is going to be the account for PayPal, PayPal, PayPal, PayPal. Okay, so then we're going to say that it's got a header line too. And how many columns? One column, okay. What's the date format? Month, month, day, day, year, year. Date column lines up to date. The description is going to be that memo column. The bank memo amount is the amount. Lines up. Movie B to the end. BN. I'm going to select that one and go okay. That's what I'm going to do. I'm going to go okay and then save it. It did it. And so import complete. That's what I like to hear. And so that now this one has imported. So now we can kind of get a feel for the bank feeds, how the bank feeds kind of flow in. That's one of the big integrations that we will have no matter what system we are using. We're going to generally connect to the bank because we have to reconcile to the bank. And that's going to be part of whatever system that we will use. Now just a quick look at this. Notice that I'm using the PayPal that has the PayPal connect here. And it's pretty cool because some of these payments notice that it shows you the fees on the PayPal side. So if I go into like this one, for example, it says income account is a dollar. But we took PayPal, took fees out of it. So there's going to be a PayPal fee of 54 cents. So it's kind of doing the fees on the PayPal side for you, which in theory could be could be nice. Because if we're doing some kind of system that's breaking out the fees on the Shopify level. Remember, we might not have the breakout of the fees on the PayPal level. Because some of the some of the interactions, the PayPal level are not going to the Shopify level when we pull into the data. Although again, it gets a little bit more confusing than just simply having one deposit that we assign out to which account that it's going to be going to. It also has a bit different integration up top where you get to decide if you want to make a sales receipt or deposit or transfer. Whereas if you look at the other bank fee normally, you only have the choice of basically this is a increase to the bank account. So it would be a deposit. Notice I don't see the breakdown of allowing me to create a sales receipt with it in this in this situation. Because usually the bank feeds only allow increases to basically be deposits or possibly some kind of transfer if it's interbank feed. So the PayPal Connect app is a little bit different, giving you a little bit more options and flexibility. But always when you have more options and flexibility, it generally would lead to more confusions and different paths that you can take and possibilities to take a wrong turn somewhere. But then what we'll do in the future presentations is we'll start to think about, OK, what if I just use those bank feeds to record revenue and we'll work it out kind of in Excel here so we can see it pictorially in a more transparent way. And then we'll see how it kind of plays out in PayPal or in QuickBooks Online.