[GNC] OFX Import with Multiple Banks/Accounts in a single file
Stu Perlman
sgperlman at gmail.com
Wed Apr 15 17:58:21 EDT 2020
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> 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> 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