量价技术因子构建（2）

声明：以下代码请在 AIStudio 3.0.0 环境下运行

In [1]:
import dai
import pandas as pd
from datetime import datetime, timedelta 

sd = '2021-01-01'
ed = datetime.now().date().strftime("%Y-%m-%d")

今日收盘价在N日内的相对价差

N：一般取值为5、20、60、120、250

In [2]:
n = 5 

sql = f"""
SELECT 
    date,  
    instrument,  
    (close - m_min(low, {n})) / (m_max(high, {n}) - m_min(low, {n}) + 1e-12) AS factor 
FROM cn_stock_bar1d 
ORDER BY date, instrument
"""

dai.query(sql, filters={'date':[sd, ed]}).df()
Out[2]:
date instrument factor
0 2021-01-04 000001.SZ NaN
1 2021-01-04 000002.SZ NaN
2 2021-01-04 000004.SZ NaN
3 2021-01-04 000005.SZ NaN
4 2021-01-04 000006.SZ NaN
... ... ... ...
3853667 2024-04-12 873703.BJ 0.478673
3853668 2024-04-12 873706.BJ 0.460905
3853669 2024-04-12 873726.BJ 0.568306
3853670 2024-04-12 873806.BJ 0.013889
3853671 2024-04-12 873833.BJ 0.224138

3853672 rows × 3 columns

N日内最高价的相对索引值

N：一般取值为5、20、60、120、250

In [3]:
n = 5 

sql = f"""
SELECT 
    date,  
    instrument,  
    m_imax(high, {n})/{n} AS factor 
FROM cn_stock_bar1d 
ORDER BY date, instrument
"""

dai.query(sql, filters={'date':[sd, ed]}).df()
Out[3]:
date instrument factor
0 2021-01-04 000001.SZ NaN
1 2021-01-04 000002.SZ NaN
2 2021-01-04 000004.SZ NaN
3 2021-01-04 000005.SZ NaN
4 2021-01-04 000006.SZ NaN
... ... ... ...
3853667 2024-04-12 873703.BJ 0.4
3853668 2024-04-12 873706.BJ 0.8
3853669 2024-04-12 873726.BJ 0.6
3853670 2024-04-12 873806.BJ 0.4
3853671 2024-04-12 873833.BJ 0.0

3853672 rows × 3 columns

N日内最低价的相对索引值

N：一般取值为5、20、60、120、250

In [4]:
n = 5 

sql = f"""
SELECT 
    date,  
    instrument,  
    m_imin(low, {n})/{n} AS factor 
FROM cn_stock_bar1d 
ORDER BY date, instrument
"""

dai.query(sql, filters={'date':[sd, ed]}).df()
Out[4]:
date instrument factor
0 2021-01-04 000001.SZ NaN
1 2021-01-04 000002.SZ NaN
2 2021-01-04 000004.SZ NaN
3 2021-01-04 000005.SZ NaN
4 2021-01-04 000006.SZ NaN
... ... ... ...
3853667 2024-04-12 873703.BJ 0.2
3853668 2024-04-12 873706.BJ 0.2
3853669 2024-04-12 873726.BJ 0.2
3853670 2024-04-12 873806.BJ 0.8
3853671 2024-04-12 873833.BJ 0.2

3853672 rows × 3 columns

N日内最高最低价的相对时间距离

N：一般取值为5、20、60、120、250

In [5]:
n = 5 

sql = f"""
SELECT 
    date,  
    instrument,  
    (m_imax(high, {n})-m_imin(low, {n}))/{n} AS factor 
FROM cn_stock_bar1d 
ORDER BY date, instrument
"""

dai.query(sql, filters={'date':[sd, ed]}).df()
Out[5]:
date instrument factor
0 2021-01-04 000001.SZ NaN
1 2021-01-04 000002.SZ NaN
2 2021-01-04 000004.SZ NaN
3 2021-01-04 000005.SZ NaN
4 2021-01-04 000006.SZ NaN
... ... ... ...
3853667 2024-04-12 873703.BJ 0.2
3853668 2024-04-12 873706.BJ 0.6
3853669 2024-04-12 873726.BJ 0.4
3853670 2024-04-12 873806.BJ -0.4
3853671 2024-04-12 873833.BJ -0.2

3853672 rows × 3 columns

N日量价相关性

N：一般取值为5、20、60、120、250

In [6]:
n = 5 

sql = f"""
SELECT 
    date,  
    instrument,  
    m_corr(close, volume, {n}) AS factor 
FROM cn_stock_bar1d 
ORDER BY date, instrument
"""

dai.query(sql, filters={'date':[sd, ed]}).df()
Out[6]:
date instrument factor
0 2021-01-04 000001.SZ NaN
1 2021-01-04 000002.SZ NaN
2 2021-01-04 000004.SZ NaN
3 2021-01-04 000005.SZ NaN
4 2021-01-04 000006.SZ NaN
... ... ... ...
3853667 2024-04-12 873703.BJ 0.172612
3853668 2024-04-12 873706.BJ 0.986417
3853669 2024-04-12 873726.BJ 0.914539
3853670 2024-04-12 873806.BJ 0.574373
3853671 2024-04-12 873833.BJ 0.814136

3853672 rows × 3 columns

N日量价隔夜涨跌幅相关性

N：一般取值为5、20、60、120、250

In [7]:
n = 5 

sql = f"""
SELECT 
    date,  
    instrument,  
    m_corr(close/m_lag(close, 1), log(volume/m_lag(volume, 1)+1), {n}) AS factor 
FROM cn_stock_bar1d 
ORDER BY date, instrument
"""

dai.query(sql, filters={'date':[sd, ed]}).df()
Out[7]:
date instrument factor
0 2021-01-04 000001.SZ NaN
1 2021-01-04 000002.SZ NaN
2 2021-01-04 000004.SZ NaN
3 2021-01-04 000005.SZ NaN
4 2021-01-04 000006.SZ NaN
... ... ... ...
3853667 2024-04-12 873703.BJ -0.039575
3853668 2024-04-12 873706.BJ 0.870171
3853669 2024-04-12 873726.BJ 0.646248
3853670 2024-04-12 873806.BJ 0.176848
3853671 2024-04-12 873833.BJ 0.197784

3853672 rows × 3 columns

N日上涨天数占比

N：一般取值为5、20、60、120、250

In [8]:
n = 5 

sql = f"""
SELECT 
    date,  
    instrument,  
    m_avg(IF(close>m_lag(close, 1), 1, 0), {n}) AS factor 
FROM cn_stock_bar1d 
ORDER BY date, instrument
"""

dai.query(sql, filters={'date':[sd, ed]}).df()
Out[8]:
date instrument factor
0 2021-01-04 000001.SZ NaN
1 2021-01-04 000002.SZ NaN
2 2021-01-04 000004.SZ NaN
3 2021-01-04 000005.SZ NaN
4 2021-01-04 000006.SZ NaN
... ... ... ...
3853667 2024-04-12 873703.BJ 0.4
3853668 2024-04-12 873706.BJ 0.6
3853669 2024-04-12 873726.BJ 0.6
3853670 2024-04-12 873806.BJ 0.4
3853671 2024-04-12 873833.BJ 0.2

3853672 rows × 3 columns

N日下跌天数占比

N：一般取值为5、20、60、120、250

In [9]:
n = 5 

sql = f"""
SELECT 
    date,  
    instrument,  
    m_avg(IF(close<m_lag(close, 1), 1, 0), {n}) AS factor 
FROM cn_stock_bar1d 
ORDER BY date, instrument
"""

dai.query(sql, filters={'date':[sd, ed]}).df()
Out[9]:
date instrument factor
0 2021-01-04 000001.SZ NaN
1 2021-01-04 000002.SZ NaN
2 2021-01-04 000004.SZ NaN
3 2021-01-04 000005.SZ NaN
4 2021-01-04 000006.SZ NaN
... ... ... ...
3853667 2024-04-12 873703.BJ 0.4
3853668 2024-04-12 873706.BJ 0.4
3853669 2024-04-12 873726.BJ 0.4
3853670 2024-04-12 873806.BJ 0.6
3853671 2024-04-12 873833.BJ 0.6

3853672 rows × 3 columns

N日上涨下跌天数占比之差

N：一般取值为5、20、60、120、250

In [10]:
n = 5 

sql = f"""
SELECT 
    date,  
    instrument,  
    m_avg(IF(close>m_lag(close, 1), 1, 0), {n}) - m_avg(IF(close<m_lag(close, 1), 1, 0), {n}) AS factor 
FROM cn_stock_bar1d 
ORDER BY date, instrument
"""

dai.query(sql, filters={'date':[sd, ed]}).df()
Out[10]:
date instrument factor
0 2021-01-04 000001.SZ NaN
1 2021-01-04 000002.SZ NaN
2 2021-01-04 000004.SZ NaN
3 2021-01-04 000005.SZ NaN
4 2021-01-04 000006.SZ NaN
... ... ... ...
3853667 2024-04-12 873703.BJ 0.0
3853668 2024-04-12 873706.BJ 0.2
3853669 2024-04-12 873726.BJ 0.2
3853670 2024-04-12 873806.BJ -0.2
3853671 2024-04-12 873833.BJ -0.4

3853672 rows × 3 columns

N日收盘价总收益比绝对总价

N：一般取值为5、20、60、120、250

In [11]:
n = 5 

sql = f"""
SELECT 
    date,  
    instrument,  
    m_sum(IF(close-m_lag(close, 1)>0, close-m_lag(close, 1), 0), {n}) / (m_sum(abs(close-m_lag(close, 1)), {n}) + 1e-12) AS factor 
FROM cn_stock_bar1d 
ORDER BY date, instrument
"""

dai.query(sql, filters={'date':[sd, ed]}).df()
Out[11]:
date instrument factor
0 2021-01-04 000001.SZ NaN
1 2021-01-04 000002.SZ NaN
2 2021-01-04 000004.SZ NaN
3 2021-01-04 000005.SZ NaN
4 2021-01-04 000006.SZ NaN
... ... ... ...
3853667 2024-04-12 873703.BJ 0.440415
3853668 2024-04-12 873706.BJ 0.668269
3853669 2024-04-12 873726.BJ 0.624217
3853670 2024-04-12 873806.BJ 0.259843
3853671 2024-04-12 873833.BJ 0.302326

3853672 rows × 3 columns

N日收盘价总损失比绝对总价

N：一般取值为5、20、60、120、250

In [12]:
n = 5 

sql = f"""
SELECT 
    date,  
    instrument,  
    m_sum(IF(m_lag(close, 1)-close < 0, 0, m_lag(close, 1)-close), {n}) / (m_sum(abs(close-m_lag(close, 1)), {n}) + 1e-12) AS factor 
FROM cn_stock_bar1d 
ORDER BY date, instrument
"""

dai.query(sql, filters={'date':[sd, ed]}).df()
Out[12]:
date instrument factor
0 2021-01-04 000001.SZ NaN
1 2021-01-04 000002.SZ NaN
2 2021-01-04 000004.SZ NaN
3 2021-01-04 000005.SZ NaN
4 2021-01-04 000006.SZ NaN
... ... ... ...
3853667 2024-04-12 873703.BJ 0.559585
3853668 2024-04-12 873706.BJ 0.331731
3853669 2024-04-12 873726.BJ 0.375783
3853670 2024-04-12 873806.BJ 0.740157
3853671 2024-04-12 873833.BJ 0.697674

3853672 rows × 3 columns

N日收益损失差比绝对总价

N：一般取值为5、20、60、120、250

In [13]:
n = 5 

sql = f"""
SELECT 
    date,  
    instrument,  
    m_sum(IF(m_lag(close, 1)-close > 0, m_lag(close, 1)-close, 0), {n}) / (m_sum(abs(close-m_lag(close, 1)), {n})+1e-12) AS factor 
FROM cn_stock_bar1d 
ORDER BY date, instrument
"""

dai.query(sql, filters={'date':[sd, ed]}).df()
Out[13]:
date instrument factor
0 2021-01-04 000001.SZ NaN
1 2021-01-04 000002.SZ NaN
2 2021-01-04 000004.SZ NaN
3 2021-01-04 000005.SZ NaN
4 2021-01-04 000006.SZ NaN
... ... ... ...
3853667 2024-04-12 873703.BJ 0.559585
3853668 2024-04-12 873706.BJ 0.331731
3853669 2024-04-12 873726.BJ 0.375783
3853670 2024-04-12 873806.BJ 0.740157
3853671 2024-04-12 873833.BJ 0.697674

3853672 rows × 3 columns

连续3日上涨

In [14]:
sql = f"""
SELECT 
    date,  
    instrument,  
    IF(close > m_lag(close, 1) AND m_lag(close, 1) > m_lag(close, 2) AND m_lag(close, 2) > m_lag(close, 3), 1, 0 ) AS factor 
FROM cn_stock_bar1d 
ORDER BY date, instrument
"""

dai.query(sql, filters={'date':[sd, ed]}).df()
Out[14]:
date instrument factor
0 2021-01-04 000001.SZ 0
1 2021-01-04 000002.SZ 0
2 2021-01-04 000004.SZ 0
3 2021-01-04 000005.SZ 0
4 2021-01-04 000006.SZ 0
... ... ... ...
3853667 2024-04-12 873703.BJ 0
3853668 2024-04-12 873706.BJ 0
3853669 2024-04-12 873726.BJ 0
3853670 2024-04-12 873806.BJ 0
3853671 2024-04-12 873833.BJ 0

3853672 rows × 3 columns

连续3日涨停

In [15]:
sql = f"""
SELECT 
    date,  
    instrument,  
    IF(price_limit_status = 3 AND m_lag(price_limit_status,1) = 3 AND m_lag(price_limit_status,2) = 3, 1, 0) AS factor
FROM cn_stock_status  
ORDER BY date, instrument
"""

dai.query(sql, filters={'date':[sd, ed]}).df()
Out[15]:
date instrument factor
0 2021-01-04 000001.SZ 0
1 2021-01-04 000002.SZ 0
2 2021-01-04 000004.SZ 0
3 2021-01-04 000005.SZ 0
4 2021-01-04 000006.SZ 0
... ... ... ...
3853667 2024-04-12 873703.BJ 0
3853668 2024-04-12 873706.BJ 0
3853669 2024-04-12 873726.BJ 0
3853670 2024-04-12 873806.BJ 0
3853671 2024-04-12 873833.BJ 0

3853672 rows × 3 columns

今日收盘价是否突破20日通道上轨

In [16]:
sql = f"""
SELECT 
    date,  
    instrument,  
    m_avg(close, 20) + 2 * m_stddev(close, 20) AS up_band,
    IF(close > up_band, 1, 0) AS factor
FROM cn_stock_bar1d 
ORDER BY date, instrument
"""

dai.query(sql, filters={'date':[sd, ed]}).df()
Out[16]:
date instrument up_band factor
0 2021-01-04 000001.SZ NaN 0
1 2021-01-04 000002.SZ NaN 0
2 2021-01-04 000004.SZ NaN 0
3 2021-01-04 000005.SZ NaN 0
4 2021-01-04 000006.SZ NaN 0
... ... ... ... ...
3853667 2024-04-12 873703.BJ 27.962939 0
3853668 2024-04-12 873706.BJ 15.553062 0
3853669 2024-04-12 873726.BJ 28.348225 0
3853670 2024-04-12 873806.BJ 10.595467 0
3853671 2024-04-12 873833.BJ 11.869168 0

3853672 rows × 4 columns

今日收盘价是否突破20日通道下轨

In [17]:
sql = f"""
SELECT 
    date,  
    instrument,  
    m_avg(close, 20) - 2 * m_stddev(close, 20) AS low_band,
    IF(close < low_band, 1, 0) AS factor
FROM cn_stock_bar1d 
ORDER BY date, instrument
"""

dai.query(sql, filters={'date':[sd, ed]}).df()
Out[17]:
date instrument low_band factor
0 2021-01-04 000001.SZ NaN 0
1 2021-01-04 000002.SZ NaN 0
2 2021-01-04 000004.SZ NaN 0
3 2021-01-04 000005.SZ NaN 0
4 2021-01-04 000006.SZ NaN 0
... ... ... ... ...
3853667 2024-04-12 873703.BJ 23.807061 0
3853668 2024-04-12 873706.BJ 11.423992 0
3853669 2024-04-12 873726.BJ 22.108775 0
3853670 2024-04-12 873806.BJ 8.488533 0
3853671 2024-04-12 873833.BJ 9.870121 0

3853672 rows × 4 columns