Adding a counter to write options?

[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.

[EViews Gareth]

Use wfsave rather than write. There is a range= argument that lets you specify the Excel range to save to.

[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.

[EViews Gareth]

I don't see anywhere where you have defined what %i is

[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.

[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:

Code: Select all

%range = @str(!i) + "!a1"

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