iXBRL to XBRL? XML inside HTML - Excel can't resolve

[JadeConnor]

Hi!
iXBRL to XBRL? XML inside HTML - Excel can't resolve


Hi!

I have a bunch of files which should be a table with one row and 30/40 columns with data, in this format:

Inline XRBL (iXRBL - XML in HTML)

http://resources.companieshouse.gov.uk/toolsToHelp/pdf/inlineXBRLsample.pdf

And I need them putting into this format:

XRBL (XML)

http://resources.companieshouse.gov.uk/toolsToHelp/pdf/XBRLsample.pdf

(The XML in this example link has an error in it and won't actually import into excel, but you get the general idea).

I have a few files in the XRBL XML format already and they open in Excel and the data goes into columns automatically - ideal. Opens up as a few rows with 30/40 columns with the values in them.

I am trying to get the iXRBL files into Excel in the same fashion, with no luck, it won't display.

I need a batch gui to run across all the iXRBL files (.html) to, at least, convert them into XRBL XML, which should allow me to open in Excel and have all the data go into the right columns. Ideally I would then need to combine it all into one XML file/XLS.

Other ideas I had was a windows GUI app (I'm no programmer but have a general idea) to "extract" multiple expressions from all the files in the directory and import automatically into excel; putting the filename EntityCurrentLegalName value in Column A colum and taking out all the defined expressions above and their value into the B, C, D, E, etc. columns.

Other ideas I had was, lets say there are 40 columns, was to duplicate the folder 40 times, and run a search and replace tool deleting everything in the files, except for the expression, for example:

Folder 1: "Filename (EntityCurrentLegalName) + value" (COLUMN A)
Folder 2: "CalledUpShareCapital + value" (COLUMN B)
Folder 3: "NetAssetsLiabilitiesIncludingPensionAssetLiability + value" (COLUMN C)

etc, etc. - I would need to use wildcard for the value as would be different in each file and search the whole expression, i.e deleting everything before and after that expression...then combining all the files together as text and just pasting each column directly into Excel, cleaning it all up - very round-about-way to do it.

(The XML in the example link has an error in it and won't actually import into excel, but you get the general idea).

Any ideas on a windows 8 GUI to achieve?

Thanks!
Jade

 

[JadeConnor]

Another way of looking at it is:

See:

ix:nonFraction name="uk-gaap-pt:CashBankInHand" contextRef="current-mud"
unitRef="currencyUnit" decimals="0">1000


I need to get that figure of 1000 into its own column in an Excel workbook. But in all the different files, the figure will be a different number. But the rest of the text "around" that value will be the same in all files. So I will have a column, C, for example with all different figures in from all the different files.

Then I need the next value:

ix:nonFraction name="uk-gaap-pt:NetAssetsLiabilitiesIncludingPensionAssetLiability" contextRef="current-mud"
unitRef="currencyUnit" decimals="0">4500


Again, 4500 will be a different value in each text/xml file. And I need all those into column D, from all the files. Again, the text around the figure is the same for each value. So I would need 30/40 modules/VB script for Excel to grab these from all the files in a directory, and put in Excel...

If you know a module or VB I can use in Excel instead, to do this, please help

Thanks
Jade

 

[k5tm]

Hello, Jade. Welcome to Tek-Tips.

I need a batch gui to run across all the iXRBL files (.html) to, at least, convert them into XRBL XML, which should allow me to open in Excel and have all the data go into the right columns.

Let's get the XBRL un-embedded (debedded??), to get you through your 'at least' step.

The embedded iXBRL document takes advantage of XML namespaces, which are used to inform the users of an XML document (of which iXBRL document is, a blending of XHTML and XBRL specifications that both use XML) how to interpret the various XML elements (or tags). It appears that the namespaces for the elements that are XBRL all contain the string 'xbrl' in them. I find the following XBRL namespaces in use:
[tt][ul]
[li]

http://xbrl.ifrs.org/taxonomy/2014-03-05/ifrs-smes

[/li]
[li]

http://xbrl.ifrs.org/taxonomy/2014-03-05/ifrs-full

[/li]
[li]

http://www.xbrl.org/inlineXBRL/transformation/2010-04-20

[/li]
[li]

http://www.xbrl.org/2003/XLink

[/li]
[li]

http://www.xbrl.org/2003/linkbase

[/li]
[li]

http://xbrl.org/2006/xbrldi

[/li]
[li]

http://www.xbrl.org/2008/inlineXBRL

[/li]
[li]

http://www.xbrl.org/2003/iso4217

[/li]
[li]

http://www.xbrl.org/dtr/type/numeric

[/li]
[li]

http://www.xbrl.org/dtr/type/non-numeric

[/li]
[li]

http://www.xbrl.org/2006/ref

[/li]
[li]

http://xbrl.org/2005/xbrldt

[/li]
[li]... and there are probably more.[/li]
[/ul][/tt]
XSLT is an XML-based technology that is used to transform XML documents into other XML documents (or HTML or text, not used for this example). One describes 'transforms' in XSLT. The transform that the following example XSLT describes is a variant of an identity transform. An identity transform is a transform that produces an output document that is structurally identical to the input document. In your case, we would like to get the elements related to XBRL in the form of an identity transform while ignoring the elements related to HTML.

<xsl:stylesheet version="1.0" xmlns:xsl="[URL unfurl="true"]http://www.w3.org/1999/XSL/Transform">[/URL]
<xsl:output method="xml" indent="yes" encoding="utf-8"/>

<xsl:template match="/">
<xsl:apply-templates select="*"/>
</xsl:template>

<xsl:template match="*[contains(namespace-uri(),'xbrl')]">
<xsl:copy>
	<xsl:apply-templates select="@* | node()"/>
</xsl:copy>
</xsl:template>

<xsl:template match="*[not(contains(namespace-uri(),'xbrl'))]">
<xsl:apply-templates select="*"/>
</xsl:template>

<xsl:template match="node()">
<xsl:copy>
	<xsl:apply-templates select="@* | node()"/>
</xsl:copy>
</xsl:template>

<xsl:template match="@*"><xsl:copy/></xsl:template>

<xsl:template match="text()[string-length(normalize-space()) = 0]"/>
</xsl:stylesheet>

On Windows, you have a built in XSL processor, called MSXML.DLL. You can find a JScript script at the end of this thread (thread426-1723716) that can be used in a batch stream to run the XSLT transformation on a bunch of files (note the title of the referenced thread -- and he meant millions!).

If there is some rational process to combine the XBRL documents, then one can do that with XSLT as well. But, first, baby steps...

Tom Morrison
Hill Country Software