[GNC] Reconciliation Roll Back (Peter Cuthbert)

[NoobAlice]

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
-------------- next part --------------
A non-text attachment was scrubbed...
Name: GnuCash Sample Reconciliation.xlsx
Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Size: 18219 bytes
Desc: not available
URL: <http://lists.gnucash.org/pipermail/gnucash-user/attachments/20240529/1db9a2c8/attachment-0001.xlsx>