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 ,函数mstdmcount和模板函数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);



最后,在GUI中绘制滑动波动率的分布图。



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.