Page 1 of 1

Exporting data to Excel

Posted: Mon Feb 09, 2015 5:04 pm
by ral
Hi,

I have a question about how to export series to multiple Excel workbooks.

My program loops over countries, runs some regressions, and produces three series for each country. I would like to export the three series for each country to a separate Excel workbook. (Separate spreadsheets would work as well.) I tried doing so with the following code:

for %x {%countries}

[...run regressions for each country that produce three series, cli_{%x}, delta_{%x}, and acc_{%x}...]

' Export data to Excel
group clioutput_{%x} cli_{%x} delta_{%x} acc_{%x}
wfsave(type=excel) "p:\requests\cli\cli_{%x}" @smpl %start %end @keep clioutput_{%x}

next

This code produces a single Excel workbook called cli_{%x}.xlsm and overwrites the previous country's three series with the three series from the next country in the loop.

Is it possible to do what I want to do in a loop structure?

Thank you.

Re: Exporting data to Excel

Posted: Mon Feb 09, 2015 5:15 pm
by EViews Gareth
You need to use the mode=update option, and specify a range for where you want the data to go. There is an example in the wfsave section of the Command and Programming reference.

Re: Exporting data to Excel

Posted: Tue Feb 10, 2015 8:43 am
by ral
Thank you for the response.

I have a further question. In the end, I would like to have separate worksheets for each country (e.g., Brazil, Korea, and so on) that contain the three series for the country computed by the program. Using "mode=update" does not seem to permit me to loop over the countries and export only the three series for each country to a separate worksheet (e.g., separate sheets labelled "Brazil", "Korea", etc.).

I want to automate the process as much as possible. There are many countries in the data set, and the three series for each country will be updated on a regular basis. I am looking for a way to change dynamically the sheet that I export the output to.

If what I proposed above is not possible, do you have suggestions for a work around?

Thanks again.

Re: Exporting data to Excel

Posted: Tue Feb 10, 2015 9:10 am
by EViews Gareth
Did you specify the range you want to save to? Just give each country a different sheet name.

Re: Exporting data to Excel

Posted: Tue Feb 10, 2015 9:37 am
by ral
It is possible that I misunderstood your suggestion. Based on your response, I changed the program in the following way:

for %x {%countries}

[...run regressions for each country that produce three series, cli_{%x}, delta_{%x}, and acc_{%x}...]

' Export data to Excel
group clioutput_{%x} cli_{%x} delta_{%x} acc_{%x}
wfsave(type=excel,mode=update) "p:...\cli.xlsx" range="{%x}!a1" @smpl %start %end @keep clioutput_{%x}

next

I tried to give each country a different sheet name using the country counter, x. My intent was to export the groups clioutput (indexed by country) to separate sheets (also indexed by country) in the workbook cli.xlsx. That is why I included the {%x} in the range command.

What the program does is overwrite the exported group in a sheet named {x%} in the workbook cli.xlsx. After the loop ends, the only data that remain on the worksheet are the three series contained in the group clioutput from the last country in the list.

How do I modify the command above to get the result I want?

Thanks.

Ron

Re: Exporting data to Excel

Posted: Tue Feb 10, 2015 9:46 am
by EViews Gareth

Code: Select all

%range = %x+"!a1"
wfsave(type=excel,mode=update) "p:...\cli.xlsx" range=%range @smpl %start %end @keep clioutput_{%x}

Re: Exporting data to Excel

Posted: Tue Feb 10, 2015 10:15 am
by ral
Thank you. I was being thick about how to define the counter.

One final issue: Now I get the following error after the program exports the group from the first country:

File 'p:...\cli.xlsx' does not exist' in "wfsave(type=excel,mode=update) "p:...\cli.xlsx" range=KOR!A1 @smpl %start %end @keep clioutput_KOR"

Korea (=KOR) is the second country in the list. I checked that the file cli.xlsm is closed and is located in the correct directory. The sheet "KOR" also exists in the workbook.

Re: Exporting data to Excel

Posted: Tue Feb 10, 2015 10:25 am
by EViews Gareth
You need type=excelxml

Re: Exporting data to Excel

Posted: Tue Feb 10, 2015 11:34 am
by ral
Hi,

Something is still not working right. I get the same error message as before after modifying the program in the way that you suggested:

wfsave(type=excelxml,mode=update) "p:\requests\cli\cli.xlsx" range=%range @smpl %start %end @keep clioutput_{%x}

When I run this command outside of the loop, the data are exported to the sheet "KOR" fine:

wfsave(type=excelxml,mode=update) "p:\requests\cli\cli.xlsx" range="KOR!A1" @smpl %start %end @keep clioutput_KOR

I checked the country list and a couple of other things, but they look fine. Any idea what the issue is?

Thank you.

Ron

Re: Exporting data to Excel

Posted: Tue Feb 10, 2015 11:39 am
by EViews Gareth
I'd need to see the full program and the workfile

Re: Exporting data to Excel

Posted: Tue Feb 10, 2015 11:48 am
by ral
Here is the program and the workfile. Thank you for your time, Gareth.

Ron

Re: Exporting data to Excel

Posted: Tue Feb 10, 2015 11:53 am
by ral
In the program I just sent you, there is a code fragment based on a change I am making to the program. (It is unrelated to the problem I am having.) The attached file removes it. Apologies.

Re: Exporting data to Excel

Posted: Tue Feb 10, 2015 12:24 pm
by EViews Gareth
Works for me. Are you sure your copy of EViews is up to date?

Re: Exporting data to Excel

Posted: Tue Feb 10, 2015 12:41 pm
by ral
It works for me now, too. I rebooted my computer. Thanks.