Excel: help with my standing orders

Excel: help with my standing orders

Didn't find your answer?

An increasing number of clients are paying by standing order and as such keying the information in Sage is becoming a tiresome task. I would love to be able to download my bank statements and import the standing order data into a spreadsheet (no problem so far) but which would then covert the particulars on the bank statement into the appropriate client code for Sage.

E.g. bank statement says Joe Bloggs would like to return a value in excel of BLO100B

Many thanks

Mark

Replies (8)

Please login or register to join the discussion.

avatar
By User deleted
03rd Mar 2010 13:14

Recurring Entry

I set up a recurring entry in SAGE at the same time as the bank mandate that way you dont have to post the bank entries but you also have a double check that a standing order has been paid. Having saud that we have now almot moved away from standing orders to Direct Debit as its easier to administer and change prices! Its not as expensive as you might think!

Thanks (0)
avatar
By mark1975
03rd Mar 2010 13:28

Recurring entries

Thanks, I had considered that but wished to avoid as it is not uncommon for clients to miss a month or, becaues of weekends, some month end entries creeping into the next month.

Thanks (0)
avatar
By paulwakefield1
03rd Mar 2010 14:02

Vlookup is one option

If you can rely on the same description appearing on the bank statement each month, set up a table listing the bank descriptions and in the next column the Sage codes.

You can then use the Vlookup function to convert the codes. Something like =VLOOKUP([reference to name from bank statement], [Reference to table range],2,false).

Make sure the table range reference is absolute. 2 is the table column number for the answer, false ensures an exact match is found.

 

Thanks (0)
avatar
By mark1975
03rd Mar 2010 15:07

Vlookup

Thanks Paul

 

Took me a while to figure it out, but that's exactly what I'm after.

 

Thanks again

Mark

 

 

Thanks (0)
avatar
By mark1975
03rd Mar 2010 16:52

Additionally

For anyone interested.

 

My bank statements did not return the same description each month but I found that by applying the first respondant on this previous post https://www.accountingweb.co.uk/item/94871 I was able to work round it.

 

Mark

Thanks (0)
avatar
By User deleted
04th Mar 2010 09:45

Direct Debit

I asked my bank about setting up direct debit facility and they told me I was too small!

Thanks (0)
avatar
By User deleted
12th Mar 2010 14:40

Direct Debits which Bank

I have also tried to change from Standing orders (approx 100 per month). My Bank (Barclays)said I was too small. 

Would be very interested if anyone knows of a Bank that will entertain DD's for smaller enterprises. 

I have looked at various third party online companies that will process DD's for a fee but I am not sure that I want someone else billing my customers. I would want to retain control of that myself.

Thanks (0)
avatar
By sandyhatter
12th Mar 2010 14:46

Try this company - we use them and they are excellent

 http://www.idirectdebit.co.uk/about-payment-processing-solutions.html

Thanks (0)