Re: CAMT053 - One bank statement import format for SEPA

Hi Erik;

On Sat, Oct 12, 2013 at 12:38 PM, Erik Huelsmann <..hidden..> wrote:

Hi,

Looking to improve my bank statement import and reconciliation process, I found that my bank offers the option to download CAMT053 format files. I then noted that many banks offer this type of download which is an XML format download as it seems to be part of the SEPA specification.

Looking at my own transaction logs in this format (admittedly in my personal account, I haven't looked in my business account yet), I see many variations of combinations of fields having been used. So, I'm now wondering about the best strategy to

1. Extract info from the XML; and
2. To match extracted info from the XML in the reconciliation

Ok, so LSMB can currently make a best effort attempt at the latter if you hand in data from the former. So it is worth mostly noting the restrictions on the latter, and how it works, as well as how to do the former.

I assume we'd probably want to rename the LedgerSMB::Reconciliation::CSV space, because it could be more generally used. It actually doesn't do anything CSV-specific. If you are using XML you'd still put the importers in that namespace currently, I am afraid.

Any .pl script in LedgerSMB/Reconciliation/CSV/Formats/ is automatically detected and can be used at import time.

What I would look at doing would be to add a module LedgerSMB::Reconciliation::SEPA which could take the file and parse it, handing the results back in the order received. You'd then have a file like LedgerSMB/Reconciliation/CSV/sepa_accounts.pl which would do the actual work.

Some banks send very nice descriptive info in the payments, like bank account, postal address and name of the transaction counter party *and* the other bank involved in the transaction. However, some banks send nothing but a blurb of payment data which doesn't have any semantical markup. Take this example (the text comes all in one line): """11.58.56.730 ZIGGO B.V. BETALINGSKENM. ARNL55636877895 KLANTNR* 11136210 ABON T/M OKT, FACT.NR* 395630014FACT. OP WWW.ZIGGO.NL/MIJNZIGGO"""

Without the quotes. How do I assign meaning to that blurb and blurbs like it when feeding data to my reconciliation routine?

Ok, so the LedgerSMB matching routines work on ordered entries, so it is important to put those entries with the best matches first. Otherwise you can get errors matching down the road. Typically we are going to match first on source, then on date and amount, then on amount, and then on date, iirc.

If you can extract a source which is also present in your own db, those entries should come first. You will then return a list of hashrefs containing at least the following entries:

* cleared_date

* amount

If you can set scn to the source that is even better. Then the file can be handled through the file upload functionality.

Hope this helps,

Chris Travers

I'd like to tackle this format in a way that others benefit as well, so I'm looking forward to read your comments!

--
Bye,

Erik.

http://efficito.com -- Hosted accounting and ERP.
Robust and Flexible. No vendor lock-in.

------------------------------------------------------------------------------
October Webinars: Code for Performance
Free Intel webinars can help you accelerate application performance.
Explore tips for MPI, OpenMP, advanced profiling, and more. Get the most from
the latest Intel processors and coprocessors. See abstracts and register >
http://pubads.g.doubleclick.net/gampad/clk?id=60134071&iu=/4140/ostg.clktrk
_______________________________________________
Ledger-smb-devel mailing list
..hidden..
https://lists.sourceforge.net/lists/listinfo/ledger-smb-devel

Best Wishes,

Chris Travers

Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in.

http://www.efficito.com/learn_more.shtml