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 <[email protected]>
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 <[email protected]>
*Sent: *Friday, 22 September 2017 10:40 PM
*To: *Christopher Lam <[email protected]>
*Cc: *[email protected]
*Subject: *Re: transaction.scm date subtotal options - calling wizards ...
On Sep 22, 2017, at 5:48 AM, Christopher Lam <[email protected]>
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
[email protected]
https://lists.gnucash.org/mailman/listinfo/gnucash-devel
_______________________________________________
gnucash-devel mailing list
[email protected]
https://lists.gnucash.org/mailman/listinfo/gnucash-devel
