Excel · Power Query · API IBAN-Test

Validar IBAN no Excel com Power Query

Envie uma lista de IBAN do Excel para a API IBAN-Test e obtenha o resultado da verificação, o nome do banco e o BIC como uma tabela - sem macros VBA.

Este guia usa Excel para Microsoft 365 no Windows. Você configura uma consulta Power Query uma vez e depois atualiza seus resultados usando o menu Dados. É necessária uma conta IBAN-Test com tokens de API e quota suficiente.

Estado dos testes: A estrutura XLSX e a sintaxe M são verificadas e a chamada da API é alinhada com a descrição da interface publicada. Um teste completo com o Microsoft Excel e consultas de API autenticadas ainda está pendente. Primeiro verifique o processo com alguns dados de teste.

O que você precisa para se preparar

• Excel para Microsoft 365 no Windows com Power Query. Este processo não é garantido aqui para Excel no navegador e Excel para Mac.
• Um token de API: Faça login em IBAN-Test. Informações sobre acesso podem ser encontradas no Documentação da API; a quota apropriada em Preços.
• Liberação para processamento: Os IBANs são transferidos para IBAN-Test. Use apenas dados que você tem permissão para processar dessa forma.

1. Prepare a pasta de trabalho e a lista de IBAN

Descompacte o ZIP e abra iban-test-excel-en.xlsx. Na folha de cálculo Input, substitua os dados de amostra pela sua lista. A coluna Reference é para sua própria atribuição; apenas o IBAN é enviado para a API.

Deixe os nomes das colunas Reference e IBAN e o nome da tabela IBAN_Input inalterados. Novas linhas devem estar dentro desta tabela do Excel. Guarde os IBANs como texto. A consulta remove espaços e converte letras em maiúsculas.

Na folha de cálculo Settings, substitua YOUR_API_TOKEN pelo seu token. O nome da tabela é IBAN_Settings e a coluna é ApiToken. O token é salvo no ficheiro. Não compartilhe esta pasta de trabalho com seu token.

2. Adicionar o código Power Query

1. No Excel, abra Dados → Obter dados → De outras fontes → Consulta vazia.
2. No editor do Power Query, abra Base → Editor Avançado.
3. Abra iban-validation.pq do ZIP em um editor de texto. Copie todo o conteúdo e use-o para substituir o código existente no editor avançado.
4. Confirme com Concluído e nomeie a consulta IBAN_Results.

Até mesmo a visualização pode acionar solicitações de API. O modelo está, portanto, inicialmente limitado a 10 IBANs diferentes e não vazios por avaliação. Esta não é uma promessa sobre o número de solicitações HTTP por clique: Power Query pode avaliar consultas várias vezes.

3. Defina o acesso à API e a proteção de dados

Quando o Excel solicitar credenciais para https://www.iban-test.eu, selecione Anónimo. O token da API ainda é enviado: o próprio código o insere no cabeçalho HTTP como Authorization: Bearer …. O tipo de login da API Web não é usado para este modelo.

O código envia uma chamada POST para /api/v2/iban/validate. De acordo com Documentação da Microsoft em Web.Contents, o POST neste fluxo só é possível com o tipo de login da fonte de dados Anónimo.

Defina os níveis de proteção de dados da pasta de trabalho e da fonte da Web para atender aos seus requisitos operacionais. Uma mensagem Formula.Firewall diz respeito à combinação de fontes de dados. Verifique o Níveis de privacidade do Power Query; Não desligue a proteção de dados de forma geral.

4. Carregue os resultados e atualize mais tarde

Selecione Fechar e carregar... → Tabela → Nova folha de cálculo. Não substitua a tabela de entrada ou as configurações. Posteriormente, modifique sua lista na folha Input e selecione Dados → Atualizar tudo.

IBANs limpos idênticos são combinados em uma avaliação e os resultados são então atribuídos a todas as linhas associadas. Sua ordem de entrada é mantida.

Lidar com erros corretamente

• Token ou login: Verifique o token e as permissões salvas em Dados → Recuperar dados → Configurações de fonte de dados. O tipo de login Anónimo deve ser aplicado ao endereço da API.
• Cota, acesso desabilitado ou erro no servidor: A consulta é interrompida. Tais problemas não devem produzir um resultado INVALID.
• Resultados antigos após um erro: O Excel pode manter o último status da tabela bem-sucedida. Verifique o status da atualização e CheckedAtUTC antes de reutilizar os resultados.
• Mais de dez IBANs diferentes: Primeiro, verifique a quota de API disponível. Só então você incrementa MaxUniqueIbans no código. Considere solicitações adicionais de visualização e reprodução.

O que um IBAN válido não prova

A validação verifica o IBAN e fornece os dados bancários disponíveis. Não confirma a existência de uma conta específica, seu titular, saldo ou a execução bem-sucedida de um pagamento. Mesmo um dígito de verificação válido não substitui a verificação do titular da conta.

Perguntas frequentes

Também posso verificar o IBAN através de uma fórmula Excel?

As verificações de formato e dígito verificador geralmente podem ser implementadas como uma fórmula. Uma simples verificação do comprimento não é suficiente. Essas instruções utilizam a API para ler as informações bancárias e os códigos de erro diferenciados ali disponíveis.

O modelo do Excel é gratuito?

O download é gratuito. O uso da API depende do seu plano e da quota disponível. Cada atualização e até mesmo uma visualização podem consumir quota.

Como posso compartilhar os resultados com segurança?

Copie apenas os valores de resultado necessários em um novo ficheiro. Remova tokens, consultas e IBANs desnecessários. Simplesmente ocultar a folha Settings não protege o token.

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