Automating ADI journal entries for Month End Close Process

Excel Month End Close VBA Misc

Disclaimer: The writing and musings of the author do not necessarily reflect the views of his employer.

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

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.

Defining The Scenario

Every month, as part of the month end close process, three text files with data representing cash and marketable securities movements are provided by State Street. The 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, then recalculate the data within Excel to validate that the calculated totals match each file's trailer totals, then post the journal.

Walking Through The Solution

The main ingredients are macros — small programs written with Microsoft Visual Basic for Applications (VBA).

Sub LoopThroughFolder()

Sub LoopThroughFolder()
    Dim myFile As String
    Dim myPath As String
    Dim file As Variant

    myPath = "C:\Users\fbrown\Desktop\ss\"
    file = Dir(myPath)

    Application.EnableEvents = False
    Sheets("JE").Select
    Call getHowManyRows
    Range("c21").Select

    While (file <> "")
        myFile = "C:\Users\fbrown\Desktop\ss\" & CStr(file)
        Call getData(myFile)
        file = Dir
    Wend

    Application.EnableEvents = True
    Range("M" & ActiveCell.Row + 1).Formula = "=SUM(M20:M" & ActiveCell.Row - 1 & ")"
    Range("I11").value = "SSB-KC GL FEED PAM "
End Sub

Sub getData(myFile As String)

Sub getData(myFile As String)
    Dim text As String, textline As String
    Open myFile For Input As #1
    Do Until EOF(1)
        Line Input #1, textline
        If Len(textline) = 106 Then
            Range("c" & ActiveCell.Row) = Mid(textline, 2, 3)
            Range("d" & ActiveCell.Row) = Mid(textline, 5, 4)
            Range("m" & ActiveCell.Row) = Mid(textline, 35, 13)
            ActiveCell.Offset(1, 0).Select
            ActiveCell.EntireRow.Insert
        End If
    Loop
End Sub

Benefit Realized

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