Creating account balances in Scutum ledger

In Ledger commander the rows in account balances are generated automatically. Each account has an opening date and a closing date. If the account is not closed then the current day can be used as closing date. You take each account and each date from the Dates table and if the selected date is in the [opening date; closing date] period for the selected account then an account balance row is created. In Ledger commander this is done by an Sql query. In Scutum ledger there are two options. 

Manual row creation

You have to fill the Id, Date Id and Account Id manually, the rest of the columns are calculated automatically. You have to perform the algorithm above by hand. Fortunately, you do not open or close an account every month so creating the rows for a new date can be fast. 

Here you already have the rows for Date Id 7.

Increase the table by dragging down the bottom right corner (the blue triangle).

Copy the rows with Date Id 7 and paste them to the new area.

Select the last two good Id and drag them down. 

Now the Id column is correct. 

Set the correct Date Id, 8, beside Id 29. 

Drag down the selection and refresh the sheet. Of course, if you closed an account, for example, the Cib current account in mid February then delete the row where the Id is 30. 

Row generation by macro

I f you prefer the automatic generation of the account balance rows then enable the macros and press the "Generate Account Balances" button. 

To see the corresponding macro press "alt + F11" and select the "AccountBalances" item at the left. 

 Public Sub GenearteAccountBalances()
    Dim dateSheet As Worksheet
    Set dateSheet = Worksheets("Dates")
    Dim dateDates As Range
    Set dateDates = dateSheet.Range("Dates[Date]")
    Set accountSheet = Worksheets("Accounts")
    Dim accountIds As Range
    Set accountIds = accountSheet.Range("Accounts[Id]")
    Dim result As Boolean

Dim creates a variable and Set will assign a value to the variable. dateSheet represents a worksheet of the excel workbook. In the next row we say we need the worksheet whose name is "Dates". So you know if you change the name of the worksheet (e.g., you localize it) then this macro should be modified  as well. 

Next we create a Range which is a bunch of excel cells. dateDates corresponds to the Date column of the Dates table. Notice that "Dates" is the name of the sheet but also the name of a table. Also notice that the sheet where the table can be found had to be also provided. 

Next the accountSheet and the accountIds range is created. In the naming of a column table the first part refers to the table, the second to the column. So, now we are talking about the Id column of the account table. At the end, a Boolean variable is created.

    Dim parametersSheet As Worksheet
    Set parametersSheet = Worksheets("Parameters")
    Dim baseCurrencyId As Integer
    baseCurrencyId = parametersSheet.Range("Parameters[Base currency Id]").Value2
    
    Dim AccountBalancesSheet As Worksheet
    Set AccountBalancesSheet = Worksheets("AccountBalances")
    
    Dim accountBalancesIdRange As Range
    Set accountBalancesIdRange = AccountBalancesSheet.Range("AccountBalances[Id]")
    Dim accountBalancesDateIdRange As Range
    Set accountBalancesDateIdRange = AccountBalancesSheet.Range("AccountBalances[Date Id]")
    Dim accountBalancesAccountIdRange As Range
    Set accountBalancesAccountIdRange = AccountBalancesSheet.Range("AccountBalances[Account Id]")

The parametersSheet has no novelty but then an integer is created, baseCurrencyId. We create a range for a column as before but then we use the Value2 property to give back the value of a cell. The parameters table contains a single row, so it is unambiguous which cell we mean. The rest of the code creates variables which will be used later.

    'based on Account.Id
    Dim typeIdOffset As Integer
    typeIdOffset = 2
    Dim openingDateOffset As Integer
    openingDateOffset = 14
    Dim closingDateOffset As Integer
    closingDateOffset = 15

The first row is a comment. Next we create integer constants about the Account table. For example, the openingDateOffset specifies the column position of the opening date of an account. If you reorder the columns in the Account table then these constants must be also modified.

    'Delete old values
    accountBalancesIdRange.Clear
    accountBalancesDateIdRange.Clear
    accountBalancesAccountIdRange.Clear

Once we have needed ranges, we can delete the old values from the AccountBalances table.

    'Calculate numberOfRows
    numberOfRows = 0
    For Each validDate In dateDates
        For Each accountId In accountIds
            If accountId.Offset(0, typeIdOffset).Value2 = 1 Then 'if Asset account
                result = IsDateInPeriod(validDate, accountId.Offset(0, openingDateOffset), accountId.Offset(0, closingDateOffset))
                If result Then
                    numberOfRows = numberOfRows + 1
                End If
            End If
        Next accountId
    Next validDate

The new size of the AccountBalances table is calculated here. We loop through each date and each account with a For Each loop. accountId is the cell examined at the moment, the Offset will move us to the typeId column of the same row, Value2 will get the value from this cell. If 1 is found which means Asset account (and not Category) then we can go on. The IsDateInPeriod method checks if the selected date, validDate, is inside the [opening date; closing date] period. If it is then we increase the numberOfRows variable by one.

    'Resize the table (does not delete if the table is decreased)
    Dim AccountBalances As ListObject
    Set AccountBalances = AccountBalancesSheet.ListObjects("AccountBalances")
    If AccountBalances.Range.Rows.Count - 2 <= numberOfRows Then
        correctedNumberOfRows = numberOfRows + 2
    Else
        correctedNumberOfRows = numberOfRows + 1
    End If
    AccountBalances.Resize AccountBalances.Range.Resize(correctedNumberOfRows)

The AccountBalances table is defined here with the ListObjects method. According to my experience it is not enough to use the Resize method with the numberOfRows variable but you have to modify this variable depending on increase or decrease. The last row uses the Resize method twice because first the range of the table has to be resized then the table itself based on the new range.

    'Write new values
    actualRow = 1
    For Each validDate In dateDates
        For Each accountId In accountIds
            If accountId.Offset(0, 2).Value2 = 1 Then 'if Asset account
                result = IsDateInPeriod(validDate, accountId.Offset(0, openingDateOffset), accountId.Offset(0, closingDateOffset)) 'is account alive
                If result Then
                    dateId = validDate.Offset(0, -1)
                    accountBalancesIdRange.Cells(actualRow, 1) = actualRow
                    accountBalancesDateIdRange.Cells(actualRow, 1) = dateId
                    accountBalancesAccountIdRange.Cells(actualRow, 1) = accountId
                    actualRow = actualRow + 1
                End If
            End If
        Next accountId
    Next validDate
End Sub

This code is very similar to the size calculation. The difference is that inside the second loop the dateId is determined. Then the actualRow, the dateId and the accountId is set. accountBalancesDateIdRange represents a column, the Cell method selects the actual row in that column. 

Public Function IsDateInPeriod(ByVal actDate As Date, ByVal openingDate As Date, ByVal closingDate As Date) As Boolean
    'check if the validDate is in ]opening date; closingDate[
    If openingDate < actDate And (actDate <= closingDate Or closingDate = 0) Then
        IsDateInPeriod = True
    Else
        IsDateInPeriod = False
    End If
End Function

IsDateInPeriod is a function which returns true if the first parameter is between the next two parameters. If the third parameter is 0 it means the account is not closed which must be taken into account.