5.4 Reference the previous/next row in each group
SPL also allows referencing the previous or next record in each group.
We have a stock data table and trying to find the daily growth rate for each stock. Below is part of the table:
| DATE | CODE | NAME | CLOSE | OPEN | AMOUNT |
|---|---|---|---|---|---|
| 2020/01/02 | 000001 | SS | 3085.1976 | 3066.3357 | 3.27197122606E11 |
| 2020/01/03 | 000001 | SS | 3083.7858 | 3089.022 | 2.89991708382E11 |
| 2020/01/06 | 000001 | SS | 3083.4083 | 3070.9088 | 3.31182549906E11 |
| 2020/01/07 | 000001 | SS | 3104.8015 | 3085.4882 | 2.88159227657E11 |
| 2020/01/08 | 000001 | SS | 3066.8925 | 3094.2389 | 3.06517394459E11 |
| … | … | … | … | … | … |
SPL script:
| A | |
|---|---|
| 1 | =T(“Stock.csv”) |
| 2 | =A1.sort(DATE) |
| 3 | =A2.group(CODE) |
| 4 | =A3.(~.derive(CLOSE/CLOSE[-1]:INCREASE)).conj() |
A1: Import the stock data table.
A2: Use sort() function to sort the data table by DATE.
A3: Group the data table by stock code.
A4: Loop each stock to calculate growth rate of the current price and the previous price for each day, and then concatenate all records.
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProcSPL
SPL Learning Material 👉 https://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProc_SPL
