Example 6. Time series manipulation in panel data
When we work with panel data that have both cross-sectional and time series dimension, it is usually not convenient to apply time series functions on each cross sectional object. Such examples include calculating moving average/volatility for stock returns, and cumulative returns or running maximums of different trading strategies.
DolphinDB offers a unique feature "context by" to significantly simplify the time series manipulation in panel data.
The following example uses US daily stocks data from CRSP to calculate volatility and beta for each stock with a rolling window of 6 months.
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);
The script above imports the data. Next, we calculate the 6-month rolling volatility and beta for each firm with the SQL clause context by, functions mstd and mcount, and the template function moving.
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);
Finally, plot the quartiles of the distribution of rolling volatility over time in IDE:
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");
