Plotting variance depending on sector

Rob Sukaldi

Join Date: Oct 2017
Posts: 38

Plotting variance depending on sector

19 Oct 2017, 13:35

Hello,

I have a panel data set which contains information on firms (given by REITID), the sector (Sectornumber) a firm operates in and I have a log return series of the firms' stockprice (TotalLogReturn). I managed to generate a graph of the variance of the log return series. I did so by calculating the Variance of the TotalLogReturn, by(Date). Now I want to graph the Variance series of the log return depending on the sector the firm operate in; for a total of 13 variance graphs for every sector. But I can't figure out how to do so. I tried xtline Variance i(Sector) t(Date) but It just generates the same Variance graph for every sector. Could you help me?

Code:

xtset REITID Date
sort REITID Date

by REITID: gen TotalLogReturn = log(TotalReturn[_n])-log(TotalReturn[_n-1])
egen SD = sd(TotalLogReturn), by(Date)
gen Variance =  SD^2

input float Date byte Sectornumber float(Variance TotalLogReturn)
171 1          .            .
172 1  .01271399    .07647028
173 1 .014478557    .13145871
174 1 .005462709     .1797182
175 1  .01207985     .1280671
176 1 .013802183    .12089758
177 1 .022718094   -.02627064
178 1 .004700374    .07014332
179 1 .004386538      .098901
180 1 .005752772  -.012686453
181 1 .007845905    .14680628
182 1 .006333977  -.035694007
183 1 .005497316    .10743682
184 1 .007141759     .1601549
185 1 .005127982   .004204398
186 1 .006305829      .074752
187 1  .01694326  -.018864773
188 1 .006311047    .04138524
189 1 .009498115 -.0045783743
190 1  .01144282    .04413582
191 1 .015725248   -.05722305
192 1 .023222476   -.04872722
193 1 .016055752    -.0510096
194 1  .06492129    .08726103
195 1   .1656582    -.5275189
196 1  .12633483   -.28661945
197 1  .05336738     .1991516
198 1  .04322393     .1398275
199 1 .023571543    .11157066
200 1  .02383995    .05644141
201 1  .01842131   -.05937822
202 1  .10762425    .12065474
203 1  .02146371   -.04043105
204 1 .011512837    .03625132
205 1 .024182616    .07122423
206 1  .03887922   -.08280947
207 1 .028643524    .07350249
208 1   .0820109    .11163995
209 1  .12951487    .02777962
210 1  .02036335    .06785064
211 1  .01993032     .0103463
212 1 .025130564     .1011951
213 1  .08419436   -.11554944
214 1  .06420892 -.0004016963
215 1  .07555228   .006008491
216 1   .0840135    .06081253
217 1    .134007    .06228115
218 1 .007738933  -.018172609
219 1  .18569894    .14843237
220 1  .10825178    .09344856
221 1  .14502089     -.187984
222 1 .026267266   .032185115
223 1   .0886342    .09677877
224 1   .3085793    .06493157
225 1  .07441192  .0039215563
226 1  .07664512    .01992873
227 1  .01925132    -.1026542
228 1  .09450532   -.07764153
229 1 .009929138   -.08239751
171 2          .            .
172 2  .01271399   .008298767
173 2 .014478557   .032523055
174 2 .005462709     .2086389
175 2  .01207985   -.10969877
176 2 .013802183    .10318407
177 2 .022718094    .29257214
178 2 .004700374    -.2366491
179 2 .004386538 -.0024751995
180 2 .005752772   -.02129074
181 2 .007845905    .04335041
182 2 .006333977  -.065104224
183 2 .005497316   .021753816
184 2 .007141759  .0037902505
185 2 .005127982    -.0101394
186 2 .006305829    .03135045
187 2  .01694326    -.0224728
188 2 .006311047 -.0025284954
189 2 .009498115  -.021753816
190 2  .01144282  -.016960636
191 2 .015725248   -.13805278
192 2 .023222476    .04864619
193 2 .016055752    -.1982754
194 2  .06492129   -.20145097
195 2   .1656582   -1.3009998
196 2  .12633483     .4123676
197 2  .05336738    -.3253563
198 2  .04322393    .54591703
199 2 .023571543   -.10919936
200 2  .02383995  -.007722039
201 2  .01842131     .1901077
202 2  .10762425   -.01948218
203 2  .02146371   -.04689976
204 2 .011512837      .279152
205 2 .024182616   -.04233433
206 2  .03887922      -.16411
207 2 .028643524    -.2042156
208 2   .0820109    .15191607
209 2  .12951487   -.07669263
210 2  .02036335  -.014598786
211 2  .01993032     -.150481

end
format %tq Date

Tags: None

Clyde Schechter

Join Date: Apr 2014

Posts: 29966
#2

19 Oct 2017, 14:16

I think you want

Code:

xtset Sectornumber Date xtline Variance

That works with the data you show.
Comment

Rob Sukaldi

Join Date: Oct 2017
Posts: 38

19 Oct 2017, 14:37

I tried that, but sadly it doesn't work. When I use the Variance as the only variable in xtline, stata tells me that I have too few variables specified.
Maybe I should have been more clear regarding setting up the panel data, so this is all I got to make it work:

Code:

clear all

import excel "Panel Data.xlsx", sheet("Panel Data") firstrow

format Date %td
gen QDate = qofd(Date)
format QDate %tq
drop Date
rename QDate Date
format Date %tq

sort REITID Date
label variable Date "Date"
xtset REITID Date
sort REITID Date

*Generating and labeling Average: Leverage, Return, Log Return, Variance, Total Assets/TD/TE/TL/MarketCap.
gen TotalLeverage = TotalDebt/TotalAssets
gen TotalReturn = Dividend + Price
by REITID: gen TotalLogReturn = log(TotalReturn[_n])-log(TotalReturn[_n-1])

egen SD = sd(TotalLogReturn), by(Date)
gen Variance =  SD^2

xtline Variance

Comment

Clyde Schechter

Join Date: Apr 2014
Posts: 29966

19 Oct 2017, 15:49

I don't understand. When I run

Code:

clear
input float Date byte Sectornumber float(Variance TotalLogReturn)
171 1          .            .
172 1  .01271399    .07647028
173 1 .014478557    .13145871
174 1 .005462709     .1797182
175 1  .01207985     .1280671
176 1 .013802183    .12089758
177 1 .022718094   -.02627064
178 1 .004700374    .07014332
179 1 .004386538      .098901
180 1 .005752772  -.012686453
181 1 .007845905    .14680628
182 1 .006333977  -.035694007
183 1 .005497316    .10743682
184 1 .007141759     .1601549
185 1 .005127982   .004204398
186 1 .006305829      .074752
187 1  .01694326  -.018864773
188 1 .006311047    .04138524
189 1 .009498115 -.0045783743
190 1  .01144282    .04413582
191 1 .015725248   -.05722305
192 1 .023222476   -.04872722
193 1 .016055752    -.0510096
194 1  .06492129    .08726103
195 1   .1656582    -.5275189
196 1  .12633483   -.28661945
197 1  .05336738     .1991516
198 1  .04322393     .1398275
199 1 .023571543    .11157066
200 1  .02383995    .05644141
201 1  .01842131   -.05937822
202 1  .10762425    .12065474
203 1  .02146371   -.04043105
204 1 .011512837    .03625132
205 1 .024182616    .07122423
206 1  .03887922   -.08280947
207 1 .028643524    .07350249
208 1   .0820109    .11163995
209 1  .12951487    .02777962
210 1  .02036335    .06785064
211 1  .01993032     .0103463
212 1 .025130564     .1011951
213 1  .08419436   -.11554944
214 1  .06420892 -.0004016963
215 1  .07555228   .006008491
216 1   .0840135    .06081253
217 1    .134007    .06228115
218 1 .007738933  -.018172609
219 1  .18569894    .14843237
220 1  .10825178    .09344856
221 1  .14502089     -.187984
222 1 .026267266   .032185115
223 1   .0886342    .09677877
224 1   .3085793    .06493157
225 1  .07441192  .0039215563
226 1  .07664512    .01992873
227 1  .01925132    -.1026542
228 1  .09450532   -.07764153
229 1 .009929138   -.08239751
171 2          .            .
172 2  .01271399   .008298767
173 2 .014478557   .032523055
174 2 .005462709     .2086389
175 2  .01207985   -.10969877
176 2 .013802183    .10318407
177 2 .022718094    .29257214
178 2 .004700374    -.2366491
179 2 .004386538 -.0024751995
180 2 .005752772   -.02129074
181 2 .007845905    .04335041
182 2 .006333977  -.065104224
183 2 .005497316   .021753816
184 2 .007141759  .0037902505
185 2 .005127982    -.0101394
186 2 .006305829    .03135045
187 2  .01694326    -.0224728
188 2 .006311047 -.0025284954
189 2 .009498115  -.021753816
190 2  .01144282  -.016960636
191 2 .015725248   -.13805278
192 2 .023222476    .04864619
193 2 .016055752    -.1982754
194 2  .06492129   -.20145097
195 2   .1656582   -1.3009998
196 2  .12633483     .4123676
197 2  .05336738    -.3253563
198 2  .04322393    .54591703
199 2 .023571543   -.10919936
200 2  .02383995  -.007722039
201 2  .01842131     .1901077
202 2  .10762425   -.01948218
203 2  .02146371   -.04689976
204 2 .011512837      .279152
205 2 .024182616   -.04233433
206 2  .03887922      -.16411
207 2 .028643524    -.2042156
208 2   .0820109    .15191607
209 2  .12951487   -.07669263
210 2  .02036335  -.014598786
211 2  .01993032     -.150481

end
format %tq Date

xtset Sectornumber Date

xtline Variance

graph export sukaldi.png, replace width(1000)

I get the graph below.

Click image for larger version

Name: sukaldi.png
Views: 1
Size: 81.0 KB
ID: 1415384

Comment

Rob Sukaldi

Join Date: Oct 2017

Posts: 38
#5

20 Oct 2017, 05:31

After I copied your code it came to my mind that Sectornumber is not an unique number. That is why I use REITID to identify the unique entities. There are thus multiple firms which can operate in the same sector. Can this be the reason it doesn't work for my whole sample?
Comment
Clyde Schechter

Join Date: Apr 2014

Posts: 29966
#6

20 Oct 2017, 08:05

Yes, probably so. The question is how you organize your data. Since you want to do the graph by sector and plot Variance by date, you need to have just one observation for each date in each sector. That shouldn't be a problem because your calculation of Variance creates a unique value of Variance for each sector out of the many observations from different firms in that sector. Your example data actually only shows one observation per sector. But since that apparently does not represent your real situation, you can do this:

Code:

by Sectornumber, sort: keep if _n == 1 // SELECT ONE OBS PER SECTOR xtset Sectornumber Date xtline Variance, by(Sectornumber)

This code drops much of the original data. If you need it back, just add -preserve- before the above commands, and -restore- after.
Comment
Rob Sukaldi

Join Date: Oct 2017

Posts: 38
#7

22 Oct 2017, 07:21

Despite your efforts, I still can't get it to work. I have included a link to my data output from Stata and my syntax (including the code for the variance graph). If it's not too much to ask I would really like your help with this.

HTML Code:

https://www.dropbox.com/sh/dn16nakia2cuqhv/AACZ9ee5PphEyCQfCkMmAmbHa?dl=0

BTW. I read that you can override the -xtset- command, by typing -xtline varlist, i(Sectornumber) t(Date)-. So I tried that, but it didn't help either
Comment
Clyde Schechter

Join Date: Apr 2014

Posts: 29966
#8

22 Oct 2017, 09:59

Rob, I'd be happy to try to help, but I do not download Excel files from strangers. Excel files can contain active malware, and it's just a risk I won't take. If you replace that with a Stata data file, I'll give it a look.

But let me ask first, why not just post the contents of the do-file and some -dataex- output for the data here?
Comment

Rob Sukaldi

Join Date: Oct 2017
Posts: 38

22 Oct 2017, 10:42

Sorry, that was never my intention. I've replaced the excel file with the stata output. Eitherway, here it goes:

*do.file

Code:

*Version 15
clear all

* Create *.dta files for excel worksheet Sheet1. (use allsheets for all sheets)
import excel "C:\Users\Rob Sukaldi\Dropbox\Master\Msc Finance\Thesis\Data\Panel Data.xlsx", sheet("Panel Data") firstrow

*Change working directory to:
cd "C:\Users\Rob Sukaldi\Dropbox\Master\Msc Finance\Thesis\Data\Stata"

*Formatting Date such that stata interprets it as quarterly data
format Date %td
gen QDate = qofd(Date)
format QDate %tq
drop Date
rename QDate Date
format Date %tq

* Set REITID as to represent the entities (i) and setting Date as time variable (t)
sort REITID Date
label variable Date "Date"
xtset REITID Date
sort REITID Date

*Generating and labeling Average: Leverage, Return, Log Return, Variance, Total Assets/TD/TE/TL/MarketCap.
gen TotalLeverage = TotalDebt/TotalAssets
gen TotalReturn = Dividend + Price
by REITID: gen TotalLogReturn = log(TotalReturn[_n])-log(TotalReturn[_n-1])

egen SD = sd(TotalLogReturn), by(Date)
gen Variance =  SD^2
drop SD

And dataex output.

Code:

input float Date byte Sectornumber float(Variance TotalLogReturn)
171 1          .            .
172 1  .01271399    .07647028
173 1 .014478557    .13145871
174 1 .005462709     .1797182
175 1  .01207985     .1280671
176 1 .013802183    .12089758
177 1 .022718094   -.02627064
178 1 .004700374    .07014332
179 1 .004386538      .098901
180 1 .005752772  -.012686453
181 1 .007845905    .14680628
182 1 .006333977  -.035694007
183 1 .005497316    .10743682
184 1 .007141759     .1601549
185 1 .005127982   .004204398
186 1 .006305829      .074752
187 1  .01694326  -.018864773
188 1 .006311047    .04138524
189 1 .009498115 -.0045783743
190 1  .01144282    .04413582
191 1 .015725248   -.05722305
192 1 .023222476   -.04872722
193 1 .016055752    -.0510096
194 1  .06492129    .08726103
195 1   .1656582    -.5275189
196 1  .12633483   -.28661945
197 1  .05336738     .1991516
198 1  .04322393     .1398275
199 1 .023571543    .11157066
200 1  .02383995    .05644141
201 1  .01842131   -.05937822
202 1  .10762425    .12065474
203 1  .02146371   -.04043105
204 1 .011512837    .03625132
205 1 .024182616    .07122423
206 1  .03887922   -.08280947
207 1 .028643524    .07350249
208 1   .0820109    .11163995
209 1  .12951487    .02777962
210 1  .02036335    .06785064
211 1  .01993032     .0103463
212 1 .025130564     .1011951
213 1  .08419436   -.11554944
214 1  .06420892 -.0004016963
215 1  .07555228   .006008491
216 1   .0840135    .06081253
217 1    .134007    .06228115
218 1 .007738933  -.018172609
219 1  .18569894    .14843237
220 1  .10825178    .09344856
221 1  .14502089     -.187984
222 1 .026267266   .032185115
223 1   .0886342    .09677877
224 1   .3085793    .06493157
225 1  .07441192  .0039215563
226 1  .07664512    .01992873
227 1  .01925132    -.1026542
228 1  .09450532   -.07764153
229 1 .009929138   -.08239751
171 2          .            .
172 2  .01271399   .008298767
173 2 .014478557   .032523055
174 2 .005462709     .2086389
175 2  .01207985   -.10969877
176 2 .013802183    .10318407
177 2 .022718094    .29257214
178 2 .004700374    -.2366491
179 2 .004386538 -.0024751995
180 2 .005752772   -.02129074
181 2 .007845905    .04335041
182 2 .006333977  -.065104224
183 2 .005497316   .021753816
184 2 .007141759  .0037902505
185 2 .005127982    -.0101394
186 2 .006305829    .03135045
187 2  .01694326    -.0224728
188 2 .006311047 -.0025284954
189 2 .009498115  -.021753816
190 2  .01144282  -.016960636
191 2 .015725248   -.13805278
192 2 .023222476    .04864619
193 2 .016055752    -.1982754
194 2  .06492129   -.20145097
195 2   .1656582   -1.3009998
196 2  .12633483     .4123676
197 2  .05336738    -.3253563
198 2  .04322393    .54591703
199 2 .023571543   -.10919936
200 2  .02383995  -.007722039
201 2  .01842131     .1901077
202 2  .10762425   -.01948218
203 2  .02146371   -.04689976
204 2 .011512837      .279152
205 2 .024182616   -.04233433
206 2  .03887922      -.16411
207 2 .028643524    -.2042156
208 2   .0820109    .15191607
209 2  .12951487   -.07669263
210 2  .02036335  -.014598786
211 2  .01993032     -.150481
212 2 .025130564    .13566592
213 2  .08419436   -.22501095
214 2  .06420892    -.1510403
215 2  .07555228  -.021756716
216 2   .0840135   -.19596677
217 2    .134007    .06538277
218 2 .007738933   -.03871455
219 2  .18569894     .3327057
220 2  .10825178     .3604415
221 2  .14502089   -.04714673
222 2 .026267266   -.13254671
223 2   .0886342  -.008540305
224 2   .3085793    .08433416
225 2  .07441192   .014492993
226 2  .07664512   -.14698213
227 2  .01925132   -.07796157
228 2  .09450532    .04414617
229 2 .009929138   -.34695715
171 3          .            .
172 3  .01271399            .
173 3 .014478557            .
174 3 .005462709            .
175 3  .01207985            .
176 3 .013802183            .
177 3 .022718094            .
178 3 .004700374            .
179 3 .004386538            .
180 3 .005752772            .
181 3 .007845905      .194057
182 3 .006333977    .06472044
183 3 .005497316   -.15539907
184 3 .007141759    .12679413
185 3 .005127982 -.0090054665
186 3 .006305829   -.11326072
187 3  .01694326     .2179638
188 3 .006311047     .0899235
189 3 .009498115   -.03842261
190 3  .01144282    -.2379353
191 3 .015725248    -.2752447
192 3 .023222476   -.51825947
193 3 .016055752    -.5771094
194 3  .06492129    -.7393233
195 3   .1656582    -.3031185
196 3  .12633483    .10257933
197 3  .05336738     .2985151
198 3  .04322393    .32974285
199 3 .023571543   -.32020405
200 3  .02383995    .09978963
201 3  .01842131    -.3085921
202 3  .10762425    .19038203
203 3  .02146371   -.13995577
204 3 .011512837   .029278684
205 3 .024182616   -.27215666
206 3  .03887922    -.4509379
207 3 .028643524    -.4555105
208 3   .0820109     .7728179
209 3  .12951487    -.5279156
210 3  .02036335   -.07776166
211 3  .01993032    -.2142973
212 3 .025130564   -.27420595
213 3  .08419436   -.08673157
214 3  .06420892   .008304773
215 3  .07555228    .03776946
216 3   .0840135     .2564271
217 3    .134007    -.2882727
218 3 .007738933    -.5235708
219 3  .18569894    -.8370647
220 3  .10825178     .7041736
221 3  .14502089    -.3475247
222 3 .026267266    -.1640266
223 3   .0886342     .1106642
224 3   .3085793    -.1683605
225 3  .07441192   -.16435073
226 3  .07664512   -.04486088
227 3  .01925132    .05111153
228 3  .09450532    .04027611
229 3 .009929138   -.12691316
171 3          .            .
172 3  .01271399            .
173 3 .014478557            .
174 3 .005462709            .
175 3  .01207985            .
176 3 .013802183            .
177 3 .022718094            .
178 3 .004700374            .
179 3 .004386538            .
180 3 .005752772            .
181 3 .007845905            .
182 3 .006333977            .
183 3 .005497316            .
184 3 .007141759            .
185 3 .005127982            .
186 3 .006305829            .
187 3  .01694326            .
188 3 .006311047            .
189 3 .009498115            .
190 3  .01144282            .
191 3 .015725248            .
192 3 .023222476            .
193 3 .016055752            .
end
format %tq Date

Comment

Clyde Schechter

Join Date: Apr 2014

Posts: 29966
#10

22 Oct 2017, 11:07

OK. Your data contain multiple observations per combination of Sectornumber and Date. But Variance is constant within each combination of Sectornumber and Date, as it should be. You can't -xtset Sectornumber Date- without first reducing the data set to one observation per Sectornumber Date combination. Assuming you don't want to discard data that way, here's another approach that preserves the data: identify one observation for each Sectornumber Date combination and use those in -graph twoway line, by()-:

Code:

// VERIFY UNIQUE VALUE OF VARIANCE FOR EACH DATE IN EACH SECTOR by Sectornumber Date (Variance), sort: assert Variance[1] == Variance[_N] egen byte flag = tag(Sectornumber Date) // SINGLE OUT ONE OBS PER Sectornumber-Date COMBO graph twoway line Variance Date if flag, by(Sectornumber) sort graph export Sukaldi.png, width(1000) replace

which gives me:
Comment
Rob Sukaldi

Join Date: Oct 2017

Posts: 38
#11

22 Oct 2017, 11:37

Thank you very much, this does show me 13 graphs per sector. Since they all look very similar, is there an easy way to show the minimum/maximum of the graph an a table?
Comment
Clyde Schechter

Join Date: Apr 2014

Posts: 29966
#12

22 Oct 2017, 12:31

So, it sounds like you want:

Code:

tabstat Variance, by(Sectornumber) statistics(min max)
Comment

Rob Sukaldi

Join Date: Oct 2017
Posts: 38

#13

25 Oct 2017, 09:09

Hello,

If I run your code it gets stuck on:

Code:

summarize Variance Date if flag, by(Sectornumber) sort
option by() not allowed

That is, after the graphs are generated. These graphs however, are similar I think. Becasue when I use -tabstat- I get the following output:

Code:

tabstat Variance, by(Sectornumber) statistics(min max)

Summary for variables: Variance
     by categories of: Sectornumber (Sector number)

Sectornumber |       min       max
-------------+--------------------
           1 |  .0043865  .3085793
           2 |  .0043865  .3085793
           3 |  .0043865  .3085793
           4 |  .0043865  .3085793
           5 |  .0043865  .3085793
           6 |  .0043865  .3085793
           7 |  .0043865  .3085793
           8 |  .0043865  .3085793
           9 |  .0043865  .3085793
          10 |  .0043865  .3085793
          11 |  .0043865  .3085793
          12 |  .0043865  .3085793
          13 |  .0043865  .3085793
-------------+--------------------
       Total |  .0043865  .3085793
----------------------------------

Comment

Clyde Schechter

Join Date: Apr 2014

Posts: 29966
#14

25 Oct 2017, 10:29

summarize Variance Date if flag, by(Sectornumber) sort
option by() not allowed

does not appear anywhere in my code, and it's not, as Stata tells you, legal syntax. -summarize- does not have a -by()- option. You can get the same functionality by using the -by:- prefix with -summarize-. But -tabstat- is easier anyway.
Comment
Rob Sukaldi

Join Date: Oct 2017

Posts: 38
#15

26 Oct 2017, 05:25

Code:

by Sectornumber Date (Variance), sort: assert Variance[1] == Variance[_N] egen byte flag = tag(Sectornumber Date) // SINGLE OUT ONE OBS PER Sectornumber-Date COMBO graph twoway line Variance Date if flag, by(Sectornumber) sort tabstat Variance, by(Sectornumber) statistics(min max)

Gives me the graphs as you have already shown in your previous post and the -tabstat- does what it is supposed to do. However, it shows me the same values for every sector as I showed in my post. I was wrong when I typed:

Code:

summarize Variance Date if flag, by(Sectornumber) sort

I figured another way of analyzing the sector variance is by creating a new variable:

Code:

egen SD1 = sd(TotalLogReturn), by(Sectornumber Date) gen SectorVariance = SD1^2 drop SD1 xtline SectorVariance, i(Sectornumber) t(Date)

Eventually it doesn't really matter but I just thought that it should be possible to do this with the 'normal' Variance variable. That seemed nicer.
Comment

Announcement

Plotting variance depending on sector

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment