Importing Multiple Excel Worksheets in EViews7

[gkomba]

Hi,
I have never used the command window before when importing data from excel to EViews. I have learnt that there is a way of importing several worksheets at a go in EViews user forum, but failed to follow the commands provided by the contributors.

What I need is to upload all excel worksheets into a single workfile. I have created my workfile for 5-day week frequency; starting from 1999-21-31 to 2015-07-15, using the command: wfcreate(wf=demo) 1999-12-31 2015-07-15. This took me to the window where I had to fill in the dates manually.

I then saved the workfile using the command: wfsave "C:\Users\HUBS\PhD HULL MAIN\Market Efficiency and Corporate Governance\Behavioural Finance\Chapter 4_Herding\data\demo"

To open the worksheet I used: wfopen(wf=demo,page=Untitled) "C:\Users\HUBS\PhD HULL MAIN\Market Efficiency and Corporate Governance\Behavioural Finance\Chapter 4_Herding\data\Demo kn_p kn_cap.xlsx" range="12/31/1999 7/15/2015"

I got an error message that table "12/31/1999 7/15/2015" does not exist. I am therefore stuck there.

I am kindly asking for your help folks for the command codes. Attached find my demo excel file.

[EViews Gareth]

The range statement has to be a valid Excel range. Usually just the name of the sheet you want, in quotes, will do.

In your case you probably want something like:

Code: Select all

import "Demo kn_p kn_cap.xlsx" range="24evrd"

[gkomba]

Hi Gareth,

Thanks for the quick response, it has worked out.

However, my other issues was how to import multiple worksheets into a sing WF page. I have seen in one of your responses of Tue May 21, 2013 8:11 am; "Re loading multiple worksheet excel files programatically" that:

.........."I should add, for future reference, that if you want to load into a single WF page, rather than multiple pages, you should use WFOPEN followed by IMPORT. Also, for true automation, you can use the @tablenames function to look up the names of the sheets in the Excel file, rather than having to type them out individually".

Can you kindly please, show me the code how to do that, using my demo file attached?

Regards,

Gabriel

[EViews Gareth]

What have you done so far?

[gkomba]

Hello Gareth,

Below are the steps I have done so far:

1. wfcreate(wf=demo) d(1-5) 12/31/1999 7/15/2015
2. wfsave "C:\Users\HUBS\PhD HULL MAIN\Market Efficiency and Corporate Governance\Behavioural Finance\Chapter 4_Herding\data\demo"
3. wfopen "C:\Users\HUBS\PhD HULL MAIN\Market Efficiency and Corporate Governance\Behavioural Finance\Chapter 4_Herding\data\Demo kn_p kn_cap.xlsx"
4. import "C:\Users\HUBS\PhD HULL MAIN\Market Efficiency and Corporate Governance\Behavioural Finance\Chapter 4_Herding\data\Demo kn_p kn_cap.xlsx" range= "24evrd"

In step 3, EViews opened the first worksheet "1ABCL". And in the step4 the 24th worksheet.

[EViews Gareth]

You don't need 1. and 2. They do nothing.

Code: Select all

%names = @tablenames("Demo kn_p kn_cap.xlsx") %n = @word(%names,1) wfopen "Demo kn_p kn_cap.xlsx" range=%n for !i=2 to @wcount(%names) %n = @word(%names, !i) import "Demo kn_p kn_cap.xlsx" range=%n next

[gkomba]

Dear Gareth,

I am stuck again. I decided to practice using your data on "Re: How to enter data into a panel workfile" posted on Fri Jan 08, 2010 3:04 pm, but could not generate any output. This is how I wrote the program

%filename=C:\Users\HUBS\sheetpanel.xlsx
%sheetnames = @tablenames("sheetpanel.xlsx")
%sheetname = @word(%sheetnames,1)
wfopen(wf=panel) "sheetpanel.xlsx" range=%sheetname
for !i=2 to @wcount(%sheetnames)
%sheetname = @word(%sheetnames,!i)
import "sheetpanel.xlsx" range=%sheetname @append @genr crossid=%sheetname
next

This kind of data is very similar to what I am doing, but I could not execute the program. Can you please help me to check if the program is correct.

Regards,

Gabriel

[gkomba]

You don't need 1. and 2. They do nothing.

Code: Select all

%names = @tablenames("Demo kn_p kn_cap.xlsx") %n = @word(%names,1) wfopen "Demo kn_p kn_cap.xlsx" range=%n for !i=2 to @wcount(%names) %n = @word(%names, !i) import "Demo kn_p kn_cap.xlsx" range=%n next

Gareth-
I first tried this several times today, but failed- I dont know if i am entering the code wrongly? I would like a quicker option to import all these worksheets- over 500 worksheets in 10 workbooks so its a tedious processing doing it one by one.

Regards,

Gabriel

[EViews Gareth]

You aren't really helping yourself much.

It is nearly impossible for anyone to help you if you don't give explicit instructions on what you have done, what happened, and how it went wrong. Saying things like "but failed" gives no indication of what you are doing wrong.

[gkomba]

Okay Gareth,

This is how I wrote the code.

open "C:\Users\HUBS\PhD HULL MAIN\Market Efficiency and Corporate Governance\Behavioural Finance\Chapter 4_Herding\data\demo.wf1"
import "C:\Users\HUBS\PhD HULL MAIN\Market Efficiency and Corporate Governance\Behavioural Finance\Chapter 4_Herding\data\Demo kn_p kn_cap.xlsx" range= "24evrd"
%names = @tablenames("C:\Users\HUBS\PhD HULL MAIN\Market Efficiency and Corporate Governance\Behavioural Finance\Chapter 4_Herding\data\Demo kn_p kn_cap.xlsx")
%n = @word(%names,1)
wfopen "C:\Users\HUBS\PhD HULL MAIN\Market Efficiency and Corporate Governance\Behavioural Finance\Chapter 4_Herding\data\Demo kn_p kn_cap.xlsx" range=%n
for !i=2 to @wcount(%names)
%n = @word(%names, !i)
import "C:\Users\HUBS\PhD HULL MAIN\Market Efficiency and Corporate Governance\Behavioural Finance\Chapter 4_Herding\data\Demo kn_p kn_cap.xlsx" range=%n
next

When I executed, it returned the following error message (see screen print attached): "Flow of control statement executed from the command line"

[startz]

Programs with loops need to go into a Program window, not the command window.

[gkomba]

how do i get to the program window please?

[startz]

File/New/program

[gkomba]

Dear Startz,

Thank you so much for your help. I have finally been successful.


My regards,

Gabriel