10.4 Get the first date and last date of a week
Find the first date and the last date of a week specified.
Suppose the current date is 2020/02/17 and we calculate the growth rate of the whole past week for SSE Composite Index. Below is part of the source data:
| Date | Open | Close | Amount |
|---|---|---|---|
| 2020/02/17 | 2924.9913 | 2983.6224 | 3.67E11 |
| 2020/02/14 | 2899.8659 | 2917.0077 | 3.08E11 |
| 2020/02/13 | 2927.1443 | 2906.0735 | 3.35E11 |
| 2020/02/12 | 2895.5561 | 2926.8991 | 2.98E11 |
| 2020/02/11 | 2894.5414 | 2901.6744 | 3.03E11 |
| … | … | … | … |
SPL has pdate(dateExp) function to get the first date and the last date of the week/month/quarter the specified date dateExp belongs to.
SPL script:
| A | |
|---|---|
| 1 | =file(“sh000001.csv”).import@cqt() |
| 2 | =A1.sort(Date) |
| 3 | =pdate@w(A2.m(-1).Date) |
| 4 | =A2.select@z1(Date<=A3-2) |
| 5 | =pdate@w(A4.Date) |
| 6 | =A2.select@z1(Date<=A5-2) |
| 7 | =A4.Close/A6.Close-1 |
A1 Import SSE Composite Index data.
A2 Group data by date.
A3 The pdate()function works with @w option to get the first date (which is a Sunday) of the week the specified date 2020/02/17 belongs to.
A4 Get the first record before the last Friday backwards, which is the last record of the past transaction week.
A5 Get the first date (Sunday) of the past transaction week.
A6 Get the first record before the last Friday before the past transaction week backwards, which is the last record of the week before the past transaction week.
A7 Calculate the growth rate.
Execution result:
| Value |
|---|
| 0.01427 |
SPL Official Website 👉 http://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc
SPL Learning Material 👉 http://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/ydhVnFH9
Youtube 👉 https://www.youtube.com/@esProc_SPL
