Welcome!

Industrial IoT Authors: Pat Romanski, William Schmarzo, Elizabeth White, Stackify Blog, Yeshim Deniz

Related Topics: Industrial IoT

Industrial IoT: Article

Computational XSLT for Financial Statements

A new role for the Extensible Stylesheet Transformation Language?

A more complex example is:


<item id="F13" formula="if( ($F10 -
$F10_prev) gt
0.01*$F10_prev ) then ($F10 + $F3)
else ($F10)"
type="calc"/>
In the corresponding function, formula:F13, the calculated-item variable $F10 is populated by calling its identically named formula function with context_id as the parameter:

<xsl:variable name="F10"
select="formula:F10( $context_id)"
as="xs:double"/>
In the definition of item F13, the _prev modifier on $F10_prev indicates that the value should be taken for the previous context. Such references are common in financial formulas. The corresponding variable in formula:F13 is created by:

<xsl:variable name="F10_prev"
select="formula:F10(helper:get_
previous($context_id))"
as="xs:double"/>
Here the function helper:get_previous(context_id) simply returns the context_id for a period of one year before that passed to it.

In the source code, compiler.xslt (Listing 4) was used to create functions.xslt from schema.xml; file host.xslt (Listing 5) shows how the functions in functions.xslt are called, and the file calculated_data.xml (Listing 6) shows the result when host.xslt transforms instance.xml.

This is all relatively straightforward and the formulas in functions.xslt obviously have all the flexibility of XPath 2.0 formulas.

Dealing with Missing (Null) Data
Sometimes data isn't available (null) for input items either because the company didn't report it or because a formula needs input data from a previous period and no data are available (inevitable for a period previous to the oldest one). Financial data experts decide how their formulas should handle nulls. For example, the logic for handing nulls must define the result for this sample of expressions unambiguously.


10 + null; null + null; 10 div null; null
div 10; if (null ne 10) then (null + 20)
else (30); if (25 gt null) ..; if(null = null).., etc..
In what follows, the business logic for evaluating formulas is based on practices in my organization; other organizations may have different business logic, but the same technical processing should apply.

Our experts dictate that formulas have a type (see below), and for each type allowable expressions are restricted. These restrictions are required if the null evaluation logic is to be applicable. The revised schema, schema_complex.xml (Listing 7), has items with the following four types:

  • Input type. The data is assigned to these in the instance document.
  • Simple calculation type. "Simple" in that there are no comparison operators and the formula must evaluate to a number.
  • Ratio. Has the form of numerator divided by denominator, where numerator and denominator are treated as sub-formulas with input or simple types.
  • Conditional. Has the form if (test is true) then return result from formula_if_true else return from formula_if_false. The test part has the form: (test_left_hand test_operator test_right_hand). The sub-formulas test_left_hand, test_right_hand, formula_if_true, and formula_if_false are input or simple types.

    Each calculated-type item also has the attribute null_eval_rule, which indicates how to evaluate a simple-type formula, or a simple-type sub-formula in a ratio or conditional type.

Null Logic
Given formulas restricted by these types, the main features of the logic are as follows, where numerical evaluation involves treating a null argument as zero. The reader need not concentrate on the details but should appreciate the growing complexity.
  • For a simple type, if any argument refers to a previous period and is null, the formula returns null regardless of any other rule.
  • For a simple type with null_eval_rule='null_if_all_null,' if there is any non-null argument, the formula is evaluated numerically, otherwise it returns null.
  • For a simple type with null_eval_rule='null_if_any_null,' if there is any null argument, the formula returns null; otherwise it is evaluated numerically.
  • For a ratio item type, numerator and denominator are evaluated like separate simple types that share the one null_eval_rule. If either returns null, the whole formula returns null; otherwise it is evaluated numerically.
  • For conditional item types, test_left_hand and test_right_hand are evaluated as separate simple types that share the single null_eval_rule. If only one of test_left_hand or test_right_hand is null and the operator is eq or ne, the test result is evaluated as true operator false; otherwise, any null test_left_hand and/or test_right_hand is treated as a zero and the two are compared as values. Having evaluated the test to true or false, the whole formula returns either formula_if_true or formula_if_false treated as a simple type.
Applying the Null Logic
When the revised schema, schema_complex.xml, is transformed, the resulting functions_complex.xslt (Listing 8) has many new features to support the null logic. First, the XSchema type returned by the formula function has been changed from xs:double to xs:double?. The latter allows an empty sequence, the XPath 2.0 equivalent of null. Second, code has been added to help establish the null status of each argument and apply the rule(s) to get the resulting null status of the whole function. File functions_complex.xslt is annotated to highlight the new features.

All this may seem inordinately complex, but any formula definition language must define the logic for evaluating a formula in a format that a processing application can analyze and apply, and that is readable. For example, XBRL Formula Requirements recognizes the need for the flexible coding of such logic and proposes using a subset of ECMAScript or XPath 1.0. The processing application would then compile that script to its own executable code. However, in our pure XSLT approach, the formula function in functions_complex.xslt simply uses helper functions and some additional code in the same file to implement the evaluation logic.

How Well Does Computational XSLT Do on Real-Life Financial Statements?
As explained above, the speed of the compiler stage isn't critical but, for the record, even with rules more complex than the preceding, hundreds of formula functions are created in approximately 10 seconds. It is more important for the compiler to create fast XSLT. XSLT indexes (using <xsl:key>) make a massive difference, as does any function-call-caching in the XSLT processor. For example, a function can be called 50 times with the same arguments, but if the result of the first call is cached, the next 49 calls are read from cache. The Saxonica 8.1 parser used here supports caching.

A batch of 8,000 XML instance documents (one per company), each with data for 170 input items at 15 periods, was transformed to 8,000 files with 336 calculated items for the same periods. Some formulas referred to calculated items whose formulas referred to calculated items, up to seven levels above the input data. The null evaluation and period adjustment rules were more complex than those discussed, and annulization and formatting were applied. The 45 million calculations were completed in seven hours, which easily meets our requirements and compares well with other processing routes. The conditions were as follows. Software: Saxonica 8.1 JAR files were incorporated into a lightweight Java 1.4 application; the JVM ran on Windows 2000. Hardware: a PC with a 3GHz processor and 3GB of RAM.

Summary
Computational XSLT is a serious option for applying formulas to financial statement data. A computational XSLT file can be generated from formula definitions and can incorporate the generally complex null-evaluation logic for applying those formulas. The XSLT file provides a way of sharing a readable, run-anywhere implementation of formulas and their processing logic. For example, such a file could be shipped with data (input + calculated) to show a user how the calculated data was derived, or to let users apply the formulas to their own data. Although not linked to any particular standard other than XSLT (or XQuery 1.0 if preferred), this approach is applicable to XBRL data. This XML-centric approach also offers a direct route to calculated data from data held in relational databases via the XML extract-and-transform (via XSLT) support from the major database vendors.

References

  • XBRL: www.xbrl.org
  • XBRL Formula Requirements, Public Working DRAFT of Tuesday, 20 April 2004: www.xbrl.org/technical/requirements/Formula-Req-PWD-2004-04-20.pdf at Members' Area of www.xbrl.org

    Acknowledgements
    The author thanks Fitch Ratings, Ltd., for permission to publish, and Otari Svanidze for his work in implementing the processes described in this article.

  • More Stories By Edmund Gimzewski

    Edmund Gimzewski has 15 years of experience in developing software for the financial sector and has been working with XML since its inception. In the last five years he has specialized in XML-centric systems for general software development and for defining and applying financial and other calculations.
    Edmund formerly worked as a scientist researching in the field of thermodynamics and has published nearly a dozen papers on thermodynamics (see Thermochimica Acta).

    Comments (0)

    Share your thoughts on this story.

    Add your comment
    You must be signed in to add a comment. Sign-in | Register

    In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.


    IoT & Smart Cities Stories
    Moroccanoil®, the global leader in oil-infused beauty, is thrilled to announce the NEW Moroccanoil Color Depositing Masks, a collection of dual-benefit hair masks that deposit pure pigments while providing the treatment benefits of a deep conditioning mask. The collection consists of seven curated shades for commitment-free, beautifully-colored hair that looks and feels healthy.
    The textured-hair category is inarguably the hottest in the haircare space today. This has been driven by the proliferation of founder brands started by curly and coily consumers and savvy consumers who increasingly want products specifically for their texture type. This trend is underscored by the latest insights from NaturallyCurly's 2018 TextureTrends report, released today. According to the 2018 TextureTrends Report, more than 80 percent of women with curly and coily hair say they purcha...
    The textured-hair category is inarguably the hottest in the haircare space today. This has been driven by the proliferation of founder brands started by curly and coily consumers and savvy consumers who increasingly want products specifically for their texture type. This trend is underscored by the latest insights from NaturallyCurly's 2018 TextureTrends report, released today. According to the 2018 TextureTrends Report, more than 80 percent of women with curly and coily hair say they purcha...
    We all love the many benefits of natural plant oils, used as a deap treatment before shampooing, at home or at the beach, but is there an all-in-one solution for everyday intensive nutrition and modern styling?I am passionate about the benefits of natural extracts with tried-and-tested results, which I have used to develop my own brand (lemon for its acid ph, wheat germ for its fortifying action…). I wanted a product which combined caring and styling effects, and which could be used after shampo...
    The platform combines the strengths of Singtel's extensive, intelligent network capabilities with Microsoft's cloud expertise to create a unique solution that sets new standards for IoT applications," said Mr Diomedes Kastanis, Head of IoT at Singtel. "Our solution provides speed, transparency and flexibility, paving the way for a more pervasive use of IoT to accelerate enterprises' digitalisation efforts. AI-powered intelligent connectivity over Microsoft Azure will be the fastest connected pat...
    There are many examples of disruption in consumer space – Uber disrupting the cab industry, Airbnb disrupting the hospitality industry and so on; but have you wondered who is disrupting support and operations? AISERA helps make businesses and customers successful by offering consumer-like user experience for support and operations. We have built the world’s first AI-driven IT / HR / Cloud / Customer Support and Operations solution.
    Codete accelerates their clients growth through technological expertise and experience. Codite team works with organizations to meet the challenges that digitalization presents. Their clients include digital start-ups as well as established enterprises in the IT industry. To stay competitive in a highly innovative IT industry, strong R&D departments and bold spin-off initiatives is a must. Codete Data Science and Software Architects teams help corporate clients to stay up to date with the mod...
    At CloudEXPO Silicon Valley, June 24-26, 2019, Digital Transformation (DX) is a major focus with expanded DevOpsSUMMIT and FinTechEXPO programs within the DXWorldEXPO agenda. Successful transformation requires a laser focus on being data-driven and on using all the tools available that enable transformation if they plan to survive over the long term. A total of 88% of Fortune 500 companies from a generation ago are now out of business. Only 12% still survive. Similar percentages are found throug...
    Druva is the global leader in Cloud Data Protection and Management, delivering the industry's first data management-as-a-service solution that aggregates data from endpoints, servers and cloud applications and leverages the public cloud to offer a single pane of glass to enable data protection, governance and intelligence-dramatically increasing the availability and visibility of business critical information, while reducing the risk, cost and complexity of managing and protecting it. Druva's...
    BMC has unmatched experience in IT management, supporting 92 of the Forbes Global 100, and earning recognition as an ITSM Gartner Magic Quadrant Leader for five years running. Our solutions offer speed, agility, and efficiency to tackle business challenges in the areas of service management, automation, operations, and the mainframe.