Page 1 of 1
Adding a counter to write options?
Posted: Thu Jun 04, 2015 3:00 pm
by elac
Hello everyone, I need help with this code i've been working on.
Code: Select all
cd "C:\Users\xxx\Desktop\GARCH_xxx"
wfopen 200
%r1="200"
%r2="480"
for !i=1 to 94
PAGESELECT {%r{!i}}
'mode quiet
smpl @all
smpl 3 1804
equation garch11.ARCH(DERIV=AA) FLUOPNET_{%r{!i}} C TIME SF_D SF_S SF_M D_Q1 D_Q2 FLUOPNET_{%r{!i}}(-1)
garch11.forecast(f=na e) fluopnet_{%r{!i}}_pf
garch11.makegarch var_{%r{!i}}_1p
genr vol_{%r{!i}}=var_{%r{!i}}_1p^.5
plot vol_{%r{!i}}
freeze (vol_{%r{!i}})
group gru_{%r{!i}} vol_{%r{!i}} var_{%r{!i}}_1p
'fluopnet_{%r{!i}}_pf
write(A2, t=xls) "C:\Users\xxx\Desktop\GARCH_xxx\vol_g.xls" gru_{%r{!i}}
next
So this program exports 94 times 2 series to an Excel spreadsheet, but right now it just overwrites the same cell (A2) until the last iteration. How do I fix this so after each iteration the program pastes the series on the next cell C2, E2, G2 and so on?
Thanks in advance.
Re: Adding a counter to write options?
Posted: Thu Jun 04, 2015 3:05 pm
by EViews Gareth
Use wfsave rather than write. There is a range= argument that lets you specify the Excel range to save to.
Re: Adding a counter to write options?
Posted: Fri Jun 05, 2015 9:46 am
by elac
Ok, so I came up with this:
Code: Select all
cd "C:\Users\xxx\Desktop\GARCH_xxx"
' Set parameters
%start = "1"
%end = "20"
wfopen 200
%r1="200"
%r2="480"
for !i=1 to 2
PAGESELECT {%r{!i}}
mode quiet
smpl @all
smpl 3 1804
equation garch11.ARCH(DERIV=AA) FLUOPNET_{%r{!i}} C TIME SF_D SF_S SF_M D_Q1 D_Q2 FLUOPNET_{%r{!i}}(-1)
garch11.forecast(f=na e) fluopnet_{%r{!i}}_pf
garch11.makegarch var_{%r{!i}}_1p
genr vol_{%r{!i}}=var_{%r{!i}}_1p^.5
'plot vol_{%r{!i}}
freeze (vol_{%r{!i}})
' Export data to Excel
group gru_{%r{!i}} vol_{%r{!i}} var_{%r{!i}}_1p fluopnet_{%r{!i}}_pf
%range ="!A1"+%i
wfsave(type=excel,mode=update) "C:\Users\xxx\Desktop\GARCH_xxx\vol_g.xls" range=%range @smpl %start %end @keep gru_{%r{!i}} vol_{%r{!i}} var_{%r{!i}}_1p fluopnet_{%r{!i}}_pf
'write(A2, t=xls) "C:\Users\emiliolopez\Desktop\GARCH_falabella\vol_g.xls" gru_{%r{!i}}
next
It doesn't look like the counter on the range argument is working because the Excel spreadsheet only shows data for the last iteration, what could the problem be? Thanks.
Re: Adding a counter to write options?
Posted: Fri Jun 05, 2015 10:02 am
by EViews Gareth
I don't see anywhere where you have defined what %i is
Re: Adding a counter to write options?
Posted: Fri Jun 05, 2015 11:03 am
by elac
I don't see anywhere where you have defined what %i is
You are right, I think I got it this time.
Code: Select all
' Export data to Excel
group gru_{%r{!i}} vol_{%r{!i}} var_{%r{!i}}_1p fluopnet_{%r{!i}}_pf
%range = !i+"!a1"
wfsave(type=excel,mode=update) "C:\Users\xxx\Desktop\GARCH_xxx\vol_g.xls" range=%range @smpl %start %end @keep gru_{%r{!i}} vol_{%r{!i}} var_{%r{!i}}_1p fluopnet_{%r{!i}}_pf
I've been reading this post
http://forums.eviews.com/viewtopic.php?f=3&t=11553 and it's been really helpful, but I'm getting the scalar added to string error unlike Ral and I can't see why.
Re: Adding a counter to write options?
Posted: Fri Jun 05, 2015 11:21 am
by EViews Gareth
!i is a number. You're trying to add it to a string, which you can't do. You need to convert the number into a string.
Try:
Although I'm not sure how that will become a valid Excel range. I guess what you actually want to do is increase the column by 3 each time.
I would do something like:
Code: Select all
!j = !i*3-2
%range = "!" + @chr(!j+96) + "1"
a(!i,1) = %range