Consider a populated bank account. Reconciliation at statement date D should ignore any splits dated after date D. Test case: select a populated account whereby *ALL* splits are reconciled. Choose Action > Reconcile, dated sometime in the past. Note the suggested Ending Balance is correctly preset to register balance as of statement date. Click OK to open the Reconcile Window -- notice the Starting and Reconciled Balances both report the account's current balances instead. This means, although all the splits are fully reconciled, we can never complete reconciliation because the Difference will never be $0.00.
Looking through window-reconcile.c it seems only starting balance needs amending.
Counter test case: * select a populated account whereby recent splits are not reconciled * reconcile should happen at say 2020-01-31 * one transaction that is part of the bank account statement is dated 2020-02-01 So while it's technically after statement date you do want to include it in the reconciliation or the reconciliation won't match. I'm not making this up. Belgian banks do this :( How do you envision dealing with this ?
Not quite sure I follow, but I've refined my understanding of this bug. The Reconciliation Window *starting* balance calculator needs to ignore splits after statement date. Test with my PR 660?
Ok, that's not quite what the title or original post suggested. I didn't get your comment 1 which I why I posted a counter example. However if that means you can still select splits after reconciliation date, I'm fine with the fix.
Sure. A test of PR 660 would be useful too. If I follow your counter-case, I understood that reconciliation using a statement dated 2020-01-31, whose 'closing-balance' aka 'balance c/f' included a txn dated 2020-02-1. Makes no sense at all. Usually reconciliation is as follows: 2020-01-01 Balance b/f $1000 2020-01-05 Deposit $200 $1200 2020-01-07 Withdraw $100 $1100 2020-01-31 Balance c/f $1100 So, 2020-02-01 doesn't belong in this statement.
I never said Belgian banks are smart :( It has to do with a difference in date of transfer request vs date of transfer valuation. I can initiate a money transfer on 2020-01-31 but due to how banks work it may only be valuated on 2020-02-01. However that transaction will still appear on my bank account statement of 2020-01-31. A similar situation may occur when you do bank transfers between your own bank accounts at different banks. The date the money leaves one account may not be the same as the date it arrives at the other account. If you model this in gnucash as only one transaction you have to pick a date. Either the date the money leaves the first account or the date the money arrives at the second. Regardless of the choice it should not prohibit successful reconciliation. Your proposal would make it impossible to reconcile the first bank account if you choose the later date for the transaction.
The whole point of reconciliation is to either prove that the bank statement is correct or to highlight discrepancies. If some banks use "Transaction Initiation Date" instead of "Transaction Clear Date" as their criterion for inclusion in a statement but then show the "Clear Date" instead on the same statement as Geert suggests, the GnuCash reconciliation process should be adaptable to that per his Comment 4. Actually, since this bug report is focussed on the starting balance, shouldn't the starting balance calculation ignore all transaction splits dated after the previous reconciliation closing date unless they are already reconciled as of that previous reconciliation, but not reconciled in subsequent reconciliations if any exist?
Chris, the reconcile code's precondition is that you are reconciling the statement immediately following the one that you last reconciled and that the reconciled balance is therefore the current statement's starting balance. The reconcile process is to mark reconciled the cleared transactions so that the new reconciled balance matches the closing balance on the statement. The reconciled balance is arrived at by traversing the account's split list and summing up all of the splits marked reconciled. That's stored on each split in memory but isn't saved: Only the reconciled flag and the date it was set are. To reconstruct a reconcile from a statement reconciled in the past GnuCash would need to know the two reconciliation dates of interest--perhaps by finding the first reconciliation before the provided closing date for the opening balance and the next one for the closing balance. The two balances would need to be calculated by traversal instead of simply looked up as they are now. The split display would show all of the splits reconciled on the second date along with all of the splits posted in the period and all unreconciled splits with some way of distinguishing between the reconciled-this-statement and reconciled later splits.
John: I appreciate the current workflow means successive Reconciliation should always be later than the previous one; my fix would mean that it is possible to reconcile a previous statement. This means I can bisect the account and hunt down an errant transaction. This is *especially* useful when I've amended the Description of an old transaction to add harmless information, and suddenly reconciliation becomes very difficult.... The workflow dictates that I must choose the last bank statement to rereconcile but I'd prefer reconcile an old one. Geert's weird bank still doesn't make sense... I'd be keen for example data and statements and test with PR 660.
No, your fix doesn't do what you think it does, see my review. As for a de-reconciled transaction, open the present window. Look at the top of the split lists and check if there are any old ones. If there are and the starting balance on the dialog doesn't match the one on your statement that tells you that at least some of those old transactions got de-reconciled and give you a clue about in what statement to start looking for their clearing the bank. Open the Reconcile Info dialog and change the closing balance to the *starting* balance on your statement, then mark as reconciled the old transactions until the balances match as usual. Finish that reconcile and start a new one for the current statement.
Date Desc Amount REC Running Reconciled Reconcile_Date 01-Jan Bal bf $100 $100 05-Jan Income $20 n $120 $100 05-Feb Income $30 n $150 $100 05-Mar Income $70 n $220 $100 Then I receive the 31-Jan bank statement on 6-Feb, ending balance is $120. All good. Date Desc Amount REC Running Reconciled Reconcile_Date 01-Jan Bal bf $100 $100 05-Jan Income $20 y $120 $120 06-Feb 05-Feb Income $30 n $150 $120 05-Mar Income $70 n $220 $120 Then I receive the 28-Feb bank statement on 8 Mar, ending balance is $150. All good. Date Desc Amount REC Running Reconciled 01-Jan Bal bf $100 $100 05-Jan Income $20 y $120 $120 06-Feb 05-Feb Income $30 y $150 $150 08-Mar 05-Mar Income $70 n $220 $150 Then I decide to amend the 05-Jan income description: it is now unreconciled. Boo. Date Desc Amount REC Running Reconciled 01-Jan Bal bf $100 $100 05-Jan Wages $20 n $120 $100 05-Feb Income $30 y $150 $130 08-Mar 05-Mar Income $70 n $220 $130 To fix it, I attempt to rerun the 31-Jan reconciliation, ending balance is $120. Action > Reconcile, set statement date as 31-jan, ending balance $110. Click OK. In the consequent reconciliation window, it shows transactions 05-jan and 05-mar only; the 05-feb one is hidden because it's already reconciled. Currently the Reconcile Window Starting balance is $130 (i.e. account's reconciled balance) and my fix will change it to $100. See screenshots. I do not know how to make it clearer. Without my fix, it is impossible to re-reconcile on statement 31-jan because the difference of $20 cannot be resolved via clicking in the Reconcile Window.
Created attachment 373598 [details] before PR 660
Created attachment 373599 [details] after PR 660
Created attachment 373600 [details] datafile
The only remaining issue is the fact that *some* banks may choose to prematurely affect the running balance with a transaction dated after the statement date. This is so wrong on many levels, but I disgress. I suspect in this case, the monthly reconciliation will still be possible, but a re-reconciliation may be difficult. If bug 797084 is fixed then I won't need to re-reconcile past statements and I'll let this bug drop.
> To fix it, I attempt to rerun the 31-Jan reconciliation, ending > balance is $120. Action > Reconcile, set statement date as 31-jan, > ending balance $110. Click OK. In the consequent reconciliation > window, it shows transactions 05-jan and 05-mar only; the 05-feb one > is hidden because it's already reconciled. Currently the Reconcile > Window Starting balance is $130 (i.e. account's reconciled balance) > and my fix will change it to $100. See screenshots. Oops: typo above -- where "ending balance $110" please read "ending balance $120"
(In reply to Christopher Lam from comment #15) > The only remaining issue is the fact that *some* banks may choose to > prematurely affect the running balance with a transaction dated after the > statement date. Rereading -- should read "some banks may choose to show transaction on statement yet running balance isn't updated until the next statement". I suspect reconciliation will still be ok. Also in the PR review I am not sure the split->reconciled-date actually matters; I hope comment 11 explains why.
What you're missing is that you have to consider *all possible use cases*, not just your own extremely narrow one. PR660 appears to fix this particular instance but fails more generally for the reasons I have already detailed. Add 9 more transactions to each of January, February, and March and add April, also with 10 transactions. For each month pick three transactions that don't clear the bank for 6 weeks. Reconcile every month accordingly. When you're done you'll have some unreconciled March and April transactions while all of the January and February transactions will be reconciled. If you unreconcile one of the cleared-in-January transactions and try to re-create the January statement you'll fail even with PR660 because the assumption that all transactions posted before the statement date are reconciled as of the statement date fails. Before you start screaming that this is an unrealistic use-case, recall that paper checks are still a thing and that they can take several weeks to clear the bank.
jralls: thank you for your overview. I've amended the date test in xaccAccountGetReconciledBalanceAsOfDate to reconcile_date and it seems to work for me, even accounting for 6 week delayed checks, but not sure it will be correct in all circumstances. Pasted the Transaction Report (augmented to show reconciled_date). (BTW I didn't realize reconciled_date means the statement_date at reconciliation; always thought it was the reconciliation date hence my misunderstanding in comment 11 above) Transaction Report From 01/01/20 to 31/12/20 Date Reconciled Date Description Amount Running Balance Bank: Balance b/f $100.00 05/01/20 31/01/20 wages amended $20.00 $120.00 07/01/20 29/02/20 write a check -$15.00 $105.00 08/01/20 29/02/20 write a check -$23.00 $82.00 09/01/20 29/02/20 write a check -$10.00 $72.00 05/02/20 29/02/20 income $30.00 $102.00 06/02/20 31/03/20 write a check -$4.00 $98.00 07/02/20 31/03/20 write a check -$9.00 $89.00 08/02/20 31/03/20 write a check -$13.00 $76.00 05/03/20 31/03/20 income $70.00 $146.00 06/03/20 01/01/70 write a check -$6.00 $140.00 07/03/20 01/01/70 write a check -$9.00 $131.00 08/03/20 01/01/70 write a check -$18.00 $113.00 Total For Bank $13.00 Grand Total $13.00 Conclusion: I'm not 100% sure that the issue is currently solvable with the data structures available. I'll leave this issue open for now.
That works because the splits are reconciled in the same order that they're created. If you were to change one of the January checks to reconcile on 31 March you'd still get $102 for a reconciled balance on 1 March because xaccAccountRecomputeBalances will have included that check amount (it *is* reconciled, just not by 1 March) in the 5 February split's reconciled balance. I think all that's needed is posted_date, reconcile_date, and amount. The key is that xaccAccountRecomputeBalances currently works on a single sort of the splits by posted_date and num/action (but the latter isn't really relevant here, it just sorts the splits within a particular day). To find the reconciled balance on a particular day we need to order the split list first by reconcile_date and then by posted_date and num/action, compute the running balance, stopping on the selected date.
(In reply to John Ralls from comment #20) > I think all that's needed is posted_date, reconcile_date, and amount. The > key is that xaccAccountRecomputeBalances currently works on a single sort of > the splits by posted_date and num/action (but the latter isn't really > relevant here, it just sorts the splits within a particular day). To find > the reconciled balance on a particular day we need to order the split list > first by reconcile_date and then by posted_date and num/action, compute the > running balance, stopping on the selected date. I've sidestepped it in the PR by scanning account's splits in a different method -- scan all splits, find the last split whose reconcile date is less than statement date, and retrieving its Reconciled Balance. This means we can't reuse same code.
I saw that while working on comment 20. It doesn't change the way the reconciled balance is calculated, only which split's reconciled balance is used. It fails in the case outlined in the first paragraph of comment 20. One way to get closer--I'm not sure it's completely right--would be to order the splits by reconciled date, posted date, and num/action and calculate a running reconciled balance from that series. With that your last split reconciled on or before a selected date would have the right balance in the comment 20 scenario. I'm not sure that it would work for a more complex case.
I think sorting in C is too difficult. How about gnc_numeric_add_fixed successive reconciled split->amount ?
Summing successive reconciled split->amount is what it does now, in date_posted-num/action-date_entered order. Sorting a GList is trivial: static int compare_reconciled_date(void* a, void* b) { Split* sa = GNC_SPLIT(a); Split* sb = GNC_SPLIT(b); if (!sa || !sb) return sa != NULL ? 1 : sb != 0 ? -1 : 0; return sa->date_reconciled < sb->date_reconciled ? -1 : sa->date_reconciled > sb->date_reconciled ? 1 : sa->parent->date_posted < sb->parent->date_posted ? -1 : sa->parent->date_posted > sb->parent->date_posted ? 1 : strncmp(sa->parent->num, sb->parent->num); } /* later... */ GList* sorted_list = g_list_sort(split_list, (GCompareFunc)compare_reconciled_date); You might want to do that on a copy so that you don't need to rerun xaccAccountReorder() when you're done. I left off handling the the num/split-action pref and the final fallback for split->parent->date_entered. If you're interested only in the balance at the end of each reconcile rather than a running reconciled balance then you don't need the fallback sorts at all, compare_reconciled_date can just return 0 if sa->date_reconciled == sb->date_reconciled. I suppose you could instead create a hash table of reconciled balances by reconciled date, but getting that right would be a lot more complicated than sorting the list by date reconciled.
Done as such in the PR. A copy was made of reconciled splits only. Seems to work for me. The bug fix commit is a one liner so can be easily reverted if it turns out to be incorrect.
Fixed in https://github.com/Gnucash/gnucash/pull/660
Chris, usually we close the bug after committing to maint or master.
Frank, he did: https://github.com/Gnucash/gnucash/commit/f182d9f9121130b49320e82810a924b73d69a6b9
