Data analysis and prediction of a dataset through algorithm extrapolation from a spreadsheet formula

• US 10,685,175 B2
• Filed: 10/02/2018
• Issued: 06/16/2020
• Est. Priority Date: 10/21/2017
• Status: Active Grant

• Current Assignee: Sciencesheet Inc.
• Original Assignee: Sciencesheet Inc.
• Inventors: Castaneda-Villagran, Oscar
• Primary Examiner(s): McIntosh, Andrew T
• Application Number:
  US16/150,262
• Publication Number:
  US 20190121847A1
• Time in Patent Office: 623 Days
• CPC Class Codes: G06F 40/18 of spreadsheets form-filling G06F40/174
• CPC Class Codes: G06F 40/186 Templates
• CPC Class Codes: G06F 9/5077 Logical partitioning of resources; Management or configuration of virtualized resources specific details on emulation or internal functioning of virtual machines G06F9/455
• CPC Class Codes: G06N 20/00 Machine learning

Abstract

Disclosed are a method, a device, a system and/or a manufacture of data analysis and prediction of a dataset through algorithm extrapolation from a spreadsheet formula. In one embodiment, a method extracts one or more spreadsheet formulas from one or more cells of a spreadsheet file and assembles a formula algorithm. The formula algorithm accepts a set of data entries comprising one or more independent variables, and outputs a prediction metric as a dependent variable such that each data entry is calculation independent. An extrapolated algorithm expressed in a programming language is generated. A computation block of a dataset is specified and submitted for computation along with the extrapolated algorithm over a network. The dataset comprising two or more data entries usable as an input to the extrapolated algorithm. An output data re-combined from the first output block and one or more additional output blocks is received.

Claims

1. A method of data analysis through an interface of a spreadsheet application, the method comprising:

determining at least one of a spreadsheet format of a spreadsheet file and a syntax format of the spreadsheet file;

wherein the spreadsheet file defining a formula algorithm accepting a data entry comprising one or more independent variables and the formula algorithm outputting a prediction metric as a dependent variable, the formula algorithm comprising one or more spreadsheet formulas stored in one or more cells of the spreadsheet file, the independent variables referenced from one or more cells of the spreadsheet file, and the prediction metric output in a cell of the spreadsheet file, and

wherein the one or more spreadsheet formulas, the independent variable, and the prediction metric are stored in the syntax format permitting independent calculation of the prediction metric for two or more instances of the data entry;

extracting from the spreadsheet file and storing in computer memory the one or more spreadsheet formulas comprising the formula algorithm, the one or more independent variables, and the prediction metric;

assembling each of the one or more spreadsheet formulas into the formula algorithm and storing the formula algorithm in a computer memory;

generating and storing an extrapolated algorithm expressed in a programming language based on the formula algorithm,

wherein each spreadsheet formula equivalent to one or more functions of the programming language and each of the one or more independent variables defining a declared variable of at least one of the one or more functions of the programming language;

receiving a dataset comprising two or more data entries in the syntax format usable as an input to the extrapolated algorithm to independently calculate the prediction metric of each of the two or more data entries;

specifying a first computation block comprising one or more data entries of the dataset;

extracting from the dataset each of the one or more data entries within the first computation block;

submitting the first computation block and the extrapolated algorithm to a computing cluster over a network,

wherein the extrapolated algorithm applied against the first computation block resulting in a first output block comprising a prediction value of the prediction metric of each instance of the data entry within the first computation block; and

receiving an output data re-combined from data comprising the first output block and one or more additional output blocks.

2. The method of claim 1, further comprising:

upon at least one of submission of the first computation block and generation of the output data, at least one of;

provisioning a computing virtual machine, provisioning a computing process container, and initiating a microservice; and

storing the output data in a computer memory of at least one of the computing virtual machine, the computing process container, and the microservice.

3. The method of claim 2, further comprising:

parsing an external data source to result in an instance of the data entry;

mapping the instance of the data entry to one or more cells conforming to the syntax format; and

importing the instance of the data entry into at least one of the spreadsheet file and the dataset.

4. The method of claim 3, further comprising:

receiving a constraint range for at least one dependent variable that is an instance of the prediction metric;

tuning an input parameter that is at least one of;

a value of one or more of the independent variables, one or more formula algorithms, and the extrapolated algorithm;

determining that a value of the instance of the prediction metric is within the constraint range after tuning the input parameter; and

modifying at least one of the instance of the independent variable, the formula algorithm, and the extrapolated algorithm.

5. The method of claim 4, further comprising:

running an automatic machine learning process to automatically apply one or more predictive models to the dataset;

determining a predictive model of the one or more predictive models fits the dataset;

modifying the extrapolated algorithm in response to an application of one or more predictive models to the dataset to result in a modified extrapolated algorithm; and

re-submitting the first computation block and the modified extrapolated algorithm to the computing cluster over the network.

6. The method of claim 5, further comprising:

determining at least one of an equivalency of an instance of the spreadsheet formula to a function of the programming language, a redundancy in the formula algorithm, and an approximation opportunity for increasing a computational efficiency; and

modifying the extrapolated algorithm in response to determination of at least one of the equivalency, the redundancy, and the approximation opportunity.

7. The method of claim 6, further comprising:

retrieving a parse tree generation routine specifying a parse tree assembly procedure for the formula algorithm of the spreadsheet file;

assembling a parse tree data of the formula algorithm comprising one or more nodes of the parse tree, each node of the one or more nodes specifying at least one of an instance of the spreadsheet formula, an instance of the dependent variable, and an instance of the independent variable;

determining an instance of the programming language in which to express the extrapolated algorithm;

receiving an output generation routine;

deconstructing the parse tree data by;

(i) traversing the one or more nodes of the parse tree,

(ii) determining one or more of the one or more nodes of the parse tree are a defined node associated with a function specified in the output generation routine, and

(iii) mapping each defined node to each instance of the function specified in the output generation routine; and

storing the extrapolated algorithm as an output of the output generation routine.

8. The method of claim 7, wherein the syntax format comprises:

(i) confining data of each data entry to a row of the spreadsheet file, with each instance of a cell of the row comprising at least one of a null, an independent variable, a dependent variable, and a spreadsheet formula, an extra-spreadsheet instruction, and an analysis instruction,

(ii) confining to the row of the spreadsheet file the one or more spreadsheet formulas comprising the formula algorithm, and

(iii) exclusively drawing each instance of a reference of each of the one or more spreadsheet formulas to a preceding cell within the row.

9. The method of claim 7, wherein the syntax format comprises:

(i) confining data of each data entry to a column of the spreadsheet file, with each instance of a cell of the column comprising at least one of a null, an independent variable, a dependent variable, and a spreadsheet formula,

(ii) confining to the column of the spreadsheet file the one or more spreadsheet formulas comprising the formula algorithm, and

(iii) exclusively drawing each instance of a reference of each of the one or more spreadsheet formulas to a preceding cell within the column.

10. The method of claim 9, wherein the syntax format comprises:

a template format specifying a plurality of cells of the spreadsheet file, each cell of the plurality of cells of the spreadsheet file comprising at least one of the independent variable, the spreadsheet formula, and the prediction metric.

11. The method of claim 9, further comprising:

calling a previous output data for addition to one or more data entries of the dataset;

wherein the spreadsheet file is in a format that is at least one of;

.123, .12M, ._XLS, ._XLSX, .AST, .AWS, .BKS, .CELL, .DEF, .DEX, .DFG, .DIS, .EDX, .EDXZ, .ESS, .FCS, .FM, .FODS, .FP, .GNM, .GNUMERIC, .GSHEET, .HCDT, .IMP, .MAR, .NB, .NCSS, .NMBTEMPLATE, .NUMBERS, .NUMBERS-TEF, .ODS, .OGW, .OGWU, .OTS, .PMD, .PMDX, .PMV, .PMVX, .QPW, .RDF, .SDC, .STC, .SXC, .TMV, .TMVT, .UOS, .WKI, .WKQ, .WKS, .WKU, .WQ1, .WQ2, .WR1, .XAR, .XL, .XLR, .XLS, .XLSB, .XLSHTML, .XLSM, .XLSMHTML, .XLSX, .XLTHTML, .XLTM, .XLTX,

wherein the dataset is in a format that is at least one of;

.CSV, .TSV, .XLS, .XLSX, .JSON, .XML, a Parquet File Format, an Avro file format, and

wherein the programming language is at least one of Scala, Java, Python, R, Clojure, Lisp, and Haskel.

12. A method of data analysis, the method comprising:

extracting from a spreadsheet file and storing in computer memory one or more spreadsheet formulas comprising the formula algorithm, one or more independent variables, and a prediction metric as a dependent variable,

wherein the formula algorithm accepting a data entry comprising the one or more independent variables and the formula algorithm outputting the prediction metric as the dependent variable, the independent variables referenced from one or more cells of the spreadsheet file, and the prediction metric output in a cell of the spreadsheet file;

assembling each of the one or more spreadsheet formulas into the formula algorithm and storing the formula algorithm in computer memory;

retrieving a parse tree generation routine specifying a parse tree assembly procedure for the formula algorithm of the spreadsheet file;

assembling a parse tree data of the formula algorithm comprising one or more nodes of the parse tree, each node of the one or more nodes specifying at least one of an instance of the spreadsheet formula, an instance of the dependent variable, and an instance of the independent variable;

determining a programming language in which to express the extrapolated algorithm;

receiving an output generation routine;

deconstructing the parse tree data by;

(i) traversing the one or more nodes of the parse tree,

(ii) determining one or more of the one or more nodes of the parse tree are a defined node associated with a function specified in the output generation routine, and

(iii) mapping each defined node to each instance of function specified in the output generation routine;

storing the extrapolated algorithm as an output of the output generation routine;

receiving a dataset comprising two or more data entries usable as an input to the extrapolated algorithm to independently calculate the prediction metric of each of the two or more data entries;

specifying a first computation block comprising one or more data entries of the dataset;

extracting from the dataset each of the one or more data entries within the first computation block;

submitting the first computation block and the extrapolated algorithm to a computing cluster over a network,

wherein the extrapolated algorithm applied against the first computation block resulting in a first output block comprising a prediction value of the prediction metric of each instance of the data entry within the first computation block; and

receiving an output data re-combined from data comprising the first output block and one or more additional output blocks.

13. The method of claim 12, further comprising:

upon at least one of submission of the first computation block and generation of the output data, at least one of;

provisioning a computing virtual machine, provisioning a computing process container, and initiating a microservice; and

storing the output data in at least one of the computing virtual machine, the computing process container, and the microservice,

wherein each spreadsheet formula equivalent to one or more functions of the programming language and each of the one or more independent variables defining a declared variable of at least one of the one or more functions of the programming language.

14. The method of claim 13, further comprising:

running an automatic machine learning process to analyze at least one of the formula algorithm and the extrapolated algorithm;

modifying the extrapolated algorithm in response to the assessment of at least one of the formula algorithm and the extrapolated algorithm.

15. The method of claim 14, further comprising:

determining at least one of an equivalency of an instance of the spreadsheet formula to a function of the programming language, a redundancy in the formula algorithm, and an approximation opportunity for increasing a computational efficiency; and

modifying the extrapolated algorithm in response to determination of at least one of the equivalency, the redundancy, and the approximation opportunity.

16. The method of claim 15, further comprising:

determining at least one of a spreadsheet format of the spreadsheet file and a syntax format of the spreadsheet file,

wherein the one or more spreadsheet formulas, the independent variable, and the prediction metric are stored in the syntax format permitting independent calculation of the prediction metric for two or more instances of the data entry, and

wherein the syntax format comprises;

(i) confining data of each data entry to a row of the spreadsheet file, with each instance of a cell of the row comprising at least one of a null, an independent variable, a dependent variable, and a spreadsheet formula, an extra-spreadsheet instruction, and an analysis instruction,

(ii) confining to the row of the spreadsheet file the one or more spreadsheet formulas comprising the formula algorithm, and

(iii) exclusively drawing each instance of a reference of each of the one or more spreadsheet formulas to a preceding cell within the row.

17. The method of claim 16, wherein the syntax format comprises:

a template format specifying a plurality of cells of the spreadsheet file, each cell of the plurality of cells of the spreadsheet file comprising at least one of the independent variable, the spreadsheet formula, and the prediction metric; and

calling a previous output data for addition to one or more data entries of the dataset,

wherein the spreadsheet file is in a format that is at least one of;

.123, .12M, ._XLS, ._XLSX, .AST, .AWS, .BKS, .CELL, .DEF, .DEX, .DFG, .DIS, .EDX, .EDXZ, .ESS, .FCS, .FM, .FODS, .FP, .GNM, .GNUMERIC, .GSHEET, .HCDT, .IMP, .MAR, .NB, .NCSS, .NMBTEMPLATE, .NUMBERS, .NUMBERS-TEF, .ODS, .OGW, .OGWU, .OTS, .PMD, .PMDX, .PMV, .PMVX, .QPW, .RDF, .SDC, .STC, .SXC, .TMV, .TMVT, .UOS, .WKI, .WKQ, .WKS, .WKU, .WQ1, .WQ2, .WR1, .XAR, .XL, .XLR, .XLS, .XLSB, .XLSHTML, .XLSM, .XLSMHTML, .XLSX, .XLTHTML, .XLTM, .XLTX,

wherein the dataset is in a format that is at least one of;

.CSV, .TSV, .XLS, .XLSX, .JSON, .XML, a Parquet File Format, an Avro file format, and

wherein the programming language is at least one of Scala, Java, Python, R, Clojure, Lisp, and Haskel.

18. A system comprising:

an execution server comprising;

a processor of the execution server;

a memory of the execution server, the memory of the execution server comprising computer readable instructions that when executed on the processor of the execution server;

receive an extrapolated algorithm expressed in a programming language based on one or more spreadsheet algorithms extracted from a spreadsheet file;

receive a dataset comprising two or more data entries in a syntax format as an input to the extrapolated algorithm to independently calculate a prediction metric of each of the two or more data entries;

specify a first computation block comprising one or more of the two or more data entries of the dataset;

extract from the dataset each of the one or more of the two or more data entries within the first computation block;

submit the first computation block and the extrapolated algorithm to a computing cluster over a network,

wherein the extrapolated algorithm applied against the first computation block resulting in a first output block comprising a prediction value for the prediction metric of each instance of the data entry within the computation block;

receive an output data re-combined from data comprising the first output block and one or more additional output blocks;

upon at least one of submission of the first computation block and generation of the output data, at least one of;

provision a computing virtual machine, provision a computing process container, and initiate a microservice; and

store the output data in at least one of the computing virtual machine, the computing process container, and the microservice; and

a network.

19. The system of claim 18, further comprising:

a translation server comprising;

a processor of the translation server;

a memory of the translation server, the memory of the translation server comprising computer readable instructions that when executed on the processor of the translation server;

generates and stores the extrapolated algorithm expressed in a programming language based on a formula algorithm,

wherein the spreadsheet file defining the formula algorithm accepting the data entry comprising one or more independent variables and outputting the prediction metric as a dependent variable, the formula algorithm comprising one or more spreadsheet formulas stored in cells of the spreadsheet file, the independent variables referenced from one or more cells of the spreadsheet file, and the prediction metric output in a cell of the spreadsheet file,

wherein the one or more spreadsheet formulas, the independent variable, and the prediction metric are stored in the syntax format permitting independent calculation of the prediction metric for two or more instances of the data entry,

wherein each instance of a spreadsheet formula comprising the spreadsheet algorithm equivalent to one or more functions of the programming language and each of the one or more independent variables as inputs to the spreadsheet formula defining a declared variable of at least one of the one or more functions of the programming language, and

a client device comprising;

a processor of the of the client device;

a memory of the client device, the memory of the client device comprising computer readable instructions that when executed on the processor of the client device;

determines at least one of a spreadsheet format of the spreadsheet file and the syntax format of the spreadsheet file;

extract from the spreadsheet file and store in the memory of the client device the one or more spreadsheet formulas comprising the formula algorithm, the independent variables, and the prediction metric;

combine each of the one or more spreadsheet formulas into the formula algorithm and store the formula algorithm in the memory of the client device; and

transmit the formula algorithm.

20. The system of claim 19, further comprising:

a model server comprising;

a processor of the model server,

a memory of the model server, the memory of the model server comprising computer readable instructions that when executed on the processor of the model server;

receive a constraint range for at least one dependent variable that is an instance of the prediction metric;

tune an input parameter that is at least one of;

one or more independent variables, the formula algorithm, and the extrapolated algorithm;

determine that a value of the instance of the prediction metric is within the constraint range after tuning the input parameter;

modify at least one of the instance of the independent variable, the formula algorithm, and the extrapolated algorithm;

run an automatic machine learning process to automatically apply one or more predictive models to the dataset;

determine a predictive model of the one or more predictive models fits the dataset;

modify the extrapolated algorithm in response to an application of one or more predictive models to the dataset to result in a modified extrapolated algorithm; and

re-submit the first computation block and the modified extrapolated algorithm to the computing cluster over the network,

the memory of translation server further comprising computer readable instructions that when executed on the processor of the translation server;

retrieve a parse tree generation routine specifying a parse tree assembly procedure for the formula algorithm of the spreadsheet file;

assemble a parse tree data of the formula algorithm comprising one or more nodes of the parse tree, each node of the one or more nodes specifying at least one of an instance of the spreadsheet formula, an instance of the dependent variable, and an instance of the independent variable;

determine an instance of the programming language in which to express the extrapolated algorithm;

receive an output generation routine;

deconstruct the parse tree data by;

(i) traversing the one or more nodes of the parse tree,

(ii) determining one or more of the one or more nodes of the parse tree are a defined node associated with a function specified in the output generation routine, and

(iii) mapping each defined node to each instance of the function specified in the output generation routine; and

store the extrapolated algorithm as an output of the output generation routine,

the memory of execution server further comprising computer readable instructions that when executed on the processor of the execution server;

determine at least one of an equivalency of an instance of the spreadsheet formula to a function of the programming language, a redundancy in the formula algorithm, and an approximation opportunity for increasing a computational efficiency;

modify the extrapolated algorithm in response to determination of at least one of the equivalency, the redundancy, and the approximation opportunity; and

call a previous output data for addition to one or more data entries of the dataset,

the memory of client device further comprising computer readable

instructions that when executed on the processor of the client device;

parse an external data source to result in an instance of the data entry;

map the instance of the data entry to one or more cells conforming to the syntax format; and

import the instance of the data entry into at least one of the spreadsheet file and the dataset,

wherein the spreadsheet file is in a format that is at least one of;

.123, .12M, ._XLS, ._XLSX, .AST, .AWS, .BKS, .CELL, .DEL, .DEX, .DEG, .DIS, .EDX, .EDXZ, .ESS, .FCS, .EM, .FODS, .FP, .GNM, .GNUMERIC, .GSHEET, .HCDT, .IMP, .MAR, .NB, .NCSS, .NMBTEMPLATE, .NUMBERS, .NUMBERS-TEF, .ODS, .OGW, .OGWU, .OTS, .PMD, .PMDX, .PMV, .PMVX, .QPW, .RDF, .SDC, .STC, .SXC, .TMV, .TMVT, .UOS, .WKI, .WKQ, .WKS, .WKU, .WQ1, .WQ2, .WR1, .XAR, .XL, .XLR, .XLS, .XLSB, .XLSHTML, .XLSM, .XLSMHTML, .XLSX, XLTHTML, .XLTM, .XLTX,

wherein the dataset is in a format that is at least one of;

.CSV, .TSV, .XLS, .XLSX, .JSON, .XML, a Parquet File Format, an Avro file format,

wherein the programming language is at least one of Scala, Java, Python, R, Clojure, Lisp, and Haskel, and

wherein the syntax format comprises;

(i) confining data of each data entry to a row of the spreadsheet file, with each instance of a cell of the row comprising at least one of a null, an independent variable, a dependent variable, and a spreadsheet formula, an extra-spreadsheet instruction, and an analysis instruction,

(ii) confining to the row of the spreadsheet file the one or more spreadsheet formulas comprising the formula algorithm, and

(iii) exclusively drawing each instance of a reference of each of the one or more spreadsheet formulas to a preceding cell within the row.