I have been putting off blogging about this topic because it’s unwieldy to explain and involves work done many years ago that I would do better now. I cringe a bit seeing an Offset formula now which I would only use these days as a last resort because it’s volatile. But let’s see if it comes together as the process I went through was important and the result very valuable in terms of saving me .
One of the most satisfying Excel applications I ever did was as a dental practice owner was automating data entry to our MYOB Accounting system using Excel. It involved using Excel templates to upload an accounting system. It worked well for many years without me touching it.
At quarterly BAS time, I would sit down and punch in 3 months worth of sales entries for our practice from a workbook called the Cash Book where we summarized our daily transactions. We had a 4 chair practice and took Cash/Cheques, EFTPOS, HiCaps and Credit Cards so I would have a journal for each day with about 8 lines with data granularity for the 4 providers and the 4 payment types. So 8 lines X about 60 work days per quarter that was 480 lines of tedious data entry, yuck! Used to take me about 3 hours and I hated it.
I had tried to import to MYOB from an Excel template a few times but it always mysteriously failed. One day I just got the right Indian lady on the MYOB Sydney support line who persevered with the steps and it worked. I was so grateful to that clever lady. Once I knew it would work I then set about to add automation to the process. After it was completed entering Cash Book enties to my Accounting system went from 3 horrible hours work to just 2 minutes. That is a beautiful thing. I think we owned the practice for another 8 years after this so 8 years X 4 quarters x 3 hours each quarter = 96 hours that’s 12 work days saved of really unenjoyable work. I also extended this concept to other entries that had to be done which I briefly explain below and post later, so it got even better than this.
Maybe some of you are thinking what’s he on about, key punching into Excel is no different to key punching into my accounting software so where is the saving?
The thing is I applied automation in two basic ways:
A) I created let’s call them ‘back’ worksheets as MYOB template sheets that manipulated data our staff were entering anyway into ‘front’ Excel sheets. One of the main ones was a Cash Book Excel worksheet at the front of the workbook which our staff entered transactions to record our takings. The ‘back’ worksheet MYOB Template basically manipulated and converted the ‘front’ sheet Cash Book data to MYOB format with checks and balances. Secondly, I also set up a workbook for fast, easier more efficient keying of invoice data by myself or staff to produce MYOB Spend Money entries. Nothing brilliant in this just allowed the using of formulas and copying to set up a journal fasts. So after this, there was little and often no keypunching needed by me for the bulk of the practice day to day transactions.
B) I used Advanced Filter in VBA code to extract data from our bank transaction CSV download for handling certain types of entries where they could be reliably identified and were consistent month to month e.g. transfers back and forth to an interest earning account and then created from MYOB entries from this. Now some may be thinking I don’t like Advanced Filter its so temperamental, and I would agree when used manually but in VBA, once set up, its very well behaved and reliable and works like a charm. This will be a Part B of this post in the near future.
So sticking now with Part A the Cash Book only for the remainder of this post…
Step 1
Starting with ‘the end in mind’ I exported the MYOB General Journal to CSV. Its best to test importing a dummy Excel based entry at this point. I bit of mucking around here and deleting and re-importing entries might be needed so you understand how it works and what’s static and what’s now. Once I had this right This CSV file was cleared, saved to Excel and became my template for later importing. I am presuming other accounting software like Xero will let you do the same thing. I used to be a Management Accountant in mining companies and packages back in the 90s allowed Excel or CSV based templates to be uploaded.
Step 2
I can’t show you our Cash Book it’s a bit too private for the net but imagine a protected simple Cash Book style spreadsheet with dates for each day going down he page and all data needed for each day in columns across the top. The Exported CSV was copied to a new sheet at the back this Cash Book which looked like this columns B:S…. By the way for any MYOB users, I used the General Journal for my receipting of cash not Receive Money.
To the right of this template there was a small setup area for the date range of the month and public holidays in the month I would need to exclude as we only want the journal to only show real workdays excluding public holidays in Column C which had this formula (the Range Name PubHolsinUploadPeriod defined in AB).
In C2:
=IF(WORKDAY(Z3-1,1,PubHolsinUploadPeriod)>$AA$3,””,WORKDAY(Z3-1,1,PubHolsinUploadPeriod)) provided the first date
In C11:
=IF(C2=””,””,IF(WORKDAY(C2,1,PubHolsinUploadPeriod)>$AA$3,””,WORKDAY(C2,1,PubHolsinUploadPeriod))) provided the next date and then in C20 and so on a similar formula would refer to the prior day for the rest of the dates needed or remain blank.
In H the Debit Column, this formula went in to the front Cash Book worksheet and extracted the data for the rows with debit entries…
=IF(C2=””,””,INDEX(‘Daily Deposit’!A$1:AF$3999,MATCH(C2,’Daily Deposit’!A:A,0),OFFSET(AH$26,0,0))) (cringing at the OFFSET)
There was a similar entry in Col J for the Credit side of the entry….
=IF(C5=””,””,INDEX(‘Daily Deposit’!A$1:AF$3999,MATCH(C5,’Daily Deposit’!A:A,0),OFFSET(AB$26,0,0))+INDEX(‘Daily Deposit’!A$1:AF$3999,MATCH(C5,’Daily Deposit’!A:A,0),OFFSET(AB$26,0,5)))
Formulas varied by each line within the 8 line entry (not ideal now in hindsight) because some where always debits so only appeared in H and their credit side 0. I am by now really feeling how hard this topic is hard to explain and wonder if there are any readers left at this point!
So ending this quickly – as I didn’t want to try and upload rubbish which MYOB would reject, immediately to the right of the journal I had a Check area see Cols T:X. This checked for Debits <> Credits and if any dates were not Weekdays as follows:
In T9:
=IF(C2=””,””,IF(AND(WEEKDAY(C2)>1,WEEKDAY(C2)<7),””,”Not a weekday!”))
In T2:
=IF(COUNTIF(T9:T207,”Not a weekday!”)=0,”Dates all OK”,”Problem with Date(s)”)
In U3:
=IF(OR(U2<>V2,U2<>W2,U2<>X2),”Error! Debits <> Credits Look for red highlighted entry(s) below for errors”,”OK Debits and Credits match”)
and conditional formatting would then highlight the entry or date in error in red. So if there were no red cells in this area I would copy Col B:S as values to Notebook (I had some VBA to make this easier) and the Notebook file was named and filed. Then back to MYOB and this file was Imported as a General Journal.
Whew!
Look as I have said the point of the blog was not to go through every aspect of what I did but just show my approach about 10 years ago which worked so well for so long. Each business will have a different situation to handle. Many years on I would do the formulas differently e.g. I would nowadays aim for the same formula on each row within a column and no OFFSET usage.
There is no better more satisfying use of Excel than automating tedious work. If you need consulting help setting up this process in your business I would be more than happy to hear from you.