[GNC] OFX Import with Multiple Banks/Accounts in a single file
David Carlson
david.carlson.417 at gmail.com
Wed Apr 15 19:44:27 EDT 2020
Sorry, I wasn,t clear that I was intentionally comparing apples with
oranges because they both grow on trees....
On Wed, Apr 15, 2020 at 5:49 PM Jean Laroche <ripngo at gmail.com> wrote:
> I think we're talking about OFX here, which is a lot more robust that
> QIF as far as importing. In any event, when you combine the OFX files,
> the OFX import is done sequentially (apparently) in GC as if you opened
> them one after the other, but I may be wrong. The transactions do all
> appear combined in a single match dialog, which is super nice.
> Jean
>
> On 4/15/20 3:42 PM, David Carlson wrote:
> >
> >
> > On Wed, Apr 15, 2020 at 5:22 PM Jean Laroche <ripngo at gmail.com
> > <mailto:ripngo at gmail.com>> wrote:
> >
> > I hacked some python code to do that if you're interested. I'm taking
> > ofx files, reading them and combining them just the way you describe
> > it.
> > It's not beautiful code but it works.
> > What would be awesome would be if you could select multiple files in
> > GC,
> > and then do the import!
> >
> > Jean,
> > I don't know if it is still true with the current QIF importer, but IIRC
> > back in the release 2.4.x era the QIF importer could handle multiple
> > files in one pass. The downside was that it couldn't match both sides of
> > a transfer in the same file. Multiple files would be a nice Request for
> > Improvement if it did match transfers.
> >
> > On 4/15/20 2:58 PM, Stu Perlman wrote:
> > > I did some experimenting today and I think I am seeing something
> > similar.
> > >
> > > If I take the </OFX> tag that ends the first account’s
> > transaction data
> > > and then select all text up to and including the <OFX> tag that
> > starts
> > > the next account’s transaction it will work perfectly.
> > >
> > > There is a bunch of stuff in between those two tags, including
> > another
> > > pair of <OFX> </OFX> tags that wrap some header info that appears
> > before
> > > the second account’s transactions.
> > >
> > > I’d rather not have to manipulate the file each time so if I can
> > figure
> > > out a script that can do what I am doing by hand I should be set.
> > If
> > > it’s not the same number of lines or characters each time my
> > script
> > > will need to be able to detect the correct tags as start and stop
> > points
> > > before removing the text. I plan on working on this a little
> > more now
> > > that I know it is possible to import multiple accounts using a
> > single file.
> > >
> > > On Tue, Apr 14, 2020 at 10:11 PM jean laroche <ripngo at gmail.com
> > <mailto:ripngo at gmail.com>
> > > <mailto:ripngo at gmail.com <mailto:ripngo at gmail.com>>> wrote:
> > >
> > > Well, combined OFX files *are* supported, at least when the
> > > originate from the same bank.
> > > I just created a combined OFX file by joining two separate OFX
> > > files, and putting the two data in between the BANKMSGSRSV1
> > tags in
> > > a row.
> > >
> > > <BANKMSGSRSV1>
> > > ....
> > > </BANKMSGSRSV1>
> > > <BANKMSGSRSV1>
> > > ....
> > > </BANKMSGSRSV1>
> > >
> > > In other words, I took everything that was between
> > <BANKMSGSRSV1>
> > > and </BANKMSGSRSV1> (including these) from one file, and
> > copied it
> > > right after the
> > > </BANKMSGSRSV1> from the other file, saved and imported. I
> > get the
> > > transactions from both accounts in one shot.
> > > That's pretty cool.
> > > So if that's not working for you, you should look at your OFX
> > file
> > > and try to identify where the problem originates...
> > > Jean
> > >
> > >
> > > On 4/14/2020 1:29 PM, David Carlson wrote:
> > >> 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 <mailto:sgperlman at gmail.com>
> > >> <mailto:sgperlman at gmail.com <mailto: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 <mailto:ripngo at gmail.com>
> > >> <mailto:ripngo at gmail.com <mailto: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 <mailto:sgperlman at gmail.com>
> > <mailto:sgperlman at gmail.com <mailto: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
> > <mailto:david.carlson.417 at gmail.com>
> > >> <mailto:david.carlson.417 at gmail.com
> > <mailto: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 <mailto:write2ove at gmail.com>
> > <mailto:write2ove at gmail.com <mailto: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
> > <mailto:david.carlson.417 at gmail.com>
> > >> <mailto:david.carlson.417 at gmail.com
> > <mailto: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 <mailto:sgperlman at gmail.com>
> > <mailto:sgperlman at gmail.com <mailto: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
> > <mailto:gnucash-user at gnucash.org>
> > >> <mailto:gnucash-user at gnucash.org
> > <mailto: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
> > <mailto:gnucash-user at gnucash.org> <mailto:gnucash-user at gnucash.org
> > <mailto: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
> > <mailto:gnucash-user at gnucash.org> <mailto:gnucash-user at gnucash.org
> > <mailto: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 <mailto:gnucash-user at gnucash.org>
> > <mailto:gnucash-user at gnucash.org <mailto: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
>
--
David Carlson
More information about the gnucash-user
mailing list