Exporting data to Excel

For questions regarding the import, export and manipulation of data in EViews, including graphing and basic statistics.

Moderators: EViews Gareth, EViews Jason, EViews Steve, EViews Moderator

ral
Posts: 23
Joined: Tue Oct 21, 2014 2:44 pm

Exporting data to Excel

Postby ral » Mon Feb 09, 2015 5:04 pm

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.

EViews Gareth
Fe ddaethom, fe welon, fe amcangyfrifon
Posts: 11876
Joined: Tue Sep 16, 2008 5:38 pm

Re: Exporting data to Excel

Postby EViews Gareth » Mon Feb 09, 2015 5:15 pm

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

ral
Posts: 23
Joined: Tue Oct 21, 2014 2:44 pm

Re: Exporting data to Excel

Postby ral » Tue Feb 10, 2015 8:43 am

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.

EViews Gareth
Fe ddaethom, fe welon, fe amcangyfrifon
Posts: 11876
Joined: Tue Sep 16, 2008 5:38 pm

Re: Exporting data to Excel

Postby EViews Gareth » Tue Feb 10, 2015 9:10 am

Did you specify the range you want to save to? Just give each country a different sheet name.
Follow us on Twitter @IHSEViews

ral
Posts: 23
Joined: Tue Oct 21, 2014 2:44 pm

Re: Exporting data to Excel

Postby ral » Tue Feb 10, 2015 9:37 am

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

EViews Gareth
Fe ddaethom, fe welon, fe amcangyfrifon
Posts: 11876
Joined: Tue Sep 16, 2008 5:38 pm

Re: Exporting data to Excel

Postby EViews Gareth » Tue Feb 10, 2015 9:46 am

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

ral
Posts: 23
Joined: Tue Oct 21, 2014 2:44 pm

Re: Exporting data to Excel

Postby ral » Tue Feb 10, 2015 10:15 am

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.

EViews Gareth
Fe ddaethom, fe welon, fe amcangyfrifon
Posts: 11876
Joined: Tue Sep 16, 2008 5:38 pm

Re: Exporting data to Excel

Postby EViews Gareth » Tue Feb 10, 2015 10:25 am

You need type=excelxml
Follow us on Twitter @IHSEViews

ral
Posts: 23
Joined: Tue Oct 21, 2014 2:44 pm

Re: Exporting data to Excel

Postby ral » Tue Feb 10, 2015 11:34 am

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

EViews Gareth
Fe ddaethom, fe welon, fe amcangyfrifon
Posts: 11876
Joined: Tue Sep 16, 2008 5:38 pm

Re: Exporting data to Excel

Postby EViews Gareth » Tue Feb 10, 2015 11:39 am

I'd need to see the full program and the workfile
Follow us on Twitter @IHSEViews

ral
Posts: 23
Joined: Tue Oct 21, 2014 2:44 pm

Re: Exporting data to Excel

Postby ral » Tue Feb 10, 2015 11:48 am

Here is the program and the workfile. Thank you for your time, Gareth.

Ron
Attachments
cli.wf1
Workfile
(293.39 KiB) Downloaded 100 times
cli.prg
Program
(2.45 KiB) Downloaded 122 times

ral
Posts: 23
Joined: Tue Oct 21, 2014 2:44 pm

Re: Exporting data to Excel

Postby ral » Tue Feb 10, 2015 11:53 am

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 137 times

EViews Gareth
Fe ddaethom, fe welon, fe amcangyfrifon
Posts: 11876
Joined: Tue Sep 16, 2008 5:38 pm

Re: Exporting data to Excel

Postby EViews Gareth » Tue Feb 10, 2015 12:24 pm

Works for me. Are you sure your copy of EViews is up to date?
Follow us on Twitter @IHSEViews

ral
Posts: 23
Joined: Tue Oct 21, 2014 2:44 pm

Re: Exporting data to Excel

Postby ral » Tue Feb 10, 2015 12:41 pm

It works for me now, too. I rebooted my computer. Thanks.


Return to “Data Manipulation”

Who is online

Users browsing this forum: No registered users and 54 guests