Adjusting 1 code to make it work for multiple cases instead of 1

[LydiaSmit]

Last request on Statalist.

 

gen chgdec = monthlydata+1
//Take log
gen logchgdec = log(chgdec)
//Sum them up
gen sumchg = sum(logchgdec)
//Exp
gen expsumchg = exp(sumchg)
//Annualize
gen annual = expsumchg^(12/_n)-1

In my dataset, I have about 600 ID-codes. For many of these codes I have monthly data. Some ID-codes have monthly data for several years.
Could someone adjust the code so that it works for my whole dataset instead of only for the first year of the first ID-code.

I only need to keep the final annualized result for each year. For example, if an ID-code has monthly data from jan2013 until may2014. Then I only want to keep the annualized result of dec2013 and the annualized result of may2014.

[LydiaSmit]

A better way to annualize my dataset is of course also very welcome, appreciated

[Richard Williams]

I'm not clear on how this works for one case for the first year. Why are you adding 1 to monthly data? Are you sure you don't want to be using lagged variables or something? To me chg implies change and I don't see how change is being measured.

Showing the data for the first few ids could help a lot. Calculate by hand the results you think you should get for those cases, and then maybe we can figure out how to generalize. Based on your formulas, it seems like all you need to show is panelid, timevar, and monthlydata.

I am guessing that somewhere along the way will be a command like egen x = total(logchgdec), by idcode. Probably combined with some sort of if or in qualifier.

[LydiaSmit]

The following code works if I only use the monthly returns of 1 year of 1 fund.

There might be a better way but here I tried to compute the cumulative annualized percentage change in a step by step manner:

clear all
input chg
0.13
0.08
0.27
0.19
0.24
0.15
0.22
end

//Divide through 100 to get decimal numbers
gen chgdec = chg/100+1
//Take log
gen logchgdec = log(chgdec)
//Sum them up
gen sumchg = sum(logchgdec)
//Exp
gen expsumchg = exp(sumchg)
//Annualize
gen annual = expsumchg^(12/_n)-1

I've already got the log returns.
Example dataset:

Date
28nov1997
31dec1997
31oct1997
30nov1998
30oct1998
31mar1998
30jan1998
29may1998
31jul1998
30apr1998
31dec1998
30sep1998
27feb1998
31aug1998
30jun1998
30jun1999
31aug1999
30sep1999
26feb1999

Idcode=1 for every of the above dates. Also a column/variable with log(monthly returns) for the above dates for that specific idcode. There are more idcodes. In my dataset, I have about 600 ID-codes. For many of these codes I have monthly data. Some ID-codes have monthly data for several years.
Could someone please adjust the code so that it works for my whole dataset?

[Richard Williams]

It seems like the critical line is

gen sumchg = sum(logchgdec)

So how about ( I am assuming the Date variable really is called Date):

gen year = year(Date)
bysort idcode year (Date): gen sumchg = sum(logchgdec)

I don't have anything to test this with so see if it seems to work. If it does, the next trick will be to only save the final value for the year.

[LydiaSmit]

Unfortunately that doesn't work Richard.

Earlier today, I used that code for my selfmade method of annualizing the monthly returns and then it worked....the annualized returns were given to the right idcode for the correct year. However, my selfmade method is a lot less accurate. So maybe it helps for you or someone else if I show my method.

 bysort idcode year: egen double averagemonthlyreturn = mean(logchgdec)
gen double annualized return=(1+averagemonthlyreturn)^12-1

[Richard Williams]

I think you will also need

bysort idcode year (Date): gen annual = expsumchg^(12/_n)-1

If there is any missing data the code may need tweaking.

[Richard Williams]

I don't really understand the formulas, but are you you sure you want to take the mean?

. di 1.03 * 1.04 * 1.05 * 1.06 * 1.07
1.2757028

. di 1.05 ^5
1.2762816

Taking the average monthly does not produce the same results as multiplying the numbers out.

In any event, you need to make sure you have the right formulas. I don't think I can help you much on that.

[LydiaSmit]

It almost works perfectly now. The correct annualized returns are randomly given to a month within the correct year. For example, March2002 receives the annualized return instead of December2002

Because of missing data the code indeed needs tweaking. Now it only works for the years with 12 months of data.

Yesterday, I also used your code for the very accurate code of the standard method, not only for my selfmade method. Never guessed that I should have used -bysort- twice.

By the way, I've came up with a method to save the final values if the annualized returns are assigned to each December. So only 2 steps left, assigning the annualized return to the correct month and tweaking the code for when there are less than 12 months of data for 1 specific year.

[Richard Williams]

You need to make sure it is also sorted correctly by month:

 
 bysort idcode year (Date):

Otherwise the months can get randomly sorted within a year, possibly leading to the results you are seeing.

[Richard Williams]

I may be getting too cutesy here, but this might be able to take the place of your current code, since you just want the year-end numbers:

gen year = year(Date)
bysort idcode year: egen sumchg = total(log(chg/100 + 1))
bysort idcode year: gen annual = exp(sumchg) ^ (12/_N) - 1

This assumes there is no missing data. If there is, _N needs to be replaced by the number of months with non-missing data. Which can be done, but I won't bother if MD is not an issue.

[LydiaSmit]

Thank you for the confirmation. I'm trying to sort by month, however, so far it doesn't work

I've tried several combinations like bysort idcode year month

[Richard Williams]

I just saw your edits. I am signing off, so please indicate whether you have got it working right or problems remain. I suspect my last code works but I can't test it.

[LydiaSmit]

Your second last post just showed up, even though I've been refreshing numerous times. I'm trying your code immediatly, however, I have missing data

[Richard Williams]

Make sure you used egen and not gen. If problems persist, show your exact code. My last code doesn't require you to sort by month.