System and methods for intelligent analytical graphing

Abstract

An electronic spreadsheet system includes an intelligent analytical graphing module for creating complex analytical graphs from user-supplied information. Methods are described for automatically creating one or more desired analyses on a spread of user-supplied information, all without the user having to modify his or her information or supply (or even understand) the operations necessary for a given analysis. The system includes a preferred interface for receiving selections from the user indicating one or more analyses to be performed on the information, and for displaying analytical graphs for visualizing the result of a selected analysis.

Claims

What is claimed is:

1. In a computer system, a method for visually interpreting user-supplied information, the method comprising:

(a) specifying a range of information;

(b) selecting a desired analysis from a displayed list of analyses;

(c) determining a set of operations which effects the selected analysis;

(d) deriving a temporary data set by performing said determined set of operations on the specified range of information; and

(e) plotting a graph from the temporary data set, whereby said selected analysis on the specified range of information is visually represented to a user.

2. The method of claim 1, wherein said information is stored as information cells in a memory device, and wherein step (a) includes selecting a range of cells with an input device.

3. The method of claim 2, wherein said selected range of cells are stored contiguously in the computer system.

4. The method of claim 1, wherein the information is stored as a plurality of information cells, each of which has a data type.

5. The method of claim 4, wherein said data type includes a numeric format selected from one of a fixed, scientific, currency, percent, date, time, text, and user-defined data types.

6. The method of claim 4, wherein before step (b) includes:

examining said specified range of information for determining a common data type;

determining which ones of available analyses can be performed for the determined data type; and

modifying the list of analyses to be displayed in step (b) so that only ones that can be performed for the determined data type are displayed to the user.

7. The method of claim 6, wherein examining the specified range of information includes examining internal representation of each of said information cells in the computer system.

8. The method of claim 6, wherein examining the specified range of information includes:

heuristically determining said common data type by examining each of the information cells with an expert system.

9. The method of claim 1, wherein said displayed list of analyses include aggregation, moving average, linear fit, and exponential fit.

10. The method of claim 1, wherein step (c) includes:

retrieving a predetermined sequence of operations from a collection of analytical models stored in the computer system.

11. The method of claim 1, wherein said temporary data set is maintained internally by the computer system in a manner not ordinarily accessible by the user of the system.

12. The method of claim 11, wherein said temporary data set is automatically deleted upon completion of step (e).

13. The method of claim 1, wherein step (e) includes:

displaying the graph on a selected one of a display device and a printer device.

14. A system for analyzing information comprising:

a computer having a memory and a processor;

means for storing in the memory standard analyses, said means specifying a series of processor operations for performing each one of the analyses;

means for selecting a range of information to be analyzed;

means for selecting a desired one of the standard analyses;

means for performing said selected analysis by performing the series of operations specified for said selected analysis on the selected range of information, whereby a temporary data set is derived; and

means for plotting said temporary data set.

15. The system of claim 14, wherein said standard analyses include aggregation, moving average, linear fit, and exponential fit analyses.

16. The system of claim 14, wherein said system includes an electronic spreadsheet for displaying said range of information as a contiguous set of spreadsheet cells.

17. The system of claim 16, wherein said means for selecting a range of information includes an input device for entering a range of cells.

18. The system of claim 14, further comprising:

means for determining a data type for said range of information.

19. The system of claim 18, further comprising:

means for determining which of the standard analyses can be performed for the determined data type.

20. The system of claim 14, wherein said means for plotting includes a selected one of a display device and a printing device.

21. A system for visually analyzing user-supplied information comprising:

(a) a computer having a processor and a memory;

(b) an input device, coupled to the computer, for entering a set of information for storage within the memory;

(c) means for displaying a list of analyses, a desired one of which is selected with the input device;

(d) means for instructing the processor to perform a series of operations which achieves the desired analysis; and

(e) means, responsive to said means for instructing the processor, for plotting the desired analysis.

22. The system of claim 21, wherein said analyses include aggregation, moving average, linear fit, and exponential fit analyses.

23. The system of claim 21, wherein said means for instructing the processor includes a knowledge base for storing a series of operations for each said analyses.

24. The system of claim 21, wherein said set of information is stored as a plurality of information cells in said memory.

25. The system of claim 24, wherein each of the information cells has a data type.

26. The system of claim 25, wherein said data type includes a numeric format selected from one of a fixed, scientific, currency, percent, date, time, text, and user-defined data types.

27. The system of claim 25, wherein said means for displaying a list of analyses further comprises:

means for determining a common data type for said set of information; and

means for displaying a list of analyses based on the common data type determined for said set of information.

28. The system of claim 27, wherein said means for determining a common data type includes means for examining internal representation in said memory for each information cell of said set of information.

29. The system of claim 21, further comprising:

means, responsive to said means for instructing the processor to perform a series of operations which achieves the desired analysis, for storing results of said series of operations as a temporary data set.

30. The system of claim 29, wherein said means for plotting the desired analysis comprises:

means for plotting a graph from said temporary data set.

Description

COPYRIGHT NOTICE

A portion of the disclosure of this patent document contains material which is subject to copyright protection. The copyright owner has no objection to the facsimile reproduction by anyone of the patent document or the patent disclosure as it appears in the Patent and Trademark Office patent file or records, but otherwise reserves all copyright rights whatsoever.

BACKGROUND OF THE INVENTION

The present invention relates generally to the field of information processing by digital computers and, more particularly, to the processing and presentation of information by program applications, particularly electronic spreadsheets.

Before computers, numerical analyses, particularly financial ones, were usually prepared on an accountant's columnar pad or spreadsheet, with pencil and calculator in hand. By organizing data into columns and rows, spreadsheets afford the rapid assimilation of information by a reader. The task of preparing a spreadsheet on paper, however, is not quite so fast. Instead, the process tends to be very slow, as each entry must be tediously calculated and entered into the spreadsheet. Since all calculations are the responsibility of the preparer, manually prepared spreadsheets are also prone to errors. Hence, preparation of spreadsheets by hand is slow, tedious, and unreliable.

With the advent of microcomputers, a solution was forthcoming in the form of "electronic spreadsheets." Better known simply as "spreadsheets," these software programs provide a computerized replacement for the traditional financial modeling tools: the accountant's columnar pad, pencil, and calculator. In some regards, spreadsheet programs are to those tools what wordprocessors are to typewriters. Spreadsheets offer dramatic improvements in ease of creating, editing, and using financial models.

A typical spreadsheet program configures the memory of a computer to resemble the column/row or grid format of an accountant's columnar pad, thus providing a visible calculator for a user. Because this "pad" exists dynamically in the computer's memory, however, it differs from paper pads in several important ways. Locations in the electronic spreadsheet, for example, must be communicated to the computer in a format which it can understand. A common scheme for accomplishing this is to assign a number to each row in a spreadsheet, and a letter to each column. To reference a location at column A and row 1 (i.e., the upper-left hand corner), for example, the user types in "A1". In this manner, the spreadsheet defines an addressable storage location or "cell" at each intersection of a row with a column.

Data entry into an electronic spreadsheet occurs in much the same manner that information would be entered on an accountant's pad. After a screen cursor is positioned at a desired location, the user can enter alphanumeric information. Besides holding text and numeric information, however, spreadsheet cells can store special instructions or "formulas" specifying calculations to be performed on the numbers stored in spreadsheet cells. In this fashion, cell references can serve as variables in an equation, thereby allowing precise mathematical relationships to be defined between cells. The structure and operation of a spreadsheet program, including advanced functions such as functions and macros, are documented in the technical, trade, and patent literature. For an overview, see e.g., Cobb, S., Using Quattro Pro 2, Borland-Osborne/McGraw-Hill, 1990; and LeBlond, G. and Cobb, D., Using 1-2-3, Que corp., 1985. The disclosures of each of the foregoing references are hereby incorporated by reference.

Electronic spreadsheets offer many advantages over their paper counterparts. For one, electronic spreadsheets are much larger (i.e., hold more information) than their paper counterparts; electronic spreadsheets having thousands or even millions of cells are not uncommon. Spreadsheet programs also allow users to perform "what if" scenarios. After a set of mathematical relationships has been entered into a worksheet, the spread of information can be recalculated using different sets of assumptions, with the results of each recalculation appearing almost instantaneously. Performing this operation manually, with paper and pencil, would require recalculating every relationship in the model with each change made.

While electronic spreadsheets offer significant productivity gains in the task of complex data management, none has particularly addressed the task of complex data analysis. For instance, a user may wish to see a weighted moving average of monthly sales figures. With conventional electronic spreadsheets, however, the user must not only understand the complex mathematics behind such a calculation, but also he or she must enter one or more formulas into the spreadsheet for performing the analysis; the likelihood of introducing an error is high. Moreover, this requires the user to modify the underlying spreadsheet with formulas and additional data which themselves are typically not of interest to the user; thus, the approach adds unnecessary complexity to the spreadsheet. Finding conventional approaches unworkable, most users simply forego these analyses--ones which may reveal important trends and characteristics of the user's data.

SUMMARY OF THE INVENTION

Spreadsheet software has found wide application for processing information, particularly financial information. Despite their computational power, however, conventional electronic spreadsheets have paid little attention to the analysis of the information which they store. As a result, valuable analyses, which may reveal important trends and characteristics of the data, have been ignored.

The present invention recognizes a need for providing a spreadsheet system with methods and interface for automating the task of deriving analytical information from one's data and presenting it in easy-to-understand graphical form. More particularly, the present invention provides an intelligent graphing module whereby analytical techniques, such as aggregation, moving average, linear regression, exponential fitting, and the like, are available to any spread of data, all without the user having knowledge of the mathematics or theories supporting these analyses.

A method of the present invention for analytical graphing proceeds as follows. First, a data series is specified (by selecting a range of cells). Next, the system displays a selection of analyses. By examining the data type of the members of the data series (e.g., fixed, scientific, currency, percent, date, time, text, and user-defined data types), the system may intelligently determine which analyses are appropriate for the task at hand. Accordingly, the displayed list of analyses may be adjusted so that only relevant ones are available for selection. If any additional parameters are required for the selected analysis (e.g., center weighted), the user is prompted to enter appropriate values. The system then proceeds to generate a meta-data set by applying an operation or a series of operations required for the specified analysis; the "intelligence" or knowledge of the series of operations which are required to effect the analysis is maintained internally by the system (so that the user need not supply the information or even understand it). Finally, a visual representation of the analysis is displayed by plotting data points from the meta-data set. At the conclusion of the method, the meta-data set is discarded; however, if the user desires, he or she may save this information for future use.

BRIEF DESCRIPTION OF THE DRAWINGS

FIG. 1A is a block diagram of a computer system in which the present invention may be embodied.

FIG. 1B is a block diagram of a software system of the present invention, which includes operating system, optional application software, and a spreadsheet system.

FIG. 1C is a simplified block diagram illustrating the basic architecture and functionality of the spreadsheet system of FIG. 1B.

FIG. 1D is a simplified block diagram illustrating a functional model of the system, from the user's perspective.

FIG. 2A is a screen bitmap illustrating a spreadsheet worksurface/interface of the present invention.

FIG. 2B is a screen bitmap illustrating Graph tools provided by the interface of FIG. 2A.

FIG. 3A is a screen bitmap illustrating a sample spread of data (daily sales information) for a fictitious company.

FIGS. 3B-D are screen bitmaps illustrating and interface and method of the present invention for creating an aggregation of information (in this example, a graph of weekly sales average).

FIGS. 3E-F illustrate an interface and method of the present invention for creating and displaying multiple relationships in a single graph.

FIG. 3G is a screen bitmap illustrating a Table operation of the present invention for placing analytical information directly within a spread of user-supplied data.

FIG. 4A is a screen bitmap illustrating an interface and method of the present invention for creating a moving average analysis on the spread of information (from FIG. 3A).

FIG. 4B is a graph of moving averages against sales data, as requested by the user through the interface of FIG. 4A.

FIG. 4C is a screen bitmap illustrating an interface and method of the present invention for creating a linear fit from the user-supplied data (of FIG. 3A).

FIG. 4D is a graph of daily sales (from FIG. 3A) fitted to a line.

FIG. 5 is a flowchart illustrating a method of the present invention for visually analyzing one's data with analytical graphs.

GLOSSARY

Axis: Most graphs contain two axes: the x-axis runs horizontally along the bottom of the graph; the y-axis runs vertically on the left. The intersection of a point on each axis represents the data point.

Data Point: A single spreadsheet value displayed in a graph.

Label: In a spreadsheet, a label is a text entry. In a graph, labels are spreadsheet entries (of any type) that the user assigns to the graph to define plotted values.

Legend: A key displayed beside or beneath a graph that specifies the colors, marker symbols, or fill patterns for each series graphed.

Logarithmic: A method of scaling graphs where each major division of the scale represents 10 times the value of the previous division. Logarithmic (or log) scaling is best suited to widely varying, positive data.

Moving Average: A progressive average that smoothes fluctuating data points by averaging each point with a previous number of points (called a period). Moving averages can be weighted, so that each point in the period has a different weight, or emphasis.

Scale: The range of values associated with an axis.

Series: A set of cell values which are used for plotting a graph. For a simple graph, for example, each cell value is plotted sequentially as a data point on the graph.

Tick Marks: Scale indicators placed at regular intervals along an axis.

DETAILED DESCRIPTION OF A PREFERRED EMBODIMENT

General System

A. Hardware

As shown in FIG. 1A, the present invention may be embodied on a computer system such as the system 100, which comprises a central processor 101, a main memory 102, an input/output controller 103, a keyboard 104, a pointing device 105 (e.g., mouse, track ball, pen device, or the like), a display device 106, and a mass storage 107 (e.g., hard disk). Additional input/output devices, such as a printing device 108, may be included in the system 100 as desired. As illustrated, the various components of the system 100 communicate through a system bus 110 or similar architecture. In a preferred embodiment, the computer system 100 includes an IBM-compatible personal computer, which is available from several vendors (including IBM of Armonk, N.Y.).

B. Software

Illustrated in FIG. 1B, a computer software system 120 is provided for directing the operation of the computer system 100. Software system 120, which is stored in system memory 102 and on disk memory 107, includes a kernel or operating system 140 and a shell or interface 160. One or more application programs, such as application software 125, may be "loaded" (i.e., transferred from storage 107 into memory 102) for execution by the system 100. The system 100 receives user commands and data through user interface 160; these inputs may then be acted upon by the system 100 an accordance with instructions from operating system module 140 and/or application module 125. The interface 160, which may support a character-based and/or preferably a graphical user interface (GUI), also serves to display results, whereupon the user may supply additional inputs or terminate the session. In a preferred embodiment, operating system 151 is MS-DOS. Interface 160, on the other hand, is typically provided by the application programs and spreadsheet system 130, which will now be described.

As shown, system 120 also includes a spreadsheet system 130 of the present invention. The spreadsheet system 30 interfaces with the system 100 through the operating system 140. Spreadsheet system 130 may also be operably coupled to one or more spreadsheet add-in libraries, which are commercially available. In a preferred embodiment, the spreadsheet system 130 includes Quattro.RTM. Pro 4.0, available from Borland International of Scotts Valley, Calif.

Shown in further detail by a simplified block diagram of FIG. 1C, the system 130 includes a spreadsheet engine or module 131, a graphing module 135, and an analytical models module 136. In operation, the system maintains a spread of information cells 133, an atomic unit which is maintained and processed by the module 131. Data and formulas for the cells are received from an input device (e.g., keyboard, mouse, disk, and the like). From the cells, there may be larger groupings, such as ranges (including blocks), sheets (including 2D spreads), and groups of sheets (including 3D spreads, linked sheets, and pages of sheets or "notebooks").

The graphing module 135 provides the system 130 with the capability of displaying the spread of information visually, such as in various graphs. Coupled to the graphing module 135 is the analytical module 136, which maintains the various analytical models (e.g., aggregation, moving average, linear regression, exponential fitting, and the like) of the system. In this manner, the system 130 maintains built-in intelligence, whereby complex analytical graphs may be easily constructed by the user (who may know nothing about the underlying mathematics and theories supporting a given analysis).

Referring now to FIG. 1D, an abstraction of the functionality of the system 130, when viewed from the user's perspective, may be represented as follows. As shown by block diagram 170, the user stores his or her information in cells 133. The system, in turn, provides a plurality of operators 173 for manipulating or otherwise processing the cells 133. In a preferred embodiment, the system provides an evaluator 171, functionally interposed between the cells 133 and the operators 173, which includes knowledge necessary for performing various analyses. In this manner, the user may be insulated from the complexities of the operators 173. Specifically, the user need not understand the various functions provided by the operators (nor even know of the operators' existence); instead, this information is provided by the evaluator knowledgebase. All told, the present invention includes built-in intelligence such that a user need only specify what analysis is desired (as opposed to prior art techniques which also require the user to specify how an analysis is performed).

Spreadsheet System

A. Introduction

The operation of the system 130, with particular emphasis on analytical graphing methods of the present invention, will now be described in detail. The following description will focus on the presently preferred embodiments of the present invention, which are embodied in spreadsheet applications operative in an MS-DOS environment. The present invention, however, is not limited to any particular environment or any particular application. Instead, those skilled in the art will find that the system and methods of the present invention may be advantageously applied to a variety of systems, including different platforms such as Macintosh, UNIX, NextStep, and the like. Moreover, the methods of the present invention will find application in other programs for managing information, such as database management systems and the like. Therefore, the description of the exemplary embodiments which follows is for purposes of illustration and not limitation.

B. Spreadsheet Interface

Referring now to FIGS. 2A-J, the user interface of the spreadsheet system 130 will now be described. As shown in FIG. 2A, the system 130 includes an interface or worksurface 200 having five distinct areas: a menu bar 210, a spreadsheet area 250, an input line 230 at the top, a status line 240 at the bottom, and a toolbar menu 220. Each component will be examined in turn.

Spreadsheet client area 250 is a rectangular or windowing interface, typically occupying most of the screen 106. It is the area where the user opens a spreadsheet (e.g., spreadsheet 255) and File Manager windows and make changes to spreadsheets. Through the window 250, the user views a portion of a spreadsheet at a time. To view other areas, the user scrolls the spreadsheet, using either the scroll bars 256, 257 or the arrow keys (from device 104). On both the vertical and horizontal scroll bars, a scroll box indicates the current position relative to the portion of the spreadsheet that contains data. To move to a different part of the spreadsheet, the user drags the scroll box along the scroll bar and then releases.

Spreadsheet 255 itself is a rectangular grid comprising columns 253 (identified by letters) and rows 251 (identified by numbers). Thus, it may be divided into the following parts. The horizontal border 253 is a row of letters. Each letter identifies a column of cells. Columns are marked with letters A-Z, then AA-AZ, BA-BZ and the like, up to IA-IV in a preferred embodiment. The column containing the cell selector appears on the border in reverse video (or in a contrasting color on a color screen). The vertical border 251 is a column of numbers (1-8192 in a preferred embodiment) that identifies each row of cells. It shows the row containing the cell selector in reverse video or a contrasting color, depending on the monitor.

Each intersection of a column and a row includes a cell. A cell is a location where the user can enter data, formulas, and the like. Each cell is identified by an address determined by the column and row that contain it. For example, the cell in the top left corner is cell A1. The letter and number that make up the address are the cell's coordinates. The cell selector is the highlighted rectangle that indicates the current cell. This is the cell that will be affected by the next action, such as entering a value. The user can move the selector to any cell in the spreadsheet.

To enter information in the spreadsheet, the user typically moves the selector to the cell (e.g., with a screen cursor 205 or keyboard 102) the user wants and types in the entry. The characters typed in appear on the input line. A cell selector, a highlighted rectangle, shows the user where the next character will appear. When the entry is complete, one presses Enter or an arrow key (Right arrow, Left arrow, PgUp, or the like). The system 130 writes the value into the current cell, erasing any previous entry. If the user enters the data by pressing an arrow key, the selector moves in the appropriate direction.

Multiple cells may be selected for aggregate operations. In particular, a block is defined as a rectangular group of cells identified by the cell addresses of two opposite corners--usually the upper left and bottom right cells. In response to user movement signals from the pointing device 105, the cursor may be "dragged" across a range of cells for selecting a block, as is known in the art. Once selected, blocks may be used in commands and formulas to act on several cells at once.

The input line 230 functions as follows. When the user selects a cell, for example, clicking it with the cursor 205, the input line at the top of the screen displays information about the current cell. The input line may display, for example, the following information about the current cell: cell address, value, formula, data types, formatting, forced data type (if any), and the like.

Status line 240 functions as follows. In Ready mode, the status line displays information about the current state of the program. File name shows the name of the current spreadsheet. A window number is displayed to indicate the number of the current window. The system assigns the numbers in the order in which the windows are opened.

With particular reference to FIGS. 2B-J, the use of the menu bar 210 will now be described. A menu, such as File menu 211, is a list of commands or options. To initiate commands, the user calls up a menu or sequence of menus and chooses items. All menus branch from the menu bar 210. To activate the menu bar from the keyboard, the user presses the forward slash key (/). Once the user activates a menu, he or she can choose a command.

Menu bar 210 includes the following selections: file, edit, style, graph, print, database, tools, options, and windows. The file option is selected when the user wants to work with files, access the operating system or file manager, or exit the spreadsheet system. The edit option invokes a submenu for copying, moving, deleting, adjusting, and naming blocks of data. The style option invokes a menu for formatting blocks, adjusting columns, adding lines and shading, and creating and using named styles. The graph option allows the user to create, change, name, and display graphs (as described in further detail hereinbelow). The print option allows the user to print a spreadsheet or graph. The database option permits one to sort and query records, and also restrict data entry. The tools option invokes a submenu for using macros; working with spreadsheet links; performing "what-if" analysis, goal seeking, linear and non-linear programming, frequency distribution, regression analysis, matrix algebra, and the like; and tracing circular references and other errors. The options menu selection is used for setting system and spreadsheet defaults. Finally, the windows option is used for arranging the window display.

Toolbar menu 220 comprises a plurality of screen buttons providing instant access to user commands. In an exemplary embodiment, for instance, the following buttons/functions may be provided:

    ______________________________________
    Button     Function
    ______________________________________
    Erase      Shortcut for /Edit Erase Block.
    copy (CPY) Shortcut for /Edit Copy.
    Move (MOV) Shortcut for /Edit Move.
    Style (STY)
               Shortcut for /Style Use Style.
    Align (ALN)
               Displays the /Style Alignment menu.
    Font (FNT) Displays the /Style Font menu.
    Insert (INS)
               Shortcut for /Edit Insert.
    Delete (DEL)
               Shortcut for /Edit Delete.
    Fit        Shortcut for /Style Block Size Auto Width.
    Sum        Uses @SUM to total rows, columns, or both.
    Format (FMT)
               Displays the /Style Numeric Format menu.
    CHR        Switches to text (character) mode display.
    WYS        Switches to WYSIWYG display.
    BAR        Displays more buttons.
    ______________________________________

    ______________________________________
    Standard time periods
    A week has
              A month has A quarter has
                                      A year has
    ______________________________________
    7 days    30 days     90 days     360 days
              4 weeks     12 weeks    51 weeks
                          3 months    12 months
                                      4 quarters
    ______________________________________

• Inventors
  Davoust, Paul;
• Assignee
  Borland International, Inc. (Scotts Valley, CA)
• Published
  Dec-20-1994
• Current US Classes:
  345/440
  715/503
• Application #
  993195
• International Classes
  G06F 015/00
• Field of Search
  395/140 395/145 395/155 395/161 345/133
• Examiner
  Nguyen; Phu K.
• Agent
  Smart; John A.
• US Patent References:
  5182796
  5278951