Transpose

[BK03]

Hi Gareth,

I have the attached file and am looking to write a program that inputs the data from excel into Eviews. The data needs to be transposed, however, as the dates are horizontally across rather than vertically down the page. I am also looking to group these variables by BS_cash, BS_obscash, BS_acctsrec etc. etc so that a time series of all the sectors is visible for each variable. Many thanks.

[EViews Gareth]

What have you tried so far?

[BK03]

Thanks, Gareth

I have tried the following:

wfcreate(wf=semi-annual, page = "BSStoxx600") s 1990 2017
%mypath = @runpath
cd %mypath

import Stoxx600_BSdata.xlsx byrow range="EViews" @freq s 1990s1

for %y BS_Cash BS_obscash

group g_{%y}

for %z Cons_Dis Cons_Stap Energy Fin Health Indust IT Mat Telecom Utilities Tot Totexfin Totexfin&energy

series {%z}_{%y}

g_{%y}.add {%z}_{%y}

It keeps saying the For statement is unterminated. The above is done only for 2 of the variables as a test.

[startz]

Each FOR statement needs to be matched with a NEXT statement

[BK03]

Ah yes, thanks Jason.

One other question.

I am trying to create a new variable which is an average of total sum of all observations divided by the number of observations.

I think I need to create a loop in Eviews, can you help me? If I have 4 variables BS_cash and BS_acctrec and BS_obscash and BS_obsacctrec where obs is the number of observations I think I would need to do something like

for %j BS_cash BS_acctrec
for %I BS_obscash BS_obsacctrec

series avg = avg_(%j)/(%I)
next
next

[EViews Gareth]

Not surprisingly, most statistics packages have built in functions for calculating means.
http://www.eviews.com/help/helpintro.ht ... 81.06.html

[BK03]

So I only have the total imported in and then separately the number of observations so I can't use that function as I don't have the individual observations making up the total. Thanks.

[EViews Gareth]

So you just want to divide one number by another?

[BK03]

Correct, but it needs to be a loop

because BS_cash needs to be divided by BS_obscash, BS_acctrec needs to be divided by BS_obsacctrec etc etc.

Thanks.

[EViews Gareth]

Code: Select all

for %j BS_cash BS_acctrec for %I BS_obscash BS_obsacctrec series avg = avg_{%j}/{%I} next next

[BK03]

so for each variable, for instance BS_cash there are different "Sectors"

CONS_DISC_
CONS_STAP_
ENERGY_
FIN_
HEALTH_
INDUST_
IT_
MAT_
TELECOM_
UTILITIES_
TOT_
TOTEXFIN_
TOTEXENERGY_
TOTEXFIN&ENERGY_

does this mean I need to list out each with "Sector"_BS_Cash ? for %j and "Sector"_BS_obscash for %I. I have 15 variables I am trying to this for times 14 sectors so this would be quite time consuming. Is there a faster way to do this?

In the next part I group them by "Sector" and Balance Sheet item. Maybe this group could be used to do the division?

for %y BS_cash BS_obscash BS_acctrec BS_obsacctrec BS_invent BS_obsinvent BS_netfxda BS_obsnetfxda BS_totasst BS_obstotasst BS_LTdebt BS_obsLTdebt BS_totliab BS_obstotliab BS_retearn BS_obsretearn BS_toteq BS_obstoteq BS_totlns BS_obstotlns BS_dmddep BS_obsdmddep BS_STbrrw BS_obsSTbrrw avg_BS_cash avg_BS_acctrec avg_invent avg_BS_netfxda

group g_{%y}

for %z Cons_Dis Cons_Stap Energy Fin Health Indust IT Mat Telecom Utilities Tot Tot_xFin Tot_xFinen

series {%z}_{%y}

g_{%y}.add {%z}_{%y}

next
next

[BK03]

The error I keep getting is AVG_("BS_cash") is not defined in "SERIES AVG = AVG_("BS_CASH") / ("BS_OBSCASH")".

[EViews Glenn]

You need to use {} (braces), not () (parentheses).

[BK03]

Thanks, Glen

I've changed that but I still get that my first variable is undefined. Any ideas?

[BK03]

Hi Glen and Gareth,

I am working on a program and am trying to figure out how to specify a loop command for a particular worksheet in Eviews.

I load in 2 excel worksheets from the same workbook which become separate Eviews tabs. Howver, when I try to execute a loop it does it to both tabs in the EViews workfile!

Many thanks