How To Sign Nevada Banking PDF

Industry sign banking alabama pdf online

what I wanted to do is I wanted to share a couple of techniques that I've learned over the years about entering data into QuickBooks desktop or QuickBooks Online and there's a particular software which you talked about towards the end that I used to convert data across multiple platforms called two qbo convert pro so first what I'll do is I'll discuss just kind of introduce myself real quick I'll discuss the difference between working with bank data or or credit card data in QuickBooks Online vs QuickBooks desktop just gonna give you a quick Reader's Digest version of the difference then we'll talk about downloading these transactions from the bank and in the different formats that the bank gives you and and whether some of these formats are useful or not and then we'll talk about when we don't have access to those formats that we need to use bank feeds and we have a PDF statements or maybe even spreadsheets and how that will work using the software called to QBO converter pro and if you want to kind of open up a side screen you never heard that software before just go to www.hsn the first half hour so talking about QuickBooks itself and then we'll jump into talking about the software and some of that and some of the the nuances behind it and what he can do and what it cannot do and so forth anyway about myself my name is Hector Garcia I'm a CPA I'm a practicing consultant in my accounting firm in Miami and I'm very busy it's tax season I'm sure a lot of you guys are as well and one of the things that we do a lot during the first quarter of the year is a whole bunch of data entry and we get data in different formats we get bank statements we get credit card statements in paper we get Excel spreadsheets we had people give us access to their banks we use hot dog an app to fetch these things I mean there's so many ways we can get that data so it becomes a little overwhelming managing and figuring out was the best way to use it I mean we done some we do so much data entry just strictly data entry that a lot of CPA firms contract us my staff have a bunch of data entry staff to do this data conversions for them so they can take care of data we can do all the data conversion on the back end and given the the files ready to go so we literally that's kind of like one of the services that we offer but most of the value in that service is the software that we use and basically I'm here to showcase what I know and and and I'm actually kind of giving up the kitchen sink with this because I'm kind of explaining to you the process of how how we function internally and one of our advantages to it but I like the software so much I talk to the software manufacturer said let's put some like this together and and I don't work for them I don't I don't I'm not an owner of the company or anything but you know we do have a relationship in which I help them develop the product and in exchange I do get paid to consult with them and improve the product so so I am a third party but at the same time and I'm deeply involved with them and helping them make the product better so that would be sort of the second let's call in the second half of the webinar so for the first half let's talk about some of the built in methods that the QuickBooks has for importing data so in quickbooks online there is a salami we'll start with quickbooks online so in quickbooks online there is in the gear box on the top right there's a button that says import data and you will think that for the most part that would do more than what it does it not really to import data the only thing that it really does is allah allowed your import lists so i can a list of customers list of vendors list of accounts so there's a button here for vendors and when you want to do is you want to make sure that you understand what that is because when you're entering a massive amounts of data a lot of times these vendors are not in quickbooks so you may have to import this vendor list preemptively before you actually do the data entry we'll explain that as we go into the process i'm gonna hit cancel so that's kind of like the only way the import vendors to QuickBooks Online now we're working with QuickBooks desktop which looks like this and let me just go to the home page here real quick the tool is called ID edit so we're gonna the list menu we go to add edit multiple entries and then we go into vendors and then in here you will literally literally just paste your vendor list so that's kind of the first premise that I wanted to set which is you know we need to know how to import our vendor list or you know our item list or vendor list whatever so we can have that entire list in there before we do bank feeds so important to know that's the first step second step is understanding the difference between bank feeds and batch enter or importing data so I'm going to start with whoops I'm gonna start with badge enter data so if you work with QuickBooks desktop accountant or QuickBooks Enterprise you can use the batch enter data to literally copy and paste data from Excel assuming that I got my bank transactions in Excel and that's the only format that I have it that tools available for QuickBooks Enterprise or QuickBooks desktop accountant only if I'm working with any other version QuickBooks Pro QuickBooks premier or QuickBooks Online I have to use a third-party tool like transaction Pro importer or z-axis these tools they cost the the fixed desktop tool cost $200 the monthly tool that's only for QuickBooks Online is like I believe is fifteen or twenty dollars a month that's the only tool that you can use to import transactions we're talking about bank credit card into QuickBooks Online because otherwise the only way to get into QuickBooks Online is with bank feeds which we're going to talk about it would be the bulk of the presentation but I want to make sure it's clear if you're working with QuickBooks mac pro or premier the only way to import information into QuickBooks without having to sorry QuickBooks Online premier will having to buy third party software it would be through bank feeds now there are other tools are the one that I'm going to talk about to QBO convert Pro which is $300 and scan writer that cross paths on what they do is Ken Reiter was sort of the original pioneer in this thing and it could take PDF statements or scan statements I won't import them into QuickBooks using its own set of rules for categorizing transactions the problem with scan writer and I love the software and I own the forever forever it's the price it's just a high upfront cost and then on top of that they charge you a monthly cost on whatever active files you're using so the alternative would be to QBO convert Pro that does a lot of the things that scan right at us which is take a PDF statement take a PDF statement or ace or even a CSV file or a scan bank statement and convert it to an import above format so then you can put it into quickbooks using benefits and not these two tools are technically not comparable and they're not the same tool you know one is designed to directly input data into quickbooks which requires you to select the account category and stuff within the software and then to qvo convert pro only takes statements and converts them to a dot qbo format which is what you import into quickbooks to do bank feeds so i wanna make sure that that's out there and that's clear now if you're not using a third-party if you don't need to use a third-party tool because I got the data in Excel already a CSV file and all my vendors are clean and the accounts are easy to identify then you can just use the batch enter tool which we're not gonna display to the hit but but you can you can search in YouTube I have several videos on this in my channel you can use the batch enter tool and again if you're working with a counting or enter price and the data is in Excel that's free you don't have to pay extra it's actually a great tool to use however a method that at the end turns out to be the best all-in-one method it's because number one it works across both QuickBooks desktop and QuickBooks Online and the functionality is the same which is called bang fields number two it's something that you can convert for your client or an end user if you're let's say for example you're an account and looking converter for them import it and let them do the categorization so you can actually use bank feeds as a way for you to let's say push the data and the user at the end is the one that recognizes it or or accepts it or matches it whatever the situation may be and then there's Bank rules and the bank rules they automate the data entry quite a bit in some cases I can have vendors that change slightly because when the bank brings them in it brings in a bunch of extra data text whatever and it's not the same vendor database I have in QuickBooks all from me to create a new vendor it will create a mess basically so with bank rules I can say look at the vendor name contains this word if it contains the word let's say American Airlines I don't care if it says American Airlines Dallas or New York or whatever as long as it contains those two words make it all one vendor called American Airlines and reclassified to travel now the other nice thing about Bank rules is it interacts really well with your real-time accounting processes so for example if I'm importing my bank data but I want to match it much different than import but I want to match it with something already did like I created the invoice separately and I want to match the deposit within words bank feeds works beautifully for that if I want if I wrote a check two months ago and then it clears this month and I wanted to match instead of duplicating it or recreating it bank feeds works really really well with it and my feet is only for banks and credit cards and for the most part with QuickBooks desktop you still have to press approve or or or or add fred to push into the register quickbooks online is a little bit different but you still have to the user still has to push it so that's the nice thing is it also it doesn't completely overtake there all the processes okay so that's that's it with the intro i want to jump into the demonstration but before I do let me answer some some of the questions that I have here someone says I've been waiting for your approach as an accountant for data entry I would like to understand what's the best way to approach and offer that I entry services okay maybe I'll answer that question as I go somebody says does this software work with the Canadian version you know I think it should I worked with several people that I recommended the software and it should so I email me and now put you in contact with the right people the software I was discussing should work with the Canadian version can you import the chart of accounts in QuickBooks desktops and the answer is yes however it's not it's beyond the scope of this webinar so the center yes you can do that but not gonna cover it some of the credit cards only offer PDF statements they don't offer CSV or QBO files and in some cases quick and downloads how can I import these so you're gonna need the software to convert them Cheryl so you need this software okay so let's do the demonstration so let's open up QuickBooks Online first and I'm on the banking page of QuickBooks Online and basically what I did is I went into Chase and I'll just kind of show you what I did so I went into my Chase Bank account that this is literally what I did this is not sample file a test file so I went into my chase bank account I went into my credit card clicked on see activity and went to somewhere a button that says download download activity and it gave me a couple of choices so he says how do you want download this you want to download it in quick and format you want download it in QuickBooks iif format WebConnect format CSV so Chase is awesome because it gives you tons of different formats and regarding on what bank you're working with I would say and I'm going to just show you a different screen similar to this that also shows me all the formats but I'm saying regardless what bank you have if you have access to downloading the qbo file this is the best approach you can do forget about anything else conversion software forget it best thing you can do is download the qbo file from your bank now the caveat is a lot of banks don't offer it past 90 days 180 days that's a big issue another caveat is some banks just bring in garbage like the data that that's on that spreadsheet or that qbo it's garbage like there's there's vendors are cut off information is missing and you may have to edit it you may have to literally change it and before you bring it back into into QuickBooks so so that's another that's a kind of a challenge and in some banks just offer either the quicken file or the CSV file which in those two cases because you can't import it through bank feeds without it being a QBO file you are gonna need third-party software for that so it's kind of wanted to clarify that and every bank is different so I really you know can show you how it looks like in every bank but when you work with the major banks typically it's not an issue so what I did afterwards is I clicked on file upload I clicked on that button that says file upload I went and looked for my QBO file that I downloaded from the bank so I searched it somewhere in the in the in my computer wherever I saved it hit open and then hit next and they'll kind of go through the motions to the check and double-check you know which account is related to and then you press next and you bring them in so after you bring them in they come into this screen this is called the bank feeds screen and the bank feed screen is designed for it to be sort of a purgatory between the data that came to the bank and what you're gonna actually put into QuickBooks now one of the really important things is that it's confusing to most users is QuickBooks is trying to make data entry easy for the layman and when I say layman I'm talking about let's say the non accountants so what they do is they will they will they will change they will change the name to make it let's say cleaner the problem is that sometimes it can make major mistakes and understanding you know the interpretation of that name so I'm gonna click on the little gear baak button on the top right and I'm gonna click on show bank details just to show you that you can toggle between how the data actually came through the bank so the bank actually says Marathon petrol and pizzahut 0 1 2 3 whatever right but then if I don't have this check box the one called show bank details so if I don't have this check box here what happens is QuickBooks will make it it may potentially change it clean it but in some cases it's wrong so you may want to switch back and forth or only work with bank details in order to just make sure that wrong data doesn't come in so I'm going to start on the payee side and I'm going to click on the first transaction and then I'm just gonna literally put my vendors name in there so one of the fastest way to do this is to literally copy from the memo and and paste into the payee name and then literally a crafter you pay your pace you hit tab you're sorry after you hit paste you click on add Marathon petrol and then you click on save and I'll create the vendor this is typically the most annoying part of the whole process so then QuickBooks suggests a category for you and you gotta be careful again you know if you're not an accountant and you're not thinking about these things you may just causing total destruction now a marathon is a gas station so could that be travel yes could I be automobile expense also yes so you have to so you have to actually do the categorization on the fly if your account is not in your account category in your chart of accounts you have to create it but for the most part you should have it and then you click on add now once you click on add what happens next is really really important because there are some artificial intelligence going on on the QuickBooks side where it's going to try to go calm your entire download and try to figure out what other transactions were similar so I'm gonna click on recognized here in the top nd notice that there was another one with the exact same naming convention and therefore it may therefore it makes total sense that is suggest you to put the same thing on it you still have to click on add and accept it for it to push through now I want to compare this against the process in QuickBooks desktop so and we're starting a little bit slow and we're gonna move into more advanced topics as we go because I know we have a couple of accountants here so far just for the heck of it on the questions box can you put there yes if you have learned something new so far or no if everything else you knew already so just put yes if you learn something new and no if so far you don't know everything and let me know whether I should pick up the pace or not okay so yeah we got you got a got a few we got kind of a mixed crowd here okay all right so let me go into bank feeds and go to a bank feed center and I'm gonna click on transaction list and I'm gonna show you the exact same concept now this screen it's actually not as friendly as the quickbooks online version and it's a little bit clunky however if you have some patience you'll work pretty much similar to it for example there's marathon petrol and what I like about this is QuickBooks desktop does not clean the payees for you so you always get the raw data from the bank now you're only gonna get a certain number of characters and the other side of the characters are going to be coming into this area called show Bank memo so if your bank separates it into two areas the memo and the payee you will see them split in there but it's possible that for example Chase it doesn't split them it puts whatever you can fit into one column and that's it so that's kind of the limitation behind that so in some cases you see data there in some cases you don't so so for this I'm gonna come in here and create my vendor so I'm gonna type Marathon petrol or let's just say Marathon hit tab and then I'm gonna click quick I'd quick ID and then okay I wish there was a shortcut for that but there isn't so you kind of have to click quick on okay create that vendor then when the account shows up I'm gonna put their automobile expenses and then here in the drop-down I'm gonna click on quick add and that's going to ask me do you want me to create a rule that the next time that I've see Marathon expense that I automatically classify it so if I exclude it if I say I've ignore the rule okay if I exclude the rule if I don't create the rule okay next time marathon comes up I have to do the same categorization manually in contrast with QuickBooks Online it didn't ask me whether or not where's my QuickBooks Online it didn't ask me whether or not he wanted the rule it actually did it for me automatically let me do something else let me do something else instead let me find which other one repeats itself I say 7/11 notice I'm gonna go payee I'm gonna type when I copy and paste they pay you from here I save and then I'm gonna put this into automobiles expense and then click on add and it didn't ask me it did not ask me and also it didn't create the rule in QuickBooks Online there's no rules being created automatically but there is this let's call it memory that lingers on lingers in and then any time that it thinks you should do something it will put it in green for you like this letting you know that it recognized that based on past history based on past behavior now this is not a rule which means that I cannot modify it so I'm at the mercy of whatever QuickBooks thought was the right thing to do and that's where some of the trouble can happen let me go ahead and and notice that there's four transactions there so I'm gonna click on add add add them all now let me go into a QuickBooks desktop and kind of contrast it here let me look for a 7-eleven vendor and there are a 7-eleven and notice that there's 470 letters there but to have one name and to have the other name quickbooks online recognized all four of them using its own artificial intelligence as i mention but you're gonna see that unless I specifically modify the rule when I do it with QuickBooks desktop is only gonna recognize the two that are exact they have to be exact to the letter so I'm going to accept the first one it's gonna ask me do if it wants to do the rule and then obviously I'm gonna say okay and then we're gonna notice on the top right side how two transactions were recognized and changed I notice that my 711 was now recognized and it's still asking for my approval now I'm gonna go ahead and click on batch actions and add a proof and that's going to bring them into my register now when I go back into my transactions to review and I'm going to click on download it as one more time the other 7-elevens were not recognized why because in QuickBooks desktop the rules have to be explicit and they have to be exact with QuickBooks Online they're loose and the problem will lose rules is that we really don't know how this behave so we have to be very careful about that behavior now when I go to rules at the top right I can actually come to this rule and I can right click and edit and I can edit the rule and I can tell it look if it contains the word 711 plus those numbers and obviously I don't need those numbers and if it contains just the word 711 ignore the numbers then automatically change the 711 automobile expense and I'll click on save and at that point I can close that and then we can see that the other two got recognized look at the top right where it says that the other took I recognized because we actually explicitly changed that rule so in QuickBooks desktop we get to create rules as well but there's also sort of behind the scene rules or something that I called implicit rules which is the stuff that QuickBooks guesses based on your behavior so you want to be very careful about recognizing the difference between explicit rules and implicit rules so let's talk about creating an explicit rule in quickbooks online so for example i'm going to create an implicit rule for our amazon marketplace right so i'm going to come in here on the top i'm going to click on manage rules and then i'm gonna go to new rule so top right new rule and then I'm going to call this Amazon and I'm gonna say if the BAM text contains the word Amazon and I'm gonna put here marketplace change the category to a category called Amazon Marketplace and I'm creating the vendor on the fly here and put that into office supplies okay and then I'll click on save and I will create my Amazon Bank rules so there's the rules things haven't been categorized automatically they're just the rules and now we're gonna see them they should be under categorized maybe I misspell something on their own the rule itself let me check real quick let me go in back into my rules here your Amazon and it's possible that your marketplace is spelled a little bit different so I'm gonna take out the word a marketplace and it's kind of a good illustration of you know what happens is something's different so maybe a marketplace was shortened shortened or something like that so might change the rule so it just says Amazon and then it should recognize how bunch of transactions let me get out of it and come back in so it refreshes now that's curious let's do it in a different way let's see what if I'm doing something wrong let me try it therefore let me try Walmart so we'll try Walmart I'm gonna copy the actual vendors name here make sure I'm not missing a beat here and I'm gonna go in the top right click on manage rules new rule room name Walmart if the bank text contains Walmart the payee name is Walmart go to add and that's office supplies so I'm being kind of consistent across the board here and then click on save and let me just make sure that yeah it's all set up ok that'll be fine and there you go recognized and there's my Walmart so the difference is with this one you see the word rule in it means that is following your explicit instructions okay it's following your in your your implicitly instructions and it's following a rule that I wrote down different than when it went it more or less finds things on its own let me use another example and I'm going to go back into in QuickBooks here and I'm going to search for one something called Publix so there's a vendor in in Florida for supermarkets called public speed be lax so what I'm gonna do is I'm gonna search through my entire list of transactions so I'm gonna select 300 rows that way I get a little bit more information in there and then I'm gonna hope that Publix is there but I can use any any of them that that repeat so I'm just looking for a few the repeat so we can make it let's do I I Tunes out that works that works just fine so I'm gonna do it I'm gonna do that the rule in a different way I'm gonna type iTunes here so I'm basically just entering the transaction again I am not creating the rule Fred I'm gonna click on add and then click books recognized 35 of them again it doesn't say the word rule because it isn't an explicit rule and it's telling you you should do this okay so that's a really important thing so let's say for example I'm going to take the same transaction and change the category and the minute I changed the category notice that something new happens here letting in no letting me know that maybe I don't know hundred percent sure what you want to do with this so I can actually tell it what do I want this rules to be so remember the first time I created it quickbooks online kind of did whatever you wanted to do the second time around before i'm clicking on add i'm reading this and i'm gonna click on edit this setting and then i can actually override it to say you know what don't create a rule for this if i hit the check box you won't create a rule for it that's pretty cool that's very interesting so when i click on save it won't create a rule for it and notice they censor all back to on categorized you see that so that's all back to Uncategorized it's not trying to do something with it and more it all sends it back to on categorized okay so that's something that we we purposely we told it that we don't want this to you know stay in your memory so basically there's literally a rule that that's that says leave iTunes on categorized now let's say I categorize it this time around I give it a category it'll prompt me again okay what do you want to do with this and then I can click on edit this setting okay that little button that's there and these things are really subtle there so you kinda have to explore and I can click on create custom rule and then what I will do is you will we will prompt QuickBooks to go the custom role which will take me straight into that screen without having me to click in so many places and then on the rule name I'm gonna I can call this iTunes and then have an actually I can actually explicit rule and then click on save and then now it will now recognize them or it should recognize them let me hit refresh here if you recognize them and send them all in there now some of the text in there has to be you know has to be exact as long as all the vendors have the exact text over have to double check if it is it will show up in there and then make a an explicit rule now so that's just heavy contrast with the way the way it works with QuickBooks desktop and the way it works with QuickBooks Online QuickBooks desktop has powerful rules you can create them from scratch I mean up in the case of Google for example I can just click on rules and then I can create a new rule from scratch and call this Pizza Hut and then tell it if it's called if if the word Pizza Hut is on the bank description then we're going to create a new PE called Pizza Hut okay quick bender okay and then add the the subsequent rule which is also put on their meals and entertainment now the problem is this screen is a little bit cookie on the way it works you can resize stuff so you can have to deal with how it is but you can create your own rules explicitly in QuickBooks desktop and then you're gonna see that it will automatically do it for you now I won't tell you you know where there is a rule in place it right explicit it's just because there's just one dimension of rules in QuickBooks desktop so that's in a nutshell the limits of QuickBooks desktop and the contrast with how it works with with QuickBooks Online and I personally like the way it works with QuickBooks Online better just because and let me go back to their rules here real quick just so we can I'm going to get rid of my on categorized iTunes one and I'm gonna leave just my one iTunes I had in there and and go back to banking so it should recognize them now so that's the the difference between the power of the desktop and online with the bank rules with online you will suggest some other stuff for you and on top of that I'll give you the ability to stop it and the ability to create an explicit rule and edit it but the best part in my opinion is being able to export my rules that import them into another QuickBooks file so you if you're working with one one client or one QuickBooks file that has really really good rules list it's really sophisticated it pretty much covers 90% of the work you can export the rules and kind of walk you through the process I'll create a special excel file and then you open up the other QuickBooks file and you click on import rules and basically you select the same file you export it from that one QuickBooks file bring it in here it's gonna ask you to double check the mapping just because not always the for obvious reasons the chart of accounts on one QuickBooks file may not sorry each other accounts and the vendor list may not be the same so once you import the rules it's gonna ask you hey can we map the vendor so they aligned can we map the categories are they aligned and you go to that process and then click import and then basically your new quickbooks online file will have the rules from the other one so I do rest suggest that you export the vendor list export the chart of accounts from your old file that contains all the good data imported into the new one before you import the rules so that's kind of the process you they wanna do that first okay all right so that's kind of like the bank feeds power in a nutshell and I know I'm I must be missing something important but that's as much as I wanted to cover with that so there's also a way to import a CSV file straight to quickbooks online not available in QuickBooks desktop but you can go to the file upload and you can go to browse and you can select a CSV file so I'm gonna grab a CSV file and let's say that I downloaded an Excel or CSV file I'm looking for the one that I have here where did I put it think it's at my desktop somewhere there it is so I got a CSV file and it's assumed that this CSV file is from my bank now there's a problem with importing CSV files and this is as of today so it's January 18 2017 as of today the CSV functionality does not allow let me create a new bank account here does not allow for importing memo okay and it also does not allow for importing check numbers of reference number so the problem is that I can only work with a date field a description field and an amount so if I wanted to bring in my check number or a memo two really important powerful things from an import I can't do it so yes I can import a CSV file and it will work great for a credit card because you got three lines basically you got date amount and payee or bank description but it won't work for reference number which is useful for check numbers and a memo if you want to bring that in so I said now QuickBooks Online does have limited CSV import capabilities into bank feeds but it's not as complete so let's talk about the cases in which the only available options I had from the bank was a CSV file or a PDF file and I want to use my full functionality for bank feeds to import my bank transaction so then you pull up the CSV file real quick where did I put it there it is so let's say this is what I downloaded from the back okay so as I mentioned if you're working with QuickBooks desktop you're not gonna be able to import this data through bank feeds you're gonna be able to convert it you're gonna have to convert it into a dot QBO file but one of the th ngs that I recommend if you download a if you download a spreadsheet like this from the bank because you couldn't download a qbo file you may want to clean it up first right because that you take advantage and you have the raw Excel data clean it up before you bring it into QuickBooks because if you clean it up then you can really speed up your process so anyway let's pretend that this is bank information this is a information from the back so I'm gonna go ahead and start cleaning this up here and only have one transaction date one description one amount I'm gonna come in and create a column from memo and then I'm gonna copy and paste my original bank information into a memo field or I think concatenated in the in the case where I already have two and the reason for that is because QuickBooks Online will will maintain the original text information but QuickBooks desktop will not so the closest thing you have to maintaining your your your original Bank memo in QuickBooks desktop is to duplicate that one column and then once we did that part we're going to go ahead and through you know certain you know several let's say Excel particular excel activities we're gonna clean these up manually in Excel because we can do them quite click quite quickly and it would be much faster than establishing bank feet so I go down the line and I look for some of the vendors that have duplicates but maybe possibly have different names is a good example right bank makes in all this extra information so I'm going to change it to American to say American era of American Airlines and then drag it down and then for example these these are all the same exact vendor I can keep scrolling down and then seeing instances in which the vendors are the same but there's only a few numbers that change and what those opportunities come in balance and oboes great example when this opportunity has come in I'm going to click and drag it down to to do those and therefore we're entering let's say 12 months worth of information a year's worth of information typically this process goes pretty smooth because you a lot of people tend to use the same vendor over and over and over or the same type of vendor over and over and the data duplicates like this so once once I'm finished doing this entire cleanup process as I mentioned that is what I suggest first if you can download the data in Excel once I do this entire cleanup process then what I do is I copy that that that vendor list I hit copy and if I'm working with QuickBooks desktop I'm gonna go to this menu I added multiple list entries which I mentioned at the beginning of the webinar I'm gonna click on vendors and then I'm gonna paste them in there and the reason for that is because I don't want to sit there and create vendors every single time so then I click Save Changes and again caveat you clean them up in Excel first don't create thousands of vendors and your QuickBooks file and create this disaster for no reason clean them up in Excel first and you may need to resort to several quick Excel skills for this and have a webinar on Excel in a couple of weeks so you can join that if you want to talk about strictly about cleaning up data in in in Excel nothing to do with QuickBooks is just an Excel webinar that I have I'll post a link in the in the chat below so I bring in the the the just the vendor names again to prevent to prevent my we said to prevent the having to create all these vendors on the fly and then what what's the value of doing that what the value is as I'm doing my data entry and as I mentioned to you you know the most annoying part is kind of cleaning the vendor on the fly as I'm doing my data entry after import my vendor list I should just be able to type for example a Google in there and it will pull up from my it's thinking for some reason but it should be able to do it from there and pull it up and and I don't have to create it on the fly let me just show you what that process looks like in quickbooks online real quick so switch over to quickbooks online so the way you would do that before is you will go to the year amet menu click on import data and then you will click on vendors select your spreadsheet and then you would select the column that contains the vendor name which in this case it would be payee or something like that whatever it is on whatever it is called in my excel file so this is called let's see this is called description so let me save this finish saving it so I can finish importing it into quickbooks online okay you know import it again now that it's updated and again i'm just importing i don't know why this is acting up but i'm just importing my my list of vendors i would basically select my my vendor name in there and then click on next and then my entire vendor list should show up I don't know why it's acting up maybe I'm gonna change this real quick to the word name I think I'll make it a little bit easier so let me open up my my spreadsheet and I put I'm gonna change the name of the column out what my computer's acting up today apologize for that so I'm gonna put here a name okay I'm gonna save that and that that should allow me to import it quite easily into QuickBooks Online but again at the point is that I don't have to type and create all those vendors on the fly so let's that's kind of what the process is that's just a quick tip you may want to change the title for it to try to read it correctly so after import my vendor list because I did have the data in Excel now I have to create all these vendors every time I I do it now so let's talk about a circumstance a completely different circumstance in which I don't have the data in Excel and I don't have the data in in the qbo format I'm gonna run a special software called to QBO convert Pro and I'm waiting for my computer to kind of react I don't know what's wrong with it give me one second okay there it is okay so there's my software is called to qbo convert pro and what this software will allow me to do is it can read a PDF bank statement or it can even read a CSV a bank statement that I edited so in some cases I want to clean my bank statement edit it and then re-imported it you know cleaned up in QuickBooks Online so I run this software and then I click on the Settings button and I tell it what am I trying to compare so I can say look I'm trying to convert a credit card statement and there on the lookup it says well what bank is it you select the bank and there's a whole bunch of banks in here now if your bank is not listed here it doesn't matter you can pick any back that's actually completely irrelevant honestly but you want to pick a bank that you have to in order for it not to be confusing and then you put the account number that's important to put the account number because if you import multiple bank statements you want to leave QuickBooks to recognize which one which one it was looking at okay so then basically I go to this initial setup and it's got a couple of interesting things like if I'm using your format you know do I want it to read your format dates are the charges positive and the publicist negative that sort of thing that way you don't have to change it on the spreadsheet you can actually if you import a qif file which is the only option that that PayPal gives you for kind of QuickBooks you can separate the the paypal fee from the original transaction using this tool which is actually really really good and that's a couple of little things here and there but we don't need to get too deeply and then I'm gonna hit okay and then I'm gonna click on convert so when I click on convert I'm gonna go search in my desktop for my bank statement so I'm gonna have bank statements of different formats and and I'm gonna before I convert it let me just open it real quick just to show you what I have so I got bank statement number one and this is my credit card statement that I was able to download digitally from the bank and sometimes the banks provide these statements and don't provide the digital downloads sometimes they provide the statements for two years worth but only do digital downloads for six months worth so you may want to convert it just for the first six months that sort of thing sometimes the person close the bank account and all they have is this and there's just no physical way to bring to bring that into quickbooks digitally so i have to use a bank statement or maybe the bank doesn't even support online banking on exports and who knows but either way if for whatever reason the only thing you have is a bank statement and it's something that was digitally downloaded the way you know is because you're selecting you can select the text easily right so if you actually move your mouse around and you see that it can recognize letter by letter that means it's digital that means it wasn't scan so this actually the conversion process is super smooth so i literally click on convert pick my statement and then i click on preview and I always like to click on preview just to kind of understand what is it that the software is doing before and doing the conversion and the software like black literally like black magic it took that statement in PDF you get rid of everything you didn't need and organized that into a spreadsheet format for you now why do you want in a spreadsheet format two reasons one I can verify I can look at it I can make changes to it if I want to adjust some of these information on the fly I can do it all before I import it into QuickBooks or I can export it into a CSV and it's exactly as I mentioned manipulate it change it clean it in QuickBooks and then bring it back and convert it into a cube yo format or I can just go straight into a qbo format and then I can click here where it says create qbo now one really important tip when you bring this information in and you're working with QuickBooks desktop you want the original payee name to copy over to the memo I mean the quickbooks online does this really well but QuickBooks desktop does not so the only way to get QuickBooks QuickBooks desktop to have the original name on the aside after I do bank feeds it's and this is a little feature that we added or that I convinced Ralph from two QBO to add it which is you basically select payee plus memo here and then you click here and you put memo add-in and this combination pay you from memo payin memo and memo I am will concatenate it so I'll bring in payment thank you and quick bookkeeping training whatever that is you'll bring it all combined into the memo field combined and that way if I if I if I lose my original payee name is still in the memo which is extremely powerful and it's a solution that worked really well with QuickBooks Online but with QuickBooks desktop this is the only way around it so then at the top right I just want to make sure that my beginning balances match my ending balances match so it this is a pre reconciliation has nothing to do with reconciling inside QuickBooks this is a pre reconciliation just to make sure that the software is not missing anything now the single most powerful feature which I love I mean it it's just it's just awesome it's called the payee cleanup settings it's actually one of the newest things that they added in the past I would say by past six months or seven months and and if you already bought the software six seven months ago you can just download the update and you will have this update so anyway so what's really nice about this is there's a couple of things here for example if I want to get rid of the phone number you see right here with says WebEx and there's a phone number if I click on remove phone numbers but I want you to focus on WebEx and the phone number focus on that and I'm gonna click on this check box you're gonna see that phone number goes away so that cleans up the process a little bit more for me then it says remove two letter state abbreviation so it will recognize if there are two letters both in caps and they're next to each other it will recognize it and remove that and there's a couple of things here like for example remove everything after a special character like if I wanted to remove a plus sign a star or quote the parentheses you can play with all these these are definitely worth playing with and then there's there's wild card so you can play with your cleanup process and you can pretty much figure out based on each bank which is going to be the best way to go about it some credit cards add the word USD next to it or something like that or some mechanics will add the currency that the international currency value on it so if you want to just get rid of information that's there you can use the pay clean up settings and clean it up preemptively before bringing it to QuickBooks on books desktop or QuickBooks Online so then I go to create QBO file and then it'll create a dot QBO file on the same folder with my PDF file is and it should be here somewhere there it is there's my qbo file and just for the sake of exercise let me show you what a QBO file looks inside of a inside of Excel just so you can kind of see widely a QBO file it's something that you really can't create on your own you really can't create on your own go from Excel to QBO it's because it uses a special format I'm just going to open it in the spreadsheet here let me go to a file oh there it is okay so this is what the qbo file looks on the inside sees a bunch of bunch of stuff right that's something you're not gonna be able to manually recreate you need converter software it to do that so that's why we use converter software again to go from CSV to qbo or Excel to qbo or PDF to cubillas or all those things and again qbo is the format not necessarily quickbooks online so then i can import this information so i'm going to go open up QuickBooks Online and I can go to file upload top right and I can select my file so I'm gonna select my new qbo file that I created and then I'm gonna click on OK or open I'm gonna click on next and that's gonna bring that in so I'm gonna bring that in I'm gonna bring it's another accountant so I'm gonna just create a second account here I'm gonna call it assume it's an Amex I'm just call this that makes for a second and then good I save and close and then I'm gonna hit next and then it's going to bring all that information there and uploaded through bank feeds so that I can use all the features that I discussed through Bank feet so that's why we spent a long time discussing Bank feeds at first so you understand the value on bringing that information through bank fees and not in any other way because I know some people like to import using batch enter or they like using third party to and importing tools like live transaction from importer or something like that so so I just trying to sell you on the value of using Bank feeds and if you have an excel file a CSV file whatever you can convert it to QBO using that one tool so as you go to the process you click lets go you go back into bank feeds and you're gonna see that same information we were looking at just now but we're gonna see it in this case we're gonna see it cleaned up but in the case of you know maybe something we haven't worked with before then all the bank rules apply accordingly if the bank rules are set to be universal and that's sort of thing and is the same process for a QuickBooks desktop or make sure we don't forget about my beloved QuickBooks desktop so if I go to open up QuickBooks desktop which I think it just literally froze on me so I'm gonna open up another session here but if I go to QuickBooks desktop and an import if I import the qbo file it works just the same one area where people get a little bit confused about they're like oh you know these qbo files only working quickbooks online in this no it has nothing to do with quickbooks online you can import manual qbo files into quickbooks that stuff just because it's called qbo it just causes some confusion so again you can use qbo files to import and desktop or online and I think 0 to I don't want to get too much into it because I don't use that other accounting software but but quick process if it was online they can both take advantage of this tool regardless on on the context that you are working in hmm let me see all right so last five inutes QA I like to answer all the questions that you have so I'm going to go through it and please so it says Oh show the upload through desktop okay I thought I did that the file menu go to utilities import web connect and you and you select it let's see what else we got um can you if you have a excel file with a petty cash transactions could I upload my petty cash activity into QuickBooks Online or QuickBooks desktop the answer is yes of course if you have an excel file and you got date amount payee you can just convert it to a QBO file full QuickBooks into thinking it's doing bank feeds and you can literally import huge excel files that contain petty cash transactions into QuickBooks desktop or online and use the bank fees functionality to import those what's the website again okay that's www.www.pinesicearena.com that I had some Chinese characters and remembering characters whatever it is and I had to literally I had to send it to them and then they worked it out and their work made it work for me but I don't know if this is going to be sort of a sustainable things I would say if US and Canada should work flawless anything else you know I would say it would probably have caveats associated with it why would you use this tool instead of importing directly to with using a transaction Pro importer or a third-party importing tool if you already have the data in Excel so the answer is with bank feeds I can use Bank rules and I can classify things using those tools and with in any importing software I have to specify the account name so it doesn't speed up the process and remember is the renaming rules what what makes it what makes it differently different and significant somebody says I have the CSV to qbo tool and I'd like to upgrade to the PDF tool can I just pay the price difference and do that so the answer is yes but you're gonna have to contact them definitely have to contact them because I really don't know what the process is to upgrade but if you only have this the Excel tool you want the statement tools you have to upgrade somebody says how does it convert scan bank statements oh yeah it uses a it uses something called OCR which is optical character recognition so it basically reads the statement that uses that technology to try to figure out what what is what and if it's digital PDF is gonna be a thousand times better you know if it's if it's can you know it's gonna be subject to all sorts of glitches especially if your papers marked you know if you got markings or lines or creases all that stuff plays into it all right so for the last thing just type in the questions box if you learn something new just but yes if you didn't let me know that it's I don't know that at least some of you knew everything that I mentioned and again strongly recommend that you purchase the software go to www.donateyouraccount.com/davidpakmanshow and I can't personally guarantee because I don't work with this company but I'm telling you you you won't have any issues if you buy a test it doesn't work contact support they'll help help you otherwise you can get returned you can get a hundred percent money-back guarantee anyway so we're gonna set this up this book I set up this webinar as a series and will run at each month and each month will cover some of the same concepts well then I'll try to add some new things each month and and you will get invitations for that automatically and if new features get added will discuss more deeper new features that's it for me and if you don't have any more questions if you want to contact me directly people always ask for my email it's Hector at Garcia CPA com if you want to contact me directly for whatever reason otherwise have a tax season have a nice tax season have a nice day and hopefully you learn something you have a good