SPESENFUCHS
An overview of the different file formats based on a travel expense report
In Germany, certain tax regulations apply to the settlement of travel expenses. There are some software solutions for travel expense reporting on the market that help to apply these regulations correctly. After that, the travel costs and expenses must be transferred to the financial accounting system and paid to the employees. However, if the data obtained has to be manually transferred to financial accounting after the payroll has been prepared, the fun stops.
To avoid these so-called “media breaks”, there are interfaces from the travel expense software solutions to various accounting systems. DATEV can still be described as the “number one” among tax advisors. Therefore, this article explains the different possibilities how to transfer the data from a software for travel expenses, using the example of Spesenfuchs, to DATEV.
A concrete example is used to run through all three possibilities. The structure of the files is explained in detail.
Products
First, we will very briefly introduce the products that we will be talking about below:
Kanzlei-Rechnungswesen is a software from DATEV for tax advisors, with which they can handle all activities from financial accounting to annual financial statements.
DATEV document transfer is a free tool from DATEV for sending (by the tax advisor) document data from the client to the DATEV Data centre.
DATEV Unternehmen Online, called DUO, replaces the “commuting folder”. Companies can send their receipts to the tax advisor via this online application. Here it is possible to send also accounting data such as amounts, descriptions and accounts.
Spesenfuchs is an online software solution for travel expense reporting.
Overview
By way of introduction it can be said that there are 3 (or 4) ways to bring the data to DATEV:
- CSV file that is imported into Kanzlei-Rechnungswesen
- XML file that is imported into DATEV Unternehmen online
- CSV file in combination with an XML file (mixture of 1. and 2.)
- Transfer of data from 2. through an online API (automated).
Example situation
The following data is taken as a basis for the examples: the self-employed pool builder Sepp Elmann goes on a 1-day business trip from Karlsruhe to Freiburg. Departure 6:00, return the following day at 23:00. He submits a receipt for entertainment.
His statement of account is as follows:
While the statement is clear for the business traveler, it does not include the accounts for the bookings. For this there is a booking list for this trip, which contains all bookings.
This posting list lists all the necessary posting records to correctly post the travel expenses, including VAT specials, etc. While this list makes it easy to transfer the entries to accounting, it is of course tedious. Wouldn’t it be nice to be able to transfer the whole thing directly to the accounting software? Here we are at the actual topic of this article, the interfaces.
The CSV interface
The CSV interface is the oldest of the DATEV formats. With the CSV interface no document images can be transferred to Kanzlei Rechnungswesen. This format is also supported by many other programs and can therefore be considered almost a de facto standard. It is only one file that starts with EXTF_ and ends with .csv.
First line: The first line always has the same structure and contains information for the entire file, such as the DATEV consultant number and the DATEV client number.
Second line: The second line of the file also always has the same structure and provides the headings for the following data lines.
N data lines: One line per posting record. A proud 120 columns, not all of which need to be filled in our case.
The following image shows the CSV file. In the second line are the headings for the posting lines.
The way of the data is simple: the user sends the file to the tax advisor and the latter reads it directly into Kanzlei Rechnungswesen. As described above, the disadvantage is that no document images can be transferred this way.
The XML interface
The DATEV XML interface is an XML-based file interface for importing data to DATEV Unternehmen online. With this file format, the entries can be transferred with both records and the corresponding document images. These files are imported via the desktop application DATEV Belegtransfer.
A set of files for the bookings from our example contains the following files for the XML interface:
These files are discussed in detail below.
Adocument.xmlfile
Further, the set of files contains N files with the extension .xml. The number of files depends on how the manufacturer of the software that generates them divides the data. In the case of Spesenfuchs, one file is created per receipt and per trip, and another file is created for the trip and its per diems and, if applicable, the deductions for meals. So, in our example, there are 3 files, one for the trip with its additional meal expenses, one for the trip, and one for the receipt. These files are structured as follows:
Other files are all receipt images and other files, in our example the travel expense report of the trip (already shown above, Figure 2) and the receipt for the hospitality.
If you now take a closer look at the files, you will notice that the document.xml is something like a table of contents for the entire package. The document.xml establishes the connection between the individual .xml files and the document images. We will look at this in more detail using the example of the receipt for hospitality.
In the document.xml there is this section:
By this the picture for the hospitality with the file name 202009071523-316261422-1.png can be assigned to the booking data in the file SPF_1_559_002.xml during the import.
The file SPF_1_559_002.xml contains the posting data for this document (upper area omitted for clarity):
...2020–08–31 19.70 6640 8 EUR Bewirtungskosten, Mahlzeit, Einladung Kunde nach erfolgreich Bewirtungskosten 70001 2020–08–31 2020–08–31 12.60 6640 9 EUR Bewirtungskosten, Getränke, Einladung Kunde nach erfolgreich Bewirtungskosten 70001 2020–08–31
The same applies to the other sections or files.
If negative amounts were necessary, such as a meal reduction, they would receive a minus.
If now a document without image would be entered, one would have to transfer a dummy image, otherwise a transfer of the booking data with this interface format would not be possible.
The data is sent from the user to the tax advisor, from there via the DATEV document transfer app to DATEV Unternehmen online and from there to Kanzlei Rechnungswesen.
The XML+ CSV interface
This interface is a mixture of the two XML and CSV described above. However, both XML and CSV contain other data in certain places.
A set of files for the postings from our example contains the following files for the XML + CSV interface:
The zip file shown in Figure 11 is sent to the tax consultant without unpacking it. Their content is similar to the XML interface but not the same:
Since the booking data is taken from the CSV file, there is no need for the dummy image. Only the document images that are actually required are sent via the XML interface. This also eliminates all data about the documents in the XML file. The XML file is therefore much smaller. There are no XML files other than the document.xml file.
Also with this interface format the document.xml file represents a kind of table of contents.
But first we have to look at the CSV file: In the CSV file there is also a difference to the pure CSV interface: in the column “document link” the UUID or also GUID, i.e. a unique identifier is listed, which is also used in the document.xml. This allows to establish the connection between the images and the bookings in the CSV file.
To explain the hospitality receipt in this example as well, let’s look at this area in the XML file:
If the guid’s of the two lines in the CSV file for the hospitality are compared with these two entries, one recognizes the connection that is made here and links the document screens with the bookings when reading in via DUO or Kanzlei-Rechnungswesen.
The path of the data is as follows:
The CSV file is read by the tax advisor directly into Kanzlei Rechnungswesen. The XML file is imported by the tax advisor via the app DATEV Belegransfer into DATEV Unternehmen online and from there into Kanzlei Rechnungswesen. The UUID creates a link between the data.
Interface DATEV Online
The DATEV online interface uses the files of the XML interface described above. The data from XML interface is sent online from the external application itself directly to DATEV Unternehmen online. Manual handling is no longer necessary. However, the structure of the data is the same as for the XML interface. Since we are only devoted to file formats here, there is nothing more to say about this topic.
Summary
Basically, the facts are not complicated. But even we at Spesenfuchs had to understand the differences first when we implemented the interfaces. Therefore this contribution.
Further, we would like to give our users a more detailed overview of the export options to better decide which format to use. All described formats can be generated in Spesenfuchs.
Links to the topic
https://www.datev-community.de moderated forum from DATEV
https://developer.datev.de/portal/ Developer Portal from DATEV (free login required)
