Loading multiworksheet Excel files programatically

[narulkar]

Hi folks,

I'm new to EViews and leaning my way slowly on version 8. One problem statement that I'm trying to solve as a first task is to automate loading and conversion of muti-worksheet excel files to a single EViews workfile (.wf1) with multiple worksheets as corresponding multiple pages within the workfile. The problem that I realized was that using commands such as pagecreate or wfopen using excel worksheet name as a qualifier EViews program opens them one at a time as .wf1 file and if followed by a wfsave or pagesave, saves only the last worksheet as the page within the .wf1 file and not all those which were opened in the program. To give an example, if you use below program:

wfcreate(wf=regress) q 2000 2013
wfsave "c:\regress"

wfopen(page=Exchange) "H:\EURCHG.xlsx" range="EURCHG-Monthly"
pagesave "c:\regress"
wfopen(page=Euro2Yr) "H:\EURCHG.xlsx" range="GGR Euro Govt Bond 2 YR"
pagesave "c:\regress"

wfsave "c:\regress"
'-----------------------------------------------------------------------------------------------------

Will result in regress.wf1 having only Euro2Yr page with data from "GGR Euro Govt Bond 2 YR" worksheet of EURCHG.xlsx. We need both the worksheets loaded as different pages within the same workfile. To sort this our, I used below:

' Step 1 - Create the parent workfile
' Mention the precise time series for which you have data in all the tabs
' if data is not in sync, some additional changes will be needed while
' reading such data down the line.
'-----------------------------------------------------------------------------------------------------
wfcreate(wf=regress) q 2000 2013
wfsave "c:\regress"

'-----------------------------------------------------------------------------------------------------
' Step 2 - Read the individual worksheets of the excel files you are inerested
' Here, if the frequency of the data is not same, additional parms should
' be specified so that the data is in sync and doesn't get corrupted while
' final aggregation.
'-----------------------------------------------------------------------------------------------------
wfopen(page=Exchange) "H:\EURCHG.xlsx" range="EURCHG-Monthly"
pagesave "c:\regress1"
wfopen(page=Euro2Yr) "H:\EURCHG.xlsx" range="GGR Euro Govt Bond 2 YR"
pagesave "c:\regress1"

'-----------------------------------------------------------------------------------------------------
' Step 3 - Consolidate the read files into 1
'-----------------------------------------------------------------------------------------------------
wfopen "c:\regress"
pageload "c:\regress1"
pageload "c:\regress2"
wfsave "c:\regress"
'-----------------------------------------------------------------------------------------------------

Now this is a poor way of solving the problem as now, we have two unnecessary files namely - regress1 and regress2 - for which there is no future use as they were created solely for the purpose of enabling consolidation. Now, I could not find a single command to delete workfiles directly, neither could I find any commands that allow creation of "temporary" workfiles that would delete themselves after the life of the program that creates them.

So can you suggest any alternative for this problem?

[narulkar]

A little errata:

'-----------------------------------------------------------------------------------------------------
' Step 2 - Read the individual worksheets of the excel files you are inerested
' Here, if the frequency of the data is not same, additional parms should
' be specified so that the data is in sync and doesn't get corrupted while
' final aggregation.
'-----------------------------------------------------------------------------------------------------
wfopen(page=Exchange) "H:\EURCHG.xlsx" range="EURCHG-Monthly"
pagesave "c:\regress1"
wfopen(page=Euro2Yr) "H:\EURCHG.xlsx" range="GGR Euro Govt Bond 2 YR"
pagesave "c:\regress1"

Should have been:
wfopen(page=Exchange) "H:\EURCHG.xlsx" range="EURCHG-Monthly"
pagesave "c:\regress1"
wfopen(page=Euro2Yr) "H:\EURCHG.xlsx" range="GGR Euro Govt Bond 2 YR"
pagesave "c:\regress2"

[jthodge]

Your first command should be wfopen to import the first worksheet of your xlsx file into a new workfile.

Your next commands should use pageload to open the remaining worksheets into separate pages of the existing workfile.

Code: Select all

wfcreate(wf=regress) q 2000 2013 pageload(page=Exchange) "H:\EURCHG.xlsx" range="EURCHG-Monthly" pageload(page=Euro2Yr) "H:\EURCHG.xlsx" range="GGR Euro Govt Bond 2 YR"

[jthodge]

I had a little errata of my own. You don't need to do wfcreate first. Here's the correct code:

Code: Select all

wfopen(wf=regress,page=Exchange) "H:\EURCHG.xlsx" range="EURCHG-Monthly" pageload(page=Euro2Yr) "H:\EURCHG.xlsx" range="GGR Euro Govt Bond 2 YR"

[narulkar]

Looks like too much experimentation without proper direction takes one nowhere - so thanks a lot! Amazing simplicity and great solution :) This thread can be closed.. For future users, I've used what jthodge suggested above and finalized with a wfsave command. Thanks again..

Cheers,
Gunjan

[EViews Gareth]

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.

[narulkar]

Hi Gareth,

Thanks for the inputs, will remember the first point regarding consolidation in single worksheet.

Regarding complete automation, tomorrow will try the function and program and get back to the forum in case if I need further handholding.

Cheers
Gunjan