Share Price Rise on Five Consecutive Days

Target

Find shares whose prices have been rising on at least five consecutive days in a certain period based on daily trading data.

The picture below is the original data in Excel and the final computing result.

1. Data deletion

Import the Excel file and delete unnecessary columns.

2. Group by code

Right-click cell “A2″ and select “Quick operation – Group”.

The table after grouping is shown below:

Note: “Quick operation – Group” will automatically sort; or choose “Operation” for specific operations.3. Filter by date

Filter data according to the specified date range, which is between 1st June and 30th June. Please note that in esCalc such an operation could also be conducted in two steps: get the data of the shares after 1st June and then get the data of these shares before 30th June.

Firstly, right click on “B3″ and select “Operation – Filter” and enter a formula as follows:

Enter the formula only once as shown in the above picture, and all groups will be filtered. Now you get data after 1st June. You can find that esCalc enables data to be filtered after grouping, on which the conventional spreadsheets fail.

Secondly, Filter out data:

Step-by-step computing simplifies problems by avoiding onetime complex formula. The method is more user-friendly to business professionals without programming experience.

4. Calculate daily price increment

As you know, price increment = the current day’s closing price – the previous day’s closing price, which is an inter-row computation. First, append a column and enter the formula “=C4-C3″ in D4.

Then, we get the result below:

You can find that you need to enter the formula only in one cell to set it migrate to the other cells holding data of the same business significance (homocells) automatically and intelligently. For example, the formula in D19 has adjusted itself as C19-C18, as shown below:

But how does a formula migrate between groups, such as D26? Let’s check it.

It doesn’t work if you mechanically perform subtraction between C26 and the previous cell in the last group, but errors will arise. It is a common headache among many kinds of spreadsheet software that cross-group copying of formulas is error-prone.

esCalc can easily fix such a problem. Let’s retype a formula in “D4”, “=C4[A2]-C3[A2]”, we will get result as below:

The cross-group copying error is avoided. Note: [A2] specifies that the operation only works within the group where A2 stays.

You know that the right result in D26 is “C26-null=49.91”. Let’s select cell “D26” and check it:

The formula in “D26” becomes =C26[A25]-C24[A25]=C26[A25]-null=49.91. It’s correct!

The success of the above computation depends on the right formula migration, and esCalc makes it to achieving the correct result. The result is shown as below:

Let’s go on with the next step.

5. Calculate the number of days of continuously rising price

Append a column, and enter a formula in E4: =if(D4[A2]>0,E3[A2]+1. It means：If the current day’s closing price rises, then add “1” to yesterday’s figure of consecutive rising days to get the current day’s figure. Similarly, one entry of the formula will suffice for automatically copying it to the other homocells, as shown below.

Homocells are cells with the same business attribute. For example, E4, E5, E24 and E26 that hold detailed data are homocells, and C2 and C25 that contain summary data are also homocells:

The unique homocell model in esCalc makes complex computing with grouped data extraordinary easy whereas conventional spreadsheets require a great deal of manual operations for the same result.

6. Calculate the maximum number of continuously rising days

To get the maximum number of continuously rising days for different stock stored in homocells, simply enter the formula in any of the homocells and the results for all stocks will be automatically presented. Select, for example, cell “E2” at the summary level of the first stock and enter the formula: ={E3}.max(), then you can get the following result:

Note: {E3} means the homocells of E3, which include cells from E3 to E24.

You can click slave row number “1” to hide the subordinate data and observe the summary area more clearly.

Now, we can see easily which stocks had been rising over at least five consecutive days, including E25, E48, E71, E209 and E370 shown above.
You want to filter the desired data out from the summary area “automatically” with conventional spreadsheets? That will be hard!! But it’s easy with esCacl.

7. Filter data for desired stocks

Right click “E2”, select “Filter”, and enter@>=5 as below:

The filtered data is our target in this case.

As you can see, homocells can clearly differentiate the summary area from the detailed area. An operation on the summary area will not affect the detailed area. For this reason, esCalc can easily filter grouped data.

On the contrary, conventional spreadsheets can hardly differentiate data between the summary area and detailed area, which will cause chaos after filtering.