Here, we use esProc to analyze the upward trend in stock prices. There is a variety of algorithms, of which one will be illustrated in detail. Other cases can be handled using the similar codes. Specific requirements are:
Among the stocks which have kept going up for N days, what is the proportion of those that continue to rise for another day?
The key to solve this problem is to compute the maximal days when each stock kept rising (hereinafter referred to as Rising Days). Divide the number of stocks where the Rising Days is greater than or equal to n +1 by the number of stocks where the Rising Days is greater than or equal to n days, this is the rate we evaluate.
Stock data must include three fields: Transaction day, Closing price and Stock codes. Therefore, the data source may like this:
Stock Date Price
120089 2009-01-01 00:00:00 50.24
120123 2009-01-01 00:00:00 10.35
120136 2009-01-01 00:00:00 43.37
120141 2009-01-01 00:00:00 41.86
120170 2009-01-01 00:00:00 194.63
120243 2009-01-01 00:00:00 15.75
120319 2009-01-01 00:00:00 1.36
120343 2009-01-01 00:00:00 20.95
120344 2009-01-01 00:00:00 232.38
120355 2009-01-01 00:00:00 31.6
120414 2009-01-01 00:00:00 244.32
120439 2009-01-01 00:00:00 181.36
120484 2009-01-01 00:00:00 6.27
120528 2009-01-01 00:00:00 43.86
120533 2009-01-01 00:00:00 227.91
120584 2009-01-01 00:00:00 25.12
120607 2009-01-01 00:00:00 36.36
120626 2009-01-01 00:00:00 29.36
120630 2009-01-01 00:00:00 29.77
120642 2009-01-01 00:00:00 30.98
…
The natural way of computing the maximum of Rising Days: Sort closing prices of each stock are by Transaction dates, and compare each closing price against the previous one in sequence. If the current closing price is higher than the previous one, then add 1 to the Rising Days; if not higher, reset the Rising Days to 0.
We find that programming idea of esProc is just a natural one. Therefore, the code written in esProc is more intelligible, and easy to maintain. Code is shown as follows:
|
|
A |
B |
C |
|
1 |
>n=2 | ||
|
2 |
=file(“stock.txt”).import@t() | /import data from txt file | |
|
3 |
=A2.group(stock) | /group by stock IDS | |
|
4 |
=create(stock,days) | /create an empty result table sequence | |
|
5 |
for A3 | /loop through each group | |
|
6 |
=A5.stock | /stock IDs | |
|
7 |
=0 | /Rising Days | |
|
8 |
=A5.sort(date) | ||
|
9 |
=B8.run(B7=if(price>price[-1],B7+1,0)) | /calculate Rising Days | |
|
10 |
=A4.insert(0,B6,B7) | / insert the loop results to A4 | |
|
11 |
=A4.count(days>=n+1)/A4.count(days>=n) | /calculate the proportion | |
Description:
1. In cell A1, specify the input value of n as 2 days. If this is an external call, you can also pass it through the parameter.
2. In cell A2, import the source data from txt file. If necessary, you can also retrieve it from the database or from HDFS.
3. In cell A3, group the data by stock IDs. After grouping, each group corresponds to one stock.
4. Create an empty table sequence to store the Rising Days for each stock.
5. In A5, loop through stock groups one by one, that is, handle one group, or a stock, at a time. Loop body is from B6 to B10.
6. In B6, get stock ID of the first record in the current group; B7 sets an initial value 0 for Rising Days.
7. B8 sorts data in the current group by transaction dates.
8. B9 does loop one by one within the current group to determine if a closing price is higher than the previous one. If higher, add 1 to B7; otherwise reset B7 to 0.
9. B10 appends computed stock ID and Rising Days to A4, which means the loop ends.
10. In A11, divide the number of stocks in A4 where the Rising Days is greater than or equal to n +1 by the number of stocks where the Rising Days is greater than or equal to n days, this is the rate we evaluate.
As we can see, the code that is in line with the natural way of thinking can diminish the difference between thinking modes of the human brain and the code’s logic, thus to reduce the difficulty of writing and maintaining code, improve efficiency and reduce costs.
After the programmers are familiar with esProc programming ideas, they will be able to write more concise codes, as shown below:
|
|
A |
B |
|
1 |
>input=2 | |
|
2 |
=file(“stock.txt”).import@t() | /import data from txt file |
|
3 |
=A2.group(stock).run(~=~.sort(date)) | /group data by stock IDS and sort each group by dates |
|
4 |
=A3.new(stock,0:days) | /create an empty result table sequence |
|
5 |
=A4.run(A3(#).run(A4.days=if(price>price[-1],A4.days+1,0))) | /use loop function to calculate |
|
6 |
=A5.count(days>=input+1)/A5.count(days>=input) | / calculate the proportion |