Need faster method to find / save correct data from other table.

[garyj]

Hello,

I am working on a non-profit accounting program - and my last bit was to create a list of year end adjustments to close (zero) income and expense accounts with the balance going to Retained Earnings. What follows is the method I used, but it is somewhat slow... perhaps 2 lines on the General Ledger (GL) per second. I am wondering if there is a faster method?

On my Chart of Accounts (CoA) I have a year-to-date total of debits and credits for each budget area. I use the Filter command to find all debits in the GL, and the same line subtracts all credits found in the GL. The HidStartLYr and HidEndLYr come from a hidden sheet that defines the society's start and end fiscal period.

dynamic formula in last column of the CoA - [Note: Budget Area is a column in that Table]:
=SUM(FILTER(TableGL[Debit],(TableGL[Budget Area]=[@[Budget Area]])*(TableGL[Date]>=HidStartLYr)*(TableGL[Date]<=HidEndLYr),0))-SUM(FILTER(TableGL[Credit],(TableGL[Budget Area]=[@[Budget Area]])*(TableGL[Date]>=HidStartLYr)*(TableGL[Date]<=HidEndLYr),0))

So for the VBA, I chose to find all Budget Areas (for all rows within the CoA Table) where the account type is "Income" or "Expense", where the Account Status is "Active", and the Total of the account at year end is not zero. The VBA then saves those items in the GL using Global variables defined for saving entries to the GL. The total difference at the end is put to Retained Earnings.

Here is the code if you can make sense of it.

Sub DoYearEnd()
    Dim wks As Worksheet, wkGL
    Set wks = Sheet1    'CoA sheet
    Set wkGL = Sheet5  'GL sheet
    Dim rw As ListRow
    Dim rwGL As ListObject
    Set rwGL = wkGL.ListObjects("TableGL")
    Dim cl As Range
    Dim myType As String, myActive
    Dim myTBal As Double, myTotal
    Dim LastRwGL As Integer
    GLTNo = Application.WorksheetFunction.Max(Range("GLTransNo")) + 1    'use the next available Transaction No for all entries for this Year End Adjustment
    GLIt = "Year End Adjustments"                                                              'set Global variable for Item for all entries in Transaction
    GLDt = Range("HidEndLYr").Value                                                          'set Global variable for Date for all entries in Transaction
    For Each rw In wks.ListObjects("Table18").ListRows                                 'Loop through all rows of Table18 (CoA table. (I just noticed I didn't set this as a ListObject)
        myType = wks.Cells(rw.Range.Row, Range("CATypeCol").Column).Value
        myActive = wks.Cells(rw.Range.Row, Range("CAActiveCol").Column).Value
        If myType = "Income" Or myType = "Expense" And myActive = "Yes" Then      'check for type to be income or expense and account to be active. if so then...
            GLBA = wks.Cells(rw.Range.Row, Range("CAAcctCol").Column).Value          'set that lines Global var. for Budget Area
            myTBal = wks.Cells(rw.Range.Row, Range("CATrialBalCol").Column).Value   'set that lines Global var. for the total year end balance
            If myTBal < 0 Then GLDr = -myTBal: GLCr = Empty                                   'if the balance is negative then it is a credit balance and must be a debit to zero
            If myTBal > 0 Then GLCr = myTBal: GLDr = Empty                                    'if balance is positive then it is a debit balance and must be a credit to zero
            If myTBal = 0 Then GoTo JmpOver                                                            'if balance is zero, then there is no need to zero, so jump the next line
            GoSub SaveYEData                                                                                  'send to SaveYEData Subroutine that writes to Gen Ledger
            End If
JmpOver:
    Next rw                                                                                                          'get next row of CoA
    'Next line formula: After all row entries, get difference  of Debit and Credit totals of GL and write to Retained Earnings
    myTotal = Sheet5.ListObjects("TableGL").TotalsRowRange(Sheet5.ListObjects("TableGL").ListColumns("Debit").Index).Value - _
        Sheet5.ListObjects("TableGL").TotalsRowRange(Sheet5.ListObjects("TableGL").ListColumns("Credit").Index).Value
    If myTotal > 0 Then
        GLDr = myTotal: GLCr = 0
        Else: GLCr = myTotal: GLDr = 0
        End If
        GLBA = "Retained Earnings"
        GoSub SaveYEData
    Set wks = Nothing
    Set wkGL = Nothing
    Set rwGL = Nothing
    Exit Sub
SaveYEData:                                                                                                          'Save to GJ 
    rwGL.ListRows.Add                                                                                             'Add new row
    LastRwGL = rwGL.ListRows.Count + wkGL.Range("GLTNo").Row - 1                       'Get last row (LastRwGL) for use in saving next lines
    If GLTNo <> "" Then Cells(LastRwGL, Range("GLTNo").Column) = GLTNo                'Save Transaction No
    If Not IsEmpty(GLDt) Then Cells(LastRwGL, Range("GLDt").Column) = GLDt           'Save Date
    If GLBA <> "" Then Cells(LastRwGL, Range("GLBA").Column) = GLBA                     'Save Budget Area
    If GLIt <> "" Then Cells(LastRwGL, Range("GLIt").Column) = GLIt                         'Save Item
    If Not IsEmpty(GLDr) Then Cells(LastRwGL, Range("GLDr").Column) = GLDr           'If applicable save Debit amount
    If Not IsEmpty(GLCr) Then Cells(LastRwGL, Range("GLCr").Column) = GLCr            'If applicable save Credit amount
    Return                                                                                                                'Return from subroutine
End Sub

Thanks for any pointers on how to speed this up. I only had about 20 items - but some saves could be much longer if there are 100 or more entries.
Gary J

[June7]

Why are you using Excel as a database anyway? Why even build an accounting program when there are OTS applications readily available? Quickbooks is first one comes to mind.

[garyj]

Quickbooks, and others, do not provide an inexpensive option for small non-profits. That and it is a far larger program than many non-profits need.
Microsoft on the other hand does provide free non-profit packages for non-profits that align with their non-profit program.
As to Access, which doesn't always come with the non-profit packages, I have found it to be more complicated for the user and more difficult to create queries. The Excel language is easy, and most of the programming for other sheets I can do by filtering a single General Ledger. i.e. Filter GL for all entries re Bank, gives me a total on what is in the bank.

I don't know.. lots has changed - and I wouldn't even consider it if it weren't for the dynamic functions in Excel. Perhaps my work will only be an exercise in my own learning?

[jindon]

Thanks for any pointers on how to speed this up. I only had about 20 items - but some saves could be much longer if there are 100 or more entries.

Better upload a sample workbook showing what you have and what you want to have after the code...

[June7]

I used Quickbooks desktop for my Little League Baseball nonprofit. Far cheaper than the cost of time and effort to build from scratch. So what if there are unused features.

Accounting is accounting, doesn't really matter if it is non-profit or business. Possibly Northwind template could be adapted for a non-profit. Really don't see how building queries in Access is "more difficult". And the complication depends on the level of "user-friendly" features designed into GUI.

A large Excel file can become sluggish to the point of non-functional. I also urge you provide file for analysis.

Which "dynamic functions" draw you to Excel?

AFAIK, non-profit does not have "retained earnings" because, well, it is a non-profit. Seems term for a non-profit is "accumulated funds".

[georgiboy]

From the information provided, the best I could say is that your dynamic array formula using two FILTER functions could be quite resource hungry depending on the volume of data.

As adding data to a table will likely recalculate all of the formulas in the workbook and given that the workbook will have calculated before the VBA is run. Maybe switching off calculation within the VBA would improve performance, this way it would not calculate for each item that data is added to the table.

Sub test()
    Application.Calculation = xlCalculationManual
    
    '''Your code
    
    Application.Calculation = xlCalculationAutomatic
End Sub

[garyj]

June, thanks for all that you said. I have well considered all that you said many times before. I also appreciate the value of what you are saying, though it discourages me from doing what I enjoy. I am sure you have more experience in both Access and Excel than do I. I agree that Quickbooks is a good option. I agree that it is less time and effort than to build from scratch. I agree that accounting is accounting. I agree that the title "retained earnings" suggests profit, though, in my last job working for a large non-profit, the accountant used that term anyway.

Anyway, I love coding. I love inventing ideas that make things simpler. I love the challenge. I am almost 65, and never took it in school. I've been doing code on and off for years - never as a job. I've completed several databases having various purposes. I did one as a challenge for a mayor for his campaign program. They used it... but someone else found it, stole my code to create a variation, and sold it to other campaigners. I had used an older version of Access and thus the older versions of queries. I was considering taking on a new challenge to learn SQL and purchased a self study book. However, I saw a decline in the use of Access. It was shortly after that Excel began releasing new ideas with their dynamic functions. I saw a way to basically have a single data file for all transactions and to use other worksheets to collect and deposit that data to the GL, or to filter the data for reports and account checks, etc. I can do it mostly with Filter, Subtotal, ChooseCols, Sequence, Sort, VStack, and Xlookup, plus the data validation and very little VBA - though there is some.

Sorry, I choose not provide a file for analysis for several reasons, some of which are:

1. I found a cool video (https://www.youtube.com/watch?v=8fdsy1vSFJs) which showed a comparison of methods by time. The faster way is to read the data as an array and then save the array to the table. Perhaps this vid will help others?
2. I tried the idea below to turn the autocalculate function off until after writing to the table - and it was much faster.
3. It is a lot of work to reduce the privacy data and send the file, and I think the first two reasons are enough to cause me to mark this thread solved.

Thanks again
Gary

I used Quickbooks desktop for my Little League Baseball nonprofit. Far cheaper than the cost of time and effort to build from scratch. So what if there are unused features.

Accounting is accounting, doesn't really matter if it is non-profit or business. Possibly Northwind template could be adapted for a non-profit. Really don't see how building queries in Access is "more difficult". And the complication depends on the level of "user-friendly" features designed into GUI.

A large Excel file can become sluggish to the point of non-functional. I also urge you provide file for analysis.

Which "dynamic functions" draw you to Excel?

AFAIK, non-profit does not have "retained earnings" because, well, it is a non-profit. Seems term for a non-profit is "accumulated funds".

[garyj]

From the information provided, the best I could say is that your dynamic array formula using two FILTER functions could be quite resource hungry depending on the volume of data.

As adding data to a table will likely recalculate all of the formulas in the workbook and given that the workbook will have calculated before the VBA is run. Maybe switching off calculation within the VBA would improve performance, this way it would not calculate for each item that data is added to the table.

Sub test()
    Application.Calculation = xlCalculationManual
    
    '''Your code
    
    Application.Calculation = xlCalculationAutomatic
End Sub

Thanks so much for this. It worked. I am going to use your idea, georgiboy, as well as see if I can read to an array and save to the GL that way. Together those changes should do wonders. Thanks again.

Gary

[garyj]

For any interested, turning off the autocalculation decreased time incredibly, and using the array to read and write decreased time by another 50%.
In other words, what took 8.5 seconds at the beginning now took less than a third of a second.

[Aussiebear]

Good to hear.