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.

SSEC.csv

Stock.csv