Keeping data only if it is observed n many times

[Britt Blom]

Hello,

I'm working with a data set of all non-financial firms on the Swiss stock exchange.
Using daily returns I have created monthly returns.
I'm using a timeframe of July 2009 till June 2019. I would like to only keep the firms if they have an observation for all those months, i.e. they have 120 monthly returns.
Is there an easy way to delete all firms that do not have an observation for all 120 months?

Kind regards.

[Andrew Musau]

You need to ensure that you have a monthly time variable recognized by Stata. Then something like

xtset firm month, monthly
keep if inrange(month, tm(2009m7), tm(2019m6))
bys firm: keep if _N==120

[Britt Blom]

Hi Andrew, thank you for the answer.

To calculate monthly returns I did the following:
gen mofd = mofd(DataDateDailyPrices)
format mofd %tm

bys CompanyName mofd: keep if _n == _N
bys CompanyName (mofd): gen rim=ln(PriceCloseDaily/PriceCloseDaily[_n-1])

so what would be my following Stata code to keep only if n=120?

[Ulrich Wohak]

your error is one this line here:

bys CompanyName mofd: keep if _n == _N

which effectively keeps the last observation per firm-month. I don't think this is what you want to do. Assuming you have an observations for each month, use this instead:

bys CompanyName: keep if _N == 120

[Andrew Musau]

Same as #2.

keep if inrange(mofd, tm(2009m7), tm(2019m6))
bys CompanyName: keep if _N==120

ADDED IN EDIT: I slightly disagree with #4. You are keeping the last stock prices in a month in #3 as you have daily prices. Append this code to your code in #3 and you should be good to go. To ensure that it is the last price, you need to sort by date:

bys CompanyName mofd (DataDateDailyPrices): keep if _n == _N

[Ulrich Wohak]

You're right Andrew - thanks for pointing this out!

[Britt Blom]

Thank you! It worked out the way I wanted.

Another question tho:
I now have a 120 monthly values for each stock.
In another excel file I have the monthly risk free rate for 120 months as well.
How can I import this excel file with the risk free rates and after that subtract these from the monthly returns? I need to find the excess returns for each month for each stock. i.e. 120 excess returns for each stock.

[Ulrich Wohak]

A bit hard to say without actually seeing the data/more information. You will probably want to save a .dta file of the risk-free rates data first. Then, load your original data set and do a m:1 merge with the risk-free .dta file. Then you can just subtract the return from the risk-free rate to get the excess return (?).