 zero accounting software 2023 manually import bank data get ready to become an accountant hero with zero 2023 first a word from our sponsor well actually these are just items that we picked from the YouTube shopping affiliate program but that's actually good for you because these aren't things that were just given to us from some large corporation which we don't even use in exchange for us selling them to you these are things that we actually researched purchase and use ourselves here we have a Western Digital WD elements 20 terabyte USB 3.0 desktop external hard drive we use as part of our backup system noting that if you lower the number of terabytes of storage the price will lower dramatically as well when you're thinking about a backup system you're usually thinking about an online system or an external hard drive system like this or ideally some combination between the two given you some redundancy you can also work directly from an external hard drive like this but there are some drawbacks to doing that one being if you use this as your primary drive you're working from it's no longer a backup drive and you're gonna need a backup system possibly another external hard drive and or some kind of cloud backup system and if you're working on something that takes up a lot of short term memory a lot of RAM as you're working on it such as video editing the external hard drive can slow up the system so you might want to come up with some kind of system where you download the project you're working on to your computer to your C drive or possibly to a solid state drive which is a much more expensive external hard drive as you do the work once the work is done then save the project to an external hard drive such as this if you would like a commercial free experience consider subscribing to our website at accounting instruction dot com or accounting instruction dot think of it dot com where we have many different courses you can purchase one at a time or have a subscription model giving you access to all the courses courses which are well organized have other resources like excel files and PDF files to download and no commercials here we are in our custom zero home page going into the company file we set up in a prior presentation get great guitars duplicating some tabs to put reports in like we do every time right click in the tab up top so we can duplicate it right click in the tab up top again so we can duplicate it again back to the tab to the middle going to the accounting drop down we want to pick up the balance sheet it's slightly customized but if you have just a normal balance sheet that will work tab into the right we're going to go to the accounting drop down this time the income statement we're picking up a comparative income statement but if you don't have that you can just open the normal income statement back to the tab to the left we would like to change the date to go to the end of January which is the bank reconciliation we're currently working on customizing drop down Jan 31st so the end of January update I'm also going to open up a bank reconciliation and so we're going to go to the tab to the right right click on it duplicate the tab one more time go into the account in drop down and the reports and then we'll type in to find the bank wreck it'll wreck that bank we're working that's not what we're doing we're reconciling the bank not wrecking it we're not wrecking it let's go to the date range and we're going to go from January to January 31st and it's going to be for the checking account and then the ending date is going to be 6124185 6124 1.85 and update it all right let's go back to the first tab now where we're going to be opening up our bank reconciliation info which we could find by going to the accounting drop down account information we've been entering the data into the checking account so we'll open up the transactions in the checking where we have the three tabs here the the reconcile tabbed the the banks tab that we've been adding data in manually to and then the account transactions tab so what we want to do now is actually test out the the importing of the data into the system instead of continuing and and manually reconciling so there's a couple ways that you would get this data in practice obviously zero really is set up so that you can connect your bank account to the bank feeds that's a big you know selling point and that's what they're mainly focused on and that's what most people want to do we'll talk about how to do that in future presentations that will you know bring the data in from the bank feeds but if you want to manually do this then and you don't want to connect your bank account then you could then just pull in the data and import the data that's what we'll do this time now if you are if you go to your your checking account or your bank accounts you can find usually in depending on your bank it'll allow you to download the activity and usually you would want to download it as a QuickBooks file which is a QBO file which is really just a data format file or here we have a comma deliminated file those are two common kind of file types that can be downloaded although there are many other file types that that might be compatible that you can download in if you have the comma deliminated download the nice thing about that is you can actually add data before you enter it into the system using basically Excel if you wanted to add something like the the vendors and the customers for example so what I'm going to do is we're going to go into the to the uploading of the report but first I'd like to un-reconcile so I'm going to go into these these ones that we reconciled I'm going to un-reconcile them see if we can remove the reconciliation and then and and so that when we upload the data we'll just re-reconcile them with the data that we uploaded so I'm going to right-click and I'm going to I'm going to open this this way right-click and open in a new tab and I think the safest way to do this is to actually go into the transaction here and then I'm going to go to the options drop down and un-reconcile so this removes the link between the imported statement line and the account transactions I'm going to say okay un-reconcile boom all right and then I'm going to go back to this tab so I'm going to close this out I go back over here and then if I update this and refresh it then we've un-reconciled that one I'm going to open this one up same thing I'm going to right-click and open this one I'm going to go into the transaction I'm going to hit the drop down and say I want to un-reconcile okay and so I've done that closing this back out back over here refresh and right-click I'm going to do it again and say I'm going to go into the transaction and un-reconcile this is un-reconcile okay and one more time refresh and then right-click on the transaction open in a new tab and I'm going to go into the transaction and un-reconcile un-reconcile we're not reconciling here man there ain't no reconciling these differences back to the tab to the left we're gonna we're gonna go back in so there's so now we've got the reconciliation tab and the bank tab are back to where basically we started nothing's here nothing's in the bank statement tab and everything's over here in the transactions as having been un-reconciled so now we want to import the data in a similar fashion as you might do if you had the bank feed so so again you could download the transactions if you download them as a CSV file then you're gonna have like the activity and remember the activity is different then you won't have the beginning balance you're just gonna have the activity or you can take your bank statement here and actually you know copy and paste into Excel or it's which is a little bit tedious of a transaction to do I mean something depends on the format of your PDF file but you can copy and try to copy and paste these into Excel into these line items and and that'll help you to upload it if you wanted to use that format to try to upload the data as well we'll try to provide you with this with these files if you want to use these files also note that if I if I go back on over here and I go into manually import which if you don't have it there you can go into here and then you can go import statement here as well and I can go into that and you would think you would you the way it indicates there that you might be importing like a PDF file but no it's talking about the bank feed data generally so you got the OFX the QFX the QIF the QBO which isn't which isn't which is like another data file and then the CSV which you can get to with an Excel file if I look at their template this is the template so if you wanted to build this this is actually in a CSV format but opened in Excel or you can build it in Excel but you want a date amount then you might not have anything in the payee but the nice thing is you could add the payee if you needed to description reference and check number so I'm going to go back on over and this is our our data that were pulled in from this information and notice all we have is the date we've got the amount we've got that we don't have a payee which and we don't have a description now we might have descriptions in practice if we had electronic transfers but if you have checks you're not going to have any descriptions you're just going to have the check number right and then if you have electronic transfers that's when the bank feeds can be easier to use because then those descriptions can be used to create the payees the customers and the vendors from the description are if you build this from Excel you can say file save as and you can save it as a CSV file instead of an Excel file so you can save it as a CSV comma deliminated file and that allow that's the file type they can be uploaded if I go into here and check out the CSV file this is a CSV file this is an Excel file if I right-click on the Excel file and I look at the properties of it we can see that it's a dot xlsx file whereas if I right-click on this and look at the properties of it even though it's going to be open with Excel and looks like a similar file this is a dot CSV file so if I open the dot CSV file it will have the same numbers but the formatting has been removed so that the Excel formatting doesn't mess up the data input into the program of zero so I'm just going to now pull this in and see if we can upload this into our system here so we'll say let's go back to zero and say we're going to select a file and I want to pull this thing in here this is going to be the January transactions what did I call it there it is the January bank transactions that's the one so pull that in so now it's there I'm going to say next and then it says mapping assigned column heading headers to your file fields in zero to set how your transactions look okay so column heading we've got the date preview there's the date assign a field so in other words this is the date in our file this is a preview of it what field do we want on the header we want the transaction date you would think it'd be able to figure that out got the amount there's an example we want that to be the field of the amount transaction amount this is the payee so we don't we don't have anything in the payee but if we did it would go into the payees there's no example because there's nothing in the CSV file the description we don't have anything in the description but obviously again we would want this to be in the description line we've got the reference nothing's in the reference but if there were we would want this in the reference line and then the check is going to go into the check number alright so we've mapped everything out in terms of the headers on our file into here and let's say okay next and say next here and it says the date column has values that don't match transaction date okay so I'm going to reformat my date column I opened it up in Excel I'm gonna say they don't like this date format so let's hit the drop down and let's say we want to make it a short date so it looks like that and then I'll save it and we'll try this again I'm gonna say file save as browse it's gonna be a January CSV file CSV comma deliminated file save it I'm gonna replace the other one and let's try it again so now if I go back in here and I open up this one it now has different formatted date I can't open it with the other Excel file open won't let me do it but hold on so now we've got the new date format all right let's try it again in zero can't use that date thing so let's go back and say trash this one select it again and we're gonna say Jan transactions okay next same so now this is now it picked it up right so I think that whole thing threw it off before because the date range so it should kind of pick up the columns you would think transaction amount the payee the description and then the reference is reference and then the check number is check number all right so those line up again let's try it again all through base all right so let's go ahead and import it so I'm gonna complete the import and let's see what we got here so now if I go into these tabs now your reconcile tab looks more like you know what you would what you would kind of see if you're trying to look at the normal reconciliation process because again zero is designed to use these bank feeds for the reconciliation process so if I go to the bank statement you could see this is the information that we pulled in now I actually pulled it in a couple times because I tested it before so these are deleted items here so don't worry about those you won't have those hopefully but but we're looking at these items they're unreconciled because they haven't been matched to the books these didn't record any transactions these were just pulled in from our side and then on this side these are the account transactions which are unreconciled this is what we put in this is what's being used to create the financial statements but isn't yet tied out to the bank side of things to help us with the reconciliation now if I go to the first tab then we've got this kind of matching system which we can reconcile with and you can see that it gives you this neat match to kind of help you out most of the time so it's trying to basically help us out with this matching process let's just redo these here's the 65,000 just to get us to the same spot there's the 65,000 so it's matching these two out not recording anything new we're just going to say okay reconciled and what that does is over here on the account transactions it's now reconciled here and on the bank statement side it's going to be reconciled on the bank statement as well alright let's go to the next one and we'll just do these deposits we did last time 50,000 for the deposit so there's the 50,000 deposit I'm just going to say okay match that out and then here's the 757085 so let's see if we can find that one 757085 January 20 so that looks good let's go ahead and match that one and then we had the 20,005 on 124 so the 20,005 there it is so there's that one so that brings us back to where we were before so I'm going to go ahead and match that this way and so now we've got this cool you know matching process so if it can match them out it'll try to tie them out if they if the sensor if they it could find it if not then it doesn't have anything matched to it at that if that's the case then then you might need to add a transaction using kind of like the bank feeds because it's on the well it's on the bank side but it's not on your books side of things we'll talk more about that later the bank statement now if I go to the bank statement this is the stuff that we pulled in on the banking side you can see we've reconciled a few of these items now and then if I go to the account transactions this is what we actually did the data input for and we matched them to what we pulled in on the bank side of things and if I go into my report on the side and update my report you'll note that I have my zero balance plus the outstanding payments and outstanding receipts and then down below we've been working on the receipts down here which have been you know deep they're going to be decreasing based on us pulling that information in and it looks like my date range january to january 31st to june it's going out to june let's see my custom date it should be january january 1st to january 31st okay so it's looking funny so 88 8 10 25 and then where we've been we took some of the information out of the outstanding receipts and you can see here that we only have these two amounts one being where's the outstanding receipts plus unrecognized statement lines outstanding receipts the 25,000 beginning balance and that 34,072 are the only outstanding receipts all right so we're basically back to where we were before but now we have like what zero wants to be using on the reconciliation process better because they're designed for the bank feeds and will continue on to the decreases in a future presentation