[GNC] OFX Import with Multiple Banks/Accounts in a single file
jean laroche
ripngo at gmail.com
Wed Apr 15 22:47:26 EDT 2020
You can put this in a combine.py file. Install python3 (preferably),
change the names of the files
at the bottom and give it a shot (python combine.py)
Python is a blast as far as languages are concerned, so give it a shot,
you probably won't regret it!
Good luck,
Jean
# This combines a list of strings (ofx_list) that are read from ofx files
# into a single ofx file suitable to be imported in GnuCash.
def combine_ofx(ofx_list,out_name):
if len(ofx_list) < 2: return
# This function finds the occurrences of <OFX> and </OFX> tags,
# and outputs the lines numbers in two arrays, along with the lines.
def findOFXTag(ofx_str):
ofxIdx = []
sofxIdx = []
# Some ofx files have all the tags combined in a single line!
# Split at < character.
all_lines = ofx_str.split('<')
out_lines = []
for ii,line in enumerate(all_lines):
# split() removes the '<' character, but we need it. Put it
back at the start
# of each line.
line='<'+line
if '<OFX>' in line: ofxIdx.append(ii)
if '</OFX>' in line: sofxIdx.append(ii)
out_lines.append(line)
# ofxIdx has the indices of lines that have <OFX>
# sofxIdx has the indices of lines that have </OFX>
# out_lines is an array of strings, one per line.
return ofxIdx,sofxIdx,out_lines
# Output the first ofx string to the last </OFX> (excluded) then
the next ones from <OFX> -> </OFX> (excluded)
print("Outputting first file")
o,s,lines = findOFXTag(ofx_list[0])
A = ''.join(lines[0:s[-1]])
for ofx in ofx_list[1:]:
print("Outputting next file")
o, s, lines = findOFXTag(ofx)
A += ''.join(lines[o[0]+1:s[-1]])
# Finally add the last </OFX> tag
A += '</OFX>\n'
# And write the file.
print("Writing out file",out_name)
with open(out_name,'w') as f:
f.write(A)
def read_ofx_file(file_name):
# This reads an ofx file and return a long string.
print("Reading",file_name)
with open(file_name) as f:
ofx_as_string = f.read()
return ofx_as_string
if __name__ == '__main__':
# You could use this this way:
file1 = r"E:\temp\Dropbox\OurAccount\OFX\80_Patelco_Checking.ofx"
file2 = r"E:\temp\Dropbox\OurAccount\OFX\79_Patelco_Checking.ofx"
file3 = r"E:\temp\Dropbox\OurAccount\OFX\77_Patelco_Visa.ofx"
all_strings =
[read_ofx_file(file1),read_ofx_file(file2),read_ofx_file(file3)]
combine_ofx(all_strings,'combined.ofx')
On 4/15/2020 6:49 PM, Stu Perlman wrote:
> Jean,
>
> I would love to see how you did it with Python. That's actually on the
> shortlist of languages I want to learn.
>
> I realized that at least in so far as the Citi produced OFX file is
> concerned, I will always need to delete a fixed number of lines. If I
> download only one account at a time, I must delete the first 30
> lines. I have been using sed to do this:
>
> sed -i '1,30d' /tmp/ofx.log
>
>
> I realized just now (after grabbing some sample downloads of various
> sizes) that if there is more than one account I need to find that
> first </OFX> tag and then delete that line and the 31 that follow. I
> tried using sed with regex but can't get it quite right as it only
> deletes the </OFX> line; the next 31 lines are left alone:
>
> sed -i '/<[^>]*\/OFX>/,32d' /tmp/ofx.log
>
>
> Much appreciation for your help!
>
> Stu
>
>
> On Wed, Apr 15, 2020 at 6: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!
>
> 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.
> >>
> >
>
More information about the gnucash-user
mailing list