Importer is missing fields

[Obbi89]

While importing transactions from a csv exported from my bank I found several fields are missing

https://github.com/firefly-iii/firefly-iii/blob/develop/config/csv.php#L276
For example opposing-account-bic, which is part of the csv I exported.
So it would be nice if that information could be automatically collected, too.

Additionally It would be nice to re-use the same field for multiple mappings.
For example the csv files I get from my banking app contain booking-date and interest-date.
I would then for example use booking-date as booking-date and as date for the transaction.

And
On my German banking information some fields are split or contain additional information,
that is currently missing in the app. Concating multiple fields into the description field would be helpful there.

Because otherwise the date would be set to the import date which is confusing when looking through the transactions.

[JC5]

Hi,

Thanks for submitting this! Let me reply point by point.

• Adding the BIC is certainly possible, and I will try to add it in the next release.
• I'm afraid reusing fields will not be possible. If you wish to reuse fields, you must add them twice to the import file. This is to reduce the complexity of the import routine and user interface
• Fields such as the description and the notes are already automatically concatenated. Meaning, if you select multiple fields to be the description, they will be appended automatically. Other fields such as dates cannot be joined. If you feel other fields would benefit from this feature as well, let me know.

I'm not sure what you mean by your last remark. Is the import routine unable to match certain date fields?

[Obbi89]

I guess adding a sample is the best way here:

"Auftragskonto";"Buchungstag";"Valutadatum";"Buchungstext";"Verwendungszweck";"Glaeubiger ID";"Mandatsreferenz";"Kundenreferenz (End-to-End)";"Sammlerreferenz";"Lastschrift Ursprungsbetrag";"Auslagenersatz Ruecklastschrift";"Beguenstigter/Zahlungspflichtiger";"Kontonummer/IBAN";"BIC (SWIFT-Code)";"Betrag";"Waehrung";"Info"
"1234567890";"08.03.18";"08.03.18";"FOLGELASTSCHRIFT";"Some data some data";"DEZZZ0000000000000000001";"4WTASJAKA";"1010102202 Id data data";"";"";"";"I receive all your money";"DE9999090909091111";"DEUTDEFFXXX";"-40,83";"EUR";"Umsatz gebucht"

{
    "has-headers": true,
    "date-format": "d.m.y",
    "delimiter": ";",
    "import-account": 1,
    "specifics": [],
    "column-count": 17,
    "column-roles": [
        "account-number",
        "date-transaction",
        "date-interest",
        "_ignore",
        "description",
        "opposing-id",
        "_ignore",
        "sepa-ct-id",
        "_ignore",
        "_ignore",
        "_ignore",
        "opposing-name",
        "opposing-iban",
        "_ignore",
        "amount",
        "currency-code",
        "note"
    ],
    "column-do-mapping": [
        false,
        false,
        false,
        false,
        false,
        false,
        false,
        false,
        false,
        false,
        false,
        false,
        false,
        false,
        false,
        false,
        false
    ],
    "column-mapping-config": [],
    "file-type": "csv",
    "has-config-file": true,
    "apply-rules": true,
    "match-bills": true,
    "auto-start": false,
    "column-roles-complete": false,
    "column-mapping-complete": false,
    "initial-config-complete": false,
    "has-file-upload": false
}

As you can see I can assign the different date fields. But I would want the system to default to one of those fields for the date column if it is not specified in the import settings. Otherwise this transaction would be imported with date set to NOW() which is incorrect.

[JC5]

Is the Glaeubiger ID your IBAN or that of the person on the other site? Because the "opposing-id" field you selected there will never match: it is designed to match Firefly III's internal ID. (see /accounts/show/123).

The system can't do a fallback, I'm afraid. When the date is missing it can't use other fields to fill in the date.

[Obbi89]

Gläubiger ID and Mandatsreferenz together build a unique Identifier for a SEPA-debit allowance.
They are information for an allowance you gave person/company X to pull money from your banking accounts. It's how 90% of bill payments with private entities work around here.
Only companies and some people do manual transactions to pay their bills.

The Lastschrift is a concept that is not in ffly3. So I guess I append it on the comment field.
Normally that stuff would have to be part of the expense account, since it is the information I would use to identify with which sepa-debit did someone pull that money from my account.

Since I realized those fields to be internal, maybe it would be a good idea to mark those in the dropdown with (internal)

[JC5]

I think it's time to add those SEPA fields as custom meta fields. I'll make a new issue to do some research. For the Lastschrift, I'll have to do some research where it's coming from. Is it a SEPA field as well?

[Obbi89]

Ok I'm gonna quote from an official document link [1] is below:
This is the formal document and not a digital representation.
The digital formats follow. ISO 20022 [3] is the standardization for financial messages.

The Mandate is defined in section 4.1. EPC guidance on the visual
presentation of Mandates is provided in the Guidelines for the Appearance
of Mandates.

The Mandate document must contain the field identifiers, followed by the
necessary blank space in which to fill the required data items. The
identifiers on the Mandates must be in at least one and up to three
languages of the country of residence of the Debtor, together with English
if the Creditor is not able to determine with reasonable certainty the
language of the Debtor in advance of the Mandate being created. It can be
issued in a personalised way by the Creditor, already containing the data
items specific for the Creditor.

The design of Mandates must comply with the requirements set out below.
The Scheme does not standardise the font or colours or format of the
Mandate or the order of the attributes used for the Mandate, although the
Creditor should always ensure that the Mandate information is clearly
legible. The reverse side of a Mandate must not set out any information
that might be misunderstood by the Debtor to be part of the Mandate.
The Scheme requires the Mandate to have a clear heading entitled “SEPA
Direct Debit Mandate”. The presence of the word “SEPA” is mandatory in
the heading.

The following attributes are to be contained within the Mandate:

Mandate attributes:

• Unique Mandate reference ⭐ (Mandatsreferenz)
• Name of the Debtor
• Address of the Debtor (only mandatory when the Creditor Bank or
  the Debtor Bank is located in a non-EEA SEPA country or territory)
• Postal code/city of the Debtor
• Debtor’s country of residence
• Debtor’s account number IBAN ⭐ (well in my case account number)
• The BIC code of the Debtor Bank (own bic is not in my export)
• Creditor company name (name of opposing bank)
• Creditor’s identifier ⭐ (Gläubiger-Identifikationsnummer)
• Creditor’s address street and number
• Creditor’s postal code and city
• Country of the Creditor
• Type of payment (recurring, one-off)
• Signature place and time
• Signature(s)
  Additional attributes for information only:
• Debtor identification code
• Name of the Debtor Reference Party
• Identification code of the Debtor Reference Party
• Name of the Creditor Reference Party
• Identification code of the Creditor Reference Party
• Underlying contract identifier
• Contract description

BPC016-06 SEPA Core Direct Debit Scheme Rulebook 2017 version 1.1 Page 66 – 18 October 2017
The name of these fields in order to assist the Debtor while filling in the
Mandate.

The legal text in the heading (the authorisation and the Refund right) and
for the two-signature field.
For Creditors who include a Mandate within a publication i.e. magazine /
journal the Mandate must still hold the above information.

Of course not all of those are useful in a digital form. Especially the signature fields.
I marked those with stars I could identify in my export.

Except from the SEPA fields. Here are all other fields + explanation:
The bank excerpt ist camt.054 which seems to be the default format used for bank to customer communication. I added the camt.054 reference [2]. This thing is quite complex and I guess just adding stuff that people stumble upon might be enough.

• Auftragskonto (my account)
• Buchungstag (date of booking)
• Valutadatum (date of interest)
• Buchungstext (booking text) (like. direct debit, bankwire, ...)
• Verwendungszweck (usage)
• Glaeubiger ID (SEPA field)
• Mandatsreferenz (SEPA field)
• Kundenreferenz (End-to-End) (SEPA field, not sure which though)
• Sammlerreferenz (Collector reference, if p.ex. paypal collects money for someone else...
• Lastschrift Ursprungsbetrag
• Auslagenersatz Ruecklastschrift
• Beguenstigter/Zahlungspflichtiger (creditor/debtor depending if the transaction is positive or negative amount)
• Kontonummer/IBAN
• BIC (SWIFT-Code)
• Betrag (amount)
• Waehrung (currency)
• Info

[1] https://www.europeanpaymentscouncil.eu/sites/default/files/kb/file/2017-10/EPC016-06%20SDD%20Core%20Rulebook%202017%20version%201.1.pdf
[2] https://www.iso20022.org/sites/default/files/documents/general/ISO20022_MDR_BankToCustomerCashManagement_2017_2018.zip
[3] https://www.iso20022.org/full_catalogue.page

[JC5]

Thanks for the expansive reference! Let me show you what I have added so far:

These fields are extra. Meaning that will only be used to make sure no info will be lost from the import. They will not (yet) be meaningfully attached to a transaction or an opposing account.

The BIC will be added to the opposing account.

Fields such as address, signatures and other identifiers are not going to be added in the near future.

Let me know what you think :)

[Obbi89]

I don't think that addresses and signatures are important
So far I cannot see any of those fields in the camt.054 format.
Meaning those are only for printed sepa forms, which don't need any represantation in ff3.

As far as I can see, you should have all sepa related fields now.

[JC5]

Closed because fixed.