DATA TRACKING SYSTEM

The present invention relates to a computer, a database, a carrier medium, a signal, and a method of tracking changes to document data. In particular the present invention relates to software applications for a computer that allow a user to create documents and to modify document objects with the documents.

In a spreadsheet, for example (such as Microsoft(RTM) Excel(RTM)), the user can manipulate document objects such as cells and worksheets, containing the cells. Other document objects such as workbooks, charts, points, ranges and so on exist within a defined hierarchy of document objects. Each of the document objects can be associated with other document objects in various ways, and some of the documents can have data values associated with them. For example, the user can enter data values such as constants or formulae (expressions) into cells (document objects) of the spreadsheet. A formula in a cell may, for example, reference other cells in the spreadsheet (such a formula may be of the form '=SUM(A1:A4)' ₅ for example). Thus a cell document object may have a 'child' association with a worksheet object (because the worksheet 'contains' or 'owns' the cell), a 'dependent' association with a range of cells ('A1:A4' in this case), and possibly also one or more 'precedent' associations with cells that in turn refer to the cell in question. Equivalent data structures can be defined in other applications, such as word processing and graphical design applications, for example.

Documents such as spreadsheets are often used to record sensitive and/or important data, for example relating to financial transactions. It is therefore often desirable to maintain a history (or 'audit trail') of changes to a document.

Microsoft(RTM) Excel(RTM) incorporates a 'track changes' feature that can provide a history/audit trail of changes to a spreadsheet. The track changes feature is described, for example, in the Microsoft(RTM) Excel(RTM) Help documentation, the contents of which are herein incorporated by reference. When the 'track changes' feature is enabled, any time that a cell is modified an entry is made in an audit log that identifies

the modified cell, the new (modified) contents, and the user who made the relevant change. The audit log so generated can then be used to trace the source of errors and any other irregularities that may arise.

However, the 'track changes' feature mentioned above was found to have some drawbacks. The feature essentially tracks changes to only limited types of document objects (for example, the spreadsheet cell), and has to prevent modifications to other document object types (such as charts and pivot tables) in order to provide a meaningful audit history. More importantly, whenever the contents of a cell is cut and pasted to another cell, for example, portions of the audit log may be overwritten and ambiguities may arise.

The present invention takes into account drawbacks in this and other prior art.

Accordingly, in a first aspect of the invention there is provided a method of tracking changes to document data, the document data including a plurality of document objects and a plurality of data values, and the method comprising: generating a set of data objects corresponding to respective data values and respective document objects; receiving an input indicating that at least one document object has been modified; selecting data objects corresponding to at least one modified document object and a number (which may be zero) of modified data values; and modifying the selected data objects in accordance with the modifications to the corresponding data values or document objects.

The document objects may be spreadsheet objects such as cells, ranges, names, pivot tables, charts, chart series, rows, columns, worksheets, and so on, or they may be other objects such as blocks of text in word processing documents, or diagrams and pictures in graphical documents, for example. The data values associated with the document objects may be numbers, text, Boolean values, formulae and so on (in the case of a spreadsheet). The data objects may be records in a database (such as a document worksheet, relational database, simple table, and so on), which may comprise a plurality of fields, and which may be stored on a storage medium. The input may be an event generated by an application, such as an event sent to an add-in application, and may (or

may not) contain a reference to the modified document object(s). It will be appreciated that the method can be adapted in cases where less than a plurality either of document objects or of data values are envisaged, and similarly elsewhere in this specification references to pluralities can, where appropriate, be replaced by reference to single or zero numbers of items.

By generating a set of data objects corresponding to respective data values, and then modifying the selected data objects in accordance with modifications made to the corresponding data values or document objects, an audit trail can be generated that tracks changes to the underlying data values in addition to (or instead of) tracking changes to the document objects. Thus when a particular expression or constant is moved around the spreadsheet (by cutting and pasting or similar, for example), the history of the changes can be more clearly determined, and it may be more easily observed that, despite the changes to document objects, the data value itself and/or its relationships with other data values may not have changed. Conversely, it may be more easily observed that, whilst document objects may not have changed, relationships between corresponding data values may have changed. Furthermore, since the data values (that is, the content of the spreadsheet) are usually of greater significance than the document objects (that is, the location of the content within the spreadsheet), a more efficient and more readily useable auditing system can be provided for applications that include, for example, automating real-time risk analysis.

The method may allow audit data to be saved (in a text or data file, for example), and the normal 'undo' function of the document application may be overridden to allow the undo function to be performed using this audit data (accordingly the undo function may be used without limitation, allowing every change to be undone). A further undo mode may be provided (a 'non-linear' undo), allowing the user to select certain document object(s) and/or data value(s) in a document, and to undo all or (at the user's selection, for example) part of the changes relating to those document object(s) and/or data value(s), irrespective of the order in which the relevant changes were made. In this case, the audit data may be used to ensure that relationships are respected by the undo operation (and, if appropriate, corresponding changes are made outside the selected

region in order to ensure consistency). The method may also allow analysis of the audit data, and the tracking of which user made what changes, when and how.

The method can also track and record changes in relationships between document objects and data within the document objects. In particular, the method can track hierarchical ('parent-child') relationships, 'precedence-dependence' relationships and 'association' relationships between data values. This can allow a more complete audit record to be provided, and can again provide an improved user interface for audit operations.

In particular, the invention provides a method of tracking changes to document data, the document data including a plurality of document objects, and the method comprising: receiving an input indicating that at least one of the document objects has been modified; and recording any changes to relationships between data values arising as a result of the modification of the document objects.

The method may be embodied in at least one software component which may be executed as an 'add-in' program or script. For example, the method may be provided by a 'COM add-in' to Microsoft(RTM) Excel(RTM). Alternatively the method may be incorporated as an integral feature of such an application.

The invention also provides a validation function, for example in the form of a method comprising determining whether a modification to a document object or data value complies with predetermined criteria and outputting data representing the result of the determination. Changes to the values and relationships of a wide variety of document objects and data values may be validated, rather than merely validating a cell's content, for example. Audit and other data relating to document objects and data values may be processed, for example using appropriate heuristics, automatically to determine new validation criteria. In response to validation failure alerts, the user can override the validation and supply new validation criteria at that point if required. The validation may occur in parallel to 'built-in' validation routines, allowing more control of many components of a document without overwriting pre-existing validation within a

document (although the 'in-built' validation, where it exists, may be overwritten by the parallel validation criteria if and when appropriate).

In another aspect of the invention there is provided a method of tracking changes to a spreadsheet containing a plurality of document objects, comprising: monitoring changes to the document objects; and recording changes to data values associated with the document objects, whereby a history of each data value can be determined from the recorded changes, irrespective of any changes to the association of data values with the document objects.

In a further aspect of the invention there is provided a method of tracking changes to a document containing a plurality of document objects, the plurality of document objects being associated with a respective plurality of data values, and the method comprising: recording changes to associations between the document objects; and recording changes to associations between the data values.

Yet further aspects of the invention are provided in accordance with the attached independent claims, and further optional features are defined by the attached dependent claims. Yet further aspects of the invention may be provided in accordance with the features described below. Apparatus equivalents of the method claims may also be provided.

The present invention can be implemented in any convenient form, for example using dedicated hardware, or a mixture of dedicated hardware and software. The present invention is particularly suited to implementation as computer software implemented by a workstation or laptop computer. Aspects of the present invention encompass computer software implementable on a programmable device. The computer software can be provided to the programmable device using any conventional carrier medium. The carrier medium can comprise a transient carrier medium such as an electrical, optical, microwave, acoustic or radio frequency signal carrying the computer code. An example of such a transient medium is a TCP/IP signal carrying computer code over an IP network, such as the Internet. The carrier medium can also comprise a storage

medium for storing processor readable code such as a floppy disk, hard disk, CD ROM, magnetic tape device or solid state memory device.

Although each aspect and various features of the present invention have been defined independently hereinabove and with regard to the appended claims, it will be appreciated that, where appropriate, each aspect can be used in any combination with any other aspect(s) or features of the invention. It will also be appreciated that further features of the invention may be provided by appropriate combination of features described below, optionally in combination with existing claims.

Embodiments of the present invention will now be described with reference to the accompanying drawings, in which:

Figure 1 is an illustration of expressions and constants contained in a Microsoft(RTM) Excel(RTM) Workbook;

Figure 2 is an illustration of the values of the constants and expressions of the Workbook of Figure 1;

Figure 3 is an illustration of the expressions and constants of the Workbook of Figure 1 after the contents of cell Bl are moved to cell Cl;

Figure 4 is a chart illustrating the parent-child relationships of certain document object types in a Microsoft(RTM) Excel (RTM) spreadsheet;

Figure 5 is a chart of the hierarchy of objects in the Workbook of Figure 1;

Figure 6 is an example of an audit trail generated by a comparison of the document objects in the Workbooks of Figure 1 and Figure 3;

Figure 7 is an example audit trail of the Workbook of Figure 1;

Figure 8 is an example audit trail of the Workbook of Figure 1 after the contents of cell Bl are moved to cell Cl;

Figure 9 is a diagram illustrating the precedents and dependents of range objects in the Workbook of Figure 1;

Figure 10 is a diagram illustrating the precedents and dependents of range objects in the Workbook of Figure 1 after the contents of cell Bl are moved to cell Cl;

Figure 11 is an example audit trail of the Workbook of Figure 1 after a worksheet- specific Name "Start" has been defined;

Figure 12 is an illustration of the values of the constants and expressions of the Workbook of Figure 1 after a worksheet-specific Name "Start" has been defined;

Figure 13 is an illustration of a parent-child hierarchy of data objects and examples of data objects' associations with document objects in the Workbook of Figure 1;

Figure 14 is an illustration of a parent-child hierarchy of data objects and examples of data objects' associations with document objects in the Workbook of Figure 1 after the contents of cell Bl are moved to cell Cl;

Figure 15 is a diagram illustrating the precedents and dependents of the auditing system's data objects corresponding to document objects in the Workbook of Figure 1;

Figure 16 is a diagram illustrating the precedents and dependents of the auditing system's data objects corresponding to document objects in the Workbook of Figure 1 after the contents of cell Bl are moved to cell Cl;

Figure 17 is a diagram illustrating the precedents and dependents of the auditing system's data objects corresponding to document objects in the Workbook of Figure 1 after a worksheet-specific Name "Start" has been defined;

Figure 18 is a diagram of the database structure used by the auditing system of a preferred embodiment;

Figure 19 is a snapshot of a table of data objects relating to the Workbook of Figure 1;

Figure 20 is a snapshot of a table of data objects relating to the Workbook of Figure 1 after the contents of cell Bl are moved to cell Cl ;

Figure 21 is a snapshot of a table of actions relating to the Workbook of Figure 1 after the contents of cell Bl are moved to cell Cl;

Figure 22 is a snapshot of a table of effects relating to the Workbook of Figure 1 after the contents of cell Bl are moved to cell Cl;

Figure 23 is a snapshot of a table of actions relating to the Workbook of Figure 1 before the contents of cell Bl are moved to cell Cl;

Figure 24 is a snapshot of a table of effects relating to the Workbook of Figure 1 before the contents of cell Bl are moved to cell Cl;

Figure 25 is an illustration of "association" relationships between data objects relating to the Workbook of Figure 1;

Figure 26 is an illustration of "association" relationships between data objects relating to the Workbook of Figure 1 after the contents of cell B 1 are moved to cell C 1 ;

Figure 27 is an overview of a method carried out in the preferred embodiment;

Figure 28 is a flowchart of a process for identifying and selecting relevant document changes;

Figure 29 is a flowchart of a process for amending the "Actions" table and for identifying and selecting data objects that are impacted by a document change;

Figure 30 is a flowchart of a process for identifying and selecting changes to data objects impacted by a document change;

Figure 31 is a flowchart of a process for amending the "Objects" and "Effects" tables; and

Figure 32 is a schematic showing the structure of the auditing system's main components.

A method and system for tracking and auditing changes to data is described below with reference to the Microsoft(RTM) Excel(RTM) spreadsheet application. Firstly, by way of background, the way in which the Microsoft(RTM) Excel(RTM) spreadsheet application manipulates data will be described. A number of spreadsheet auditing methods will then be described in order to illustrate various issues relating to spreadsheet auditing, and a preferred auditing method will then be described.

A Microsoft(RTM) Excel(RTM) user can enter data items, including inputs to calculations and calculations' parameters, via "constants" and "expressions". "Values resulting from expressions" represent further data items that include outputs of calculations. Microsoft(RTM) Excel(RTM) differentiates constants from expressions and their resulting values by their behaviour on calculation; on calculation, constants are unchanged whereas values resulting from expressions may change. Examples of constants are '3' and '"Tuesday"', for example. Examples of expressions are ^ς =Now()' and '=A3+A4', for example. Hereafter, constants, expressions and values resulting from expressions are referred in their entirety as "data values".

A spreadsheet's structure is defined by its document objects and their relationships with one another, including both parent-child relationships and precedence-dependence relationships. Microsoft(RTM) Excel(RTM) Workbooks' document objects, and collections thereof, are defined by the Microsoft(RTM) Excel(RTM) Object Model. Constants and expressions can be contained in a variety of Microsoft(RTM) Excel(RTM) document objects that include, for example: Range objects and Name

objects. The structure of the Microsoft(RTM) Excel(RTM) Object Model is described, for example, in the Microsoft(RTM) Excel(RTM) Help documentation, the content of which is herein incorporated by reference.

The present auditing system records changes to Microsoft(RTM) Excel(RTM) document objects. In addition, the auditing system further records changes to data values and explicitly records detail of changes to data values' precedence-dependence relationships, as distinct from the detail of expressions and any changes thereto, for example.

The environment in which the auditing system operates will now be described, in particular to illustrate the distinction between document objects and the data values contained therein and, further, to illustrate the transient nature of associations between data values and document objects.

Figure 1 is an illustration of the expressions and constants contained in a MicrosoftζRTM) Excel(RTM) Workbook. Workbook "Bookl" contains the Name "Start", which has been defined as being equal to 1. Worksheet "Sheetl" of Workbook "Bookl" contains cells Al, Bl ₅ Cl, Dl, El, A2 and B2. Cells Cl and Dl contain constants 5, 6 with values of 3 and 4 respectively. Cells Al, Bl, El ₅ A2 and B2 contain expressions 3, 4, 7, 8, 9 respectively: "=Start", "=2+Al", "=SUM(A1:D1)", "=SUM(A1 :B 1)" and "-B 1 ".

Figure 2 is an illustration of the values of the constants and expressions of the Workbook of Figure 1. In Figure 2, values 3 ₅ 4, 7, 8, 9 resulting from the expressions in cells Al ₅ Bl, El ₅ A2 and B2 are, respectively: I ₅ 3, 11, 4 and 3. Worksheet "Sheetl" further contains a Chart 10, titled "My Chart", that contains Series "Seriesl" 11, with the expression "=SERIES(,{1999,2000 ₅ 2001,2002} ₅ Sheetl !$A$1: $D$1,1)", which plots the values of cells Al, Bl ₅ Cl and Dl.

Figure 3 is an illustration of the expressions and constants of the Workbook of Figure 1 after one of the cells has been moved. The Workbook is shown after the user "cuts and pastes" or "drags and drops" cell Bl to cell Cl. Following the change, cell Bl contains

a null data value 13 and cell Cl contains the expression 14 "=2+Al". Also shown is the constant 15 in cell Dl and the expressions 12, 16, 17, 18 in cells Al, El, A2, B2.

Various methods of tracking the change to the Workbook will now be described, with particular reference to the hierarchy of the Workbook.

Figure 4 is a chart illustrating the parent-child relationships of certain document object types in a Microsoft(RTM) Excel (RTM) spreadsheet. With reference to Figure 4, parent-child relationships between a Workbook's document objects are defined by the respective document objects' "Parent" properties. For example: a Worksheet 20 object's Parent property returns a Workbook 19 object; a Chart object's Parent property returns either a ChartObject 21 object (in the case of an embedded chart 22) or a Workbook 19 object (in the case of a chart sheet 25); a Range 23 object's Parent property returns a Worksheet 20 object; a Name object's Parent property returns either a Worksheet 20 object (in the case of a worksheet-specific name 24) or a Workbook 19 object; and so on.

Figure 5 is a chart of the hierarchy of objects in the Workbook of Figure 1. The initial hierarchy of document objects includes: Worksheet "Sheetl" 27, Range "Al" 28, Range "Bl" 29, Range "Cl" 30, Range "Dl" 31, Range "El" 32, Range "A2" 35, Range "B2" 36, ChartObject "Chartl" 39, Chart "Sheetl Chartl" 40, (unnamed) ChartGroup 41, Series "Seriesl" 42, (unnamed) Points 43-46 and Name "Start" 47. Workbook "Bookl" 26 is the parent of both Worksheet "Sheetl" 27 and Name "Start" 47. Worksheet "Sheetl" 27 is the parent of ChartObject "Chartl" 39 and all Range objects ("Al" 28, "Bl" 29, "Cl" 30, "Dl" 31, "El" 32, "Fl" 33 - "IVl" 34, "A2" 35, "B2" 36 and "C2" 37 - "IV65536" 38). ChartObject "Chartl" 39 is the parent of Chart "Sheetl Chartl" 40 which is, in turn, the parent of the (unnamed) ChartGroup object 41. The (unnamed) ChartGroup object 41 is the parent of Series "Seriesl" 42 which is, in turn, the parent of all (unnamed) Point objects 43-46.

When the change is made to Workbook "Bookl" as described above (moving the content of cell Bl to cell Cl), the parent-child hierarchy of document objects in

"Bookl" is unchanged, since Ranges "Bl" 29 and "Cl" 30 remain (with parent Worksheet "Sheetl" 27).

Figure 6 is an example of an audit trail generated by a comparison of the document objects in the Workbooks of Figure 1 and Figure 3. The audit trail might record, for example, that Range "Bl" has changed from the expression "=2+Al" to become a null value 50 and that Range "Cl" has changed from a constant, with value 3, to contain the expression 51 "=2+Al".

However the essence of the change includes the movement of the expression "=2+Al" (and its resulting value), from cell Bl to cell Cl ₅ rather than the changes to each of Ranges "Bl" and "Cl" referred above. Data values are distinct from document objects because their association with document objects may be transient. For example, whereas the expression "=2+Al" was associated with Range "Bl" before the change, it is associated with Range "Cl" after the change.

Such an audit trail delivers a continuous history of changes for each of a spreadsheet's document objects. For example, referring to Figure 6, a history of changes for Range "Bl" is provided in the audit trail's records 48, 50 corresponding to Action Numbers 3 and 10 and a history of changes for Range "Cl" is provided in the audit trail's records 49, 51 corresponding to Action Numbers 4 and 11. However, it does not deliver a continuous history of changes for each of a spreadsheet's data values. For example, it is unclear from the audit trail whether the expression "=2+Al" 14 (and its resulting value) in cell Cl of Figure 3 represents the same data item as the expression "=2+Al" 4 in cell Bl of Figure 1. With reference to Figure 6, the same audit trail might result from the user deleting the expression "=2+Al" in cell Bl 50 and entering anew the expression "=2+Al" in cell Cl 51 which, by implication, may represent a different data item.

A method of auditing changes to a spreadsheet is to track the changes not in real-time but for example by comparing successive file versions of the spreadsheet (for example by carrying out a daily scan of spreadsheet files to identify changes with reference to corresponding file versions backed up on the previous day). This method is limited to either similarly tracking changes to document objects or, due to the transient nature of

associations between document objects and data values, to uncertain interpretation of changes made to spreadsheets' data values. In either event, the resulting audit trails may not be readily readable and may find limited applications.

Another approach is instead to prohibit such changes - for example, limiting changes to static document objects - or to record changes in terms of the essence of the change to the spreadsheets' document objects, such as "moved Range Bl to Range Cl". The former benefits from continuity of associations between a spreadsheet's document objects and its data values, and consequently produces more practicable audit trails. However, it can restrict the flexibility of a spreadsheets.

An audit trail created using the latter approach, of recording a change to a spreadsheet in terms changes to the spreadsheets' document objects, will now be described with reference to Figures 7 and 8.

Figure 7 is an example audit trail of the Workbook of Figure 1 , shown before the contents of cell Bl is moved to cell Cl.

Figure 8 is the example audit trail of the Workbook as shown in Figure 3, shown after the contents of cell Bl are moved to cell Cl. Here it is clear the expression "=2+Al" in cell Cl represents the same data item as the expression "=2+Al" formerly in cell Bl. The "Range" reference in action number 3 has changed to "Cl" from "Bl" 169. Accordingly, the change history in Figure 8 effectively presents changes to data values. However, further issues arise relating not only to the transience of associations between document objects and data values but also to precedence-dependence relationships between spreadsheets' document objects and data values contained therein.

As referred earlier, a spreadsheet's structure is defined not only by its document objects' parent-child relationships but also by its document objects' precedence-dependence relationships. Range objects' precedence-dependence relationships are defined by their "DirectPrecedents" and "DirectDependents" properties. A Range object's DirectPrecedents property returns a Range object that represents direct precedents of the Range object i.e. other Range objects that are referred in the Range object's formula.

Similarly, the DirectDependents property returns a Range object that represents direct dependents of the Range object i.e. other Range objects whose formulae refer to the Range object.

Figure 9 is a diagram illustrating the precedents and dependents of range objects in the Workbook of Figure 1, before the change is made to the Workbook. The DirectPrecedents property of Range "Bl" 29 returns Range "Al" 28, since the expression in cell Bl refers to cell Al. Conversely, the DirectDependents property of Range "Al" 28 returns Range "B1,E1,A2", representing Ranges "Bl" 29, "El" 32 and "A2" 35, because the expressions in cells Bl, El and A2 all refer to cell Al. The DirectDependents property of Range "Bl" 29 returns Range "E1,A2:B2", representing Ranges "El" 32, "A2" 35 and "B2" 36, because the expressions in cells El, A2 and B2 all refer to cell Bl.

Figure 10 is a diagram illustrating the precedents and dependents of range objects in the Workbook of Figure 3, after the change is made to the Workbook. Range "Cl" 30 has moved to the location previously occupied by Range "Bl", reflecting the movement of cell Bl to cell Cl. After the change to Worksheet "Sheetl" in Workbook "Bookl", the DirectPrecedents property of Range "Cl" 30 returns Range "Al" 28 and the DirectDependents property of Range "Cl" 30 returns Range "E1,A2:B2". Thus Range "Cl" 30 has assumed the precedence-dependence relationships with Ranges "Al" 28, "A2" 35 and "B2" 36 that formerly had precedence-dependence relationships with Range "Bl", whilst maintaining its precedence-dependence relationship with Range "El". In particular, Range "Cl" 30 not only contains the expression that was formerly in Range "Bl" 29 but has also assumed the corresponding precedence-dependence relationship with Range "Al" 28.

As a result of the movement of cell Bl to cell Cl, other expressions and precedence- dependence relationships have changed. For example, the expression in cell B2 has changed from "=B1" to "=C1", and the former precedence-dependence relationship between Range "B2" and Range "Bl" has been replaced by a precedence-dependence relationship between Range "B2" and Range "Cl".

In the audit trail shown in Figures 7 and 8, detail of precedence-dependence relationships is included within expression data in the change history. For example, the expression "=B1" in action number 9 of Figure 7 denotes that Range "B2" is dependent upon Range "Bl". Referring to Figure 10, after "dragging and dropping" or "cutting and pasting" cell Bl to cell Cl, Range "B2" 36 is no longer dependent on Range "Bl" 29 but is now dependent on Range "Cl" 30. The change tracking method illustrated in Figures 7 and 8 maintains detail of precedence-dependence relationships, throughout document changes, by effectively making retrospective modifications to the change history. For example, detail of this precedence-dependence relationship is maintained in the change history as a result of the modification of the expression in action number 9 from "=Bl" to "=Cl".

Such retrospective modification of audit trail data overwrites data pertaining to prior data values, including the precedence-dependence data implicit therein, and consequently the resulting audit trail does not detail changes in precedence-dependence relationships. A change to a document object can constitute a change to data values or precedent-dependent relationships or both. For example, with regard to Range "B2", "dragging and dropping" or "cutting and pasting" cell Bl to cell Cl constitutes a change to both its precedence-dependence relationships and the data value in cell B2. This is true not only of moving document objects but also of changes to data values contained in document objects. For example, if the data value in cell Bl had initially been entered as "=1+A1" and then changed to "=2+Al", the change would have constituted only a change to the data value without any change to precedence-dependence relationships, whereas if the data value had instead been changed to "=1+A2" then the change would have constituted a change to both the data value and the precedence-dependence relationships of Range "Bl".

Changes to precedence-dependence relationships, as distinct from changes to data values, are relevant to a change-analysis audit trail and applications thereof. However, referring to Figure 8, action number 10 refers to only Ranges "Bl" and "Cl"; it omits to record that the movement of cell Bl to cell Cl relates also to changes to other document objects' data values and/or precedence-dependence relationships and, further, it is unclear which other document objects' data values and/or precedence-dependence

04910

16 relationships changed. For example, after "dragging and dropping" or "cutting and pasting" cell Bl to cell Cl ₅ it is unclear from the change history in Figure 8 whether the data value "=SUM(A1:C1)" referred in action number 8 (regarding Range "A2") was previously "=SUM(A1:B1)" (in which event its data value and precedence-dependence relationships have changed similar to Range "B2" above) or was instead previously "=SUM(A1:C1)" (in which event its data value and precedence-dependence relationships did not change) since the same change history results from both scenarios.

Indeed, the data contained in action number 10 relates to a data value that has changed neither expression ("=2+Al"), nor resulting value (3) nor related precedence- dependence relationships (with Ranges "Al" 28, "El" 32, "A2" 35 and "B2" 36). The details of changes to data values and precedence-dependence relationships, which can provide greater insight to the effects of changes and thus finds more extensive applications, is excluded as a result of retrospective changes to the change history as described above.

Changes to precedence-dependence relationships can also occur in the absence of any corresponding changes to dependents' expressions, which can cause difficulties with the auditing methods described above.

One example of such a situation is if the user did not move cell Bl to cell Cl but instead defined a new (constant) worksheet-specific Name "Start" with value 10. This example will now briefly be described with reference to Figures 12 and 13.

Figure 11 is an example audit trail of the Workbook of Figure 1 after the worksheet- specific Name "Start" has been defined, and Figure 12 is an illustration of the values of the constants and expressions of the Workbook of Figure 1 after the worksheet-specific Name "Start" has been defined.

When the new (constant) worksheet-specific Name "Start" is defined as having value 10 then, the entry 53 in respect of the expression "=Start" in Range "Al" is unchanged from action number 2 before the change. But the value resulting from the expression "=Start" in cell Al has changed from 1 to 10, reflecting that the former relationship

between existing Name "Start" referred in action number 1 and the Range "Al" referred in action number 2 has been replaced by a relationship between new worksheet-specific Name "Start" referred in action number 10 and the Range "Al" referred in action number 2.

Returning to the prior example of "dragging and dropping" or "cutting and pasting" cell Bl to cell Cl in Worksheet "Sheetl" of Workbook "Bookl", the data value "=SUM(A1:D1") in cell El is unchanged and the precedence-dependence relationships between Range "El" and Ranges "Al", "Bl", "Cl" and "Dl" are also unchanged. The value resulting from the expression in cell El has changed from 11 to 8 as a result of the changes to the data values in Ranges "Bl" and "Cl", but this change would not be recorded in the audit trail created using the above-described auditing method.

But viewed instead in terms of the changes made to the data Values contained in Worksheet "Sheetl" of Workbook "Bookl", the value resulting from the expression in Range "El" has changed as a result of its changing precedence-dependence relationships with other data values. In "dragging and dropping" or "cutting and pasting" cell Bl to cell Cl, the essence of the change made to Worksheet "Sheetl" in Workbook "Bookl" was threefold: firstly, referring to Figure I ₅ deletion of the constant 5, with value 3, formerly in cell Cl; secondly, referring to Figure 5, association of the expression "=2+Al" with Range "Cl" 30, replacing its former association with Range "Bl" 29; and thirdly, referring to Figure 3, implicit creation of a new (null) data value 13 in cell Bl. Thus, after "dragging and dropping" or "cutting and pasting" cell Bl to cell Cl, whilst the precedent document objects for Range "El" are unchanged as described above, its precedent data values no longer include the constant (with value 3) and additionally include the new data value (with null value).

A preferred embodiment of an auditing system and method for auditing and tracking changes to data, which takes into account the issues mentioned above, will now be described.

In the preferred embodiment, "data objects" are defined so as to broadly coincide with data values, as defined earlier, including constants and expressions. The uses of the data objects include:

• tracking changes of associations between document objects and data values

• tracking changes to data values contained within expressions and document objects' names; and

• separating the detail of data values' relationships, and records of changes thereto, from the detail of expressions.

The auditing system's data objects in relation to a given Microsoft(RTM) Excel(RTM) Workbook, and their parent-child and precedence-dependence relationships, differ from the Workbook's document objects and data values, and their parent-child and precedence-dependence relationships, in a number of ways.

Some Microsoft(RTM) Excel(RTM) document objects cannot be associated with data values (they can contain neither constants nor expressions) and hence these objects are similarly not associated with any data objects. Such document objects act only as "containers" for other document objects (which may, in turn, contain data values) and include ChartObjects and ChartGroups. For example, referring to Figure 4, a ChartObject object 21 cannot contain any data values (it can contain only a Chart object 22 and related formatting data).

The parent-child hierarchy of document objects in a Microsoft(RTM) Excel(RTM) Workbook may include document objects that, whilst they are capable of containing data values, currently contain no data values (for example, in the case of Range objects, they are blank) and, further, have no precedence-dependence relationships with other document objects (they are not referred in any expression). Such document objects are not associated with data objects. For example, referring to Figure 5, Worksheet "Sheetl" of Workbook "Bookl" contains single-cell Ranges "Fl" 33, ..., IVl 34, "C2" 37, ..., "IV65536" 38 that do not contain data values and are not referred in any expressions. (It further contains multi-cell Range objects that similarly contain no data values and include, for example, Ranges "F1:G1", "C2:IV2", "A3:IV65536".)

Elements of an expression may represent data items entered in to a Workbook which may be considered to constitute constants since they are not calculated and do not change. For example, referring to Figure 1, the expression "=2+Al" 4 in cell Bl contains a "constant" with value 2; whereas the value resulting from the expression in cell Al may change and hence the value resulting from the expression "=2+Al" in cell Bl may also change, the value 2 is not calculated and does not change. Where expressions and constants so coexist in a single document object, the Microsoft(RTM) Excel(RTM) Object Model terminates with the document object. In contrast, the "constant", with value 2, corresponds to a data object that is distinct from the data object(s) corresponding to the expression and its resulting value.

User data is often entered in to document objects' "Name" etc properties. Their contents may similarly be considered to constitute data values and, accordingly, the auditing system's data objects include data objects corresponding to such data values. For example, referring to Figure 4, a Chart object 22 can be associated with a (constant) data value e.g. the chart's title as returned by the "Text" property of the ChartTitle object that is, in turn, returned by the Chart object's "ChartTitle" property.

Figure 13 is an illustration of a parent-child hierarchy of data objects and examples of data objects' associations with document objects in accordance with the preferred embodiment. Figure 13 illustrates the state of the data objects and document objects prior to the "dragging and dropping" or "cutting and pasting" cell Bl to cell Cl in the Workbook of Figure 1.

The auditing system contains 19 data objects, numbered 1-19, in relation to the Workbook "Bookl" of Figure 1. Titles are represented by data objects I ₉ 2, 14 and 15, which correspond to (text) constants "Bookl", "Sheetl", "My Chart" and "Seriesl" respectively. Data objects 16, 17, 18 and 19 correspond to constants that represent years 1999-2002. Data objects 3, 6, 7 and 8 represent inputs to Workbook "Bookl" and correspond to constants with respective numerical values of 1, 2, 3 and 4. Thus each constant in Workbook "Bookl" corresponds to an individual data object in the auditing system.

Data objects 4, 5, 9, 11 and 13 correspond to expressions and the values resulting from the same expressions. For example, referring to Figure 13, data object 5 corresponds to the expression "=2+Al" 4 in cell Bl of Figure 1 and the resulting value of 3 in the same cell. Data object 5 is the parent data object of data object 6, which corresponds to the constant, with value 2, in the expression "=2+Al" of Figure 1.

Parent-child relationships between data objects define the parent-child hierarchy of data objects in the audit trail for Workbook "Bookl". Data object 1 is the parent data object of data objects 2 and 3. Data object 2 is the parent data object of data objects 4, 5, 7, 8, 9, 10, 11, 12, 13 and 14. As described above, data object 5 is the parent data object of data object 6. Data object 14 is the parent data object of data object 15 which, in turn, is the parent data object of data objects 16, 17, 18 and 19.

Data objects can be considered to be a "step" removed from a Workbook's document objects. The associations of data objects with document objects are broadly coincident with the associations between related data values and document objects. For example: data objects 1, 2, 14 and 15 are respectively associated with Workbook "Bookl" 26, Worksheet "Sheetl" 27, Chart "Sheetl Chartl" 40 and Series "Seriesl" 42 respectively; data object 3 is associated with Name "Start"; data objects 4, 5, 7, 8, 9, 11 and 13 and respectively associated with Range objects "Al" 28, "Bl" 29, "Cl" 30, "Dl" 31, "El" 32, "A2" 35 and "B2" 36. Document objects that cannot contain data values, such as ChartObject "Chartl" 39, the (unnamed) ChartGroup object 41 and Point objects 43-46, are not associated with any data objects and empty document objects, such as Range "Fl" 33, are also not associated with any data objects.

As described earlier, associations between a Microsoft(RTM) Excel(RTM) Workbook's document objects and its data values are transient. Similarly, associations between the auditing system's data objects and a Workbook's document objects may change, and detail of changes thereto may be recorded to the system's audit trail. However, data objects consistently correspond to the same data values throughout changes to the Workbook, enabling the auditing system to deliver a continuous audit trail of changes to each data value.

Figure 14 is an illustration of a parent-child hierarchy of data objects and examples of data objects' associations with document objects in the Workbook of Figure 1 after the contents of cell Bl are moved to cell Cl .

Referring to Figure 14, after "dragging and dropping" or "cutting and pasting" cell Bl to cell Cl, data object 5 is no longer associated with Range "Bl" 29 but is now associated with Range "Cl" 30 (reflecting the movement of the expression "=2+Al" from cell Bl to cell Cl). All data objects persist with the exception of data object 7 (which corresponds to the former constant value 3 in cell Cl) that is now "dead" and no longer associated with any document object. New data object 20 is associated with Range "Bl" 29 and corresponds to the new (null) data value therein. Data object 5 corresponds to the expression "=2+Al" throughout the document change and thus may serve as a consistent point of reference for recording detail of changes to the same expression and its relationships with other data values.

As mentioned above, in addition to differences between data objects' parent-child relationships and those of a Workbook's document objects, data objects' precedence- dependence relationships also differ from document objects' precedence-dependence relationships. Precedence-dependence relationships between document objects are constrained in ways that include aforementioned limitations of the document object hierarchy. For example, referring to Figure 9, the Range object returned by the DirectPrecedents property of Range "Bl" 29 represents only Range "Al" 28 and does not represent the "constant", with value 2, that is contained within the expression "=2+Al" in Range "Bl" 29, which might equally be considered to constitute a precedent of the same expression.

Further, Range objects' DirectPrecedents and DirectDependents properties do not serve to define precedence-dependence relationships with other (non-Range) document objects that can contain data values. Such document objects include, for example, Names. For example, referring to Figure 9, whilst the expression "=Start" in Range "Al" 28 refers to Name "Start", the DirectPrecedents property of Range "Al" 28 does not represent Name "Start" (it represents no precedent document objects). Conversely,

Name objects do not have DirectPrecedents or DirectDependents properties. (Also, in the event that a Range object refers to cells on more than one Worksheet object, its DirectPrecedents property will return a Range object representing only a subset thereof i.e. cells referred on the "active" Worksheet object, since the property can not trace "remote" references.)

Figure 15 is a diagram illustrating the precedents and dependents of the auditing system's data objects corresponding to document objects in the Workbook of Figure 1.

Prior to "dragging and dropping" or "cutting and pasting" cell Bl to cell Cl, precedence-dependence relationships between the auditing system's data objects include the dependence of data object 5, which corresponds to the expression "=2+Al" in Range "Bl", upon data object 6, which corresponds to the "constant", with value 2, that is contained within the expression "=2+Al" in Range "Bl".

Further, precedence-dependence relationships between the auditing system's data objects reflect precedence-dependence relationships between all data values including, but not restricted to, data values contained in Range objects. For example, referring again to Figure 15: data object 4, which corresponds to the expression "=Start" in Range "Al", is dependent upon data object 3, which corresponds to the constant, with value 1, that is contained in Name "Start"; data object 15, which corresponds to Series "Series 1", is dependent upon data objects 4, 5, 7 and 8, which correspond to data values contained in Ranges "Al", "Bl", "Cl" and "Dl" respectively, and is also dependent upon data objects 16-19, which correspond to data values representing the years 1999— 2002.

Figure 16 is a diagram illustrating the precedents and dependents of the auditing system's data objects corresponding to document objects in the Workbook of Figure 1 after the contents of cell Bl are moved to cell Cl.

After "dragging and dropping" or "cutting and pasting" cell Bl to cell Cl, data object 9, which corresponds to the expression "=SUM(A1 :D1)" in Range "El", is no longer dependent upon data object 7, which corresponds to the constant (with value 3) that was

formerly contained in Range "Cl", but is now dependent upon data object 20, which corresponds to the new (null) data value in Range "Bl". Precedence-dependence relationships have similarly changed in relation to data object 15. All other existing precedence-dependence relationships between data objects are unchanged. Notably, for example, data object 9, which corresponds to the expression "=SUM(A1:D1)" in Range "El", remains dependent upon data object 5, which corresponds to the expression "=2+Al" that was formerly contained in Range "Bl" and is now contained in Range "Cl". Thus the relevant precedence-dependence relationships for data objects 9 and 15 have been maintained throughout the document change and, where precedence- dependence relationships between data objects have otherwise changed as a result of the document change, the changes are indicative of revisions to the underlying calculations that are programmed in Workbook "Bookl".

For example, referring again to Figure 16, whereas data object 11, which corresponds to the expression contained in Range "A2" that changed from "=SUM(A1:B1)" to "=SUM(A1:C1)", remains dependent upon data object 4, which corresponds to the expression "=Start" in Range "Al", and similarly upon data object 5, which corresponds to the expression "=2+Al" that was formerly contained in Range "Bl" and is now contained in Range "Cl", but it is now also dependent upon new data object 20, which corresponds to the (null) data value now in Range "Bl".

An alternative example, in which the user defines a new worksheet-specific Name "Start" with (constant) value 10, will now be considered.

Figure 17 is a diagram illustrating the precedents and dependents of the auditing system's data objects corresponding to document objects in the Workbook of Figure 1 after a worksheet-specific Name "Start" has been defined.

When the new worksheet-specific Name "Start" is defined, the data object 4, which corresponds to the expression "=Start" in Range "Al", ceases to be dependent upon data object 3, which corresponds to the constant (with value 1) that is contained in initial Name "Start", and is instead dependent upon new data object 20, which corresponds to the constant (with value 10) that is contained in worksheet-specific

Name "Start". Accordingly, the auditing system that refers changes to data objects and their interrelationships can detail not only the creation of data object 20 but also that of its precedence-dependence relationship with data object 4, in place of the former precedence-dependence relationship between data objects 3 and 4.

An implementation of the auditing system will now be described in more detail with reference to Figures 18 to 32.

Figure 18 is a diagram of the database structure used by the auditing system of the preferred embodiment.

Audit data is stored in a relational database containing three tables entitled: (Data) Objects 84, Actions 85 and Effects 86. In respect of a Microsoft(RTM) Excel(RTM) Workbook, the Objects table 84 details all data objects which, as described above, broadly correspond to data values 83 and may be associated with document objects 82. Each data object corresponds to a single record in the Objects table. The Objects table stores detail of associations between data objects and document objects, together with detail of parent-child and precedence-dependence relationships between data objects. The Objects, Actions and Effects tables might be stored in Worksheet objects.

Figure 19 is a snapshot of a table of objects relating to the Workbook of Figure 1.

Each populated row of Figure 19 corresponds to a record in the Objects table and relates to a distinct data object. For example: "Row 1" 95 relates to data object 1, "Row 2" 96 relates to data object 2, and so on. Each populated column of Figure 19 corresponds to a field in the Objects table, where:

"Column A" 87 contains a reference to each data object's parent data object and serves to define the data objects' parent-child hierarchy. For example, referring to cell A2 97, data object 1 is the parent of data object 2;

"Column B" 88 and "Column C" 89 define each data object's associated document object where applicable, as described below;

"Column D" 90 contains references to each data object's precedent data objects, where applicable, and thereby serves to define data objects' precedence-dependence

relationships. For example, referring to cell D4 100, data object 4 is dependent upon data object 3; and

"Column F" 91 denotes whether a data object is "live" (TRUE) or "dead" (FALSE).

"Column B" 88 identifies the type of associated document object where, for example: 1 denotes a Workbook object, 2 denotes a Worksheet object and 3 denotes a (single-cell) Range object. "Column C" 89 variously contains the contents of the associated document object's Name property (in the case of, for example, a Worksheet object), Address property (in the case of a Range object) or other relevant property. With reference to the type of document object associated with a given data object and the data object's parent data object (and data contained in parent data objects' records), the content of "Column C" 89 enables identification of the associated document object. For example, referring to cells B5 103 and C5 104, data object 5 is associated with Range "Bl" in, referring to cells A5 102, B2 98 and C2 99, Worksheet "Sheetl".

Figure 20 is a snapshot of a table of objects relating to the Workbook of Figure 1 after the contents of cell Bl are moved to cell Cl.

After the "dragging and dropping" or "cutting and pasting" cell Bl to cell Cl in Worksheet "Sheetl" of Workbook "Bookl", data object 5 is associated with Range "Cl" 104 whereas it was previously associated with Range "Bl" 104. Further, referring to cell F7 106, data object 7 is now "dead" and, referring to cells D9 107 and D15 110 therein, it is no longer a precedent of data objects 9 and 15. Instead, data object 20 has been added and is associated with Range "Bl" in Worksheet "Sheetl". Referring to cells D9 107, DIl 108 and D15 110, data objects 9, 11 and 15 are all now dependent upon data object 20.

Referring back to Figure 18, the Actions table 85 includes a record in respect of each relevant document change. It includes a record, or "action", in respect of each document change that resulted in one or more changes to the auditing system's data pertaining to at least one data object, as described below. Two fields therein contain a date and time corresponding to the document change.

Figure 21 is a snapshot of a table of actions relating to the Workbook of Figure 1 after the contents of cell Bl are moved to cell Cl.

In this table, "Row 12" 114 corresponds to action 12 which denotes that a relevant document change occurred on 14 November 2005 at 13 minutes and 25 seconds after midday.

Referring again to Figure 18, the Effects table 86 includes relational references to the Objects and Actions tables. Each record therein relates to precisely one record in the Objects table and to precisely one record in the Actions table. Thus each record, or "effect", relates to at least one change to a single object at a specified date and time.

Figure 22 is a snapshot of a table of effects relating to the Workbook of Figure 1 after the contents of cell Bl are moved to cell Cl.

In this table, for example, "Column A" 116 contains a reference to a record in the Actions table, i.e. an action, for each record in the Effects table. Similarly, "Column B" 117 contains a reference to a record in the Objects table, i.e. a data object, for each record in the Effects table. For example, "Row 3" corresponds to effect 3 which, referring to cell A3 123, relates to action 2 and, referring to cell B3 124, data object 3.

In addition to a data object's creation or deletion, effects might relate to changes to, for example: the data object's parent data object, its association with a document object, its corresponding data value and its precedence-dependence relationships with other data objects. An effect might relate to a data object that has so changed as a "direct" result of the corresponding document change including, for example, a change made by the user to the specified data object's associated document object. For example, referring to Figure 5, "dragging and dropping" or "cutting and pasting" cell Bl to cell Cl results in a direct change to the data object that corresponds to the expression "=2+Al" i.e. the change in its associated document object from Range "Bl" 29 to Range "Cl" 30. An effect might also, or instead, detail "indirect" changes to a data object that include, for example, changes to a data object that resulted from the application making changes to

its corresponding data value (in response to a document change that was made by the application's user). For example, referring to Figure 3 in comparison with Figure 1, "dragging and dropping" or "cutting and pasting" cell Bl to cell Cl results in an indirect change to the data object that corresponds to the expression in cell A2 i.e. from "=SUM(A1:B1)" 8 to "=SUM(A1:C1)" 17.

Referring back to Figure 22, a third field in the Effects table is contained in "Column C" 118 and denotes the type(s) of change(s) to the specified data object where, for example:

1 denotes (direct) creation of a data object;

2 denotes (direct) movement of a data object in the data objects' parent-child hierarchy or, on creation of a data object, establishment of its initial location in the parent-child hierarchy;

3 denotes (direct) change of a data object's associated document object (in the case of, for example, a Range object) or change to, say, the Name property of a data object's associated document object;

4 denotes (direct) change to the data value that corresponds to a data object and, where applicable, change(s) to a data object's relationships with other data objects, including precedence-dependence relationships;

5 denotes (direct) deletion of a data object;

6 denotes (indirect) creation of a data object and establishment of its initial location in the data objects' parent-child hierarchy;

7 denotes (indirect) change of a data object's associated document object (in the case of, for example, a Range object) or change to, say, the Name property of a data object's associated document object;

8 denotes (indirect) change to the expression that corresponds to a data object;

9 denotes change(s) to a data object's relationships with other data objects, including precedence-dependence relationships; and

0 denotes (indirect) deletion of a data object.

Combinations thereof denote multiple corresponding changes to the specified data object.

Referring again to Figure 18, the nature of the contents of other fields in the Effects table 86 might vary with the type of change made, as described above, and also, where applicable, with the type of document object associated with the specified data object. Referring to Figure 22, "Column D" 119, "Column E" 120 and "Column F" 121 constitute three fields that contain data items relating to each effect which, within each field, vary in content (analogous to, say, Visual Basic(RTM) variables of "Variant" data type, which can contain string, date, time, Boolean or numeric values). The content of such a field for a given record in the Effects table might be defined by rules that refer the type of change made to the data object and, as appropriate, the type of document object associated with the specified data object. For each type of change detailed in an effect, at least one subsequent field in the effect's record might, where applicable, contain corresponding data.

For example, referring to Figure 22: the number 1 in cell C3 125 denotes creation of data object 3 which, referring to cell D3 126, is associated with a Name object (as denoted by the number 7); the number 2 in cell C3 125, together with the number 1 in cell E3 127, denotes that data object 1 is the initial parent data object of data object 3 (data object 1 is associated with Workbook "Bookl"); the number 3 in cell C3 125 denotes that the document object initially associated with data object 3 is named, referring to cell F3 128 "Start"; and the number 4 in cell C4 129 denotes that the initial data value corresponding to data object 3 is, referring to cell D4 130, the expression

Referring again to Figure 22, action 12 and corresponding effects 40-49 exclusively relate to data values that have changed constant/expression, relationships and/or associated document object. For example, direct changes include the association of data object 5 with Range "Cl" per effect 41 and indirect changes include the deletion of data object 7 per effect 40 and the creation of data object 20 which is associated with Range "Bl" per effect 43. (Referring to Figure 21, action 13 and Figure 22, corresponding effects 50-51 relate to the user "saving" the Workbook.)

Referring to Figures 21 and 22, whilst the audit trail includes actions 12-13 and corresponding effects 40-51, the auditing system has not made any retrospective

changes to either pre-existing actions 1-11 or effects 1-39, which remain intact. This is illustrated with reference to Figures 23 and 24.

Figure 23 is a snapshot of a table of actions relating to the Workbook of Figure 1 before the contents of cell Bl are moved to cell Cl, and Figure 24 is a snapshot of a table of effects relating to the Workbook of Figure 1 before the contents of cell Bl are moved to cell Cl.

As mentioned above and illustrated in Figures 23 and 24, pre-existing actions 1-11 and effects 1-39 remain intact despite the changes to the Workbook. Consequently, detail of changes that preceded "dragging and dropping" or "cutting and pasting" cell Bl to cell Cl remains available for review and reference by any associated functionality and reporting.

For example, referring to Figure 22, in contrast with other auditing systems described above with reference to Workbook "Bookl", the audit trail records that: the expression in cell A2 was not "=SUM(A1:C1)" throughout but was initially "=SUM(A1:B1)" per effect 29 (and, as a result of "dragging and dropping" or "cutting and pasting" cell Bl to cell Cl, was subsequently changed to "=SUM(A1:C1)" per effect 47, with corresponding changes to its precedent-dependent relationships); and, prior to "dragging and dropping" or "cutting and pasting" cell Bl to cell Cl, the expression in cell Bl was initially entered as "=1+A1" per effect 9 and then changed to "=2+Al" per effect 11.

In addition to parent-child and precedence-dependence relationships, other relationships can be inferred in respect of data values contained in a spreadsheet. Such relationships are not explicitly represented by Microsoft(RTM) Excel(RTM)'s Object Model and might include, for example, "association" relationships between data values that are commonly referred by a range reference in a formula (as distinct from association of data objects with document objects). For example, referring to Figure 1, the expression "=SUM(A1:B1)" 8 in cell A2 contains reference to Range "A1:B1", and Ranges "Al" and "Bl" therein contain data values which are commonly referred by the single Range reference "A1:B1" (as distinct from separate references in, for example, the expression

"=A1+B1"); one might therefore infer that the data values in Ranges "Al" and "Bl" have an association relationship with one another.

Figure 25 is an illustration of "association" relationships between data objects relating to the Workbook of Figure 1.

Referring to Figure 25, the auditing system's data object 4 (which is associated with Range "Al") and data object 5 (which is associated with Range "Bl") share an association relationship with data object 12 (which is associated with Range "A1:B1"). Referring to Figure 19, "Column E" contains references denoting the same data objects' association relationships, in cells E4 101, E5 105 and E12 109, and data objects 4, 5 and 12 share a precedence-dependence relationship with data object 11 (which is associated with Range "A2") as denoted by cell Dl 1 108.

Further, such relationships might include association relationships between such data objects (where the data objects correspond to data values contained in Range objects) and data objects associated with other document objects that include, for example, a Name object that similarly commonly refers the same Range objects. For example, referring again to Figure 25, a new data object, associated with a Name object that contains the formula "=Sheetl !$A$l :$B$l", might have association relationships with data objects 4, 5 and 12, and vice versa. Association relationships might also relate to data values that, whilst not commonly referred by a range reference in a formula, were created in a manner that implies association relationships. For example, data objects, corresponding to data values contained in Range objects, might share association relationships in the event that they are created by extending a series e.g. by selecting cells and dragging the "fill handle" or by using the Microsoft(RTM) Excel(RTM) Series command. Thus association relationships may be independent of any Range references contained in expressions.

Similar to changes to precedent-dependent relationships, distinct records of changes to association relationships are informative to functionality and reporting.

Figure 26 is an illustration of "association" relationships between data objects relating to the Workbook of Figure 1 after the contents of cell Bl are moved to cell Cl.

As a result of "dragging and dropping" or "cutting and pasting" cell Bl to cell Cl: association relationships for data object 10, which is associated with Range "A1:D1", have changed (to exclude data object 7 and include data object 20 80) and accordingly, whilst the expressions contained in Range "El" and Series "Series 1" (which both refer Range "A1:D1") are unchanged as described above, precedents of associated data objects 9 and 15 have changed accordingly; and association relationships for data object 12, which is associated with Range "A1:C1" after the change, have changed (to include data object 20) and hence, referring to Figure 20, precedents of data object 11 (corresponding to the expression in Range "A2" which refers Range "A1:C1") have changed accordingly.

Various processes carried out in the preferred embodiment will now be described with reference to Figures 27 to 32.

Figure 27 is an overview of the processes carried out in the preferred embodiment.

The auditing system realises the audit trails described above by repeatedly executing a process that can be considered in terms of the following sequence of steps:

• In step 145 the application's user performs an action relating to the document, whether a change to the document or simply navigating the document or employing other functionality available in the application such as "saving" a Microsoft(RTM) Excel(RTM) Workbook;

• In step 146 the system identifies all actions by the user that relate to document changes that have not previously been processed by the system, whether the changes coincided or preceded the action referred above;

• In step 147, for each relevant action so identified, the system creates a record in the Actions table and records a corresponding date and time therein;

• In step 148: similarly, for each relevant action, the system identifies all document objects that changed as a result of the action;

• In step 149, for each relevant action, with reference to corresponding document changes, the system identifies all data objects that require related amendments; and

• In step 150, for each data object so identified, the system creates and populates new records in the "Effects" table and, as required, amends the corresponding record(s) in the Objects table.

Figure 28 is a flowchart of a process for identifying and selecting relevant document changes.

If the method is executed as an add-in program or script, the audit process might begin with an event 151 that is raised by, for example, the Microsoft(RTM) Excel(RTM) application. Otherwise, the start of the process is broadly coincident with such an event. Throughout the process, data is variously sourced from, for example: the event, the Microsoft(RTM) Excel(RTM) application, the Microsoft(RTM) Excel(RTM) workbook, the Windows® operating system and the add-in. Data is first analysed to identify any changes to the spreadsheet's document objects, where said changes preceded the event and are not reflected in the audit data 152. Such changes might, for example, include those for which the Microsoft(RTM) Excel(RTM) application does not raise an event and those made without the auditing system in operation. For each document change thus identified, a process described below is performed 153. Data is then analysed with specific regard to any changes, to the spreadsheet's document objects, that relate to the event 154. If a document change is thereby identified, the process described below is performed 155.

Figure 29 is a flowchart of a process for amending the "Actions" table and for identifying and selecting data objects that are impacted by a document change.

In the process of Figure 29, a new record is created in the audit data's Actions table 156 and its fields are populated as described above. Thus the Actions table includes a record for each relevant event raised by the application. Data is analysed to identify all data objects that are impacted as a direct result of the document change 157. For each object

thus identified, the process described below is performed 158. Data is further analysed to identify all data objects that are impacted as an indirect result of the document change 159. For each object thus identified, the process described below is performed 160.

Figure 30 is a flowchart of a process for identifying and selecting changes to data objects impacted by a document change.

In the process of Figure 30, the process described below is performed in respect of each change to the data obj ect 161.

Figure 31 is a flowchart of a process for amending the "Objects" and "Effects" tables.

In the process of Figure 31, one or more records are created in the audit data's Effects table and fields therein are populated with data relevant to the change to the data object and/or the change to the data object's relationships with other data objects 162. The contents of the record's fields are as described above. Where the change relates to the same object and action as those of the last record in the Effects table, the data is appended to existing data in the table's last record. Where the change to the object implies a change to the contents of its record in the Objects table, the Objects table is amended accordingly 163.

On entry of a new or amended formula, the system parses the formula to identify all data values and relational references therein and, with reference to its data objects and audit trail data, identifies and amends related data objects and creates corresponding entries in the audit trail. For example, referring back to Figure 22, effects 10 and 11, relating to action 5 and the expression then contained in cell Bl respectively, reflect that the constant data value contained in the expression has changed (from 1) to 2 and, whilst the expression has consequently changed (from "=1+A1") to "=2+Al", it remains dependent upon the data value contained in cell Al (effect 11 135 includes change type "8" and, specifically, it excludes both change type "9" and associated references to precedent data objects). Where applicable, this includes creating new data objects, which may be associated with blank document objects. For example: on initially entering the expression "=1+A1" in cell Bl, the system creates data objects 5

and 6, per effects 7 and 8, which respectively correspond to the expression "=1+A1" and the constant therein with value 1; and on "dragging and dropping" or "cutting and pasting" cell Bl to cell Cl, the system creates data object 20, per effect 43, which corresponds to the resulting (null) data value contained in cell Bl.

On "dragging and dropping" or "cutting and pasting" document object(s), the system creates additional records in its audit trail that include detail of resulting changes of document objects associated with data objects and thereby preserves continuity in respect of records for data objects. For example, referring again to Figure 22, on "dragging and dropping" or "cutting and pasting" cell Bl to cell Cl ₅ the audit trail's corresponding entries include effect 41, which reflects that the document object associated with data object 5 has changed (from Range "Bl") to Range "Cl". On inserting or deleting document object(s) - for example, row(s), column(s) or cell(s) in a spreadsheet - the system similarly preserves continuity in respect of records for related document objects. For example, if a row is deleted from a Microsoft(RTM) Excel(RTM) Workbook then, in addition to other new entries in the Effects table where applicable, the system records changes of document objects associated with all relevant data objects - that is, data objects that correspond to data values contained in Range objects located below the deleted row.

Figure 32 is a schematic showing the structure of the auditing system's main components.

In this structure, components are executed in response to corresponding events raised by the application, which may relate to a document change 164 or may not relate to a document change 165. For example, in the case of the Microsoft(RTM) Excel(RTM) application, the "SheetChange" event relates to a document change (it occurs when the contents of cells in a worksheet are changed by, for example, the user) whereas the "SheetSelectionChange" event does not relate to a document change (it occurs when the selection changes on a worksheet).

Where applicable, the component 166 examines the document to identify any preceding document changes that are unrelated to the event (including, for example, changes that

did not correspond to an event and are not yet reflected in the system's audit trail), which may result in the execution of one or more components 167 that make corresponding changes to the system's audit trail data. For example, in one embodiment operable with Microsoft(RTM) Excel(RTM), following a SheetSelectionChange event, the system identifies a new Name object and a component amends the system's audit trail accordingly.

Where the process commences with a document change-related event 164, corresponding components 168 then record resulting changes to the system's audit trail.

As applicable, all components read/write data from/to, for example, document objects and the system's audit trail. A variety of subsidiary components can be employed to perform operations required by one or more components that may include, for example, reading data from the system's audit trail. Further, the system can create instances of objects (where each object corresponds to, for example, a data object or document object) and these objects can be referred by components for the sake of processing efficiency. For example, in an embodiment operable with Visual Basic(RTM), the system defines "class types" to act as templates from which instances of objects can be created to represent document objects that contain data values.

Some further embodiments of the auditing system will now be described.

In one embodiment, the auditing system's definition of data objects does not extend beyond data values to include constants contained within expressions as described above.

In comparison with the system of the preferred embodiment, all tables can vary in structure and content according to efficiency considerations that include, for example, computer memory allocation, file size (whether stored with or separate from the document) and processing time (in respect of both modifying the audit trail and delivering associated functionality). In general, such efficiency considerations have inverse relationships, and efficiency in one respect may thus imply relative inefficiency

in another respect. Accordingly, the structure and content of the audit trail may vary according to requirements and applications thereof at any given time.

For example, referring to Figure 19, the integer field that denotes the type of associated document object 88 in the Objects table may further contain another integer field in the range 1-99 by multiplying the value of existing data in the field by 100 and adding new data thereto. Specifically, detail of a data object's precedence-dependence relationships can be employed to infer the corresponding data value's standing in the document. For example, in the case of a Microsoft(RTM) ExcelζRTM) Workbook, a data value's standing might be, say, "input" (no precedents but dependents), "calculation" (precedents and dependents), "output" (precedents only) or "title" (neither precedents nor dependents). Thus, for sake of processing efficiency, the Objects table can include an integer field, as described above, denoting such standing where, for example, 1 denotes "input", 2 denotes "calculation", 3 denotes "input & calculation" (e.g. a data object corresponding to an expression that contains one or more constants and has dependents), 4 denotes "output", 5 denotes "input & output" and so on. Further, changes thereto can be recorded in the Effects table.

Data contained in the Effects table is sufficient to recreate the Objects table at the present time. Similarly, with reference to the Actions table's content, data contained in the Effects table is sufficient to recreate the Objects table at any past date and time in the document's lifetime. Hence, whilst the Objects table can be stored with the audit data for sake of efficiency, the auditing system's output can be considered to include only the Actions and Effects tables and the inclusion of the Objects table 84 in Figure 18 may be considered to be for illustrative purposes only.

In a variant where the Objects table is stored with the system's audit trail, for the sake of processing efficiency, it can include additional fields. For example, referring to Figure 19, for each record therein, the Objects table can include references to child data objects 92, dependent data objects 93 and/or corresponding effects 94. Indeed, the Objects table can be further expanded to include constants, expressions and/or values resulting from expressions, in which event a change to the contents of the Objects table

can be considered to trigger creation of one or more corresponding records in the Effects table.

In a variant where the Objects table is stored with the audit data in practice, the objects table can be stored only in part and omitted fields can be interpreted with reference to audit data, optionally with reference to the Objects and Effects tables alone. For example, referring again to Figure 19: references to data objects' child data objects 92 can be populated by examination of (other) data objects' parent data objects 87; similarly, references to data objects' dependent data objects 93 can be populated by examination of (other) data objects' precedent data objects 90; and references to data objects' corresponding effects 94 can be populated by examining (each and every record in) the Effects table.

In an alternative embodiment, to minimise the size of the audit trail (and, in particular, corresponding computer memory allocation), any fields omitted from the stored Objects table are not populated but corresponding data is instead referred, on each relevant occasion, from the Effects table. For example, where detail of constants and expressions are excluded from the Objects table, the auditing system obtains them from corresponding entries in the Effects table, with reference to the specified data objects' corresponding records in the Effects table 94 (as shown in Figure 19).

In respect of all tables, data can extend across multiple fields as required due to constraints arising from the specifications and limitations of the audit trail's storage environment. For example: referring to Figure 19, where the audit trail is stored within a Microsoft(RTM) Excel(RTM) Workbook (whether or not the Workbook represents the audited document), references to the effects that correspond to each data object 94 can be contained in more than one field where the length of the corresponding text entry exceeds the number of characters permitted in a cell; and similarly, with consideration to the number of rows and columns permitted in a Worksheet, tables' fields can extend or repeat across a Worksheet and tables' records can further extend across multiple Worksheets.

For the sake of processing efficiency, the auditing system can record (in the Effects table) changes to, for example, a data object's child and dependent data objects, in addition to changes to a data object's parent and precedent data objects as described above. Similarly, referring to Figure 19, where the audit trial is stored in a Microsoft(RTM) Excel(RTM) Workbook, the system can, for example, write each data object's numerical identifier (1 in respect of data object 1, 2 in respect of data object 2 and so on) to cells, as opposed to employing row numbers (and multiples thereof) as described above.

Data objects can be associated with documents other than the audited document and with document objects therein. For example, referring to Figure 18, in respect of a Microsoft(RTM) Excel(RTM) Workbook with "links" that include "external references"-, the auditing system's data objects 84 can include data objects associated with other Workbook objects 82 and/or document objects therein and the data objects' "Associations" can include data objects in the other Workbook. The system can thus assess, and record or report as required, whether or not the associated document object in the other workbook is associated with the desired data object in the other Workbook. (If the document object in the other Workbook has been moved then it may no longer be associated with the desired data object in the other Workbook.)

Referring again to Figure 18, references to data objects can be hyphenated for sake of storage efficiency. For example, references to data objects 1, 2 and 3 can be recorded as "1, 2, 3" or "1-3". Further, references to precedent and dependent data objects, in the Objects 84 and/or Effects 86 tables, can be negative in order to denote contingent relationships. For example, in the case of a Microsoft(RTM) Excel(RTM) Workbook and a document object containing an expression that includes the IF function, precedent data in respect of the data object that corresponds to the expression can include negative references to the data objects that are associated with the document objects referred in the first argument of the IF function and, conversely, the data object that corresponds to the expression can be referred with a negative sign in dependent data in respect of the data objects that are associated with the document objects referred in the first argument of the IF function.

Fields in the tables can be positive or negative to denote other Boolean data. For example, referring to Figure 19, the contents of "column A" can be positive or negative to denote the content of "Column F" and thus remove the need for "Column F". Further, formatting available in the application can be employed in place of data fields. For example, referring again to Figure 19, where the audit trail is stored in a Workbook, the contents of "Column B" 88 can be replaced by varied formatting of records where, for example, bold denotes a Workbook object, underline denotes a Worksheet object, italic denotes a (single-cell) Range object and so on.

Referring to Figure 21, where the Actions table is stored in an appropriate document, for example a Workbook, the content of "Column A" and "Column B" can be stored in a single Column with content of type "Date", hi an alternative embodiment, the contents of the Actions table can be stored within the Effects table. Specifically, referring to Figure 22, an action's date and time are stored in two fields of each corresponding effect, in place of the references to the action that is contained in "Column A" 116 of the Effects table. Irrespective of this, the contents of the Actions table can be completely excluded from the audit data where it is not required by applications of the audit data.

With regard to records in the Effects table, referring again to Figure 22, there can be additional fields beyond "Column D", "Column E" and "Column F" as required according to consideration of operational and storage efficiency. Further, for sake of storage efficiency, a single record in the Effects table can relate to more than one data object where the content of such fields is empty or the same. Whilst the contents of such fields in the Effects table have been described as varying with the type of object changed and/or the type of change made, the nature of each individual field can instead be constant, in which case there may be additional fields and, in a given effect, one or more fields may be empty where they are not applicable to the type of object changed and/or the type of change made.

Where contents of the audit trail have been described as containing numeric codes such as, referring to Figure 22, the digits 0-9 contained in "Column C" 118 and denoting the type of change(s) related to an effect, such data fields can take alternative formats such

as alphanumeric. For example, referring again to Figure 22, records may be created in the Effects table relating to system-generated reporting that can include, for example, records of system-generated notifications and records of any responses thereto from users, and records of such reporting might include alphabetic codes in "Column C" 118. Similarly, alternative characters and codes can be employed in effects that relate to "undoing" or "redoing" actions or actions involving, for example, selecting cells and dragging the "fill handle" or using the Microsoft(RTM) Excel(RTM) Series command as described above. Further, the numeric codes illustrated in "Column C" can instead, for example, include a first digit denoting the "Owner" of an effect and hence whether the effect is "direct" or "indirect" (where, for example, 1 = the application's user, 2 = the application, etc) and subsequent digits represent the nature of the change without variation between "direct" or "indirect" changes. For example, "indirect" deletion of a data object might be recorded with the numeric code "25", where 2 denotes that the change was made by the application and 5 denotes that the change related to deletion of a data object. The audit trail described above, notably including such alphanumeric codes, is a specific example of its presentation and it may be presented differently for purposes such as perusal by users with, for example, text in place of codes.

Records in the Effects table described above include detail of changes to data objects that correspond to constants and expressions. The auditing system can similarly write detail of changes in values resulting from expressions, which may (or may not) correspond to different data objects from those that correspond to the related expressions.

Whilst Figure 29 illustrates that exhaustive analysis of data relating to objects impacted as a direct result of a document change precedes analysis of data relating to objects impacted as an indirect result of the same document change, and thus implies that creation of records in the Effects table relating to the former objects precedes creation of records relating to the latter objects, the analysis can be performed other than strictly sequentially (that is, data relating to some objects impacted as an indirect result of the document change can be analysed prior to data relating to some objects impacted as a direct result of the document change) and, with consideration to applications of the

audit data, the order of the Effects table's records may similarly vary from that implied by Figure 29.

The audit trail described above facilitates delivery of a variety of functionality to the application's user and some examples thereof will now be described with reference to the Microsoft(RTM) Excel(RTM) spreadsheet application.

With reference to the system's audit trail, detail of data therein can be presented in varying forms. Such detail may be presented in terms of document objects, data values and/or data objects and can furthermore be filtered to highlight or selectively present data pertaining to, for example, specific object(s), user(s), time period(s), type(s) of changes and/or relevant criteria relating to system-generated reporting. Such data may relate to more than one document in the event that the system's audit trail data, in relation to a number of documents, is consolidated.

The system's audit trail data facilitates extension or replacement of an application's "undo" functionality to encompass all past actions. For example, in one embodiment, changes made prior to most recently "opening" a Microsoft(RTM) Excel(RTM) Workbook can be "undone". Further, the data similarly facilitates extension or replacement of the application's "undo" functionality to afford "non-linear" reversal of past actions. For example, referring to Figure 22, after "dragging and dropping" or "cutting and pasting" cell Bl to cell Cl, the prior change to the expression therein, from "=1+A1" to "=2+Al" per effect 11 135, can be "undone" (i.e. the expression might be changed from "=2+Al" to "=1+A1") via "undo" functionality, without necessitating moving the same expression from cell Cl to its former location in cell Bl. Such additional functionality can selectively be applied to more than one document object where, for example, the user concurrently selects multiple cells in a Worksheet. Any "redo" functionality afforded by the application can similarly be extended or replaced with reference to data contained in the system's audit trail.

The system's audit trail data, in particular detail therein relating to precedence- dependence relationships, similarly facilitates extension or replacement of other application functionality. For example, Microsoft(RTM) Excel(RTM) includes

"formula auditing" functionality that displays precedent cells and dependent cells of formulas, similar to Range objects' DirectPrecedents and DirectDependents properties described above. Such functionality can be extended or replaced to present other types of document objects that are precedents or dependents of a formula, where said formula may be located in various types of document objects. For example, referring to Figure 15, Name "Start" (which is associated with data object 3), can be displayed as a precedent of Range "Al" (which is associated with data object 4) and, conversely, Range "Al" can be displayed as a dependent of Name "Start". Further, such functionality might present relationship data in terms of data values and/or data objects, rather than in terms of document objects. For example, referring again to Figure 15, "formula auditing" functionality might be extended to display the constant, with value 2, that is contained in expression "=2+Al" as a precedent of the same expression, or data object 6 as a precedent of data object 5. It can additionally enable selectively displaying precedent and dependent data values and/or data objects according to, for example, their standing as described above.

An application's validation functionality can similarly be extended, complemented or replaced in order that it might benefit from data contained in the system's audit trail. For example, Microsoft(RTM) Excel(RTM) includes "data validation" functionality that enables the user to designate valid cell entries according to a variety of criteria. With reference to data contained in the system's audit trail, this functionality can also, for example: relate to entries in other types of document objects; relate to constants contained in expressions; include criteria relating to when, and/or for what time period, changes are valid; and include relevant criteria relating to relationships. Further, with reference to data contained in the system's audit trail, relevant validation criteria can be automatically interpreted and defined (via or independent of the application's existing validation functionality) and detail of any validation failures can be recorded in the system's audit trail as described above and/or advised to the user by various means.

Whilst the embodiments above have been described principally with reference to the Microsoft(RTM) Excel(RTM) spreadsheet application, it will be appreciated that the invention finds wider application to other spreadsheet applications and also to other document applications having document objects with associated data values. Examples

of other document applications include word processors (such as Microsoft(RTM) Word(RTM)), databases (such as Microsoft(RTM) Access(RTM)), applications for creating vector- and/or bitmap-based graphics, sound and music editing applications.

Further modifications lying within the spirit and scope of the present invention will be apparent to a skilled person in the art.