create groups

[martingale]

Hi

I am trying to systemize data based on lists in Datastream. One list encompasses all stocks in Oslo Stock Exchange. More specialized lists for different liquidity categories and different industry sectors are subsets of the list of all stocks. Financial data using these lists is downloaded to Excel for various variables, for instance bid price(PB), earnings per share(EPS), earnings before interest, tax, depreciation and amortization(EBITDA) and so on.
I found the following handy code in your pages to transfer from Excel to Eviews:

Code: Select all

%filename = "c:\temp\sheetpanel.xlsx" 'file name of the file to be opened %sheetnames = @tablenames(%filename) 'find the names of the sheets in that file %sheetname = @word(%sheetnames,1) 'get the first sheet name wfopen(wf=panel,page=%sheetname) %filename range=%sheetname 'open the first sheet as a new workfile (with name=panel, and pagename=the first sheet name) 'loop through the remaining sheets, loading them into the workfile one at a time for !i=2 to @wcount(%sheetnames) %sheetname = @word(%sheetnames,!i) 'get the name of the next sheet pageload(page=%sheetname) %filename range=%sheetname 'load the next sheet next

Now I would like to create groups of the various lists for the various financial variables, using a code.

I give an example of a group in EViews, created "manually", based on data downloaded using a list in Datastream for bid price (PB), which consists of the most liquid stocks:
n_akso_pb_
n_alge_pb_
n_deto_pb_
n_dnb_pb_
n_dno_pb_
n_foe_pb_
n_fro_pb_
n_gjf_pb_
n_gol_pb_
n_mhg_pb_
n_nhy_pb_
n_ork_pb_
n_pgs_pb_
n_prs_pb_
n_rcl_pb_
n_rec_pb_
n_sch_pb_
n_sdrl_pb_
n_song_pb_
n_stb_pb_
n_stl_pb_
n_subc_pb_
n_tel_pb_
n_tgs_pb_
n_yara_pb_

Can I use a code to make groups for the same stocks (the stock names are in the middle) but for other financial variables, for instance for EPS-data? Much obliged for any ansewrs and suggestions.

[martingale]

Code: Select all

group OBX *akso* *alge* *deto* *dnb* *dno* *foe* *fro* *gjf* *gol* *mhg* *nhy* *ork* *pgs* *prs* *rcl* *rec* *sch* *sdrl* *song* *stb* *stl* *tel* *tgs* *yara*

Eureka( :D ),this seems to work

[martingale]

I try to recode NA to 0 for 350 series put together to one group.

I found the code:

Code: Select all

X=@recode(X=NA,0,X)

This works for one series at a time.

How can I automate recoding for all series in the same group?

Thanx

[EViews Gareth]

Loop through the series in the group one at a time.

[martingale]

this code seems to work(ebitda_mn_a is the name of the group):

Code: Select all

for !i=1 to ebitda_mn_a.count %name = ebitda_mn_a.@seriesname(!i) {%name} = @recode({%name}=na,0,{%name}) next

Should I start the code with

Code: Select all

group ebitda_mn_a * ebitda_mn_a.drop resid

?

To change 0 back to NA:

Code: Select all

for !i=1 to ebitda_mn_a.count %name = ebitda_mn_a.@seriesname(!i) {%name} = @recode({%name}=0,na,{%name}) next

When I have NAs and try to run descriptive statistic for the group as a common I get the message "insufficient number of observations". When I have 0's I get descriptive statistic for the group as a common but I don't know if the 0's introduce some major error in the calculations.

How could I exclude individual series(the ones with no observations at all or with few observations) from a group sample?

[martingale]

I've got the following problem in EViews 7:
I have a work file with 2 pages. Each page contains several hundreds of series. Each series represent a company. In one page I have data for volume. In the other page I have data for standard deviation of estimates. The series are put together in different groups called "OSEAX", "OSE" and AXESS", the same groups are in both pages but the series contain different financial variables. The series names are "n_companycode_vo_" in the page for volume and "n_companycode_ptsd_" in the other page with the standard deviation. I want to regress the series for volume on the series for st.dev., for the same companycode, for instance n_acta_vo_=c(1)+c(2)*n_acta_ptsd_ and store the results.

How can this be done efficiently? I am afraid I don't know enough programming to do it myself. Many thanx in advance.

[EViews Gareth]

You'll have to copy them onto the same page, then loop through the members of the group again, creating an equation for each.

[martingale]

Putting together codes from different threads, I got something like this:

for !i=1 to ebitda_mn_a.count
%nameebitda =ebitda_mn_a.@seriesname(!i)
for !j=1 to returns.count
%namereturns =returns.@seriesname(!j)
if @mid( {%nameebitda},12,17)=@mid({%namereturns}, 8,13)
Equation eq {%nameebitda}=c(1)+c(2)*{%namereturns}
End if
next
next

What code will make the program recognize that two series with different financial data concern the same company? for instance ebitda_mn_a404774 and returns404774, where 404774 denotes the company code? The bold line didn't work in this respect.

[EViews Gareth]

Is there logic to the way the series are named?

Assuming that every series has a name, in the returns group, of RETURNSXXXXXX where XXXXXXX refers to the company code, and that same company code is used in the ebitda group with a similar pattern, then you just need something like:

Code: Select all

for !i=1 to returns.@count %code = @right(returns.@seriesname(!i), 6) 'grab the last 6 digits of the series name. This should be the company code %namereturn = "returns" + %code %nameebitda = "ebitda_mn_a" + %code equation eq{%code}.ls {%nameebitda} c {%namereturn} next

[martingale]

Thanx a lot Gareth!

[martingale]

I have run a test which created a big number of equations. How can I store the result statistics( the t-values and R)? thanx

[EViews Gareth]

http://forums.eviews.com/viewtopic.php?f=5&t=1638

[martingale]

for !i=1 to returns.@count
%code = @right(returns.@seriesname(!i), 6) 'grab the last 6 digits of the series name. This should be the company code
%namereturn = "returns" + %code
%nameptgtsd = "delta_p_tgt_sd_a" + %code
equation eq_r_d_ptgt_sd_{%code}.ls {%namereturn} c {%nameptgtsd}
matrix(?,?) coefs
colplace(coefs, eq.@coefs, !i)
next

in the examples given at the referred thread it is assumed to be known in advance the number of columns and rows of the matrix used to store the coefficients (the bold line), what if you don't know?

[EViews Gareth]

Make one that is too big, and just fill it in row by row (or column by column). You can always trim off the excess at the end.

[martingale]

i tried this code:

'create matrix to store coefficients
matrix(1000,1000) coefs
'create matrix to store standarderrors
matrix(1000,1000) se
'create matrix to store tstatistics
matrix(1000,1000) tstats
'create empty equation to be used inside the loop
equation eq
'counter of how many equations we have run
!rowcounter=1
'run pairwise regressions between series in groups
for !i=1 to returns.@count
%code = @right(returns.@seriesname(!i), 6) 'grab the last 6 digits of the series name. This should be the company code
%namereturn = "returns" + %code
%nameptgtsd = "delta_p_tgt_sd_a" + %code
equation eq_r_d_ptgt_sd_{%code}.ls {%namereturn} c {%nameptgtsd}
r2s(!rowcounter) = eq_r_d_ptgt_sd_{%code}.@r2
r2a(!rowcounter) = eq_r_d_ptgt_sd_{%code}.@rbar2
!rowcounter = !rowcounter+1
'store coefficients, standard errors and t-statistics into matrix
colplace(coefs, eq_r_d_ptgt_sd_{%code}.@coefs, !i)
colplace(se, eq_r_d_ptgt_sd_{%code}.@stderrs, !i)
colplace(tstats, eq_r_d_ptgt_sd_{%code}.@tstats, !i)
next

The bold lines produce matrices which contain just zeros. What is wrong?