NL 2 F ormula: Generating Spreadsheet Formulas from Natural Language Queries (2024)

Abstract: Writing formulas on spreadsheets, such as Microsoft Excel and Google Sheets, is a widespread practice among users performing data analysis. However, crafting formulas on spreadsheets remains a tedious and error-prone task for many end-users, particularly when dealing with complex operations. To alleviate the burden associated with writing spreadsheet formulas, this paper introduces a novel benchmark task called NL2Formula, with the aim to generate executable formulas that are grounded on a spreadsheet table, given a Natural Language (NL) query as input. To accomplish this, we construct a comprehensive dataset consisting of 70,799 paired NL queries and corresponding spreadsheet formulas, covering 21,670 tables and 37 types of formula functions. We realize the NL2Formula task by providing a sequence-to-sequence baseline implementation called fCoder. Experimental results validate the effectiveness of fCoder, demonstrating its superior performance compared to the baseline models. Furthermore, we also compare fCoder with an initial GPT-3.5 model (i.e., text-davinci-003). Lastly, through in-depth error analysis, we identify potential challenges in the NL2Formula task and advocate for further investigation.

## introduction it is a widespread practice among users to engage in data analysis by composing formulas within spreadsheet applications such as microsoft excel and google sheets. while spreadsheet formula languages (e.g., microsoft excel formula) are relatively simpler than general-purpose programming languages for data analysis, formulating these formulas on spreadsheets remains burdensome and error-prone for end-users @xcite . to address this challenge, numerous approaches and tools (e.g., flashfill @xcite and spreadsheetcoder @xcite ) have been proposed to automatically generate spreadsheet formulas. building upon substantial progress in spreadsheet formula generation, this paper goes beyond the existing efforts by introducing a novel natural language (nl) interface capable of generating spreadsheet formulas from a user's nl query (short for nl2formula). we believe that, for the majority of end-users, expressing their intentions in nl is more accessible than working with formulas when performing data analytics on spreadsheets. figure 1 presents two representative running examples to illustrate the task of nl2formula. this task involves generating the corresponding spreadsheet formula automatically, given a spreadsheet table and an nl query input from an end-user. the resulting formula is intended for execution in spreadsheet applications, such as microsoft excel. in this paper, we focus the spreadsheet application only on microsoft excel, where spreadsheet formulas can take on various forms, offering a wide range of possibilities for exploration. specifically, we present two primary categories of spreadsheet formulas. the first category is the analysis query (figure 1 (a) ), typically comprising excel formula functions utilized for data analysis. the second category is the calculation (figure 1 (b)), consisting of basic numerical operations used for straightforward calculations. it is important to note that nl2formula shares similarities with the well-studied task of text2sql, which involves translating an nl description into a sql query grounded on a database table @xcite @xcite . however, it differs in two nl: what are the name and the nationality of the host of the highest age? formula: let(a,sortby(choose({1,2},a2:a 11,b2:b11),c2:c11,-1),filter(a, sequence(rows(a))<=1)) (a) analysis query formula: c8/c10 nl: what is the proportion of imft's property, plant, and equipment over total assets in 2018? fundamental aspects. (1) the structure of a spreadsheet table is more flexible than that of a database table. unlike fixed patterns in databases, the metadata (e.g., headers and orientation) of tables in a spreadsheet is optional, and the placement of the table in the layout is highly flexible. this flexibility presents significant challenges when it comes to representing the data. (2) the formula is typically expressed by the index of data location. in the process of generating formulas, it becomes crucial not only to determine which columns in the table should be selected but also to identify the exact position of the cell containing these values. additionally, the expression of formulas can change with the placement of the table in the layout. in this paper, we pioneer the effort to formulate and benchmark the task of nl2formula. one main challenge lies in the lack of well-labeled data for training. to tackle this issue, we construct a novel dataset comprising paired nl queries and their corresponding formulas, grounded on specific spreadsheet tables. as manual labeling would require extensive human effort and time, we opt for an indirect transformation approach using an existing dataset of text2sql (i.e., spider @xcite ), which is composed of 10,181 nl descriptions along with their corresponding sql queries. we devise a set of conversion rules by analyzing the grammar of sql and excel formulas. by applying the formulated conversion rules, we convert sql queries from the established text2sql datasets into formulas suitable for nl2formula. additionally, to augment the dataset, we engage in the manual collection of labeled data following a set of predefined rules. as a result, we produce a comprehensive dataset comprising 70,799 paired nl queries and formulas, associated with a total of 21,670 tables. furthermore, we establish a benchmark for nl2formula. in this benchmark, we also present f coder, a sequence-to-sequence frame-work based on the pre-trained language model t5 @xcite . as a baseline model, we adapt fortap @xcite , originally designed for synthesizing spreadsheet formulas, for comparison. we conduct comprehensive experiments and analysis to assess the effectiveness of our proposed f coder. the experimental results demonstrate that f coder achieves the highest performance with 70.6% exact matching accuracy and 77.1% accuracy based on the results of running formulas on a specific engine (i.e., microsoft excel). after conducting a comprehensive analysis of the experimental results, we have identified potential areas for improvement and future directions that warrant further exploration. in summary, the key contributions of this paper are three-fold. (1) we are the first to formulate a new task of nl2formula, that can serve as an interface allowing users to effortlessly translate input nl queries into spreadsheet formulas. (2) we introduce a novel dataset that comprises 70,799 paired nl queries and their corresponding formulas, associated with 21,670 tables. (3) we benchmark several models for the task of nl2formula, including our designed f coder that is based on pretrained t5, as well as fortap @xcite that is adapted from tuta @xcite . we can perform various computing, data processing, and operational tasks using pre-defined formulas within the spreadsheet. in a formula, we can refer to a cell by combining its column and row numbers, as shown by the notation (e.g., b2). additionally, we have the option to use a range operator ":" to create a rectangular range between two cells, with the top-left and bottom-right corners specified. for instance, the @xmath0:b5) encompasses all cells in columns a and b, ranging from row 1 to row 5. in general, a formula is composed of constant values, arithmetic operations, function calls, and references to cells. formally, the microsoft excel formula studied in this paper can be defined by the extended bnf grammar, referred to appendix a. figure 2 shows a detailed example of the excel formula. problem statement. let n denote the nl query composed of a sequence of tokens {q 1 , q 2 , . . . , q l }, and t denote the corresponding tabular context composed of a collection of cells {c 1 , c 2 , . . . , c m }. let f denote the corresponding formula to predict that is denoted a sequence of tokens {y 1 , y 2 , . . . , y k }. inspired by previous semantic parsing tasks, we formulate the task of nl2formula as a sequence-to-sequence problem, where the source sequence is the nl query and its tabular contexts, while the target sequence is the formula. more specifically, the nl2formula problem is expressed as follows: given a source nl sequence n , as well as the tabular context t , the goal is to learn a mapping function f to map the input {n, t } into a formula f , i.e., @xmath1; t ), where θ is the parameters of model f . ## f coder : a reference framework for the task of nl2formula, we adopt the encoder-decoder paradigm as the baseline approach. in this paradigm, an encoder network embeds the nl queries and tabular contexts into an table: row index column index ## conclusion in this paper, we have presented a novel and challenging research problem, nl2formula, and develop an accompanying dataset that includes spreadsheet tables, nl queries, and formulas. we construct a comprehensive dataset consisting of 70,799 paired nl queries and corresponding spreadsheet formulas, covering 21,670 tables and 37 types of formula functions. we also realize the nl2formula task by providing a sequence-tosequence baseline implementation called f coder. through in-depth error analysis, we identify potential challenges in the nl2formula task and advocate for further investigation. we believe that the benchmark developed in this paper can prompote the related research in nl2formula.