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");

Download source code here.