Automating ADI journal entries for Month End Close Process

As accountant, FP&A or any other data custodian knows well, it is quite uninteresting and down-right tiring doing the same mundane manual tasks month-over-month year-after-year. We know there are better ways, but not all of us have the technical knowledge to promote automation.

This is the main goal for the series of knowledge exchange articles I have created and coined – "Well You Ought To Know".

Basic automation has served me well throughout my career and I felt it well about time for me to give back.

The articles will take the following form:

1. Defining the scenario
2. Walking through the solutions
3. Benefit realized

Defining The Scenario

Every month, as part of the month end close process, the three text files with data representing cash and marketable securities movements are provided to me by State Street.

My job is to remove the files from an ftp server, open each file, copy the content of each and paste the data to an ADI journal template (Excel Add-in used to upload journals to Oracle).

Then recalculate the data within Excel to validate that my calculated totals match each files trailer totals, assuring no fat-thumb error. Then post the journal.

The result of the automated (VBA macro) produces an ADI journal entry resembling the below image. The macro opens each file provided by State Street, parses each file line by line then creating a consolidated ADI journal entry.

Consider that we started here (the below image), then in seconds after initiating the macro we have a journal like the above image with over 100 or so line of transactions each month. The macro can handle as much lines as your version of Excel allows.

And the content of the text files looks like:

Walking Through The Solution

Here we get to the sauce of this tutorial and the main ingredients are macros. What's a macro you ask? In a quick breath, macros are small programs written with Microsoft Visual Basic for Applications, VBA for short. Macros are used for good and bad, I suggest you do a quick Google search to get more information amount macros.

For now, I will focus on breaking down the codes used for this automation. The hope is that you will be able to copy and edit the code to fit your specific situation. This is not intended to be a beginners tutorial on macros. There is a wealth of such tutorials online. Again, the main goal for these tutorials is for you to develop the understanding that automation is your friend and for you to become system-minded.

This macro could have been written many ways and with different program languages. What I created was actually copied from many sources online. I could have written for scratch and used a myriad of different methods – but why waste the precious time.

I didn't bother with references and acknowledgements to the gracious online contributors of the codes used. Quite frankly there is nothing unique here.

Some key understandings to keep in mind:

• Comments within a macro proceed a single quote as in – 'this is an example of a comment format
• Macro commands are executed within a single Sub procedure or using multiple subprocedure(s)/function(s) using calls as in:

Sub hello() 'singular procedure macro
  'a macro displaying message box with the text hello world in one sub
  Msgbox("hello world")
End Sub

'multiple procedure macro
Sub hello()
  'a macro displaying message box with the text hello world using two subs
  Call msgboxHello
End Sub

Sub msgboxhello()
  Msgbox("hello world")
End Sub

Essentially there are three sub procedures at play for this macro. Sub LoopThroughFolder() is the starting point for this macro. It calls (jumps to) getHowManyRows() and getData(myFile As String) to do some of the work.

Sub LoopThroughFolder()

Dim myFile As String
Dim MyObj As Object
Dim MySource As Object
Dim myPath As String
Dim file As Variant

myPath = "C:\Users\fbrown\Desktop\ss\" 'create a variable that references a folder
file = Dir(myPath) 'Dir function creates an array(a list) of all files in directory referenced

Application.EnableEvents = False 'turn off any additional alerts within the workbook
Sheets("JE").Select  'select (activate) worksheet named JE in the active workbook
Call getHowManyRows 'jumps to getHowManyRows() sub procedure
Range("c21").Select 'move the cursor to cell B20 of the active worksheet

While (file <> "")  'function to iterate through all files in folder "C:\Users\fbrown\Desktop\ss\"
    myFile = "C:\Users\fbrown\Desktop\ss\" & CStr(file)
    Call getData(myFile)
    file = Dir
Wend

Application.EnableEvents = True  'turn event alerts back on within the workbook

'Next three codes enter formulas and text into respective cells. ActiveCell.Row is the current row
Range("M" & ActiveCell.Row + 1).Formula = "=SUM(M20:M" & ActiveCell.Row - 1 & ")"
Range("I11").value = "SSB-KC GL FEED PAM "

End Sub

The getHowManyRows() sub procedure is used to clear rows 22 to the row in column B that has the text Totals. This assures that the template is clear every time before populating the data from the text file.

Sub getHowManyRows()

Dim tRows As Long

Range("b20").Select  'moves the cursor to cell B20 of the active worksheet
Selection.End(xlDown).Select  'use to the first non-empty row in current column B to find the last row in column B
tRows = ActiveCell.Row   'give variable tRows the row value of the current cell (eg. If current cell is A31 tRows will equal 31)

'used to remove all rows from 22 to tRows (rows 22 to 31 removed using above example)
If ActiveCell.Row > 22 Then
    Rows("22:" & tRows - 1).Select
    Selection.Delete Shift:=xlUp
End If

End Sub

The getData(myFile As String) sub procedure processes all the lines of the current file. Depending of each line content, further data transformation and extraction methods and conditionals are performed.

Sub getData(myFile As String)

Dim text As String, textline As String, posLat As Integer, posLong As Integer
Dim header As String

Open myFile For Input As #1  'open file into memory for reading as a file object reference by #1
Do Until EOF(1)  'keep doing until last line – end of file (EOF): used to loop through each record of file
    Line Input #1, textline  'read the current line records into textline String variable

    'if textline String contains PAM enter If block from second character
    If Mid(textline, 2, 3) = "PAM" Then
        header = Mid(textline, 30, 19)
        'header now equals the textline's characters 30 to 49
    End If

    If Len(textline) = 106 Then  'confirms that textline relates to an actual entry and is not a header
        'following lines use the Mid function to extract string required for each cell
        Range("c" & ActiveCell.Row) = Mid(textline, 2, 3)
        Range("d" & ActiveCell.Row) = Mid(textline, 5, 4)
        Range("e" & ActiveCell.Row) = Mid(textline, 9, 1)
        Range("f" & ActiveCell.Row) = Mid(textline, 10, 7)
        Range("g" & ActiveCell.Row) = Mid(textline, 17, 4)
        Range("h" & ActiveCell.Row) = Mid(textline, 21, 6)
        Range("i" & ActiveCell.Row) = Mid(textline, 27, 3)
        Range("j" & ActiveCell.Row) = Mid(textline, 30, 3)
        Range("k" & ActiveCell.Row) = Mid(textline, 33, 2)
        Range("l" & ActiveCell.Row) = "0000"
        Range("o" & ActiveCell.Row) = Mid(textline, 57, 20)

        'nested If statement: if value contains a – then it is a credit (column N) else it is a Debit (column M)
        If Mid(textline, 48, 1) = "-" Then
            Range("n" & ActiveCell.Row) = CDbl(Mid(textline, 35, 13))
        Else
            Range("m" & ActiveCell.Row) = Mid(textline, 35, 13)
        End If

        ActiveCell.Offset(1, 0).Select   'select one row down
        ActiveCell.EntireRow.Insert   'inserts a new row
    End If
Loop

End Sub

Benefit realized includes:

1. Reduced journal processing time
2. Reduced journal errors due to fat-thumb errors

Submitted by: Franklin Brown, CPA, CISSP, CRCP, CGEIT, CRISC

Author: Franklin Brown