[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