Tutorial · Excel

Company lookup and VAT check in Excel

Q: Can Excel check a column of VAT numbers against VIES?

Yes, with a Power Query function that calls GET /vat/{vatNumber} for each row. It returns whether VIES lists the number as valid, plus the registered name and address when the member state gives them. It covers the 27 EU member states and Northern Ireland (XI); GB VAT numbers are out of scope. Each check costs 1 credit.

Q: Where is the API key stored?

In a Power Query parameter named ApiKey, sent in the Authorization: Bearer header, so it is in no cell and not in the URL. The parameter is saved inside the workbook file, so keep the file private and replace the key if it leaks.

Two Power Query functions: one looks a company up from its country and register number, the other checks an EU VAT number against VIES. Apply them to a column and load the result into the sheet.

1. Keep the key in a parameter

Open Data, Get Data, Launch Power Query Editor, then Home, Manage Parameters, New Parameter. Name it ApiKey, type Text, and paste your rk_live_... key as the current value. The functions below send it in the Authorization: Bearer header (the API also accepts x-api-key), so it is in no cell and not in the URL.

Be clear about where it ends up: a Power Query parameter is saved inside the workbook file. Keep the file private, do not attach it to a mail, and replace the key in your dashboard if it leaks.

2. The company function

Add a blank query (New Source, Other Sources, Blank Query), open the Advanced Editor, paste this and name the query fnCompany. It calls GET /companies/search with country and company_number. ManualStatusHandling keeps one failed row from stopping the whole refresh: the http column shows 200, 400 for a malformed number, 429 when you are over a limit.

(country as text, number as text) as record =>
let
    Resp = Web.Contents("https://api.prometiam.com/functions/v1/risk-api/companies/search", [
        Query = [country = country, company_number = Text.Trim(number)],
        Headers = [Authorization = "Bearer " & ApiKey],
        ManualStatusHandling = {400, 401, 403, 404, 429, 503}
    ]),
    Code = Value.Metadata(Resp)[Response.Status],
    Body = if Code = 200 then Json.Document(Resp) else null,
    Row = if Body <> null and List.Count(Body[data]) > 0 then Body[data]{0} else null
in
    [
        http = Code,
        company_name = if Row = null then null else Row[company_name],
        status = if Row = null then null else Row[status],
        local_status = if Row = null then null else Row[local_status],
        legal_form = if Row = null then null else Row[legal_form],
        city = if Row = null then null else Record.FieldOrDefault(Row, "city"),
        vat_number = if Row = null then null else Row[vat_number]
    ]

The same request from a terminal:

curl 'https://api.prometiam.com/functions/v1/risk-api/companies/search?country=GB&company_number=00445790' \
  -H 'Authorization: Bearer rk_live_...'

3. The VAT function

Another blank query named fnVat. It calls GET /vat/{vatNumber}, which asks VIES, the European Commission's VAT number service, and returns is_valid, the registered name and the address when the member state supplies them. The path takes the full number with its two-letter country prefix; spaces and punctuation are ignored.

(vat as text) as record =>
let
    Clean = Text.Upper(Text.Select(vat, {"0".."9", "A".."Z", "a".."z"})),
    Resp = Web.Contents("https://api.prometiam.com/functions/v1/risk-api/vat/" & Clean, [
        Headers = [Authorization = "Bearer " & ApiKey],
        ManualStatusHandling = {400, 401, 403, 404, 429, 503}
    ]),
    Code = Value.Metadata(Resp)[Response.Status],
    Data = if Code = 200 then Json.Document(Resp)[data] else null
in
    [
        http = Code,
        is_valid = if Data = null then null else Data[is_valid],
        name = if Data = null then null else Data[name],
        address = if Data = null then null else Data[address]
    ]

curl 'https://api.prometiam.com/functions/v1/risk-api/vat/BE0417497106' \
  -H 'Authorization: Bearer rk_live_...'

4. Apply them to a column

Put the register numbers in an Excel table with a country column and a number column, both formatted as Text (a number format drops leading zeros, and 00445790 is a different company from 445790).
Load the table into Power Query (Data, From Table/Range), then Add Column, Invoke Custom Function, choose fnCompany and map its two arguments to the two columns.
Expand the record column with the arrows and Close and Load. Do the same with fnVat on a column of VAT numbers.

If Excel asks for the credentials of api.prometiam.com, choose Anonymous: the key is already in the header. If it stops with a Formula.Firewall or privacy-level message, give the workbook and api.prometiam.com the same privacy level, Organizational for both: Microsoft explains the rule in the Power Query data privacy firewall (read 6 Nov 2026). The functions are written for Power Query in Excel for Microsoft 365 on Windows: run them on one row before a whole column.

What it costs

A successful company lookup costs 1 credit and so does a VAT check; an error costs nothing. Starter is EUR 9.99 a month with 1,000 credits, Professional EUR 29.99 with 10,000, Scale EUR 99.99 with 50,000, and Enterprise is custom. Starter is the only plan with a trial: 14 days and 500 credits, card required, nothing charged during the trial.

Every row is one call and a refresh repeats every row, so switch off refresh on open and refresh when the list changes. Starter allows 60 requests a minute: a long list can show 429 on its last rows, and refreshing again retries them.

What the calls return, and what they do not

Call	Returns	Limits
`fnCompany`	The company record of the country's register: name, status, legal form, address, VAT number with its source	No directors or owners. Fields differ by country and are empty where the register does not publish them. Each country has its own scope limits, in its company data guide. The status is the register's reading, not a guarantee of solvency
`fnVat`	Whether VIES lists the number as valid, with the registered name and address	EU member states and Northern Ireland (XI) only, GB is out of scope. A live service: when a member state's registry is down it answers 503, not "invalid", and the row is not billed. Some member states withhold the name

A vat_number in the company record built by rule is only the right format for the country; fnVat is the check against VIES. See the UK company data guide for a worked country, and the Brazilian-Portuguese version for Brazil: consultar CNPJ no Excel.

FAQ

Can Excel check a column of VAT numbers against VIES?
Yes, with a Power Query function that calls GET /vat/{vatNumber} for each row. It returns whether VIES lists the number as valid, plus the registered name and address when the member state gives them. It covers the 27 EU member states and Northern Ireland (XI); GB VAT numbers are out of scope. Each check costs 1 credit.

Where is the API key stored?
In a Power Query parameter named ApiKey, sent in the Authorization: Bearer header, so it is in no cell and not in the URL. The parameter is saved inside the workbook file, so keep the file private and replace the key if it leaks.

What happens when VIES is down?
The row shows HTTP 503 and is not billed. VIES is a live service of the member states and is sometimes unavailable for a country; the call is not reported as invalid, so refresh the rows that show 503 later.

How much does a refresh cost?
Every row is one call of 1 credit when it succeeds, and a refresh repeats every row. Errors cost nothing. Starter is EUR 9.99 a month with 1,000 credits; the 14-day trial includes 500 credits, needs a card and charges nothing during the trial.

The 14-day trial needs a card, nothing is charged during it, and it then continues as Starter (EUR 9.99/month) unless you cancel; it includes 500 credits.