Spreadsheet Error Detection via Abstract Syntax Tree Pattern Analysis
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing spreadsheet analysis tools are limited in detecting errors in formulae, particularly in identifying various types of anomalous formulae and interrelationships within spreadsheets, leading to difficulties in understanding and validating spreadsheet content.
Innovation Solution
A system and method that convert spreadsheet formulae into Abstract Syntax Trees (ASTs), encode their structure as patterns, and compare these patterns to identify anomalous formulae, employing a structural pattern-based approach for error detection and classification.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If conventional spreadsheet analysis tools are used, then basic formula validation is possible, but detection of anomalous formulae and interrelationships is limited
Solution Approach 1:
The patent segments spreadsheet formulae into Abstract Syntax Trees (ASTs), breaking down each formula into its structural components. This segmentation enables detailed analysis of formula structure and facilitates the detection of anomalous patterns that conventional tools miss.
Solution Approach 2:
The patent introduces an intermediary representation layer between the raw spreadsheet formulae and the analysis process. By converting formulae into ASTs and then into encoded patterns, the system creates a mediating structure that enables sophisticated comparison and anomaly detection while maintaining adaptability to different formula types.
2Ease of operation
If spreadsheet data is analyzed in semi-structured cell-based format, then data representation is maintained, but structural analysis and error detection are difficult
Solution Approach 1:
The patent creates a copied and transformed version of the spreadsheet data in the form of ASTs and encoded patterns. This copy maintains the essential structural information needed for error detection while presenting it in a format that is much easier to analyze systematically, thereby reducing the difficulty of detecting formula errors.
Solution Approach 2:
The patent changes the parameter representation of spreadsheet formulae by converting them from semi-structured cell-based text into structured AST representations with defined nodes and edges. This parameter transformation enables systematic structural analysis and simplifies the detection of anomalies.
3Reliability
If pattern encoding and comparison is implemented, then anomalous formulae can be identified, but system complexity increases
Solution Approach 1:
The patent extracts the essential structural patterns from spreadsheet formulae by converting them into ASTs and then into encoded pattern representations. This extraction separates the critical structural information from the complex semi-structured data, enabling reliable anomaly detection through pattern comparison while managing system complexity.
Data Source
AI summary
Proposed are concepts of transforming a spreadsheet (or set of spreadsheets) into a knowledge base for analysis and error detection. Such a concept may convert a spreadsheet into an abstracted model that facilitates error detection. For instance, by turning the semi-structured cell-based data representation into a structured abstract model, it may be possible to perform analysis and/or error detection that would otherwise not be possible with the existing cell-based tools.