We get quite a few bank payments into 5 of our bank accounts which all get transferred into a single bank account through out the month. I have all these bank accounts set up in Sage. I update these transfers manually in Sage at the end of the month using the bank transfer function. This can be a pain to do as it does take some time.
So I was looking for someone to show me how I can do these as an import. I can download the actual bank statements in excel so it would be easy to copy/cut/paste into an import file.
I have had a had look a the Sage help files however I don't think it lists what I am trying to do. There are some templates from Sage at C:\Program Files\Sage\Accounts but these do not really do what I want to do.
Hi ??? (there is no name on your profile!)
Can you do journals to/from the Bank in this version of sage so that the items will still show up in Bank Recs? (Older versions wouldnt allow that and would skew the Bank rec). Do backup/test back up try it out first.
If so then use the audit trail import file - instructions are held within sage. Dont forget to include both side of the journal and also that all figures are positive, it wont take negatives.
Failing that - why not suggest they either do a transfer once a month from the 5 other Bank accounts or even ask your Bank about setting up a 'Group' so that you dont need to do transfers at all (assuming they are all sterling accounts and non interest bearing)
__________________
Joanne
Winner of Bookkeeper of the Year 2015, 2016 & 2017
Thoughts are my own/not to be regarded as official advice,which should be sought from a suitably qualified Accountant.
You should check out answers with reference to the legal position
There is not Journal button on the bank accounts screen, only on the nominal codes screen. When I look at the journal entry for this there is not entry for A/C numbers for the banks so I cannot see how I can do it this way.
Sorry the other 4 banks are almost virtual bank accounts that just sit in Sage, basically set up as Amazon and Paypal bank accounts. I get the transfers from these. I have added the virtual bank accounts so I can input all the sales into them and then transfer the funds from them into to my main bank account.
Hi Asif
You can add your name to the signature bar by going to 'edit profile'.
I use sage professional and can do journals via the Bank which will show when you come to do a Bank Rec, but you need to double check yours before you go any further. I think you can, but no point putting all the effort in if it doesnt work.
Do the backup and test the backup as I suggested first. Then go to the nominal screen and do a journal between say your Paypal account and the current account dated a few days ago and then go into Bank Rec on the current and see if you are able to match the entry. If so you can do the import.
Dont forget to then do a restore to get your data back to where it was before you did the test.
NB - you can also try it out in and anything else in fact in the practice data module.
If all is ok then you can use that sheet I mentioned - if you hover over each heading or click on the little red square - you can see some handy hints one of which is that if you are doing a journal you can leave the 'Account reference' box blank.
You need to know which are your debits and credits to match them to the relevant bank accounts and dont forget to T9 code them as there is no VAT involved.
Suggest you only do a couple on the first upload, with relevant backups in place first until you get used to the info required. Also it doesnt need to be a csv file, an excel one will do.
__________________
Joanne
Winner of Bookkeeper of the Year 2015, 2016 & 2017
Thoughts are my own/not to be regarded as official advice,which should be sought from a suitably qualified Accountant.
You should check out answers with reference to the legal position
Just tried it on the practice date and it works. :)
So now I need to test it with the import function. However I am a bit confused with the "audit trail import file". Is the one attached the correct one?
Thank you. I will give this a go as I can get the banks in the journal entry.
So just to make sure I am doing this right can you please check the attachment, do I have the entries the right way round?
Thanks for the tip on testing on the practice data module.
You do have the entries the correct way round. Good idea to write this down somewhere for your first few attempts:-
To INCREASE the Bank use a DEBIT
To Decrease the Bank use a Credit.
(It is the opposite of the way the Banks do it - good reason but no time to explain now Im afraid!)
Hint - Might be worth adding something to the narrative, just as you are able to in the Bank screen Eg Transfer in from Paypal / transfer out to HSBC. Unless that makes it more confusing.
Another hint - JD is a Journal Debit when you come to complete the spreadsheet file.
Hope that helps!
__________________
Joanne
Winner of Bookkeeper of the Year 2015, 2016 & 2017
Thoughts are my own/not to be regarded as official advice,which should be sought from a suitably qualified Accountant.
You should check out answers with reference to the legal position
Hi Asif Sorry our posts crossed! Ok here goes. Not helped by when you open the file the first column is missing from view!! Just use your scroll bar along the bottom to get to it.....it was the same on mine but I knew straight away something was missing cos Ive used the import file lots!
Easier for now if you keep the headings the same otherwise when you come to 'map' it into sage you might find a problem - again practice helps!
Just tried to add it on here but its skewed my headings - give me a mo!
__________________
Joanne
Winner of Bookkeeper of the Year 2015, 2016 & 2017
Thoughts are my own/not to be regarded as official advice,which should be sought from a suitably qualified Accountant.
You should check out answers with reference to the legal position
