Onion - the missing ingredient for Sage Line 50 / Sage Instant accounts packs in Excel

Onion - the missing ingredient for Sage Line 50 / Sage Instant accounts packs in Excel
Full audit trails to underlying transactions from P&Ls, Balance Sheets, graphs, PivotTables, and departmental TBs in a stand-alone Excel file. Aged Debtors and Aged Creditor files too. Free 30 day trials. Download today at www.onionrs.co.uk

Sunday 27 March 2011

E-reconcile for everyone in Sage Line 50 / Sage Instant

I was using the manual bank reconciliation feature in Sage Instant Accounts the other day and found it to be a bit laborious marking cash book transactions off one by one.  I don't subscribe to the file transfer module offered by my bank (for an extortionate amount of money) so I thought the e-Banking, e-Reconcile option was out. 

However, then I remembered some work I'd done some considerable time ago where bank accounts with tens of thousands of transactions had to be reconciled in a different software package.  The only statement information available was a csv file containing statement date, reference and amount.  I constructed an Excel macro to create a statement file similar to the ones produced by the bank's file transfer module (http://www-2.danskebank.com/Link/CAPDBUK/$file/CAP_DB_BRAND_UK.pdf) and, hey presto, I was able to import the necessary tens of thousands of bank statement transactions and get to work. 

Thinking about my Sage situation I realised that an ordinary set of transactions saved from my basic electronic banking screens will give me a csv file containing statement date, reference and amount. Consequently, I dusted down the old Excel routine and ran a month's statement transactions through it. I enabled e-Banking within Sage and imported the statement details using the file I'd created.  Now I am able to use the auto reconcile features within Sage!  Nice. Even better, I find that, with a bit of pre-processing in the Excel routine, I can maximise the "full match" (reference and amount) success rate and cut down manual matching to a minimum.

It should be possible to use this methodology for any bank account, so I'm planning to implement this approach for all Sage bank accounts where file transfer facilities don't exist with the bank.  I like to have software do as much work for me as possible so this fits in with my overall stratregy pretty well.

P.S. if you use Sage you may also be interested in this Excel reporting tool I've written - www.onionrs.co.uk. A video is available on YouTube.