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.

esCalc_demo_share_price_1

1. Data deletion

Import the Excel file and delete unnecessary columns.

esCalc_demo_share_price_2

2. Group by code

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

esCalc_demo_share_price_3

The table after grouping is shown below:

esCalc_demo_share_price_4

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:

esCalc_demo_share_price_5

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:

esCalc_demo_share_price_6

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.

esCalc_demo_share_price_7

Then, we get the result below:

esCalc_demo_share_price_8

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:

esCalc_demo_share_price_9

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

esCalc_demo_share_price_10

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:

esCalc_demo_share_price_11

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:

esCalc_demo_share_price_12

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:

esCalc_demo_share_price_13

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.

esCalc_demo_share_price_14

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:

esCalc_demo_share_price_15

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:

esCalc_demo_share_price_16

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.

esCalc_demo_share_price_17

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:

esCalc_demo_share_price_18

The filtered data is our target in this case.

esCalc_demo_share_price_19

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.