So this post is the 2nd part of my recent post on how Excel can automate data entry to your accounting system. See here for Part A. This example concerns automating entries on your bank statement you can reliably identify. In my case it was to record bank transfers from my cheque account to an interest earning account and visa versa. I wanted Excel to capture these entries and actually create the 2 sided journal itself within a valid MYOB template.
From a bank download of data we can identify this specific transaction because the statement data shows the two accounts involved. So this data is consistent and totally reliable so we can build automation around it. You might have a few situations where this is the case in your bank transactions. For instance any entry with say Merchant in the bank entry is surely funds from your EFTPOS machine, or bank fees or regular debit charges to suppliers and so on. We can filter this data using Advanced Filter within VBA and return just the data we need. So my example solution is a workbook with one sheet (Sheet11) that contains all the bank transactions and a worksheet (in the case below Sheet5) to handle each specific transaction type. It needs to filter Sheet11 for the transactions we want in one section of the worksheet Sheet5, then convert this data to general journal rows for debits and credits in a second section of Sheet5 and finally in a 3rd section of Sheet5 convert the data to the exact MYOB required journal format. If we don’t do this precisely the way MYOB wants the data being an accounting system it will reject the entry.
Below shows the 1st section of Sheet5 AD:AM which is the Criteria to be used by Advanced Filter AD, the Advanced Filter Output AF:AJ and in AL:AM a cell for the Month’s data we want as well as some checks and the macro button. Note, you would think I would only have 2 criteria because its a case of transfers between two bank accounts but our bank closed our branch and so a new BSB number with the same account number became a 3rd possibility.
So now in this 2nd section of Sheet5 W:AB (we going right to left) there is an intermediate manipulation section to convert the filtered data to double entry accounting entries. Basically we need double the rows with a positive and negative side just using basic formulas.
So then in B:S of Sheet5 is Section 3 the MYOB General Journal format which uses basic formulas to pick up what it needs from Section 2. Now I have used dummy data of 4 rows but in the real situation was 20 rows meaning 40 lines of journal entries automated and so why I took the effort to create this kind of solution.
Here is the VBA behind the button in the first image.
If you need my consulting help to set up automation for your business I would be happy to hear from you.
[vb]
Sub AdvFilterMoneyTransfers()
‘—————————————————————————————
‘ Procedure : AdvFilterMoneyTransfers
‘ Author : John Hackwood theexcelfactor.com e:[email protected]
‘ Date : 20/03/2015
‘ Purpose : Use Adf Filter to Extract DMoney Transfer Data for upload to MYOB Process
‘————————————————————————————
Dim FinalRow As Integer
Dim MonthChosen As String
Dim DateofMonthChosen As Date
Dim TargetRng As Range
On Error GoTo err
‘*** Caution specific addresses used
Set ChqOutput = Sheet5.Range(“AF2:AJ2”)
Set ChqCriteria = Sheet5.Range(“ChqMoneyTransfers”)
FinalRow = Cells(Cells.Rows.Count, “AF”).End(xlUp).Row
If FinalRow > 3 Then
FinalRow = 3
End If
‘ Need to clear previous Bank Statement Data
Sheet5.Range(“AF3”, “AJ” & FinalRow).Clear
‘ Copy in header row from Bank Statement area
Sheet11.Activate
‘On Error Resume Next
Sheet11.Columns(“B:F”).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=ChqCriteria, CopyToRange:=ChqOutput, Unique:=False
Application.CutCopyMode = False
‘apply borders
FinalRow = Sheet5.Cells(Cells.Rows.Count, “AF”).End(xlUp).Row
Set TargetRng = Sheet5.Range(“AF3”, “AJ” & FinalRow)
With TargetRng.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 48
End With
With TargetRng.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 48
End With
With TargetRng.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 48
End With
With TargetRng.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 48
End With
With TargetRng.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 48
End With
With TargetRng.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 48
End With
‘On Error GoTo 0
‘
Set ChqOutput = Nothing
Set ChqCriteria = Nothing
Set TargetRng = Nothing
Sheet5.Activate
On Error GoTo 0
Exit Sub
err: MsgBox “oops something is wrong :(”
On Error GoTo 0
End Sub
[/vb]