 QuickBooks Online 2024, download bank fee transactions from the bank. Get ready and some coffee because we're about to go on stage with QuickBooks Online 2024. First a word from our sponsor. Yeah actually we're sponsoring ourselves on this one because apparently the merchandisers they don't want to be seen with us but but that's okay whatever because our merchandise is 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. I 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 fee practice file we set up in a prior presentation remembering the major objectives of the accounting department or bookkeeper being number one the creation of the financial statements balance sheet income statement otherwise known as the profit and loss and related reports typically done with the help and use of the forms under the plus or new button those forms broken out into the categories of the customer cycle vendor cycle payroll cycle number two communicate with the people we do business with customers vendors and employees with the help and use of the centers on the left sale center or customer center expense center or vendor center and then the payroll center if we're processing payroll through the QuickBooks system the first thing we need to do of course when we're setting up the QuickBooks file is to first get the QuickBooks file which we did in a prior presentation lay down the foundational items which are usually thought of as lists which you can find under the cog up top and then go into the lists the primary two lists necessary before you can enter forms are usually the chart of accounts and the products and services in a prior presentation we went into the transactions tab to find that chart of accounts and we basically removed all of the accounts except the mandatory ones so that we can build our own chart of accounts as we do transactions primarily from the bank feeds then we thought about how we can connect to the bank fees just a quick recap you could connect to the bank feeds during the interview process as you set up your QuickBooks file I don't like to do it at that time because I first like to look at the chart of accounts you could also create your accounts first and then go into the connect to the bank or you can go directly into the bank fee transactions and you can connect to the bank here which can be useful because it will actually create accounts for you if you do not already have them as you import from the bank or we can upload the transactions that's what we're going to practice this time we're going to think about uploading the transactions how would we get those transactions when might we want to upload instead of connecting to the bank now when you first upload the transactions into QuickBooks you sometimes want to go very far back in time because you might not have done your taxes for the last five years or something and you're trying and you're trying to go back and build your financial statements from the bank feeds for a long period of time so hopefully you're not in that situation but if you are you might find a limitation in terms of how far you can go back with just connecting to the banks and you might find that you could go back further if you actually download the transactions from the bank so that's one reason you might download from the bank instead of connecting to the bank you could also if you just don't like the idea of connecting to the bank on QuickBooks then that might be another reason why you would just download the transactions from your bank and upload them to QuickBooks or of course if you're practicing with this this system that's another reason that you might just upload and that's what we are going to use here so once the information is uploaded into QuickBooks it will be the same as though you connected to the bank in that those transactions will be directly kind of from the bank and they'll be in bank feed limbo so that's what we're going to practice with here and so let's just take a look at the types of files that you might look at and the way you might get this information directly from your bank now all banks are going to be of course different in terms of their website but most banks these days have the capacity to download account activity so if you have online banking then you're probably going online to your bank account to look at the transactions in the list of transactions and you would think if they have the capacity to do that if you have the capacity to look at your transactions you also should have the capacity to be able to download those transactions in a format which hopefully can be then uploaded into QuickBooks so usually you could find something like download account activity with whatever financial institution you do business with and then you can then usually have something that looks like this again every financial institution will be different but usually there's going to be something like a range so if you're saying I would like to download a list of transactions then the next question would be what's the starting point and ending point of the transactions you want to download now if you're starting a new company file then you could just basically download the transactions from the point in time you started the new checking account for your new company file if you're already had a company file and now you're connecting to the bank whereas you weren't connecting to the bank before you want to make sure that your range is appropriate in that the starting point is starting it's not going to overlap with the with the transactions you already have in the system right so and and and you also want to make sure that you don't miss any transactions in the system so make sure your cutoff date here is appropriate and then up to as far as you want to go and then they'll typically be a list of formats that you can download the data file into now we of course are in quickbooks so we can download as a quickbooks file which they're calling a web connect file that's probably the cleanest thing to be able to do because you can download it and you can upload it there don't mistake quickbooks for quick in quick in used to also be owned by into it but then I believe someone else bought it a while back so it's no longer owned by the same company even it's a different file you're not going to be able to I haven't tried but I don't think you're going to be able to upload the quick in file to quickbooks so don't let don't get those two confused and then the other format is the comma deliminated which is basically just a data file that is separated by commas but usually if you were to open a comma deliminated file it would open with Microsoft excel so it's going to look kind of like an excel file for most people but you have to note it's not actually an excel file because there's some important differences we'll take a look at shortly now again the file you would probably want to most cleanly do this with is the quickbooks file because that's designed specifically for quickbooks however the comma deliminated file is something that we can actually create and adjust with the help usually of excel so if you wanted to actually format some of the data that you're that you're adding into quickbooks before you actually upload them with the bank feeds possibly even being able to add like accounts and stuff like that then you might first put it into the comma deliminated or for example you might try to change the memos in a in a in a way that's going to be easier in excel to represent the the contacts including the customers and the vendors or something like that so that when you upload it into quickbooks then it'll be easier to convert that memo convert the memo line into the customer and the vendor so there are reasons why you might want to use just the comma deliminated file even if you have access to the quickbooks file because you can adjust it before you upload the data into quickbooks now here is just a look at the two files this of course is the quickbooks file it looks like it would be like a quickbooks icon it looks kind of like the quickbooks desktop icon but it's not if i right click on it and i go into the properties of it then i can see here that it's it's a qbo it's it's called a quickbooks o f x data qbo file so that is a data file so all it has is the data for your bank in it it's not an actual software file it's a data file and then here is the information in a comma deliminated file a csv file now it looks like it's in excel because when i open it excel will be the program used to open the file but it's not actually an excel file because if i right click on it you can see if i go to the properties then it's going to say that it's a dot csv or microsoft excel comma separated a value file so if i and let me just show you the difference if i open this file up and you don't have access to this file you can practice downloading from your bank to look at it we'll actually create our own data file using excel so you kind of get an idea of exactly what the data file looks like or how it was created exactly what it is when we upload it but here's just like a sample of the data of types of data files that could happen now if i was to format this like say i made it yellow or something and then closed it again it says save your changes i could try to save it but then it wants to put it somewhere it's not the point is it's not going to save the changes right because it's in a csv file so if i if i i'm gonna i'm gonna say don't save even if i saved the file and i opened it back up the point is it's not going to save things like yellow or formatting in a comma deliminated file because it only it's really data that's just separated by a comma which excel is able to read and put into a column format so the whole point is that we don't want all the added stuff from excel because we want to just a clean comma deliminated data file to be able to upload to quickbooks so all the other formatting of excel doesn't mess it up so if you wanted to then adjust this in excel so that you can adjust the data in it and then save it you could save go to file save as and then when you save it you change the type the file type is currently a csv file versus an excel file now in a following presentation we will do the opposite we will create an excel file to build our mock data set and then we will save it as a csv comma deliminated file so we can upload to quickbooks that's a really great tool to be able to use to practice with the bank feeds because you can you can practice different types of data sets and then and then map them out as you know they would export from the bank and then upload them up into to quickbooks so that you can practice things like bank rules which will do in future presentations so two methods that you might practice with the bank feeds you might download your actual data because maybe you don't want to connect to the bank possibly because you're just practicing with it and testing it and then you can upload your actual data we in in this course we're going to primarily be making our own data set method number two using excel creating our data set now that we've seen the idea and the format of the data which is going to be in this format and then we'll save it as a csv file so that we can customize our data sets to be able to upload also so that we can get all of our data on the same page so everyone's got the same kind of stuff and i can also provide those files to you as well if you wanted to just use them instead of create them all right but the general idea what what is this file going to have in it whether you're doing whether you're looking at this type of file or this type of file quickbooks file that's just a data file or the comma deliminated it's going to have this basic data in it which is basically the same data that you would see on a bank statement but it's not going to have the same formatting of the bank statement it's not going to have the subtotals and all that kind of stuff it's just going to have a list of the transactions which will include a date column and then it's going to include one or possibly two columns depending on the financial institution that you are in i think it's becoming more standardized to have all the transactions in one column because when you're thinking of it from terms of the cash account it's either going to be an increase a deposit or a decrease so we're just going to have increases and decreases and then there's going to be a memo line noting that the memo line is only going to be there it's only going to be relevant if you have electronic transfers so we talked a little bit about the different kinds of transactions that you might have so for example if you deposit cash into the checking account and you just deposit cash it's not going to have anything in the memo line except for there was a cash deposit it's not going to have anything that's going to help you to record the transaction what kind of help do we need to record the transaction we typically are going to need something that's going to indicate the vendor or the customer so that we can then apply it to an account in other words if we was money going out then hopefully in the memo line we have something that would indicate who we paid and that person that we paid is not the account that we will create but it will often give us an indication of which account we can create so if it said Verizon for example then I can say oh then I'm going to say that's the telephone company and I'm going to apply that to the telephone expense type of account and I can also apply the vendor of Verizon so that's going to be the general idea now if it was a check then of course you if you wrote a physical check it's not going to have this information in the in the memo line either although you will have the cancelled checks typically and you can you could go into the cancelled checks but that's going to be a lot more tedious than electronic type of transfers also if you use intermediary type of type of financial institutions like a stripe or a PayPal or things like that then you can see here in the memo it's going to say stripe or something like that right when money came in and it goes through stripe or goes through PayPal as we'll see in our example problems then you could you have some choices to make there the question is going to be do I need to pull in more detailed information from stripe or PayPal about the deposits I've received or do I just want to record those deposits all as a lump sum to income or something like that which would be the easy thing to do so that's another area where you're going to have limited information even though you have like the electronic transfers that are coming into your system also note that when you have these electronic transfers like this one for example money is going out to the SoCal Edison so this in essence looks like it's going to be the vendor which I could copy and create a vendor from and then I can use that information to intuit what the expense account would be and I would think well that should be like utilities or I can call it you know the the electric expense whatever I want to how detailed I want to get but you can see that that's not the only information in here we also have this this jargon as well so this memo line is not the same thing as the customer or vendor it's not going to be exactly the same thing it might include information which will give the name of the customer or the vendor which means on the bank memo line you're going to have to copy the information that relates to the customer or vendor create the customer or vendor from that and then use that to assign the proper account once you do that you can memorize the transaction so that when this whole thing memo line shows up again or something similar or something that includes this in it then you can you can tell QuickBooks what to do with it so it's not like it's so the memo line is is going to have more jargon is the point in it than you might be you know than you might imagine at first all right so if we go like so again Stripe could be could be kind of an an issue with the intermediary accounts anytime that you pay that's usually going to be the easiest accounts that you can deal with with those reoccurring transactions like the utility bills those are the ones that are most easy to assign typically interest payments we can basically from the bank we can deal with those fairly easily if you're dealing with credit cards you have the same kind of thing with the credit cards in that you can connect the credit cards to the bank as well and we'll talk it will actually show how to do that and then you'll have some of these inter company bank feeds between the credit card and the bank meaning you're going to be paying from the checking account which is connected to the bank feeds to the credit card which is also connected to the bank feeds so we'll see what those kind of transactions look like if you're dealing with with like platforms like a youtube or like audible and or amazon and and those people just pay you and they just pay you directly into your account that's the easiest kind of transaction to record on the deposit side because now i'm not sending out an invoice i'm not a check register i'm just receiving money and i'm waiting till it clears the bank it's an electronic transfer so that i can see who it came from and then i can assign it to an income account uh so so those are going to be the primary kind of transactions here okay so those i think that's basically it so next time we'll take this concept and we'll build our own uh uh excel file of transactions to practice with convert it to a csv file upload it to quickbooks and then start start testing out in a systematic way the types of transactions that could be incurred and how we can include them from bank feed lembo into the promised land to create the financial statements