 QuickBooks Online 2024, import bank transactions into QuickBooks. Get ready and some coffee because we're going on air with QuickBooks Online 2024. First a word from our sponsor. Actually we're sponsoring ourselves on this one because apparently the merchandisers they don't want to be seen with us but that's okay whatever. Because our merchandise is better than their stupid stuff anyways. Like our Accounting Rocks product line. If you're not crunching cords using Excel you're doing it wrong. A must-have product because the fact as everyone knows of accounting being one of the highest forms of artistic expression means accountants have a requirement, the obligation, a duty to share the tools necessary to properly channel the creative muse. And the muse she rarely speaks more clearly than through the beautiful symmetry of spreadsheets. So get the shirt because the creative muse she could use a new pair of shoes. If you would like a commercial free experience consider subscribing to our website at accountinginstruction.com or accountinginstruction.thinkific.com. Here we are in our QuickBooks Online Bank Feed Practice file we set up in a prior presentation. After setting up the QuickBooks file we talked about some of those foundational items that need to be set up in a similar way as like the infrastructure of the accounting system so we can run our normal accounting process within them. The normal accounting process typically being the recording of financial transactions usually found under the plus button grouped by cycle, customer or revenue cycle, vendor or expense cycle and the payroll cycle. As we enter these transactions we construct the financial statements one of the primary goals of the accounting system and then we can communicate with these individuals we do business with in the centers on the left hand side sales center or revenue center or customer center, expenses center or vendor center and the payroll center or employee center if we're processing payroll through QuickBooks. One of those foundational items is in the transactions and that would be the chart of accounts. So within the chart of accounts we have cleaned up some of our chart of accounts removed many of them so we can actually build the chart of accounts as we do our bank our bank feeds. However now we want to upload some transactions that we have downloaded from the bank remembering there's a couple different ways that we can connect to the bank get the banking information in other words into our QuickBooks file. We could do it in the interview process when you first set up QuickBooks. I don't typically like to do that because I'd like to see the chart of accounts. We could then go into the chart of accounts here and then set up our bank accounts. It has to be a bank type of account or an a credit card type account and then we can connect to the bank or we can simply go to the bank transactions which is the first tab and connect to the bank this way. We can also upload our transactions which is what we're going to do in the practice problem after having uploaded the transactions will be in the same place as if we connected to the bank. In other words the information coming from the bank in this location which I call Bank Feed Limbo which we then need to add information that minimum information being the accounts so we can pull it from here into the creation of the financial statements. So in a prior presentation we downloaded in a couple different formats the information from a bank. So this is the mock information that could be in a QuickBooks type of format or it could be in a CSV type of format. Now here's the CSV. This is the one that we're or this is the type of format we're going to use. We'll actually create our own CSV file and format it in a similar fashion and that'll give us a better understanding of what is actually being included when we download this information as well as what is the file type that is being used. Now if I was to upload over here to QuickBooks and I say upload transactions you can see here it says manual upload your transactions manually. How it works. Number one open a new tab and sign in to your online bank account. Number two export your bank statements in CSV QFX QBO QFX or TXT format. Now when we say bank statements I don't think they actually mean like the bank statements that you're used to seeing in a PDF format or just a paper format formatted you know with the subtotals and everything they mean the actual detailed of the transactions that are going to be downloaded and the primary formats are going to be the CSV file that most banks have which is kind of like an Excel file but it's going to be it's going to be stripped of its formatting a comma deliminated file and then we have the QBO file that's the other most common type of file that you will see you might see if you have nothing else a TXT file text file but usually I think the default is going to be a CSV if they don't have the QBO file so we recall that the data that we get will look something like this let's create our own file and that will allow us a lot of flexibility to upload our own data so that we can create certain scenarios specific to what we want to practice so I'm going to right click on Excel and just start with an Excel file noting that the Excel file is not typically what I want to be uploading possibly I'm probably going to want to convert it to a CSV file to upload it however I want to format it in Excel because Excel allows me to do formatting which will help me to kind of set up my data so and then when I save it as a CSV file it'll strip the formatting so I'm actually going to lay down the formatting I prefer which is I'm going to select the triangle right click here and I'm going to format and I'm going to say I want to see currency negative numbers bracketed and read so I can tell what's going on no dollars on I will keep the decimals and okay all right so then and so then I'm just going to mirror the column format here so what we typically have is a date type of field so let's say that we're going to be doing this in January and February so I'm just going to make up some dates to start off in the beginning of January so I'm going to say one one two four and notice it's not formatting as a date so I'm going to select the column a home tab number group drop down and I want to see it as a short date so one one two four and then we have the actual dollar amount typically so I'm going to say this was let's just say a generic one hundred dollars and then the description that's going to be in place now I'm going to say this was an increase instead of a decrease and I'm going to give it just a generic kind of description of a stripe transaction now stripe is something similar to like a PayPal transaction in that it went through a certain intermediary so that's going to be a little bit more difficult possibly for me to assign to an account and we'll take a look at that kind of situation in future presentations and then let's say this is going to be one three two four and let's say that we had then a decrease now of let's say sixty seven dollars and let's say that was for the SoCal Edison which you often will have something like this in the memo which includes Edison which is my which is going to be utilities but it has a bunch of other jargon within it and let's say that we then have one three two four again and then we've got let's say another seventy nine and let's get say this is very very let's say Verizon and then again it would probably have a bunch of jargon after that something like Verizon payment or something and then bank jargon right and let's say we had another deposit one five two four and let's say we had a deposit for nine hundred and let's say that it was just a cash deposit which means we might not have anything in the memo it might just see it might just be a cash deposit it's going to indicate in the memo in some way which means I don't have anything to tell who it came from it was just cash that was put into the bank not with an electronic transfer but in some other type of format let's add another one on one six to four and let's say that we had let's say nine thousand five hundred I'm just making this up and let's say it was another stripe transaction this time stripe transactions coming through stripe is going to be helping us to to organize our our information for deposits that are going into our account and let's just do a couple other normal ones here let's say let's say we have like an office depot one so let's say one six and let's say we said negative six seventy eight and it was office office depot that we paid or something and then it has a bunch of numbers and stuff after it and then let's say that we have one on one eight two four and let's say this was for three thousand seven hundred income and let's say that's from YouTube YouTube dot dot dot dot dot dot dot Google or something right it's going to say something in the memo something like that and that was on one eight two four and then let's say on one ten let's just keep it at one eight one eight two four let's put another stripe one at let's put we need some more decreases here let's say that we had a decrease of just three hundred dollars three hundred dollars and then again I didn't put anything in the memo is just a withdrawal withdrawal so it might say ATM withdrawal so again I don't have enough information in that case we want to avoid doing that unless it's for a specific purpose like my my personal use type of purpose right because then again I don't have the information to put that anywhere I don't know where it's going to go and then we might have different like Amazon so I might say one nine two four and let's say we had income of like of 430 from let's say Amazon media and let's say now there could be multiple Amazon type of ways that you could get paid right so depending on what you're doing for selling for from Amazon and then we could continue it from here let's keep it out here for now and then I'll save this and we'll upload it let's do a couple more let's do a rent one let's do one nine two four and let's say we had like 2,400 and let's say this was Mr. or let's say rental company just generic name that's their actual company name and then again we've got some something after it and so on so we've got the rent in there and then what I'm gonna do is I'm gonna copy a few of these so that we can memorize the transactions let's actually copy this whole thing and so I'm gonna copy this and put it down here and then I'm just gonna change the dates to February and we're gonna imagine that everything is repeating in February and so when we start making the bank rules we'll be able to see that it should be applying the bank rule to these two items so we'll do this okay so we've just mirrored the whole thing alright and so then I'm gonna save this I'm gonna file save as and let's first save it just as our Excel document and I'm just gonna call it bank data 364 that's the that's the number of the presentation that we're on so that so that I'll be able to share this and then I'm gonna I'm gonna say okay let's close that for now and let's find it so I put it into this folder and is that folder open where did it go don't day and then so it's not so here it is so there's the data file notice if I right click on it and look at the properties of the file it's an XL S X which is an Excel worksheet so now I'm just gonna open it up and then save it as a CSV file so let's open it back up and then I'm gonna save it as a CSV which will strip the formatting of things like these negative numbers so we're gonna say file save as browse and then on the drop-down I want to save it as just a comma-deliminated CSV file so I'm gonna save it and then boom let's close this back out and then there it is so now it looks similar but there you could see the difference between the two if I right click on this one and look at the properties you could see it is actually a CSV file so let's go into that file just to take a look at what it looks like so I'm gonna open it up and you can see it changed the formatting so it it just put a very basic stripped all the Excel formatting because it's really just a comma-deliminated file now all right so now that we have that let's go into QuickBooks again and you will recall where we are at we're in the transactions we're in the banks transactions we're gonna upload the file I think we have a file format we can use the CSV file we will select the file and then let me I could drag and drop it I think but let's see if I can copy the location here and so there it is and you can see when I do that it only shows the CSV file I can't even see the Excel file because the Excel file isn't a file format I can upload so I have to have this CSV file if you don't see it it's probably because you have an Excel file and it won't let you upload it so I'm gonna go ahead and say all right continue it says which account are these transactions from select the QuickBooks account for the bank file you want to upload now I I've already made the bank account I'm gonna imagine it's this B of A account if you didn't have one you can make the account as you go adding a new account I'm gonna choose this one because we made it first continue and then tell us about the format so is the first row in your file a header I'm gonna say no it's actually not we don't have a header row it's it's just the data and it probably might have made it easier if I had a header row on it but we did not put a header row so called how many columns show amounts in other words did they put the increases and decreases in two columns or are they in one column they're only in one column so what's the data format in your file the date format it's a what did I put it I put month it's month let me just check it one more time let's make sure we get this right so let's go back into here and check the date format so month day and then a four digit year so we're gonna say okay let's go back over here so it's it has two digits for the month day year so I think this would be the closest format and then it's got dashes okay month day year four digits for the year even though okay and so then the first one says date the date so that's gonna be column one is the date if I map this over so these are this is it looks kind of funny because it looks like rose but it's actually telling us it's asking what the columns are column one two and three so column one is the date and then the description is actually column three the description and then the amount is column two so we're just mapping out the data that might be a little bit easier if I had added a header row and then told the system that there was a header on it but there's no header on this one so we can just apply it out and then the check number there is no check number so so we don't have a check number because we didn't have any checks right so we're gonna say all right let's go ahead and continue so it says let's verify and import your transactions check generally income transactions post as positive numbers and expense transactions post as negative numbers occasionally some banks send us files with this reversed so do the transactions below correctly indicate income and expense keep original values reverse so if you have a financial institution which flips the positive the positive and negatives for deposits and expenses which could happen because of the debit and credit format that they're using on the banking side of things that might be why they they give you data files that have the deposits which are negatives and in the right so but and if that's the case they allow you to reverse it automatically which is really neat but we don't need to do that because our positive numbers are the deposits so we've got the stripe as a deposit and then this is so Cal's a negative Verizon's a negative cash is a positive stripe is a positive office depot is a negative YouTube's a positive ATM withdrawal negative so I think everything is good so let's go ahead and say okay select all of them boom and then we'll just continue so quick books will import 20 transactions using the fields you chose do you want to import yes we do all right and so import completed next step accept your transactions you're in control of how your bank info goes into quick books transactions only show up and your books after you review and accept them in other words they're putting them into what I would call bank feed limbo and then it gives you this little widget here power up quick books to get organized automatically review a few expenses now to help quick books learn how to categorize for you when you're done see a snapshot of how you spend so we could review this I'm going to skip this because I think it's just as easy to just go into the actual transactions it's trying to automate the transactions which quick books might be okay at doing I'm going to close this out I'm going to I'm going to say and then up here deposits that work as fast as you do you have money on hand I'm going to close this out they're trying to upsell me on something so I'm going to then close the hamburger so now we have our little tag here and I like that quick books actually made this little tag a lot smaller than it has before because it takes up a lot of empty space up top downtown you down down down below you have the actual transactions now we'll go over these in more detail what I just want to point out now is that it pulled it into what we call bank feed limbo it's not actually creating the financial statements in other words if I if I was to duplicate this tab and then go into the reports for example on the left hand side and then go into the reports and open up for example a balance sheet which should have stuff in it if anything was posted you could see nothing's been posted so this is what I mean by that by the idea that if you pulled in a bunch of data you might think that everything's just going to happen automatically because that's kind of the marketing of most online accounting softwares with bank feeds they kind of market as though it's just really just a snap of the fingers everything is ai you could just pull it in and it'll but that's not really the case you still have to kind of if you think of ai terms train it you have to give it rules so that so that you can add the vital piece of information that last piece being at least the account that it needs to go to number one and two the the contact either the vendor or the customer is also useful information so we'll go into this whole field in more detail in a following presentation and then we'll start to basically add these transactions and as we do we will create bank rules so the first time we add the transactions from the first set of bank feeds is very important because then we if we do that rigorously and set up good rules then it'll be automatic going forward you could try to use quickbooks not quickbooks is trying to guess where it should go but i would make your own rules right because because you want to make them you want to make sure they're correct because it's going to repeat after this point in time so you'd like to really spend a little bit of time on the first few months and every time there's a new vendor or a new customer and once you get that done well then you automate the process with confidence and we'll talk about that more in the future