CSV Importing transactions in wrong direction

[sotornien]

Hi all,

I don't know if this is a bug or if I'm missing something. On my CSV transactions are split into 2 rows. First Row is transfering from Account A to B, and second row SHOULD do the other direction (B to A). This is exactly what the CSV says, but in one Row the source and destination account gets switched.

This is my CSV:

859;DE42XXXXXXXXXX158505;COXXXXXXX55;Tagesgeld PLUS;DE80XXXXXXXXXX158500;COXXXXXXX55;;100;01.09.2015;01.09.2015;KONTOÜBERTRAG;GIROKONTO UMBUCHUNG;2.129,17;;NONREF;;;END-TO-END-REF.: NICHT ANGEGEBEN Ref. GQXXXXXXX2235600/1 ;;;
856;DE80XXXXXXXXXX158500;COXXXXXXX55;Girokonto;DE42XXXXXXXXXX158505;COXXXXXXX55;;-100;01.09.2015;01.09.2015;ÜBERTRAG/ÜBERWEISUNG;GIROKONTO UMBUCHUNG;2.144,49;;NONREF;;;END-TO-END-REF.: NICHT ANGEGEBEN Ref. GQXXXXXXX2235600/2 ;;;

This is what I get:

I've checked the logs from the data importer (no mappings active) and here I see the wrong mapping was done allready by the importer:

{"group_title":null,"error_if_duplicate_hash":true,"transactions":[{"type":"transfer","date":"2015-09-01 00:00:00","currency_id":1,"currency_code":null,"amount":"100.000000000000","description":"END-TO-END-REF.: NICHT ANGEGEBEN Ref. GQXXXXXXX2235600/1","source_id":5,"source_name":null,"destination_id":101,"destination_name":null,"internal_reference":"859","source_iban":null,"source_bic":"COXXXXXXX55","destination_iban":null,"destination_bic":"COXXXXXXX55","process_date":"2015-09-01 00:00:00","category_name":"","original_source":"jc5-data-import-v0.2.0","tags":[],"source_number":null,"destination_number":null}]}
{"group_title":null,"error_if_duplicate_hash":true,"transactions":[{"type":"transfer","date":"2015-09-01 00:00:00","currency_id":1,"currency_code":null,"amount":"100.000000000000","description":"END-TO-END-REF.: NICHT ANGEGEBEN Ref. GQXXXXXXX2235600/2","source_id":5,"source_name":null,"destination_id":101,"destination_name":null,"internal_reference":"856","source_iban":null,"source_bic":"COXXXXXXX55","destination_iban":null,"destination_bic":"COXXXXXXX55","process_date":"2015-09-01 00:00:00","category_name":"","original_source":"jc5-data-import-v0.2.0","tags":[],"source_number":null,"destination_number":null}]}

Non transactions getting correctly placed in the right Account. Do I miss something?

Thank you

[JC5]

Could you share your config file?

[sotornien]

Here we go

{
    "date": "d.m.Y",
    "default_account": 5,
    "delimiter": "semicolon",
    "headers": false,
    "rules": true,
    "skip_form": false,
    "add_import_tag": false,
    "roles": [
        "internal_reference",
        "account-iban",
        "account-bic",
        "account-name",
        "opposing-iban",
        "opposing-bic",
        "opposing-name",
        "amount",
        "date_transaction",
        "date_process",
        "_ignore",
        "description",
        "_ignore",
        "_ignore",
        "_ignore",
        "category-name",
        "_ignore",
        "description",
        "_ignore",
        "_ignore",
        "_ignore"
    ],
    "do_mapping": [
        false,
        false,
        false,
        false,
        false,
        false,
        false,
        false,
        false,
        false,
        false,
        false,
        false,
        false,
        false,
        false,
        false,
        false,
        false,
        false,
        false
    ],
    "mapping": {},
    "duplicate_detection_method": "classic",
    "ignore_duplicate_lines": true,
    "ignore_duplicate_transactions": true,
    "unique_column_index": 0,
    "unique_column_type": "internal_reference",
    "version": 3,
    "flow": "csv",
    "identifier": "0",
    "connection": "0",
    "ignore_spectre_categories": false,
    "map_all_data": false,
    "accounts": [],
    "date_range": "",
    "date_range_number": 0,
    "date_range_unit": "",
    "date_not_before": "",
    "date_not_after": "",
    "nordigen_country": "",
    "nordigen_bank": "",
    "nordigen_requisitions": [],
    "conversion": true
}

[JC5]

Nice find. It's a bug. I pushed a fix to the develop branch and to the develop Docker image. Would you be able to test this?

[sotornien]

Thanks JC5,

it changed a bit. Now they have changed from transaction to deposit/withdraw and the accounts were created instead of using the existing assets, but the direction is correct now :)

.

[JC5]

Mmmm. A quick check. In my test environment I matched them on the iban only. Is that the case in your installation too?

[sotornien]

Currently no, I disabled any mapping to avoid possible side effects. I tried it now again with mappings, which results unfortunately in the initial problem again.

{
    "date": "d.m.Y",
    "default_account": 5,
    "delimiter": "semicolon",
    "headers": false,
    "rules": true,
    "skip_form": false,
    "add_import_tag": false,
    "roles": [
        "internal_reference",
        "account-iban",
        "account-bic",
        "account-name",
        "opposing-iban",
        "opposing-bic",
        "opposing-name",
        "amount",
        "date_transaction",
        "date_process",
        "_ignore",
        "description",
        "_ignore",
        "_ignore",
        "_ignore",
        "category-name",
        "_ignore",
        "description",
        "_ignore",
        "_ignore",
        "_ignore"
    ],
    "do_mapping": {
        "1": true,
        "4": true,
        "0": false,
        "2": false,
        "3": false,
        "5": false,
        "6": false,
        "7": false,
        "8": false,
        "9": false,
        "10": false,
        "11": false,
        "12": false,
        "13": false,
        "14": false,
        "15": false,
        "16": false,
        "17": false,
        "18": false,
        "19": false,
        "20": false
    },
    "mapping": {
        "1": {
            "DE42XXXXXXXXXXXXXX8505": 101,
            "DE67XXXXXXXXXXXXXX8540": 701,
            "DE80XXXXXXXXXXXXXX8500": 5
        },
        "4": {
            "DE42XXXXXXXXXXXXXX8505": 101,
            "DE67XXXXXXXXXXXXXX8540": 701,
            "DE80XXXXXXXXXXXXXX8500": 5
        }
    },
    "duplicate_detection_method": "classic",
    "ignore_duplicate_lines": true,
    "ignore_duplicate_transactions": true,
    "unique_column_index": 0,
    "unique_column_type": "internal_reference",
    "version": 3,
    "flow": "csv",
    "identifier": "0",
    "connection": "0",
    "ignore_spectre_categories": false,
    "map_all_data": false,
    "accounts": [],
    "date_range": "",
    "date_range_number": 0,
    "date_range_unit": "",
    "date_not_before": "",
    "date_not_after": "",
    "nordigen_country": "",
    "nordigen_bank": "",
    "nordigen_requisitions": [],
    "conversion": true
}

[JC5]

My apologies, I misconfigured the build. Could you pull the latest develop image and try again? Look for:

Build number:  3
Build date:    05-12-2021 16:45:37 CET

[sotornien]

Thanks JC5,
I deployed again with the develop tag and a new image was pulled.

Now in entrypoint.sh for Firefly III Data Importer
Script:        1.5 (2021-12-05)
User:          'root'
Group:         'root'
Working dir:   '/var/www/html'
Build number:  3
Build date:    05-12-2021 16:45:37 CET
Firefly III data importer v0.2.0
PHP: cli 8.0.12 Linux

But unfortunately this didn't changed the Importer issue.

[JC5]

After replacing your anonimised with fake IBAN's (present in my dev account, and linked to asset accounts), I get the correct result.

In your log files, please be on the lookout for this line, which is an indication my change was successful.

Transaction is a transfer, and amount is positive, must reverse accounts again.

In my case, it happened like so:

[2021-12-05 18:51:56] local.DEBUG: Now in determineType::determineType("asset", "asset")
[2021-12-05 18:51:56] local.DEBUG: Check config for "transaction_types.account_to_transaction.asset.asset" and found "transfer". Returning "transfer"
[2021-12-05 18:51:56] local.DEBUG: Transaction type is set to "transfer"
[2021-12-05 18:51:56] local.DEBUG: 100.000000000000 is positive and type is "transfer".
[2021-12-05 18:51:56] local.DEBUG: Now in determineType::determineType("asset", "asset")
[2021-12-05 18:51:56] local.DEBUG: Check config for "transaction_types.account_to_transaction.asset.asset" and found "transfer". Returning "transfer"
[2021-12-05 18:51:56] local.DEBUG: Transaction is a transfer, and amount is positive, must reverse accounts again.

[sotornien]

I don't have these lines:

[2021-12-05 20:12:27] production.DEBUG: Now in determineType::determineType("asset", "asset")  
[2021-12-05 20:12:27] production.DEBUG: Check config for "transaction_types.account_to_transaction.asset.asset" and found "transfer". Returning "transfer"  
[2021-12-05 20:12:27] production.DEBUG: 100.000000000000 is positive.  
[2021-12-05 20:12:27] production.DEBUG: Now in determineType::determineType("asset", "asset")  
[2021-12-05 20:12:27] production.DEBUG: Check config for "transaction_types.account_to_transaction.asset.asset" and found "transfer". Returning "transfer"  
[2021-12-05 20:12:27] production.DEBUG: Now running task App\Services\CSV\Conversion\Task\PositiveAmount  
[2021-12-05 20:12:27] production.DEBUG: Now running task App\Services\CSV\Conversion\Task\EmptyDescription  
[2021-12-05 20:12:27] production.DEBUG: Now running task App\Services\CSV\Conversion\Task\EmptyAccounts  
[2021-12-05 20:12:27] production.DEBUG: Now in EmptyAccounts::process()  
[2021-12-05 20:12:27] production.DEBUG: Now processing transaction 1 of 1  
[2021-12-05 20:12:27] production.DEBUG: Now in EmptyAccounts::processTransaction()  
[2021-12-05 20:12:27] production.DEBUG: Add message on index #2 (line no. 3): Converted CSV line 3 into a transaction.  
[2021-12-05 20:12:27] production.DEBUG: Now in storeConversionStatus(conv-tR98feYdaxrm): conv_running  
[2021-12-05 20:12:27] production.INFO: Now processing line 4/26.  
[2021-12-05 20:12:27] production.DEBUG: Now in App\Services\CSV\Conversion\Routine\PseudoTransactionProcessor::processPseudoLine  
[2021-12-05 20:12:27] production.DEBUG: Now running task App\Services\CSV\Conversion\Task\Amount  
[2021-12-05 20:12:27] production.DEBUG: Now at the start of processAmount("-100")  
[2021-12-05 20:12:27] production.DEBUG: Transaction["amount"] value is not NULL, assume this is the correct value.  
[2021-12-05 20:12:27] production.DEBUG: Amount is now -100.000000000000.  
[2021-12-05 20:12:27] production.DEBUG: Amount -100.000000000000 is negative, so this is probably a withdrawal.  
[2021-12-05 20:12:27] production.DEBUG: Now running task App\Services\CSV\Conversion\Task\Tags  
[2021-12-05 20:12:27] production.DEBUG: Now running task App\Services\CSV\Conversion\Task\Currency  
[2021-12-05 20:12:27] production.DEBUG: Set currency to 1 because it was NULL or empty.  
[2021-12-05 20:12:27] production.DEBUG: Now running task App\Services\CSV\Conversion\Task\Accounts  
[2021-12-05 20:12:27] production.DEBUG: Now in Accounts::process()  
[2021-12-05 20:12:27] production.DEBUG: Now processing transaction 1 of 1  
[2021-12-05 20:12:27] production.DEBUG: Now in Accounts::processTransaction()  
[2021-12-05 20:12:27] production.DEBUG: Now in findAccount {"transaction_type":"withdrawal","id":null,"name":"Girokonto","iban":"DE80XXXXXXXXXXXXX58500","number":null,"bic":"COBADEHD055","direction":"source"} 
[2021-12-05 20:12:27] production.DEBUG: Default account is #1 ("Girokonto")  
[2021-12-05 20:12:27] production.DEBUG: Find by IBAN.  
[2021-12-05 20:12:27] production.DEBUG: Going to search account with IBAN "DE80XXXXXXXXXXXXX58500"  
[2021-12-05 20:12:27] production.DEBUG: [a] Found asset account #1 based on IBAN "DE80XXXXXXXXXXXXX58500"  
[2021-12-05 20:12:27] production.DEBUG: Result of findByIBAN is not null, returning: {"id":1,"name":"Girokonto","type":"asset","iban":"DE80XXXXXXXXXXXXX58500","number":"XXXXXXX00","bic":"COBADEHD055","currency_code":"EUR"} 
[2021-12-05 20:12:27] production.DEBUG: Now in findAccount {"transaction_type":"withdrawal","id":null,"name":"","iban":"DE42XXXXXXXXXXXXX58505","number":null,"bic":"COBADEHD055","direction":"destination"} 
[2021-12-05 20:12:27] production.DEBUG: findAccount() default account is NULL.  
[2021-12-05 20:12:27] production.DEBUG: Find by IBAN.  
[2021-12-05 20:12:27] production.DEBUG: Going to search account with IBAN "DE42XXXXXXXXXXXXX58505"  
[2021-12-05 20:12:27] production.DEBUG: [a] Found asset account #3 based on IBAN "DE42XXXXXXXXXXXXX58505"  
[2021-12-05 20:12:27] production.DEBUG: Result of findByIBAN is not null, returning: {"id":3,"name":"Tagesgeld Plus","type":"asset","iban":"DE42XXXXXXXXXXXXX58505","number":"XXXXXXX05","bic":"COBADEHD055","currency_code":"EUR"} 

I now redeployed Firefly completely (removed the Container with its Volumes and dropped the DB). The only thing I did then was creating the Asset Accounts:

Then I imported following csv:
transfer-imports - Kopie.csv

What can I possible do wrong? :(

[JC5]

It looks like you're not running the very latest data importer. The fix is included in the develop tag. I can tell because in your logs, you see this message, which is no longer there in the correct version:

OLD version:

[2021-12-05 20:12:27] production.DEBUG: 100.000000000000 is positive.  

NEW version:

[2021-12-05 18:51:56] local.DEBUG: 100.000000000000 is positive and type is "transfer".

Run docker pull fireflyiii/data-importer:develop and make sure fireflyiii/data-importer:develop is the tag you use.

[sotornien]

Sorry I didn't know that I had to pull again. Now everything is like expected. I imported 2 Years now and its on the cent correct. Thanks (also for #5344), great Job

[Tamba3998]

(Posting this here because it looks like it might be related, but I can make it a separate issue if you want me to)
Using Firefly III Data Import Tool, v0.4.1.
Transfers between an asset account A and an opposing account B always get turned into a transfer from A into B, regardless of whether the amount is positive or negative.

Here's a minimal example to reproduce this:

• Create two asset accounts A and B with the currency set to €
• Import this
  transfer-test.csv
• Use this config file
  import_config_2021-12-25.txt

Expected result: Both accounts should be back at 0 (1000 in, 1000 out for both accounts).
Actual result: A is at -2000, B is at +2000

relevant parts from the log:

[2021-12-25 11:29:27] production.DEBUG: Amount -1000.000000000000 is negative, so this is probably a withdrawal.  
[...]
[2021-12-25 11:29:27] production.DEBUG: Transaction type is set to "transfer"  
[...]
[2021-12-25 11:29:27] production.DEBUG: Final transaction:  {"group_title":null,"error_if_duplicate_hash":true,"transactions":[{"type":"transfer","date":"2021-12-25 00:00:00","currency_id":1,"currency_code":null,"amount":"1000.000000000000","description":"A to B","source_id":248,"source_name":null,"destination_id":249,"destination_name":null,"original_source":"jc5-data-import-v0.4.1","tags":[],"source_iban":null,"source_number":null,"source_bic":null,"destination_iban":null,"destination_number":null,"destination_bic":null}]} 


-----------------------


[2021-12-25 11:29:27] production.DEBUG: Amount is now 1000.000000000000.  
[2021-12-25 11:29:27] production.DEBUG: Amount 1000.000000000000 is positive, so this is probably a deposit.  
[...]
[2021-12-25 11:29:28] production.DEBUG: Transaction type is set to "transfer"  
[2021-12-25 11:29:28] production.DEBUG: 1000.000000000000 is positive and type is "transfer".  
[...]
[2021-12-25 11:29:28] production.DEBUG: Transaction is a transfer, and amount is positive, must reverse accounts again.  
[...]
[2021-12-25 11:29:28] production.DEBUG: Final transaction:  {"group_title":null,"error_if_duplicate_hash":true,"transactions":[{"type":"transfer","date":"2021-12-25 00:00:00","currency_id":1,"currency_code":null,"amount":"1000.000000000000","description":"B to A","source_id":248,"source_name":null,"destination_id":249,"destination_name":null,"original_source":"jc5-data-import-v0.4.1","tags":[],"source_iban":null,"source_number":null,"source_bic":null,"destination_iban":null,"destination_number":null,"destination_bic":null}]} 

full log of that import:
log.txt

[JC5]

Use "amount (negated)" instead of "amount".

[Tamba3998]

Same result. One account is at -2000, the other at +2000.

[JC5]

I see it, nice find. Probably caused by my previous fix, so reported in the right place. Stay tuned.

[JC5]

Not sure if you use Docker but updated code is in the develop branch and develop image

Now in entrypoint.sh for Firefly III Data Importer
Script:        1.5 (2021-12-05)
User:          'root'
Group:         'root'
Working dir:   '/var/www/html'
Build number:  38
Build date:    25-12-2021 15:37:11 CET
Firefly III data importer v0.4.2
PHP: cli 8.0.13 Linux

[Tamba3998]

I do use docker. Build 38 works both with that test case and with my real data. Thanks for the quick fix.