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