Kluwer International Tax Blog

The problem

“Please tell me what the header is, I don’t know what the namespace for multiple schemas should be.”  It seemed like a reasonable question, but I never got the answer.  The same fate struck my other seemingly simple request: “Can you please give us a sample of a completed, valid, xml file so we can at least see what it should look like?”

Compliance should never be this difficult.

Multinationals filing their Country by Country files by 31 December are in for a rough ride.  We thought that collecting the data would be the major obstacle; we thought that we had to deliver two or three Excel spreadsheets, because that is what it looked like in Annex III of the OECD BEPS Action 13 plan.  Boy were we wrong.

The OECD published BEPS Action 13 and various Action 13 guidelines.  As tax professionals we can read those and understand most of it.  But then there is the guideline called “Country-by-Country Reporting XML Schema, User Guide for Tax Administrations” (my underlining – and here after referred to as “the User Guide”).  Very, very few tax professionals can read this.  Which is not surprising, as it is written for IT people, working for governments.  It is a guideline for explaining how an XML schema (called an xsd file) should be read and how a data file, called an xml file, should be validated against the xsd files (there are 3 in the CbCR XML Schema).

The main OECD XML schema contains about 14 pages of rules: get one wrong and your whole xml file will be rejected.  However, readers hoping to find guidance in the User Guide on how to convert their Excel files into a valid xml file will be sorely disappointed; it does not say a word about Excel.  Remember, this is a “User Guide for Tax Administrations”.

Excel itself does have a built-in function for exporting data into xml.  It even has a function for doing so into a simple XML Schema.  But the OECD XML Schema is not simple.  Any attempt and Excel simply tells you that it cannot do “lists of lists”.  “Lists of lists” is computer speak for saying Excel can do 2 dimensions (rows and columns) only, not more.  The more computer savvy tax professionals will then realise that Microsoft Access should be able to do this, because that is exactly what databases are: lists of lists.  However, the OECD XML Schema simply crashes the 2010 and 2016 versions of Access I tried; they are too complicated.

So, considering the above, have the tax administrations issued guidance, or helpful tools, or something?  Well, no.  It seems as though most tax administrations have basically dumped the task for figuring out the User Guide with their taxpayers.  (One exception that I am aware of is the Dutch government – kudos to them).

This basically leaves taxpayers in a consultants’ nirvana.  Several IT firms never heard of before and a few well-known tax consulting firms now offer to convert companies’ Excel files to XML, for a seemingly agreed average of Euro 2.500.  Typically that is for the conversion only – any additional actions such as uploading the files through a special portal cost extra.  It reminds me of the situation where public transport goes on strike and taxi prices magically jump 300% ….  It also leaves the same bitter after taste and the determination to find a better way.

The DYI solution

Companies can do this themselves, though it does take some effort.  It is basically a three step process.

First step: Get your Excel files into XML

XML looks like this

<Country>
<CountryCode>NL</CountryCode>
<Revenues>
<Internal Revenues> 100</Internal Revenues>
<External Revenues>150</External Revenues>
<Total Revenues>250</Total Revenues>
</Revenues>
</Country>

There are at least three ways to get your Excel file into that format.

1. Write a macro in Excel VBA to do this for you. In fact, this is the one thing the OECD and tax administration could have done and should have done; in the very least.
   In the absence thereof, several in-house tax departments now have IT savvy people on board which may be able to do this for them.
   This is also what I ended up doing, and by the way a valid XML header then is “<cbc:CBC_OECD xmlns:iso=”urn:oecd:ties:isocbctypes:v1″ xmlns:cbc=”urn:oecd:ties:cbc:v1″ mlns:stf=”urn:oecd:ties:stf:v4″ xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xsi:schemaLocation=”urn:oecd:ties:cbc:v1 CbcXML_v1.0.1.xsd”>”.
2. A second way may be to buy an XML Editor and combine your Excel files with the OECD’s XML Schemas into an XML file that can be validated. I assume this is possible, but I have not tried it.  Besides, at an annual license fee of around USD 550 per company an XML editor such as Oxygen does not come cheap, especially if this is for a once year affair only.  The OECD itself used XML Spy which is slightly more affordable at Euro 400.
   The advantage of this solution – if it is doable – is that it automatically can be used to validate your XML file against the OECD XML Schemas as well.
3. A third way would be to export your Excel data to a third party database and from there to XML using a “simple” computing language, such as Python. But, frankly, if you can do that, you would not be reading this blog.

Second step: Validate your xml file online

The good news is that this is free.  But, there are two problems with online validation.

The first problem is that online validation is online.  Do you want to risk uploading your company data to a website, trusting that they will not store it afterwards and trusting that it will not be intercepted?  The choice is yours.  An alternative is obviously to populate the Excel files with mock data and validate your xml file that way, trusting that there will be nothing in the real data which would subsequently get invalidated (warning in this regard: do not use “<”, “&” or any letter outside the 26 letter alphabet).

That still leaves us with the second problem: finding an online validator that can work with multiple xsd files.  One solution that I found and used is the XML Validator from www.biyeescitech.com.  The interface is relatively simple and if there is something wrong with the XML file, it will give you hints as to what it is.  One problem, if the xml file header is not correct, the validation will fail in that the validator will tell you that the file is validated, when actually it should not be.  So if you go this route I recommend using the header I included above.

Third step: Recheck your validated xml file in Excel

Ironically, Excel is capable of turning your xml file back into Excel tables, in some recognisable fashion.  This makes sense, as Excel does not have to deal with the complications built into XML Schema and the xsd files.  It simply infers the schema from the order of the xml opening (“<>”) and xml closing (“</>”) elements.

To do this exercise e.g. in Excel 2016, one goes to the Excel “Data” ribbon and select “Get data”, “From file”, “From XML” and then select “CbcBody” in the popup Navigator to import the file.  When that is done, one “unfolds” the tables by clicking through the “┐┌” symbols in the column heads.

Conclusions

For MNE’s: I do hope the above is helpful, should you wish to do your filings yourselves.

For the OECD and for tax administrations: Compliance should not be this difficult.

For consultants: Don’t be greedy.  This should be an excellent opportunity to show loyalty to your customers, not an easy revenue raiser.  Give your solutions to them for free; that is what I do.

________________________

To make sure you do not miss out on regular updates from the Kluwer International Tax Blog, please subscribe here.