Verifying Bank account numbers in Business Objects

Today, I was asked to do a data-quality check on bank account numbers -- of course, since I live in Belgium, I will focus on the Belgian bank account numbers, but the system is pretty much the same elsewhere -- and I'll be focussing on the limitations of Business Objects and how to get around them.

The basic calculation function that is being used is Modulo 97.

We'll be considering the following fictious IBAN Bank account number:

BE74857221143209

(I used http://www.mobilefish.com/services/random_iban_generator/random_iban_generator.php to generate a random number)

The first part we need to do, is move the left 4 characters to the right.

[strInvertedBacc]=Substr("BE74857221143209";5;Length("BE74857221143209")-4) + Left("BE74857221143209";4)

Which gives us:

857221143209BE74

Next, we need to replace the letters with numbers.

A = 10
B = 11
C = 12
D = 13
E = 14
....
Z = 35

Replace(Replace([strInvertedBacc];"B";"11");"E";"14")

I know this is a shortcut -- but I'm being pragmatic here -- to take into account all possible countrycodes would make the code way too complex.

So now our number has become:

857221143209111474

Now we perform the function tonumber() on it and..

Here we have the first indication why I'm writing this article. Business Objects makes a mistake and the number displayed is :

857221143209111000

That would be bad.

The solution in Business Objects is to treat the number in two halves. Excel has an even lower treshhold for error on this kind of calculation. There you have to work in groups of 5.


Mod(ToNumber(Left("857221143209111474";9));97)

gives us our left numbers

[leftnumbers] = 36

We turn that number into a string and we stick the right 9 numbers to it

=formatnumber([leftnumbers];"0") + Right(857221143209111474;9)

[newnumber] = 36209111474

which we turn into a number
And we perform another Modulo 97 on that:

= mod(tonumber([newnumber]);97)

= 1

And if the result is 1 -- the number is correct.

The total formula is:

=Mod(ToNumber(FormatNumber(Mod(ToNumber(Left("857221143209111474";9));97);"0") + Right("857221143209111474";9));97)

That's how it's done.

Enjoy,

Peter