数据计算部分让 QuantChat 来写
User: BigQuant平台提供股票数据表 cn_stock_bar1d(date, instrument, close)，计算某只股票的月度收益，用SQL实现
QuantChat: 要计算某只股票的月度收益，我们需要从cn_stock_bar1d数据表中提取每月的第一个交易日和最后一个交易日的数据。然后用最后一个交易日的收盘价除以第一个交易日的收盘价，然后减去1得到月度收益率。以下是SQL实现的方法：
WITH
first_day_of_month AS (
SELECT
instrument,
MIN(date) AS first_date,
EXTRACT(YEAR FROM date) AS year,
EXTRACT(MONTH FROM date) AS month
FROM
cn_stock_bar1d
WHERE
instrument = '股票代码'
GROUP BY
year,
month,
instrument
),
last_day_of_month AS (
SELECT
instrument,
MAX(date) AS last_date,
EXTRACT(YEAR FROM date) AS year,
EXTRACT(MONTH FROM date) AS month
FROM
cn_stock_bar1d
WHERE
instrument = '股票代码'
GROUP BY
year,
month,
instrument
),
first_day_prices AS (
SELECT
a.instrument,
a.date AS first_date,
b.date AS last_date,
a.close AS first_close,
b.close AS last_close
FROM
cn_stock_bar1d a
INNER JOIN first_day_of_month fd ON a.instrument = fd.instrument AND a.date = fd.first_date
INNER JOIN last_day_of_month ld ON a.instrument = ld.instrument AND fd.year = ld.year AND fd.month = ld.month
INNER JOIN cn_stock_bar1d b ON a.instrument = b.instrument AND b.date = ld.last_date
)
SELECT
instrument,
first_date,
last_date,
(last_close / first_close) - 1 AS monthly_return
FROM
first_day_prices;将'股票代码'替换为目标股票的代码。这个查询将返回股票的每个月的月度收益率。
%%chart {"type_": "bar"}
WITH
first_day_of_month AS (
SELECT
instrument,
MIN(date) AS first_date,
EXTRACT(YEAR FROM date) AS year,
EXTRACT(MONTH FROM date) AS month
FROM
cn_stock_bar1d
WHERE
date BETWEEN '2021-01-01' AND '2021-12-31' AND instrument = '000001.SZ'
GROUP BY
year,
month,
instrument
),
last_day_of_month AS (
SELECT
instrument,
MAX(date) AS last_date,
EXTRACT(YEAR FROM date) AS year,
EXTRACT(MONTH FROM date) AS month
FROM
cn_stock_bar1d
WHERE
date BETWEEN '2021-01-01' AND '2021-12-31' AND instrument = '000001.SZ'
GROUP BY
year,
month,
instrument
),
first_day_prices AS (
SELECT
a.instrument,
a.date AS first_date,
b.date AS last_date,
a.close AS first_close,
b.close AS last_close
FROM
cn_stock_bar1d a
INNER JOIN first_day_of_month fd ON a.instrument = fd.instrument AND a.date = fd.first_date
INNER JOIN last_day_of_month ld ON a.instrument = ld.instrument AND fd.year = ld.year AND fd.month = ld.month
INNER JOIN cn_stock_bar1d b ON a.instrument = b.instrument AND b.date = ld.last_date
WHERE
a.date BETWEEN '2021-01-01' AND '2021-12-31' AND a.instrument = '000001.SZ'
AND b.date BETWEEN '2021-01-01' AND '2021-12-31' AND b.instrument = '000001.SZ'
)
SELECT
first_date AS date,
(last_close / first_close) - 1 AS monthly_return
FROM
first_day_prices;
import bigcharts
from bigcharts import opts
import dai
data = dai.query("""
WITH
first_day_of_month AS (
SELECT
instrument,
MIN(date) AS first_date,
EXTRACT(YEAR FROM date) AS year,
EXTRACT(MONTH FROM date) AS month
FROM
cn_stock_bar1d
WHERE
date BETWEEN '2021-01-01' AND '2021-12-31' AND instrument = '000001.SZ'
GROUP BY
year,
month,
instrument
),
last_day_of_month AS (
SELECT
instrument,
MAX(date) AS last_date,
EXTRACT(YEAR FROM date) AS year,
EXTRACT(MONTH FROM date) AS month
FROM
cn_stock_bar1d
WHERE
date BETWEEN '2021-01-01' AND '2021-12-31' AND instrument = '000001.SZ'
GROUP BY
year,
month,
instrument
),
first_day_prices AS (
SELECT
a.instrument,
a.date AS first_date,
b.date AS last_date,
a.close AS first_close,
b.close AS last_close
FROM
cn_stock_bar1d a
INNER JOIN first_day_of_month fd ON a.instrument = fd.instrument AND a.date = fd.first_date
INNER JOIN last_day_of_month ld ON a.instrument = ld.instrument AND fd.year = ld.year AND fd.month = ld.month
INNER JOIN cn_stock_bar1d b ON a.instrument = b.instrument AND b.date = ld.last_date
WHERE
a.date BETWEEN '2021-01-01' AND '2021-12-31' AND a.instrument = '000001.SZ'
AND b.date BETWEEN '2021-01-01' AND '2021-12-31' AND b.instrument = '000001.SZ'
)
SELECT
first_date AS date,
(last_close / first_close) - 1 AS monthly_return
FROM
first_day_prices;
""")
bigcharts.Chart(
data=data,
type_="bar",
# 默认 x="date",
# y=["close"],
).render()