Counting and Extracting Specific Information

[blue2011]

Hello Dear Forum Members

I have my data in the following format:

CompanyID DailyPrice DataDate
100 4.571 20040301
100
100
....
120
120
120
....
130
130
130
130

I retrieved the daily stock prices from a data provider from March 2004 till March 2005 but not each company's stock is traded every trading day. Some stocks have 260 days of observation and some have only 35 days. What I would like to do now is to count for how many days I have observation for each company and remove the companies whose stock is traded less than a threshold day (e.g.125) from the workfile. The company identifiers are in four, five and six digits (100, 120, etc are not the real IDs). If a company's stock is not traded on a specific day, it is not reported at all. In another words, there is not a empty row or N/A. Such days are simply skipped.

I would really appreciate if you could help me write a program for this task. I looked at the previous posts, but I have not encountered such an issue.

Thanks

[startz]

Possibly

Code: Select all

smpl if @obsby(companyid,companyid)>125

[blue2011]

Possibly

Code: Select all

smpl if @obsby(companyid,companyid)>125

Thank you so much startz for your suggestion. It works great, but I ran into further issues. Here is the line of codes:

smpl if prirow=iid and @obsby(gvkey,gvkey)>=130 and monthend=1

series primary_liquid=@recode(prirow=iid and @obsby(gvkey,gvkey)>=130 and monthend=1,gvkey,na)

The first line works where I have imposed three conditions. When I wanted to see the newly created series, I applied the code in the second line. It worked once but when I re-ran the code, all of the cells were NA (still it is the case). I don't understand why the first line would work, but the second line would not.

I also have a question on viewing all the variables together. As I mentioned first, I have daily stock price data. How can I make a group where the newly arranged company identifiers correspond to their corresponding prices? I executed all of the manipulations on the company identifier (gvkey), but I want the rest of the variables to be aligned with these manipulations.

I have been working on this right after you posted your response with no success. It would really appreciate if you could further help me.

Thank you

[startz]

I don't understand why your code doesn't work either. You might try

Code: Select all

series primary_liquid smpl if prirow=iid and @obsby(gvkey,gvkey)>=130 and monthend=1 primary_liquid=gvkey

In a group spreadsheet, you can sort the appearance of observations according to any variable you like.

[blue2011]

Thanks a lot, startz. I modified the code the following way:

group complete
smpl if prirow=id and @obsby(gvkey,gvkey)>=130 and monthend=1
complete.add gvkey price

I have one remaining issue with this modification though. I want Eviews to execute the "smpl" command in a timely order. First apply, smpl if prirow=id and then for the remaining observations, apply @obsby(gvkey,gvkey)>=130 and for the remaining ones, apply monthend=1. Is there a way to do this?

Thanks a lot for your help and patience.

[startz]

You can probably finagle this by supplying @obsby with a smpl as a third argument.

[blue2011]

Thanks a lot again for your quick feedback. I tried @obsby as the third argument as follows:

group complete
smpl if prirow=iid and monthend=1 and @obsby(gvkey,gvkey)>=130
complete.add gvkey iid prirow datadate ajexdi prccd trfd fic monthend

Nevertheless, it is still not following the order I want it to. I have been playing around the code, but it looks like I cannot figure this out. Is there another way to execute "smpl" command the way I described in my previous post? Thank you for your time.

[EViews Gareth]

I don't understand what you're asking. Or, rather, I don't understand why you code wasn't doing what you were describing.

[startz]

Maybe

Code: Select all

smpl if prirow=iid and monthend=1 and @obsby(gvkey,gvkey, "if prirow=iid and monthend=1")>=130

[blue2011]

Thank you so much for your help. Now the program works the way I want.

Best Regards

[blue2011]

I actually have another follow-up question if you don't mind taking a look at. After using several filters, I put the filtered series in a group.What I want to do is to calculate the stock returns for each id (company identifier) by dividing the current month's adjusted price by the previous month's adjusted price minus 1. The challenge I am having here is that the observations in the group(lets call it datam) do not go like 1,2,3 etc, but 10, 33, 54 etc ( referring to their original position in the unfiltered workfile). Therefore, I could not lag the adjusted_price series and do the necessary computation. Another issue is that I don't want to divide one company's stock price with another company's stock price. For example,
obs id closing_day adjusted_price
10 2338 19960628 5.238762035
33 2338 19960731 5.625101418
54 2338 19960830 5.872358623
75 2338 19960930 6.182102585
98 2338 19961031 5.528453522
119 2338 19961129 5.331571274
139 2338 19961231 5.67808403
161 2338 19970131 5.166190186
181 2338 19970228 5.158314896
200 2338 19970327 5.489077072
222 2338 19970430 4.939003929
242 2338 19970530 4.622713037
263 2338 19970630 4.111781596
273 2410 19960628 5.281294795
296 2410 19960731 5.449697464
317 2410 19960830 5.864354528
338 2410 19960930 6.266022647
361 2410 19961031 6.247120618
382 2410 19961129 6.550459327
402 2410 19961231 6.684044805
.........................................

I want the formula to restart when I get to the next id (here at obs 273). I know that datam(3) refers to the third series in the group which is the adjusted price. As mentioned above, I tried

series returns
returns=datam(3)/datam(3)(-1)
datam.add returns

but the formula did not work at all. It also does not account for the fact that there is more than one company in the group.

Could you recommend me a way of getting around these issues?

I also attached a screen-shot of my workfile to clarify the issue that I am having.

I really appreciate your help.

Best Regards