Install-and-Use Desktop Data Calculator

The basic function of a calculator is computing, which can be as simple as the four arithmetic operations and also as complex as the calculation for the next move in chess with Deep Blue. Among these, esCalc is a desktop data calculation tool for the business users to handle the occasional, complex, and business-related data computation. Such tools are named as the desktop BI software.
For example, a stock analyst is required to recommend some stocks to the clients urgently. From all calculations involved, the analyst needs to find shares from the daily trading stocks which had risen for 5 consecutive days in previous month. With esCalc, he can open the data calculator, imports the daily data of the more than 20 stocks, monitors and analyzes the data, works out the outline of algorithms, and then groups, summarizes, sorts, filters and takes other possible operations to calculate the results with some simple formulas. At last, he gets the result and makes the recommendation successfully.

The similar calculations also include:

For all clients of the insurance company, what’s the average insurance price of those who bought the basic insurance first and then bought the additional insurance?
In the 3 months with the most client complaints, find out the top 3 products with the highest defective rate.
For the top N sales persons who achieved the 50% of the total sales for the company, what are their respective sales proportions to that of their respective sales team?

esCalc is the plug-and-use desktop data calculator with the powerful computational capability for business users to grasp easily, owing to the advantages below:

1.Typical Desktop Application

The installer of esCalc is only dozens of MB. The installation procedure only requires a few clicks and can be run immediately after installation. As a JAVA application running on the Windows desktop, esCalc can run on most office computers independently without having to deploy the extra server additionally.
esCalc resembles Excel in UIs that is easy to learn and grasp. The overall interface is shown as the below figure:
esCalc desktop bi 1

The Data Computation section is shown below:
esCalc desktop bi 2

esCalc is especially designed for the business users without technical background. esCalc can be installed in a common working environment easily, and put to use once installed.

2.Various Data Sources Support

esCalc supports various databases, including MSSQL, Oracle, Access, MySQL, DB2, Sybase, and other mainstream databases. In facts, esCalc supports any databases with JDBC and ODBC drivers.

Besides the access to the database in the LAN, esCalc also supports the access to the local data file, such as txt, log, tab, other text files, and the Excel 97~2010 spreadsheets.

esCalc also supports the interactive calculations between various data sources, for example, store the basic information like the company name, the contact information, and the company industry in the database, and store the follow-up visit to some clients by sales persons in another Excel spread sheet. With esCalc, you can merge the two pieces of data easily to form a follow-up visit log classified by the company industry. Even if their Number of clients and the Sort Criteria are different from each other, esCalc can handle it easily.

The office environment of business users is sometimes rather complex, such as the CRM, MIS, ERP, DSS or performance environments, supply chain management, and other application systems. esCalc supports various data sources and interactive calculations and is able to handle the complex office environment.

3.Step-by-step calculation

The step-by-step calculation can decompose the complex computational goal into several simple steps and solve an apparently-complex problem by settling each relatively simple problem.

Still with the above example, in order to calculate the stock rising for consecutive 5 days, you can group by stock first, and then calculate the daily increment of the stock. An increment greater than 0 indicates the stock is rising. Based on this judgment, the consecutive rising days of each stock can be calculated out. Lastly, the longest consecutive rising days of each stock can be calculated through filtering and sorting. The details are shown below:

Step 1: Import the stock data from txt file, as shown in the below figure:
esCalc desktop bi 3

Step 2: Filter out the data in the previous month by date. Suppose it is June in 2011, as shown in the below figure:
esCalc desktop bi 4

Step 3: set the level by the stock code:
esCalc desktop bi 5

In the above figure, the newly-built level is in the red block on the left, and in the red circle on the right is a stock. This row is the summery row.

Step 4: Sort the transaction data of each stock by Date ascendingly.
esCalc desktop bi 6

Step 5: Calculate the daily growth rate of each stock. In this step, a calculation column D is added, and a formula =(C4[A2]-C3[A2])/C3[A2] is input to the D4. The formula will be pasted to the respective cell automatically, as shown in the below figure:
esCalc desktop bi 6

Step 6: Compute the consecutive rising days. In this step, a computation column E is to be newly added, and then enter the formula in E4: =if(D4>0,E3+1). The result is shown in the below figure:
esCalc desktop bi 6

Step 7: Calculate the longest days of a certain stock rising consecutively by entering the formula like max({E4}) into the summary row in E2. The result is shown below:
esCalc desktop bi 7

Step 8: Fold the summary row by clicking on the level number 1 on the left, as shown in below figure:
esCalc desktop bi 8

Step 9: Sort by the longest consecutive rising days, as shown in below figure:
esCalc desktop bi 9

In the above figure, 5 stocks keep rising for 5 consecutive days in June 2011, which are American Express Co., The Boeing Co., Citigroup, Inc., General Motors Corp, and Coca-Cola Co. It is certain that the data can also be filtered by the longest consecutive rising days, as shown in the below figure:
esCalc desktop bi 10

To solve the relatively complex computational goal in this case, we divide the computational goal into 9 steps of simple operation or formula computing.

The step-by-step computation allows users to decompose, simplify, and ultimately solve problems in a rather intuitive train of thoughts. Owing to this, business users can also solve some complex data computing problems by themselves.

4. Adequate data computational capability
esCalc is powerful enough to handle the various computational task in the daily office work.

Function as SQL in every aspect

With the same computational capability as SQL, esCalc can be used to filter, group, sort, and perform the distinct, union, join, and other equivalent actions like SQL. Please refer to the below menu items shown in the below figure:
esCalc desktop bi 12

No technical background required

SQL is the tool that only the professional technician could grasp, while esCalc represents SQL functions with graphics and decomposes it into several steps so that even the business users with no technical experiences can handle it easily. For example, in this case, to perform the step 3 of “building level by stock code”, esCalc does not require any complex coding, and users only need to set it up in the menu easily.
esCalc desktop bi 13

Alternatively, just use the default shortcut menu, as shown in below figure:
esCalc desktop bi 14

Because SQL does not support the step-by-step calculation, SQL solution for this case will be lengthy, error-prone, and hard-to-understand. Obviously, it is hard for the business users to grasp.

Computational capability beyond Excel

The Excel and other tools alike do not provide the auto copy and intelligent adjustment functions. The similar functions can only be implemented with a great amount of manual operations. For example, the longest consecutive rising days of each stock, esCalc users can enter the formula =max({E4}) in E2 directly, as shown in the below figure:
esCalc desktop bi 15

After entering the formula in E2, all homocells of E2 will be populated with the formula, such as E94. By comparison, Excel does not provide the auto-copy function, and you will have to conduct it manually. In addition to the auto-copy, esCalc also support the intelligent adjustment function, for example, the formula in E94 will be adjusted to =max({E96}) to meet the business logics, as shown in the below figure:
esCalc desktop bi 16

In addition, the homo-cell model maintains the business relations between esCalc cells, so that the true grouping is implemented, and the grouped data can be further processed. This is hard to implement with the Excel and other tools alike, for example, in the step 4 of the above case, “sort the dealing data of each stock by Date ascendingly”. For Excel, ungrouping is required to sort the data by stock & date and then group. However, with esCalc, you can sort directly to implement it.

More computational capabilities
esCalc can be used to perform the complex computations related to sequence numbers, still the above example, to proceed to calculate the rankings of closing price of the end of previous month on the basis of the result in step 8.

Firstly, calculate the closing price in the end of month, then append a new column F, and enter the new formula in F2: ={C3}.m(-1), as shown in the below figure:
esCalc desktop bi 17

Then calculate the rankings. Append the new column G, and input the formula ={F2}.ranki(F2) in G2, as shown in the below figure:
esCalc desktop bi 18

As can be seen from above, 9N12584 (i.e. IBM) has the highest closing prices among these shares of more than 20.
esCalc can also perform the intersection, union, compliment, and other set operations, for example, to compute the stocks that are among the last 10th (cheaper) by closing price and rising for consecutive 4 days. esCalc can also be used to perform the inter-row computations such as monthly year-on-year comparison and the link relative ratio comparison, for example, to compute the stock price moving averages in the 5 days.
In conclusion, esCalc is a typical desktop application which is able to support multiple data sources and step-by-step computation with sufficient data computational capability. It is the data computation tools for business users to handle the various computational problems in the daily office work easily.