Import a Credit Card Statement


Hi, so you may have read recently that
there was a conversation on Facebook about using VT transaction+ to cash code a
statement and a very similar way to Xero so what I’ve got here is I’ve got a
statement from a credit card that I had scanned in by Auto entry so this is a
credit card statements for October November and December
and I’ve also got for the rest of the year so concentrate on the first three so the first thing that we need to do in
Excel is select these and sort them by details and start grouping all the similar entries together and then it’s just the
case of copying that going over to VT transaction+ and then what we want
to do is use this universal input sheet and we’re going to import from the
clipboard now this will give us the option to set what each each row in
the clipboard is so I’ll go to general options so the first one is the date the
second is the details and the third is the total and then just import that and
that gives us the whole sheet, now we need to fill in these blank columns but
this is quite simple and VT so just right-click and choose set then credit
card so these are all payments it’ll auto the reference number and I’ll put a
primary account as the current account in this case that’s not the case so
right click and say it and we’ll set that to credit card and it’s simply
just the case of highlighting this right click and choose fill down and that will
fill all of these details It’s also auto-filled
the analysis column to be the same as the total so all that’s left for us to
do is choose the analysis account so let me just hide this make it a bit easier
to see so the first thing is BP which is going to be right click, set that’s
an expense and it’s a motor expense okay the second one’s BT so what I can do is I can highlight these and just choose fill down next one is Cauldwell Wright which is a supplier so cost of sales, purchases OK there’s some extra things from Cauldwell Wright copy them, sorry select them and fill down next one is Co-op groceries that’s going to be drawings so just set that as capital account drawings okay
and then that’s drawings and that’s drawings as well and that’s drawings as
well select them all and fill down next one is powercraft so what we’ve got
is we just set that to expenses, hire of equipment and then just select these
and fill down and then just go through the bank statement, sorry credit card statement just doing the same roweb timber that’s going to be cost
of sales purchases couple for them fill down Sainsbury we’ve done drawings already so let’s just copy that a paste in into there, save
going and setting up. Screwfix thats a purchase. right click copy and paste it in there and then we’ll just copy that down and just do that util we fill up the
statement ok then copy and paste and that’s us and
it’s just a case of clicking post and that tells you that all be posted and then cleared from this sheet and if we close that will see that they have all being posted in
to VT that’s great for the first one but it makes even quicker for the rest what
we’ll do is go back to excel and we’ll get the remainder of the year we will do
the same with us we’ll select all and we’ll sort that by details based on the
values A to Z and then we’ll copy that we’ll go back to Universal input sheet
and we will post from the clipboard it remembers what the columns we’re before
so we don’t need to them put that again just click OK.
Put all the details in. It’s not put the total in but that’s fine. What we can
do is we can just select all that and we can copy that across to this column and
paste it in okay just set these again so we’re going to set that as payment auto
and we’ll change that to a credit card again same thing again we’ll select
all these something’s going wrong here I think machines hung okay so my
computer hung there for a second but I’ve copied it all back in and ready to go from where we were so set this as payment set this as credit card and
again it’s just a case of selecting these rows and choosing fill down and it
fills it all in. The good thing about this now is that because we put some of
these things on before it can sometimes remember what it’s done before and VT so
what we’ll do is we’ll just select all of these analysis column for everything
right click and choose autocomplete and where it can it will remember what it
was used for before and then we just need to sort out the one that we don’t
have before so we just need to set everything that it doesn’t do so the
more that you use this the more it will remember what each supplier or details
field is for and it will start to fill them in without you needing to do very
much work at all and just using the copy and paste you can really move through
this quite quickly so we had some motor expenses there we can copy that and we
can just paste that into there let some drawings just like copy and paste that
and fill down. We’ve got a few drawings here and just fill down I’ve
got a bit more fuel copy that in there and paste. Fill down I’ve got
some more fuel copy and paste that in copy paste I’ve got some Powercraft it seems that auto entry hasn’t picked up these quite right its put some speech marks in
front of them so it’s not picked up quite right and if you don’t have
something we can still go back and to set and set that as drawings. We’ve got
tile giant so that’s going to be purchases just paste that in there and
fill down and it’s got these slightly wrong but we can still just to fill down Tyson Tyres is going to be motor
expensive so let’s just set that as expenses motor expenses okay
and again we can just fill down and in fact there’s some fuel so we can
just fill it all down and it’s just not picked up properly there so we can
fill down there and that’s us completed a whole year worth of credit card
statements in about 15 minutes and that’s the the power of the universal
input sheet on VT

, , , , , ,

Post navigation

Leave a Reply

Your email address will not be published. Required fields are marked *