![]() 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. I am presuming other accounting software like Xero will let you do the same thing. Once I had this right This CSV file was cleared, saved to Excel and became my template for later importing. 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. Its best to test importing a dummy Excel based entry at this point. Starting with ‘the end in mind’ I exported the MYOB General Journal to CSV. So sticking now with Part A the Cash Book only for the remainder of this post… ![]() This will be a Part B of this post in the near future. 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. transfers back and forth to an interest earning account and then created from MYOB entries from this. So after this, there was little and often no keypunching needed by me for the bulk of the practice day to day transactions.ī) 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. Nothing brilliant in this just allowed the using of formulas and copying to set up a journal fasts. 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. The ‘back’ worksheet MYOB Template basically manipulated and converted the ‘front’ sheet Cash Book data to MYOB format with checks and balances. 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 thing is I applied automation in two basic ways:Ī) I created let’s call them ‘back’ worksheets as MYOB template sheets that manipulated data our staff were entering anyway into ‘front’ Excel sheets. 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? 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. 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. After it was completed entering Cash Book enties to my Accounting system went from 3 horrible hours work to just 2 minutes. Once I knew it would work I then set about to add automation to the process. One day I just got the right Indian lady on the MYOB Sydney support line who persevered with the steps and it worked. I had tried to import to MYOB from an Excel template a few times but it always mysteriously failed. 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. 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. It worked well for many years without me touching it.Īt 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. It involved using Excel templates to upload an accounting system. 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. 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. I cringe a bit seeing an Offset formula now which I would only use these days as a last resort because it’s volatile. 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.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |