Re: [GNC-dev] Transactions vs Splits

[Wm via gnucash-devel]

On 05/03/2019 12:57, David Cousens wrote:


I will get to testing the import of these transactions either tomorrow
morning or Thursday. I don't know yet whether it is possible to import them
in the multiline format with 2 or more splits yet.


Yes, it is possible but you have to be very careful when creating the 
export.



i am working slowly and
documenting everything i do so the developers have enough data to start
identifying where the problems are. 


I am impressed at your attention to detail, D

I think the problem is that once you've worked everything through in 
your beautifully crafted way you're going to reach the same conclusion 
as me.


At that point you should make a mug of tea, swig a beer or whatever is 
appropriate and conclude, the gnc people haven't got it right yet.



Once I understand it I will update the
wiki and then get the Help/Guide docs up to date for the next release.  I
suspect the bug fixes might not make it until the next release comes out


OK, the documentation theory works in two ways: the document can say 
what happens or the document can say what might happen.


We are free software so we can't do the latter, doing the former doesn't 
make sense either as it hasn't been done yet and may never be done.


What do you put in the wiki, Mr David?


If you are only exporting and importing transactions with 2 splits you
should be able to get it to work.


I can make it work with many splits but not many currencies or commodities


I would create a dummy transaction first
and export it, delete it in the book and then reimport it. 


NAUGHTY BOY!  you *know* gnc can't do the round trip of export and 
import.  It doesn't understand itself.



There is an
identified bug in the Export Transactions to CSV where transactions cannot
be exported on the date they are created so it is better to use Export
Transactions from Active Window to export a dummy transaction. Bob Fewel is
already working on a fix for this.


buglist ref please


I normally setup a test datafile to work with while exploring this rather
than use a real datafile so that other transactions don't complicate life.


If the test file I looked at earlier is an example, it is near perfect, 
I can see your thinking, actually, more importantly, I think other 
people can see your thinking.  I'm not the important person because I'm 
looking at another problem that you may not have thought about yet.  I 
do question documenting in such detail a problem that might change or 
not be there in a month or so, D.


I'm more interested in structural stuff at the moment.

--
Wm


___
gnucash-devel mailing list
gnucash-devel@gnucash.org
https://lists.gnucash.org/mailman/listinfo/gnucash-devel

Re: [GNC-dev] Transactions vs Splits

[cicko]

David, that's a very elaborate explanation. I'm sure there will be enough
information once you are through with testing. So far our findings match.
One thing I'd like to note,


David Cousens wrote
> Date,Transaction ID,Number,Description,Notes,Commodity/Currency,Void
> Reason,Action,Memo,Full Account Name,Account Name,Amount With Sym,Amount
> Num.,Reconcile,Reconcile Date,Rate/Price
> 15/11/18,b60da83af9b84334aa2f5e129c23016f,,Transfer with currency
> exchange,,CURRENCY::AUDAssets:Current Assets:Savings Account,Savings
> Account,$100.00,100.00,n,,1.00
> ,Assets:Current Assets:Savings USD,Savings
> USD,-$110.00,-110.00,n,,10/11
> 
> The Price is what should control the currency conversion. 

I find the Price and Amount information redundant, if both are sent. For
example, if they don't match, which one is the relevant one?
If amounts are sent in all splits, it would be better to read the amounts
and calculate the price/rate on import.

I assume your export is from GnuCash so it is good to learn that it exports
the Price element, too. I'll adjust my export to do the same, just in case.

In conclusion, the current state of import would work fine for transactions
in single currency. 

Another issue I had with QIF import was that it was matching accounts by
name, which is not what I wanted. When exporting transactions that use
categories, then expenses in all currencies go to, for example, Dining. QIF
import would always match Dining to Expenses:Dining, which is in EUR, in my
case. So it would always create duplicate accounts after the holidays.
One thing for me to check is if the CSV importer is better in that regard
and will match (or at least remember manually set values) the categories to
the accounts in correct currency (i.e. Expenses:Dining:AUD etc.).



--
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] Transactions vs Splits

[David Cousens]

Alen,

The multicurrency transactions are a problem because at the moment GnuCash
is introducing a spurious unrelated amount into the transaction. For eg I
had a simple $100 AUD debit to an AUD Savings Account with a split to a
Savings USD account for $110 USD. This is correctly exported but when
reimported it introduces an amount of $1000 USD. The following CSV when
imported

Date,Transaction ID,Number,Description,Notes,Commodity/Currency,Void
Reason,Action,Memo,Full Account Name,Account Name,Amount With Sym,Amount
Num.,Reconcile,Reconcile Date,Rate/Price
15/11/18,b60da83af9b84334aa2f5e129c23016f,,Transfer with currency
exchange,,CURRENCY::AUDAssets:Current Assets:Savings Account,Savings
Account,$100.00,100.00,n,,1.00
,Assets:Current Assets:Savings USD,Savings
USD,-$110.00,-110.00,n,,10/11

produces

SavingsAccount_From_AUD_ML_004.png

  
and in the Savings USD account register this produces 2 transactions on
import
Savings_USD_From_AUD_ML_005.png

   
and when you open the first transaction to display the splits
Savings_USD_From_AUD_ML_split1_006.png

  
and the second transaction created is
Savings_USD_From_AUD_ML_split2_007.png

  

The multi-currency import is just not working correctly at all at the
moment.  I'm doing the imports into a pristine data file each time so I can
see exactly what is happening when I vary the import conditions. I was
waiting to test out a few more possibly related things for multiline imports
of transactions between accounts in the same currency before reporting it as
a bug. This will help with isolating where it is in the code.

The Price is what should control the currency conversion. The book currency
I am using is AUD so a price of 10/11 is the conversion rate from  USD to
AUD in my case for the second split.  The price on the  split 
 to the AUD Savings Account is 1.00  which is as expected.  

This is how the account registers appeared before exporting the transaction.
The way it is supposed to work is if I look at the AUD "Savings Account"
register all amounts are rendered in the currency for that account, so the
register appears as Savings_Account-GnuCash_Initial_013.png

  
and if I look at the "Savings USD" account register all amounts are in USD
as follows
Savings_USD-GnuCash_Initial_015.png

 
.
 I would be very cautious about using the export/import on multicurrency
transactions at the moment

I got into this because I some changes to the import matcher late last year
and while testing them out noticed that the CSV documentation was way out of
date when I was documenting my changes.  I started to rewrite the
documentation but then found anomolies in how it worked I couldn't
understand after the release of V3. Geert really hadn't had time to debug it
fully with the load of bug fixes after the v3 release so I undertook to go
through it methodically initially mainly to look at the problem that GnuCash
v3.2 could not import its own exported files, particularly in the multiline
format. I'm preparing a documented report to identify the  bugs in a
reproducible manner. If Geert is unable to tackle fixing the bugs then when
I finish doing that, then I will start working on fixes for them and
updating the documentation.

The CSV importer still seems to be able to work for what was its original
functionality, importing CSV exports form bank accounts etc where single
line mode is usually adequate.  My bank exports categories in the record
which I can setup so I have these set to match the GnuCash Income and
Expense accounts I use. The majority of my transactions are usually matched
on import but I have never been sure whether it is using the categories or
the Bayesian matching algorithm or both. 

I suspect the latter as some of the transactions I regularly have problems
with are ones where the provider uses a different reference number in the
description field each time. Their number has a fixed part with a sequential
number tacked on the end. The matcher algorithm tokenizes the information in
the description field but it can't separate the fixed part of the number
from the variable part so it rejects the match. The rest of the description
field is not sufficient to override this. I'll have to delete the data file
for te bayesian matcher between each import when i get around to testing how
that works in detail.





-
David Cousens
--
Sent from: http://gnucash.1415818.n4.nabble.com/GnuCash-Dev-f1435356.html

Re: [GNC-dev] Transactions vs Splits

[cicko]

cicko wrote
> However, if the price is taken into account, then perhaps the Price field
> may have some role there?

Hm, the Price column definitely affects the destination amount but not in a
way I can understand. Using

111,2019-03-06,,,15,"HSBC savings",,0
111,2019-03-06,,9.8788,,"cash, EUR",,2

where the last column is Price, the result is

 





--
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] Transactions vs Splits

[cicko]

cicko wrote
> If this works as expected, then multi-currency transfers should not be an
> issue at all. Each split would specify the amount so no need for GnuCash
> to
> fiddle with prices. 

This works great for single-currency transactions. Everything fits, as
specified in CSV records.

There are issues, as hinted by David, with multi-currency transfers. This
makes no sense to me at the moment.
During the import, one of my transfers was matched but the other not. This
may be due to the fact that I have matched the first one during an earlier
import attempt.

Here is the sample CSV content:

26,2015-09-12,"Biss Tours",,33.25,"cash, EUR",
26,2015-09-12,"Biss Tours",12,,"Vacation:Travel",
26,2015-09-12,"Biss Tours",21.25,,"Vacation",
33,2015-09-13,"Billa",,15.26,"cash, EUR",
33,2015-09-13,"Billa",15.26,,"Food:Groceries",
110,2019-03-06,,,10,"cash, EUR",
110,2019-03-06,,19.5925,,"cash, BAM",
111,2019-03-06,,,15,"HSBC savings",
111,2019-03-06,,9.8788,,"cash, EUR",

If the multi-currency transfers were imported as specified, that would be
perfect. What happens instead is this:
 

The Cash EUR, €10 -> Cash BAM, KM19.59 transfer imports both splits (10 and
19.59) but adds additional records so that the end-result of the transfer
turns out to be 10:10.

The HSBC, $15 -> Cash Eur, €9.88, does something similar, adding "only"
€0.97 to the destination account.

I'm not sure why these happen and would appreciate some feedback before
reporting it as a bug. 

My suggestion, as written above, would be simply to leave the multi-currency
transfers as-is. There is really no way to tell if they match due to the
Price element. 
However, if the price is taken into account, then perhaps the Price field
may have some role there?



--
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] Transactions vs Splits

[cicko]

OK, now I see that I'll need to use the Multi-split option always, to cover
for the eventual real multi-split in the source. So, I'll be focusing on
that option.
The good news is, when multi-split is selected, the Transaction ID field
becomes available for mapping. That helps a lot as the matcher will know
which splits to group together into a transaction.


David Cousens wrote
>>I see that, with Multi-split checked, I can no longer select the Transfer 
>>Account destination field. This is a dead end for my current approach,
> using 
>>Transfer Account. 
> 
> With a multisplit multiline record there should be no need. GnuCash should
> interpret the following lines as transfer accounts

Oh, I think I'm getting the idea. In this case, the splits rows represent
exactly what will end up in GnuCash and need only one Account per split.
Yes, that makes sense. 


David Cousens wrote
> I suspect the mismatches you get are a problem with GnuCash not
> interpreting
> the multiline splits correctly. 

Nah, I believe this is because I was still using the
one-split-one-transaction approach in writing CSV.


David Cousens wrote
> The account selection in the first window is AFAIK intended to select an
> account for all transactions to be imported into. ...

This part I believe I now understand and agree with you, except



The notation transfer account is not one as an accountant that I really
like. It only makes sense when transferring funds between two  asset (or
liability accounts) but many software developers and users have adopted it
so it is now part of the language. My view of a transaction is that it
consist of two or more splits each of which has a target account which is
debited or credited by that split.


David Cousens wrote
> Where the multiline capability comes into its own is in dealing with
> transactions with 3 or more splits and it really only needs to be used in
> this case.

Now that I understand how Multi-split are supposed to work (I still need to
confirm it in practice), I find the multi-split a better option always, as
one can specify the Transaction Id and explicitly tell GnuCash which splits
go together.
So, as I mentioned at the top, I will probably use that as the default,
unless I run into some other issues.

If this works as expected, then multi-currency transfers should not be an
issue at all. Each split would specify the amount so no need for GnuCash to
fiddle with prices. 
I will test this as soon as I adapt my code to always produce multiple
splits instead of using Transfer Account.



--
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] Transactions vs Splits

[David Cousens]

Hi Alen,

>I see that, with Multi-split checked, I can no longer select the Transfer 
>Account destination field. This is a dead end for my current approach,
using 
>Transfer Account. 

With a multisplit multiline record there should be no need. GnuCash should
interpret the following lines as transfer accounts

I suspect the mismatches you get are a problem with GnuCash not interpreting
the multiline splits correctly. It is why I am doing a structured testing of
the import capability.

>At the last screen, I now get all mismatches as the destination account is 
>missing and I'm prompted to select one. This makes no sense as I could have 
>simply selected that at the first screen and save my matcher schema. 
>Should I raise this as a bug or am I misunderstanding how this is supposed 
>to work? 

The account selection in the first window is AFAIK intended to select an
account for all transactions to be imported into. It is intended I think for
the case where the records to be imported do not specify the account they
are being imported to. Where the transaction has only 2 splits and both
accounts which are the targets of the splits are specified then the single
line format with both the account and transfer account are specified  is
appropriate as you have done.  You could equally treat "Cash" as the
transfer account and "Food:Dining out" as the account being impoorted to if
you swapped the deposit and withdrawal columns as well.  The mismatches seem
to indicate that GnuCash is not recognizing the account in the second line
as the transfer account which it should. You can raise it as a bug if you
wish.  I will be testing all the import settings fairly methodically in in
the next few days so if you don't I will in any case. If you have already I
can just add the results of my testing as a comment. 

The notation transfer account is not one as an accountant that I really
like. It only makes sense when transferring funds between two  asset (or
liability accounts) but many software developers and users have adopted it
so it is now part of the language. My view of a transaction is that it
consist of two or more splits each of which has a target account which is
debited or credited by that split.

Where the multiline capability comes into its own is in dealing with
transactions with 3 or more splits and it really only needs to be used in
this case.

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] Transactions vs Splits

[cicko]

This helps:

 




--
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] Transactions vs Splits

[cicko]

Thanks a lot, David. I have completely missed that checkbox. Will try it out.

Also, to comment on your reference to single- vs multi-split import. I saw
the Transfer Account field available and decided to use it from the start.
That way I don't need two splits for simple transactions, meaning this is
quite suitable more category-like approach, not double accounting. Single
CSV rows match what is available though QIF, for example. Hence I'm not even
trying the case that you describe, and I guess completely skipping the
matcher for two split rows in one transaction.

My test records look like this:
date, description/payee, deposit, withdrawal, account, category/transfer
account
2016-01-27,"Falafel",,4,"cash","Food:Dining out"

This fits more to my source schema. Note that I'm writing a library that
will allow me to pull records from MoneyManagerEx database, which is based
on categories.

I see that, with Multi-split checked, I can no longer select the Transfer
Account destination field. This is a dead end for my current approach, using
Transfer Account. 
At the last screen, I now get all mismatches as the destination account is
missing and I'm prompted to select one. This makes no sense as I could have
simply selected that at the first screen and save my matcher schema.
Should I raise this as a bug or am I misunderstanding how this is supposed
to work?

Thanks a lot for your assistance. Note that there's also no rush from my
side. For me, this is a hobby project that will, in the end, save me some
time in automating the transaction transfer from my phone app to GnuCash. So
don't feel pressured on that end, either.



--
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] Transactions vs Splits

[David Cousens]

Alen

When you are importing a multiline exported transaction you should see a
multiline checkbox at the bottom of the first window. You may get multiline
import to work if you select this as it tells GnuCash to look for following
lines which are part of a single transaction.

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] Transactions vs Splits

[David Cousens]

Alen,

Each exported transaction occupies at least 2 or more lines, one for each
split in the transaction.

The following is an exported multiline format single transaction for a
credit of $100 to the Savings account with splits which debit the Checking
account by $50 and the cash in Wallet account by $50. The formatting may not
survive the posting but I will separate each line with two newlines:

Date,Transaction ID,Number,Description,Notes,Commodity/Currency,Void
Reason,Action,Memo,Full Account Name,Account Name,Amount With Sym,Amount
Num.,Reconcile,Reconcile Date,Rate/Price

04/03/2019,2a9b1f7950ab4f388e2c4ae3ac8fe1c3,,Tfr to Cash in 
Wallet,,CURRENCY::AUDAssets:Current Assets:Cash in Wallet,Cash in
Wallet,$50.00,50.00,n,,1.00

,Assets:Current Assets:Checking Account,Checking
Account,$50.00,50.00,n,,1.00

,Assets:Current Assets:Savings Account,Savings
Account,-$100.00,-100.00,n,,1.00


If during export you select the Simple Layout in the first window of the
export procedure you will get a single line format but this does not work
for transactions which have more than 2 splits e.g. it only exports the
following:

Date,Account Name,Number,Description,Full Category Path,Reconcile,Amount
With Sym,Amount Num.,Rate/Price

04/03/2019,Assets:Current Assets:Savings Account,,Tfr to Cash in  Wallet,--
Split Transaction --,n,-$100.00,-100.00,1.00

It cannot deal with more than 2 splits in a transaction. This is why Geert
has been setting up the multi-line export and import. 

I will get to testing the import of these transactions either tomorrow
morning or Thursday. I don't know yet whether it is possible to import them
in the multiline format with 2 or more splits yet. i am working slowly and
documenting everything i do so the developers have enough data to start
identifying where the problems are. Once I understand it I will update the
wiki and then get the Help/Guide docs up to date for the next release.  I
suspect the bug fixes might not make it until the next release comes out

If you are only exporting and importing transactions with 2 splits you
should be able to get it to work. I would create a dummy transaction first
and export it, delete it in the book and then reimport it. There is an
identified bug in the Export Transactions to CSV where transactions cannot
be exported on the date they are created so it is better to use Export
Transactions from Active Window to export a dummy transaction. Bob Fewel is
already working on a fix for this. 

I normally setup a test datafile to work with while exploring this rather
than use a real datafile so that other transactions don't complicate life.

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

[GNC-dev] Transactions vs Splits

[cicko]

What is meant by "multiline" in this case?

I've tried importing a CSV file with multiple records and it seems to be
going fine.
However, I'd like to keep the split structure in place but am not sure if
that is possible. Currently I create rows for each split in a transaction
and so it makes sense that they are imported as separate transaction in
GnuCash.

Is there a way to keep splits within one transaction during the CSV import?



--
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