After an evaluation, GNOME has moved from Bugzilla to GitLab. Learn more about GitLab.
No new issues can be reported in GNOME Bugzilla anymore.
To report an issue in a GNOME project, go to GNOME GitLab.
Do not go to GNOME Gitlab for: Bluefish, Doxygen, GnuCash, GStreamer, java-gnome, LDTP, NetworkManager, Tomboy.
Bug 793461 - Transaction matcher window blank when importing QFX
Transaction matcher window blank when importing QFX
Status: RESOLVED FIXED
Product: GnuCash
Classification: Other
Component: Import - OFX
2.7.x
Other Windows
: Normal normal
: ---
Assigned To: gnucash-import-maint
gnucash-import-maint
: 795245 795286 795606 795665 (view as bug list)
Depends on:
Blocks:
 
 
Reported: 2018-02-14 17:42 UTC by Dawid Wrobel
Modified: 2018-06-30 00:04 UTC
See Also:
GNOME target: ---
GNOME version: ---


Attachments
Sample QFX file (1.05 KB, text/plain)
2018-02-14 17:42 UTC, Dawid Wrobel
  Details
Proposed changes to libofx-0.9.10 (1.58 KB, patch)
2018-04-08 10:01 UTC, Bob
none Details | Review
Zipped libofx-6.dll Windows file (1017.88 KB, application/zip)
2018-04-08 10:07 UTC, Bob
  Details
problem report (Apple) - Gnucash [2018-04-29] (69.64 KB, text/plain)
2018-04-30 04:35 UTC, Jay
  Details

Description Dawid Wrobel 2018-02-14 17:42:19 UTC
Created attachment 368349 [details]
Sample QFX file

Trying to import the QFX transaction reports from CHASE bank and the transaction matcher is blank. Note that this works with GnuCash stable release. 

Sample QFX file attached.
Comment 1 Dawid Wrobel 2018-02-14 18:52:54 UTC
Just checked with another bank's QFX and yet another card issuer's OFX and it's the same issue.
Comment 2 Bob 2018-03-10 12:37:02 UTC
I have tried your file on Gnucash version 2.7.5 and the first time I do the import I have two transactions in the transaction matcher window. If I allocate accounts for the two transactions they get added to my file. If I try to import them again, then I get no entries as I have already added them so I would assume you have already done the import.

Bob
Comment 3 Dawid Wrobel 2018-03-13 18:48:49 UTC
I just downloaded a fresh OFX with the transactions dating only a few days back. This is for an account I haven't updated in a month. It still reads blank in GnuCash 2.7.5.

Looks like transaction matching logic is too eager to deem transactions as previously imported?
Comment 4 Dawid Wrobel 2018-03-13 18:54:14 UTC
More importantly, 2.6.19 imports the transactions just fine using the same GnuCash XML and QFX files, so there's definitely some regression in 2.7.x.
Comment 5 Chris Good 2018-03-13 19:47:06 UTC
As Bob said, Are sure you don't already have transactions in your GnuCash data file with the same FITID? If you do, GnuCash thinks you have already imported them.
Comment 6 Dawid Wrobel 2018-03-13 20:58:25 UTC
As I explained, when using the same, newly generated QFX file and same GnuCash database XML file, the importing works as expected in 2.6.19 but not in 2.7.x.
Comment 7 Bob 2018-03-14 14:36:52 UTC
Could you try this please, save an uncompressed xml file and then open it in a text editor and do a search for 201802050 and 201801040 which are the FITID's of the transactions in the QFX file above and see if they appear and what dates they are.

I seem to recall there was some discussion about some banks recycling FITID's and so the matcher sees them already existing in the past.

Can not remember what the outcome was.

Bob
Comment 8 Dawid Wrobel 2018-03-14 19:33:28 UTC
I just check the most recent statement that I had the same issue with and the FITIDs are *not* present in Gnucash XML file, so it's not a bank recycling the FITID - which would be very surprising in this case, since they use transaction date as a basis...

Also, while I feel like I am repeating myself, let me stress that the same Gnucash XML file opened in 2.6.19 allows me to import that QFX statement just fine, while 2.7.x has the issue as described.
Comment 9 Bob 2018-03-15 14:28:13 UTC
OK, I failed to notice that you are on Windows and I am on Linux. Rebooted into Windows and can confirm the matcher is blank, now much more difficult to find reason.
Comment 10 Bob 2018-03-17 10:41:11 UTC
I think what is happening is that gnc_file_ofx_import sets up the callbacks from the OFX library, loads the file and tries to parse the file but the callbacks do not fire and so Gnucash does not get updated.

Just to make sure I added some some debug comments to the gnc_ callbacks and set all the ofx_XXXX_msg settings to true but no output. As a test I disable any optimization of libofx and rebuilt it with this...

module_extra_env["libofx"]={'CFLAGS':'-O0'}

but still no luck.

Still pondering but this is probably beyond my knowledge.

Bob
Comment 11 David Reiser 2018-04-03 21:42:55 UTC
The same blank transaction matcher window happens on MacOS 10.13.4 with the 3.0 release.
Comment 12 Peter Kane 2018-04-03 21:48:53 UTC
I'm also getting this issue on MacOS 10.13.3 with the 3.0 release.
OFX files from Commonwealth Bank Australia.
It's critical for me, so I've reverted to 2.6.19
Comment 13 Jeremie Gaucher 2018-04-06 12:54:59 UTC
Same behaviour confirmed on MacOS 10.10.5.
Blank import transaction matcher window on release 3.0.
Release 2.6.19 shows transactions when importing same ofx file.
Comment 14 Jeremie Gaucher 2018-04-06 12:55:33 UTC
Same behaviour confirmed on MacOS 10.10.5.
Blank import transaction matcher window on release 3.0.
Release 2.6.19 shows transactions when importing same ofx file.
Comment 15 John Ralls 2018-04-06 14:22:04 UTC
On MacOS adding the line
  OFX_DTD_PATH={GNC_HOME}/share/libofx/dtd
to GnuCash.app/Contents/Resources/etc/gnucash/environment fixes the problem. The files are in the Windows bundle, so the same should work on Windows.
Comment 16 Bob 2018-04-06 17:41:30 UTC
Interesting, after great struggle with my windows build machine I found that libofx is creating temporary files but then can not access them. Managed to send the output to a file and this was recorded...

ofx_proc_file():Unable to open the created temp file ....

this is from ofx_preproc.cpp, line 120

After some poking around I was able to get it to work by changing the 'mkstemp' on line 114 to use the function defined in win32.cpp.

How to do this properly is another thing...

Bob
Comment 17 Jeremie Gaucher 2018-04-06 17:46:23 UTC
Fixes it for me on MacOS 10.10.5, GnuCash release 3.0.

(In reply to John Ralls from comment #15)
> On MacOS adding the line
>   OFX_DTD_PATH={GNC_HOME}/share/libofx/dtd
> to GnuCash.app/Contents/Resources/etc/gnucash/environment fixes the problem.
> The files are in the Windows bundle, so the same should work on Windows.
Comment 18 John Ralls 2018-04-06 19:27:55 UTC
(In reply to Bob from comment #16)
> Interesting, after great struggle with my windows build machine I found that
> libofx is creating temporary files but then can not access them. Managed to
> send the output to a file and this was recorded...
> 
> ofx_proc_file():Unable to open the created temp file ....
> 
> this is from ofx_preproc.cpp, line 120
> 
> After some poking around I was able to get it to work by changing the
> 'mkstemp' on line 114 to use the function defined in win32.cpp.
> 
> How to do this properly is another thing...

Is this a mingw vs mingw-w64 difference?

Doing it properly means a PR to Benoit at https://github.com/libofx/libofx.
Comment 19 andrew@schwartzmeyer.com 2018-04-07 05:20:46 UTC
(In reply to John Ralls from comment #15)
> On MacOS adding the line
>   OFX_DTD_PATH={GNC_HOME}/share/libofx/dtd
> to GnuCash.app/Contents/Resources/etc/gnucash/environment fixes the problem.
> The files are in the Windows bundle, so the same should work on Windows.

Thanks! This worked on MacOS 10.13.4 for me.
Comment 20 Bob 2018-04-08 10:01:18 UTC
Created attachment 370652 [details] [review]
Proposed changes to libofx-0.9.10

With my limited knowledge, these are the changes I have made to get this to work on Windows.

Made the changes detailed in the above patch to libofx files and added this line to gnucash-on-windows.git/jhbuildrc

module_extra_env["libofx"]={'CPPFLAGS':'-D OS_WIN32'}

With these changes I am able to build libofx on Windows which fixes this issue.

Also managed to install version 3.0 from sourcefourge and then replace gnucash/bin/libofx-6.dll with my compiled version and all appeared to work. Will attach if any one would like to try.

Bob
Comment 21 Bob 2018-04-08 10:07:46 UTC
Created attachment 370653 [details]
Zipped libofx-6.dll Windows file

Extract file and copy to gnucash/bin directory for Gnucash Version 3.0, may like to rename existing version for backup.
Comment 22 Wes Shank 2018-04-11 15:35:34 UTC
Thank you, Bob. The file you posted corrected this issue for me.
Comment 23 Wes Shank 2018-04-12 09:52:49 UTC
(In reply to Bob from comment #21)
> Created attachment 370653 [details]
> Zipped libofx-6.dll Windows file
> 
> Extract file and copy to gnucash/bin directory for Gnucash Version 3.0, may
> like to rename existing version for backup.

Follow up - the file does import but it doesn't seem to be able to match an accounts to previous transactions.
Comment 24 Bob 2018-04-13 13:11:39 UTC
Are saying the transactions show up unbalanced ?
Are you using Bayesian matching, preferences/'On line Banking'
What backend are you using, xml / sqlite ?

Open tools/'import map editor', may take a short while depending how many entries.

If Bayesian, find source account in list and look for parts of transaction description and day of transaction in 'match string' column which should all point to the required mapped to account. There is a calculation done on these entries to equate to a successful mapping.

If non Bayesian it's easier as the whole description is in the 'match string' column.
Comment 25 Gerry Matte 2018-04-13 22:04:36 UTC
gnucash 2.6.19 on Windows 10.  Using mysql database.
Trying to import multiple QFX files - first four files imported perfectly.
The fifth file only imported the first transaction in the QFX file.
The remaining 31 transactions were ignored.

An attempt to import the file a second time results in no transactions accepted.

I'm wondering if the accepted transaction is blocking the QFX file from further processing.  I'm tempted to edit the QFX file and remove the first transaction then try importing again.

Anyone have any comments or suggestions ?
Comment 26 John Ralls 2018-04-14 02:37:06 UTC
*** Bug 795245 has been marked as a duplicate of this bug. ***
Comment 27 Wes Shank 2018-04-14 15:07:29 UTC
(In reply to Bob from comment #24)
> Are saying the transactions show up unbalanced ?
> Are you using Bayesian matching, preferences/'On line Banking'
> What backend are you using, xml / sqlite ?
> 
> Open tools/'import map editor', may take a short while depending how many
> entries.
> 
> If Bayesian, find source account in list and look for parts of transaction
> description and day of transaction in 'match string' column which should all
> point to the required mapped to account. There is a calculation done on
> these entries to equate to a successful mapping.
> 
> If non Bayesian it's easier as the whole description is in the 'match
> string' column.

Bob - apologies in advance - I'm just a user with enough development experience to be dangerous.

My user experience was the bulk of my OFX imported tranx came in with matching. I used whatever tools are GnuCash out-of-the-box. After installing the dll provided by you, the imports came in (YAY!) but none of the matching came in. What was weird was that the 2nd time I imported, much more of the matching showed up - I wasn't paying enough attention to know if it was the matches that were part of the first.

I run a help desk for a software company. I am totally up for calling this user-error and marching on but if you guys are running down any kind of bug, I know this type of feedback can sometimes be helpful. Consider me taken care of - it seems like matching is working "good enough".
Comment 28 John Ralls 2018-04-14 20:30:57 UTC
Comment on attachment 370652 [details] [review]
Proposed changes to libofx-0.9.10

Where does mkstemp_win32 come from? When I try to compile that I get:
C:/gcdev64/gnucash/unstable/src/libofx-0.9.10/lib/ofx_preproc.cpp:114:19: error: 'mkstemp_win32' was not declared in this scope
     tmp_file_fd = mkstemp_win32(tmp_filename);
                   ^~~~~~~~~~~~~
C:/gcdev64/gnucash/unstable/src/libofx-0.9.10/lib/ofx_preproc.cpp:114:19: note: suggested alternative: 'mkstemp'
     tmp_file_fd = mkstemp_win32(tmp_filename);
                   ^~~~~~~~~~~~~
                   mkstemp
Comment 29 Bob 2018-04-14 23:17:07 UTC
All it is the existing function in the lib/win32.cpp win32.hh renamed as it was conflicting with the ming64 one. It is part of the patch also.
Comment 30 John Ralls 2018-04-15 00:03:27 UTC
OK, I get it.

Are you going to submit a PR to https://github.com/libofx/libofx?
Comment 31 Gerry Matte 2018-04-15 19:41:03 UTC
Regarding my comment 25, I restored my mysql database from backups and I upgraded gnucash to version 3.0.  I then copied Bob's modified libofx6.dll into the bin folder.

I did this on a second PC that had never imported the bad qfx file that caused my problems - assuming that however FITIDs are saved this PC would never have seen the data that caused me problems.

My bank transactions display without the single transaction that was previously imported using 2.6.19 on my primary PC.

When I try to again process the problematic qfx file it fails to show any transactions in the import window.

None of the 34 transactions are accepted - not even the single transaction that was previously accepted on my primary pc.

Is there any software available that can wipe out the FITIDs from previously processed qfx files but leave the imported data in my mysql database ?
Comment 32 John Ralls 2018-04-16 02:42:44 UTC
*** Bug 795286 has been marked as a duplicate of this bug. ***
Comment 33 Bob 2018-04-17 14:33:15 UTC
(In reply to John Ralls from comment #30)
> OK, I get it.
> 
> Are you going to submit a PR to https://github.com/libofx/libofx?

Will do once I have sorted myself out...
Did you miss adding the following to jhbuildrc.in

module_extra_env["libofx"]={'CPPFLAGS':'-D OS_WIN32'}
Comment 34 John Ralls 2018-04-17 14:45:12 UTC
That shouldn't be necessary, platform detection is built in to autotools. It's actually better still to use the gcc built-in defines __WIN32__ and __MINGW__.
Comment 35 Chris Good 2018-04-18 12:40:42 UTC
Bob, Your libofx-6.dll from comment 21 enabled me to import using GnuCash 3.0 in Windows 10 with XML data store. It also correctly matched a manually entered transaction that was already in the register. Many Thanks.
Comment 36 John Ralls 2018-04-18 13:39:27 UTC
The latest nightly builds at https://code.gnucash.org/builds/win32/maint have Bob's patch applied.
Comment 37 bernie 2018-04-20 10:43:54 UTC
John, I tried the latest nightly builds and it didn't resolve the issue (Windows 10). However, manually installing the libofx-6.dll as above did resolve the issue, so I guess the nightly build isn't including the updated libofx dll. I am using a PostgreSQL database. Let me know if you want any more detail.
Comment 38 PATH 2018-04-27 14:42:21 UTC
*** Bug 795606 has been marked as a duplicate of this bug. ***
Comment 39 Mitch 2018-04-27 22:58:27 UTC
( I just downloaded Build ID: git 3.0+ (2018-04-01) and have the ofx import problem)

OK, so for a temporary fix one can flip in the suggested .dll - what I have not seen is any discussion about what other functions may be disrupted due to the incorrect dll, and on the other side, what intended fixes may not be present in the flipped in dll.

Can anyone comment?  

Mitch
Comment 40 John Ralls 2018-04-28 02:21:09 UTC
I've redone the patch to use __WIN32__ instead of OS_WIN32. That's defined by the compiler so it doesn't depend on configure.in to set it, a behavior that seems to have been removed from libofx. The change will be in tomorrow's nightly (https://code.gnucash.org/builds/win32/maint).

Please test.
Comment 41 John Ralls 2018-04-28 02:24:29 UTC
(In reply to Mitch from comment #39)
> ( I just downloaded Build ID: git 3.0+ (2018-04-01) and have the ofx import
> problem)
> 
> OK, so for a temporary fix one can flip in the suggested .dll - what I have
> not seen is any discussion about what other functions may be disrupted due
> to the incorrect dll, and on the other side, what intended fixes may not be
> present in the flipped in dll.
> 
> Can anyone comment?  

Assuming that Bob built the library the same as the regular build script, and there's no reason to think that he didn't, there should be no disruption. There are a few other uses of OS_WIN32 that Bob's library will have enabled and tomorrow's nightly won't. They're mostly associated with fallback paths (C:\ instead of /tmp) so I don't expect that they'll matter, but we'll see as soon as folks try it.
Comment 42 Geert Janssens 2018-04-28 14:15:40 UTC
To get it to build a few more OS_WIN32 instances had to be replaced with __WIN32__. I have done so now and restarted the nightly build.

Can you retry with this one please:
https://code.gnucash.org/builds/win32/maint/gnucash-3.0-2018-04-27-git-3.0-94-g74fd6e046+.setup.exe
Comment 43 Mitch 2018-04-28 23:55:47 UTC
tried just now ( 4/28 1850 CST) with setup from comment 42 - no change.  sigh.  Still get empty match screen on ofx file.

Mitch // Arkansas
Comment 44 John Ralls 2018-04-29 05:17:05 UTC
Hmm, not sure what went wrong, I'll troubleshoot that tomorrow. However I did test the release build and found that it didn't work so I redid it, making sure that it got a clean rebuild of libofx, and that works OK at least on my system.
Comment 45 Geert Janssens 2018-04-29 08:36:32 UTC
The build I pointed at in comment 42 was a manually crafted one. While that one was building I also was working on creating a full patch on another machine. That full patch is what got committed in the end and included in 3.1.
I had tested the manually crafted build locally though as well as a build with the 3.1 patch. And both worked fine for me. I don't know what went wrong in the end that caused it to fail for you after all.

And for the record, I have also downloaded and tested the clean build
https://code.gnucash.org/builds/win32/releases/gnucash-3.1-1.setup.exe
and again that seems to work fine for me.
Comment 46 Geert Janssens 2018-04-29 08:46:16 UTC
(In reply to Wes Shank from comment #27)
> My user experience was the bulk of my OFX imported tranx came in with
> matching. I used whatever tools are GnuCash out-of-the-box. After installing
> the dll provided by you, the imports came in (YAY!) but none of the matching
> came in. What was weird was that the 2nd time I imported, much more of the
> matching showed up - I wasn't paying enough attention to know if it was the
> matches that were part of the first.
> 
Wes, this got a bit sidetracked because we were still struggling to get the transaction matcher to show transactions in the first place.

However if I understand correctly you are seeing the transactions but gnucash doesn't reuse previous match data to automatically recognize them ?

If so that would also be a bug, but another one. Gnucash 2.6 and 3.0 treat the previous match data (more formally referred to as "import maps") slightly differently. If coming from 2.6 your old import maps are no longer recognized, can I ask you to file this as a separate bug ? It would become too confusing to treat that one here as well.
Comment 47 Geert Janssens 2018-04-29 08:51:03 UTC
(In reply to Gerry Matte from comment #31)
> Is there any software available that can wipe out the FITIDs from previously
> processed qfx files but leave the imported data in my mysql database ?

I believe the new Import map editor in gnucash 3.0 should allow you to do this.
Comment 48 Geert Janssens 2018-04-29 17:19:09 UTC
*** Bug 795665 has been marked as a duplicate of this bug. ***
Comment 49 Mitch 2018-04-29 19:37:53 UTC
OK!  just downloaded Version: 3.1
Build ID: 3.0-118-gd2ef5fd0f+ (2018-04-28)
from website ( no custom links ...)

It works as desired/expected now.  Note I am using the same transaction file that has been failing since first tried on 4/27.  It had been coming up completely empty: no matches no new entries, just empty.  This round I got 4 transactions as expected, so I think y'all finally squashed this one

Thanks!

Mitch//Arkansas
Comment 50 John Ralls 2018-04-29 19:46:32 UTC
(In reply to Gerry Matte from comment #31)
> Regarding my comment 25, I restored my mysql database from backups and I
> upgraded gnucash to version 3.0.  I then copied Bob's modified libofx6.dll
> into the bin folder.
> 
> I did this on a second PC that had never imported the bad qfx file that
> caused my problems - assuming that however FITIDs are saved this PC would
> never have seen the data that caused me problems.
> 
> My bank transactions display without the single transaction that was
> previously imported using 2.6.19 on my primary PC.
> 
> When I try to again process the problematic qfx file it fails to show any
> transactions in the import window.
> 
> None of the 34 transactions are accepted - not even the single transaction
> that was previously accepted on my primary pc.
> 
> Is there any software available that can wipe out the FITIDs from previously
> processed qfx files but leave the imported data in my mysql database ?

No, you'd have to write an update query to do that.
Comment 51 John Ralls 2018-04-29 19:47:15 UTC
Anyone else want to relate their experience with 3.1 before we declare victory?
Comment 52 Gerry Matte 2018-04-29 21:42:49 UTC
(In reply to John Ralls from comment #50)
> (In reply to Gerry Matte from comment #31)
> > Regarding my comment 25, I restored my mysql database from backups and I
> > upgraded gnucash to version 3.0.  I then copied Bob's modified libofx6.dll
> > into the bin folder.
> > 
> > I did this on a second PC that had never imported the bad qfx file that
> > caused my problems - assuming that however FITIDs are saved this PC would
> > never have seen the data that caused me problems.
> > 
> > My bank transactions display without the single transaction that was
> > previously imported using 2.6.19 on my primary PC.
> > 
> > When I try to again process the problematic qfx file it fails to show any
> > transactions in the import window.
> > 
> > None of the 34 transactions are accepted - not even the single transaction
> > that was previously accepted on my primary pc.
> > 
> > Is there any software available that can wipe out the FITIDs from previously
> > processed qfx files but leave the imported data in my mysql database ?
> 
> No, you'd have to write an update query to do that.

If you mean an SQL query, that would be no problem but I don't know which mysql table to update and which column should be set to zero/null/blank.
Do you know where the FITIDs are stored and what the update should set them to ?   Thanks for responding to this comment.
Gerry
Comment 53 John Ralls 2018-04-29 22:10:05 UTC
The schema is at https://wiki.gnucash.org/wiki/SQL. FITID is stored in the slots table with the key "online_id". The slot's obj_guid will match the corresponding split's guid. Since it's a slot the only information in the row is the FITID so you can just delete the row from slots.
Comment 54 Jay 2018-04-30 04:22:41 UTC
Updated to 3.0 on macOS 10.12.15 a few days ago. Encountered the blank screen when attempting to import QFX files, like the OP and others. Found this thread. Was glad that 3.1 was released the same day. Reinstalled. After relaunching Gnucash in Full Screen mode, attempted to import the QFX file, but the program crashed. Relaunched it and successfully imported the QFX file in regular windowed mode. To determine whether the first crash was a fluke, closed out and then relaunched in Full Screen again. Sure enough, the import resulted in another crash. The Apple Problem Report has been saved, if those details could help. Just let me know if so and how to relay them. Perhaps the import in Full Screen mode provokes a distinctly different bug but figured it was worth mentioning. Otherwise, VERY GLAD that the issue seems to have been functionally resolved.

(In reply to John Ralls from comment #51)
> Anyone else want to relate their experience with 3.1 before we declare
> victory?
Comment 55 Jay 2018-04-30 04:29:25 UTC
correction: macOS 10.12.5

(In reply to Jay from comment #54)
Comment 56 John Ralls 2018-04-30 04:32:11 UTC
Jay, yes, please do attach the crash report.
Comment 57 Jay 2018-04-30 04:35:30 UTC
Created attachment 371529 [details]
problem report (Apple) - Gnucash [2018-04-29]
Comment 58 John Ralls 2018-04-30 04:56:39 UTC
Hmm, crashing in the Gtk quartz backend with a bad pointer. Probably not GnuCash's doing. Wouldn't crash for me in 10.13, but I didn't go all the way through the import. This is clearly a separate problem, so please open a new bug for it.
Comment 59 Geert Janssens 2018-04-30 08:03:55 UTC
Marking this a fixed for 3.1
Comment 60 Geert Janssens 2018-04-30 18:42:15 UTC
I have adapted the libofx patch to libofx's current development branch and it got applied there today. Hopefully a new libofx release will follow soon.
Comment 61 John Ralls 2018-06-30 00:04:29 UTC
GnuCash bug tracking has moved to a new Bugzilla host. This bug has been copied to https://bugs.gnucash.org/show_bug.cgi?id=793461. Please update any external references or bookmarks.