Problems with QFX file import

rsums's Avatar

rsums

20 Mar, 2018 07:22 PM

My credit union switched to a new system last week, eliminating Direct Connect. When I import transactions from my checking account in either QFX or OXF format, Moneydance treats all items labeled "Draft Paid" as credits, rather than debits. At first I thought it was a problem with the QFX file, but when I import using the Money app by Jumsoft, the drafts all come in properly as debits. What is going on? I have attached the QFX file.

  1. 1 Posted by rsums on 20 Mar, 2018 08:00 PM

    rsums's Avatar

    Investigating further by using a text editor on the QFX file, it appears that the transaction type is being labeled "CHECK" in the field <TRNTYPE>:
    <STMTTRN>
    <TRNTYPE>CHECK
    <DTPOSTED>20180305000000.000[-07:MST]
    <TRNAMT>100.00
    <FITID>20180305138443644130655
    <CHECKNUM>1069
    <REFNUM>42323339
    <NAME>Draft Paid
    </STMTTRN>
    Moneydance thus appears to process this field as a credit rather than a debit, perhaps because it doesn't recognize the CHECK value. Can this be fixed, or do I need to fight with my credit union and their online banking provider to get them to change the label for checks/drafts to DEBIT? What does Quicken do? (I suspect it processes it as a DEBIT or it would have been fixed by now.)

  2. 2 Posted by rsums on 20 Mar, 2018 10:26 PM

    rsums's Avatar

    Checking the OFX specs, it appears that CHECK is a valid TRNTYPE, so it appears Moneydance is processing this field in error.

  3. 3 Posted by rsums on 20 Mar, 2018 11:23 PM

    rsums's Avatar

    deleted and replaced by following comment

  4. 4 Posted by rsums on 20 Mar, 2018 11:36 PM

    rsums's Avatar

    Further investigation: The OFX spec (both 2.1.1 and 2.2 released for public comment in 2016) say that transactions that reduce the balance from a user's perspective should have negative values for the <TRNAMT> field:

    "3.2.9.2 Positive and Negative Signs

    "Most OFX transaction aggregates describe the flow of funds. Amounts in transactions which clearly describe the flow of funds should normally be positive. For example, bank transfers (<INTRARQ>), bill payments (<PMTRQ>) and investment buys/sells (<BUYSTOCK>, <SELLSTOCK>) should all have positive amounts. An exception to this is the sign of the amount in statement download transactions, wrapped within <STMTTRN></STMTTRN> tags. The amounts in these transactions should be signed on the basis of how the account is affected, e.g. a <TRNTYPE>DEBIT should have a negative <TRNAMT> value.

    "Servers should sign amounts from the perspective of the user in cases where the flow of funds cannot be determined from the transaction aggregate alone. For example, interest amounts can be either positive or negative, depending on whether the interest is earned or paid."

    All values for <TRNAMT> in the OFX or QFX file downloaded from my credit union are positive, so in the case of values for <TRNTYPE> of DEBIT or CHECK, it is not adhering to the OFX spec. For positive DEBIT values, Moneydance is not enforcing strict adherence to the OFX spec and processing the values as negative anyway, which yields the correct result. However, for positive CHECK values, Moneydance IS enforcing strict adherence to the spec, which gives incorrect results. So things are a mess.

    Can Moneydance take the same approach for positive CHECK amounts as it does for positive DEBIT amounts? Right now, I have a cumbersome workaround where I manually edit the QFX file to globally replace <TRNTYPE>CHECK with <TRNTYPE>DEBIT before importing into Moneydance. I will also be in touch with my credit union (and hopefully their online provider for their export tool) to try to work this.

  5. 5 Posted by rsums on 23 Mar, 2018 08:03 PM

    rsums's Avatar

    Infinite Kind, anybody there? Any comment on my diagnosis and the possibility of modifying Moneydance to process CHECKs in the same manner it does for DEBITs? I have formally requested that my credit union address this with Alkami Technology, their online banking tool provider. They are the ones that need to adhere to the OFX specs.

  6. 6 Posted by rsums on 24 Apr, 2018 02:07 AM

    rsums's Avatar

    No one from Infinite Kind every responded. However, my credit union has fixed their QFX and OFX download tool so that both DEBIT and CHECK are negative values, so my problem is fixed.

  7. rsums closed this discussion on 24 Apr, 2018 02:07 AM.

Comments are currently closed for this discussion. You can start a new one.

Keyboard shortcuts

Generic

? Show this help
ESC Blurs the current field

Comment Form

r Focus the comment reply box
^ + ↩ Submit the comment

You can use Command ⌘ instead of Control ^ on Mac