Excel · Power Query · IBAN-Test API

IBAN in Excel prüfen mit Power Query

Eine IBAN-Liste aus Excel an die IBAN-Test API senden und Prüfergebnis, Bankname und BIC als Tabelle zurückbekommen – ohne VBA-Makros.

Diese Anleitung verwendet Excel für Microsoft 365 unter Windows. Sie richten einmalig eine Power-Query-Abfrage ein und aktualisieren anschließend Ihre Ergebnisse über das Menü Daten. Ein IBAN-Test-Konto mit API-Token und ausreichendem Kontingent ist erforderlich.

Stand des Beispiels: XLSX-Struktur und M-Syntax sind geprüft und der API-Aufruf ist mit der veröffentlichten Schnittstellenbeschreibung abgeglichen. Ein vollständiger Test mit Microsoft Excel und authentifizierten API-Abfragen steht noch aus. Prüfen Sie den Ablauf zunächst mit wenigen Testdaten.

Was Sie vorbereiten müssen

• Excel für Microsoft 365 unter Windows mit Power Query. Für Excel im Browser und Excel für Mac wird dieser Ablauf hier nicht zugesichert.
• Einen API-Token: Melden Sie sich bei IBAN-Test an. Informationen zum Zugang finden Sie in der API-Dokumentation; das passende Kontingent unter Preise.
• Freigabe für die Verarbeitung: Die IBANs werden an IBAN-Test übertragen. Verwenden Sie nur Daten, die Sie auf diesem Weg verarbeiten dürfen.

1. Arbeitsmappe und IBAN-Liste vorbereiten

Entpacken Sie das ZIP und öffnen Sie iban-test-excel-de.xlsx. Ersetzen Sie im Blatt Input die Beispieldaten durch Ihre Liste. Die Spalte Reference dient Ihrer eigenen Zuordnung; nur die IBAN wird an die API geschickt.

Lassen Sie die Spaltennamen Reference und IBAN sowie den Tabellennamen IBAN_Input unverändert. Neue Zeilen müssen innerhalb dieser Excel-Tabelle stehen. Speichern Sie IBANs als Text. Die Abfrage entfernt Leerzeichen und wandelt Buchstaben in Großbuchstaben um.

Im Blatt Settings ersetzen Sie YOUR_API_TOKEN durch Ihren Token. Der Tabellenname lautet IBAN_Settings, die Spalte ApiToken. Der Token wird in der Datei gespeichert. Geben Sie diese Arbeitsmappe nicht mit Ihrem Token weiter.

2. Power-Query-Code einfügen

1. Öffnen Sie in Excel Daten → Daten abrufen → Aus anderen Quellen → Leere Abfrage.
2. Öffnen Sie im Power-Query-Editor Start → Erweiterter Editor.
3. Öffnen Sie iban-validation.pq aus dem ZIP in einem Texteditor. Kopieren Sie den gesamten Inhalt und ersetzen Sie damit den vorhandenen Code im erweiterten Editor.
4. Bestätigen Sie mit Fertig und nennen Sie die Abfrage IBAN_Results.

Bereits die Vorschau kann API-Anfragen auslösen. Die Vorlage ist deshalb zunächst auf 10 unterschiedliche, nicht leere IBANs pro Auswertung begrenzt. Das ist keine Zusage über die Zahl der HTTP-Anfragen pro Klick: Power Query kann Abfragen mehrfach auswerten.

3. API-Zugriff und Datenschutz einstellen

Wenn Excel nach Zugangsdaten für https://www.iban-test.eu fragt, wählen Sie Anonym. Der API-Token wird trotzdem gesendet: Der Code trägt ihn selbst als Authorization: Bearer … in den HTTP-Header ein. Die Anmeldeart „Web-API“ wird für diese Vorlage nicht verwendet.

Der Code sendet einen POST-Aufruf an /api/v2/iban/validate. Laut Microsoft-Dokumentation zu Web.Contents ist POST in diesem Ablauf nur mit der Datenquellen-Anmeldeart Anonym möglich.

Setzen Sie die Datenschutzstufen der Arbeitsmappe und der Webquelle passend zu Ihren betrieblichen Vorgaben. Eine Meldung Formula.Firewall betrifft die Kombination von Datenquellen. Prüfen Sie die Power-Query-Datenschutzstufen; schalten Sie den Datenschutz nicht pauschal ab.

4. Ergebnisse laden und später aktualisieren

Wählen Sie Schließen & laden in … → Tabelle → Neues Arbeitsblatt. Überschreiben Sie weder die Eingabetabelle noch die Einstellungen. Später ändern Sie Ihre Liste im Blatt Input und wählen Daten → Alle aktualisieren.

Identische bereinigte IBANs werden innerhalb einer Auswertung zusammengefasst und die Ergebnisse anschließend allen zugehörigen Zeilen zugeordnet. Ihre Eingabereihenfolge bleibt erhalten.

Fehler richtig behandeln

• Token oder Anmeldung: Prüfen Sie den Token sowie die gespeicherten Berechtigungen unter Daten → Daten abrufen → Datenquelleneinstellungen. Für die API-Adresse muss die Anmeldeart Anonym gelten.
• Kontingent, deaktivierter Zugang oder Serverfehler: Die Abfrage bricht ab. Solche Probleme dürfen kein Ergebnis INVALID erzeugen.
• Alte Ergebnisse nach einem Fehler: Excel kann den letzten erfolgreichen Tabellenstand behalten. Prüfen Sie den Aktualisierungsstatus und CheckedAtUTC, bevor Sie Ergebnisse weiterverwenden.
• Mehr als zehn unterschiedliche IBANs: Prüfen Sie zuerst das verfügbare API-Kontingent. Erst danach erhöhen Sie MaxUniqueIbans im Code. Berücksichtigen Sie zusätzliche Vorschau- und Wiederholungsanfragen.

Was eine gültige IBAN nicht beweist

Die Prüfung bewertet die IBAN und liefert verfügbare Bankdaten. Sie bestätigt weder die Existenz eines konkreten Kontos noch dessen Inhaber, Guthaben oder die erfolgreiche Ausführung einer Zahlung. Auch eine gültige Prüfziffer ersetzt keine Kontoinhaberprüfung.

Häufige Fragen

Kann ich die IBAN auch mit einer Excel-Formel prüfen?

Format- und Prüfziffernprüfungen lassen sich grundsätzlich als Formel umsetzen. Eine einfache Längenprüfung reicht dafür nicht. Diese Anleitung verwendet die API, um zusätzlich die dort verfügbaren Bankinformationen und differenzierte Fehlercodes auszulesen.

Ist die Excel-Vorlage kostenlos?

Der Download ist kostenlos. Die API-Nutzung richtet sich nach Ihrem Tarif und verfügbaren Kontingent. Jede Aktualisierung und auch eine Vorschau kann Kontingent verbrauchen.

Wie teile ich die Ergebnisse sicher?

Kopieren Sie ausschließlich die benötigten Ergebniswerte in eine neue Datei. Entfernen Sie dabei Token, Abfragen und nicht benötigte IBANs. Das bloße Ausblenden des Settings-Blatts schützt den Token nicht.

let
    // Read only the two named tables supplied with the starter workbook.
    Settings = Excel.CurrentWorkbook(){[Name="IBAN_Settings"]}[Content],
    RawToken = Text.Trim(Text.From(Settings{0}[ApiToken])),
    ApiToken = if RawToken = "" or RawToken = "YOUR_API_TOKEN" then
        error Error.Record("Configuration", "Enter your API token in IBAN_Settings before refreshing.", null)
        else RawToken,
    Input = Excel.CurrentWorkbook(){[Name="IBAN_Input"]}[Content],
    AsText = Table.TransformColumnTypes(Input, {{"Reference", type text}, {"IBAN", type text}}),
    Normalized = Table.AddColumn(AsText, "IBAN_Normalized", each
        if [IBAN] = null then "" else Text.Upper(Text.Remove([IBAN], {" ", "#(tab)", "#(cr)", "#(lf)", Character.FromNumber(160)})), type text),
    UniqueIbans = List.Distinct(List.Select(Normalized[IBAN_Normalized], each _ <> "")),
    // Explicit cap for the starter example. Increase only after checking your quota.
    MaxUniqueIbans = 10,
    ToCheck = if List.Count(UniqueIbans) > MaxUniqueIbans then
        error Error.Record("Quota protection", "This example checks at most 10 distinct IBANs per refresh. Check your API quota before increasing MaxUniqueIbans.", null)
        else UniqueIbans,
    CheckIban = (iban as text) as record =>
        let
            Response = Web.Contents("https://www.iban-test.eu", [
                RelativePath = "api/v2/iban/validate",
                Headers = [Authorization = "Bearer " & ApiToken, #"Content-Type" = "application/json", Accept = "application/json"],
                Content = Json.FromValue([iban = iban]),
                Timeout = #duration(0, 0, 0, 30),
                ManualStatusHandling = {400, 404, 408, 422, 429, 500, 502, 503, 504, 509}
            ]),
            HttpStatus = Record.FieldOrDefault(Value.Metadata(Response), "Response.Status", 200),
            Body = if HttpStatus <> 200 then
                error Error.Record("API request failed", "HTTP " & Text.From(HttpStatus) & ": check authentication, quota or service availability. Refresh was stopped.", null)
                else Json.Document(Response),
            CheckedBody = if not Value.Is(Body, type record) then
                error Error.Record("Unexpected API response", "The response was not a JSON object.", null)
                else Body,
            Code = try Number.From(Record.FieldOrDefault(CheckedBody, "code", null)) otherwise null,
            IsValid = Code = 2100 and Record.FieldOrDefault(CheckedBody, "error", true) = false,
            IsInvalid = List.Contains({3100, 3101, 3102}, Code),
            Status = if IsValid then "VALID" else if IsInvalid then "INVALID" else
                error Error.Record("Validation unavailable", "API code " & (if Code = null then "missing" else Text.From(Code)) & ". Check your token, quota and API documentation. This is not an invalid-IBAN result.", null),
            RawDetails = Record.FieldOrDefault(CheckedBody, "details", []),
            Details = if Value.Is(RawDetails, type record) then RawDetails else [],
            // Force status evaluation before returning metadata.
            Result = if Status = "VALID" or Status = "INVALID" then [
                Status = Status,
                Code = Code,
                Message = Record.FieldOrDefault(CheckedBody, "message", ""),
                Bank = Record.FieldOrDefault(Details, "bankName", null),
                BIC = Record.FieldOrDefault(Details, "bic", null)
            ] else error "Unexpected validation status"
        in
            Result,
    // Evaluate distinct IBANs in sequence. An operational error aborts the refresh.
    CheckedRecords = List.Accumulate(ToCheck, {}, (state, iban) =>
        let
            PreviousReady = List.Count(state) = 0 or List.Last(state)[Status] <> "",
            Current = if PreviousReady then CheckIban(iban) else error "Previous check failed",
            Ready = Current[Status]
        in
            if Ready = "VALID" or Ready = "INVALID" then
                state & {Record.Combine({[IBAN_Normalized = iban], Current})}
            else error "Check failed"),
    Checked = Table.Buffer(Table.FromRecords(CheckedRecords,
        type table [IBAN_Normalized = text, Status = text, Code = nullable number, Message = nullable text, Bank = nullable text, BIC = nullable text])),
    Joined = Table.NestedJoin(Normalized, {"IBAN_Normalized"}, Checked, {"IBAN_Normalized"}, "Check", JoinKind.LeftOuter),
    Expanded = Table.ExpandTableColumn(Joined, "Check", {"Status", "Code", "Message", "Bank", "BIC"}),
    MissingMarked = Table.ReplaceValue(Expanded, null, "MISSING", Replacer.ReplaceValue, {"Status"}),
    CheckedAt = Table.AddColumn(MissingMarked, "CheckedAtUTC", each
        if [Status] = "MISSING" then null else DateTimeZone.RemoveZone(DateTimeZone.FixedUtcNow()), type nullable datetime)
in
    CheckedAt