Transformation of real time data into times series and filtered real time data within a spreadsheet application

• US 5,926,822 A
• Filed: 09/06/1996
• Issued: 07/20/1999
• Est. Priority Date: 09/06/1996
• Status: Expired due to Term

• Current Assignee: Financial Engineering Associates, Inc. (ION Investment Group Ltd.)
• Original Assignee: Financial Engineering Associates, Inc. (ION Investment Group Ltd.)
• Inventors: Garman, Mark B.
• Primary Examiner(s): Kulik, Paul V.
• Assistant Examiner(s): ROBINSON, GRETA LEE
• Application Number:
  US08/709,101
• Time in Patent Office: 1,047 Days
• Field of Search: 707/503, 707/504, 707/500, 707/100, 707/104
• US Class Current: 715/201
• CPC Class Codes: G06F 40/18 of spreadsheets form-filling G06F40/174

Abstract

A spreadsheet application includes functions and data structures for transforming real time data items individually received over time from a real time data source into time series data globally available within the spreadsheet environment. The spreadsheet application includes a tape function operating in conjunction with a plurality of tape data structures, to store real time data items received over individually over time from a real time data source into one of the tape data structures, thereby persistently storing a set of previously received real time data items in a manner that is accessible to other functions of the spreadsheet. A plurality of filter functions and filter data structures enable computation of various statistical measures on real time data items by storing in globally accessible filter data structures information derived from a sequence of real time data items, such as a previously computed filter output, latency information describing the time intervals between real time data items, and other data, to allow the computation of a current filter output without storing all previously received real time data items. The filter and tape functions allow for various uses of real time data items without the need to provide complex macro programming.

Claims

1. A computer executable spreadsheet application for processing a plurality of time dependent data values generated by a real time data source external to the spreadsheet application, the spreadsheet application executable by a processor of a computer system having a memory, the spreadsheet application comprising:

a tape function, executable by the processor, that receives the plurality of data values in order individually over time from the real time data source; and

,

a tape data structure, stored in the memory and globally accessible from and independent of, any cell of the spreadsheet application, that stores each of the plurality of data values in the order received by the tape function.

2. The spreadsheet application of claim 1, wherein:

the tape data structure stores a predetermined number N of data values; and

,

for each currently received data value, the tape data function removes an N^th oldest data value from the tape data structure, and stores the currently received data value to the tape data structure.

3. The spreadsheet application of claim 1, wherein each data value is associated with a time value, and the tape data structure stores the time value in association with the data value.

4. The spreadsheet application of claim 1, wherein each time is associated with a time value, the real time data source provides a plurality of data values at each time value, and the tape data structure stores in association with each time value the plurality of data value received at the time value.

5. The spreadsheet application of claim 1, further comprising:

a plurality of tape data structures, each tape data structure referenced by a unique tape number; and

the tape function includes an interface for specifying the tape number of the tape data structure to receive the plurality of data values from the real time data source.

6. A computer executable spreadsheet application for processing time dependent data values generated by a real time data source external to the spreadsheet application, the spreadsheet application executable by a processor of a computer system having a memory, the spreadsheet application comprising:

a tape data structure, stored in the memory and globally accessible from, and independent of, any cell of the spreadsheet application, and having N ordered storage locations S₁ to S_N for a predetermined value of N for storing an ordered, predetermined number of data values; and

a tape function, executable by the processor, that receives a plurality of data values individually over time from the real time data source and stores each existing data value in the tape data structure in a storage location S_j to a storage location S_j+1 for 1≦

j≦

N-1, and stores a currently received data value to storage location S₁.

7. A computer executable spreadsheet application for processing time dependent data values generated by a real time data source external to the spreadsheet application, the spreadsheet application executable by a processor of a computer system having a memory, the spreadsheet application comprising:

a filter function, executable by the processor, that receives a plurality of data values individually over time from the real time data source and computes a current filter output based on a currently received data value, and a previously computed filter output stored in a filter data structure; and

a filter data structure, stored in the memory and globally accessible from, and independent of, any cell of the spreadsheet application, that receives the filter output from the filter function and stores the filter output.

8. The spreadsheet application of claim 7, wherein the filter data structure further stores filter specific auxiliary data for computing the filter output.

9. The spreadsheet application of claim 7, wherein the filter function is selected from a group consisting of:

a volatility function;

a smoothing function;

a maximum value function;

a minimum value function;

an autoregressive conditional heteroscedaskicity function;

a generalized autoregressive conditional heteroscedaskicity function;

a Kalman filter function;

a sign function;

a standard deviation function;

a time weighted standard deviation function;

a trim function;

a direction drift function; and

a weighted average function.

10. The spreadsheet application of claim 7, further comprising:

a plurality of filter data structures, each filter data structure having a unique filter number; and

the filter function includes an interface for specifying the filter number of the filter data structure to receive and store the filter output.

11. The spreadsheet application of claim 7, wherein:

there are a plurality of filter functions, each filter function computing a different filter output, and associated with a predetermined set of data types used to compute the filter output; and

each filter data structure comprises a union of the data types for all of the filter functions.

12. A computer executable spreadsheet application for processing time dependent data values generated by a real time data source external to the spreadsheet application, the spreadsheet application having a plurality of user-editable cells, and executable by a processor of a computer system having a memory, the spreadsheet application comprising:

a plurality of filter functions, each invokable from within any cell of the spreadsheet application and executable by the processor in response to an input of data values from the real time data source, each filter function having an interface that specifies;

a filter number of a filter data structure for storing data used or computed by the filter function;

zero or more parameters for modifying computation of the filter function;

a reference to the real time data source from which the filter function receives a plurality of data values individually over time;

each filter function for computing a current filter output based on a currently received data value from the real time data source, and a previously computed filter output stored in the filter data structure specified by the filter number; and

,

a plurality of filter data structures stored in the memory and globally accessible from, and independent of, any cell of the spreadsheet application, each filter data structure having a filter number for being referenced within the interface of a filter function, each filter data structure storing the filter output from a filter function.

13. A computer implemented method of processing in a spreadsheet application, time dependent data values generated by a real time data source external to the spreadsheet application, the spreadsheet application including a spreadsheet having a plurality of cells, and executable by a processor of a computer system having a memory, the method comprising:

storing in the memory a data structure globally accessible from, and independent of, any cell of the spreadsheet application for storing an ordered, predetermined number of data values;

receiving into a cell of spreadsheet each of a plurality of data values in order over time from the real time data source;

storing each data value as it is received in the cell to the data structure; and

removing from the data structure each time a data value is stored, a least recently received data value in excess of the predetermined number of data values.

14. A computer implemented method of processing in a spreadsheet application, time dependent data values generated by a real time data source external to the spreadsheet application, the spreadsheet application including a spreadsheet having a plurality of cells and executable by a processor of a computer system having a memory, the method comprising:

storing in the memory a data structure globally accessible from, and independent of, any cell of the spreadsheet application for storing computed data values;

receiving into a cell of the spreadsheet each of a plurality of data values in order over time from the real time data source;

computing a current filter output of a filter function based on;

a currently received data value from the real time data source; and

a previously computed output of the filter function based on a previously received data values; and

storing the current filter output in the data structure.

15. A computer implemented method of processing in a spreadsheet application, time dependent data values generated by a real time data source external to the spreadsheet application, the spreadsheet application including a spreadsheet having a plurality of cells and executable by a processor of a computer system having a memory, the method comprising:

storing in memory a data structure, globally accessible from, and independent of, any cell of the spreadsheet application, and having N ordered storage locations S₁ to S_N for a predetermined value of N for storing an ordered, predetermined number of data values;

receiving into a cell of spreadsheet each of a plurality of data values in order over time from the real time data source;

for each data value in a storage location S_j, storing the data value to a storage location S_j+1 for 1≦

j≦

N-1; and

storing each data value as it is received from the real time data source to a storage location S₁ of the data structure.

16. A computer executable spreadsheet application for processing time dependent data values generated by a real time data source external to the spreadsheet application, the spreadsheet application executable by a processor of a computer system having a memory, the spreadsheet application comprising:

a function, executable by the processor, that receives a plurality of data values individually over time from the real time data source, and returns either a single value derived from the plurality of data values or an array of data values; and

a data structure, stored in the memory, and globally accessible from and independent of, any cell of the spreadsheet application, that receives from the function the single value or the array of data values.