Import cc number field
so I would say the most common question I get from my clients and colleagues fellow accountants and QuickBooks consultants is how do I get my bank transactions imported into QuickBooks so how do I get the transactions are in my bank and put them into QuickBooks and this logical right nobody really loves to do manual data entry so I want to show you step by step what are the different options out there on how to get the information into QuickBooks however what matters is how the data is formatted currently for example some banks allow you to download a dot QBO file often called webconnect sometimes it's called an OE FX file so if your bank and most major banks have this if your bank gives you the ability from the bank's website to download a dot QBO so period QBO extension file that is the single best way to download the transactions because once you have a dot QBO file you can import it into QuickBooks desktop into your bank feeds or you can manually import it into your QuickBooks Online into your online banking and I'm going to show you examples of that later on now some banks do not have the ability to download the dot QBO file alternatively you can download maybe the bank statement in a PDF format so if you can download that that's great there's gonna be a tool that I use for that and I'll show you kind of how I do the PDF to qbo conversion sometimes the bank will allow you to download the transactions in a CSV format CSV means comma separated values which in a nutshell is how you can download the data in txt open it in Excel you can actually make changes to it and then you can use a tool which I'm going to show you is basically the same tool where I can go from CS we into dot qvo now sometimes the bank allows you to download into dot qif format which is the legacy quicken format and if your bank allows you to download quicken then great because the same tool that I used to convert everything to dark you can also convert the qif file so in a nutshell best thing and obviously for free if you can download the dot QBO file from the bank if you can't get a dot QBO file then Excel CSV qif and PDF all those things can be converted into dot QBO which towards the end of the video I'll basically show you an example of actually putting that stuff into QuickBooks so just for example I'm in chase in the bank account and I'm gonna show you what it looks like in Chase now unfortunately I can't really show you an example for every single bank in the country and every bank is going to be different the website will look different the interface would look different however at the point where you get to choose the type of file that you're going to download that's what you you have that choice and you'll see what options your bank gives you so I'm gonna go into the bank account itself right so it just usually use click on the bank account itself and somewhere in that screen you should see a button that says download or download transactions or online banking or maybe something like that is typically gonna say something like download or download activity in the case of Chase for example it actually says download account activity so I'm going to click on that and then most like for example in the chase a screen it gives you a choice to do download or do direct axis so I'm not gonna talk about the REC connect or direct axis just because if you already have direct access to the download then you're not watching this video obviously but this is for people that don't have that direct connection so they want to do the manual downloading now in the case of Chase for example Chase it's limited to two years so I think Bank of America it's a year if I'm not mistaken and Wells Fargo maybe one or two years maybe 18 months so every bank has a different policy so in this case I'm gonna choose the bank account itself and then here this is the key here select download type so I'm gonna zoom in here just gonna show you what I mean so in the case of Chase it's great Chase has the quicken web connect file it has the quicken qif file it has a QuickBooks AF file by the way I never really use a AF files a complete disaster then there's our QuickBooks web connect QBO that's the first choice that I was talking about then Microsoft money I don't never used one and then CSV so actually chase is a great bank because it gives you all the different choices so this would QuickBooks web connect QBO that's typically the preferred choice if you have that then you're home free but if you don't have that then this is what this video is about is talking about the alternatives so I'm gonna choose for example QuickBooks web connect and then I'm gonna choose my date range so like I said I can go back two years so I'll go back here two years and then select my data range and I kind of just give you an idea what this looks like on the inside when you actually download it because it oh it's nice to see so there's yep so there's a limit of 24 months so in this case let me go ahead and pick a date that would fall into that 24 month range and click on download activity this typically takes 30 seconds to a minute for that whole download to come through so whether you're a PC or Mac it doesn't really matter it usually downloads with the bank's name and then that dot qbo that's the file extension that dot Q below and I'm gonna open that in Microsoft Excel just to kind of give you an idea what that looks like is what the file looks like when you open it in Excel and honestly it doesn't really matter what it looks like in Excel because you're not really gonna be manipulating this this looks like script or code but this is the actual transactions one by one that actually come in through the bank in QuickBooks understands this file and it's able to convert that to to a workable format in which you don't really have to do data entry now if you don't have access to this dot QBO file you how to convert this dart QBO file from different formats however let me first show you real quick how the dot qbo files work really well inside of QuickBooks so I'm here in QuickBooks Online so I'm gonna show your quickbooks online first up here on the top right it says file upload and we're gonna click on that and we'll click on that button there and then it tells you select file to upload again this works exactly the same way whether you're in a PC or a Mac I happen to be on a Mac but it's gonna be the exact same process for quickbooks online so I picked the file I hit next I select here the bank account and I want to connect so in this case I want to connect my check my chase checking account and then I click on next after a few seconds to a minute you will get this screen that says it's been successfully uploaded and now in the case of this one for example you can see clearly fifteen hundred transactions were uploaded into QuickBooks now they're not straight into the register you still gonna have to categorize these however you know as you categorize these transactions it does get easier and easier like for example here the information says Intuit payment solutions so in this case I would pick a vendor or create the vendor so in this case I'm creating the vendor I guess I've never used that before it's actually a brand new a QuickBooks file so i'm creating a vendor called Intuit payment solutions and I'm gonna put it here on their bank service charges right I've been well that should really focus too much on how accurate that particular categorization is but what I'm trying to show you here is it we pick it once we pick the the less you say service very Bank service charges or spelling correctly would be helpful and then I click on add one of the real cool things about online banking for example there was 86 transactions exactly like this one so when i click on recognized so I click on recognized there so you're gonna see 86 transactions are similar and every single time this vendor was used it gets categorized to that category that I chose and that goes into the register like that so you can actually batch at six transactions extremely quickly so online banking it's not direct input but it does create the workflow for you to do direct input so in QuickBooks desktop or QuickBooks for Windows the process is very similar so let me go into the chart of accounts real quick and make sure a bank account is created there it is there's a bank account so I'm gonna be uploading the transactions into that notice that it has a serial transactions for the time being so I'm gonna click on the file menu utilities import web connect files alright so that was the process I went to file and went down to utilities import and then web connect files so I click on that and the system is gonna ask me what file would you like to import and there it is there's the file I downloaded from Chase so I click on open and the systems are asking me well which account would you like to link it so I'm gonna hit the use existing account hit the drop-down and select this one here called Bank click on continue so it tells me your web connect data has been successfully uploaded I'll click OK now this is the bank feed screen and if you're working with QuickBooks 2013 2014 2015 or 2016 so 2013 or above it will look like this if it's older than 2013 the screen looks different however the process is extremely similar so I'm going to click here on transaction list and there are all my transactions so I can do the same thing I did with here that I did with the other one so I can in this one for example I can call it here into it merchant solutions whatever I want to call it right so I get to create the vendor and I'm gonna put that on to bank service charges and then I'm gonna click on click Add and then what will happen is QuickBooks will start reading similar transactions so if you see up here on the top it says 100 transactions are similar when I click on that you get to see how transactions that have recognized is something that has been used before get automatically categorized now I don't have to accept them but I can I can correct these like if these are not supposed to they're not linked correctly then I can make the changes and click push will start learning as I go but that is the concept of online banking by manually uploading a dot QBO file so let's move on to PDF bank statements so first before we go into the conversion of PDF bank statements into the start QBO format and then can be downloaded into QuickBooks using the bank feeds in QuickBooks desktop or the bank downloads in QuickBooks Online as I just showed you it's important to know whether your PDF bank statement is an original original PDF you downloaded from the bank or if it's a bank statement that was on a paper printed paper that you scanned and therefore you turned into PDF so here's a PDF bank statement I have of a credit card it's it's actually the same thing it could be credit or it could be back they actually both work and the way you know that a PDF is the original version from the bank is because every single field can be selected or highlighted like in this case I can select this text and if you see the text that you can select it or perfectly highlight it this means this is already in digital format and these are the original PDFs from the bank so if you're working with the original PDFs from the bank you can use a tool called PDF to qbo and I'm gonna put the links there below in the video or you can use a tool called dot qbo convert pro which is pretty much the same thing as PDF to qbo but it has a couple of more settings built in so here's this is the tool that I'm talking about and whether you use PDF to qbo or tube qbo convert Pro either one tool it's actually gonna have the exact same behavior so the first thing is I click here on settings and I have to tell it whether this is a bank or a credit card because QuickBooks is going to sorry this software it's gonna convert this to a dot QBO format that QuickBooks is going to read and QuickBooks has to know what type it is I'm gonna hassle that credit card then I'm gonna click here where it says look up so I can select the bank so I'm gonna search the chase and if your bank is not there it actually doesn't matter which bank you choose however if a consistency is good to just choose the correct one if it happens to be there on the list so I'll hit OK and it's important to put the bank account number or at least the last four digits it could be an identifier especially if you're converting multiple files so I'm gonna go ahead and click OK and then I'm gonna click on convert ok then it's gonna ask me well where's the file you want to convert so my happens to be on the desktop so I'll go ahead and pick up I'll go ahead and pick up that PDF file and then I can either just go straight to a conversion which I convert to CSV which is which we'll talk about a little bit later on as I talk about CSV to QBO conversion I can go straight to convert to cuba and allow the software kind of do this magic but you can go to preview this is actually my favorite thing to do because when i click on preview i'm gonna get a grid and what this grid shows me it's a it's a quick preview of the transactions that it read um so i can actually just double check that the software is doing what it's supposed to be doing I can check the dates and see if the dates are correct I can see how it read the payee information I can see if I read the amount information and then down here I get to make sure that I map it so for whatever reason it read it correctly but it didn't map it then I get the chance to map it now when when you have digital PDFs like for the example that I showed you this software is pretty much flawless because everything is in digital format when you have to read transit I'm sorry when you have to read statements that were scanned this could get a little bit more random so it requires you to tweak the settings a little bit more now something I really like up here it tells me what your total credits are what your total debits are and allows you to for example double click your bank statement real quick and you can actually verify they're in the summary if the total payments and the total charges were accurate so sort of a quick automatic reconciling just to make sure the data is to be complete and I'm gonna go ahead and click on create' QBO and then the software will create this dot QBO file now as I showed you before there you can go to file utilities import web connect and I'm gonna go ahead and choose that file that I just converted and hit open then it's gonna ask me which credit card would you like to use because in this particular example we use the credit card so I'm going to call a credit card here visa I'll call it Chase visa and you're gonna see the import scheme it works exactly the same way as the bank so this software actually works for importing both bank statements and credit card statements so I click on transaction list you just kind of show you and there it is there's the date the the name of the downloaded name and the amount so what's great about this is all I really have to do is put the vendors name right and then if it's already on my database then I just select it and select the account and then click here on quick add and it will add it for me and askew editor more and more QuickBooks will learn this is why this is my favorite way of importing bank transactions into QuickBooks now on to bank statements or credit card statements that were scanned so let me show you this is basically the same bank statement that I thought I went ahead and I printed and scanned and you're gonna see the behavior is a little bit different so let me go ahead and open it and was typically different about this is I click and click and click and I try to select these numbers and it doesn't allow me to select it so when you actually scan the statement and obviously there's imperfections there could be creases there could be markings that can affect this whole thing but if I try to select the text like I'm trying to them clicking and I'm trying to do it now that's really when you know whoops that's really when you know that this is not the original PDF so what happens with this exact same tool with PDF to qbo or to qbo convert pro there is a version called the PDF last version that can actually read scan statements so let me go through the same process here I'm gonna go ahead and just change the account number so when I import it into QuickBooks it doesn't import into the same account and I'm gonna click on convert and I'm gonna go ahead and select that statement and click on preview and you see it will take a little bit longer it goes through the process because it's actually reading the image and using OCR optical character recognition to to detect the the names and the amounts and the dates and all that good stuff now when it finishes reading and it will take longer if you're using a scan statement it's not a perfect science yet you know I would much prefer use a digital statement to a book to avoid these potential issues here's the columns of how it detected the information and you can actually see here payee and this is where the mapping matters right this is what I tell it well that's that's paid and this is the date so this is where I tell it this is the date and then we see the amount and then the memo so it looks very similar and as I scroll down I get to see all the transactions and for example in this particular case it didn't it didn't detect any of the payment so for whatever reason it actually took in all the expenses but not the payments and you can see up here on the outer reconciliation feature you can see look there yourself there's credits but but no debits and again it's not a perfect science if you need to add them manually you can click here where it says add transaction and you can scroll all the way down actually all the way whatever you clicked and then you can actually manually add the one payment let's say that happens to be a $5,000 payments whatever whatever it is and we'll put here payment and so if you whatever reason it missed one or two you can manually put it in there again it's much better if you have the digital a statement but if it has the PDF akin to a lot of the work for you and at that point I would go to create qbo and import it into QuickBooks the same way I did it with the other ones now what if I don't have the dot qbo file and I don't have the bank statements in PDF but I do have an excel file or CSV file so the most important thing is make sure that the spreadsheet has at the very minimum at the very minimum they should have these three columns which are date payee or description it doesn't matter it's the same sort of concept and amount we're negatives are the charges or checks and positives are the deposits or payments in a credit card now the optional columns you can have our memo transaction number and have a debit and credit column instead of a single amount column let me fix that there so those are the optional columns but date payee and amount is extremely important so this is for example a spreadsheet that I have okay and then whether this spreadsheet was downloaded from the bank or it was created manually notice that all I really need is three columns date payee an amount I'll go ahead and delete that to show you so as long as I have these three columns here the software can do is its bidding now the nice thing about having it on excel for for a couple reasons nice thing about having an excel is if you got checks and you wanna handwrite all the payees in there it's a great tool to use before you import it into QuickBooks that way you don't have to retype everything in QuickBooks also this is great for petty cash logs you know so that's not really a bank but if you have a lot of petty cash logs in in Excel you can convert them into dot qbo which is gonna be great for you to be able to import that into QuickBooks so I'm gonna go ahead and save save this a CSV file and show you how that works in this converter software so for CSV specifically you can use a tool called CSV to qbo but again I use the two qbo convert which is the accountant tool that has all the file formats so I'm gonna go ahead and click on settings and I want to tell it well this is a bank account or credit card whatever it happens to be I'm gonna put the account number in this case I just put the last four digits and then I hit okay then I'm gonna click on convert and I'm gonna select that CSV file a half there I'll click on preview just to double-check that the day that's being read correctly and there it is there's my date column there's my payee column there's my amount column and then down here you see that the mapping is correct and I click on create QBO and now I have my QBO file which I can import to QuickBooks desktop or QuickBooks Online so what are your options so let's talk about scenario number one I showed that at the very beginning of this video if your bank allows you to download the dot QBO files then you're home free ok this video wasn't focused on how to use online banking or bank feeds there's we have other videos in our channel that focuses on you know what best tips and tricks on what to do there but if you can download the dog QPR file then it's great you have you don't pay anything for it it's free it can import into QuickBooks Online or QuickBooks desktop however if you don't have access to the about QBO files this is option number two you have to digital PDF statements then you need either PDF to qbo or to qbo convert pro those are the two options there third option is let's say I don't have a digital PDF but I have a scanned PDF so I have a scan paper bank statement then I need to get PDF to qbo with the plus or to qbo convert with the Plus that Plus basically means that it can actually read scanned PDF statements and finally the fourth situation here let's say I have the data in excel or a CSV file then all I need is CSV to qbo now I'm in the website here for the vendor of the manufacturer of this of this software that I'm recommending and I use it all the time for my practice and that's why I made this video because I use it I love it I worked hand in hand with a developer to make it better you know all the time I'm suggesting new features people email me and say Hector I would love that to QBO convert broke and do this so I could do that so I usually talk to the developer and we talk to it we tested and this is incredibly time-saver especially when my my client doesn't have access to the bank it just has PDF bank statements that this allows me to really really quickly go through it anyway let me show you the website so here on the right side there's all the software programs there's two QBO convert pro there's PDF two QBO convert and then down here this CSV to QBO now what are the main questions that people ask me is Hector what's the difference between the two QBO convert pro and PDF to QBO so the difference is that PDF to QBO is a single utility it takes your PDF bank statements cumbersome to QBO done whether there are scanned or digital it just means that you have to get that plus version that cost a little bit more but what to QBO convert pro it combines multiple tools so so that's what I call the accountant tool the person that works with multiple file types so this one can convert CSV files as I mentioned earlier you can do qif files these are the quicken versions of some banks can only download into quicken it can do the PDF of course and they give you some really cool things like it can actually take a regular qbo file and convert it into CSV so you can make some edits and then go from CSV to qbo and then you can actually manipulate the type of data that goes in there now in terms of pricing I prefer not to discuss it in this video because I've seen the price change over time so but you know ranging from under $100 for the CSV to qbo all the way to two or three hundred dollars for the for the highest end tool if you're working with multiple clients or multiple bank statements and you want to save yourself hours of manual data entry then I strongly recommend this product