[GNC] OFX Import with Multiple Banks/Accounts in a single file

Jean Laroche ripngo at gmail.com
Tue Apr 14 16:01:51 EDT 2020


BTW, that's a feature I was interested in (importing combined OFX 
files). If there are others interested, I'll look at a way to support 
that. Another thing that I miss is the ability to automatically 
reconcile after an OFX import (not to highjack this thread).
J.

On 4/14/20 12:53 PM, Ove Grunnér wrote:
> Ah, I was talking of QIF, not QFX, sorry about that.
> yes the java code writes QIF.
> Ove.
> 
> On Tue, 14 Apr 2020 at 20:45, Stu Perlman <sgperlman at gmail.com> wrote:
> 
>> Ove,
>>
>> Thanks for the info that you shared.  Is your Java program writing to the
>> QIF format or the OFX format?
>>
>> David - my apologies, I never noticed your reply to my original email last
>> month!  I'm happy to read that it should work.
>>
>> I'm still running GNC 3.8 from a mid-march build on my WIndows system. I
>> have yet to get GNC, AqBanking 6.x and Citi's OFX API to all play well with
>> each other.  I am able to get my data from Citi by using an older AqB
>> release that I have running under Cygwin to pull the data from the Citi's
>> web server.  I have found that in order to import the files that are
>> download into GNC that I need to make one file for each account at Citi (by
>> running aqbanking-cli for one account at a time). I then strip out the 1st
>> dozen or so lines in the response received.  I have managed to script most
>> of this so it's not nearly as cumbersome as it may appear, but needing to
>> enter the password once for each account at Citi is annoying as is needing
>> to import multiple files into GNC instead of one consolidated file. I tried
>> to eliminate the repeating password step with the Expect tool, but I could
>> not get it to work in the Cygwin environment.
>>
>> Unfortunately, the other financial institutions where I have multiple
>> accounts don't even support OFX (or at least if they do, the info is not
>> available on OFXhome.net)  so I don't know if the issues I have run into
>> are caused by Citi, GNC, or AqB. These issues are not so annoying that I
>> would go back to Quicken! LOL    I may mess around with trying to automate
>> my calls to aqbanking-cli using PowerShell so that I can take Cygwin out of
>> the mix and/or just try moving over to my Ubuntu system for GNC.
>>
>> - Stu
>>
>>
>>
>>
>>
>>
>> On Tue, Apr 14, 2020 at 2:40 PM David Carlson <david.carlson.417 at gmail.com>
>> wrote:
>>
>>> Ove,
>>>
>>> I believe !Clear:AutoSwitch is only used in QIF imports.  My OFX imports
>>> have tags similar to
>>>
>>> <BANKID> <ACCTID> and <ACCTTYPE>.
>>>
>>>
>>>
>>>
>>> On Tue, Apr 14, 2020 at 12:12 PM Ove Grunnér <write2ove at gmail.com> wrote:
>>>
>>>> it works for me as well, but I know I need to put a specific line in the
>>>> beginning of the file to let gnuCash know it is multi account
>>>>
>>>> open up your file and check if the firs line is:
>>>> !Clear:AutoSwitch
>>>>
>>>> Try to add the line if it is missing.
>>>>
>>>> I use the below java code to write my import files.
>>>>
>>>> br
>>>>
>>>>
>>>> try {
>>>>      FileWriter fstream = new FileWriter(targetDirectoryPointer + targetQifFileName);
>>>>      BufferedWriter info = new BufferedWriter(fstream);
>>>>
>>>>      // WRITE TO FILE
>>>>      // indicator for multi account import
>>>>      info.write("!Clear:AutoSwitch"+"\n");
>>>>
>>>>      for (int acc=0; acc< maxAcc; acc++) {
>>>>
>>>>          int transactionTableMaxRows = accountTable.getTransactionTableMaxRows(acc);
>>>>          DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
>>>>
>>>>          // get data for this account
>>>>          String targetFileAccountName = accountTable.getTransactionTableAccountName(acc);
>>>>          String[] transactionDescription = accountTable.getTransactionDescription(acc);
>>>>          float[] transactionAmount = accountTable.getTransactionAmount(acc);
>>>>          float[] transactionBalance = accountTable.getTransactionBalance(acc);
>>>>          Date[] transactionDate = accountTable.getTransactionDate(acc);
>>>>
>>>>          // WRITE TO FILE
>>>>          // New Account
>>>>          String account = accountDetailsGnuCashName[acc] ;
>>>>          info.write("!Account" + "\n" + "N" + account + "\n" + "^" + "\n");
>>>>
>>>>
>>>>          for (int l = 0; l < transactionTableMaxRows; l++) {
>>>>
>>>>              // Transaction
>>>>              String type = "Bank";
>>>>              String date = dateFormat.format(transactionDate[l]);
>>>>              String amount = String.valueOf(transactionAmount[l]);
>>>>              String description = transactionDescription[l];
>>>>              String category = transactionDescription[l];
>>>>
>>>>              info.write("!Type:" + type + "\n");
>>>>              info.write("D" + date + "\n");
>>>>              info.write("T" + amount + "\n");
>>>>              info.write("P" + description +"\n");
>>>>              info.write("L" + category + "\n");
>>>>              info.write("^" + "\n");
>>>>          }
>>>>      }
>>>>
>>>>      // Close File
>>>>      info.close();
>>>>
>>>> }
>>>> catch (IOException e) {
>>>>      System.out.println("A write error has occurred");
>>>>      e.printStackTrace();
>>>> }
>>>>
>>>>
>>>> On Fri, 13 Mar 2020 at 15:21, David Carlson <david.carlson.417 at gmail.com>
>>>> wrote:
>>>>
>>>>> Stu,
>>>>>
>>>>> I have been importing OFX files from my bank for years with three or
>>>>> four
>>>>> checking or savings accounts in one file, so I know GnuCash release
>>>>> 2.6.19
>>>>> can import OFX files with multiple accounts.  It is possible but
>>>>> unlikely
>>>>> that GnuCash has lost that ability in recent releases.  You may have
>>>>> something else happening in your case.
>>>>>
>>>>> On Fri, Mar 13, 2020 at 9:58 AM Stu Perlman <sgperlman at gmail.com>
>>>>> wrote:
>>>>>
>>>>>> Hello,
>>>>>>
>>>>>> I am using GNC on Windows 10.  I have a fairly recent build, dated
>>>>> March 9
>>>>>> from the maintenance branch.
>>>>>>
>>>>>> I noticed that I'm still unable to use AqBanking to get OFX data from
>>>>> Citi
>>>>>> into GNC.  I Did a little exploring and discovered that if I
>>>>> manipulate the
>>>>>> file in C:\tmp\ofx.log and break it into separate parts for each
>>>>> account
>>>>>> that was downloaded that GNC will import the data just fine if it's
>>>>> limited
>>>>>> to one account per file.  I also believe, but I need to confirm that
>>>>> I can
>>>>>> leave all of the data in a single file but that I need to eliminate a
>>>>> lot
>>>>>> lines starting with the closing tags for the first account's response
>>>>> body
>>>>>> and up to and including the opening tags for the subsequent accounts'
>>>>>> response bodies.
>>>>>>
>>>>>> Has anyone else encountered this issue and if so, do you have any
>>>>> ideas how
>>>>>> to fix this?  Is it a defect?
>>>>>>
>>>>>> Thanks in advance for your help.
>>>>>> _______________________________________________
>>>>>> gnucash-user mailing list
>>>>>> gnucash-user at gnucash.org
>>>>>> To update your subscription preferences or to unsubscribe:
>>>>>> https://lists.gnucash.org/mailman/listinfo/gnucash-user
>>>>>> If you are using Nabble or Gmane, please see
>>>>>> https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
>>>>>> -----
>>>>>> Please remember to CC this list on all your replies.
>>>>>> You can do this by using Reply-To-List or Reply-All.
>>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> David Carlson
>>>>> _______________________________________________
>>>>> gnucash-user mailing list
>>>>> gnucash-user at gnucash.org
>>>>> To update your subscription preferences or to unsubscribe:
>>>>> https://lists.gnucash.org/mailman/listinfo/gnucash-user
>>>>> If you are using Nabble or Gmane, please see
>>>>> https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
>>>>> -----
>>>>> Please remember to CC this list on all your replies.
>>>>> You can do this by using Reply-To-List or Reply-All.
>>>>>
>>>>
>>>
>>> --
>>> David Carlson
>>>
>>
> _______________________________________________
> gnucash-user mailing list
> gnucash-user at gnucash.org
> To update your subscription preferences or to unsubscribe:
> https://lists.gnucash.org/mailman/listinfo/gnucash-user
> If you are using Nabble or Gmane, please see https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
> -----
> Please remember to CC this list on all your replies.
> You can do this by using Reply-To-List or Reply-All.
> 


More information about the gnucash-user mailing list