[GNC] OFX Import with Multiple Banks/Accounts in a single file
David Carlson
david.carlson.417 at gmail.com
Tue Apr 14 16:29:17 EDT 2020
Stu,
Are you doing online banking through GnuCash or importing OFX files that
you downloaded earlier?
I am importing OFX files that I imported directly from my bank's website.
I think that the same OFX format is used either way but a different
procedure is used to get the data into GnuCash.
When I mentioned three tags that appear in OFX files that I receive I only
gave selected examples not a complete definition. One would need to get a
copy of the OFX standard for completeness.
On Tue, Apr 14, 2020, 3:14 PM Stu Perlman <sgperlman at gmail.com> wrote:
> Jean, I always assumed it was supported because if you use GNC to configure
> AqB, you only need to set up one User per financial institution even though
> you can set up multiple accounts for that single user.
>
> - Stu
>
> On Tue, Apr 14, 2020 at 4:01 PM Jean Laroche <ripngo at gmail.com> wrote:
>
> > 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.
> > >
> >
> _______________________________________________
> 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