Method and apparatus for automatically producing spreadsheet-based models

• US 20060095833A1
• Filed: 09/16/2005
• Published: 05/04/2006
• Est. Priority Date: 11/22/2000
• Status: Abandoned Application

• Current Assignee: Andrew Clive Orchard, Geoffrey John Bristow
• Original Assignee: Andrew Clive Orchard, Geoffrey John Bristow
• Inventors: Orchard, Andrew Clive, Bristow, Geoffrey John
• Application Number:
  US11/228,102
• Publication Number:
  US 20060095833A1
• Time in Patent Office: Days
• US Class Current: 715/209
• CPC Class Codes: G06F 40/18 of spreadsheets form-filling G06F40/174

Abstract

A designer builds a condensed, generic form of a spreadsheet which is a definition file. This can be designed in Microsoft Excel, following a set of guidelines, or using a high level symbolic language. If required, HTML files, can be produced directly from the Excel definition file to enable the user to, input parameters using a web browser. A user defines the various parameters that are to be used for their particular spreadsheet and the software of the present invention expands the template'"'"'s rows and columns according to those requirements, working out the effect on the formulae and linked cells as this takes place. Behind the scenes a set of information tables are built, either in memory or in a file, based on the information in the definition file. These tables are processed in a structured way to build the spreadsheet formulae, reference other tables and build any objects required to operate the model. Formulae are copied and/or rebuilt depending on the circumstances in which they will be used. In addition, the data input areas of the spreadsheet can be separated onto another worksheet to facilitate usability. Reports and charts can be adjusted to suit the data. Several templates or definitions can be combined into one spreadsheet and other objects, e.g. drop down lists and scrollbars, can be added to enhance usability. VBA code can also be included to perform complex operations not normally available in a spreadsheet. Definition libraries that describe, or contain a copy of, a part of the model can be added seamlessly, for example, a bar chart with a particular color scheme or a complicated calculating function. The definition library can also contain VBA code, which can become part of the finished model and can add to its functionality.

Claims

1. A method of automatically generating a spreadsheet comprising the steps of:

(a) creating a spreadsheet definition, including symbolic programming language defining relationships between ranges of data, said language comprising worksheets for building modules for designated tasks incorporated in said spreadsheet definition,

(b) generating at least one user interface based on data contained in the spreadsheet definition and operable to receive user data related to user design choices,

(c) receiving user data via the input interface, and

(d) automatically combining the user data with the definition thereby to generate a customised spreadsheet by expanding and populating the definition according to the user data.

2. A method according to claim 1 further including the step of providing a user with the customised spreadsheet.

3. A method according to claim 2, wherein the customised spreadsheet is provided to a user by an electronic transmission.

4. A method according to claim 1, wherein the spreadsheet definition includes a cell formula.

5. A method according to claim 1, wherein the spreadsheet definition includes a repetition flag indicating the possibility of repeating the formula in at least one direction across the customised spreadsheet.

6. A method according to claim 1, wherein the spreadsheet definition is stored in a standard spreadsheet file format.

7. A method according to claim 1, wherein the generated user interface is arranged to be presented to a user via a web browser application.

8. A method according to claim 7, wherein the electronic transmission is an email transmission.

9. A method according to claim 1, wherein the customised spreadsheet is generated based on a plurality of spreadsheet definitions.

10. A method according to claim 1, wherein the customised spreadsheet includes a separate worksheet containing cells which require user input.

11. A method of automatically generating a spreadsheet comprising the steps of:

(a) creating a spreadsheet definition using a symbolic programming language, including symbolic programming language defining relationships between ranges of data, said language comprising worksheets for building modules for designated tasks incorporated in said spreadsheet definition, and

(b) automatically combining user-supplied data with the definition thereby to generate a customised spreadsheet by expanding and populating the definition according to the user data.

12. A method according to claim 11, wherein the spreadsheet definition includes fields selected from the group of a chart field, a number format field, an era field and a derivation field.

13. A method according to claim 11, wherein the customised spreadsheet is generated based on a plurality of spreadsheet definitions.

14. A method according to claim 11, wherein the customised spreadsheet includes a separate worksheet containing cells which require user input.

15. A method of indexing a worksheet object comprising the steps of:

(a) providing an object worksheet operable to hold source data,

(b) providing a table worksheet operable to index portions of the object worksheet,

(c) providing a switch worksheet operable to hold an index pointer which points to a set of values in the table worksheet which in turn indexes a portion of the source data in the chart worksheet,

(d) importing the said indexed source data into a data area of the switch worksheet, and

(e) generating an object based on the data in the said data area of the switch worksheet.

16. A method according to claim 15, wherein the worksheet object data displayed is varied by varying the value of the index pointer to point to a different set of cells in the table worksheet.

17. A customised spreadsheet created by the method of:

(a) creating a spreadsheet definition, including symbolic programming language defining relationships between ranges of data, said language comprising worksheets for building modules for designated tasks incorporated in said spreadsheet definition,

(b) generating at least one user interface based on data contained in the spreadsheet definition and operable to receive user data related to user design choices,

(c) receiving user data via the input interface, and

(d) automatically combining the user data with the definition thereby to generate a customised spreadsheet by expanding and populating the definition according to the user data.

18. A customised spreadsheet created by the method of:

(a) creating a spreadsheet definition using a symbolic programming language, said language comprising worksheets for building modules for designated tasks incorporated in said spreadsheet definition, and

(b) automatically combining user-supplied data with the definition thereby to generate a customised spreadsheet by expanding and populating the definition according to the user data.