SPL: adjacent record reference
To calculate data that are arranged in a certain order, we often encounter cross-row references. For example, calculate the increase of stock price compared to the previous day, the moving average price of a stock for 20 days and so on.
SPL supports references to adjacent records by their relative positions in loop calculations.
[e.g. 1] Calculate the increase of the closing price per day of the Shanghai composite index compared to the previous day. Some of the Shanghai composite index data are as follows:
DATE |
CLOSE |
OPEN |
VOLUME |
AMOUNT |
2020/01/02 |
3085.1976 |
3066.3357 |
292470208 |
3.27197122606E11 |
2020/01/03 |
3083.7858 |
3089.022 |
261496667 |
2.89991708382E11 |
2020/01/06 |
3083.4083 |
3070.9088 |
312575842 |
3.31182549906E11 |
2020/01/07 |
3104.8015 |
3085.4882 |
276583111 |
2.88159227657E11 |
2020/01/08 |
3066.8925 |
3094.2389 |
297872553 |
3.06517394459E11 |
… |
… |
… |
… |
… |
The SPL script looks like this:
A |
|
1 |
=T("SSEC.csv") |
2 |
=A1.sort(DATE) |
3 |
=A2.derive(CLOSE/CLOSE[-1]:INCREASE) |
A1: import the Shanghai Composite Index table.
A2: use the sort function to sort the data by date.
A3: calculate the increase of daily stock price compared to the previous day, in which CLOSE[-1] represents the price of the previous day. The derive function is used to add computed columns to the ordered table.
We can use the interval of relative positions to refer to the sequence of adjacent records.
[e.g. 2] List the average 20-day closing price of the Shanghai composite index for each day from January 1st to 10th, 2020. Some of the Shanghai composite index data are as follows:
DATE |
CLOSE |
OPEN |
VOLUME |
AMOUNT |
2020/01/02 |
3085.1976 |
3066.3357 |
292470208 |
3.27197122606E11 |
2020/01/03 |
3083.7858 |
3089.022 |
261496667 |
2.89991708382E11 |
2020/01/06 |
3083.4083 |
3070.9088 |
312575842 |
3.31182549906E11 |
2020/01/07 |
3104.8015 |
3085.4882 |
276583111 |
2.88159227657E11 |
2020/01/08 |
3066.8925 |
3094.2389 |
297872553 |
3.06517394459E11 |
… |
… |
… |
… |
… |
A |
|
1 |
=T("SSEC.csv") |
2 |
=A1.sort(DATE) |
3 |
=A2.pselect@a(DATE>=date("2020/01/01")&& DATE<=date("2020/01/10")) |
4 |
=A2(A3).derive(A2.calc(A3(#),avg(CLOSE[-19:0])):MA20) |
A1: import the Shanghai Composite Index table.
A2: use the sort function to sort the data by date.
A3: select the row numbers of the records from January 1st to 10th, 2020.
A4: calculate the average 20-day closing price for each day by loop operation based on the selected row numbers, in which CLOSE[-19:0] represents the sequence of closing price for the first 19 days and the current day.
SPL also supports adjacent record references in group.
[e.g. 3] Calculate the daily increase of each stock among multiple stocks. Some of the stock market data are as follows:
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 |
… |
… |
… |
… |
… |
… |
The SPL script looks like this:
A |
|
1 |
=T("Stock.csv") |
2 |
=A1.sort(DATE) |
3 |
=A2.group(CODE) |
4 |
=A3.(~.derive(CLOSE/CLOSE[-1]:INCREASE)).conj() |
A1: import stock data table.
A2: use the sort function to sort the data by date.
A3: group stock data by stock code.
A4: loop through each stock and calculate its increase of daily price compared to the previous day, and concatenate the records at last.
[e.g. 4] Calculate the trading volume increase of each stock’s maximum closing price compared to the previous day. Some of the stock market data are as follows:
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 |
… |
… |
… |
… |
… |
… |
The SPL script looks like this:
A |
|
1 |
=T("Stock.csv") |
2 |
=A1.sort(DATE) |
3 |
=A2.group(CODE) |
4 |
=A3.new(CODE,NAME,(p=~.pmax(CLOSE),~.calc(p,AMOUNT/AMOUNT[-1])):INCREASE) |
A1: import stock data table.
A2: use the sort function to sort the data by date.
A3: group stock data by stock code.
A4: create a table sequence based on the grouped stock market data. Loop through the row number where the record of the highest closing price of each stock is located and calculate the increase in trading volume of the current day and the previous day.
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_SPL
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