Get Cash Deposit Summary in Indian Bank Statement using Excel VBA

How to Get a Cash Deposit Summary in Indian Bank Statements Using Excel

Managing and analyzing bank statements manually can be time-consuming, especially when tracking cash deposits. With Excel VBA, you can automate this process and quickly generate a cash deposit summary from your Indian Bank statements. Follow these simple steps to streamline your financial analysis.

Steps to Extract a Cash Deposit Summary

Open Your Bank Statement in Excel

Ensure your Indian Bank statement is opened in Microsoft Excel. Rename the sheet to Sheet1 for seamless execution.

Access the VBA Editor

Press Alt + F11 to open the VBA Editor.

Insert a New Module

In the VBA Editor, go to Insert > Module. This will create a new module where you can paste the VBA code.

Paste the VBA Code

Copy and paste the provided VBA code into the module. This script will process your Indian Bank statement and extract cash deposit transactions based on the selected period.


Sub ExtractDailyClosingBalanceAndCashDeposits()
    Dim wsSource As Worksheet
    Dim wsResult As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim dateColumn As Long
    Dim balanceColumn As Long
    Dim dateDict As Object
    Dim postDate As Variant
    Dim balance As Double
    Dim cashDepDict As Object
    Dim details As String
    Dim credit As Double
    Dim currentDate As Date
    Dim startDate As Date
    Dim endDate As Date
    
    ' Define the start and end dates explicitly
    startDate = DateSerial(2024, 3, 1) ' Start date
    endDate = DateSerial(2024, 4, 30) ' End date
    
    ' Set the source worksheet
    Set wsSource = ThisWorkbook.Sheets("Sheet1") ' Replace with the actual name of your source sheet
    
    ' Create a new worksheet for results
    Set wsResult = ThisWorkbook.Sheets.Add
    wsResult.Name = "Results"
    
    ' Initialize dictionaries for daily balances and cash deposits
    Set dateDict = CreateObject("Scripting.Dictionary")
    Set cashDepDict = CreateObject("Scripting.Dictionary")
    
    ' Set the column numbers for Date and Balance
    dateColumn = 1 ' Assuming Date is in the first column (A)
    balanceColumn = 7 ' Assuming Balance is in the seventh column (G)
    
    ' Find the last row in the source worksheet
    lastRow = wsSource.Cells(wsSource.Rows.Count, dateColumn).End(xlUp).Row
    
    ' Initialize the opening balance for the start date
    dateDict(startDate) = 0
    
    ' Loop through the source data
    On Error Resume Next ' Ignore errors and continue the loop
    For i = 2 To lastRow ' Assuming the headers are in the first row
        ' Read the Date and Balance from the source data
        postDate = wsSource.Cells(i, dateColumn).Value
        If IsDate(postDate) Then
            balance = CDbl(Val(Replace(wsSource.Cells(i, balanceColumn).Value, "Cr", "")))
            ' Check if balance is numeric
            If Not IsNumeric(balance) Then
                balance = 0 ' Set balance to zero for non-numeric values
            End If
            ' Update the daily balance in the dictionary
            dateDict(postDate) = balance
            
            ' Check for cash deposits
            details = UCase(wsSource.Cells(i, 3).Value)
            credit = wsSource.Cells(i, 6).Value
            If InStr(details, "BY CASH DE") > 0 And credit > 0 Then
                If cashDepDict.Exists(postDate) Then
                    ' If cash deposits already exist for this date, add the credit amount to the existing total
                    cashDepDict(postDate) = cashDepDict(postDate) + credit
                Else
                    ' If no cash deposits exist for this date, initialize it with the credit amount
                    cashDepDict(postDate) = credit
                End If
            End If
        End If
    Next i
    On Error GoTo 0 ' Reset error handling
    
    ' Create an imaginary column with each day in the defined date range
    Dim imaginaryColumn As Object
    Set imaginaryColumn = CreateObject("Scripting.Dictionary")
    currentDate = startDate
    Do While currentDate <= endDate
        imaginaryColumn(Format(currentDate, "dd-mm-yyyy")) = currentDate
        currentDate = currentDate + 1
    Loop
    
    ' Fill in the missing dates and their corresponding balances
    currentDate = startDate
    Do While currentDate <= endDate
        If Not dateDict.Exists(currentDate) Then
            ' Fill in the missing date with the previous day's balance
            dateDict(currentDate) = dateDict(currentDate - 1)
        End If
        currentDate = currentDate + 1
    Loop
    
    ' Get the unique dates from the dictionary
    Dim sortedDates() As Variant
    sortedDates = imaginaryColumn.Items
    
    ' Sort the dates in ascending order
    Dim tempDate As Date
    For i = LBound(sortedDates) To UBound(sortedDates) - 1
        For j = i + 1 To UBound(sortedDates)
            If sortedDates(i) > sortedDates(j) Then
                tempDate = sortedDates(i)
                sortedDates(i) = sortedDates(j)
                sortedDates(j) = tempDate
            End If
        Next j
    Next i
    
    ' Write daily closing balances to the results sheet
    wsResult.Cells(1, 1).Value = "Date"
    wsResult.Cells(1, 2).Value = "Closing Balance"
    For i = 2 To UBound(sortedDates) + 2 ' Adjust loop range
        wsResult.Cells(i, 1).Value = sortedDates(i - 2) ' Adjust index
        wsResult.Cells(i, 2).Value = dateDict(sortedDates(i - 2)) ' Adjust index
    Next i
    
    ' Write cash deposits to the results sheet
    wsResult.Cells(1, 4).Value = "Date"
    wsResult.Cells(1, 5).Value = "Cash Deposit Amount"
    i = 2
    For Each postDate In cashDepDict.Keys
        wsResult.Cells(i, 4).Value = postDate
        wsResult.Cells(i, 5).Value = cashDepDict(postDate)
        i = i + 1
    Next postDate
End Sub

Set the Date Range

Modify the script to specify the time period for which you need the cash deposit summary.

Run the VBA Code

Click the Run button or press F5 to execute the script. Excel will generate a summary of all cash deposit transactions within the selected period.

Why Use VBA for Bank Statement Analysis?

Automates repetitive tasks – No need for manual filtering.
Increases accuracy – Reduces human errors in calculations.
Saves time – Extract data instantly without complex formulas.
Customizable – Modify the script to fit your financial needs.

Conclusion

By using VBA in Excel, you can easily extract a cash deposit summary from your Indian Bank statements, making financial management more efficient. Follow these steps and automate your cash deposit tracking today.