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

[JadeConnor]

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 XBRL (iXRBL - XML in HTML)
http://resources.companieshouse.gov.uk/toolsToHelp/pdf/inlineXBRLsample.pdf

And I need them putting into this format:

XBRL (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).

I want the next file to be on the next row and so on.

Any ideas on how to get the first link to show as table in excel? Or a Windows 8 GUI to sort it?

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

 

[JadeConnor]

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

Link to Q other forum