Exporting data to Excel
Moderators: EViews Gareth, EViews Jason, EViews Steve, EViews Moderator
Exporting data to Excel
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.
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.
-
- Fe ddaethom, fe welon, fe amcangyfrifon
- Posts: 13319
- Joined: Tue Sep 16, 2008 5:38 pm
Re: Exporting data to Excel
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.
Follow us on Twitter @IHSEViews
Re: Exporting data to Excel
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.
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.
-
- Fe ddaethom, fe welon, fe amcangyfrifon
- Posts: 13319
- Joined: Tue Sep 16, 2008 5:38 pm
Re: Exporting data to Excel
Did you specify the range you want to save to? Just give each country a different sheet name.
Follow us on Twitter @IHSEViews
Re: Exporting data to Excel
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
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
-
- Fe ddaethom, fe welon, fe amcangyfrifon
- Posts: 13319
- Joined: Tue Sep 16, 2008 5:38 pm
Re: Exporting data to Excel
Code: Select all
%range = %x+"!a1"
wfsave(type=excel,mode=update) "p:...\cli.xlsx" range=%range @smpl %start %end @keep clioutput_{%x}
Follow us on Twitter @IHSEViews
Re: Exporting data to Excel
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.
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.
-
- Fe ddaethom, fe welon, fe amcangyfrifon
- Posts: 13319
- Joined: Tue Sep 16, 2008 5:38 pm
Re: Exporting data to Excel
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
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
-
- Fe ddaethom, fe welon, fe amcangyfrifon
- Posts: 13319
- Joined: Tue Sep 16, 2008 5:38 pm
Re: Exporting data to Excel
I'd need to see the full program and the workfile
Follow us on Twitter @IHSEViews
Re: Exporting data to Excel
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.
- Attachments
-
- cli.prg
- (2.43 KiB) Downloaded 486 times
-
- Fe ddaethom, fe welon, fe amcangyfrifon
- Posts: 13319
- Joined: Tue Sep 16, 2008 5:38 pm
Re: Exporting data to Excel
Works for me. Are you sure your copy of EViews is up to date?
Follow us on Twitter @IHSEViews
Re: Exporting data to Excel
It works for me now, too. I rebooted my computer. Thanks.
Who is online
Users browsing this forum: No registered users and 17 guests