Internationale Bankleitzahlen/Kontonummern SWIFT/BIC/IBAN
Holger Wirtz
suche für ein Projekt eine Bankleitzahlentabelle die die BIC für deutsche
Sparkassen und Banken, inkl Bezeichnung der Sparkasse oder Bank im Klartext
aufführt.
Kennt jemand darüberhinaus die Prüflogik einer IBAN-Kontonummer?
Für Hinweise und Tipps wäre ich sehr dankbar.
Viele Grüße
Holger
Michel Fouquet
Holger Wirtz schrieb:
>
> Kennt jemand darüberhinaus die Prüflogik einer IBAN-Kontonummer?
>
die Benutzung einer Internetsuchmaschine mit den Begriffen
Prüfziffer IBAN
ergibt ca. 14.700 Treffer. Bereits die allerersten sollten helfen, z.B.
http://www.pruefziffernberechnung.de/I/IBAN.shtml
http://www.zahlungsverkehrsfragen.de/iban.html
Gruß,
Michel
Thomas Möller
Holger Wirtz <wi...@softworld-seminare.de> schrieb:
> suche für ein Projekt eine Bankleitzahlentabelle die die BIC für
> deutsche Sparkassen und Banken, inkl Bezeichnung der Sparkasse oder
> Bank im Klartext aufführt.
schau mal bei der Bundesbank:
http://www.bundesbank.de/zahlungsverkehr/zahlungsverkehr_bankleitzahlen_download.php
(Link in einer Zeile)
Dort findest Du eine BLZ-Tabelle zum Download. Darin ist auch die BIC
enthalten.
HTH
--
Thomas
Homepage: www.Team-Moeller.de
Klaus Oberdalhoff
> suche für ein Projekt eine Bankleitzahlentabelle die die BIC für
> deutsche Sparkassen und Banken, inkl Bezeichnung der Sparkasse oder
> Bank im Klartext aufführt.
>
> Kennt jemand darüberhinaus die Prüflogik einer IBAN-Kontonummer?
a)
http://www.bundesbank.de/zahlungsverkehr/zahlungsverkehr_bankleitzahlen_download.php
b) von Henry Habermacher übernommen
Option Compare Database
Option Explicit
'IBAN Number Generation in Germany
'
'In Deutschland kann man, wenn die Kontonummer sowie die BLZ bekannt ist,
daraus die IBAN Nr generieren
'
'Info aus http://www.europebanks.info/ibanguide.htm#iban
'
'Algorithm to Generate IBAN Account Numbers
'The most common algorithm that is used to generate or calculate IBAN
account numbers is using
'Mod 10,97 (ISO 7064) model which is mostly applied in banking industry.
This scheme produces
'two digits as a check or control digits. And as a result, it catches just
about every possible error.
'The general formula is check digits are given by Control digit (2) =
mod(98 - mod(data * 100, 97), 97).
'
'
'Most banks in europe union countries like UK, Germany, France, Italy,
Switzerland, Spain,
'Austria, Belgium and other countries use this algorithm to compute the IBAN
number. The standard
'method to compute IBAN from existing domestic account number is explained
in steps below..
'
'Let us assume that we are given account number and other parameters so that
we are required to
'compute IBAN for germany.
'
'We know that for germany to compute IBAN, the following data are required
to compute IBAN.
'Refer to ECBS for complete reference regarding IBAN for specific countries.
'
'Country code (2a)
'Control Digit (2n):
'Bank code or Bankleitzahl (BLZ) (8n)
'Account number (10n)
'____________________________________________
'Total 22(an) in length.
'Note: 'a' means alphabets only, 'n' numbers only, 'an' means numbers or
alphabets
'Given IBAN Parameters:
'
'Country code(2a): DE (ISO country code for germany)
'Control Digit(2n): ?? (not available)
'Bank code or Bankleitzahl (BLZ) 8n: 37040044
'Account number (10n): 532.013.000
'Total length of IBAN for Germany: 22 an
'
'Goal: To order to compute the IBAN number we need all the 4 parameters as
for germany.
'As you observe above, we still don't know the control digit, only then we
can successfully
'generate IBAN. So our goal is to compute the check digit from above given
parameters.
'
'Step 1:
'
'Stripp off all bank spaces and other special characters like dots, commas,
hyphens of account no,
'bank code blz and country code are to be written continously. If the data
given does not fit in
'length then padd zeros in front. This is must and important.
'
'For Germany, Account no (10n) must have to be 10 digits in length. So
account no is rewritten as
'
'Account no: 0532013000 (10n)
'BLZ (8n): 37040044
'Country code (2a): DE
'
'Step 2:
'
'Concatenate (Join) bank code blz and account number without any spaces.
This becomes
'
'370400440532013000
'
'Step 3:
'
'Again join the step 3 value to: 'DE00'
'
'370400440532013000DE00
'
'Step 4:
'
'In this step, you must convert the whole result from step 3into fully
numbers, according the
'table given below, by eliminating any alphabet characters.
'
'A 10 I 18 Q 26 Y 34
'B 11 J 19 R 27 Z 35
'C 12 K 20 S 28
'D 13 L 21 T 29
'E 14 M 22 U 30
'F 15 N 23 V 31
'G 16 O 24 W 32
'H 17 P 25 X 33
'
'370400440532013000DE00 becomes 370400440532013000131400
'
'Step 6:
'
'Now, you have to perform a modulo 97 operation on the result derived from
step 5 and find that result
'
'370400440532013000131400 mod 97
'
'The Result = 9
'
'Step 7:
'
'In this step, we will catch hold of control digits, because once we get
control digits,
'then the data required for iban number is complete.
'
'Subtract 9 from 98:
'
'98 - 9 = 89
'
'This 89 is our control digit.
'Now, fill in the IBAN slots with our data.
'Country Code: DE (2a)
'Control Digit: 89 (2n)
'Bank Code or BLZ : 37040044 (8n)
'Account number: 0532013000 (10n)
'which becomes
'
'DE89 37040044 0532013000
'(An IBAN is always grouped in 4s separated by blanks, when quoted in paper
format).
'
'In Paper: DE89 3704 0044 0532 0130 00
'In Electronic Format: DE89370400440532013000
'
'Algorithm to Validate or Check IBAN manually
'In most countries, banks use Modulo 97-10 algorithm for checking the
correctness of the IBAN
'and the steps given below..
'
'Let us consider the iban number that we just generated. We are required to
check the
'correctness or validity of the given number we just computed.
'
'DE89 3704 0044 0532 0130 00
'IBAN format for Germany
'
'Country code: DE - 2 characters (2a).
'Control Digit (XX) 2 digits (2n).
'Bank code 8n digits (BLZ).
'Account Number (10n digits).
'Note: 'a' means alphabets only, 'n' numbers only, 'an' means both numbers
or alphabets.
'It is not necessary that, the iban may contain only numbers, but can also
have alpha-numeric characters.
'
'As you notice from the above parameters, we have all necessary data for
iban but 'Control digit' is missing.
'For that see the procedure below.
'
'Available IBAN Parameters:
'
'Country code: DE
'Control Digit: 89
'Bank code: 37040044
'Account number: 0532 0130 00
'Total length of IBAN for Germany: 22 an
'
'Goal: To check whether given IBAN is correct or valid.
'
'Step 1:
'
'Strip off all spaces to make IBAN a single 22 length format. The length of
IBAN varies
'specific to countries. In this case it is 22 for Germany. Make sure that
you padd zeros
'with bank code and account number if it does not fit the given length.
'
'DE89370400440532013000
'Step 2:
'
'Detach first 4 characters(ISO country code & control digit) from IBAN and
attach it at the end.
'
'370400440532013000DE89
'Step 3:
'
'Now eliminate all those alphabets by converting to numbers from the table
given below
'
'A 10 I 18 Q 26 Y 34
'B 11 J 19 R 27 Z 35
'C 12 K 20 S 28
'D 13 L 21 T 29
'E 14 M 22 U 30
'F 15 N 23 V 31
'G 16 O 24 W 32
'H 17 P 25 X 33
'
'That becomes 370400440532013000131489
'Step 4:
'
'Now, perform a modulo operation with 97
'
'370400440532013000131489 mod 97
'
'which gives 1
'
'The result from the calculated modulo 97 must always be 1, only then the
given IBAN number is correct.
'Hence, the given IBAN number is correct.
'
'WARNING! DO NOT attempt to generate or compute IBAN by yourself or using
any third party tools.
'Only your bank can provide you true and accurate IBAN. If you use incorrect
IBAN then either
'payment will be rejected or you will penalised extra for defaulted
payments. The algorithm
'published aimed only for your better understanding on inside thoughts of
IBAN and does not
'guarantee you true and accurate IBAN number.
'
'Most Frequently Asked Questions about IBAN account numbers
'
'What is the difference between an IBAN and my account number?
'
'Your account number and branch details are the primary way in which the
bank identifies your account.
'Your IBAN is used for receipt of cross border payments in your favour to
identify your account
'in a uniform way. It allows the sending bank to validate that the IBAN data
provided is in the correct format.
'
'Where can i get my IBAN account number? Who issues it?
'
'When you open an account with any bank in europe, the bank is required to
provide you to the
'IBAN account number along with your domestic account number. It is the
legal requirement for
'all banks in europe to issue you IBAN. You have to ASK your bank to give
you one.
'
'Caution: DO NOT attempt to generate or compute IBAN by yourself or using
any third party tools.
'Only your bank can provide you true and accurate IBAN. If you use incorrect
IBAN then either
'payment will be rejected or you will penalised extra for defaulted
payments. The algorithm published
'only for your understand and may not guarantee you true and accurate IBAN
number.
'
'You should use your IBAN & BIC for invoicing and for instructing a foreign
bank to make payments to
'your account. The IBAN & BIC should be used instead of account number and
branch detail in cross
'border payments. Always quote BIC/SWIFT code of the institution along with
IBAN.
'
'What are the costs for using IBAN in bank transfers?
'
'Ever since july 2003, most of cross border european union payment
transactions between european
'banks are processed and validated by means of IBAN account numbers. The
significant advantage
'of sending payments using IBAN, the payment transaction between these
countries are considered
'domestic and it would cost you less, but under the following conditions...
'
'The Payment is made in EUR currency.
'The total amount is less than EUR 12'500.
'IBAN account number is specified along with BIC/SWIFT code of institution.
'Please consult your bank for charges involving IBAN as it varies specific
to countries and currency involved.
'
'Can i also quote IBAN if my payment is sent outside the EU?
'
'If you are sending payments outside europe, for example to USA or Asia, it
is unlikely that the
'payer 's country banks will use IBAN system since IBAN is used widely only
within europe. Therefore
'it is not required to send them your IBAN. All you need to give the payer
is..
'
'account Number
'BIC/SWIFT code of Institution
'How about receiving payments using IBAN ?
'
'If you receive funds within EU & EEA, then you should give to payer:
'
'IBAN account number
'BIC/SWIFT code of Institution
'bank Name And Address
'If you receive funds from outside europe, then you have to give the payer
'
'account Number
'BIC/SWIFT code of Institution
'The only difference of using IBAN is your bank will levy you only domestic
charges, instead of foreign
'transfer. If you prefer not to quote IBAN and prefer only account number
and swift code, the payment
'will not be considered as foreign payment and appropriate charges will be
levied. Some banks will
'charge EUR 20 to EUR 35 for foreign transfers.
'
'Will Banks outside of the Europe area accept an IBAN?
'An IBAN can be used instead of your account number. Any bank will simply
pass on the 'account number'
'as they are given it. All banks in the International Payments community
should be aware of IBAN
'initiatives, even if their country is not using IBAN formats at present.
They should therefore
'accept your IBAN & BIC instead of your usual account number and sort code.
'
'Useful Links
'Wikipedia: IBAN Wiki definition on IBAN
'http://www.wikipedia.org/wiki/IBAN
'ECBS European committee for banking Standards IBAN reference and published
reference for all countries in europe.
'http://www.ecbs.org/
'IBAN checker and calculator A free IBAN calculator and validator (for
German, Austrian and Belgian banks)
'http://www.iban-rechner.de/
'A free IBAN decoder (for all countries)
'http://www.xe.com/idt/
'Official BIC/SWIFT code reference for Institutions Worldwide
'http://www.swift.com/biconline
' mdpa - Henry Habermacher
'Ich habe soeben ein bisschen in meinen Datenbanken gegraben und die
'relevanten Funktionen gefunden. Hier sind sie. Es sind deren zwei. Eine
'braucht 's zur Verifikation, die andere ist eben die Modulo Berechnung. Der
'ersten übergibst Du (falls nicht NULL!) die eingegebene IBAN Nummer. Diese
'gibt dann True oder False zurück, je nachdem ob die Prüfung erfolgreich war
'oder nicht.
'
'Also etwa so (beim IBANFeld_AfterUpdate)
'
'If Not IsNull(Me!IBANFeld) Then
' If verifyIBAN(Me!IBANFeld) = False Then
' MsgBox "Das ist keine korrekte IBAN, bitte kontrollieren"
' End If
' DoCmd.CancelEvent
' Me!IBANFeld.SetFocus
'End If
Public Function verifyIBAN(IBAN As String) As Boolean
On Error GoTo PROC_ERR
Dim strTemp As String
Dim strTemp2 As String
Dim lngL As Long
Dim I As Long
Dim C As String
'definitions according:
'http://www.europebanks.info/ibanguide.htm
'step 1: get rid of the spaces
strTemp = Replace(IBAN, " ", "")
'padd zeroes to match the length
' defined by the country code
Select Case Left(strTemp, 2)
Case "NO"
lngL = 15
Case "BE", "NO"
lngL = 16
Case "DK", "FI", "NL"
lngL = 18
Case "SI"
lngL = 19
Case "AT", "EE", "LT", "LU"
lngL = 20
Case "CH", "LV"
lngL = 21
Case "DE", "IE", "GB"
lngL = 22
Case "GI"
lngL = 23
Case "AD", "CZ", "ES", "SE", "SK"
lngL = 24
Case "PT"
lngL = 25
Case "IS"
lngL = 26
Case "FR", "IT", "GR"
lngL = 27
Case "CY", "HU", "PL"
lngL = 28
Case Else
verifyIBAN = False
GoTo PROC_EXIT
End Select
strTemp = strTemp + String(lngL - Len(strTemp), "0")
'step 2 countrycode and checkdigit
' to the end of the code
strTemp = Mid(strTemp, 5) & Left(strTemp, 4)
'step 3 convert characters to nunbers
strTemp2 = ""
For I = 1 To Len(strTemp)
C = Mid(strTemp, I, 1)
If C >= "A" And C <= "Z" Then
C = CStr(Asc(C) - 55)
End If
strTemp2 = strTemp2 & C
Next
strTemp = strTemp2
'step 4: check with Mod 97, if Result is 1
' then IBAN is correct.
If BigMod(strTemp, 97) = 1 Then
verifyIBAN = True
Else
verifyIBAN = False
End If
PROC_EXIT:
Exit Function
PROC_ERR:
verifyIBAN = False
GoTo PROC_EXIT
End Function
Public Function BigMod(BigNumber As String, _
Operand As Long) As Long
Dim R As Long 'rest
Dim O As Long 'operand
Dim L As Long 'länge des operanden
Dim T As String 'aktuell betrachteter Teil
Dim I As Long 'laufwariable
O = Operand
L = Len(CStr(O)) 'Länge des Operanden
T = Left(BigNumber, L)
R = CLng(T) Mod O 'Rest der 1. Operation
For I = L + 1 To Len(BigNumber)
T = CStr(R) & Mid(BigNumber, I, 1)
R = CLng(T) Mod O
Next
BigMod = R
End Function
--
mit freundlichen Grüßen aus Nürnberg
Klaus Oberdalhoff KO...@gmx.de
Ich beantworte keine NG-Fragen und -Nachfragen per Mail!
Newbie-Info: http://www.doerbandt.de/Access/Newbie.htm
KnowHow-mdb und andere Beispiele: http://www.freeaccess.de
Sofern Access 200x bitte beachten:
http://www.freeaccess.de/MS-Access-Artikel.asp?ID=99
Ich unterstütze PASS Deutschland e.V. (http://www.sqlpass.de)
Nächstes Treffen in Nürnberg am 12.02.2008
Henry Habermacher
Holger Wirtz wrote:
> Kennt jemand darüberhinaus die Prüflogik einer IBAN-Kontonummer?
Meinst Du sowas?
Public Function verifyIBAN(IBAN As String) As Boolean
On Error GoTo PROC_ERR
Dim strTemp As String
Dim strTemp2 As String
Dim lngL As Long
Dim I As Long
Dim C As String
'definitions according:
' http://www.europebanks.info/ibanguide.htm,
'Problem: VBA can't calculate that large numbers
' precisely to return a correct mod 97 result
If BigMod(strTemp, 97) = 1 Then
verifyIBAN = True
Else
verifyIBAN = False
End If
PROC_EXIT:
Exit Function
PROC_ERR:
verifyIBAN = False
GoTo PROC_EXIT
End Function
Private Function BigMod(BigNumber As String, Operand As Long) As Long
Dim R As Long 'Rest
Dim o As Long 'Operand
Dim L As Long 'Länge des Operanden
Dim t As String 'aktuell betrachteter Teil
Dim I As Long 'Laufvariable
o = Operand
L = Len(CStr(o)) 'Länge des Operanden
t = Left(BigNumber, L)
R = CLng(t) Mod o 'Rest der 1. Operation
For I = L + 1 To Len(BigNumber)
t = CStr(R) & Mid(BigNumber, I, 1)
R = CLng(t) Mod o
Next
BigMod = R
End Function
Pack obigen Code in ein neues Standard Modul rein und rufe die Funktion
verifyIBAN() mit der zu prüfenden IBAN Nr als Parameter auf. True: Die
Nummer stimmt, False: Die Nummer stimmt nicht.
Gruss
Henry
--
Microsoft MVP Office Access
Keine E-Mails auf Postings in NGs. Danke.
Access FAQ www.donkarl.com
Holger Wirtz
Ihr habt mir weitergeholfen. Tausend Dank...
Viele Grüße
Holger