Statement Import
125 views
Skip to first unread message
Albert Cervera i Areny
Oct 25, 2012, 4:44:44 AM10/25/12
to tryto...@googlegroups.com
Hi,
I intend to work on a generic bank statement import module which
should be the basis for importing and automating as much as possible
the input of bank statements in any file format.
In Spain there's the Norma 43 from the AEB but there are of course
many other formats such as the ones provided by PayPal, simple Excel
files and probably other countries have their own standards. The idea
is to provide the basis so those formats are new modules "like
plugins" that extend the account_statement_import module to understand
a given file format (or webservice?).
Main goals should include full traceability (how a given bank
statement line links with one or several tryton account statement
line) as well as a rule system that automates and eases as much as
possible the introduction of bank statements by making it robust
(helping in avoiding possible duplicate entries).
I've created a Blueprint here:
http://code.google.com/p/tryton/wiki/StatementImport
Comments are very welcomed!
--
Albert Cervera i Areny
http://www.nan-tic.com
Tel: 93 553 18 03
Twitter: @albertnan
I intend to work on a generic bank statement import module which
should be the basis for importing and automating as much as possible
the input of bank statements in any file format.
In Spain there's the Norma 43 from the AEB but there are of course
many other formats such as the ones provided by PayPal, simple Excel
files and probably other countries have their own standards. The idea
is to provide the basis so those formats are new modules "like
plugins" that extend the account_statement_import module to understand
a given file format (or webservice?).
Main goals should include full traceability (how a given bank
statement line links with one or several tryton account statement
line) as well as a rule system that automates and eases as much as
possible the introduction of bank statements by making it robust
(helping in avoiding possible duplicate entries).
I've created a Blueprint here:
http://code.google.com/p/tryton/wiki/StatementImport
Comments are very welcomed!
--
Albert Cervera i Areny
http://www.nan-tic.com
Tel: 93 553 18 03
Twitter: @albertnan
Cédric Krier
Oct 25, 2012, 5:29:22 AM10/25/12
to tryto...@googlegroups.com
On 25/10/12 10:44 +0200, Albert Cervera i Areny wrote:
> I've created a Blueprint here:
>
> http://code.google.com/p/tryton/wiki/StatementImport
>
> Comments are very welcomed!
I don't see the need of such infrastructure.
> I've created a Blueprint here:
>
> http://code.google.com/p/tryton/wiki/StatementImport
>
> Comments are very welcomed!
> Store the bank statement keeping the same data provided by the bank
It just needs to create a Binary field to store the imported file on the
statement. I think it is better to have one field per format so each
module that adds support for 1 format should create it.
> Rule system to ease and automate the process of finding invoices and
> reconciliation or determining accounts to use
I think we already have it: search_rec_name
> Rule system should be extensible
search_rec_name can be extended.
> Allow reverting and redoing the process by bank statement line => Full
> traceability of information from statement lines to imported (bank
> provided) statement lines.
The wizard that will import the file has to clean the statement before.
> Independent of file format
One wizard per format.
> Manage duplicate lines if dates or lines of two imported files overlap
I don't get you. If the bank send you 2 files with similar lines then
you have to import both of them. You must reflect what the bank tells
you. If it is a mistake of the bank than they should send you a new
statement with correction.
--
Cédric Krier
B2CK SPRL
Rue de Rotterdam, 4
4000 Liège
Belgium
Tel: +32 472 54 46 59
Email/Jabber: cedric...@b2ck.com
Website: http://www.b2ck.com/
Albert Cervera i Areny
Oct 25, 2012, 5:50:22 AM10/25/12
to tryto...@googlegroups.com
2012/10/25 Cédric Krier <cedric...@b2ck.com>:
>> Rule system to ease and automate the process of finding invoices and
>> reconciliation or determining accounts to use
>
> I think we already have it: search_rec_name
The problem is that bank statment file can contain several fields not
just one. Hence search_rec_name is not appropriate to handle this. For
example, some information that can be found (optionally) is the
description of the line, the VAT number of the sender/receiver, as
well as extra information. This already exists in AEB43 and I guess
others will have something similar.
So what we need is in one hand a way to extract information from
different file formats and put them in some kind of properties or
attributes. And then another process that has the logic for
determining each line which account, party and invoice is appropriate.
>> Rule system should be extensible
>
> search_rec_name can be extended.
>
>> Allow reverting and redoing the process by bank statement line => Full
>> traceability of information from statement lines to imported (bank
>> provided) statement lines.
>
> The wizard that will import the file has to clean the statement before.
Say you import a bank statement and you missed several invoices before
importing it. If we force the user to remove all lines (by
re-importing the file), any manual changes he may have done are lost.
The idea is that we import the original bank lines and those create
one or more statement lines. Those can be removed and recreated but
only the ones needed.
>> Independent of file format
>
> One wizard per format.
>
>> Manage duplicate lines if dates or lines of two imported files overlap
>
> I don't get you. If the bank send you 2 files with similar lines then
> you have to import both of them. You must reflect what the bank tells
> you. If it is a mistake of the bank than they should send you a new
> statement with correction.
At least here, the user is the one that goes to the bank's web pages
and asks for the bank statement from one day to another. Say from
01/01/2012 till 21/01/2012. The next day I go to the bank and should
ask for the one from 22/01/2012 till 31/01/2012 but what happens if
the user makes a mistake and the second time downloads a file from
21/01/2012? I want to detect those duplicates and help the user
discard them or, at least, ask him to re-obtain the file starting at
22/01/2012. I personally prefer that only inappropriate lines are
discarded because that allows users to download today's statement even
if not all moves are available yet. This allows some companies that
have online shops and allow payments by bank transfer to integrate
bank statements several times a day because they cannot wait until the
next day because they usually deliver in 24/48 hours.
>
> --
> Cédric Krier
>
> B2CK SPRL
> Rue de Rotterdam, 4
> 4000 Liège
> Belgium
> Tel: +32 472 54 46 59
> Email/Jabber: cedric...@b2ck.com
> Website: http://www.b2ck.com/
> On 25/10/12 10:44 +0200, Albert Cervera i Areny wrote:
>> I've created a Blueprint here:
>>
>> http://code.google.com/p/tryton/wiki/StatementImport
>>
>> Comments are very welcomed!
>
> I don't see the need of such infrastructure.
>
>> Store the bank statement keeping the same data provided by the bank
>
> It just needs to create a Binary field to store the imported file on the
> statement. I think it is better to have one field per format so each
> module that adds support for 1 format should create it.
I thought of using attachments. But I'm ok with a binary file too.
>> I've created a Blueprint here:
>>
>> http://code.google.com/p/tryton/wiki/StatementImport
>>
>> Comments are very welcomed!
>
> I don't see the need of such infrastructure.
>
>> Store the bank statement keeping the same data provided by the bank
>
> It just needs to create a Binary field to store the imported file on the
> statement. I think it is better to have one field per format so each
> module that adds support for 1 format should create it.
>> Rule system to ease and automate the process of finding invoices and
>> reconciliation or determining accounts to use
>
> I think we already have it: search_rec_name
just one. Hence search_rec_name is not appropriate to handle this. For
example, some information that can be found (optionally) is the
description of the line, the VAT number of the sender/receiver, as
well as extra information. This already exists in AEB43 and I guess
others will have something similar.
So what we need is in one hand a way to extract information from
different file formats and put them in some kind of properties or
attributes. And then another process that has the logic for
determining each line which account, party and invoice is appropriate.
>> Rule system should be extensible
>
> search_rec_name can be extended.
>
>> Allow reverting and redoing the process by bank statement line => Full
>> traceability of information from statement lines to imported (bank
>> provided) statement lines.
>
> The wizard that will import the file has to clean the statement before.
importing it. If we force the user to remove all lines (by
re-importing the file), any manual changes he may have done are lost.
The idea is that we import the original bank lines and those create
one or more statement lines. Those can be removed and recreated but
only the ones needed.
>> Independent of file format
>
> One wizard per format.
>
>> Manage duplicate lines if dates or lines of two imported files overlap
>
> I don't get you. If the bank send you 2 files with similar lines then
> you have to import both of them. You must reflect what the bank tells
> you. If it is a mistake of the bank than they should send you a new
> statement with correction.
and asks for the bank statement from one day to another. Say from
01/01/2012 till 21/01/2012. The next day I go to the bank and should
ask for the one from 22/01/2012 till 31/01/2012 but what happens if
the user makes a mistake and the second time downloads a file from
21/01/2012? I want to detect those duplicates and help the user
discard them or, at least, ask him to re-obtain the file starting at
22/01/2012. I personally prefer that only inappropriate lines are
discarded because that allows users to download today's statement even
if not all moves are available yet. This allows some companies that
have online shops and allow payments by bank transfer to integrate
bank statements several times a day because they cannot wait until the
next day because they usually deliver in 24/48 hours.
>
> --
> Cédric Krier
>
> B2CK SPRL
> Rue de Rotterdam, 4
> 4000 Liège
> Belgium
> Tel: +32 472 54 46 59
> Email/Jabber: cedric...@b2ck.com
> Website: http://www.b2ck.com/
Cédric Krier
Oct 25, 2012, 6:04:03 AM10/25/12
to tryto...@googlegroups.com
On 25/10/12 11:50 +0200, Albert Cervera i Areny wrote:
> 2012/10/25 Cédric Krier <cedric...@b2ck.com>:
> > On 25/10/12 10:44 +0200, Albert Cervera i Areny wrote:
> >> I've created a Blueprint here:
> >>
> >> http://code.google.com/p/tryton/wiki/StatementImport
> >>
> >> Comments are very welcomed!
> >
> > I don't see the need of such infrastructure.
> >
> >> Store the bank statement keeping the same data provided by the bank
> >
> > It just needs to create a Binary field to store the imported file on the
> > statement. I think it is better to have one field per format so each
> > module that adds support for 1 format should create it.
>
> I thought of using attachments. But I'm ok with a binary file too.
>
> >> Rule system to ease and automate the process of finding invoices and
> >> reconciliation or determining accounts to use
> >
> > I think we already have it: search_rec_name
>
> The problem is that bank statment file can contain several fields not
> just one. Hence search_rec_name is not appropriate to handle this. For
> example, some information that can be found (optionally) is the
> description of the line, the VAT number of the sender/receiver, as
> well as extra information. This already exists in AEB43 and I guess
> others will have something similar.
So search for each fields you have but you can not do that in a generic
> 2012/10/25 Cédric Krier <cedric...@b2ck.com>:
> > On 25/10/12 10:44 +0200, Albert Cervera i Areny wrote:
> >> I've created a Blueprint here:
> >>
> >> http://code.google.com/p/tryton/wiki/StatementImport
> >>
> >> Comments are very welcomed!
> >
> > I don't see the need of such infrastructure.
> >
> >> Store the bank statement keeping the same data provided by the bank
> >
> > It just needs to create a Binary field to store the imported file on the
> > statement. I think it is better to have one field per format so each
> > module that adds support for 1 format should create it.
>
> I thought of using attachments. But I'm ok with a binary file too.
>
> >> Rule system to ease and automate the process of finding invoices and
> >> reconciliation or determining accounts to use
> >
> > I think we already have it: search_rec_name
>
> The problem is that bank statment file can contain several fields not
> just one. Hence search_rec_name is not appropriate to handle this. For
> example, some information that can be found (optionally) is the
> description of the line, the VAT number of the sender/receiver, as
> well as extra information. This already exists in AEB43 and I guess
> others will have something similar.
way because each format will give you a specific set of information.
> >> Allow reverting and redoing the process by bank statement line => Full
> >> traceability of information from statement lines to imported (bank
> >> provided) statement lines.
> >
> > The wizard that will import the file has to clean the statement before.
>
> Say you import a bank statement and you missed several invoices before
> importing it. If we force the user to remove all lines (by
> re-importing the file), any manual changes he may have done are lost.
> The idea is that we import the original bank lines and those create
> one or more statement lines. Those can be removed and recreated but
> only the ones needed.
file and have tuned it. Why does he need to do it again?
By the way, it is the best way to miss a line in the import process.
Being KISS on this is really important for correctness.
> >> Manage duplicate lines if dates or lines of two imported files overlap
> >
> > I don't get you. If the bank send you 2 files with similar lines then
> > you have to import both of them. You must reflect what the bank tells
> > you. If it is a mistake of the bank than they should send you a new
> > statement with correction.
>
> At least here, the user is the one that goes to the bank's web pages
> and asks for the bank statement from one day to another. Say from
> 01/01/2012 till 21/01/2012. The next day I go to the bank and should
> ask for the one from 22/01/2012 till 31/01/2012 but what happens if
> the user makes a mistake and the second time downloads a file from
> 21/01/2012? I want to detect those duplicates and help the user
> discard them or, at least, ask him to re-obtain the file starting at
> 22/01/2012. I personally prefer that only inappropriate lines are
> discarded because that allows users to download today's statement even
> if not all moves are available yet. This allows some companies that
> have online shops and allow payments by bank transfer to integrate
> bank statements several times a day because they cannot wait until the
> next day because they usually deliver in 24/48 hours.
:-) because you can not retreive twice the same statement) than there
is a problem in the workflow. You must have a way to retrieve statements
that were not yet retreived otherwise there is no way to ensure
correctness.
In Tryton, we must force users to use the right way and not trying the
correct it afterwards.
Mathias Behrle
Oct 25, 2012, 10:48:12 AM10/25/12
to tryto...@googlegroups.com
* Betr.: " Re: [tryton-dev] Statement Import" (Thu, 25 Oct 2012 12:04:03 +0200):
> If this is really what can happen (not in Belgium (knonw for its banks
> :-) because you can not retreive twice the same statement) than there
> is a problem in the workflow. You must have a way to retrieve statements
> that were not yet retreived otherwise there is no way to ensure
> correctness.
Just answering to this point only because I am lacking the time to take part in
the whole discussion:
You don't use banking programs in Belgium? AFAIK each serious banking software
has the ability to export statements belonging to random periods. It is
absolutely necessary to have a reliable mechanism to detect duplicates resp.
the correct sequence, if import files should overlap. The same way you must
recognize, if there is a gap between imports.
> In Tryton, we must force users to use the right way and not trying the
> correct it afterwards.
Welcome in the real world. You should be aware, that it is very often not
possible to determine 'one only right way' to meet the requirements of
different countries and purposes. I think I also remember your attitude 'the
fewest constraints as possible in a generic application'...;), which fits much
better in this subject.
--
Mathias Behrle
MBSolutions
Gilgenmatten 10 A
D-79114 Freiburg
Tel: +49(761)471023
Fax: +49(761)4770816
http://m9s.biz
UStIdNr: DE 142009020
PGP/GnuPG key availabable from any keyserver, ID: 0x8405BBF6
> If this is really what can happen (not in Belgium (knonw for its banks
> :-) because you can not retreive twice the same statement) than there
> is a problem in the workflow. You must have a way to retrieve statements
> that were not yet retreived otherwise there is no way to ensure
> correctness.
the whole discussion:
You don't use banking programs in Belgium? AFAIK each serious banking software
has the ability to export statements belonging to random periods. It is
absolutely necessary to have a reliable mechanism to detect duplicates resp.
the correct sequence, if import files should overlap. The same way you must
recognize, if there is a gap between imports.
> In Tryton, we must force users to use the right way and not trying the
> correct it afterwards.
possible to determine 'one only right way' to meet the requirements of
different countries and purposes. I think I also remember your attitude 'the
fewest constraints as possible in a generic application'...;), which fits much
better in this subject.
--
Mathias Behrle
MBSolutions
Gilgenmatten 10 A
D-79114 Freiburg
Tel: +49(761)471023
Fax: +49(761)4770816
http://m9s.biz
UStIdNr: DE 142009020
PGP/GnuPG key availabable from any keyserver, ID: 0x8405BBF6
Cédric Krier
Oct 25, 2012, 11:37:12 AM10/25/12
to tryto...@googlegroups.com
On 25/10/12 16:48 +0200, Mathias Behrle wrote:
> * Betr.: " Re: [tryton-dev] Statement Import" (Thu, 25 Oct 2012 12:04:03 +0200):
>
> > If this is really what can happen (not in Belgium (knonw for its banks
> > :-) because you can not retreive twice the same statement) than there
> > is a problem in the workflow. You must have a way to retrieve statements
> > that were not yet retreived otherwise there is no way to ensure
> > correctness.
>
> Just answering to this point only because I am lacking the time to take part in
> the whole discussion:
>
> You don't use banking programs in Belgium? AFAIK each serious banking software
> has the ability to export statements belonging to random periods. It is
> absolutely necessary to have a reliable mechanism to detect duplicates resp.
> the correct sequence, if import files should overlap. The same way you must
> recognize, if there is a gap between imports.
I know what I'm talking about. I worked 2 years in a bank.
> * Betr.: " Re: [tryton-dev] Statement Import" (Thu, 25 Oct 2012 12:04:03 +0200):
>
> > If this is really what can happen (not in Belgium (knonw for its banks
> > :-) because you can not retreive twice the same statement) than there
> > is a problem in the workflow. You must have a way to retrieve statements
> > that were not yet retreived otherwise there is no way to ensure
> > correctness.
>
> Just answering to this point only because I am lacking the time to take part in
> the whole discussion:
>
> You don't use banking programs in Belgium? AFAIK each serious banking software
> has the ability to export statements belonging to random periods. It is
> absolutely necessary to have a reliable mechanism to detect duplicates resp.
> the correct sequence, if import files should overlap. The same way you must
> recognize, if there is a gap between imports.
First of all, in belgium asking for a statement you already received is
charged by the bank. And a bank should not send you twice the same
statement by default, this is very basic logici if they don't want
customer complaints.
Second, detect duplicates could not be generic, again it will depend on
each format. I'm pretty that each standard format that exists will have
a unique reference for the statement and that this statement could not
contains duplicates lines.
> > In Tryton, we must force users to use the right way and not trying the
> > correct it afterwards.
>
> Welcome in the real world. You should be aware, that it is very often not
> possible to determine 'one only right way' to meet the requirements of
> different countries and purposes. I think I also remember your attitude 'the
> fewest constraints as possible in a generic application'...;), which fits much
> better in this subject.
Mathias Behrle
Oct 25, 2012, 12:14:05 PM10/25/12
to tryto...@googlegroups.com
* Betr.: " Re: [tryton-dev] Statement Import" (Thu, 25 Oct 2012 17:37:12 +0200):
> On 25/10/12 16:48 +0200, Mathias Behrle wrote:
> > * Betr.: " Re: [tryton-dev] Statement Import" (Thu, 25 Oct 2012 12:04:03
> > +0200):
> >
> > > If this is really what can happen (not in Belgium (knonw for its banks
> > > :-) because you can not retreive twice the same statement) than there
> > > is a problem in the workflow. You must have a way to retrieve statements
> > > that were not yet retreived otherwise there is no way to ensure
> > > correctness.
> >
> > Just answering to this point only because I am lacking the time to take
> > part in the whole discussion:
> >
> > You don't use banking programs in Belgium? AFAIK each serious banking
> > software has the ability to export statements belonging to random periods.
> > It is absolutely necessary to have a reliable mechanism to detect
> > duplicates resp. the correct sequence, if import files should overlap. The
> > same way you must recognize, if there is a gap between imports.
>
> I know what I'm talking about. I worked 2 years in a bank.
> First of all, in belgium asking for a statement you already received is
> charged by the bank. And a bank should not send you twice the same
> statement by default, this is very basic logici if they don't want
> customer complaints.
> Second, detect duplicates could not be generic, again it will depend on
> each format. I'm pretty that each standard format that exists will have
> a unique reference for the statement and that this statement could not
> contains duplicates lines.
PLease re-read. I talked about exports from a banking software. And sorry, no,
there is no unique reference despite all assumptions one would make
(specifically talking about format MT940, which is a very current exchange
format in Germany).
> On 25/10/12 16:48 +0200, Mathias Behrle wrote:
> > * Betr.: " Re: [tryton-dev] Statement Import" (Thu, 25 Oct 2012 12:04:03
> > +0200):
> >
> > > If this is really what can happen (not in Belgium (knonw for its banks
> > > :-) because you can not retreive twice the same statement) than there
> > > is a problem in the workflow. You must have a way to retrieve statements
> > > that were not yet retreived otherwise there is no way to ensure
> > > correctness.
> >
> > Just answering to this point only because I am lacking the time to take
> > part in the whole discussion:
> >
> > You don't use banking programs in Belgium? AFAIK each serious banking
> > software has the ability to export statements belonging to random periods.
> > It is absolutely necessary to have a reliable mechanism to detect
> > duplicates resp. the correct sequence, if import files should overlap. The
> > same way you must recognize, if there is a gap between imports.
>
> I know what I'm talking about. I worked 2 years in a bank.
> First of all, in belgium asking for a statement you already received is
> charged by the bank. And a bank should not send you twice the same
> statement by default, this is very basic logici if they don't want
> customer complaints.
> Second, detect duplicates could not be generic, again it will depend on
> each format. I'm pretty that each standard format that exists will have
> a unique reference for the statement and that this statement could not
> contains duplicates lines.
there is no unique reference despite all assumptions one would make
(specifically talking about format MT940, which is a very current exchange
format in Germany).
Cédric Krier
Oct 25, 2012, 12:16:31 PM10/25/12
to tryto...@googlegroups.com
On 25/10/12 18:14 +0200, Mathias Behrle wrote:
> And sorry, no,
> there is no unique reference despite all assumptions one would make
> (specifically talking about format MT940, which is a very current exchange
> format in Germany).
If there is no unique reference than it is impossible to detect
> And sorry, no,
> there is no unique reference despite all assumptions one would make
> (specifically talking about format MT940, which is a very current exchange
> format in Germany).
duplicates.
Bertrand Chenal
Oct 25, 2012, 12:35:45 PM10/25/12
to tryto...@googlegroups.com
Le Thu, 25 Oct 2012 18:14:05 +0200,
Mathias Behrle <mbe...@m9s.biz> a écrit :
> * Betr.: " Re: [tryton-dev] Statement Import" (Thu, 25 Oct 2012
> 17:37:12 +0200):
I see a field "Statement Number/Sequence Number" which identify the
statement inside the year.
As Albert said earlier, there is a lot of different format. I fail to
see how to find common concepts except the statement itself.
--
Bertrand Chenal
B2CK SPRL
Rue de Rotterdam, 4
4000 Liège
Belgium
Email: bertran...@b2ck.com
Website: http://www.b2ck.com/
Mathias Behrle <mbe...@m9s.biz> a écrit :
> * Betr.: " Re: [tryton-dev] Statement Import" (Thu, 25 Oct 2012
> 17:37:12 +0200):
> > I know what I'm talking about. I worked 2 years in a bank.
> > First of all, in belgium asking for a statement you already
> > received is charged by the bank. And a bank should not send you
> > twice the same statement by default, this is very basic logici if
> > they don't want customer complaints.
> > Second, detect duplicates could not be generic, again it will
> > depend on each format. I'm pretty that each standard format that
> > exists will have a unique reference for the statement and that this
> > statement could not contains duplicates lines.
>
> PLease re-read. I talked about exports from a banking software. And
> sorry, no, there is no unique reference despite all assumptions one
> would make (specifically talking about format MT940, which is a very
> current exchange format in Germany).
Are you sure? from martin.hinner.info/bankconvert/swift_mt940_942.pdf,
> > First of all, in belgium asking for a statement you already
> > received is charged by the bank. And a bank should not send you
> > twice the same statement by default, this is very basic logici if
> > they don't want customer complaints.
> > Second, detect duplicates could not be generic, again it will
> > depend on each format. I'm pretty that each standard format that
> > exists will have a unique reference for the statement and that this
> > statement could not contains duplicates lines.
>
> PLease re-read. I talked about exports from a banking software. And
> sorry, no, there is no unique reference despite all assumptions one
> would make (specifically talking about format MT940, which is a very
> current exchange format in Germany).
I see a field "Statement Number/Sequence Number" which identify the
statement inside the year.
As Albert said earlier, there is a lot of different format. I fail to
see how to find common concepts except the statement itself.
--
Bertrand Chenal
B2CK SPRL
Rue de Rotterdam, 4
4000 Liège
Belgium
Website: http://www.b2ck.com/
Albert Cervera i Areny
Oct 25, 2012, 12:41:09 PM10/25/12
to tryto...@googlegroups.com
> El dijous 25 d’octubre de 2012 18:32:59 UTC+2, Bertrand Chenal va escriure:It may not be possible to have a common identifier but maybe we could agree on a way to manage/store those duplicates.
Mathias Behrle
Oct 25, 2012, 1:01:42 PM10/25/12
to tryto...@googlegroups.com
* Betr.: " Re: [tryton-dev] Statement Import" (Thu, 25 Oct 2012 18:35:45 +0200):
> Le Thu, 25 Oct 2012 18:14:05 +0200,
> Mathias Behrle <mbe...@m9s.biz> a écrit :
>
> > * Betr.: " Re: [tryton-dev] Statement Import" (Thu, 25 Oct 2012
> > 17:37:12 +0200):
>
> > > I know what I'm talking about. I worked 2 years in a bank.
> > > First of all, in belgium asking for a statement you already
> > > received is charged by the bank. And a bank should not send you
> > > twice the same statement by default, this is very basic logici if
> > > they don't want customer complaints.
> > > Second, detect duplicates could not be generic, again it will
> > > depend on each format. I'm pretty that each standard format that
> > > exists will have a unique reference for the statement and that this
> > > statement could not contains duplicates lines.
> >
> > PLease re-read. I talked about exports from a banking software. And
> > sorry, no, there is no unique reference despite all assumptions one
> > would make (specifically talking about format MT940, which is a very
> > current exchange format in Germany).
>
> Are you sure? from martin.hinner.info/bankconvert/swift_mt940_942.pdf,
> I see a field "Statement Number/Sequence Number" which identify the
> statement inside the year.
Usage of those fields is far from being standardized, at least with German
banks. There is for example field :20: of MT940, which is according to
[1] (the standard document we are using as base for our implementations)
designed to be a reference number. The problem is, that many (all?) german
banks just don't use it this way...
To our knowledge it is necessary to compare balances and accounts, as far as
available, to provide reliable import results.
> As Albert said earlier, there is a lot of different format. I fail to
> see how to find common concepts except the statement itself.
+1
[1]
http://www.bankenverband.de/downloads/012010/version-2-4-anlage-3-dfue-abkommen.pdf
> Le Thu, 25 Oct 2012 18:14:05 +0200,
> Mathias Behrle <mbe...@m9s.biz> a écrit :
>
> > * Betr.: " Re: [tryton-dev] Statement Import" (Thu, 25 Oct 2012
> > 17:37:12 +0200):
>
> > > I know what I'm talking about. I worked 2 years in a bank.
> > > First of all, in belgium asking for a statement you already
> > > received is charged by the bank. And a bank should not send you
> > > twice the same statement by default, this is very basic logici if
> > > they don't want customer complaints.
> > > Second, detect duplicates could not be generic, again it will
> > > depend on each format. I'm pretty that each standard format that
> > > exists will have a unique reference for the statement and that this
> > > statement could not contains duplicates lines.
> >
> > PLease re-read. I talked about exports from a banking software. And
> > sorry, no, there is no unique reference despite all assumptions one
> > would make (specifically talking about format MT940, which is a very
> > current exchange format in Germany).
>
> Are you sure? from martin.hinner.info/bankconvert/swift_mt940_942.pdf,
> I see a field "Statement Number/Sequence Number" which identify the
> statement inside the year.
banks. There is for example field :20: of MT940, which is according to
[1] (the standard document we are using as base for our implementations)
designed to be a reference number. The problem is, that many (all?) german
banks just don't use it this way...
To our knowledge it is necessary to compare balances and accounts, as far as
available, to provide reliable import results.
> As Albert said earlier, there is a lot of different format. I fail to
> see how to find common concepts except the statement itself.
[1]
http://www.bankenverband.de/downloads/012010/version-2-4-anlage-3-dfue-abkommen.pdf
Bertrand Chenal
Oct 25, 2012, 1:09:06 PM10/25/12
to tryto...@googlegroups.com
Le Thu, 25 Oct 2012 09:41:09 -0700 (PDT),
Albert Cervera i Areny <alb...@nan-tic.com> a écrit :
> > El dijous 25 d’octubre de 2012 18:32:59 UTC+2, Bertrand Chenal va
> >
duplicate the only thing to do is to ignore it and warn the user,
isn't it?
Albert Cervera i Areny <alb...@nan-tic.com> a écrit :
> > El dijous 25 d’octubre de 2012 18:32:59 UTC+2, Bertrand Chenal va
> >
> > As Albert said earlier, there is a lot of different format. I fail
> > to see how to find common concepts except the statement itself.
>
> It may not be possible to have a common identifier but maybe we could
> agree on a way to manage/store those duplicates.
IMO the challenge is to detect duplicates. If we know we have a
> > to see how to find common concepts except the statement itself.
>
> It may not be possible to have a common identifier but maybe we could
> agree on a way to manage/store those duplicates.
duplicate the only thing to do is to ignore it and warn the user,
isn't it?
Albert Cervera i Areny
Oct 25, 2012, 1:38:13 PM10/25/12
to tryto...@googlegroups.com
El dijous 25 d’octubre de 2012 19:06:19 UTC+2, Bertrand Chenal va escriure:
> Le Thu, 25 Oct 2012 09:41:09 -0700 (PDT),
> Albert Cervera i Areny <alb...@nan-tic.com> a écrit :
> > > El dijous 25 d’octubre de 2012 18:32:59 UTC+2, Bertrand Chenal va
> > >
> > > As Albert said earlier, there is a lot of different format. I fail
> > > to see how to find common concepts except the statement itself.
> >
> > It may not be possible to have a common identifier but maybe we could
> > agree on a way to manage/store those duplicates.
>
> IMO the challenge is to detect duplicates. If we know we have a
> duplicate the only thing to do is to ignore it and warn the user,
> isn't it?
Albert Cervera i Areny
Oct 25, 2012, 2:04:13 PM10/25/12
to tryto...@googlegroups.com
El dijous 25 d’octubre de 2012 12:04:06 UTC+2, Cédric Krier va escriure:
> On 25/10/12 11:50 +0200, Albert Cervera i Areny wrote:
> > 2012/10/25 Cédric Krier <cedric...@b2ck.com>:
> > > On 25/10/12 10:44 +0200, Albert Cervera i Areny wrote:
> > >> I've created a Blueprint here:
> > >>
> > >> http://code.google.com/p/tryton/wiki/StatementImport
> > >>
> > >> Comments are very welcomed!
> > >
> > > I don't see the need of such infrastructure.
> > >
> > >> Store the bank statement keeping the same data provided by the bank
> > >
> > > It just needs to create a Binary field to store the imported file on the
> > > statement. I think it is better to have one field per format so each
> > > module that adds support for 1 format should create it.
> >
> > I thought of using attachments. But I'm ok with a binary file too.
> >
> > >> Rule system to ease and automate the process of finding invoices and
> > >> reconciliation or determining accounts to use
> > >
> > > I think we already have it: search_rec_name
> >
> > The problem is that bank statment file can contain several fields not
> > just one. Hence search_rec_name is not appropriate to handle this. For
> > example, some information that can be found (optionally) is the
> > description of the line, the VAT number of the sender/receiver, as
> > well as extra information. This already exists in AEB43 and I guess
> > others will have something similar.
>
> So search for each fields you have but you can not do that in a generic
> way because each format will give you a specific set of information.
> > >> Allow reverting and redoing the process by bank statement line => Full
> > >> traceability of information from statement lines to imported (bank
> > >> provided) statement lines.
> > >
> > > The wizard that will import the file has to clean the statement before.
> >
> > Say you import a bank statement and you missed several invoices before
> > importing it. If we force the user to remove all lines (by
> > re-importing the file), any manual changes he may have done are lost.
> > The idea is that we import the original bank lines and those create
> > one or more statement lines. Those can be removed and recreated but
> > only the ones needed.
>
> I don't understand the scenario. If the user has already imported the
> file and have tuned it. Why does he need to do it again?
> By the way, it is the best way to miss a line in the import process.
> Being KISS on this is really important for correctness.
>
> > >> Manage duplicate lines if dates or lines of two imported files overlap
> > >
> > > I don't get you. If the bank send you 2 files with similar lines then
> > > you have to import both of them. You must reflect what the bank tells
> > > you. If it is a mistake of the bank than they should send you a new
> > > statement with correction.
> >
> > At least here, the user is the one that goes to the bank's web pages
> > and asks for the bank statement from one day to another. Say from
> > 01/01/2012 till 21/01/2012. The next day I go to the bank and should
> > ask for the one from 22/01/2012 till 31/01/2012 but what happens if
> > the user makes a mistake and the second time downloads a file from
> > 21/01/2012? I want to detect those duplicates and help the user
> > discard them or, at least, ask him to re-obtain the file starting at
> > 22/01/2012. I personally prefer that only inappropriate lines are
> > discarded because that allows users to download today's statement even
> > if not all moves are available yet. This allows some companies that
> > have online shops and allow payments by bank transfer to integrate
> > bank statements several times a day because they cannot wait until the
> > next day because they usually deliver in 24/48 hours.
>
> If this is really what can happen (not in Belgium (knonw for its banks
> :-) because you can not retreive twice the same statement) than there
> is a problem in the workflow. You must have a way to retrieve statements
> that were not yet retreived otherwise there is no way to ensure
> correctness.
> On 25/10/12 11:50 +0200, Albert Cervera i Areny wrote:
> > 2012/10/25 Cédric Krier <cedric...@b2ck.com>:
> > > On 25/10/12 10:44 +0200, Albert Cervera i Areny wrote:
> > >> I've created a Blueprint here:
> > >>
> > >> http://code.google.com/p/tryton/wiki/StatementImport
> > >>
> > >> Comments are very welcomed!
> > >
> > > I don't see the need of such infrastructure.
> > >
> > >> Store the bank statement keeping the same data provided by the bank
> > >
> > > It just needs to create a Binary field to store the imported file on the
> > > statement. I think it is better to have one field per format so each
> > > module that adds support for 1 format should create it.
> >
> > I thought of using attachments. But I'm ok with a binary file too.
> >
> > >> Rule system to ease and automate the process of finding invoices and
> > >> reconciliation or determining accounts to use
> > >
> > > I think we already have it: search_rec_name
> >
> > The problem is that bank statment file can contain several fields not
> > just one. Hence search_rec_name is not appropriate to handle this. For
> > example, some information that can be found (optionally) is the
> > description of the line, the VAT number of the sender/receiver, as
> > well as extra information. This already exists in AEB43 and I guess
> > others will have something similar.
>
> So search for each fields you have but you can not do that in a generic
> way because each format will give you a specific set of information.
It depends. Amount and description are quite usually there. Also, there are some complex cases. For example, in Spain you may receive a single payment that corresponds to the payment of several customers (yes, I know it sounds strange to you) which the user may have already sent to the bank the request. Don't want to get into details about this but basically one bank statement line can correspond to hundreds of different invoices (have now a customer that corresponds to thousands of them). That can usually be automated (we inform the system that there will be this payment) but in OpenERP the search system was linked to the import process and had to change it because you may not have some information at the import time on the system. Then you make changes to the statement and when you have all the information you want the system to "recheck" some of the lines of the original statement. In order for the automatic system to find invoices (or whatever) for the user.
> > >> Allow reverting and redoing the process by bank statement line => Full
> > >> traceability of information from statement lines to imported (bank
> > >> provided) statement lines.
> > >
> > > The wizard that will import the file has to clean the statement before.
> >
> > Say you import a bank statement and you missed several invoices before
> > importing it. If we force the user to remove all lines (by
> > re-importing the file), any manual changes he may have done are lost.
> > The idea is that we import the original bank lines and those create
> > one or more statement lines. Those can be removed and recreated but
> > only the ones needed.
>
> I don't understand the scenario. If the user has already imported the
> file and have tuned it. Why does he need to do it again?
As I said above, there may not be all the information in the system when the file was imported. Then the user starts working on the system, after some time realizes some information is missing. Adds it, and wants to reprocess only those lines of the bank's statement that were not yet manually manipulated.
> By the way, it is the best way to miss a line in the import process.
You wouldn't miss a line because the original statement would be imported in a different model (account.statement.import.line, for example). Each of those lines would be linked to one or more statement lines.
> Being KISS on this is really important for correctness.
>
> > >> Manage duplicate lines if dates or lines of two imported files overlap
> > >
> > > I don't get you. If the bank send you 2 files with similar lines then
> > > you have to import both of them. You must reflect what the bank tells
> > > you. If it is a mistake of the bank than they should send you a new
> > > statement with correction.
> >
> > At least here, the user is the one that goes to the bank's web pages
> > and asks for the bank statement from one day to another. Say from
> > 01/01/2012 till 21/01/2012. The next day I go to the bank and should
> > ask for the one from 22/01/2012 till 31/01/2012 but what happens if
> > the user makes a mistake and the second time downloads a file from
> > 21/01/2012? I want to detect those duplicates and help the user
> > discard them or, at least, ask him to re-obtain the file starting at
> > 22/01/2012. I personally prefer that only inappropriate lines are
> > discarded because that allows users to download today's statement even
> > if not all moves are available yet. This allows some companies that
> > have online shops and allow payments by bank transfer to integrate
> > bank statements several times a day because they cannot wait until the
> > next day because they usually deliver in 24/48 hours.
>
> If this is really what can happen (not in Belgium (knonw for its banks
> :-) because you can not retreive twice the same statement) than there
> is a problem in the workflow. You must have a way to retrieve statements
> that were not yet retreived otherwise there is no way to ensure
> correctness.
We may agree that there's a problem in the workflow but that part of the workflow is out of my control (depends on each bank UI), so I should help users not to make this kind of mistakes.
Cédric Krier
Oct 25, 2012, 2:42:30 PM10/25/12
to tryto...@googlegroups.com
On 25/10/12 11:04 -0700, Albert Cervera i Areny wrote:
> El dijous 25 d’octubre de 2012 12:04:06 UTC+2, Cédric Krier va escriure:
> > On 25/10/12 11:50 +0200, Albert Cervera i Areny wrote:
> > > 2012/10/25 Cédric Krier <cedric...@b2ck.com <javascript:>>:
> El dijous 25 d’octubre de 2012 12:04:06 UTC+2, Cédric Krier va escriure:
> > On 25/10/12 11:50 +0200, Albert Cervera i Areny wrote:
If you don't find invoice on the statement that's not a big issue, just
make the reconciliation later.
Raimon Esteve
Mar 30, 2013, 8:58:40 AM3/30/13
to tryto...@googlegroups.com
> We are running OpenERP v5 and are investigating if we are going to move to Tryton or stay with OpenERP.
> Because we are a reseller we have always a lot of statements which we want to import automatically, so we build an import-framework our self for OpenERP and released it to the community. Edusense, a Dutch company has completely changed the import-framework, and it works much better now. IMHO Tryton needs such a module for importing statements. See http://www.edusense.nl/index.php/en/downloads for the source-code
We have a module for spanish statements & co. Tryton offer a API to develop it
Reply all
Reply to author
Forward
0 new messages