[GNC] OFX Import with Multiple Banks/Accounts in a single file
Stu Perlman
sgperlman at gmail.com
Thu Apr 16 08:35:29 EDT 2020
Thanks so much for sharing this, Jean!
I look forward to trying it out.
On Wed, Apr 15, 2020 at 10:47 PM jean laroche <ripngo at gmail.com> wrote:
> 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> 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>> 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>> 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>> 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>> 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>>
>> >> > >> 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>>
>> >> > 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>>
>> >> > >>>> 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>>
>> >> > >>>>> 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>
>> >> > >>>>>> 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>
>> >> > >>>>> 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>
>> >> > > 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>
>> >> 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