On 2024-05-29 04:39 PM, Peter Cuthbert via gnucash-user wrote:
The dates associated with the reconciled/not reconciled flag struck me as an easy way to do a roll back without using the backups. Something along the lines of "if date = [specified reconciliation date] then flag = n" (I have no idea what language is used by the developers. ... So where am I with my data? I have corrected the £10k which was 'swopped' and then gone after the error schrapnel. Some of that I have found and corrected but eventually I had to succumb to a balancing 'fudge it' figure. With that in place the rest of the reconciliation was correct to the penny.
To you and anyone else facing this issue, you can run a transaction report that shows you a history of of the account with reconciled dates for every transaction.
Reports > Transaction Report
Options
Accounts: Choose your checking account
Display:
Check Reconciled Date
Check Date Entered (may be useful, so why not)
Amount: Single column
General:
Start: BEFORE YOUR FIRST TRANSACTION EVER - I pick Jan 1 2000
End: Today
Sorting:
Primary key: Reconciled Status, Ascending
Uncheck Primary subtotal
Secondary key: Reconciled Date, Ascending (sort doesn't work)
Secondary subtotal for date: NONE
Now run the report. It doesn't get the sort by reconciled date quite
right, but it might be close.
At this point you need to put the report in a spreadsheet program. I copy and paste, but there are many methods people have shared on the list.
See attached for an example of what I describe below. It is very important that the start date of your report is before the first transaction you have in the account.
In the spreadsheet program, paste and unmerge cells, then: 1) Freeze panes so the column labels at the top don't move 2) Set up a filter across all columns.3) Use the filter dropdowns to sort by reconciled date. Make sure the transactions with no date are at the bottom and that the reconciled transactions are with reconciled dates oldest to newest. 4) Make a formula in a column to the right of your data to make your spreadsheet program recognize the numbers. 5) Make a formula in a column to the right of your data to do a running subtotal. (Something like =SUM(I$1:I2) and drag it down, with "$" making a part of the formula sticky.)
Now you can quickly look at the reconciled balance as of the end of each reconciliation and see if it matches your bank statement. Once you find the first one that doesn't work (or the last one that works), you can look at your bank statements and see what transactions are missing from the reconciled list in GnuCash. Look for those missing transactions in the unreconciled ones at the end of the list.
I would suggest fixing transactions on the spreadsheet report to get things working and highlight all my changes in bright yellow. Once everything is making sense in the spreadsheet, you can make the changes in GnuCash.
I have this report saved as Transaction Report -> Rec Report Details
GnuCash Sample Reconciliation.xlsx
Description: MS-Excel 2007 spreadsheet
_______________________________________________ gnucash-user mailing list gnucash-user@gnucash.org To update your subscription preferences or to unsubscribe: https://lists.gnucash.org/mailman/listinfo/gnucash-user ----- Please remember to CC this list on all your replies. You can do this by using Reply-To-List or Reply-All.
