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