support

Stock Rise for 10 Consecutive Days

From the closing prices of several stocks this month, you are to find the stocks that have
been rising for 10 consecutive days. Let’s see how esProc manage it.

  1. Retrieve the raw data
    Retrieve data from StockRecords.txt

    A
    1 =file(“StockRecords.txt”).import@t()

    Cell value in A1:

  1. Add a calculation column to store the number of consecutive days to that day during which the stock keeps rising
    The calculation column UP will be used to store the number of consecutive days during which the stock keeps rising to that days, and currently the column value is null.

    A
    1 =file(“StockRecords.txt”).import@t()
    2 =A1.derive(:UP)

    Cell value of A2:

  1. Sort
    Sort the data by the stock code and dealing date

    A
    1 =file(“StockRecords.txt”).import@t()
    2 =A1.derive(:UP)
    3 =A2.sort(CODE,DT)

    Cell value of A3:

  1. Group
    Group the data by the stock code. Each group of data is the daily closing price of a certain stock in each day of a month. The efficient “direct grouping without sorting” is adopted in this step.

    A
    1 =file(“StockRecords.txt”).import@t()
    2 =A1.derive(:UP)
    3 =A2.sort(CODE,DT)
    4 =A3.group@o(CODE)

    Cell value of A4:

    In the figure, each row of data represents a group or a stock. For example, by clicking the stock 1001, you may display the details of group:

  1. Calculate the number of consecutive days of rising to that day
    In each group, calculate the consecutive days of rising to that day of each stock in each group, that is, find out the days during which period the stock keeps rising. In this case, the ~ represents the member of each group in A4. The algorithm is: If the closing price CL that day is lower than the closing price yesterday CL[-1], then the number of consecutive rising days to that day UP is 1; Conversely, if the closing price of that day is higher than the closing price yesterday, then the number of consecutive rising days to that day equals to number consecutive rising days till yesterday UP[-1] plus 1.

    A
    1 =file(“StockRecords.txt”).import@t()
    2 =A1.derive(:UP)
    3 =A2.sort(CODE,DT)
    4 =A3.group@o(CODE)
    5 =A4.run(~.run(UP=if(CL<=CL[-1],1,UP[-1]+1)))

    Cell value of A5. You may view the group first:

    As we can see from the above figure, the number of consecutive days for which a stock keeps rising to a certain day is calculated.

  1. Find out the stocks rising for 10 consecutive days
    Based on A5, filter out the stocks rising for 10 consecutive days. ~.max(UP) represents the maximum of consecutive days for which each stock keeps rising in that month.

    A
    1 =file(“StockRecords.txt”).import@t()
    2 =A1.derive(:UP)
    3 =A2.sort(CODE,DT)
    4 =A3.group@o(CODE)
    5 =A4.run(~.run(UP=if(CL<=CL[-1],1,UP[-1]+1)))
    5 =A5.select(~.max(UP)>10).(CODE)

    Cell value of A6. It is the desired result of the problem.

numskull813
landamman407 animalism364 undetermindtion905 hellhag238 fetich178 viticulture137 fabulist203