Stocks Hitting Their Daily Limits for 3 Consecutive Days in a Month
l Problem
The following is the record of daily closing prices of a stock exchange in a month, in which the CODE column contains the stock codes, DT is the date, and CL is the closing price level.
Please select out the stocks whose prices rise to their daily limits for 3 consecutive days in the month shown in the table. In order to avoid the error caused by rounding off, the rate of stock price increase is set as 9.5%.
l Tip
General steps: Sort the records by code and date, and group them by the stock code. Thus, you will get a monthly price list of a stock. In this way, you can easily compute the rate of ups and downs for each stock per day. By comparing the rate of ups and downs and the daily limit, you can judge if the price of a stock rises to its daily limit and find out the stocks hitting their daily limits for 3 consecutive days.
1. First, add a field to each record in the journal table to record the rate of ups and downs computed by comparing the current price with that of the previous day. Values of this field are left empty temporarily.
2. Sort the table by code and date. The purpose is to guarantee that data is sorted by date after the sequence is grouped in the following step.
3. Group the table by stock code. Because the table is already sorted by code, no sorting will be performed again.
4. For each record in each group, work out the rate of ups and downs. Please note if this is the first record in the group, then there will be no previous record. The rate shall be taken as 0.
5. Select the stocks that have hit their daily limit during a consecutive 3 days, and retrieve their codes.
l Code
|
|
A |
|
|
1 |
=file("C:\\txt\\StockRecords.txt").import@t() |
Stock exchange journal |
|
2 |
0.095 |
Limit of rising-up |
|
3 |
=A1.derive(:UP) |
Add a column to record the ranges of ups and downs |
|
4 |
=A3.sort(CODE,DT) |
Sort the table by code and date |
|
5 |
=A4.group@o(CODE) |
Group by code |
|
6 |
=A5.run(~.run(UP=if(#==1,0,(CL-CL[-1])/CL[-1]))) |
Work out the rate of ups and downs, and write it to the UP field |
|
7 |
=A6.select(~.count(UP>A2 && UP[-1]>A2 && UP[-2]>A2)>0).(CODE) |
Select out the codes of stocks rising to its limit for consecutive 3 days for once and above times |
l Result
