« Altova RaptorXML | Main | Getting the Best Information and Keeping Current »

Semantic Spreadsheets

Over the years I have mentioned the notion of the semantic spreadsheet. Probably the best example of a semantic spreadsheet can be seen in this software application. Watch the "Introduction" video, the first in the list.

At a point in the video they talk about "meaningful labels rater than arbitrary coordinates provided by rows and columns". That is the difference between today's electronic spreadsheets and tomorrow's semantic spreadsheets.  Rather than working with meaningless cell references such as "Row 25, Column D" you work with meaningful semantic information, "Sales for fiscal year end 2012".

Why is this such a significant difference?  Well, if you have ever tried to link spreadsheets together or tried to reuse information within one spreadsheet in another spreadsheet, you already understand.  Linking or reusing based on where something is presented is brittle.  If someone adds a new row or column to the spreadsheet you are linking to, your links break.

Semantic spreadsheets will work differently. There are three key points to understand.

First, because the coordinates of the information in a semantic spreadsheet are based on meaning rather then the presentation coordinates, linking is far less brittle.  The linking of information is driven by the information's semantics (meaning). The meaning does not change.  For example, "total Sales for fiscal year 2012 for the consolidated group" is always the same and the details of that total is likewise always the same.  You may have different aspects or breakdowns of that total, but the breakdown will always add up to the total.

Second, the semantics are enforced by business rules which could be included within the spreadsheet or  seperate from the semantic spreadsheet and therefore usable across many spreadsheets or all spreadsheets throughout an organization.  This both makes the bindings between spreadsheets stronger and improves information quality because one set of business rules common to any spreadsheet can be used to make sure the information is always correct.

The third difference relates to something which is a bit harder to explain, but I will give it a shot.  If you use spreadsheets you are probably familiar with pivot tables.  Excel pivot tables only work with numbers in the cells, the coordinates which contain information.  If you look closely to the Quantrix Modeler in the video above, you can see that it also only works with numbers within the cells.  But information contains both text AND numbers.  Pivot tables and the Quantrix Modeler are incorrectly focused on just numbers. I don't want to go into a long explanation here, you can get that information from this blog post.  I will just tell you that a lot of software is incorrectly focused on or optimized for numbers and OLAP and make it very difficult or impossible to incorporate both text and numbers into their models.  That will change.

The best example of a semantic spreadsheet that I have seen is the XBRL Cloud viewer.  Now, you still need to use your imagination because the tool only allows you to view information, not create information.  Here is an SEC financial report as viewed within the XBRL Cloud viewer. Also, there are a significant number of improvements which can be made in all sorts of areas; but if you look through all the distractions you can see the notion of a semantic spreadsheet and how it might work.

Fiddle with the XBRL Cloud viewer.  In particular see how the connections between pieces of the financial report work.  For example, click here to go to "Property, plant and equipment" for 2012 on the balance sheet:

When you go to the balance sheet using the link provided, click on one of the fact values for the line item "Property, plant and equipment".  A dialog box comes up "Fact Characteristics and Properties".  You are in the properties view, click on the "Occurrences" tab.  Notice that you see the disclosure "1074 - Disclosure - Components of Property and Equipment".  Click on that.  Notice that it takes you directly to that disclosure.

Those two places in the financial statement are linked together semantically by the fact total property, plant and equipment, the legal entity "consolidated group", the reporting entity represented by the Microsoft SEC CIK number, and the period which you clicked on.  That link is automatic, the XBRL technical syntax takes care of all the details and the software implemented those technical rules.

Way back in 2004 I created some prototypes of semantic spreadsheets.  Here is a screen shot. (Click on the graphic for a larger view)

This is basically an audit schedule for information related to long-term debt.  The yellow notes point to different pieces of a financial statement where a fact would be shown.  I created some macros which basically took the information in the spreadsheet, generated the XBRL which represents this audit schedule using the XBRL GL (XBRL Global Ledger) format, and other stuff necessary to connect this audit schedule and numerous other audit schedules for accounts receivable and accounts payable to a financial report.

I did this back in 2004 to better understand how all this worked so that I could bring this information into the project to create the US GAAP Taxonomy.

Imagine a set of semantic spreadsheets all interconnected which represent a financial report and all the audit schedules and other detailed schedules which represent the information which supports the financial statement.  Imagine accounting systems and other systems generating these detailed spreadsheets, trial balances, agings, sub ledgers, etc.

Would that be useful to accountants?  I would certainly think so, that is exactly why XBRL was created. Basically you get everything that you get from spreadsheets today but more effectiveness and effeciency.  Who would not want that?

Why don't we have this sort of stuff today? Read the book The Innovators Dilemma.  That explains everything.  Most software vendors are going after what SEC filers and others mandated to use XBRL are asking for.  They are focused on meeting required mandates.  Few understand both the technology piece and the domain.  Some software vendors do get this.  You will see semantic spreadsheets which work correctly.  In fact, I believe that the semantic spreadsheet will be the killer app which enables digital financial reporting to take off.

What do you think?  If you understand what I am trying to say above, talk to software vendors about this.  The more accountants who ask for this sort of thing, the sooner some software vendor will deliver products which support this type of functionality.

Maybe semantic spreadsheets can help fix this problem of errors in Excel spreadsheets.

Panko's meta-analysis of several such studies, he claims, indicates that 84 percent of spreadsheets contain some kind of materially significant error.

PrintView Printer Friendly Version

EmailEmail Article to Friend

Reader Comments

There are no comments for this journal entry. To create a new comment, use the form below.

PostPost a New Comment

Enter your information below to add a new comment.

My response is on my own website »
Author Email (optional):
Author URL (optional):
All HTML will be escaped. Hyperlinks will be created for URLs automatically.