The accounting format of DATEV is still the standard for tax consultants in Germany, but is also frequently used by auditors, lawyers, and alike. If, for example, you want to assist your tax advisor by supplying not only receipts but also the appropriate posting batches, you have to create DATEV-compliant files. We explain here how we did it. We will leave some headings and code variables in German, since the resulting DATEV posting batches will also be in German.
Ultimately, DATEV files are nothing more than tables or CSV files in a certain format and with a special header, which can be generated with any software that can output such files. We use R to be able to call the function for DATEV posting batches later in other scripts or applications.
Structure of a DATEV posting batch
The first line of a DATEV posting batch consists, as mentioned, of the header, which must follow a specific “record structure” that DATEV has explained in more detail here. The individual components of the “record” are separated from each other here quite normally like column names of a CSV with comma or semicolon.
The components of the header are as follows. In the column “Example” are (partly) the values which we have used in the later following function. Of course, the whole thing can easily be adapted for your own purposes. In this example, we are dealing with an accounting batch from November 2021 in euros for a company whose fiscal year began in July 2020.
| # | Heading | Example | Description |
| 1 | Kennzeichen | EXTF | EXTF = Export from 3rd-Party App DTVF = Export from DATEV App |
| 2 | Versionsnummer | 700 | Version of the Header. |
| 3 | Formatkategorie | 21 | 16 = Debitors-/Creditors 20 = Account descriptions 21 = Posting Batch 46 = Payment modalities 48 = Diverse Addresses 65 = Recurring postings |
| 4 | Formatname | Buchungsstapel | Formatname |
| 5 | Formatversion | 12 | Debitors/Creditors = 5 Account descriptions = 3 Posting Batches = 12 Payment modalities = 2 Recurrent Postings = 4 Diverse Addresses = 2 |
| 6 | Erzeugt am | 20211116154846000 | Time stamp: YYYYMMDDHHMMSSFFF |
| 7 | Reserviert | (empty) | Empty Cell |
| 8 | Reserviert | (empty) | Empty Cell |
| 9 | Reserviert | (empty) | Empty Cell |
| 10 | Reserviert | (empty) | Empty Cell |
| 11 | Beraternummer | 1001 | Range 1001-9999999 |
| 12 | Mandantennummer | 99999 | Range 1-99999 |
| 13 | WJ-Beginn | 20200701 | Beginn of financial year Format: YYYYMMDD |
| 14 | Sachkontenlänge | 4 | Length of internal account numbers. |
| 15 | Datum von | 20200701 | Start date of posting batch Format: YYYYMMDD |
| 16 | Datum bis | 20201231 | End date of posting batch Format: YYYYMMDD |
| 17 | Bezeichnung | Buchungen | Name of the posting batch |
| 18 | Diktatkürzel | (empty) | Initials of the author in capital letters |
| 19 | Buchungstyp | 1 | 1 = Financial accounting (default) 2 = Annual financial statement |
| 20 | Rechnungs-legungszweck | 0 | 0 = independent (default) 30 = Tax law 40 = Cost accounting 50 = Commercial law 64 = IFRS |
| 21 | Festschreibung | 0 | 0 = No locking up 1 = Locking up |
| 22 | WKZ | EUR | ISO-Code of the currency |
| 23 | Reserviert | (empty) | Empty cell |
| 24 | Derivatskennzeichen | (empty) | Empty cell |
| 25 | Reserviert | (empty) | Empty cell |
| 26 | Reserviert | (empty) | Empty cell |
| 27 | Sachkontenrahmen | (empty) | (honestly, we don’t know what this is for…) |
| 28 | ID der Branchenlösung | (empty) | If a specific DATEV solution is used |
| 29 | Reserviert | (empty) | Empty cell |
| 30 | Reserviert | (empty) | Empty cell |
| 31 | Anwendungsinformation | (empty) | (Again, we don’t know what this is for, but it does not seem to be necessary) |
The second line of the header consists of the actual column names. These column names will determine the later entries (Euro amount, account numbers, etc.). The further lines must now contain the actual entries, with the individual “cells” separated by semicolons. We leave the German cell names as is, since the resulting posting batch will be in German, too.
- Umsatz (ohne Soll/Haben-Kz)
- Soll/Haben-Kennzeichen
- WKZ Umsatz
- Kurs
- Basis-Umsatz
- WKZ Basis-Umsatz
- Konto
- Gegenkonto (ohne BU-Schlüssel)
- BU-Schlüssel
- Belegdatum
- Belegfeld 1
- Belegfeld 2
- Skonto
- Buchungstext
- Postensperre
- Diverse Adressnummer
- Geschäftspartnerbank
- Sachverhalt
- Zinssperre
- Beleglink
- Beleginfo – Art 1
- Beleginfo – Inhalt 1
- Beleginfo – Art 2
- Beleginfo – Inhalt 2
- Beleginfo – Art 3
- Beleginfo – Inhalt 3
- Beleginfo – Art 4
- Beleginfo – Inhalt 4
- Beleginfo – Art 5
- Beleginfo – Inhalt 5
- Beleginfo – Art 6
- Beleginfo – Inhalt 6
- Beleginfo – Art 7
- Beleginfo – Inhalt 7
- Beleginfo – Art 8
- Beleginfo – Inhalt 8
- KOST1 – Kostenstelle
- KOST2 – Kostenstelle
- Kost-Menge
- EU-Land u. UStID
- EU-Steuersatz
- Abw. Versteuerungsart
- Sachverhalt L+L
- Funktionsergänzung L+L
- BU 49 Hauptfunktionstyp
- BU 49 Hauptfunktionsnummer
- BU 49 Funktionsergänzung
- Zusatzinformation – Art 1
- Zusatzinformation- Inhalt 1
- Zusatzinformation – Art 2
- Zusatzinformation- Inhalt 2
- Zusatzinformation – Art 3
- Zusatzinformation- Inhalt 3
- Zusatzinformation – Art 4
- Zusatzinformation- Inhalt 4
- Zusatzinformation – Art 5
- Zusatzinformation- Inhalt 5
- Zusatzinformation – Art 6
- Zusatzinformation- Inhalt 6
- Zusatzinformation – Art 7
- Zusatzinformation- Inhalt 7
- Zusatzinformation – Art 8
- Zusatzinformation- Inhalt 8
- Zusatzinformation – Art 9
- Zusatzinformation- Inhalt 9
- Zusatzinformation – Art 10
- Zusatzinformation- Inhalt 10
- Zusatzinformation – Art 11
- Zusatzinformation- Inhalt 11
- Zusatzinformation – Art 12
- Zusatzinformation- Inhalt 12
- Zusatzinformation – Art 13
- Zusatzinformation- Inhalt 13
- Zusatzinformation – Art 14
- Zusatzinformation- Inhalt 14
- Zusatzinformation – Art 15
- Zusatzinformation- Inhalt 15
- Zusatzinformation – Art 16
- Zusatzinformation- Inhalt 16
- Zusatzinformation – Art 17
- Zusatzinformation- Inhalt 17
- Zusatzinformation – Art 18
- Zusatzinformation- Inhalt 18
- Zusatzinformation – Art 19
- Zusatzinformation- Inhalt 19
- Zusatzinformation – Art 20
- Zusatzinformation- Inhalt 20
- Stück
- Gewicht
- Zahlweise
- Forderungsart
- Veranlagungsjahr
- Zugeordnete Fälligkeit
- Skontotyp
- Auftragsnummer
- Buchungstyp
- Ust-Schlüssel (Anzahlungen)
- EU-Land (Anzahlungen)
- Sachverhalt L+L (Anzahlungen)
- EU-Steuersatz (Anzahlungen)
- Erlöskonto (Anzahlungen)
- Herkunft-Kz
- Leerfeld DATEV intern
- KOST-Datum
- SEPA-Mandatsreferenz
- Skontosperre
- Gesellschaftername
- Beteiligtennummer
- Identifikationsnummer
- Zeichnernummer
- Postensperre bis
- Bezeichnung SoBil-Sachverhalt
- Kennzeichnung SoBil-Buchung
- Festschreibung
- Leistungsdatum
- Datum Zuord. Steuerperiode
- Fälligkeit
- Generalumkehr (GU)
- Steuersatz
- Land
- Abrechnungsreferenz
- BVV-Posiiton
- EU-Mitgliedstaat u. UStID (Ursprung)
- EU-Steuersatz (Ursprung)”
How to manually create a DATEV posting batch in R?
For our use case, it is sufficient to transfer the following information into the posting batch. Not all of them may be mandatory:
- Start date of the batch
- Start date of the fiscal year
- End date of the batch
- Date of the postings
- Amount
- Debit/credit code
- Debit account
- Credit account
- Posting text
- Client number
- Client name
- Consultant number
- G/L account length
- SKR (e.g. 03)
Now that we have discussed the theory, an R function can look like this to create working DATEV posting batches:
create_datev <- function(betrag,
belegdatum, # als R-Datum
soll_haben_kennziffer,
konto_soll,
konto_haben,
buchungstext,
name = "Mustermann",
beraternummer = 1001,
mandantennummer = 99999,
wj_beginn = "20210101",
sachkontenlaenge = 4,
skr = "03") {
# Checks
stopifnot(is.Date(belegdatum))
stopifnot(nchar(konto_soll) == sachkontenlaenge)
stopifnot(nchar(konto_haben) == sachkontenlaenge)
stopifnot(nchar(wj_beginn) == 8)
# Make sure every transaction is complete
n_transactions <- length(belegdatum)
stopifnot(length(konto_soll) == n_transactions)
stopifnot(length(konto_haben) == n_transactions)
stopifnot(length(buchungstext) == n_transactions)
stopifnot(length(betrag) == n_transactions)
# DATEV-Header bestehend aus zwei Zeilen
# Zeile 1 wird später generiert
datev_header <- c(
"",
"Umsatz (ohne Soll/Haben-Kz);Soll/Haben-Kennzeichen;WKZ Umsatz;Kurs;Basis-Umsatz;WKZ Basis-Umsatz;Konto;Gegenkonto (ohne BU-Schlüssel);BU-Schlüssel;Belegdatum;Belegfeld 1;Belegfeld 2;Skonto;Buchungstext;Postensperre;Diverse Adressnummer;Geschäftspartnerbank;Sachverhalt;Zinssperre;Beleglink;Beleginfo - Art 1;Beleginfo - Inhalt 1;Beleginfo - Art 2;Beleginfo - Inhalt 2;Beleginfo - Art 3;Beleginfo - Inhalt 3;Beleginfo - Art 4;Beleginfo - Inhalt 4;Beleginfo - Art 5;Beleginfo - Inhalt 5;Beleginfo - Art 6;Beleginfo - Inhalt 6;Beleginfo - Art 7;Beleginfo - Inhalt 7;Beleginfo - Art 8;Beleginfo - Inhalt 8;KOST1 - Kostenstelle;KOST2 - Kostenstelle;Kost-Menge;EU-Land u. UStID;EU-Steuersatz;Abw. Versteuerungsart;Sachverhalt L+L;Funktionsergänzung L+L;BU 49 Hauptfunktionstyp;BU 49 Hauptfunktionsnummer;BU 49 Funktionsergänzung;Zusatzinformation - Art 1;Zusatzinformation- Inhalt 1;Zusatzinformation - Art 2;Zusatzinformation- Inhalt 2;Zusatzinformation - Art 3;Zusatzinformation- Inhalt 3;Zusatzinformation - Art 4;Zusatzinformation- Inhalt 4;Zusatzinformation - Art 5;Zusatzinformation- Inhalt 5;Zusatzinformation - Art 6;Zusatzinformation- Inhalt 6;Zusatzinformation - Art 7;Zusatzinformation- Inhalt 7;Zusatzinformation - Art 8;Zusatzinformation- Inhalt 8;Zusatzinformation - Art 9;Zusatzinformation- Inhalt 9;Zusatzinformation - Art 10;Zusatzinformation- Inhalt 10;Zusatzinformation - Art 11;Zusatzinformation- Inhalt 11;Zusatzinformation - Art 12;Zusatzinformation- Inhalt 12;Zusatzinformation - Art 13;Zusatzinformation- Inhalt 13;Zusatzinformation - Art 14;Zusatzinformation- Inhalt 14;Zusatzinformation - Art 15;Zusatzinformation- Inhalt 15;Zusatzinformation - Art 16;Zusatzinformation- Inhalt 16;Zusatzinformation - Art 17;Zusatzinformation- Inhalt 17;Zusatzinformation - Art 18;Zusatzinformation- Inhalt 18;Zusatzinformation - Art 19;Zusatzinformation- Inhalt 19;Zusatzinformation - Art 20;Zusatzinformation- Inhalt 20;Stück;Gewicht;Zahlweise;Forderungsart;Veranlagungsjahr;Zugeordnete Fälligkeit;Skontotyp;Auftragsnummer;Buchungstyp;Ust-Schlüssel (Anzahlungen);EU-Land (Anzahlungen);Sachverhalt L+L (Anzahlungen);EU-Steuersatz (Anzahlungen);Erlöskonto (Anzahlungen);Herkunft-Kz;Leerfeld DATEV intern;KOST-Datum;SEPA-Mandatsreferenz;Skontosperre;Gesellschaftername;Beteiligtennummer;Identifikationsnummer;Zeichnernummer;Postensperre bis;Bezeichnung SoBil-Sachverhalt;Kennzeichnung SoBil-Buchung;Festschreibung;Leistungsdatum;Datum Zuord. Steuerperiode;Fälligkeit;Generalumkehr (GU);Steuersatz;Land;Abrechnungsreferenz;BVV-Posiiton;.EU-Mitgliedstaat u. UStID (Ursprung);EU-Steuersatz (Ursprung)"
)
created_at <- format(Sys.time(), "%Y%m%d%H%M%S")
created_at <- paste0(created_at, "000")
beginndatum_stapel <- min(belegdatum)
beginndatum_stapel <- format(beginndatum_stapel, "%Y%m%d")
enddatum_stapel <- max(belegdatum)
enddatum_stapel <- format(enddatum_stapel, "%Y%m%d")
datev_header[1] <- paste0(
'"EXTF";700;21;"Buchungsstapel";12;',
created_at,
';;;',
name,
';"";',
beraternummer,
';', mandantennummer, ';',
wj_beginn, ';',
sachkontenlaenge, ';',
beginndatum_stapel, ';',
enddatum_stapel, ';',
'"Bezeichnung des Buchungsstapels";"";1;;;"EUR";;;;;',
skr, ';;;"";""'
)
betrag <- gsub(pattern = "\\.", replacement = ",", x = as.character(betrag))
datum_tag <- day(belegdatum)
datum_monat <- month(belegdatum)
datum_tag <- ifelse(test = datum_tag < 10,
yes = paste0("0", datum_tag),
no = as.character(datum_tag))
datum_monat <- ifelse(test = datum_monat < 10,
yes = paste0("0", datum_monat),
no = as.character(datum_monat))
belegdatum <- paste0(datum_tag, datum_monat)
buchungen <- paste0(
betrag,
';\"',
soll_haben_kennziffer,
'\";\"\";\"\";\"\";\"\";',
konto_soll,
';',
konto_haben,
';\"\";',
belegdatum,
';\"\";\"\";\"\";\"',
buchungstext,
'\";0;\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";;\"\";;\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";\"\";0;;;\"\";0;0;\"\";\"\";\"\";\"\";\"\"'
)
return(c(datev_header, buchungen))
}Convert sample posting to DATEV posting batch
Let’s assume we now have the following posting transaction: interest income of 100€ is credited to the company’s account.
So a possible posting record (SKR03) would be:
1200 Bank to 2650 Other interest and similar income
With the above R function, one can now convert this posting record into a DATEV posting batch, so that it can be read in by common accounting programs or by the tax consultant.
one_entry <- create_datev(
betrag = 100,
belegdatum = as.Date("2022-04-05"),
soll_haben_kennziffer = "S",
konto_soll = "1200",
konto_haben = "2650",
buchungstext = "My Interest",
name = "My company GmbH",
wj_beginn = "20220101"
)
write_lines(one_entry, "my_posting_batch.txt")Example: Convert CSV with postings into DATEV posting batch
More practical is certainly this example, in which we read in bookings from a CSV file and convert them into a DATEV booking batch. With read_csv a table can be read from the hard disk, for better clarity we generate here the CSV directly in the code. Finally, we save the posting batch as a text file so that it can be imported by accounting programs.
Once again, a DATEV-compliant posting batch is ultimately nothing more than a CSV file with specific columns. A CSV, in turn, is a table in text form, where the columns are separated by commas or semicolons.
In the first section of the code, we create such a CSV and read it in. Then we create the batch of entries with create_datev and in the last step we save it as a .txt file, which can then be imported by the accounting program.
entries <- c(
"Datum, Betrag, Sollkonto, Habenkonto, Text
2022-01-01, 100, 1200, 2600, Zinsertrag
2022-02-01, 200, 1200, 2600, Zinsertrag
2022-03-01, 300, 1200, 2600, Zinsertrag"
)
entries <- read_csv(entries)
entries$Datum <- as.Date(entries$Datum)
datev_batch <- create_datev(
betrag = entries$Betrag,
belegdatum = entries$Datum,
soll_haben_kennziffer = "S",
konto_soll = entries$Sollkonto,
konto_haben = entries$Habenkonto,
buchungstext = entries$Text,
wj_beginn = "20220101"
)
write_lines(datev_batch, "my_datev_batch.txt")
