<<URGENT>> Hi there,
i need to calculate the monthly return by daily data(as attached - RIsna_dwnS_all_cs)
following is the code that i wrote, but the results show lots of NA, please help to check the reason. Thank you so much!!!!!!!!!!
Code: Select all
%dir="F:\Thesis\Eviews\St12_Ret_mth\dwnSize\2.RIsna"
%dir1="F:\Thesis\Eviews\St12_Ret_mth\csv"
output(t,o) {%dir1}\out1
'all data
wfopen {%dir}\RIsna_dwnS_all_cs.xlsx range=Sheet1 colhead=0 namepos=all na="#N/A" names=("date") @freq D5 12/31/1969 @smpl @all
group o ser*
stomna(o, om)
rowvector ds1 = @rowextract(om,1) '取dscode
scalar alfirm = @columns(om) 'count nobs of firm
for !i =1 to 9
series w{!i} = series0{!i}
next
for !i =10 to @columns(o)
series w{!i} = series{!i}
next
for !i=1 to 9
w{!i}(1)=na '將series第一行變成na
next
for !i=10 to @columns(o)
w{!i}(1)=na
next
series Mend = @day(+1) =1
for !i = 1 to @columns(o)
series riM{!i}= Mend*w{!i} '找出每個月最後一天的RI
next
group aa riM*
for !i =1 to @columns(aa)
scalar mcount = 12*(2016-1969)
matrix(mcount, 2) m{!i} =na
matrix(mcount, 2) ml{!i} =na 'lag
!n=0
for !y = 1970 to 2016
for !m=1 to 12
!n=!n+1
smpl if @year=!y and @month=!m
m{!i}(!n, 1)=!y*100+!m
ml{!i}(!n, 1)=!y*100+!m
series riMkk{!i}_!y_!m = na
riMkk{!i}_!y_!m = riM{!i}
series riMLg{!i}_!y_!m = riM{!i}(-1)
stomna(riMkk{!i}_!y_!m, riMk{!i}_!y_!m)
stomna(riMLg{!i}_!y_!m, riML{!i}_!y_!m)
m{!i}(!n, 2)=@sum(riMk{!i}_!y_!m)
if m{!i}(!n, 2) =0 then 'm{!i}(!n, 2) =333
m{!i}(!n, 2) =na
endif
ml{!i}(!n, 2)=@sum(riML{!i}_!y_!m)
if ml{!i}(!n, 2) =0 then 'm{!i}(!n, 2) =333
ml{!i}(!n, 2) =na
endif
smpl @all
next
next
delete rimk*
delete riml*
matrix retM{!i}= @ediv(ml{!i}, m{!i})-1
vector c1 = @columnextract(m1, 1) 'monthly date
colplace(retM{!i},c1,1)
next
'delete w*
delete rim*
'combine all retM
matrix(mcount, alfirm_act1+1) rrM = na
for !i=1 to @columns(aa)
vector cc{!i} = @columnextract(retM{!i},2)
colplace(rrM,cc{!i}, !i+1)
next
colplace(rrM,c1,1)
rrM.write(t=txt, d=c) F:\Thesis\Eviews\St12_Ret_mth\csv\retM_F_cs.csv