German Public Holidays as CSV or iCal for Excel, Outlook

Why a list copied once eventually stops being enough

For a single team at one site, typing the public holidays into a spreadsheet once a year is often enough. The trouble starts once a second state (Bundesland) is involved, once the list feeds software instead of an overview, or once a year like 2027 arrives, when notice periods, contribution thresholds and public holidays all change at once and upkeep quietly slips.

Three things make a hand-kept list fragile in particular: regional holidays that apply in only part of a state, one-off holidays that appear on the calendar exactly once, and the turn of the year itself, when a missed update only shows up once a holiday is simply missing from the calendar.

Opening the CSV directly in Excel

Every call that returns a list accepts the parameter format=csv and writes it as a CSV file instead of JSON. For a German-language Excel, add trennzeichen=semikolon (German for semicolon separator): the file then separates columns with semicolons and writes decimal numbers with a comma, exactly what Excel expects under German regional settings. Double-clicking the downloaded file opens it straight away as a table, no import wizard required.

Technically the file follows RFC 4180: a header row with column names, CRLF line endings, and fields with commas or quotation marks wrapped in quotes. It arrives as UTF-8 with a BOM so that umlauts in names such as Fronleichnam (Corpus Christi) display correctly, and the filename sits in the Content-Disposition header. The full format is documented under Responses and CSV.

What one row of the public holidays CSV contains (example: New Year's Day, the first entry of every year)

Working days as a list, not just individual holidays

For target hours, day rates or capacity planning, the holiday list alone is often not enough; what is needed is the actual number of working days in a period. That is exactly what the working days calculator returns through the same route: January 2027 has 20 working days out of 31 calendar days in Hesse, also available with format=csv.

The difference from a plain holiday list: the working days calculator needs a period (von and bis, German for from and to) and returns a count plus the holidays deducted within it, not a full year's calendar. For a complete list of every holiday in a year, the public holidays CSV is the right tool; for a single figure per month or project, the working days calculator is.

Several states in one call

A nationwide overview does not need sixteen separate calls. Without the land parameter, the same call returns every public holiday once, together with the list of states where it applies: 20 distinct holidays come together this way for 2027, from New Year's Day, which applies everywhere, to the three regional holidays that count only in certain states or municipalities.

For a CSV per site, a separate call per state is still the clearer route, because then every row belongs to exactly one state. The combined list works better as an overview, or as a one-off import into your own table that the software then filters by site itself.

Staying current automatically, without waiting for New Year

Public holidays are not only in the data for the current year; they are computed in advance from 2015 through 2035. A new year therefore does not show up as a sudden gap: even a one-off holiday is already on the list. Berlin, for instance, already has a one-off day of remembrance on the books for June 17, 2028, even though that year is still a long way off.

If something does change, for example because a state passes a new public holiday law, the weekly review adds it and a webhook reports it as a wert.neu event, complete with a ready-made address to fetch the new value. Nobody has to check every January by hand whether something changed; instead, a message arrives when it does.

For a simple, predictable rhythm without a webhook, an annual export also works: a cron job that pulls the current and the following year's list in early January covers most cases. Either way replaces manually looking up a web page with a year number in its title.

Getting it into Outlook or Google Calendar

One honest point first: Quellenkontor delivers public holidays as CSV or JSON, not as an iCal file to subscribe to. Anyone who genuinely just wants a calendar feed that adds the holidays by itself may get there faster with a calendar-focused service such as the OpenHolidays API, which offers public holidays for several countries as either JSON or iCal.

That is no drawback for Excel, but it is for a calendar. Google Calendar accepts events via CSV only on a computer, with English column headers such as Subject, Start Date and All Day Event and dates in MM/DD/YYYY format, as described in Google's help on importing events. Classic Outlook expects its own CSV columns such as Subject and Start Date during import, while newer versions and Outlook on the web tend to expect an ICS file instead, as covered in Microsoft's support article on importing and exporting.

Quellenkontor's public holidays CSV uses its own, German column names, because it is built for analysis, not for a direct calendar import. For a one-off transfer, renaming the name and datum columns in Excel and adding an all-day column set to true is usually enough to match the CSV shape most calendar programs expect.

Keeping a public holidays list usable in practice

• For Excel and analysis: format=csv with trennzeichen=semikolon, once per state and year, opened straight from a double-click.
• For a calendar import: rename the columns to match the target program's format first, instead of importing the raw file as is.
• For several states: one call per state with its own code, for example land=BY for Bavaria.
• For software instead of spreadsheets: the same call as JSON, an SDK method, or an MCP tool, skipping the CSV step entirely.
• For staying current automatically: set up a webhook on the feiertage dataset, or run an annual cron job like the example below.

Common mistakes with CSV or calendar imports

• Importing the raw file into Google Calendar or Outlook unchanged and then being surprised by an error about missing columns.
• Loading a CSV without trennzeichen=semikolon into a German-language Excel, so every value lands in a single column.
• Downloading the list once and reusing it indefinitely, without planning a repeat for the next turn of the year.
• Missing regional holidays: they appear in the list with regional: true, but they do not automatically apply to every site in the state.
• Pointing Google Sheets' IMPORTDATA at the API address directly: the function cannot send a header, so it cannot carry an API key; the file has to be downloaded and imported instead.
• Reusing last year's list instead of pulling a fresh one: movable holidays such as Ascension Day (Christi Himmelfahrt) follow Easter and fall on a different date every year, in 2027 on May 6, 2027.

Fetching CSV for a German-language Excel

$ curl "https://api.quellenkontor.dev/v1/hr/feiertage?jahr=2027&land=HE&format=csv&trennzeichen=semikolon" \
    -H "Authorization: Bearer $QK_KEY" -o feiertage-2027-he.csv

# feiertage-2027-he.csv, first line
datum;id;name;wochentag;regional;regional_hinweis;sonntag
2027-01-01;neujahr;Neujahr;Freitag;false;;false

Annual export without a webhook

# every January 2 at 6 a.m., refresh the current and the coming year's holidays
0 6 2 1 * QK_KEY=qk_live_… qk feiertage --jahr 2027 --land HE --format csv --trennzeichen semikolon > /daten/feiertage-2027.csv
5 6 2 1 * QK_KEY=qk_live_… qk feiertage --jahr 2028 --land HE --format csv --trennzeichen semikolon > /daten/feiertage-2028.csv

Where the API route pays off further

A CSV export is the simplest starting point, but only one of four forms of the same dataset. The REST API, the JavaScript and Python SDKs, the CLI and the MCP server all return the same fields, just in a different shape: the CLI for scripts and cron jobs, the SDK for a fixed integration into an application, and the MCP server so that an AI agent looks holidays up instead of guessing them.

For HR software that needs public holidays directly inside absence or shift plans, the solution page public holidays, deadlines and vacation for HR software covers the full path from planning to querying by site.