NZ239657A - Transferring data from database to spreadsheet file cells - Google Patents

Description

239657 PATENTS FORM NO. 5 Our Ref: JT667589 NEW ZEALAND PATENTS ACT 1953 COMPLETE SPECIFICATION DATA PROCESSING SYSTEM AND METHOD COMPLETE AFTER PROVISIONAL DATED 3/9/91 I, NORMAN MICHAEL RORKE, a New Zealand citizen, of 118 Curtis Street, Northland, Wellington, New Zealand, hereby declare the invention, for which I pray that a patent may be granted to me and the method by which it is to be performed, to be particularly described in and by the following statement: The present invention relates to a system whereby data can be imported from any external system into the cells of a spreadsheet generated through programs such as QuattroPro^, LOTUS*"111, and EXCELtrn by the placement of commands within the affected cells which are interpreted and acted upon by the external system.

More particularly, but not exclusively, the present invention relates to a system enabling the transferal of data from an accounting system into a preformatted spreadsheet file for graphing and report generation purposes.

To the present time accounting software packages and spreadsheet programs have stored data according to their own individual formats. Accounting software packages to date have had limited report generation capability, especially with regard to graphical representation of information. To date it has been difficult to produce a spreadsheet in usable form from the data in an accounting software package.

The approach adopted to date has been to try to produce a spreadsheet file compatible with a spreadsheet program based upon knowledge of the format used in the spreadsheet program. Programs produced in this way are not always in fact compatible, nor do they allow a user to arrange a spreadsheet in the format they desire.

It is an object of the present invention to provide a system which enables the direct transposition of data from a first format into a format suitable for use in a spreadsheet program such as the spreadsheets Quattro^m, Lotustm and Symphonytm.

Further objects of the invention will come apparent from the following description.

According to one aspect of the present invention there is provided in a data processing system including a database in which data relating to one or more matters is allocated into data fields, a method of transferring data from the database into the cells of a spreadsheet file comprising the steps of: i) creating a spreadsheet mask file in the spreadsheet program by inserting commands in selected cells of a spreadsheet which represent the data fields of matters which are to be transferred to the selected cells; and ii) processing the spreadsheet mask file to produce a file in which the commands are replaced with data from the selected data fields of the selected matters.

According to a further aspect of the invention there is provided a data processing system for extracting data from a database and formatting data as specified in a mask file, the data processing system reading a mask file to identify commands in the mask file and replacing the commands with data from the fields of the database as specified by the commands, and formatting the transferred data into the same format as the commands were in the mask file to produce an output file which may be directly processed in a spreadsheet program.

Preferably a unique tag character is inserted before each command, and an output file is produced which can be directly processed by the spreadsheet program.

Further aspects of the invention will become apparent from the following description which is given by way of example with reference to the accompanying drawings in which: Figure 1: Shows the data fields of an account according to one embodiment of the invention.

Figure 2: Shows a mask created in Lotustm.

Figure 3: Shows some of the data values for the accounting database.

Figure 4: Shows the spreadsheet generated by processing the mask with the accounting data base.

The present invention enables data from a database to be imported into a spreadsheet file as directed by commands stored in a spreadsheet mask file, allowing subsequent processing of the output file by the spreadsheet program. The method of the invention may be performed using standard data processing equipment, such as an IBM personal computer.

The method of the present invention involves creating a spreadsheet mask file in a spreadsheet program in which commands are placed in selected cells. The spreadsheet mask file is then processed in conjunction with the database to produce an output file which is directly usable in the spreadsheet program. As the output file is in the same format as the commands were in the spreadsheet mask file it can be ensured that the output file is in a suitable format to be directly processed by the spreadsheet program.

The invention will now be described by way of example to an accounting application. It is to be appreciated however that the invention is not limited to accounting applications and can be utilised wherever it is necessary to import data into the cells of a spreadsheet according to instructions stored in selected cells.

Referring now to Figure 1, an account "n" (the "matter" in this example) is shown having a plurality of fields associated therewith.

The accounts may refer to the amount in bank accounts, the cost of advertising and promotion, computer expenses, power and gas expenses etc in accordance with usual accounting practice.

The full field names are given to the right of the field symbols. It will be noted that each field commences with a It is not essential that the sign is used. Any unique character tag which does not have a special meaning to the spreadsheet application may be used.

Any form of notation may be used to distinguish the various accounts, although it is preferred the accounts n be given numerical values.

According to the invention a spreadsheet mask file is created which contains commands requesting the transfer of data from any field of an accounting data base into specific cells within the spreadsheet.

Requests for data from specific fields of the accounting system are made by inserting reserved commands in selected cells in the place of the actual values which would otherwise be entered. For example "&AccNAME()" may be entered in place of an actual account name. Likewise, "&CBAL()" may be entered in place of the current account balance.

A sample mask is shown in figure 2. The left hand column "Account No." lists the account number. For example the first account number 41 is the "turnover account". The second account 4101 is the sub-account representing "sales revenue". The next column gives the "total last year". To the right of account 4101 is the command "&LyBal()" indicating that the last years ,C O 'J i j closing balance should be positioned here. The next column is the "account name" column.

Adjacent both accounts 41 and 4101 is the command "&AccNAME()". This indicates that the account name should be inserted in this position. In this example the account name for account 41 is "turnover" and the account name for account 4101 is "sales".

The next column is the actual current month balance. The command for account 4101 in this column is "&Ptd()" which indicates that the "period-to-date balance" should be inserted in this position.

The next column is the current month of budget. The command for account 4101 in this column is "&PtdBgt()" which indicates that the budget for the current financial period should be inserted in this position. The next column is percent of budget which indicates the percentage of the budget spent so far this month. This value is calculated from the actual current month expenditure and the budget for the current month columns. At present this value indicates an error as no numerical operands are present.

The next three columns look at the yearly rather than the monthly position. The first of these columns is the actual year to date expenditure. The command for account 4101 in this column is "&Ytd()" which indicates the year-to-date balance of this account should be inserted at this position. The next column is the budget for the year to date and in this column for account 4101 is the command "&YtdBgt()" which indicates the year-to-date budget for the current year should be inserted in this position.

Again the percentage of budget column indicates there is an error in calculation. This is because again no numeric values are present to be operated on.

It will be seen that likewise throughout the mask commands are given along the row of each account number indicating the information that should be inserted in the appropriate column. Cells are defined at the intersections of the rows and columns. In its present form no information about the actual accounts is given. The actual account information needs to be extracted from the accounting package.

According to the invention a first category of commands may be provided as follows: Command Meaning &AccName() Account Name &AccNo() Account Number These functions require only a single account number. Only a single value can be returned for each account.

J J Jt.l) / A second group of commands may be provided as follows: Command &AnnBGT() &LyPTD() &LyYTD() &PTDBGT() &YTDBGT() Meaning Last year's Period-To-Date balance Last year's Year-to-Date balance Budget for current financial period Year-to-date budget for current year Current financial year annual budget These commands will accept a range of account numbers. Accordingly, &PTD(5200...5210) will retrieve the period-to-date sum of all the accounts between 5200 to 5210. It will be appreciated however that more complex calculations may be calculated for other commands.

There may further be provided a third category of commands in which a range of both account numbers and periods may be selected. The commands provided in group three may be as follows: Command Meaning &BGT() Account budget for any financial period &PRD() &LyPRD() Account balance for any period Account balance for any period last year For example &BGT(1...5, 5201...5301) will return the value of the sum of accounts 5201 to 5301 for the period from period one to period 5.

Finally, commands may instruct the processing program to calculate values without transferring data from the database to the output file. For example a command may instruct the processing program to calculate a tax component.

Having created a spreadsheet mask, as herein described with reference to figure 2, processing is required to insert the actual account values in place of the commands in the spreadsheet mask. According to the invention the processing program searches for the unique character tag When this character tag is located the following characters are processed to see whether they belong to the set of allowed commands. When an allowed command is located it is interpreted and replaced with the required character or numeric value from the database.

For example if the command &BGT(1...5, 5201...5301) is processed it will be substituted with the sum of the accounts 5201 to 5301 for the period between period 1 and period 5. This processing continues until all of the commands have been replaced with the required character or numeric values. Upon completion the spreadsheet mask file may be modified and stored or a new file may be written having the same name as the original file but a different extension. It is preferred that a new output file is created so that the original spreadsheet mask file can be used again when the data changes. For example, in Lotus, the file \; < j t • r: u i Jt J t ) "example.WK1" may be processed and output as "example.WQ". In this way the mask file is unaltered and may be used again and again as the account information changes.

The output file generated by the processing program produces a file which can be used directly in the spreadsheet program. Numeric values are inserted in place of the commands so calculations to be performed by the spreadsheet program may be carried out on numerical data to produce calculated values in place of the "ERR" symbols shown in figure 2.

Figure 3 shows the data values for the commands/fields of the accounts 41, 4101, 51 and 5110 listed in Figure 2. Figure 4 shows the output in Lotus after processing the spreadsheet mask in combination with the data in the database.

The present invention may be used with any spreadsheet program. The user merely needs to create a mask in the spreadsheet program inserting the specified commands in place of actual characters or numerical values. The user needs no detailed knowledge of the accounting program. The user need only know the specified commands. The processing program will then process the mask and substitute character and numeric values in place of the commands so that the generated file can be used directly in the chosen spreadsheet program.

The generated file can be used directly in the spreadsheet program to produce printed output or visual displays including graphical output (dependent upon the particular application).

Although this invention has been described in relation to an accounting application it is to be appreciated that the system and method of the present invention have application in other fields. For example, technical information automatically recorded during the monitoring of an industrial process may be extracted into spreadsheet form to enable the production of reports showing the performance of the process.

Although this invention has been described by way of example and with reference to possible embodiments thereof it is to be appreciated that modifications or improvements may be made to the invention without departing from the scope of the invention as defined in the appended claims.

Claims (8)

WHAT WE CLAIM IS:

1. In a data processing system including a database in which data relating to one or more matters is allocated into data fields, a method of transferring data from the database into the cells of a spreadsheet file comprising the steps of: i) creating a spreadsheet mask file in the spreadsheet program by inserting commands in selected cells of a spreadsheet which represent values to be calculated and/or data fields of matters which are to be transferred to the selected cells; and ii) processing the spreadsheet mask file to produce a file in which the commands are replaced with calculated values and/or data from the selected data fields of the selected matters.

2. A method as claimed in claim 1 wherein each command is preceded by a unique tag character which has no specific meaning to the spreadsheet program.

3. A method as claimed in claim 2 wherein the unique tag character is the symbol &.

4. A method as claimed in claim 2 wherein the processing includes searching for occurrences of the unique tag character in the spreadsheet mask file and examining the characters after each occurrence of the unique tag character to see if they form a valid command. -14-

5. A method as claimed in claim 4 wherein the spreadsheet mask file is not altered during the processing and a unique output file is created in which the transferred data is stored in the format specified by the mask file.

6. A data processing system for extracting data from a database and formatting data as specified in a mask file, the data processing system reading a mask file to identify commands in the mask file and replacing the commands with a value calculated by the data processing system and/or data from the fields of the database as specified by the commands, and formatting the transferred data and/or calculated values into the same format as the commands were in the mask file to produce an output file which may be directly processed in a spreadsheet program.

7. A data processing system as claimed in claim 6 wherein a unique tag character is inserted before each command and the data processing system searches for occurrences of the unique tag character in the mask file and examines the characters following each occurrence of the unique tag character to see if they form a valid command.

8. A data processing system as claimed in claim 6 wherein at least one command instructs the data processing system to replace the command with a calculation of a number of fields of a given matter. -15- 10. A data processing system as claimed in claim 8 wherein at least one command provides instructions for the data processing system to substitute in its place a calculation for a number of matters for a number of fields. A method as claimed in claim 1 substantially as herein described with reference to the accompanying drawings. A data processing system as claimed in claim 6 substantially as herein described with reference to the accompanying drawings.