Re: [GNC-dev] About 3.9 and reconciliation balances
Thanks for inquiring. I've hinted at top-level concepts and ideas in my thread "reconciliation concept from top view" https://lists.gnucash.org/pipermail/gnucash-devel/2020-April/044863.html and in particular the relationship between a "shared truth" and the GnuCash "reconciliation" process. I am not aware of any must-fix/change now/bug or die situation with GnuCash reconciliation. Instead, it seems to just be an envogue topic. Someone got excited and so there's chatter and coding on it. Nothing wrong with that...do what makes one happy. :-) Caveat -- the following ideas haven't yet due diligence. I volunteer it only as you inquired. I need more time before deep review though I'm very open to discussion/input. Some of the ideas I have on shared truths and reconciliation might eliminate data duplication, increase reconciliation integrity, increase auditable records, etc. I think the envogue conversations around reconcilation dates/autofixes/future/past are minimized. And, some auditing wants from the German users (that I read months ago) become possible. For example, one concept I'm noodling is the idea of a shared truth called a "reconciliation certificate". Referencing my "top view" thread and pseudocode there and here: vector> facts; // e.g. vector from the GnuCash user's data entry, vector from the bank statement, vector from auditor, etc. const reconciliation_certificate = reconciliation_process(facts); - reconciliation_certificate is unchangeable; its input was hashed and therefore later changes in facts are detected; digitally signed; digital timestamps like RFC 3161; etc. - reconciliation_certificate can be exported to a file to be given to a 3rd party, archived, etc. An audit can use that exported cert to validate the books haven't changed. These external certs (or the internal copies of them) could be used to validate the integrity of gnucash data itself to detect changes to transactions due to gnucash bugs, bitrot, HD failures, etc. - All the tech needed to do this is stable and readily available today. I have experience coding C++, node, and python with them all. While the underlying math is complex, the coding with APIs is generally straight-forward. - reconciliation_certificate is an entity in itself. GnuCash would have one for each time reconciliation_process() was successful. So it is possible to re-reconcile by simply making a new reconciliation_certificate with reconciliation_process(facts). Since each reconciliation_certificate is saved, now we have an ongoing record of current and past reconciliations. - reconciliation_process() has validation in it. Validation on the input facts, validation on the equality/compatibility of the facts to each other, validation with a pluggable set of rules by country's laws/regs, GAAP, IFRS, etc. - a transaction and a split both have no attribute "reconciled date". Instead, a split has/not a pointer to a reconciliation certificate. It is that certificate which has the reconciliation timestamp. - This is all deep in implementation. How it is exposed to end-users in the GnuCash UI could be seamless and uneventful. Yet, new features/UI can be built with capabilities granted by a fresh implementation. With more diligence, ideas with be refined or eliminated...more coherent. I want people to be able to understand my thinking even if they disagree with it. I'm approaching "reconciliation" from a perspective of what are needs, what fresh tech is available to meet them, and how do those fit together. Trying to avoid the good-enough, legacy, or long-timers pitfalls while at the same time eliminate ideas which have already been tried elsewhere (including outside GnuCash). Add in conversations on feature/benefit/effort given the existing GnuCash codebase. Given my workload, the ideas I already have, diligence and the discussions needed to answer "is this wanted/needed/useful?"...I think any potentially-related coding is most likely 2021 or later. I hope others will involve themselves, so I don't consider this a solitary effort by me. :-) --Dale On Sat, Apr 18, 2020 at 11:15 PM David Cousens wrote: > Dale, > > While that was the main thrust of the thread, it is inevitable that it will > bring attention to other issues so no real dramas. The advent of electronic > clearing almost immediately and OFX direct connect connections to at least > some banks (mine won't cooperate) is also changing the game. An OFX > directConnect query may have the status of a statement in some > circumstances > and an import in that can already trigger the reconciliation processand > reconcile in some circumstances as John described in another post. This is > still consistent with the manual reconciliation process. The discussion > did > encompass improvements to recording the reconciliation process, i.e a > reconciliation history if you like to help with automatic checking of the >
Re: [GNC-dev] About 3.9 and reconciliation balances
Dale, While that was the main thrust of the thread, it is inevitable that it will bring attention to other issues so no real dramas. The advent of electronic clearing almost immediately and OFX direct connect connections to at least some banks (mine won't cooperate) is also changing the game. An OFX directConnect query may have the status of a statement in some circumstances and an import in that can already trigger the reconciliation processand reconcile in some circumstances as John described in another post. This is still consistent with the manual reconciliation process. The discussion did encompass improvements to recording the reconciliation process, i.e a reconciliation history if you like to help with automatic checking of the account and Geert outlined an approach to that by including a statement data structure which links to the account being reconciled and has essential information about each statement which would make what I have in mind much easier down the track. I wasn't objecting to issues you raised but was more concerned that we were on the same page in terms of terminology. Not sure what an alternative approach to reconciliation would look like. As an accounting process, reconciliation is about checking your books records against those maintained by another entity and ensuring agreement. From an accounting perspective GnuCash's reconciliation process is already very good. How would you do it differently? David - David Cousens -- Sent from: http://gnucash.1415818.n4.nabble.com/GnuCash-Dev-f1435356.html ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: [GNC-dev] About 3.9 and reconciliation balances
Oops, I misunderstood the turn this thread took. My mistake. I don't have any particular comments on an autofix to clean XML files with inconsistent reconciliation dates and the current approach GnuCash has for reconciliation. My comments were for some future many months->years away in a later GnuCash that has an alternate approach to reconciliation. Sorry if I derailed anyone's reading. --Dale On Sat, Apr 18, 2020 at 12:24 AM David Cousens wrote: > Dale, > > I was referring specifically to the dates the bank puts on transactions > included in the statement for a specific period but not necessarily the > dates that may have been recorded by a user in GnuCash. The latter of > course > may be different and the reconciliation process deals with that. > > I disagree with you however about which date should be recorded as the > *reconciliation date* for a split to the specific account you are > reconciling for a transaction maked as 'y' (not 'c'). When you do a > reconciliation you are confirming that the transactions in your books agree > with the transactions in the statement up to the end date of the statement > are correct in the amounts recorded and that the balances recorded by the > bank and yourself at that date are in agreement. > > This is the date you are reconciled to not the date at which you entered > the > transaction into GnuCash or the date at which you posted the transaction > (the date at which the event which causes the transaction to be recorded > occurred). > > Both of these dates are already recorded in the Gnucash transaction data > structure and recording them as the reconciliation date on a split does not > really add any new information. > > David > > > > > > - > David Cousens > -- > Sent from: http://gnucash.1415818.n4.nabble.com/GnuCash-Dev-f1435356.html > ___ > gnucash-devel mailing list > gnucash-devel@gnucash.org > https://lists.gnucash.org/mailman/listinfo/gnucash-devel > ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: [GNC-dev] About 3.9 and reconciliation balances
Geert > From an implementation point of view I'd structure this slightly > differently: > 1. instead of adding an account property with a list of reconciliation > history, I would introduce a > new object, like a "statement" This object would have the fields you > mention before (date of > reconciliation, statement start date, statement starting balance, > statement end date) and some > more: > *statement id* - most bank statements has a unique number which may be > helpful to track > *statement ending balance* - particularly useful to verify manual > transaction entries. If you > explicitly enter a start and ending balance in addition to the > transactions themselves, amount > typos will be caught by the numbers no longer adding up. > *account" - the account this statement refers to. > > Lastly each split should get an additional field "statement id" referring > to the statement which > includes it. The split "reconciliation date" field would no longer be > necessary. That info is > encapsulated in the associated statement. > > This mapping would be much closer to the real world order of things: > * during reconciliation a split is matched to a line on a statement > * each split can be linked to only one statement > * in case of reconciliation trouble in the past, the extra statement > details make it easier to dig > up the related external source (there's a statement id in addition to a > reconciliation date). > * it is more clear which splits were reconciled together - they are tied > to the same statement, > where in the past there was only a reconciliation date, which may have > been wrong for various > reasons. I also agree that this is a good way to implemnet the idea. It meets what i had in mind for a reconciliation history much better. David - David Cousens -- Sent from: http://gnucash.1415818.n4.nabble.com/GnuCash-Dev-f1435356.html ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: [GNC-dev] About 3.9 and reconciliation balances
Thanks John for clarifying that. My take on that situation is that the statement start date is TODAY. The statement end date is TODAY and the reconciliation date for any included transactions is TODAY. That is obviously consistent with the manual reconciliation process. David - David Cousens -- Sent from: http://gnucash.1415818.n4.nabble.com/GnuCash-Dev-f1435356.html ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: [GNC-dev] About 3.9 and reconciliation balances
Adrien, I am referring specifically to setting the reconciliation date for the split you are reconciling. When a transaction is created in GnuCash both the date it was entered into Gnucash and the date it was posted are recorded in the transaction data structure and file. Only the posted date is normally displayed in the register. The posted date should of course be the date relevant to the event which you are recording in your books. The reconciliation date is separate from both of these and is part of the data structure for the split to the account being reconciled that is part of the transaction data It is currently set to the end date for the statement when the reconciliation flag for the split is set to 'y' by the reconciliation. During the import process when an existing transaction is updated from an imported record, it is set to 'c' (cleared) and TODAY, the date at which the importation occurred is recorded in the reconciliation date field. splits set to 'c' are not included in the starting balance calculation for a transaction, only those set to 'y'. WHen they are included in a reconciliation process, then they are set to 'y' and the reconciliation date field is set to the end date of the relevant statement. I believe the above is all kosher and the current situation only arises AFAIK when the user has inadvertently set the end date of a statement to a far future date as I did and then Chris's improvement in 3.9 prevents them from being included in the starting balance sum. My reference was only to the dates the bank records the transactions at in its statement not to any dates recorded for the transaction in GnuCash. I was being very pedantic in the interest of trying to make what I meant clear (but still didn't quite manage it). One problem in all these discussions is always making sure everyone is using the same terminology for the same conceptual structure/objects. I agree adjusting transactions may not have the date of the original transaction and may not necessarily fall within the period encompassing the original transaction at the date recorded in the book and could possibly have been recorded in the future relative to the end of statement date. AFAIK there is nothing which prevents a user from reconciling a future event to the account. These appear in the debits and credits panels in the reconciliation window even if their transaction posted date is after the end of statement date so they can be checked for inclusion in the relevant reconciliation process along with the original transaction they correct - hopefully to the value recorded in the statement. David - David Cousens -- Sent from: http://gnucash.1415818.n4.nabble.com/GnuCash-Dev-f1435356.html ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: [GNC-dev] About 3.9 and reconciliation balances
Dale, I was referring specifically to the dates the bank puts on transactions included in the statement for a specific period but not necessarily the dates that may have been recorded by a user in GnuCash. The latter of course may be different and the reconciliation process deals with that. I disagree with you however about which date should be recorded as the *reconciliation date* for a split to the specific account you are reconciling for a transaction maked as 'y' (not 'c'). When you do a reconciliation you are confirming that the transactions in your books agree with the transactions in the statement up to the end date of the statement are correct in the amounts recorded and that the balances recorded by the bank and yourself at that date are in agreement. This is the date you are reconciled to not the date at which you entered the transaction into GnuCash or the date at which you posted the transaction (the date at which the event which causes the transaction to be recorded occurred). Both of these dates are already recorded in the Gnucash transaction data structure and recording them as the reconciliation date on a split does not really add any new information. David - David Cousens -- Sent from: http://gnucash.1415818.n4.nabble.com/GnuCash-Dev-f1435356.html ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: [GNC-dev] About 3.9 and reconciliation balances
David, No, Gunter meant what he said. If the bank's response to a FinTS or OFX DirectConnect query includes a balance, GnuCash will pop a dialog telling you that and allow you to reconcile. If you reconcile it will skip the reconcile info dialog and open the reconcile window with the statement date set to now and the statement balance set to what the bank sent. This dance is separate from the matcher window dance, and the bank-sent-balance dialog pops up at the end of communication rather than waiting for the matcher window to complete. Regards, John Ralls > On Apr 16, 2020, at 8:45 PM, David Cousens wrote: > > hi Geert > > The first was really about directConnect OFX imports, not something I am all > that familiar with since banks here won't allow us access. Another user in > the discussion around Bug 797668 which I initially raised had mentioned > direct reconciliation in the import of OFX directly from the bank. I may > have misunderstood Gunter's comment that that procedure was starting the > reconcile procedure and automaticallly reconciling. I was trying to say > tthat if the bank provided enough information that matched the GnuCash > information, it may be possible to reconcile automatically but I think > Gunter was actually referring to the marking of the transactions as cleared > so it appeared checked when the reconcilaition was initiated. > > Agreed that the statement date is totally independent of the transaction > date. The dates in a statement will all fall between the end date of the > last statement +1 (the start date of the current statement) and the end date > of the current statement as the statement dates are inclusive. > > The importers as far as I can tell so far (OFX and CSV) don't set the > reconcile flag to 'y' only to 'c' and the reconcile date is set to TODAY in > the importing process. This shouldn't affect the manual reconcilaition > process though which should set the reconciliation date to the statement end > date when the flag is set to 'y'. > > I am currently trawling through the code to satisfy myself that I understand > fullywhat is happening and will update the documentation accordingly if the > update i did last year is misleading. > > Davdi > > > > - > David Cousens > -- > Sent from: http://gnucash.1415818.n4.nabble.com/GnuCash-Dev-f1435356.html > ___ > gnucash-devel mailing list > gnucash-devel@gnucash.org > https://lists.gnucash.org/mailman/listinfo/gnucash-devel ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: [GNC-dev] About 3.9 and reconciliation balances
+1 on your idea of the statement as a separate object. If a closing date is later realized to be erroneous, it only needs to be corrected in one place, not every transaction involved. And I hazard the guess that the UI that allows a user to edit a statement object data would be simpler and more straightforward than walking through every transaction looking for suspected bad dates and then making changes. Regards, Adrien > On Apr 15, 2020 w16d106, at 6:14 AM, Geert Janssens > wrote: > > I agree this is useful extra information and also what Christopher Lam has > been playing with. > > From an implementation point of view I'd structure this slightly differently: > 1. instead of adding an account property with a list of reconciliation > history, I would introduce a > new object, like a "statement" This object would have the fields you mention > before (date of > reconciliation, statement start date, statement starting balance, statement > end date) and some > more: > *statement id* - most bank statements has a unique number which may be > helpful to track > *statement ending balance* - particularly useful to verify manual transaction > entries. If you > explicitly enter a start and ending balance in addition to the transactions > themselves, amount > typos will be caught by the numbers no longer adding up. > *account" - the account this statement refers to. > > Lastly each split should get an additional field "statement id" referring to > the statement which > includes it. The split "reconciliation date" field would no longer be > necessary. That info is > encapsulated in the associated statement. > > This mapping would be much closer to the real world order of things: > * during reconciliation a split is matched to a line on a statement > * each split can be linked to only one statement > * in case of reconciliation trouble in the past, the extra statement details > make it easier to dig > up the related external source (there's a statement id in addition to a > reconciliation date). > * it is more clear which splits were reconciled together - they are tied to > the same statement, > where in the past there was only a reconciliation date, which may have been > wrong for various > reasons. > > Regards, > > Geert ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: [GNC-dev] About 3.9 and reconciliation balances
I concur. It is not unusual for a transaction to be made in one period and clear in the next, even in modern times. This is highly probable with issuing a paper check. Lower date bounds are going to be a problem. While I don’t think there is much case (though one user reported so) for a future date (beyond ’today’) there is a case for including a transaction dated past the statement date. If you need to enter a balancing or correcting transaction some users may prefer the date of that transaction be the day it was made, not ‘as of the closing date’. Sticklers for using correcting transactions (as opposed to editing transactions) are likely to prefer such dating. This date can very likely be more than ’closing +1’. Regards, Adrien > On Apr 17, 2020 w16d108, at 9:27 AM, Dale Phurrough via gnucash-devel > wrote: > > As an attempt for clarity, I don't globally agree with what David wrote > "The dates in a statement will all fall between the end date of the last > statement +1 (the start date of the current statement) and the end date of > the current statement as the statement dates are inclusive." > > I believe in the global (as in all scenarios all possibilities) that the > dates for transactions in a statement would be on or before the end date of > the current statement. However, such transactions could also be before the > end date of the last statement. I have countless examples of this where I > caused/made a payment before the end of month and that payment wasn't > booked by a bank until the next month. In this scenario, the date that one > wants in GnuCash might be the causation date or the booking date. > > I believe it is the user's choice of which date to save into GnuCash: > either the date the transaction was caused (e.g. data entry into GnuCash) > or the date the bank finally booked it. So the date should not be lower > bounded by the end date of the last statement. Instead, only upper bounded > by the end date of the current statement. > > Follow my thinking? > > --Dale ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: [GNC-dev] About 3.9 and reconciliation balances
As an attempt for clarity, I don't globally agree with what David wrote "The dates in a statement will all fall between the end date of the last statement +1 (the start date of the current statement) and the end date of the current statement as the statement dates are inclusive." I believe in the global (as in all scenarios all possibilities) that the dates for transactions in a statement would be on or before the end date of the current statement. However, such transactions could also be before the end date of the last statement. I have countless examples of this where I caused/made a payment before the end of month and that payment wasn't booked by a bank until the next month. In this scenario, the date that one wants in GnuCash might be the causation date or the booking date. I believe it is the user's choice of which date to save into GnuCash: either the date the transaction was caused (e.g. data entry into GnuCash) or the date the bank finally booked it. So the date should not be lower bounded by the end date of the last statement. Instead, only upper bounded by the end date of the current statement. Follow my thinking? --Dale On Fri, Apr 17, 2020 at 5:46 AM David Cousens wrote: > hi Geert > > The first was really about directConnect OFX imports, not something I am > all > that familiar with since banks here won't allow us access. Another user in > the discussion around Bug 797668 which I initially raised had mentioned > direct reconciliation in the import of OFX directly from the bank. I may > have misunderstood Gunter's comment that that procedure was starting the > reconcile procedure and automaticallly reconciling. I was trying to say > tthat if the bank provided enough information that matched the GnuCash > information, it may be possible to reconcile automatically but I think > Gunter was actually referring to the marking of the transactions as cleared > so it appeared checked when the reconcilaition was initiated. > > Agreed that the statement date is totally independent of the transaction > date. The dates in a statement will all fall between the end date of the > last statement +1 (the start date of the current statement) and the end > date > of the current statement as the statement dates are inclusive. > > The importers as far as I can tell so far (OFX and CSV) don't set the > reconcile flag to 'y' only to 'c' and the reconcile date is set to TODAY in > the importing process. This shouldn't affect the manual reconcilaition > process though which should set the reconciliation date to the statement > end > date when the flag is set to 'y'. > > I am currently trawling through the code to satisfy myself that I > understand > fullywhat is happening and will update the documentation accordingly if the > update i did last year is misleading. > > Davdi > > > > - > David Cousens > -- > Sent from: http://gnucash.1415818.n4.nabble.com/GnuCash-Dev-f1435356.html > ___ > gnucash-devel mailing list > gnucash-devel@gnucash.org > https://lists.gnucash.org/mailman/listinfo/gnucash-devel > ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: [GNC-dev] About 3.9 and reconciliation balances
hi Geert The first was really about directConnect OFX imports, not something I am all that familiar with since banks here won't allow us access. Another user in the discussion around Bug 797668 which I initially raised had mentioned direct reconciliation in the import of OFX directly from the bank. I may have misunderstood Gunter's comment that that procedure was starting the reconcile procedure and automaticallly reconciling. I was trying to say tthat if the bank provided enough information that matched the GnuCash information, it may be possible to reconcile automatically but I think Gunter was actually referring to the marking of the transactions as cleared so it appeared checked when the reconcilaition was initiated. Agreed that the statement date is totally independent of the transaction date. The dates in a statement will all fall between the end date of the last statement +1 (the start date of the current statement) and the end date of the current statement as the statement dates are inclusive. The importers as far as I can tell so far (OFX and CSV) don't set the reconcile flag to 'y' only to 'c' and the reconcile date is set to TODAY in the importing process. This shouldn't affect the manual reconcilaition process though which should set the reconciliation date to the statement end date when the flag is set to 'y'. I am currently trawling through the code to satisfy myself that I understand fullywhat is happening and will update the documentation accordingly if the update i did last year is misleading. Davdi - David Cousens -- Sent from: http://gnucash.1415818.n4.nabble.com/GnuCash-Dev-f1435356.html ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: [GNC-dev] About 3.9 and reconciliation balances
Thanks for this overview. It matches mostly with how I understand reconciliation. I will add a few comments in between. Op zondag 12 april 2020 07:14:22 CEST schreef David Cousens: > I don't see any problem with the reconcile status at present as implemented > in the QIF, OFX imports or even the AQbanking import of transactions and > balances, provided the bank accepts that record is a statement of the > account on their part. With AQbanking and OFX directconnect import of > records, the process. I don't understand this sentence. > If the process of querying the bank server for > records can provide a starting balance at the end of any past downloads of > data, the transaction split details for the account and relevant details and > the bank's record of the ending balance at the end of that group of > transaction splits then it should be in principle be reconciled using an > instance of the current procedure where the date entered for the > transaction = reconcilation date of the split = end date of the > statement=date at which reconciliation was carried out. > I don't understand this either. As far as I understand the statement date should not necessarily be the transaction date. A statement can have transactions spanning several days. > In the more general case there are multiple relevant properties and dates > associated with a reconciliation cuirrently recorded: > *date entered* - a transaction property > *date posted* - also a transaction property > *reconciliation status* split property ["n","c","y" ] only the "y" > indicates reconciliation. being cleared is different from being reconciled. > *reconciliation date* - split property - currently set by the reconciliation > process to the end date of the statement as the date to which that split is > reconciled AFAIK. Apparently not all of our importers do that, only manual reconciliation does. > > and some which are currently not recorded AFAIK but could be useful if > maintained in a reconciliation history for the account as a list: > *date of reconciliation* - date at which a reconciliation was carried out > -limited use but may be of some use in tracking down errors > *statement start date* > *starting balance>* > *statement end date>*. I agree this is useful extra information and also what Christopher Lam has been playing with. >From an implementation point of view I'd structure this slightly differently: 1. instead of adding an account property with a list of reconciliation history, I would introduce a new object, like a "statement" This object would have the fields you mention before (date of reconciliation, statement start date, statement starting balance, statement end date) and some more: *statement id* - most bank statements has a unique number which may be helpful to track *statement ending balance* - particularly useful to verify manual transaction entries. If you explicitly enter a start and ending balance in addition to the transactions themselves, amount typos will be caught by the numbers no longer adding up. *account" - the account this statement refers to. Lastly each split should get an additional field "statement id" referring to the statement which includes it. The split "reconciliation date" field would no longer be necessary. That info is encapsulated in the associated statement. This mapping would be much closer to the real world order of things: * during reconciliation a split is matched to a line on a statement * each split can be linked to only one statement * in case of reconciliation trouble in the past, the extra statement details make it easier to dig up the related external source (there's a statement id in addition to a reconciliation date). * it is more clear which splits were reconciled together - they are tied to the same statement, where in the past there was only a reconciliation date, which may have been wrong for various reasons. Regards, Geert ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: [GNC-dev] About 3.9 and reconciliation balances
Op zaterdag 11 april 2020 05:27:48 CEST schreef D. via gnucash- devel: > I will also add in here that the developers have muddied the waters on this > whole situation with the decision to de-reconcile transactions when the > user edits it later-- even if the changes are not related to an account's > balances. This change was reverted in gnucash 3.9. Editing transaction fields no longer de-reconciles any splits. You will only get a warning. Geert ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: [GNC-dev] About 3.9 and reconciliation balances
David, Am 12.04.20 um 07:25 schrieb David Cousens: > if the bank process fails or does not supply the necessary required > information I guess you would mark it as unreconciled but that assumes the > user is gong to be able to do a manual reconciliation. If the bank reports valid transactions, but no closing balance, they should be marked cleared - confirmed by the bank. Frank ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: [GNC-dev] About 3.9 and reconciliation balances
Frank Agreed. The other difficulty is that there is no control over a bank's particular implementation unless they are strictly adhering to a well defined standard and even then some countries adopt different standards. It is moot for me in any case since Australia's major banks don't allow directConnect server access except through selected software providers. if the bank process fails or does not supply the necessary required information I guess you would mark it as unreconciled but that assumes the user is gong to be able to do a manual reconciliation. david - David Cousens -- Sent from: http://gnucash.1415818.n4.nabble.com/GnuCash-Dev-f1435356.html ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: [GNC-dev] About 3.9 and reconciliation balances
David T, Christopher, Dale et al In accounting terms a reconciliation is always a confirmation of the details of the splits to an account against some external record independent of the particular set of books records are kept in. This is generally done in a set of contiguous periods over which that external record is available. A statement like a bank statement which is issued periodically is the most common example and probably the most systematic. (I have in the past had to reconcile a statement from a supplier of credit dealings against A/P and Payments for example and many similar processes for a business a process not necesarily as easily automated or defined as against an account but I don't think reconciliation in this wider sense is what we are dealing with here.) I don't see any problem with the reconcile status at present as implemented in the QIF, OFX imports or even the AQbanking import of transactions and balances, provided the bank accepts that record is a statement of the account on their part. With AQbanking and OFX directconnect import of records, the process. If the process of querying the bank server for records can provide a starting balance at the end of any past downloads of data, the transaction split details for the account and relevant details and the bank's record of the ending balance at the end of that group of transaction splits then it should be in principle be reconciled using an instance of the current procedure where the date entered for the transaction = reconcilation date of the split = end date of the statement=date at which reconciliation was carried out. In the more general case there are multiple relevant properties and dates associated with a reconciliation cuirrently recorded: *date entered* - a transaction property *date posted* - also a transaction property *reconciliation status* split property ["n","c","y" ] only the "y" indicates reconciliation. being cleared is different from being reconciled. *reconciliation date* - split property - currently set by the reconciliation process to the end date of the statement as the date to which that split is reconciled AFAIK. and some which are currently not recorded AFAIK but could be useful if maintained in a reconciliation history for the account as a list: *date of reconciliation* - date at which a reconciliation was carried out -limited use but may be of some use in tracking down errors *statement start date* *starting balance>* *statement end date>*. This would obviously need additional data structures, data file modifications, ie not short term. If we are going to try and verify the integrity of the account and reconciliation process we need to to > Looking this over, it seems to me that your goals could only be achieved > by adding a statement date data element to each transaction, which would > tie that transaction to a specific statement. David the problem with this is that reconciliation (and its parameters like reconciliation date and status) are the property of a particular group of splits to a particular account so any data elements that record whether a record is reconciled or not and to what statement period it is reconciled properly belong with the split where they are now. If one is recording the end date of the last reconciliation period and any associated balances which pertain to the whole group, these really should be properties of the account that was reconciled rather than a transaction which has links to two or more accounts and can be independently reconciled in each of those accounts. David Cousens - David Cousens -- Sent from: http://gnucash.1415818.n4.nabble.com/GnuCash-Dev-f1435356.html ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: [GNC-dev] About 3.9 and reconciliation balances
Hi David, I have not looked in the sources, but try a theoretical abstrakt: If all transmitted transactions are cleared by the bank and the bank statement contains a closing balance and this closing balance is equal the current (at that date) account balance, the section can be marked as reconciled. This would be more reliable than fat finger entered bank stements. But I don't know, what a) should or b) will happen, when the one or other condition fails. Regards Frank Am 11.04.20 um 00:57 schrieb David Cousens: > I was not aware that AQbanking does a reconcile on the fly. I also use OFX, > CSV nad very occasionally QIF imports (but not from Quicken) but not that > heavily any more. I think it would be useful to have this information about > the reconcile marking process documented at least in the importing section > of the help manual. There are sections on reconciliation in several sections > of the guide and the help manual. I suspect it is covered but there isn't a > nice summary. > > I don't know enough about how AQbanking works at the bank level, e.g. > whether it only makes transactions available after all bank internal > processes (clearing) have occurred as my bank hasn't implemented it and > refuses to on security grounds (but it does allow MYOB and a number of other > commercial packages direct server access but only on business accounts not > personal accounts - no doubt for a hefty fee) but it is clearly regarded as > the equivalent of a statement. > > In my manual OFX downloads for my credit card, the bank records transactions > as pending in their web portal, which I have made, but are not yet cleared > through VISA, until they get a clearnce from VISA. I can't include the > pending transactions in either a CSV or OFX download which makes sense. The > shortened times for electronic clearance between banks these days usually > means that i have had no conflicts between downloads and statements for > several years now unless I have had finger trouble in importing. On my > Savings account I can't remember having had a transaction marked as pending > since electronic clearing came in. > > I will put it on my to do list to find an appropraite places to put such a > summary. I tried to rationalize the Guide recently by having a general > section Ch4 on reconciliation and then pointing to the existing sections > which were focussed on reconciling specific account types. > > It makes sense to me to store the last reconciliation date and the ending > balance at that date in the account data then by comparison with > calculations of the starting balance on the fly, there would be a good > mechanism for detecting any altered transaction splits invalidating a > previous reconciliation and alert the user. Even more useful perhaps than > just the last reconciliation date and end balance would be a complete > reconciliation history for each account but that introduces alot more > complications for forward/backward compatability but it would improve the > ability to verify account integrity and to locate when a change affecting > the account integrity was introduced. > > By "fix" in this context are you referring to the exclusion of the future > reconciliation dates from the starting balance sums or a manual or auto fix > to the future reconciliation dates themselves. > > I am still trying to work out if an autofix is possible for the future > reconciliation dates case. It may be possible from the transaction dates > posted and dates entered for splits which have a future reconciliation date > to identify a potentially valid reconciliation date if you were also able to > construct a list of all reconciliation dates used by the account which was > what i did manually i.e. suggest the earliest and latest reconciliation > dates a user could choose to be consistent with the rest of the account > data. The same approach may also be the basis of an internal check on the > stored reconciliation data validity. > > David > > > > - > David Cousens > -- > Sent from: http://gnucash.1415818.n4.nabble.com/GnuCash-Dev-f1435356.html > ___ > gnucash-devel mailing list > gnucash-devel@gnucash.org > https://lists.gnucash.org/mailman/listinfo/gnucash-devel > ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: [GNC-dev] About 3.9 and reconciliation balances (auditing)
Hi David, Am 11.04.20 um 05:27 schrieb D. via gnucash-devel: > Looking this over, it seems to me that your goals could only be achieved by > adding a statement date data element to each transaction, which would tie > that transaction to a specific statement. This field would have much more > limited purpose and modification avenues. It would also help with generation > of reconciliation reports. The existing reconciliation date field would then > be freer to be considered a last-edited date, which might have benefits in > other ways-- for example, one might be able to identify a transaction in a > reconciliation set that has a significantly different last edited date. Or a > user could be notified in the popup when they change the value of a > reconciled split that they should revisit the mm-dd- reconciliation to be > sure that their books are still accurate. I like the idea of a last-edited date for another purpose: German law and probably others forbid editing once entered transactions. You have to void and reenter the transaction. A compare between the date_posted and the last-edit date would help autitors to verify that the rule was applied. Regards Frank ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: [GNC-dev] About 3.9 and reconciliation balances
Christopher, Those are all very good points. I do think that the goals are laudable, and I think Dale's call for further analysis is a good approach. The question of what reconciliation is supposed to be, and what it's supposed to do (beyond the most rudimentary textbook descriptions) are not yet clearly defined in theoretical or practical ways, and they should be. The original developer model, I believe, was that reconciliation was a one time, forward moving progression that never deviates. You get a statement from your bank, check off those entries in your books that match, and if they balance, you are done until the next month, when you repeat the process with the next batch, etc. The need for a date of reconciliation is secondary in this model. Unfortunately, real life doesn't always match that, and when a fat finger problem arises, it becomes exceedingly difficult to track down the source. I've been known to revert more than a year's transactions and reconcile them all over again in some situations. So, a mechanism that would allow me to isolate that variation would be fabulous. But, I am also one who will reconcile an account for a year or more in one pass, simply because it's an account with one or two monthly transactions. So, that is a complication... I will also add in here that the developers have muddied the waters on this whole situation with the decision to de-reconcile transactions when the user edits it later-- even if the changes are not related to an account's balances. In addition to the challenges these changes may cause to your reconcile model, they go straight to the question of what reconciliation means in the GnuCash realm. If reconciliation is an established regular process to compare against a statement always, then allowing an edit to change this status needs reevaluation. But if status is always malleable by later actions, then your model is doomed to failure, since you can't rely on the date of reconciliation to remain set to the actual statement date. The aqbanking issue (that imported transactions get the current date in reconciliation date) requires further consideration as well. Looking this over, it seems to me that your goals could only be achieved by adding a statement date data element to each transaction, which would tie that transaction to a specific statement. This field would have much more limited purpose and modification avenues. It would also help with generation of reconciliation reports. The existing reconciliation date field would then be freer to be considered a last-edited date, which might have benefits in other ways-- for example, one might be able to identify a transaction in a reconciliation set that has a significantly different last edited date. Or a user could be notified in the popup when they change the value of a reconciled split that they should revisit the mm-dd- reconciliation to be sure that their books are still accurate. David T. Original Message From: Christopher Lam Sent: Sat Apr 11 03:41:01 GMT+05:30 2020 Cc: gnucash-devel Subject: Re: [GNC-dev] About 3.9 and reconciliation balances The "purpose" for the "fix" was to allow future features: 1. allow manual reconciliation of an account, using past reconciled_date and relevant past ending_balance. If my "fix" were applied, it would serve *me* very well: (a) If an account, properly reconciled, had an errant split hiding somewhere caused by fat-finger, I could reconcile from any past bank statement, verify the balance is correct (or not) and narrow down to the exact period that contains the errant split. (b) This was also useful to fix cases where a past split was unreconciled and needed to be rereconciled -- you could reconcile latest statement and change the field 'n' to 'y' but its reconciled date would *not* be accurate anymore because it'd be the latest statement; I meant to reset the split reconciled_date to be the statement_date. 2. a natural extension of allowing past reconciliation is to store the list of statement_date and ending_balance somewhere in the account metadata and retrieve the list in reconcile_window. Hence https://github.com/Gnucash/gnucash/pull/667 is born. https://user-images.githubusercontent.com/1975870/77246245-5a1e2d80-6c1d-11ea-8fc3-b2ec34fdb5f9.png is possible. In my testing, rereconciling past statements is nicely upgraded. 3. a benefit of maintaining old reconciled_data is that we can now compare an account's reconciled balances at periodic dates against the stored list, and loudly report if a balance discrepancy is found. See https://user-images.githubusercontent.com/1975870/78035104-2c8d5e80-7358-11ea-95bc-feba7f9f2bba.png -- note the first two lines show reconciliation and account balances match; the third section show unequal balances and show the relevant splits which contain an extra one. So, (1) and (2) are not possible -- rereconciling past s
Re: [GNC-dev] About 3.9 and reconciliation balances
I was not aware that AQbanking does a reconcile on the fly. I also use OFX, CSV nad very occasionally QIF imports (but not from Quicken) but not that heavily any more. I think it would be useful to have this information about the reconcile marking process documented at least in the importing section of the help manual. There are sections on reconciliation in several sections of the guide and the help manual. I suspect it is covered but there isn't a nice summary. I don't know enough about how AQbanking works at the bank level, e.g. whether it only makes transactions available after all bank internal processes (clearing) have occurred as my bank hasn't implemented it and refuses to on security grounds (but it does allow MYOB and a number of other commercial packages direct server access but only on business accounts not personal accounts - no doubt for a hefty fee) but it is clearly regarded as the equivalent of a statement. In my manual OFX downloads for my credit card, the bank records transactions as pending in their web portal, which I have made, but are not yet cleared through VISA, until they get a clearnce from VISA. I can't include the pending transactions in either a CSV or OFX download which makes sense. The shortened times for electronic clearance between banks these days usually means that i have had no conflicts between downloads and statements for several years now unless I have had finger trouble in importing. On my Savings account I can't remember having had a transaction marked as pending since electronic clearing came in. I will put it on my to do list to find an appropraite places to put such a summary. I tried to rationalize the Guide recently by having a general section Ch4 on reconciliation and then pointing to the existing sections which were focussed on reconciling specific account types. It makes sense to me to store the last reconciliation date and the ending balance at that date in the account data then by comparison with calculations of the starting balance on the fly, there would be a good mechanism for detecting any altered transaction splits invalidating a previous reconciliation and alert the user. Even more useful perhaps than just the last reconciliation date and end balance would be a complete reconciliation history for each account but that introduces alot more complications for forward/backward compatability but it would improve the ability to verify account integrity and to locate when a change affecting the account integrity was introduced. By "fix" in this context are you referring to the exclusion of the future reconciliation dates from the starting balance sums or a manual or auto fix to the future reconciliation dates themselves. I am still trying to work out if an autofix is possible for the future reconciliation dates case. It may be possible from the transaction dates posted and dates entered for splits which have a future reconciliation date to identify a potentially valid reconciliation date if you were also able to construct a list of all reconciliation dates used by the account which was what i did manually i.e. suggest the earliest and latest reconciliation dates a user could choose to be consistent with the rest of the account data. The same approach may also be the basis of an internal check on the stored reconciliation data validity. David - David Cousens -- Sent from: http://gnucash.1415818.n4.nabble.com/GnuCash-Dev-f1435356.html ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: [GNC-dev] About 3.9 and reconciliation balances
The "purpose" for the "fix" was to allow future features: 1. allow manual reconciliation of an account, using past reconciled_date and relevant past ending_balance. If my "fix" were applied, it would serve *me* very well: (a) If an account, properly reconciled, had an errant split hiding somewhere caused by fat-finger, I could reconcile from any past bank statement, verify the balance is correct (or not) and narrow down to the exact period that contains the errant split. (b) This was also useful to fix cases where a past split was unreconciled and needed to be rereconciled -- you could reconcile latest statement and change the field 'n' to 'y' but its reconciled date would *not* be accurate anymore because it'd be the latest statement; I meant to reset the split reconciled_date to be the statement_date. 2. a natural extension of allowing past reconciliation is to store the list of statement_date and ending_balance somewhere in the account metadata and retrieve the list in reconcile_window. Hence https://github.com/Gnucash/gnucash/pull/667 is born. https://user-images.githubusercontent.com/1975870/77246245-5a1e2d80-6c1d-11ea-8fc3-b2ec34fdb5f9.png is possible. In my testing, rereconciling past statements is nicely upgraded. 3. a benefit of maintaining old reconciled_data is that we can now compare an account's reconciled balances at periodic dates against the stored list, and loudly report if a balance discrepancy is found. See https://user-images.githubusercontent.com/1975870/78035104-2c8d5e80-7358-11ea-95bc-feba7f9f2bba.png -- note the first two lines show reconciliation and account balances match; the third section show unequal balances and show the relevant splits which contain an extra one. So, (1) and (2) are not possible -- rereconciling past statement cannot currently take place (unless code sets/queries a book property "use strict reconciled dates"), but I believe (3) is still possible. It won't happen until 4.x though. On Fri, 10 Apr 2020 at 18:51, Dale Phurrough via gnucash-devel < gnucash-devel@gnucash.org> wrote: > Yes JR and DA > Nothing bad or sinister, rather a future opportunity to virtually > whiteboard the concepts and events surrounding "reconciliation", then > reviewing what gnucash currently does to see the gaps. > > It's comforting to see that it works for so many scenarios, and the > discussion on this was hardy for change/regression. > With it reverting to 3.8 behavior, I see all this as a healthy outcome with > a opportunity for the future. I'll gladly participate in it as I'm a > regular qif, ofx, manual, multiple currencies reconciler. > > On Fri, Apr 10, 2020, 6:43 PM Derek Atkins wrote: > > > Dale, > > > > On Fri, April 10, 2020 12:37 pm, John Ralls wrote: > > > > > > > > >> On Apr 10, 2020, at 8:22 AM, Dale Phurrough via gnucash-devel > > >> wrote: > > >> > > >> This feels like a separation of accounting logic -- separate from > > >> storage > > >> field -- has broken down. From what you wrote, it seems each of these > > >> sources have direct access to core fields without any > > >> accounting/business > > >> logic to control the transaction of reconciliation nor to validate the > > >> input into that reconciliation. > > > > > > Unfortunately there is very little such separation anywhere in GnuCash. > > > > Even moreso, in many cases (e.g. Import) the data just isn't there! QIF > > has a reconcile flag but does not contain a reconcile date field. So if > > you're importing reconciled data, what should you use for the reconciled > > date? Over time, different developers working in different parts of the > > code made slightly different decisions, because at the time the actual > > date didn't matter because nothing used it. > > > > -derek > > > > -- > >Derek Atkins 617-623-3745 > >de...@ihtfp.com www.ihtfp.com > >Computer and Internet Security Consultant > > > > > ___ > gnucash-devel mailing list > gnucash-devel@gnucash.org > https://lists.gnucash.org/mailman/listinfo/gnucash-devel > ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: [GNC-dev] About 3.9 and reconciliation balances
Yes JR and DA Nothing bad or sinister, rather a future opportunity to virtually whiteboard the concepts and events surrounding "reconciliation", then reviewing what gnucash currently does to see the gaps. It's comforting to see that it works for so many scenarios, and the discussion on this was hardy for change/regression. With it reverting to 3.8 behavior, I see all this as a healthy outcome with a opportunity for the future. I'll gladly participate in it as I'm a regular qif, ofx, manual, multiple currencies reconciler. On Fri, Apr 10, 2020, 6:43 PM Derek Atkins wrote: > Dale, > > On Fri, April 10, 2020 12:37 pm, John Ralls wrote: > > > > > >> On Apr 10, 2020, at 8:22 AM, Dale Phurrough via gnucash-devel > >> wrote: > >> > >> This feels like a separation of accounting logic -- separate from > >> storage > >> field -- has broken down. From what you wrote, it seems each of these > >> sources have direct access to core fields without any > >> accounting/business > >> logic to control the transaction of reconciliation nor to validate the > >> input into that reconciliation. > > > > Unfortunately there is very little such separation anywhere in GnuCash. > > Even moreso, in many cases (e.g. Import) the data just isn't there! QIF > has a reconcile flag but does not contain a reconcile date field. So if > you're importing reconciled data, what should you use for the reconciled > date? Over time, different developers working in different parts of the > code made slightly different decisions, because at the time the actual > date didn't matter because nothing used it. > > -derek > > -- >Derek Atkins 617-623-3745 >de...@ihtfp.com www.ihtfp.com >Computer and Internet Security Consultant > > ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: [GNC-dev] About 3.9 and reconciliation balances
Dale, On Fri, April 10, 2020 12:37 pm, John Ralls wrote: > > >> On Apr 10, 2020, at 8:22 AM, Dale Phurrough via gnucash-devel >> wrote: >> >> This feels like a separation of accounting logic -- separate from >> storage >> field -- has broken down. From what you wrote, it seems each of these >> sources have direct access to core fields without any >> accounting/business >> logic to control the transaction of reconciliation nor to validate the >> input into that reconciliation. > > Unfortunately there is very little such separation anywhere in GnuCash. Even moreso, in many cases (e.g. Import) the data just isn't there! QIF has a reconcile flag but does not contain a reconcile date field. So if you're importing reconciled data, what should you use for the reconciled date? Over time, different developers working in different parts of the code made slightly different decisions, because at the time the actual date didn't matter because nothing used it. -derek -- Derek Atkins 617-623-3745 de...@ihtfp.com www.ihtfp.com Computer and Internet Security Consultant ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: [GNC-dev] About 3.9 and reconciliation balances
> On Apr 10, 2020, at 8:22 AM, Dale Phurrough via gnucash-devel > wrote: > > This feels like a separation of accounting logic -- separate from storage > field -- has broken down. From what you wrote, it seems each of these > sources have direct access to core fields without any accounting/business > logic to control the transaction of reconciliation nor to validate the > input into that reconciliation. Unfortunately there is very little such separation anywhere in GnuCash. Regards, John Ralls ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: [GNC-dev] About 3.9 and reconciliation balances
Wow...this mix-match of reconcile date behaviors are good to log somewhere as an design/architecture aberration/bug. Good for discussion on... 1. what *does* the concept of "reconciliation date" mean? 2. does that meaning change with the source of reconciliation/truth (qif, quicken, AQBanking, manual, etc.). If so, are multiple fields needed? 3. what are the rules and validation for "reconciliation date" before it is stored into the field? 4. etc. This feels like a separation of accounting logic -- separate from storage field -- has broken down. From what you wrote, it seems each of these sources have direct access to core fields without any accounting/business logic to control the transaction of reconciliation nor to validate the input into that reconciliation. I agree. Let it stay in 3.8 style so a broader conversation can occur given all the good new info that have been surfaced in this discussion. --D On Fri, Apr 10, 2020 at 4:36 PM Christopher Lam wrote: > Some further findings. I don't think the "fix" needs to come back. > > The reconciled_date field is not well defined in code > - during manual reconciliation (which I use heavily) it sets > reconciled_date to statement_date. > - during QIF import from Quicken, it can also set reconcile_status to > mirror Quicken reconcile status, but the reconciled_date is never set at > all, and is interpreted as 01/01/70. These splits would be counted > correctly if my "fix" were reinstated. > - during QIF imports from banks (I use occasionally) the importer doesn't > typically set splits as reconciled but 'c'leared instead > - during OFX import from bank (I use this heavily) splits are set > 'c'leared. > - during AQBanking import splits' seem to be reconciled to TODAY. > > Conclusion: I think a future storage for *reconciled_date *and > *ending_balance* is still possible if it is populated when completing > successful manual reconciliation, and this does not necessarily need the > "fix". It would simply serve as a data integrity report verifying account > reconciled balances are still matching stored balances. > > On Thu, 9 Apr 2020 at 23:09, David Cousens > wrote: > > > Christopher > > > > My remaining concern is that fixing any incorrect reconciliation dates is > > at > > this stage a manual procedure requiring the editing of the XML file (SQL > > database users?). In addition it requires sufficient knowledge of the XML > > file structure to be able to correctly identify which accounts are > affected > > by the errors and to identify what the correct date should have been. > While > > this is not as much of a concern for users using GnuCash for their > personal > > finances, those using it for business uses may have external requirements > > imposed on them which may for example require verification of > > reconciliations if they are being audited. > > > > If an auditor examines records which have reconciliation dates that are > not > > consistent with the transaction dates it would at the least raise a flag > > with them. This is an unlikely event but if detected is likely to result > in > > considerable expenditure for such a user. > > > > I was in the fortunate position of having enough knowledge of how GnuCash > > works, the datafile structure and enough accounting knowledge and also > > having the records of exactly when I did the reconciliations to the > account > > which allowed me to determine that there had only been one occurrence of > > entering a statement end date incorrectly and that only the splits to one > > specific file had been affected. My situation could have been far more > > complicated. Some users may have records going back much further than > 2016 > > in a single file. I was fortunate in that after I retired I started a new > > simplified file for my personal records for my wife and myself. > > > > The average user, and particularly business users who will primarily be > > focussed on their business, is unlikely to be able to fix the XML file > and > > many would not feel confident about doing it and the risk of them > damaging > > their data file irrepairably is high (although you would clearly be > foolish > > to do this without creating one or more backups first). > > > > It is easy to say correct the reconciliation dates and rereconcile but I > > feel it will be necessary to provide users with a means of doing that > > correction in a consistent fashion (this really requires a knowledge of > the > > transaction dates and the dates of entering the transaction and whether > > other reconciliation dates are used in splits to the particular account > so > > that users can select an appropriate reconciliation date which is at > least > > consistent with the other dates in their records. > > > > I would opt for option 2 at this stage along with popup warnings on > > detecting the future reconciliation dates and statement end dates ahead > of > > the current date, but by default make the feature turned on for new books > >
Re: [GNC-dev] About 3.9 and reconciliation balances
Some further findings. I don't think the "fix" needs to come back. The reconciled_date field is not well defined in code - during manual reconciliation (which I use heavily) it sets reconciled_date to statement_date. - during QIF import from Quicken, it can also set reconcile_status to mirror Quicken reconcile status, but the reconciled_date is never set at all, and is interpreted as 01/01/70. These splits would be counted correctly if my "fix" were reinstated. - during QIF imports from banks (I use occasionally) the importer doesn't typically set splits as reconciled but 'c'leared instead - during OFX import from bank (I use this heavily) splits are set 'c'leared. - during AQBanking import splits' seem to be reconciled to TODAY. Conclusion: I think a future storage for *reconciled_date *and *ending_balance* is still possible if it is populated when completing successful manual reconciliation, and this does not necessarily need the "fix". It would simply serve as a data integrity report verifying account reconciled balances are still matching stored balances. On Thu, 9 Apr 2020 at 23:09, David Cousens wrote: > Christopher > > My remaining concern is that fixing any incorrect reconciliation dates is > at > this stage a manual procedure requiring the editing of the XML file (SQL > database users?). In addition it requires sufficient knowledge of the XML > file structure to be able to correctly identify which accounts are affected > by the errors and to identify what the correct date should have been. While > this is not as much of a concern for users using GnuCash for their personal > finances, those using it for business uses may have external requirements > imposed on them which may for example require verification of > reconciliations if they are being audited. > > If an auditor examines records which have reconciliation dates that are not > consistent with the transaction dates it would at the least raise a flag > with them. This is an unlikely event but if detected is likely to result in > considerable expenditure for such a user. > > I was in the fortunate position of having enough knowledge of how GnuCash > works, the datafile structure and enough accounting knowledge and also > having the records of exactly when I did the reconciliations to the account > which allowed me to determine that there had only been one occurrence of > entering a statement end date incorrectly and that only the splits to one > specific file had been affected. My situation could have been far more > complicated. Some users may have records going back much further than 2016 > in a single file. I was fortunate in that after I retired I started a new > simplified file for my personal records for my wife and myself. > > The average user, and particularly business users who will primarily be > focussed on their business, is unlikely to be able to fix the XML file and > many would not feel confident about doing it and the risk of them damaging > their data file irrepairably is high (although you would clearly be foolish > to do this without creating one or more backups first). > > It is easy to say correct the reconciliation dates and rereconcile but I > feel it will be necessary to provide users with a means of doing that > correction in a consistent fashion (this really requires a knowledge of the > transaction dates and the dates of entering the transaction and whether > other reconciliation dates are used in splits to the particular account so > that users can select an appropriate reconciliation date which is at least > consistent with the other dates in their records. > > I would opt for option 2 at this stage along with popup warnings on > detecting the future reconciliation dates and statement end dates ahead of > the current date, but by default make the feature turned on for new books > (these should have no reconciliation dates set - it may be necessary to > consider if this affects records imported from a previous book or other > program into a new book) and created in the off state when written into an > existing book which was created without the feature. The warnings could > contain a reference to the option i.e. turn it on once you have corrected > the advance date. I think this will allow all users to continue to function > while incorporating the function until such time as we have a robust fix > method in place. Even thess will require considerable code changes in a > varietyt of areas of the code to get up and running where as the fix > procedure will also be a fairly major undertaking. > > Those who feel they have the necessary skills and knowledge can in the > meantime repair their files and switch the feature on if they desire. > > Unfortunately new options always increase the risk of user confusion but > the > above approach may minimise this as the user won't see the feature unless > they are using a new book or have deliberately chosen to use it after > repairing their file. > > The other obvious
Re: [GNC-dev] About 3.9 and reconciliation balances
Christopher My remaining concern is that fixing any incorrect reconciliation dates is at this stage a manual procedure requiring the editing of the XML file (SQL database users?). In addition it requires sufficient knowledge of the XML file structure to be able to correctly identify which accounts are affected by the errors and to identify what the correct date should have been. While this is not as much of a concern for users using GnuCash for their personal finances, those using it for business uses may have external requirements imposed on them which may for example require verification of reconciliations if they are being audited. If an auditor examines records which have reconciliation dates that are not consistent with the transaction dates it would at the least raise a flag with them. This is an unlikely event but if detected is likely to result in considerable expenditure for such a user. I was in the fortunate position of having enough knowledge of how GnuCash works, the datafile structure and enough accounting knowledge and also having the records of exactly when I did the reconciliations to the account which allowed me to determine that there had only been one occurrence of entering a statement end date incorrectly and that only the splits to one specific file had been affected. My situation could have been far more complicated. Some users may have records going back much further than 2016 in a single file. I was fortunate in that after I retired I started a new simplified file for my personal records for my wife and myself. The average user, and particularly business users who will primarily be focussed on their business, is unlikely to be able to fix the XML file and many would not feel confident about doing it and the risk of them damaging their data file irrepairably is high (although you would clearly be foolish to do this without creating one or more backups first). It is easy to say correct the reconciliation dates and rereconcile but I feel it will be necessary to provide users with a means of doing that correction in a consistent fashion (this really requires a knowledge of the transaction dates and the dates of entering the transaction and whether other reconciliation dates are used in splits to the particular account so that users can select an appropriate reconciliation date which is at least consistent with the other dates in their records. I would opt for option 2 at this stage along with popup warnings on detecting the future reconciliation dates and statement end dates ahead of the current date, but by default make the feature turned on for new books (these should have no reconciliation dates set - it may be necessary to consider if this affects records imported from a previous book or other program into a new book) and created in the off state when written into an existing book which was created without the feature. The warnings could contain a reference to the option i.e. turn it on once you have corrected the advance date. I think this will allow all users to continue to function while incorporating the function until such time as we have a robust fix method in place. Even thess will require considerable code changes in a varietyt of areas of the code to get up and running where as the fix procedure will also be a fairly major undertaking. Those who feel they have the necessary skills and knowledge can in the meantime repair their files and switch the feature on if they desire. Unfortunately new options always increase the risk of user confusion but the above approach may minimise this as the user won't see the feature unless they are using a new book or have deliberately chosen to use it after repairing their file. The other obvious thing is to update the documentation with appropriate instructions and in the shorter term put this up on the wiki in the meantime. I don't know if it is legit or desirable to reference a wiki page from the documentation or even from within the program (in the warning popups for example) but this may be a temporary way to alert users and point users to a solution until an autofix procedure can be developed. I can start preparing a wiki page outlining the problem and how to do the manual fix to the XML file. I guess SQL users could always save their data to XML, do the fix and then reopen the fixed XML file and then resave to the database as a fix while it is fresh in my mind. David Cousens - David Cousens -- Sent from: http://gnucash.1415818.n4.nabble.com/GnuCash-Dev-f1435356.html ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: [GNC-dev] About 3.9 and reconciliation balances
Christopher, I understand your frustration at trying to help and getting a negative response. In trying to fix one problem, you have unwittingly waded into a bigger one. Your message raises a couple of points. First, this rollback need not be forever. It could wait until such time as the reconcile date issues can be more effectively addressed-- for example, by creating an interface to allow users to view or correct them. Your own advice to use the reconciliation report to track down the errors might point a way toward such a solution. Second, your changes have made a set of books that were completely functional in earlier versions no longer functional-- all without any changes to the underlying data. I have significant problems with that, and I can't believe that you would even suggest arbitrarily changing data in a user's books without considering ways to allow the user to make those fixes consciously, themselves. I've mentioned that you've done this before with the reports; I suggest you reconsider your approach to development of Gnucash tools. Screwing up reconciliation and then looking for a slap dash post hoc fix is not the way to win over users. David T. Original Message From: Christopher Lam Sent: Thu Apr 09 12:55:18 GMT+05:30 2020 Cc: gnucash-devel Subject: Re: [GNC-dev] About 3.9 and reconciliation balances Regarding reverting: It is too difficult to create a UI to modify reconciled dates. Options to fix this are: 1. roll back this change and tolerate the invalid data. This means any progress on that matter is hindered forever. 2. allow this change depending on a user-selectable book property "Use strict reconcile date balances". Pros: This means the book can be read/written by any recent version. See "Use split action for num" as a similar example. Cons: The toggled property can be a confusing option for new users. 3. allow this change depending on a non-user-selectable book feature "Use strict reconcile date balances". Pros: This feature can be set by a menu item. It triggers one-time verification of all reconciled dates, instructing users to correct/re-reconcile previous reconciled-dates, and sets the book feature if all dates seem correct. Cons: This means the book can only be read by 3.10 onwards. On Thu, 9 Apr 2020, 11:29 am Christopher Lam, wrote: > Thank you David. > > Further work for tighter safeguards is planned in > https://github.com/Gnucash/gnucash/pull/685 -- no scrubbing is currently > on the table but a warning during reconciliation if future splits are > detected. > > On Thu, 9 Apr 2020, 9:59 am David Cousens, > wrote: > >> I am now happy that there is no problem with the starting balance >> calculation >> in 3.9 and the problem was caused by a previously undetected finger >> problem >> on my part and withdraw my previous qualms about retaining it for the >> future. >> >> I still think it is excessive to restrict the reconciliation date entry in >> any way but agree that a warning should be displayed if either a statment >> date in advance of "today" is entered, perhaps with the option for the >> user >> to confirm that they either want to retain the advance date or reenter it >> in >> a popup dialog. >> >> Also agree with a warning if any reconciled transactions with reconcile >> dates in advance of today are detected in the file. I am less sure about >> assigning them to an arbitrary past date however. >> >> If a procedure for correcting them is developed it might be helpful if it >> could display data for transactiions with reconciled splits which have the >> advance date. The information that I found most useful to determine when I >> made the error were the transaction date posted and the transaction date >> entered into GnuCash and the reconciliation date and the account name or >> at >> least the account guid to confirm that all the affected splits are to a >> single account. This was particularlyuseful to me because i append an "_R" >> to statement pdf files I have reconciled and the file modified time allows >> me to determine the date I did a recociliation. In future I will append >> "_Rmmdd" so i don't have to look up the modified times for the files >> as >> well. >> >> Bug797668 is now marked as resolved >> >> >> >> - >> David Cousens >> -- >> Sent from: http://gnucash.1415818.n4.nabble.com/GnuCash-Dev-f1435356.html >> ___ >> gnucash-devel mailing list >> gnucash-devel@gnucash.org >> https://lists.gnucash.org/mailman/listinfo/gnucash-devel >> > ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: [GNC-dev] About 3.9 and reconciliation balances
I don’t see #1 as ‘forever’ but ‘until a better option can be crafted’. #2 gives flexibility. To reduce confusion, should it be selected by default? (only those who intentionally need to reconcile future dates are the users who’ll have to grok it) How viable is: 3b. Add this to the Check & Repair process already in the Actions Menu. Does the book feature need to be set without further input? (is that the part that prevents the book from being read from <3.10?) Regards, Adrien > On Apr 9, 2020 w15d100, at 2:25 AM, Christopher Lam > wrote: > > Regarding reverting: > > It is too difficult to create a UI to modify reconciled dates. Options to > fix this are: > > 1. roll back this change and tolerate the invalid data. This means any > progress on that matter is hindered forever. > > 2. allow this change depending on a user-selectable book property "Use > strict reconcile date balances". > Pros: This means the book can be read/written by any recent version. See > "Use split action for num" as a similar example. > Cons: The toggled property can be a confusing option for new users. > > 3. allow this change depending on a non-user-selectable book feature "Use > strict reconcile date balances". > Pros: This feature can be set by a menu item. It triggers one-time > verification of all reconciled dates, instructing users to > correct/re-reconcile previous reconciled-dates, and sets the book feature > if all dates seem correct. > Cons: This means the book can only be read by 3.10 onwards. > ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: [GNC-dev] About 3.9 and reconciliation balances
Op dinsdag 7 april 2020 18:37:25 CEST schreef Derek Atkins: > Hi, > > On Tue, April 7, 2020 11:47 am, Dale Phurrough wrote: > > Cool use of cleared/reconciled for expenses. I can follow that general > > approach. > > I do have an inquiry regarding the need for the future when reconciling. > > (What am I missing...or is there a gnucash feature I'm assuming exists > > that > > doesn't?) 樂 > > The path I follow is CLOSE to the path you follow, except I do a few > things at the same time in gnucash and post-date the transaction. More > below.. > > > In my mind, I see two accounts > > 1) bank account, e.g. Bank of America Checking > > 2) the expenses, for simplicity lets put them all into a single > > "Reimbursable Expenses" account > > In my case I also have a credit card account involved. Also, note, that > the Reimbursable Expenses account is really an ASSET type. > > > As I think through this, I would hope you can following a workflow similar > > the following: > > 1. Enter multiple transactions into "Reimbursable Expenses" account for > > all > > such expenses > > Yes. These come from various places, like Credit Cards, Cash, etc. > > > 2. Submit expense reports and mark those expenses in "Reimbursable > > Expenses" account as cleared > > 3. Wait for check > > 4. Receive reimbursement check on 1 May 2020 > > Correct. > > > 5. On 2 May 2020, use GnuCash to reconcile the "Reimbursable Expenses" > > account > > 6. Mark the splits in the "Reimbursable Expenses" that are included with > > this reimbursement check as reconciled. The reconcile date on those > > specific splits will be 2 May 2020. > > 7. Now the "Reimbursable Expenses" account is reconciled. > > 8. On 4 May 2020, deposit the reimbursement check. > > This is not correct, or at least not completely correct. Recall that the > overall process is like A/R: > > A) CC -> Reimbursible > B) Reibursible -> Cash/Bank > > So when I get the reimbursement check on May 1, I use my bank app to > deposit the check, but I know that deposit wont actually hit my account > until May 2 (or possibly May 4). So on May 1st I enter in a transaction > for Reimbursible -> Bank (to process the check) dated May 2 (because > that's when it hits the bank, which is more important to me than when it > hits the reimbursible account). The NEXT step is that, also on May 1, I > want to reconcile my Reimbursibles back down to $0 (because they've been > reimbursed)!! In this case, I want to date the reconcile on May 2 (so it > includes the deposit that will hit the next day). > > I am lazy and forgetful. Hmm, so lazy and forgetful are the source of this debate... ;-) For your convenience, even if we would prevent setting a reconcile date in the future (which actually *would* make sense), you could add splits to the reconcile that are future dated. The downside would be you'd be required to manually adjust the end balance in the reconcile info window. So for your example above, you could - set reconcile date to May 1 - set end balance to 0 (which the reconcile info window would have miscalculated based on the May 1 end date) - continue with reconciliation and include the check's split in the reconcile. That will work just fine. Do you really insist on being able to set a date in the future just for this ? Regards, Geert ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: [GNC-dev] About 3.9 and reconciliation balances
Regarding reverting: It is too difficult to create a UI to modify reconciled dates. Options to fix this are: 1. roll back this change and tolerate the invalid data. This means any progress on that matter is hindered forever. 2. allow this change depending on a user-selectable book property "Use strict reconcile date balances". Pros: This means the book can be read/written by any recent version. See "Use split action for num" as a similar example. Cons: The toggled property can be a confusing option for new users. 3. allow this change depending on a non-user-selectable book feature "Use strict reconcile date balances". Pros: This feature can be set by a menu item. It triggers one-time verification of all reconciled dates, instructing users to correct/re-reconcile previous reconciled-dates, and sets the book feature if all dates seem correct. Cons: This means the book can only be read by 3.10 onwards. On Thu, 9 Apr 2020, 11:29 am Christopher Lam, wrote: > Thank you David. > > Further work for tighter safeguards is planned in > https://github.com/Gnucash/gnucash/pull/685 -- no scrubbing is currently > on the table but a warning during reconciliation if future splits are > detected. > > On Thu, 9 Apr 2020, 9:59 am David Cousens, > wrote: > >> I am now happy that there is no problem with the starting balance >> calculation >> in 3.9 and the problem was caused by a previously undetected finger >> problem >> on my part and withdraw my previous qualms about retaining it for the >> future. >> >> I still think it is excessive to restrict the reconciliation date entry in >> any way but agree that a warning should be displayed if either a statment >> date in advance of "today" is entered, perhaps with the option for the >> user >> to confirm that they either want to retain the advance date or reenter it >> in >> a popup dialog. >> >> Also agree with a warning if any reconciled transactions with reconcile >> dates in advance of today are detected in the file. I am less sure about >> assigning them to an arbitrary past date however. >> >> If a procedure for correcting them is developed it might be helpful if it >> could display data for transactiions with reconciled splits which have the >> advance date. The information that I found most useful to determine when I >> made the error were the transaction date posted and the transaction date >> entered into GnuCash and the reconciliation date and the account name or >> at >> least the account guid to confirm that all the affected splits are to a >> single account. This was particularlyuseful to me because i append an "_R" >> to statement pdf files I have reconciled and the file modified time allows >> me to determine the date I did a recociliation. In future I will append >> "_Rmmdd" so i don't have to look up the modified times for the files >> as >> well. >> >> Bug797668 is now marked as resolved >> >> >> >> - >> David Cousens >> -- >> Sent from: http://gnucash.1415818.n4.nabble.com/GnuCash-Dev-f1435356.html >> ___ >> gnucash-devel mailing list >> gnucash-devel@gnucash.org >> https://lists.gnucash.org/mailman/listinfo/gnucash-devel >> > ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: [GNC-dev] About 3.9 and reconciliation balances
Thank you David. Further work for tighter safeguards is planned in https://github.com/Gnucash/gnucash/pull/685 -- no scrubbing is currently on the table but a warning during reconciliation if future splits are detected. On Thu, 9 Apr 2020, 9:59 am David Cousens, wrote: > I am now happy that there is no problem with the starting balance > calculation > in 3.9 and the problem was caused by a previously undetected finger > problem > on my part and withdraw my previous qualms about retaining it for the > future. > > I still think it is excessive to restrict the reconciliation date entry in > any way but agree that a warning should be displayed if either a statment > date in advance of "today" is entered, perhaps with the option for the user > to confirm that they either want to retain the advance date or reenter it > in > a popup dialog. > > Also agree with a warning if any reconciled transactions with reconcile > dates in advance of today are detected in the file. I am less sure about > assigning them to an arbitrary past date however. > > If a procedure for correcting them is developed it might be helpful if it > could display data for transactiions with reconciled splits which have the > advance date. The information that I found most useful to determine when I > made the error were the transaction date posted and the transaction date > entered into GnuCash and the reconciliation date and the account name or at > least the account guid to confirm that all the affected splits are to a > single account. This was particularlyuseful to me because i append an "_R" > to statement pdf files I have reconciled and the file modified time allows > me to determine the date I did a recociliation. In future I will append > "_Rmmdd" so i don't have to look up the modified times for the files as > well. > > Bug797668 is now marked as resolved > > > > - > David Cousens > -- > Sent from: http://gnucash.1415818.n4.nabble.com/GnuCash-Dev-f1435356.html > ___ > gnucash-devel mailing list > gnucash-devel@gnucash.org > https://lists.gnucash.org/mailman/listinfo/gnucash-devel > ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: [GNC-dev] About 3.9 and reconciliation balances
I am now happy that there is no problem with the starting balance calculation in 3.9 and the problem was caused by a previously undetected finger problem on my part and withdraw my previous qualms about retaining it for the future. I still think it is excessive to restrict the reconciliation date entry in any way but agree that a warning should be displayed if either a statment date in advance of "today" is entered, perhaps with the option for the user to confirm that they either want to retain the advance date or reenter it in a popup dialog. Also agree with a warning if any reconciled transactions with reconcile dates in advance of today are detected in the file. I am less sure about assigning them to an arbitrary past date however. If a procedure for correcting them is developed it might be helpful if it could display data for transactiions with reconciled splits which have the advance date. The information that I found most useful to determine when I made the error were the transaction date posted and the transaction date entered into GnuCash and the reconciliation date and the account name or at least the account guid to confirm that all the affected splits are to a single account. This was particularlyuseful to me because i append an "_R" to statement pdf files I have reconciled and the file modified time allows me to determine the date I did a recociliation. In future I will append "_Rmmdd" so i don't have to look up the modified times for the files as well. Bug797668 is now marked as resolved - David Cousens -- Sent from: http://gnucash.1415818.n4.nabble.com/GnuCash-Dev-f1435356.html ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: [GNC-dev] About 3.9 and reconciliation balances
A previous reconciliation on 31/12/2018 had had the statement date entered as 31/12/20 rather than 31/12/2018 which causes the entered date to be read as 31/12/2020 into GnuCash. I checked the entered dates on all transactions which had 2020-12-31 as the recociliation date verified these were all splits to the account I had been trying to reconcile and that the entered dates were consistent with a reconciliation statement end date of 2018-12-31 for a reconciliation carried out on 15/01/2019. Editing the XML data file and changing all reconciliation dates set at 2020-12-31 to 2018-12-31 The account now reconciles with the correct starting balance. David - David Cousens -- Sent from: http://gnucash.1415818.n4.nabble.com/GnuCash-Dev-f1435356.html ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: [GNC-dev] About 3.9 and reconciliation balances
Typical very old transaction that has had its reconciliation date altered but not by reconciling the period it is from (The date the transacion originally occurred and the weird date 202-12-31 and the reconciliation date for the other split of the transaction are highlighted. Only transaction splits to my PayPal account (guid d5918ab81d0a78a9b0b167f38f7e6bb3) have been affected. I haven't yet determined whether it is all or only some of the the splits to this account which have been altered. c081e40b43b03afbb16972d9e01d286a CURRENCY AUD * 2016-07-18 10:59:00 + * 2017-01-05 05:34:41 + Direct Debit 279234 PAYPAL AUSTRALIA 4QU229QXCL3ZC date-posted 2016-07-18 notes OFX ext. info: |Trans type:Generic debit 83954d093b9c8cbfc8f5ee5f4ef30067 y * 2020-12-31 13:59:59 + * 2000/100 2000/100 d5918ab81d0a78a9b0b167f38f7e6bb3 f5ad907bdae7fc58e365bde2c40a7e91 Direct Debit 279234 PAYPAL AUSTRALIA 4QU229QXCL3ZC y * 2016-07-31 13:59:59 + * -2000/100 -2000/100 fa0b7ad960e493b992a2dd374bbd1070 online_id D620002676419001NPA David - David Cousens -- Sent from: http://gnucash.1415818.n4.nabble.com/GnuCash-Dev-f1435356.html ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: [GNC-dev] About 3.9 and reconciliation balances
Chris, 1. I am concerned that the "new method of calculating starting balances" introduced in GnuCash 3.9 may have a problem. I am still tracking down exactly what happened in my case. I do have a future reconciliation date on some transactions that had previously been imported in V 3.8. A preliminary look at the data file after it was first opened with 3.9 seems to indicate that v3.9 itself introduced those spurious reconciliation dates. At this stage it is not clear how these future reconciliation dates have been added. I reverted to v3.8 (after attempting to reconcile in 3.9 and getting a weird starting balance not in agreement with the previously reconciled closing balance) and I was able to get the correct starting balance and complete the reconciliation to the end of the next statement (I didn't actually apply it) but the difference was 0 and the reconciled balance agreed with the closing balance of the statement. I am in the process of chasing this up in the XML datafiles and my backups (last 30 days) which go back to before I changed from 3.8 to 3.9 to try and determine exactly when and how the spurious future reconciliation dates came about. It may have been a mistyping on my part while reconciling what is the target account of the splits from the account I am currently trying to reconcile. Why should the reconciliation dates of the splits that are to an account other than the one I am currently I am reconciling affect the process of reconciliation? From an accounting perspective my view is that it should not. Reconciliation of accounts should only be dependent upon the splits to the account being reconciled and the exrternal statement that it is being reconciled against, not the data associated with any other account. A more important question is how these spurious future reconciliation dates arise in the first place. Most likely as a result of mistyping the date during entry. *At this I would suggest reversing these changes to the starting balance calculation if possible in 3.10, abandoning 3.9 and reverting to 3.8 until 3.10 can be released. I think a more thorough examination of the cause of the problem it is trying to address and whether checks on the entered statement date and issuing a warning for the user to decide is not a better approach. Derek's is possibly the only user case where one might want to reconcile in advance 1. I fail to see why there is any necessity to restrict the statement date in any way. OK warn the user that their statement date is in the future with a popup, but if that is what they want to do why stop them. 2. I don't agree with imposing any random repair process. I think we need to identify firstly how and why this is ocurring and particularly why historical dates that are not associated with transactions in recent reconciliations are having their reconciliation dates altered (see below) by a relatively recent reconciliation processes. The reconciliation process should not be altering records of past reconciliations. Once that is identified and fixed then consider a repair process. * The problem is I have now found spurious 2020-12-31 reconciliation dates applied to transactions which were recorded and reconciled originally at the time 2015 -2017 in the Paypal account I have been trying to reconcile for 2019-2020 and these are also present in my back up files as at 22/03/2020 which were created when I was still using v3.8 ( I only keep 30 days of backups) so I can't go back further. Only the reconciliation dates on the Paypal account have been changed and reconciliation dates on other splits in the same transactions are still set at dates in the 2015-2017 range which indicates that it is a reconciliation process applied to the Paypal account which is causing the problem. AFAIK at this stage the spurious 31-12-2020 reconciliation date is only occurring in my Paypal account (Liability) and not any other accounts. I have established that I reconciled thePayPal account on 03/04/2020 in v3.8 (v 3.9 was installed on 06/04/2020 at 17:46:55AEST and I mark statements which have been reconciled by appending "_R" to the file name so the file modification times when I did the reconciliation) against a statement to 31/12/2019 which is when I could possibly have entered the 31/12/2020 date incorrectly if that was the cause. However the spurious reconciliation date of 31-12-2020 is present in transactions to the PayPal account in a backup file created 22/03/2020 so it is hard to explain as a result of a reconciliation which ocurred on 03/04/2020. The problem with a future reconciliation date being present was then not created by V3.9 and Chris Lam's mod only highlighted that it was already present. David Cousens - David Cousens -- Sent from: http://gnucash.1415818.n4.nabble.com/GnuCash-Dev-f1435356.html ___ gnucash-devel mailing list gnucash-devel@gnucash.org
Re: [GNC-dev] About 3.9 and reconciliation balances
Hi, On Tue, April 7, 2020 11:47 am, Dale Phurrough wrote: > Cool use of cleared/reconciled for expenses. I can follow that general > approach. > I do have an inquiry regarding the need for the future when reconciling. > (What am I missing...or is there a gnucash feature I'm assuming exists > that > doesn't?) 樂 The path I follow is CLOSE to the path you follow, except I do a few things at the same time in gnucash and post-date the transaction. More below.. > In my mind, I see two accounts > 1) bank account, e.g. Bank of America Checking > 2) the expenses, for simplicity lets put them all into a single > "Reimbursable Expenses" account In my case I also have a credit card account involved. Also, note, that the Reimbursable Expenses account is really an ASSET type. > As I think through this, I would hope you can following a workflow similar > the following: > 1. Enter multiple transactions into "Reimbursable Expenses" account for > all > such expenses Yes. These come from various places, like Credit Cards, Cash, etc. > 2. Submit expense reports and mark those expenses in "Reimbursable > Expenses" account as cleared > 3. Wait for check > 4. Receive reimbursement check on 1 May 2020 Correct. > 5. On 2 May 2020, use GnuCash to reconcile the "Reimbursable Expenses" > account > 6. Mark the splits in the "Reimbursable Expenses" that are included with > this reimbursement check as reconciled. The reconcile date on those > specific splits will be 2 May 2020. > 7. Now the "Reimbursable Expenses" account is reconciled. > 8. On 4 May 2020, deposit the reimbursement check. This is not correct, or at least not completely correct. Recall that the overall process is like A/R: A) CC -> Reimbursible B) Reibursible -> Cash/Bank So when I get the reimbursement check on May 1, I use my bank app to deposit the check, but I know that deposit wont actually hit my account until May 2 (or possibly May 4). So on May 1st I enter in a transaction for Reimbursible -> Bank (to process the check) dated May 2 (because that's when it hits the bank, which is more important to me than when it hits the reimbursible account). The NEXT step is that, also on May 1, I want to reconcile my Reimbursibles back down to $0 (because they've been reimbursed)!! In this case, I want to date the reconcile on May 2 (so it includes the deposit that will hit the next day). I am lazy and forgetful. I don't want to have to remember to re-reconcile the deposit the next day, or have to reconcile multiple times. I want to do it all simultaneously when I actually process the reimbursement. But as I said above, I care about the date I expect it to hit the bank moreso than the date I receive the check. > 9. On 8 May 2020, you see that check's cash was deposited into your bank > account. You mark the credit split in the "Bank of America Checking" > account as cleared. > 10. On 2 *June* 2020, you use your May bank account statement to reconcile > the "Bank of America Checking" account > 11. You mark the splits in "Bank of America Checking" account that match > those in the bank statement as reconciled. The reconcile date on those > specific splits will be 2 *June* 2020 > 12. Now the " Bank of America Checking" account is reconciled. Other than that I often skip #9, yes, the rest of this is correct. > In the above workflow, there is no future-reconcile handling needed. > Reconciling the expenses is separate form reconciling the bank account. > Reconciles always happen now or in the past. See my corrections. In short: 1) make expenses 2) File expense report / mark items cleared 3) Receive Check. Deposit check (dated 1 day ahead) and Reconcile Reimbursement (also dated 1-2 days ahead) 4) *at some point in the future* reconcile the bank account once I get the next bank statement -derek -- Derek Atkins 617-623-3745 de...@ihtfp.com www.ihtfp.com Computer and Internet Security Consultant ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: [GNC-dev] About 3.9 and reconciliation balances
Cool use of cleared/reconciled for expenses. I can follow that general approach. I do have an inquiry regarding the need for the future when reconciling. (What am I missing...or is there a gnucash feature I'm assuming exists that doesn't?) 樂 In my mind, I see two accounts 1) bank account, e.g. Bank of America Checking 2) the expenses, for simplicity lets put them all into a single "Reimbursable Expenses" account As I think through this, I would hope you can following a workflow similar the following: 1. Enter multiple transactions into "Reimbursable Expenses" account for all such expenses 2. Submit expense reports and mark those expenses in "Reimbursable Expenses" account as cleared 3. Wait for check 4. Receive reimbursement check on 1 May 2020 5. On 2 May 2020, use GnuCash to reconcile the "Reimbursable Expenses" account 6. Mark the splits in the "Reimbursable Expenses" that are included with this reimbursement check as reconciled. The reconcile date on those specific splits will be 2 May 2020. 7. Now the "Reimbursable Expenses" account is reconciled. 8. On 4 May 2020, deposit the reimbursement check. 9. On 8 May 2020, you see that check's cash was deposited into your bank account. You mark the credit split in the "Bank of America Checking" account as cleared. 10. On 2 *June* 2020, you use your May bank account statement to reconcile the "Bank of America Checking" account 11. You mark the splits in "Bank of America Checking" account that match those in the bank statement as reconciled. The reconcile date on those specific splits will be 2 *June* 2020 12. Now the " Bank of America Checking" account is reconciled. In the above workflow, there is no future-reconcile handling needed. Reconciling the expenses is separate form reconciling the bank account. Reconciles always happen now or in the past. --Dale On Tue, Apr 7, 2020 at 4:37 PM Derek Atkins wrote: > Hi, > > On Tue, April 7, 2020 10:21 am, Dale Phurrough via gnucash-devel wrote: > > > 1. > > This time period of one month is arbitrary and my experience suggests > this > > is a symptom of a hack, incomplete solution, or partial workaround. Both > > (a) allowing future/advance reconciliation -and- (b) disallowing > something > > "too far ahead" is again arbitrary and is forcing an arbitrary accounting > > policy rather than conservative double-book rules and math. > > Features with rules which are defined by the computer clock > > are troublesome. It makes sense when one needs to transfer money via an > > online API. It doesn't make sense when one is doing core accounting > tasks. > > Also this seems (in my shallow understanding of the 3.9 change) to not > > work > > with that 3.9 change. How could someone future reconcile if future-date > > splits wouldn't be valid with respect to the future-target-reconcile > > balance? This suggests a cascade of more workarounds. > > I will comment on this, because this came directly from a conversation > that Chris and I had. Chris originally wanted to ensure that reconciles > could never happen in the future *at all*. I.e., it would not allow you > to future-reconcile. > > I said that I do often future-reconcile. My use-case is that I keep track > of my reimbursable expenses using cleared/reconciled flags to denote that > I expensed them (cleared), and the expense was reimburned (reconciled). > So when I file my expense report I mark those items as cleared. Then when > the reimbursement check comes in, I reconcile down to $0. My issue is > that I want to do this (enter the reimbursement check and reconcile) on > the day I receive the check, however the check only gets deposited the > next day, so I need to be able to reconcile 1-2 days into the future. > > When I proposed this use-case to Chris, he suggested a month leeway, and I > didn't push back saying "that is too much." > > If you have an ACTUAL use-case of future-reconciling I would like to hear > it, because this is the only future-reconcile use case I have come across. > Everything else in my experience is reconciling in the past. > > > 2. > > I highly discourage altering any data without explicit disclosure and > > acceptance of each change occurrence. This is not in alignment with the > > ideas of data integrity, data persistence, trust and disclosure, etc. > > Also a similar arbitrary accounting policy with the suggested date>1month > > logic and 1/1/1970. > > GnuCash already has several places where is will correct (read: alter) > data entries on the fly, when it Scrubs the accounts. It does this with > bad strings and invalid dates, among other corrections. It will already > do these corrections silently when the data file is loaded. Having said > that, it probably still makes sense to pop up a dialog to warn the user if > it finds this kind of "invalid" data. > > > --Dale > > -derek > -- >Derek Atkins 617-623-3745 >de...@ihtfp.com www.ihtfp.com >Computer and Internet Security
Re: [GNC-dev] About 3.9 and reconciliation balances
Hi, On Tue, April 7, 2020 10:21 am, Dale Phurrough via gnucash-devel wrote: > 1. > This time period of one month is arbitrary and my experience suggests this > is a symptom of a hack, incomplete solution, or partial workaround. Both > (a) allowing future/advance reconciliation -and- (b) disallowing something > "too far ahead" is again arbitrary and is forcing an arbitrary accounting > policy rather than conservative double-book rules and math. > Features with rules which are defined by the computer clock > are troublesome. It makes sense when one needs to transfer money via an > online API. It doesn't make sense when one is doing core accounting tasks. > Also this seems (in my shallow understanding of the 3.9 change) to not > work > with that 3.9 change. How could someone future reconcile if future-date > splits wouldn't be valid with respect to the future-target-reconcile > balance? This suggests a cascade of more workarounds. I will comment on this, because this came directly from a conversation that Chris and I had. Chris originally wanted to ensure that reconciles could never happen in the future *at all*. I.e., it would not allow you to future-reconcile. I said that I do often future-reconcile. My use-case is that I keep track of my reimbursable expenses using cleared/reconciled flags to denote that I expensed them (cleared), and the expense was reimburned (reconciled). So when I file my expense report I mark those items as cleared. Then when the reimbursement check comes in, I reconcile down to $0. My issue is that I want to do this (enter the reimbursement check and reconcile) on the day I receive the check, however the check only gets deposited the next day, so I need to be able to reconcile 1-2 days into the future. When I proposed this use-case to Chris, he suggested a month leeway, and I didn't push back saying "that is too much." If you have an ACTUAL use-case of future-reconciling I would like to hear it, because this is the only future-reconcile use case I have come across. Everything else in my experience is reconciling in the past. > 2. > I highly discourage altering any data without explicit disclosure and > acceptance of each change occurrence. This is not in alignment with the > ideas of data integrity, data persistence, trust and disclosure, etc. > Also a similar arbitrary accounting policy with the suggested date>1month > logic and 1/1/1970. GnuCash already has several places where is will correct (read: alter) data entries on the fly, when it Scrubs the accounts. It does this with bad strings and invalid dates, among other corrections. It will already do these corrections silently when the data file is loaded. Having said that, it probably still makes sense to pop up a dialog to warn the user if it finds this kind of "invalid" data. > --Dale -derek -- Derek Atkins 617-623-3745 de...@ihtfp.com www.ihtfp.com Computer and Internet Security Consultant ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: [GNC-dev] About 3.9 and reconciliation balances
Hi. I do have feedback on the 3.10 change suggestions. Meanwhile, I'll not comment on the feature change "3.9 comes with a new method for calculating reconciliation starting balances". I defer to others on this list for that. 1. This time period of one month is arbitrary and my experience suggests this is a symptom of a hack, incomplete solution, or partial workaround. Both (a) allowing future/advance reconciliation -and- (b) disallowing something "too far ahead" is again arbitrary and is forcing an arbitrary accounting policy rather than conservative double-book rules and math. Features with rules which are defined by the computer clock are troublesome. It makes sense when one needs to transfer money via an online API. It doesn't make sense when one is doing core accounting tasks. Also this seems (in my shallow understanding of the 3.9 change) to not work with that 3.9 change. How could someone future reconcile if future-date splits wouldn't be valid with respect to the future-target-reconcile balance? This suggests a cascade of more workarounds. 2. I highly discourage altering any data without explicit disclosure and acceptance of each change occurrence. This is not in alignment with the ideas of data integrity, data persistence, trust and disclosure, etc. Also a similar arbitrary accounting policy with the suggested date>1month logic and 1/1/1970. --Dale On Tue, Apr 7, 2020 at 9:32 AM Christopher Lam wrote: > Release 3.9 comes with a new method for calculating reconciliation starting > balances. > > Previously, the account reconciled balance was considered the starting > balance. This includes any splits previously reconciled with an invalid > (e.g. future) reconciliation statement date. > > From 3.9 onwards, the starting balance is calculated by adding all account > splits whose *previous *reconciled date the *current* reconciliation date. > This means any past reconciliation with an invalid (ie future) reconciled > date would be ignored. The benefit is to allow re-reconciliation of a past > statement. > > So, anyone with difficulties reconciling an account with 3.9 onwards should > check the account Reconciliation Report, Start Date = today, End Date = > 31/12/ to seek these splits. To fix it manually, you can unreconcile > them and re-reconcile with any past date or today. Or modify the XML/SQL to > fix these dates. > > To prevent future issues there are several safeguards being planned for > 3.10: > > 1. any reconciliation must have a statement date of TODAY + 1MONTH. This > allows some leeway for users who wish to reconcile in advance, yet > disallows reconciliation too far ahead. > > 2. a datafile with splits with reconciled_date in the future *may *be > repaired; e.g. if reconciled_date > 1 month from today, then it is > evidently an error and can be changed to an arbitrary old date eg 1/1/1970. > This will allow them to be counted when calculating modern reconciled > balances. > > I think 1 is acceptable. Any particular votes for 2? > ___ > gnucash-devel mailing list > gnucash-devel@gnucash.org > https://lists.gnucash.org/mailman/listinfo/gnucash-devel > ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: [GNC-dev] About 3.9 and reconciliation balances
Christopher, I have to admit to some trepidation at having something as significant to my books as reconciliation suddenly changed based on data elements that aren't visible in the GUI. Are you sure the solution is better than the problem? I'm also concerned at the fact that (as with some other reports you've changed in the past) you are changing GnuCash behaviors without considering their effects on end users, and then waiting for the outcry from users to enact fixes. That is poor planning on your part, and has significant effect on the user base. Perhaps you should revert this change for the time being, until such time as you figure out a solution that works for the user base. For example, give the user an interface that allows them to see and fix the kinds of errors you've already identified. As for your proposed safeguards, option 2 should better be implemented by giving the user a listing of spurious reconciled txns, and giving them some interface to set a proper date (or a better one) on a group or individual basis, rather than arbitrarily creating a date that will cause the user to question the quality of their data sometime down the road. David T. Original Message From: Christopher Lam Sent: Tue Apr 07 13:00:55 GMT+05:30 2020 To: gnucash-devel Subject: [GNC-dev] About 3.9 and reconciliation balances Release 3.9 comes with a new method for calculating reconciliation starting balances. Previously, the account reconciled balance was considered the starting balance. This includes any splits previously reconciled with an invalid (e.g. future) reconciliation statement date. From 3.9 onwards, the starting balance is calculated by adding all account splits whose *previous *reconciled date the *current* reconciliation date. This means any past reconciliation with an invalid (ie future) reconciled date would be ignored. The benefit is to allow re-reconciliation of a past statement. So, anyone with difficulties reconciling an account with 3.9 onwards should check the account Reconciliation Report, Start Date = today, End Date = 31/12/ to seek these splits. To fix it manually, you can unreconcile them and re-reconcile with any past date or today. Or modify the XML/SQL to fix these dates. To prevent future issues there are several safeguards being planned for 3.10: 1. any reconciliation must have a statement date of TODAY + 1MONTH. This allows some leeway for users who wish to reconcile in advance, yet disallows reconciliation too far ahead. 2. a datafile with splits with reconciled_date in the future *may *be repaired; e.g. if reconciled_date > 1 month from today, then it is evidently an error and can be changed to an arbitrary old date eg 1/1/1970. This will allow them to be counted when calculating modern reconciled balances. I think 1 is acceptable. Any particular votes for 2? ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
[GNC-dev] About 3.9 and reconciliation balances
Release 3.9 comes with a new method for calculating reconciliation starting balances. Previously, the account reconciled balance was considered the starting balance. This includes any splits previously reconciled with an invalid (e.g. future) reconciliation statement date. >From 3.9 onwards, the starting balance is calculated by adding all account splits whose *previous *reconciled date the *current* reconciliation date. This means any past reconciliation with an invalid (ie future) reconciled date would be ignored. The benefit is to allow re-reconciliation of a past statement. So, anyone with difficulties reconciling an account with 3.9 onwards should check the account Reconciliation Report, Start Date = today, End Date = 31/12/ to seek these splits. To fix it manually, you can unreconcile them and re-reconcile with any past date or today. Or modify the XML/SQL to fix these dates. To prevent future issues there are several safeguards being planned for 3.10: 1. any reconciliation must have a statement date of TODAY + 1MONTH. This allows some leeway for users who wish to reconcile in advance, yet disallows reconciliation too far ahead. 2. a datafile with splits with reconciled_date in the future *may *be repaired; e.g. if reconciled_date > 1 month from today, then it is evidently an error and can be changed to an arbitrary old date eg 1/1/1970. This will allow them to be counted when calculating modern reconciled balances. I think 1 is acceptable. Any particular votes for 2? ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
