New OFX Problem

[Brendan Coupe]

I'm running the latest CVS version and have been using KMM for 4 years and I have been downloading OFX directly from my bank since this feature was first added to the CVS version  a while ago. I'm running on Fedora 11, 32 bit.

This morning when I downloaded the transactions for my credit card account I had 15 transactions that were added to my account. The problem is that 14 of the 15 had already been downloaded so I had 14 duplicate transactions.

This problem is inconsistent. I closed my KMM file without saving it and tried to reproduce the problem. It does not happen every time. Sometimes I get "15 processed, 1 added, 14 duplicates" (correct) and sometimes I get "15 processed, 15 added, 0 duplicates" (wrong).

When it adds the 14 that already exist and then I update the account again it always finds the duplicates (i.e. I never get triplicate transactions). When I select the two duplicate transactions the "Match" option is disabled.

Most of my testing has been done downloading the OFX file for just my credit card. The few times that I download the OFX files for all of my online accounts causes this problem every time (I only get duplicates in my credit card account, not in any other accounts and I have not done this enough times to be sure it really happens every time). If I start by only updating my credit card account about half the time it sees the duplicates and half the time it adds the duplicates. When it sees the duplicates (and therefore does not add them) and I try to update the credit card account again and again it eventually misses the duplicate transactions and adds them to the account. Sometimes it happens the second time, sometimes it takes 6 or 6 tries. Eventually I will have duplicates added to my KMM file.

To be clear on my last test the first 3 times worked as expected and the 4TH try added the 14 transactions that were already in the account.

I deleted all of the transactions for the month of October, told KMM to only import the previous 5 days. I got 5 transactions. Saved KMM. Opened KMM, downloaded OFX for the credit card 3 times. The third time it duplicated the transactions.

I have many OFX accounts and this is only affecting my credit card account. It has the most transactions but most or all of the other accounts have recent transactions that could be duplicated but they are not.

We just switched back from Daylight Saving Time to standard time in the US today. I have no idea why this would cause a problem but I hate troubleshooting problems with partial info.

I compile the CVS version fairly often and went back to the October 15 version. It has the same problem as today's CVS. Unfortunately I purged older versions recently since everything seemed to be stable.

--
Brendan


------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
_______________________________________________
KMyMoney2-developer mailing list
KMyMoney2-developer@...
https://lists.sourceforge.net/lists/listinfo/kmymoney2-developer

[Thomas Baumgart]

Hi,

on Sunday 01 November 2009 Brendan Coupe wrote:

> I'm running the latest CVS version and have been using KMM for 4 years and
>  I have been downloading OFX directly from my bank since this feature was
>  first added to the CVS version  a while ago. I'm running on Fedora 11, 32
>  bit.
>
> This morning when I downloaded the transactions for my credit card account
>  I had 15 transactions that were added to my account. The problem is that
>  14 of the 15 had already been downloaded so I had 14 duplicate
>  transactions.
>
> This problem is inconsistent. I closed my KMM file without saving it and
> tried to reproduce the problem. It does not happen every time. Sometimes I
> get "15 processed, 1 added, 14 duplicates" (correct) and sometimes I get
>  "15 processed, 15 added, 0 duplicates" (wrong).
>
> When it adds the 14 that already exist and then I update the account again
> it always finds the duplicates (i.e. I never get triplicate transactions).
> When I select the two duplicate transactions the "Match" option is
>  disabled.
>
> Most of my testing has been done downloading the OFX file for just my
>  credit card. The few times that I download the OFX files for all of my
>  online accounts causes this problem every time (I only get duplicates in
>  my credit card account, not in any other accounts and I have not done this
>  enough times to be sure it really happens every time). If I start by only
>  updating my credit card account about half the time it sees the duplicates
>  and half the time it adds the duplicates. When it sees the duplicates (and
>  therefore does not add them) and I try to update the credit card account
>  again and again it eventually misses the duplicate transactions and adds
>  them to the account. Sometimes it happens the second time, sometimes it
>  takes 6 or 6 tries. Eventually I will have duplicates added to my KMM
>  file.
>
> To be clear on my last test the first 3 times worked as expected and the
>  4TH try added the 14 transactions that were already in the account.
>
> I deleted all of the transactions for the month of October, told KMM to
>  only import the previous 5 days. I got 5 transactions. Saved KMM. Opened
>  KMM, downloaded OFX for the credit card 3 times. The third time it
>  duplicated the transactions.
>
> I have many OFX accounts and this is only affecting my credit card account.
> It has the most transactions but most or all of the other accounts have
> recent transactions that could be duplicated but they are not.
>
> We just switched back from Daylight Saving Time to standard time in the US
> today. I have no idea why this would cause a problem but I hate
> troubleshooting problems with partial info.
>
> I compile the CVS version fairly often and went back to the October 15
> version. It has the same problem as today's CVS. Unfortunately I purged
> older versions recently since everything seemed to be stable.

In the meantime - and sorry for the delay: I was sick - Brandon found out,
that while downloading the same statement information he receives different
FITIDs for the same thing. That's bad. Here's the relevant portion from the
OFX 2.1.1 spec (don't know if that is the newest one or not):

---8<---
3.2.3 Financial Institution Transaction ID <FITID>
Format: A-255

An FI (or its Service Provider) assigns an <FITID> to uniquely identify a
financial transaction that can appear in an account statement. Its primary
purpose is to allow a client to detect duplicate responses. Open Financial
Exchange intends <FITID> for use in statement download applications, where
every transaction (not just those that are client-originated or server-
originated) requires a unique ID.

An <FITID> also uniquely identifies the closing statement in <CLOSINGRS> and
<CCCLOSINGRS>. Again, the OFX client should detect repeated closing statements
(duplicate downloads) using these identifiers.

FITIDs must be unique within the scope of an account but need not be
sequential or even increasing. Clients should be aware that FITIDs are not
unique across FIs. If a client performs the same type of request within the
same scope at two different FIs, clients will need to use FI + <ACCTID> +
<FITID> as a globally unique key in a client database. That is, the <FITID>
value must be unique within the account and Financial Institution (independent
of the service provider).

Note: Although the specification allows FITIDs of up to 255 characters, client
performance may significantly improve if servers use fewer characters. It is
recommended that servers use 32 characters or fewer.
---8<---

So it's time to get in touch with the bank, as they violate the following
(maybe they are not even aware of it):

* FITIDs must be unique within the scope of an account
* the <FITID> value must be unique within the account and Financial
Institution (independent of the service provider).

Brandon is seeing the following when download (sometimes duplicates are
detected, sometimes not. That's what OK and BAD are saying):

OK:    bankid="ID 11/04/09($31.00)...."
BAD:   bankid="ID 11/04/2009($31.00)...."

bankid in this case is filled with the data from the OFX field FITID with a
prepended "ID ". If this field is not filled by the OFX provider, KMyMoney
uses the OFX field REFNUM prepended by "REF " as a unique key if present.


--


Regards

Thomas Baumgart

GPG-FP: E55E D592 F45F 116B 8429   4F99 9C59 DB40 B75D D3BA
-------------------------------------------------------------
Of all the computing resources available, the most valuable one is
programmers' time. Especially in open source where most of us have to
sneak in time to write and debug code. (Ace Jones)
-------------------------------------------------------------


------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now.  http://p.sf.net/sfu/bobj-july
_______________________________________________
KMyMoney2-developer mailing list
KMyMoney2-developer@...
https://lists.sourceforge.net/lists/listinfo/kmymoney2-developer