Example 6.时间序列处理

当我们使用跨区和时序维度的固定样本数据时，在每个跨区对象上使用时间序列函数会造成不便。例如，计算股票收益的移动平均数或波动、计算累计收益、运行不同交易策略的最大值等。

DolphinDB提供了独特的功能“context by”来简化固定样本数据中的时间序列操作。

以下例子使用了CRSP的数据来计算每个股票6个月滚动窗口的波动和β值：

schema = extractTextSchema("c:/DolphinDB/Data/USstocks.csv")

update schema set type=`DOUBLE where name=`RET

USstocks = ploadText("c:/DolphinDB/Data/USstocks.csv", , schema)

rets = select PERMNO, date, RET, SHROUT*abs(PRC) as MV from USstocks where 1<=weekday(date)<=5 and isValid(PRC) and isValid(VOL) order by PERMNO, date

undef(`USstocks, VAR);

上述脚本导入了数据。接着，我们使用SQL语句 context by ，函数mstd和mcount和模板函数moving，计算每个公司6个月的滚动波动值和β值。

rollingWindow = 126;

vols = select PERMNO, date, mstd(RET, rollingWindow) as vol, mcount(RET, rollingWindow) from rets context by PERMNO

vols = select PERMNO, date, vol from vols where mcount_RET=rollingWindow

mktRets = select wavg(RET, prevMV) as mktRet from (select date, RET, prev(MV) as prevMV from rets context by PERMNO) group by date

data = select PERMNO, date, RET, mktRet from ej(rets, mktRets, `date) where isValid(mktRet)

betas = select PERMNO, date, moving(def(x,y):covar(x,y)/var(y), RET, mktRet, rollingWindow) as beta from data context by PERMNO

betas = select * from betas where isValid(beta);

最后，在开发集成环境绘制随时间滚动波动的分布。

dist = select percentile(vol, 25) as q1, median(vol) as q2, percentile(vol, 75) as q3 from vols group by date

plot((select q1, q2, q3 from dist), dist.date, "The distribution of 6-month volatilities of US stocks");

Download source code here.