Auto-generated XBRL from Excel-based Logic
Thursday, February 13, 2020 at 10:25AM
Charlie in Digital Financial Reporting

I took information for a small logical system, SFAC 6 Elements of a Financial Statement, put the logic of that information in an Excel spreadsheet, and then generated the XBRL taxonomy schema, XBRL linkbases, XBRL formulas, and XBRL instance.  I then validated everything to make certain that it was correct, both the XBRL syntax and the financial information logic, using three different XBRL processors.  Here is information for this experiment: 

I created the processing using Microsoft Access.  I was going to use Excel but did not want to suffer trying to figure out VLOOKUPS and such necessary.  Relational queries are much easier.  I wanted to use Excel so it would be easier to share the code.  I simply attached the Excel spreadsheets to the Access database and processed the information using Access.

There was one task where I "cheated".  Because my programming skills do not allow me to parse the XBRL formulas such as "$ComprehensiveIncome = ($Revenues - $Expenses + $Gains - $Losses)"; I manually put each of the concepts into a relational database table so that I could have the information I needed to autogenerate the XBRL Formulas.  Other than that short cut which adds a little work for the creator of the Excel data; 100% of the information necessary to generate the XBRL yourself exists in that Excel spreadsheet.  There a handful of lookup tables that I created to convert say provide the friendly data type "Monetary" to the Excel user but then generate "xbrli:monetaryItemType" in the XBRL taxonomy schema.

It took me only about 16 hours to write the the code and there is about 1,600 lines of code.  Not that much and probably 30% of that is blank lines, comments, etc.

While it took me 16 hours to program, it took me 10 years to figure out WHAT to program.  There is one additional detail that I want to implement that I will explain later.

What I created takes into consideration the information about how to exchange complex financial information effectively.  The model used in Excel to represent the logic follows the logical conceptualization of a business report prescribed by the forthcoming OMG Standard Business Report Model (SBRM).

"Why is this significant?", you may ask.  Well, it is actually significant for an number of reasons.  First, this ends to discussion about syntax.  I already represented SFAC 6 in XBRL, Prolog, and now Excel syntax.  I then converted the Excel into XBRL proving that I can go from Excel >> XBRL; or XBRL >> Excel.  Next, I am going to modify the application to output PROLOG. Then I will run that using the online SWISH PROLOG Processor.

After that I will add functionality to my application to convert the Excel into OWL/RDF/SHACL and then process that using Protege or some other semantic reasoner.

Second, I will be able to compare the capabilities of XBRL, Prolog, and OWL/RDF/SHACL to process the complex financial information contained in a financial report. Not all knowledge representation tools are equal.

Today, there are two tools that can process 100% of the business rules that I can specify and that are REQUIRED to verify that a financial report logical system is properly functioning.  If you use the formula chaining capabilities of XBRL you can get a little closer with just basic standard XBRL processing (If you don't understand XBRL formula chaining, see here; and have a look at these two examples where I left out the fact for liabilities {before} and then added it using formula chaining {after}.  This video walks you through how XBRL formula chaining works. This ZIP file has the batch file to run, the input file, and all the result files.)

Third, this proves that you can, in fact, create a properly functioning logical system that allows for the kind of variability that exists within a financial report.  With XBRL + SBRM + System Theory; this works today as long as you implement it correctly.  Don't like XBRL?  Fine, use whatever syntax you like. Fads, trends, preferences, and misinformation will come and go. It's all about logic and engineering. It's all about know how and brick-by-brick making it work effectively to solve a problem.

Article originally appeared on XBRL-based structured digital financial reporting (http://xbrl.squarespace.com/).
See website for complete article licensing information.