GNOME Bugzilla – Bug 793461
Transaction matcher window blank when importing QFX
Last modified: 2018-06-30 00:04:29 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.
Just checked with another bank's QFX and yet another card issuer's OFX and it's the same issue.
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
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?
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.
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.
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.
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
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.
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.
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
The same blank transaction matcher window happens on MacOS 10.13.4 with the 3.0 release.
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
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.
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.
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
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.
(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.
(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.
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
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.
Thank you, Bob. The file you posted corrected this issue for me.
(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.
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.
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 ?
*** Bug 795245 has been marked as a duplicate of this bug. ***
(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 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
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.
OK, I get it. Are you going to submit a PR to https://github.com/libofx/libofx?
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 ?
*** Bug 795286 has been marked as a duplicate of this bug. ***
(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'}
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__.
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.
The latest nightly builds at https://code.gnucash.org/builds/win32/maint have Bob's patch applied.
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.
*** Bug 795606 has been marked as a duplicate of this bug. ***
( 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
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.
(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.
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
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
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.
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.
(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.
(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.
*** Bug 795665 has been marked as a duplicate of this bug. ***
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
(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.
Anyone else want to relate their experience with 3.1 before we declare victory?
(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
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.
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?
correction: macOS 10.12.5 (In reply to Jay from comment #54)
Jay, yes, please do attach the crash report.
Created attachment 371529 [details] problem report (Apple) - Gnucash [2018-04-29]
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.
Marking this a fixed for 3.1
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.
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.