Spreadsheet Entity Shift Tracking for Data Auditing
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Spreadsheets lack effective mechanisms for tracking changes, making it difficult to manage and audit data due to their flexible and interconnected nature, which hinders quality control and creates obstacles in creating auditable business activities.
Innovation Solution
A system that includes an entity selector, tracker, offset determinator, and offset applicator to monitor and audit spreadsheet data by tracking entity shifts, removing noise from insertions, deletions, and sorting actions, allowing for cell-by-cell comparison of spreadsheet versions.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Ease of operation
If spreadsheets allow easy modification and flexibility, then ease of operation is improved, but reliability and traceability of data deteriorate
Solution Approach 1:
The system performs preliminary actions by automatically capturing snapshots of spreadsheet states at defined time points before modifications occur. This allows the system to record the evolution of data without interfering with user operations, maintaining both flexibility and traceability
Solution Approach 2:
The invention introduces an intermediary monitoring system that sits between the user and the spreadsheet data. This intermediary captures changes, records time-stamped snapshots, and enables traceability without restricting user access or modification capabilities, thus preserving ease of operation while improving reliability
2Adaptability or versatility
If spreadsheets allow interconnected cells with complex formulas, then functionality is improved, but difficulty of detecting and measuring changes worsens
Solution Approach 1:
The system segments the complex spreadsheet into individual cells and tracks changes at the cell level. By breaking down the interconnected structure into discrete trackable units, the system can monitor modifications, insertions, and deletions independently, simplifying the detection of changes even in highly functional spreadsheets with complex formulas
3Adaptability or versatility
If spreadsheets are used instead of controlled databases, then adaptability is improved, but manufacturing precision of data management deteriorates
Solution Approach 1:
The system implements feedback mechanisms by continuously monitoring spreadsheet changes and recording them in a structured manner. This feedback loop provides quality control capabilities similar to controlled databases, allowing verification and auditing of data management processes while maintaining the adaptability of spreadsheet formats
Data Source
Figure 1
Figure 2
Figure 3
AI summary
A system is provided for processing and storing spreadsheets. The system comprises a determining unit arranged to determine each unique formula occurring within a spreadsheet, a first store for storing each unique formula, a second store for storing a unique identification for each unique formula, and a replacing unit arranged to replace each formula occurring within the spreadsheet with its corresponding formula identification. More efficient storage of formulas is thus achieved. In a further aspect, a system is provided comprising an entity selector for selecting one or more entities in a spreadsheet, and a tracker for tracking the shift in position of the selected entities between two time points. An offset determinator is arranged to derive one or more offset values from information received from the tracker, the offset values representing the shift in position of the entities in the spreadsheet. An offset applicator applies the offset values to the spreadsheet data before comparing the spreadsheet data with a version of the spreadsheet data at a different time point. Shifting of entities resulting from insertion or deletion of rows and columns or sorting are thus taken into account when comparing changes made to spreadsheets between the two time points.