transaction.scm date subtotal options - calling wizards ...

[Bob Gustafson]

Hi Christopher

I use a program with a massive pattern recognition phase that sorts 
MT940 bank transaction records into categories. It has evolved over a 
decade to help prepare US taxes for German real estate properties.

The data is entered by other people. You cannot believe how many ways 
there are to spell the name of one electrical contractor - with umlauts, 
with 'UE' instead of umlaut, upper case, lower case, misspelled.. Each 
has been categorized correctly with an additional pattern. Also, over 
the decade, the bank has evolved their export data format from csv to 
mt940. Also the character set has changed - finally settling on UTF-8 a 
few years ago.

Hopefully your report desires are less ambitious. My program is written 
in Ruby and accesses a postgresql database. If I were to rewrite it 
today, it would be in Nim (http://nim-lang.org/)

I use GNUCASH, but just as a viewer of input data and some intermediate 
results.

Best regards - BobG

On 09/22/2017 10:41 AM, Christopher Lam wrote:
> Hi John,
>
>
>
> The origin of this project comes from my wish to produce report... "how
> much did I spend on various categories, in this quarter?"
>
>
> Source account=Asset:Bank.
>
> Prime-sortkey=date
>
> Prime-subtotal=quarterly
>
> Sec-sortkey=other-accname
>
> Sec-subtotal=true
>
>
>
> 2016Q1
>
> Expense:Groceries
>
> 01/01/16 – Allmart $200
>
> 01/02/16 – Qmart $300
>
> 01/03/16 – Nomart $300
>
> *Subtotal Expense:Groceries $800*
>
> Expense:Petrol
>
> 15/01/16 – Coral $300
>
> 15/02/16 – BQ $250
>
> * Subtotal Expense:Petrol $550*
>
> * Subtotal 2016Q1 = $1350*
>
>
>
> However the current query mechanism means that the dates will be split up,
> because, although they belong to the same quarter, they have different
> dates.
>
> qof-query mechanism cannot sort by quarter... can only sort by date.
>
>
> I attach 2 screenshots to illustrate and the sample test file. This is my
> amended transaction.scm and the original/custom sorter can be chosen in the
> checkbox.
>
>
> C
>
> On 22 September 2017 at 23:33, Christopher Lam <christopher.lck at gmail.com>
> wrote:
>
>> Hi John,
>>
>>
>>
>> The origin of this project comes from my wish to produce report.
>>
>> Source account=Asset:Bank. Prime-sortkey=date (subtotal quarterly) and
>> sec-sortkey=other-accname (subtotal=true).
>>
>>
>>
>> 2016Q1
>>
>> Expense:Groceries
>>
>> 01/01/16 – Allmart $200
>>
>> 01/02/16 – Qmart $300
>>
>> 01/03/16 – Nomart $300
>>
>> *Subtotal Expense:Groceries $800*
>>
>> Expense:Petrol
>>
>> 15/01/16 – Coral $300
>>
>> 15/02/16 – BQ $250
>>
>> * Subtotal Expense:Petrol $550*
>>
>> * Subtotal 2016Q1 = $1350*
>>
>>
>>
>> However the current query mechanism means that the dates will be split up,
>> because they’
>>
>>
>>
>> Sent from Mail <https://go.microsoft.com/fwlink/?LinkId=550986> for
>> Windows 10
>>
>>
>>
>> *From: *John Ralls <jralls at ceridwen.us>
>> *Sent: *Friday, 22 September 2017 10:40 PM
>> *To: *Christopher Lam <christopher.lck at gmail.com>
>> *Cc: *gnucash-devel at lists.gnucash.org
>> *Subject: *Re: transaction.scm date subtotal options - calling wizards ...
>>
>>
>>
>>
>>
>>
>>
>> On Sep 22, 2017, at 5:48 AM, Christopher Lam <christopher.lck at gmail.com>
>> wrote:
>>
>>
>>
>> Hi devs,
>>
>> While working on transaction.scm (again) to fix a sorting/grouping bug[1],
>> I came across some inconsistencies:
>>
>> 1. if I choose sortkey "date" "exact-time" -- they do the exact same thing,
>> but otherwise work well
>>
>> 2. if I choose sortkey "reconciled date" -- it does what it says, however
>> *the date-subtotal selector is not used at all.*
>>
>> 3. if I choose sortkey "register order"
>> (a) first, I'm not entirely sure what exactly this mean - *does it mean,
>> sort by EntryDate *(accessible by xaccTransGetDateEntered
>> <https://wiki.gnucash.org/docs/MASTER/group__Transaction.html#
>> gaa03b01c98f9ac644074a19a182cd5dd3>)
>>
>> (b) second, it allows date-subtotal selection, which to me is puzzling, but
>> could be acceptable if we're sorting by EntryDate
>>
>> My conclusion from above would be that the original date-sorting-types has
>> a typo, it was (define date-sorting-types (list 'date 'exact-time
>> 'register-order)) but it should really be (define date-sorting-types (list
>> 'date 'exact-time 'reconciled-date))
>>
>> *What would be the consensus from wizards if we had to do it right?*
>>
>> I suggest:
>> 1. We change (define date-sorting-types (list 'date 'exact-time
>> 'reconciled-date))
>> 2. When sorting by register-order, it should disallow date-subtotals
>>
>> My solution to produce my report correctly requires a custom sorting
>> algorithm which is nearly complete, and would override the
>> qof-query-set-sort-order. Work-in-progress at
>> https://github.com/christopherlam/gnucash/commits/master-fix-grouping-
>> by-date
>>
>> [1] this bug relates to my preferred sorting/grouping: prime-sortkey=date,
>> subtotal=quarterly, sec-sortkey=account-name, subtotal=true. This will
>> produce an incorrect grouping whereby a quarter (eg 2016 Q1) will be split
>> incorrectly, because the query will sort by dates rather than quarters.
>> Documented in my comment on
>> https://bugzilla.gnome.org/show_bug.cgi?id=626385
>>
>>
>>
>> Christopher,
>>
>>
>>
>> I would expect “Register Order” to mean the normal sorting order of
>> transactions in the register:
>>
>> date-posted, num, date-entered, description, though it’s possible that it
>> would look at the Sort By… settings on the source register.
>>
>>
>>
>> “Date” should be “date-posted” for clarity. While a sort of date-entered
>> might be amusing it’s not particularly useful. Exact time on date-posted
>> isn’t meaningful: All transactions are posted at 10:59Z so that the date
>> remains the same in most time zones. Similarly I’d expect that most
>> date-reconciled entries will be grouped on a single date and time since
>> it’s unusual to reconcile an account more than once per day (or even once
>> per month for most people).
>>
>>
>>
>> I don’t understand your analysis that "a quarter (eg 2016 Q1) will be
>> split incorrectly, because the query will sort by dates rather than
>> quarters”. Sorting by quarters is just a large-granularity sort by date; a
>> sort by date at day granularity should be easily divided by quarters.
>>
>>
>>
>> Regards,
>>
>> John Ralls
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> _______________________________________________
>> gnucash-devel mailing list
>> gnucash-devel at gnucash.org
>> https://lists.gnucash.org/mailman/listinfo/gnucash-devel