changing a large number of transactions in a batch -- impossible?

classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

changing a large number of transactions in a batch -- impossible?

Anna's unattended mail
There does not seem to be a way to select many transactions, and do
something like delete them, change the description, or move them to
another account.  Is gnucash really limited to working on one
transaction at a time?  So if a user wants to move 100 transactions
from account A to account B, the gnucash way is to tediously do ~3-4
clicks on each row (since there are no keyboard shortcuts either).  Is
that correct?

What are the options here?  What about exporting an account to another
software tool (Ledger-cli?), performing the batch operation there, and
then importing the new accounts?  Has anyone tried this?

_______________________________________________
gnucash-user mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|

Re: changing a large number of transactions in a batch -- impossible?

Derek Atkins
Hi,

"Anna's unattended mail" <[hidden email]> writes:

> There does not seem to be a way to select many transactions, and do
> something like delete them, change the description, or move them to
> another account.  Is gnucash really limited to working on one
> transaction at a time?  So if a user wants to move 100 transactions
> from account A to account B, the gnucash way is to tediously do ~3-4
> clicks on each row (since there are no keyboard shortcuts either).  Is
> that correct?

Yes, you can in general only operate on a single transaction at once.

> What are the options here?  What about exporting an account to another
> software tool (Ledger-cli?), performing the batch operation there, and
> then importing the new accounts?  Has anyone tried this?

I did say "in general", there are some cases where you can operate on
multiple transactions at once.  The importer, for one.  The QIF
importer, specifically, operates on meta data so multiple transactions
with the same metadata can all be assigned the same target.

Moreover, if you want to move *all* the transactions from one account
into another you can delete that account and gnucash will ask you where
you want to move the transactions.

Other than that, however, you are limited to only doing one at a time.

You can *try* modifying the datafile out from under GnuCash, but this is
neither recommended nor supported.  Be sure to backup your data before
you attempt that.

> Please remember to CC this list on all your replies.
> You can do this by using Reply-To-List or Reply-All.

-derek

--
       Derek Atkins, SB '93 MIT EE, SM '95 MIT Media Laboratory
       Member, MIT Student Information Processing Board  (SIPB)
       URL: http://web.mit.edu/warlord/    PP-ASEL-IA     N1NWH
       [hidden email]                        PGP key available
_______________________________________________
gnucash-user mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|

Re: changing a large number of transactions in a batch -- impossible?

sunfish62
Anna--

Derek's points are all accurate.


My experience has been that changing a subset of a register's transactions--even a large subset--can be handled pretty quickly with just a few tricks.


First, open the register with the erroneous transactions in it, and set the view to Transaction Journal view. Next, get to the split of a transaction you want to change, and change it. Before leaving the field, highlight the full account information (for example, "Expenses:Business:New Account") and copy it. Now, press "Enter", and because of how Gnucash works, the transaction will "disappear" from the current register. (Actually, the transaction isn't gone, it's just been moved to the new account register. Since you changed the split line that anchored the transaction to the current register, it goes away from this one.) Go to the next transaction you want to move, highlight the current account split line, and paste in the account information. Pressing enter will disappear this transaction as well. Repeat for each transaction you want to move, and before you know it, you'll be done.

HTH,
David



________________________________
 From: Derek Atkins <[hidden email]>
To: [hidden email]
Sent: Thursday, August 16, 2012 6:34 AM
Subject: Re: changing a large number of transactions in a batch -- impossible?
 
Hi,

"Anna's unattended mail" <[hidden email]> writes:

> There does not seem to be a way to select many transactions, and do
> something like delete them, change the description, or move them to
> another account.  Is gnucash really limited to working on one
> transaction at a time?  So if a user wants to move 100 transactions
> from account A to account B, the gnucash way is to tediously do ~3-4
> clicks on each row (since there are no keyboard shortcuts either).  Is
> that correct?

Yes, you can in general only operate on a single transaction at once.

> What are the options here?  What about exporting an account to another
> software tool (Ledger-cli?), performing the batch operation there, and
> then importing the new accounts?  Has anyone tried this?

I did say "in general", there are some cases where you can operate on
multiple transactions at once.  The importer, for one.  The QIF
importer, specifically, operates on meta data so multiple transactions
with the same metadata can all be assigned the same target.

Moreover, if you want to move *all* the transactions from one account
into another you can delete that account and gnucash will ask you where
you want to move the transactions.

Other than that, however, you are limited to only doing one at a time.

You can *try* modifying the datafile out from under GnuCash, but this is
neither recommended nor supported.  Be sure to backup your data before
you attempt that.

> Please remember to CC this list on all your replies.
> You can do this by using Reply-To-List or Reply-All.

-derek

--
       Derek Atkins, SB '93 MIT EE, SM '95 MIT Media Laboratory
       Member, MIT Student Information Processing Board  (SIPB)
       URL: http://web.mit.edu/warlord/    PP-ASEL-IA     N1NWH
      [hidden email]                        PGP key available
_______________________________________________
gnucash-user mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
_______________________________________________
gnucash-user mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|

Re: changing a large number of transactions in a batch -- impossible?

Anna's unattended mail
On 2012-08-16, David T. <[hidden email]> wrote:
>
> First, open the register with the erroneous transactions in it, and
> set the view to Transaction Journal view. Next, get to the split of
> a transaction you want to change, and change it.

I appreciate the feedback from you and Derek.  Your approach is
probably the fastest manual way of doing it, but still quite tedious
for more than 50 transactions, particularly if non-sequential.  If a
batch of transactions with descriptions matching a string are to be
moved, and the batch size is in the hundreds or thousands, I've found
that hacking the XML is (unfortunately) the only practical way.

Suppose your business books are mixed with your personal books, and
you want to create the separation that should have existed initially,
but didn't.  If all transactions containing "Netflix" are not business
related, this is how I would delete them:

  tr '\n' '^' < business_and_personal.gnucash |
    sed 's/\^.gnc:trans/\n<gnc:trans/g' |
    grep -vi '^.....transaction.*netflix.*transaction.$' |
    tr '^' '\n' > business.gnucash

This works only because the caret ("^") character does not appear
anywhere in my gnucash file, so it can be used to temporarily replace
the newlines.  If anyone else attempts this, they should verify that
"grep -c '\^' file.gnucash" is zero first.

Wasn't there some talk a while back about gnucash using sql instead?
That would seem to have more utility than XML, making it easier to
make batch changes that tools do not support.

_______________________________________________
gnucash-user mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|

Re: changing a large number of transactions in a batch -- impossible?

Derek Atkins
Hi,

"Anna's unattended mail" <[hidden email]> writes:

> On 2012-08-16, David T. <[hidden email]> wrote:
>>
>> First, open the register with the erroneous transactions in it, and
>> set the view to Transaction Journal view. Next, get to the split of
>> a transaction you want to change, and change it.
>
> I appreciate the feedback from you and Derek.  Your approach is
> probably the fastest manual way of doing it, but still quite tedious
> for more than 50 transactions, particularly if non-sequential.  If a
> batch of transactions with descriptions matching a string are to be
> moved, and the batch size is in the hundreds or thousands, I've found
> that hacking the XML is (unfortunately) the only practical way.

That begs the question of how you got hundreds or thousands of
transactions input with the wrong data in the first place?  Perhaps the
right time to fix it would be when you enter the data?

> Suppose your business books are mixed with your personal books, and

This is already a bad idea....

> you want to create the separation that should have existed initially,
> but didn't.  If all transactions containing "Netflix" are not business
> related, this is how I would delete them:
>
>   tr '\n' '^' < business_and_personal.gnucash |
>     sed 's/\^.gnc:trans/\n<gnc:trans/g' |
>     grep -vi '^.....transaction.*netflix.*transaction.$' |
>     tr '^' '\n' > business.gnucash
>
> This works only because the caret ("^") character does not appear
> anywhere in my gnucash file, so it can be used to temporarily replace
> the newlines.  If anyone else attempts this, they should verify that
> "grep -c '\^' file.gnucash" is zero first.
>
> Wasn't there some talk a while back about gnucash using sql instead?
> That would seem to have more utility than XML, making it easier to
> make batch changes that tools do not support.

Not necessarily any easier.  And we DO have a SQL backend available, but
it's not recommended for real data yet because people keep finding data
integrity bugs in it.  We don't have a good test suite to make sure
every API actually works with every backend.  So these bugs only get
found when some poor user loses data and complains.  :-(

> Please remember to CC this list on all your replies.
> You can do this by using Reply-To-List or Reply-All.

-derek

--
       Derek Atkins, SB '93 MIT EE, SM '95 MIT Media Laboratory
       Member, MIT Student Information Processing Board  (SIPB)
       URL: http://web.mit.edu/warlord/    PP-ASEL-IA     N1NWH
       [hidden email]                        PGP key available
_______________________________________________
gnucash-user mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|

Re: changing a large number of transactions in a batch -- impossible?

Anna's unattended mail
On 2012-08-20, Derek Atkins <[hidden email]> wrote:
>
> That begs the question of how you got hundreds or thousands of
> transactions input with the wrong data in the first place?

It doesn't matter how it got that way.  

> Perhaps the right time to fix it would be when you enter the data?

This would require a crystal ball.  Users cannot predict the future.
This is like saying an OS does not need a "mv" command, because users
can organize their files in advance, and put each file in the right
place the first time.

If you cannot make changes, scalability is shot.

>> Suppose your business books are mixed with your personal books, and
>
> This is already a bad idea....

Depends.  If a business only needs one asset, income, and expense
account, breaking it into two files only creates inconvenience.  It's
also safer in the sense that it's easier to have one file that needs
to split than it is to have two files that need to merge.

Same with currencies.  You can create a new top-level expense account
for each currency, or you can keep the same tree and create a new
leaf-node account for each currency, or you can create another whole
book (file) for each currency.

Each approach has different merits, and a user might only know they
made the wrong decision 1000+ transactions later.

> Not necessarily any easier.

I'm not sure how XML could be easier to hack than sql.  XML would
require a special parser (which in effect builds a database
internally), and there would need to be a DOM parser API for each
language that users would need to work in.  SQL is a universal
language that would be all a user needs to know for most things.  Plus
there are SQL APIs for all the high-level languages, if someone needs
to do something more complex than pure SQL.

> We don't have a good test suite to make sure every API actually
> works with every backend.

Why would GC developers support more than one API?  They should only
have the burdon of supporting the sql API that gnucash itself uses.
If someone wants to use another toolchain, it would be their
responsibility to make necessary changes.  As long as the test suite
covers the GC-SQL API, that would prevent disruptions to regular
users.

_______________________________________________
gnucash-user mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Liz
Reply | Threaded
Open this post in threaded view
|

Re: changing a large number of transactions in a batch -- impossible?

Liz
On Mon, 20 Aug 2012 15:41:25 +0000 (UTC)
"Anna's unattended mail" <[hidden email]> wrote:

> >
> > That begs the question of how you got hundreds or thousands of
> > transactions input with the wrong data in the first place?  
>
> It doesn't matter how it got that way.  
>
> > Perhaps the right time to fix it would be when you enter the data?  
>
> This would require a crystal ball.  Users cannot predict the future.
> This is like saying an OS does not need a "mv" command, because users
> can organize their files in advance, and put each file in the right
> place the first time.

Being sadly familiar with a commercial package, popular in Australia, I
can advise that ability to change your account structure is not always
available.

Whether your accountant assists with the setup or not, mistakes can
still be made n the account hierarchy setup.
In Gnucash I do a find to find the transactions I want to move, then
transfer the transactions one-by-one with a series of keystrokes. It
works like a game of Space Invaders as you move all the transactions
until the result of the 'find' is blank. It might be tedious but it
does work.
_______________________________________________
gnucash-user mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|

Re: changing a large number of transactions in a batch -- impossible?

Yawar Amin
In reply to this post by Anna's unattended mail
Hi,

On 2012-08-20 11:41, Anna's unattended mail wrote:

> On 2012-08-20, Derek Atkins <[hidden email]> wrote:
> [...]
>> We don't have a good test suite to make sure every API actually
>> works with every backend.
> Why would GC developers support more than one API?  They should only
> have the burdon of supporting the sql API that gnucash itself uses.
> If someone wants to use another toolchain, it would be their
> responsibility to make necessary changes.  As long as the test suite
> covers the GC-SQL API, that would prevent disruptions to regular
> users.
I think what Derek meant is that we don't have a good test suite to make
sure every function in the GnuCash API actually works with every SQL
backend (i.e. SQLite, MySQL, PostgreSQL).

Regards,

Yawar



_______________________________________________
gnucash-user mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.

signature.asc (268 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: changing a large number of transactions in a batch -- impossible?

Derek Atkins
In reply to this post by Anna's unattended mail
"Anna's unattended mail" <[hidden email]> writes:

>> Not necessarily any easier.
>
> I'm not sure how XML could be easier to hack than sql.  XML would
> require a special parser (which in effect builds a database

XML can be opened in any text editor and modified there.

> internally), and there would need to be a DOM parser API for each
> language that users would need to work in.  SQL is a universal
> language that would be all a user needs to know for most things.  Plus

Nah, no need to be that complicated.  Just M-x replace-string to change
the guids.  But it's dangerous to do that.  It's also dangerous to do it
in raw SQL.

> there are SQL APIs for all the high-level languages, if someone needs
> to do something more complex than pure SQL.

There's also the Python GnuCash bindings which would let you do it
programatically and safely.

>> We don't have a good test suite to make sure every API actually
>> works with every backend.
>
> Why would GC developers support more than one API?  They should only
> have the burdon of supporting the sql API that gnucash itself uses.
> If someone wants to use another toolchain, it would be their
> responsibility to make necessary changes.  As long as the test suite
> covers the GC-SQL API, that would prevent disruptions to regular
> users.

Sorry, let me be more explicit here, because you and I are using the
word "API" to mean different things in this context, so let me rephrase.
GnuCash does not have a good test suite that tests every function within
the GnuCash C API against every backend to make sure that each function
(and set of functions that are designed to work together) works properly
with every backend.

There is no "GC-SQL API" per se.  There is a "GNC Backend API", but
that's not sufficient to prevent data loss because there are definitely
ways to use the GnuCash Engine in a way that will cause data loss.  The
reason is that GnuCash is NOT a DB App.  It never was.  The DB Backend
is purely a data store that happens to use a SQL DB as the data store
instead of the XML File.  But it's still just that, a Data Store.  There
are still places in GnuCash that make the assumption that GnuCash will
write out the whole dataset via "Save".  And those are the data-loss
places we need to find.  It's also in places we don't have tests.

So no, until we get a comprehensive test suite over the entire GnuCash
Engine API and apply it to every Backend, alas, no, we cannot be assured
that we wont have data loss.

> Please remember to CC this list on all your replies.
> You can do this by using Reply-To-List or Reply-All.

-derek

--
       Derek Atkins, SB '93 MIT EE, SM '95 MIT Media Laboratory
       Member, MIT Student Information Processing Board  (SIPB)
       URL: http://web.mit.edu/warlord/    PP-ASEL-IA     N1NWH
       [hidden email]                        PGP key available
_______________________________________________
gnucash-user mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.