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:

  • Defining the scenario
  • Walking through the solutions
  • 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 on 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 call (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)  ‘jumps to getData(myFile) sub procedure
file = Dir  ‘takes the next file referenced in the Dir object listing (array)

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(“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 form 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 – example textline = “JPAM hello the world keeps tur t to get off” header = “ning even if we wan” 30 to 49 characters of textline the Mid function

End If

If Len(textline) = 106 Then  ‘confirms that textline relates to an actual entry as is not a header ‘following 11 lines use the Mid function power 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 used to confirm a debit or credit entry if value contains a – then it is a credit enter in column N else it is a Debit enter in 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

 

Benefit realized includes:

  • Reduced journal processing time
  • Reduced journal errors due to fat-thumb errors

 

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

Leave a Reply

Your email address will not be published. Required fields are marked *