Rolling standard deviation daily observations

[Mattia Coppo]

Hi,

I am not sure how to create a rolling standard deviation using

tsegen

, I read several posts but I did not find a response to why I have to few missing values.

I have a dataset with daily series (see below), skipping weekends
I need to create a monthly (24 obs) and a yearly (250 obs) rolling standard deviation

When i use

tsegen rolling_30d_sd = rowsd(L(0/).all_share_index)

I thought that the first 23 rows would be empty, however only the first 1 is. How is this possible? And how can I correct it?

I tried

tsegen rolling_30d_sd = rowsd(L(0/).all_share_index, 24)

, but this does not work as it counts the weekends as missing value.

Is there a way to calculate the window from today-1 month ago?

* Example generated by -dataex-. To install: ssc install dataex
clear
input int date byte encoded double all_share_index str41 country
17167 1 376.69 "CYPRUS"
17168 1 381.44 "CYPRUS"
17169 1 385.21 "CYPRUS"
17170 1 383.43 "CYPRUS"
17171 1 395.64 "CYPRUS"
17174 1 406.45 "CYPRUS"
17175 1  413.5 "CYPRUS"
17176 1 410.89 "CYPRUS"
17177 1 425.99 "CYPRUS"
17178 1 428.67 "CYPRUS"
17181 1 423.41 "CYPRUS"
17182 1 406.93 "CYPRUS"
17183 1 407.93 "CYPRUS"
17184 1 418.21 "CYPRUS"
17185 1 427.89 "CYPRUS"
17188 1 432.01 "CYPRUS"
17189 1 422.86 "CYPRUS"
17190 1 415.44 "CYPRUS"
17191 1 415.76 "CYPRUS"
17192 1 410.42 "CYPRUS"
17195 1 401.93 "CYPRUS"
17196 1 395.61 "CYPRUS"
17197 1 406.35 "CYPRUS"
17198 1 414.38 "CYPRUS"
17199 1 416.35 "CYPRUS"
17202 1 415.95 "CYPRUS"
17203 1 413.66 "CYPRUS"
17204 1 416.82 "CYPRUS"
17205 1 418.19 "CYPRUS"
17206 1 410.49 "CYPRUS"
17209 1 402.48 "CYPRUS"
17210 1 403.88 "CYPRUS"
17211 1    410 "CYPRUS"
17212 1 410.63 "CYPRUS"
17213 1 411.51 "CYPRUS"
17216 1 411.51 "CYPRUS"
17217 1 410.54 "CYPRUS"
17218 1 412.95 "CYPRUS"
17219 1 412.05 "CYPRUS"
17220 1 413.71 "CYPRUS"
17223 1 417.87 "CYPRUS"
17224 1 411.43 "CYPRUS"
17225 1 403.57 "CYPRUS"
17226 1 401.19 "CYPRUS"
17227 1 395.24 "CYPRUS"
17230 1 383.29 "CYPRUS"
17231 1 397.09 "CYPRUS"
17232 1 406.85 "CYPRUS"
17233 1  403.2 "CYPRUS"
17234 1 400.62 "CYPRUS"
17237 1 401.14 "CYPRUS"
17238 1 395.36 "CYPRUS"
17239 1 385.51 "CYPRUS"
17240 1  392.8 "CYPRUS"
17241 1 386.38 "CYPRUS"
17244 1 393.09 "CYPRUS"
17245 1 394.95 "CYPRUS"
17246 1 395.19 "CYPRUS"
17247 1 400.85 "CYPRUS"
17248 1  401.2 "CYPRUS"
17251 1 402.99 "CYPRUS"
17252 1 403.91 "CYPRUS"
17253 1 404.06 "CYPRUS"
17254 1 403.79 "CYPRUS"
17255 1 404.85 "CYPRUS"
17258 1 407.52 "CYPRUS"
17259 1 411.52 "CYPRUS"
17260 1 411.49 "CYPRUS"
17261 1 413.92 "CYPRUS"
17262 1 413.92 "CYPRUS"
17265 1 413.92 "CYPRUS"
17266 1 413.92 "CYPRUS"
17267 1 415.34 "CYPRUS"
17268 1 413.97 "CYPRUS"
17269 1 414.53 "CYPRUS"
17272 1 422.63 "CYPRUS"
17273 1  433.1 "CYPRUS"
17274 1 432.19 "CYPRUS"
17275 1 428.64 "CYPRUS"
17276 1 434.93 "CYPRUS"
17279 1 434.68 "CYPRUS"
17280 1 429.31 "CYPRUS"
17281 1 429.39 "CYPRUS"
17282 1 431.72 "CYPRUS"
17283 1 428.65 "CYPRUS"
17286 1 427.76 "CYPRUS"
17287 1 427.76 "CYPRUS"
17288 1 426.38 "CYPRUS"
17289 1 425.14 "CYPRUS"
17290 1 429.37 "CYPRUS"
17293 1 433.12 "CYPRUS"
17294 1 431.09 "CYPRUS"
17295 1 430.47 "CYPRUS"
17296 1 431.78 "CYPRUS"
17297 1 430.84 "CYPRUS"
17300 1 436.25 "CYPRUS"
17301 1 440.37 "CYPRUS"
17302 1 450.19 "CYPRUS"
17303 1 459.83 "CYPRUS"
17304 1 462.02 "CYPRUS"
end
format %tdnn/dd/CCYY date
label values encoded encoded

[Attaullah Shah]

Here is a solution based on asrol, which can be downloaded from SSC. On a side note, you are using share index and finding the volatility of that index, why are you not converting the index to daily returns and then finding the volatility of the returns?

ssc install asrol
bys encoded: asrol all_share_index, stat(sd) window(date 30) gen(sd) min(22)

Please note option min(22) that causes asrol to report sd only when there are at least 22 observations in the range.

[Mattia Coppo]

Attaullah , your method doesn't seem to work, because it creates all these missings in the middle, as weekends are missing from my data.

My technical concern is that if I use

 bys encoded: asrol all_share_index, stat(sd) window(date 24) gen(sd)

the first 23 observations still show (but they shouldn't and I do not understand how obs at t1 can calculate SD)

* Example generated by -dataex-. To install: ssc install dataex
clear
input int date byte encoded double all_share_index str44 country double sd
19015 1  40.26 "CYPRUS"                  .
17178 1 428.67 "CYPRUS"                  .
19029 1  43.87 "CYPRUS"                  .
19470 1  20.47 "CYPRUS"                  .
19038 1  43.31 "CYPRUS"  2.473792722299189
19044 1  43.34 "CYPRUS"   1.90168892809018
19052 1   38.9 "CYPRUS"   2.68365696738885
19059 1  39.97 "CYPRUS"  2.432501963178201
17233 1  403.2 "CYPRUS"  8.271420898470446
21766 1  14.74 "CYPRUS"                  .
19075 1  38.28 "CYPRUS"  .9858035595435124
19274 1  24.05 "CYPRUS"                  .
18945 1  42.66 "CYPRUS"                  .
19089 1  36.13 "CYPRUS" 1.2771877927731135
19205 1  23.24 "CYPRUS"  .8405419814089142
21741 1  15.24 "CYPRUS" .29225389246738454
18589 1    110 "CYPRUS"  7.260785334910659
19102 1  35.57 "CYPRUS"  .8270571885145501
18070 1 145.95 "CYPRUS"                  .
19024 1  47.96 "CYPRUS" 2.6286497200367624
19198 1  23.82 "CYPRUS"  .6184325949195064
20958 1  18.97 "CYPRUS"  .8614138182277819
18515 1 133.42 "CYPRUS"  3.459942102210577
17532 1 467.93 "CYPRUS" 12.918907391177402
17343 1 458.87 "CYPRUS"  6.161297440246341
21430 1  16.33 "CYPRUS"                  .
19124 1  31.33 "CYPRUS" 1.8863978821279914
19128 1  30.18 "CYPRUS" 2.0920256758872324
19129 1   29.8 "CYPRUS" 2.1620552992194995
19131 1  28.87 "CYPRUS"   2.29054692045485
19632 1  19.94 "CYPRUS" .10595269711514672
19156 1  24.53 "CYPRUS" 2.0284500299020247
19158 1  25.66 "CYPRUS" 1.7645170905294638
19159 1  26.08 "CYPRUS"  1.592442717815561
19163 1  26.99 "CYPRUS" 1.3496002294089828
19173 1  25.77 "CYPRUS" 1.0874097464663246
21181 1   16.3 "CYPRUS" .24096846446329956
21070 1  17.86 "CYPRUS"   .312872807925831
19260 1  22.97 "CYPRUS"                  .
21343 1  15.35 "CYPRUS" .39940106675021797
20832 1  17.84 "CYPRUS"  .5941812002254014
19184 1  25.44 "CYPRUS" 1.0294773397572152
20132 1  19.86 "CYPRUS"  .6308573153899002
20865 1  16.43 "CYPRUS"  .5879565095675505
19906 1  24.58 "CYPRUS"   .552905744141587
20566 1  16.93 "CYPRUS" .09698484730132119
20290 1  19.71 "CYPRUS"  .6251013770595388
19228 1  22.43 "CYPRUS" .28374924909792476
19229 1  21.39 "CYPRUS" .40073660531554467
21299 1  15.36 "CYPRUS" .07861490336000583
20685 1  16.55 "CYPRUS" .17436899223928956
18730 1  98.59 "CYPRUS"   2.98204743865803
21073 1  17.89 "CYPRUS"                  .
19753 1  24.16 "CYPRUS"  .5483829041133664
19277 1  22.85 "CYPRUS"  .6545103255364014
20066 1  22.15 "CYPRUS" .30892110818500434
20151 1  20.46 "CYPRUS"  .4547593436160058
21263 1  15.77 "CYPRUS" .19153345643794198
19282 1  23.36 "CYPRUS"  .5203056461042542
19291 1  27.66 "CYPRUS" 1.7665212714817224
19295 1  25.51 "CYPRUS"                  .
19296 1  25.27 "CYPRUS"  1.800752848813746
19309 1  23.87 "CYPRUS"                  .
19312 1  23.69 "CYPRUS" 1.3146434672136194
18953 1  37.81 "CYPRUS"  4.273916459731274
19317 1  24.34 "CYPRUS" 1.3455690471343569
19320 1  25.52 "CYPRUS" 1.1387545410499604
19323 1  24.96 "CYPRUS"                  .
19324 1  24.44 "CYPRUS"  .8137009938296181
18494 1 126.78 "CYPRUS" 3.5375543683583026
21374 1  16.48 "CYPRUS"                  .
19332 1  23.97 "CYPRUS"  .7205110319669974
19333 1  24.03 "CYPRUS"   .652879237456131
19334 1  23.98 "CYPRUS"  .5550086774408417
18581 1 117.11 "CYPRUS"                  .
20545 1  16.94 "CYPRUS" .32382588448609784
19351 1  22.61 "CYPRUS"                  .
20397 1  18.72 "CYPRUS" .18837422457663527
19362 1  23.62 "CYPRUS"  .5612971988657577
19367 1  23.37 "CYPRUS" .49225363011727663
19368 1  23.21 "CYPRUS" .44209066473310193
19372 1  22.77 "CYPRUS"                  .
19379 1  22.16 "CYPRUS"                  .
19383 1  22.45 "CYPRUS" .47247460363732907
19389 1   22.2 "CYPRUS"  .5254451833375589
19397 1  22.06 "CYPRUS"    .34418138025004
20963 1   18.9 "CYPRUS"  .7704414880963293
19425 1  21.14 "CYPRUS"  .5861636962384131
19428 1   21.2 "CYPRUS"                  .
19429 1  21.01 "CYPRUS"  .6624479069611656
17650 1 311.47 "CYPRUS"                  .
19446 1  21.52 "CYPRUS"  .2844406678140333
20999 1  18.63 "CYPRUS" .15152328982472435
18310 1 131.15 "CYPRUS"  6.426388660213543
20838 1   17.9 "CYPRUS"  .5368097601708808
20432 1   17.1 "CYPRUS"  .5706774283890397
19459 1  21.03 "CYPRUS" .31317014724513736
20578 1  16.98 "CYPRUS" .07895009108998481
18862 1   51.7 "CYPRUS"  7.752514017599298
19466 1  20.58 "CYPRUS" .38178012075808854
end
format %tdnn/dd/CCYY date
label values encoded encoded
label def encoded 1 "CYPRUS-DS Market - PRICE INDEX", modify

[Nick Cox]

Your data example isn’t in date order. But weekends indeed appear to be not present in the data, which is not at all the same as having explicit missing values. But what do you expect? asrol tsegen rangestat (SSC, which I can recommend over tsegen for your purpose) all look for observations within a specified window of fixed length, unless you use a more complicated syntax. Naturally no 30 day window, for your kind of data, but you could use a business calendar.

[Mattia Coppo]

Hi Nick,
You are right, I had to change the date and gave me an idea:

 tostring date, gen(date2)
 encode date2, gen(date3)
bys encoded: asrol all_share_index, stat(sd) window(date3 24) gen(sd) min(22)

Maybe a bit far-fetch, but gets the job done.
Thanks!

[Nick Cox]

I can’t recommend that method. Business calendars are what to use with that preference.