Importing Bank Statements into QuickBooks with QBO, QIF, CSV, and/or PDF Bank Statements

Purchase 2qbo Convert Pro Plus for $299 at http://www.2qbo.com (covers all file types)

You can also look into the single functionality tools like CSV2QBO or PDF2QBO; which are less expensive

If you have the transactions in a PDF Bank Statement, or CSV / Excel file, .QBO, .QIF, or OFX files, then this comprehensive guide will show you all the options built into QuickBooks (all versions: Pro, Permier, Accountant, Enterprise, Mac and/or Online) and a 3rd party tool you can purchase to accommodate different file types. Manual Upload of qbo webconnect files converted from .pdf or .csv

---

Closed Caption:

so I would say the most common question
I get from my clients and colleagues
fellow accountants and quickbooks
consultancies 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 arm 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 . qbo file
often called webconnect sometimes it's
called an 0 if X 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 . qbo so . qbo extension file
that is the single best way to download
the transactions because once you have a
. qbo file you can import it into
QuickBooks desktop into your bank feeds
or you can manually import it into your
quickbooks online in to 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 . qbo file
alternatively you can download maybe the
bank statement in a PDF format so you
can download that that's great
there's going to be a a tool that I use
for that i'm not sure you can
how I do the PDF to qbo conversion
sometimes the bank will allow you to
download the transactions in a CSV
format the CSV means , separated values
which in a nutshell is how you can
download that the data in text open it
in Excel you can actually make changes
to it and then you can use a tool which
are going to show you is basically the
same tool where I can go from csv into .
qbo now sometimes the bank allows you to
download into . 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 use
to convert everything to . qbo can also
convert the qif file
so in a nutshell best thing and
obviously for free if you can download
the . q your file from the back
if you can't get a . qbo file then excel
csv qif and PDF all those things can be
converted into . qbo which towards the
end of the video are basically show you
an example of actually putting that
stuff into quickbooks so just for
example I mean chase in the bank account
and i'm going to show you what it looks
like and chase now fortunately 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 there for the
interface will 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'll you have that choice
and you'll see what options your bank
gives you
so I'm going to go into the bank account
itself right so it is usually used click
on the bank account itself and somewhere
in the screen you should see a button
that says download or download
transactions or online banking or maybe
something like that is typically going
to 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 the most like for
example in the chase screen it gives you
a choice to do
download or do direct axis so i am not
going to talk about the right connector
direct axis
I'm 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 that man
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 or every bank
has a different policy
so in this case I'm going to choose the
bank account itself and then here this
is the key here
select download types i'm gonna assume
in here just gonna show you what I mean
so in the case of Chase is great Chase
has the quick and webconnect file it has
the quick and qif file it has a
quickbooks is filed by the way it never
really use is files are completely
disaster
then there's our quickbooks will connect
QB oh that's the first choice that I was
talking about the microsoft money and
they're not never use one and then csv
so actually chases a great bank because
it gives you all the different choices
so this with quickbooks webconnect QB oh
that's typically the preferred choice if
you have that then you're home free
but if you don't have that then this
what this video is about us talking
about about the alternatives
so i'm going to choose for example
quickbooks webconnect and then i'm going
to choose my date range so like i said i
can go back two years so i'll go back
here are two years and then select my
data range and I kind of just give you
an idea of what this looks like on the
inside when you actually downloading
this is well it's nice to see so there
is yet 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
months range and click on download
activity is typically take 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
you usually downloads with the bank's
name and then that . QB oh that's the
file extension that . qbo
and i'm going to open that in Microsoft
Excel just going to give you an idea
what that looks like this is where the
file looks like when you open it in
Excel and honestly it doesn't really
matter what it looks like an Excel
because you're not really going to 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 and quickbooks
understands this file and it's able to
convert that to a to a workable format
in which you don't really have to do
data entry
now if you don't have access to this .
qbo file i'm going to show you how to
convert the star QB or file on from
different formats however let me first
show you real quick how the . qbo files
work really well inside of quickbooks so
I'm here in QuickBooks Online something
to show your quickbooks online first
appear in the top right it says file
upload and we're going to click on that
and will 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 happened
to be on a Mac but it's going to 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'll
get this screen that says it's been
successfully uploaded and now in the
case of this one for example you can see
clearly 1500 transactions were uploaded
into quickbooks now they're not straight
into the register
you still going to have to categorize
these I'm however you know ask you
categorize these transactions
it does get easier and easier like for
example here it the information says
into a 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 it before it's actually a brand new
books files on creating a vendor called
into the payment solutions and i'm going
to put it here on there are bank service
charges either then or not 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 picked up of that is a
service right bank service charges are
expelling 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 can recognize there so you're going
to see 86 transactions are similar and
every single time this vendor was used
it gets categorized in that category
that I chose and I goes into the
register like that so you can actually
match at 86 transactions extremely
quickly so online banking
it's not direct input but it does create
the workflow for you the right to 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 cero
transactions for the time being
so i'm gonna click on the file menu
utilities import webconnect files is
allows the process i went to file and
went down to utilities import and then
webconnect files so i click on that and
the system is going to ask me what file
would you like to import and there it is
there's the final i dial i downloaded
from chase
so click on open and the system's asking
me well which account would you like to
link it so i'm going to 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 the other has been
successfully uploaded i'll click ok now
this is the bank feet screen and if
you're working with quickbooks 2013 2014
2015 or twenty sixteen 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 win this one for
example i can call in here into it
merchant solutions whatever I want to
call it right so i get to create the
vendor and i'm going to put that on to
bank service charges and then i'm going
to click on quick at and then what will
happen is quickbooks will start reading
similar transactions so if you see
appear on the top it says a hundred
transactions are similar when i click on
that you'll get to see how transactions
that have recognized something that has
been used before
get automatically categorize 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 quickbooks will
start learning as I go
but that is the concept of online
banking by manually uploading a . qbo
file
so let's move on to a PDF bank
statements
so first before we go into the
conversion of PDF bank statements into
this . qbo format and then can be
downloaded into quickbooks using their
bank feeds in QuickBooks desktop or the
bank downloads in quickbooks online as i
showed you is important to know in
whether your PDF bag statement
it's an original original PDF you
downloaded from the bank or if it is 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 of a
credit card it's it's actually the same
thing could be credit card 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 that are
perfectly highlighted this means this is
already in digital format and these are
the original PDFs from the back
so if you're working with the original
PDF from the bank
you can use a tool called PDF to QB oh
I'm going to put the links there below
in the video or you can use a tool
called . qbo convert pro which is pretty
much the same thing as PDF the qbo but
it has a couple of more settings built
in
so here's this is the tool that I i'm
talking about and whether you use PDF to
qbo or two qbo convert five
one tool
it's actually going to 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 going to
sorry
this software is going to convert this
to a . QB or format quickbooks is going
to read a great book us to know what
type it is right someone has to like
credit card then i'm going to click here
where it says look up so i can select
the bank so i'm going to search the
chase and if your back is not there
it actually doesn't matter which bank
you choose however if your consistency
is good to just choose the correct one
if it happens to be there on the list so
i hit okay and it's important to put the
bank account number or at least the last
four digits is gonna be an identifying
especially if you're converting multiple
files
i'm going to go ahead and click OK and
then when i click on convert
okay then it's going to ask me well
where's the file you want to convert so
might happen to be on the desktop so
i'll go ahead and pick up a going to
pick up that PDF file and then i can
either just go straight to a conversion
which can convert to csv which is which
we'll talk about a little bit later on
as i talk about csv to cure your
conversion
I can go straight to convert to kill Bo
and allow the software kind of do this
magic but you can go to preview this
actually my favorite thing to do because
when i click on preview
I'm going to get a grid and with this
grid shows me it's a it's a quick
preview of the transactions that are red
I'm 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 pee information i can see if it 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 the map it then I get the chance to
map it now
when when you have digital pdfs like
with the example that I showed you this
offer is pretty much flawless because
everything is in digital format when you
have to read a transfer
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 where 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 their in the
summary if the total payments at the
total charges where accurate sort of a
quick
automatic reconciling just to make sure
that the data is going to be complete
and I want to go ahead and click on
create qbo and the software will our
create this . qbo file that as i showed
you before
there you can go to file utilities
import webconnect and i'm going to go
ahead and choose that file that I just
converted and hit open then it's going
to ask me which credit card would you
like to use because in this particular
example we use the credit cards i'm
going to call a credit card here
visa I call it chase visa and you're
going to see the import scheme works
exactly the same way as the bank so this
offer actually works for importing both
bank statements and credit card
statements so i click on transaction
list just going to show you
and there it is there's the date that
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 it was are already on my
database then i just select it and
select the account
and then click here on quick at and it
will add it for me and ask you enter
more and more QuickBooks will learn this
is why this is my favorite way of
importing bank transactions into
quickbooks not 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 up that i
went ahead and i printed and scanned and
you're going to see the behaviors 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
they could be creases that could be
markings i can affect this whole thing
but if i try to select the text like i'm
trying to gun clicking and I'm trying to
do it now
and that's really when you know what
that's really when you know that this is
not the original PDF so what happens
with this exact same tool with PBS the
qbo or two qbo convert pro there is a
version called the PDF plus version that
can actually read scan statements
so let me go through the same process
here i'm going to go ahead and just
change the account number so when I
imported into quickbooks it doesn't
import into the same account and i'm
going to click on convert and i'm going
to go ahead and select that statement
and click on preview and you see it will
take a little bit longer
he goes to the process because it's
actually reading the image and using an
OCR optical character recognition to to
detect the the names and the amount of
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 much prefer use a digital statement to
about to avoid this potential issues and
here's the columns of how it detected
the information and you can actually see
here payee and
where the mapping matters right this is
what i tell it well that'sthat's pay and
this is the date
so this is where I tell her this is the
date and then we see the amount and then
the memo so it looks a very similar and
as I scroll down I get to see all the
transactions and for example in this
particular case I'm 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
this credits but but no debits and and
again it's not a perfect science if you
need to add the 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 the one payment let's
say that happens to be a
five-thousand-dollar 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 we
could do a lot of the work for you
at that point that would go to create
qbo and imported into quickbooks the
same way i did it with the other ones
now what if i don't have the . qbo file
and I don't have the bank statements in
PDF but i do have an excel file or a CSV
file so the most important thing is make
sure that the spreadsheet has at the
very minimum at the very minimum should
have these three columns which are date
pay or description
it doesn't matter it's the same sort of
concept and amount where negatives are
the charges or checks and possibilities
are there the depositor 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 we fix that they're
so those are there optional our columns
but date pay an amount is extremely
important so this is for example a
spreadsheet that I have
ok and then whether this spreadsheet was
downloaded from the bank or it was
created manually
notice that I all I really need is three
columns date pay an amount of go ahead
and delete have to show you
so as long as I have these three columns
here are the software can do is is
building 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 want to hand
write all the payees in there
it's a great tool to use in before you
import it into quickbooks that way you
don't have to retype everything in
quickbooks also and this is great for
petty cash logs
you know something that's not really a
bank but if you have a lot of petty cash
logs in an Excel you can convert them
into the dark you do which is going to
be great for you to be able to import
that into quickbooks so i'm gonna go
ahead and save save this as 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 we're
going to click on settings and I want to
tell it well this is a bank account or
credit card whatever happens to be
i'm going to put the account number in
this case I just for the last four
digits and then hit ok then i'm going to
click on convert and i'm going to select
that CSV file a half there i'll click on
preview just to double check that the
data is being read correctly and there
it is there's my date column there's my
pay
column there's my amount column and then
down here you see the mapping is correct
and i click on create qbo and now i have
my cubicle 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 show that at the very beginning of
this video if your bank allows you to
download the . 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 and what to do
there but if you can download the dark
your file
then it's great you have you don't pay
anything for it it's free
you can import into quickbooks online or
QuickBooks desktop however if you don't
have access to the doc you be all files
this is option number two you have the
digital PDF statements then you need
either PDF to qbo or two qbo convert
pros are the two options
their third option is let's say I don't
have the digital PDF but i have a
scanned PDF so have a scan paper bank
statement that need to get PDF to qbo
with the + or - qbo convert with a 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
CSV file then all i need is csv to QB oh
now i'm in the website here for the
vendor for the manufacture of this of
the software that i'm recommending and I
use it all the time for my practice and
that's why I I i made this video because
I you said i love it
I worked hand in hand with a developer
to make it better
you know all the time of suggesting new
features people email me and say Hector
will love that too qbo convert broke
into this so i could do that so I
usually talk to the developer and and we
talked through it with tested and this
is incredibly time saver especially when
I my client doesn't have access to the
bank it just has a PDF bank statements
and this allows me to really really
quickly go through it anyway
initially the website so here on the
right side there's all the software
programs there's two qbo convert pro
there's PDF to Cuba will convert and
then down here this csv to qbo now one
of the main questions that people ask me
is a hectare
what's the difference between the two
qbo convert pro and PDF to QB oh so the
difference is that PDF the qbo is a
single utility it takes your PDF bank
statements converts into qbo done
whether there are scan or digital it
just means that you have to get that
Plus version that cost a little bit more
but what - qbo convert pro
it combines multiple tools 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 think you can do
qif files are the quicken version so
some banks can only download into quick
and they can do the PDF of course
together 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 QB oh 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 a
hundred dollars for the CSV to qbo all
the way to two or three hundred dollars
for the for the highest and tool if
you're working with multiple clients are
multiple bank statements and you want to
save yourself hours of manual data entry
then I strongly recommend this product