how to calculate the moving average for a variable

[Saleh sharmah]

Dear all

I am trying to calculate the average bond maturity over the preceding three months as an instrumental variable in my analysis. it is worth noting that my data is unbalanced and one firm_id can have multiple observations within one year. Therefore, declaring the data as a panel using firm_id would not work. Therefore,
My command is :
xtset observations_num Year

gen matruity_moveavrg=(F1.maturity+maturity+L1.maturit y)/3

the generated new variable is missing and the code is incorrect.
any help is highly appreciated in proving the correct code.

[Nick Cox]

Please show a data example. https://www.statalist.org/forums/help#stata indicates why and how to do that.

[Saleh sharmah]

Dear Nick
please find the example of my data:
input float brorroer_id int Year double log_loanmat
28 2005 1.9459101
283 2006 1.9459101
258 2006 1.9459101
691 2007 1.9459101
534 2007 1.9459101
834 2008 1.9459101
834 2008 1.9459101
381 2008 1.9459101
43 2008 1.9459101
109 2008 1.9459101
21 2009 1.9459101
526 2010 1.9459101
689 2010 1.9459101
765 2010 1.9459101
354 2011 1.9459101
72 2012 1.9459101
72 2012 1.9459101

[Nick Cox]

Calculation for the three preceding months (#1) needs more time detail than just a variable recording the year.

[Saleh sharmah]

does this data help? if not, would please be more specific about what I need to include in the dataset example? thanks

input float brorroer_id int(Year maturity in months)
81 2008 12
835 2018 60
81 2011 36
835 2018 84
81 2008 12
835 2019 83
835 2019 47
641 2013 48
641 2012 60
641 2009 60
641 2013 60
641 2005 13
102 2017 35
641 2013 48
641 2016 60
102 2015 36
902 2012 46
102 2017 59
641 2010 37
641 2018 84
641 2009 60
641 2009 36
641 2016 60
641 2007 36
641 2016 60
406 2018 44
641 2014 48
102 2015 36
641 2014 48
641 2018 89
641 2013 48
641 2007 36
641 2018 84
902 2014 49
331 2017 60
902 2012 46
902 2017 47
902 2012 46
641 2007 36
641 2009 36
641 2012 60
641 2016 60
641 2014 48
641 2014 60
641 2009 60
902 2014 49
102 2015 60
641 2014 48
641 2013 48
641 2016 60
902 2017 47
641 2006 60
102 2017 59
641 2010 61
641 2016 60
102 2017 35
641 2014 60
641 2012 48
641 2013 48
141 2008 35
141 2008 36
59 2017 63
141 2008 36
141 2008 36
59 2013 60
698 2012 36
750 2018 35
750 2010 45
814 2008 36
542 2014 36
750 2019 48
750 2019 48
750 2012 36
814 2007 63
542 2009 36
814 2007 60
750 2018 59
893 2008 46
542 2011 36
542 2011 36
698 2014 46
698 2008 120
542 2014 36
698 2018 48
542 2011 36
814 2014 36
698 2006 54
257 2010 48
698 2006 54
698 2006 48
698 2018 120
542 2009 36
698 2011 120
750 2018 47
750 2019 60
698 2012 48
698 2006 60
698 2011 120
893 2010 36
698 2010 48

[Nick Cox]

Sorry, but I don't think that helps me. I don't work in finance so have no precise idea what bond maturity means. Someone else may be able to work out what you mean, but doesn't a bond become mature in a particular month, and if so don't you need monthly dates?

[Saleh sharmah]

Thanks, Nick for your kind response
it is a loan maturity and it means that when a firm obtains a loan from a bank that loan has a maturity of 5 years ( 60 months) to be paid off (this is just an example, a firm can have loan maturity from 12 months to 120 months ). Thus, I need to know how to calculate the average loan maturity over the preceding three months. I hope this helps with what I need.

[Saleh sharmah]

I have included additional data for you that may help: loan start date and loan end date.

input float brorroer_id int(Year maturity loan_startdate) long loan_enddate
81 2008 12 18231 18596
835 2018 60 21504 23330
81 2011 36 19053 20148
835 2018 84 21504 24061
81 2008 12 18231 18596
835 2019 83 21907 24426
835 2019 47 21907 23330
641 2013 48 19681 21142
641 2012 60 19271 21097
641 2009 60 18359 20185
641 2013 60 19681 21507
641 2005 13 16701 17095
102 2017 35 21175 22242
641 2013 48 19681 21142
641 2016 60 20740 22566
102 2015 36 20430 21522
902 2012 46 19416 20808
102 2017 59 21175 22972
641 2010 37 18711 19843
641 2018 84 21593 24150
641 2009 60 18359 20185
641 2009 36 18359 19455
641 2016 60 20740 22566
641 2007 36 17591 18686
641 2016 60 20740 22566
406 2018 44 21502 22857
641 2014 48 20066 21537
102 2015 36 20430 21522
641 2014 48 20066 21537
641 2018 89 21593 24295
641 2013 48 19681 21142
641 2007 36 17591 18686
641 2018 84 21593 24150
902 2014 49 20261 21761
331 2017 60 21458 23284
902 2012 46 19416 20808
902 2017 47 21039 22492
902 2012 46 19416 20808
641 2007 36 17591 18686
641 2009 36 18359 19455
641 2012 60 19271 21100
641 2016 60 20740 22566
641 2014 48 20066 21537
641 2014 60 20066 21902
641 2009 60 18359 20185
902 2014 49 20261 21761
102 2015 60 20430 22253
641 2014 48 20066 21537
641 2013 48 19681 21142
641 2016 60 20740 22566
902 2017 47 21039 22492
641 2006 60 17248 19075
102 2017 59 21175 22972
641 2010 61 18711 20574
641 2016 60 20740 22566
102 2017 35 21175 22242
641 2014 60 20066 21902
641 2012 48 19271 20735
641 2013 48 19681 21142
141 2008 35 17870 18965
141 2008 36 17870 18965
59 2017 63 21266 23184
141 2008 36 17870 18965
141 2008 36 17870 18965
59 2013 60 19900 21726
698 2012 36 19264 20359
750 2018 35 21535 22614
750 2010 45 18714 20080
814 2008 36 18036 19132
542 2014 36 19970 21066
750 2019 48 21906 23367
750 2019 48 21906 23367
750 2012 36 19250 20345
814 2007 63 17672 19590
542 2009 36 18400 19496
814 2007 60 17465 19292
750 2018 59 21535 23344
893 2008 46 17955 19358
542 2011 36 18977 20073
542 2011 36 18977 20073
698 2014 46 20258 21651
698 2008 120 17717 22100
542 2014 36 19970 21066
698 2018 48 21663 23130
542 2011 36 18977 20073
814 2014 36 19913 21009
698 2006 54 17017 18662
257 2010 48 18599 20060
698 2006 54 17017 18662
698 2006 48 17017 18478
698 2018 120 21481 25134
542 2009 36 18400 19496
698 2011 120 18898 22551
750 2018 47 21535 22979
750 2019 60 21906 23733

[Nick Cox]

I imagine that that is now clear to people who work with this kind of data, and perhaps others too, but beyond suggesting that rangestat from SSC can help with this, I am going to leave this open to people in your field.

[Saleh sharmah]

Thanks Nick for your efforts anyway. I hope to see other’s responses here.