[Richard Williams]

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

If sorting by month, month needs to be in parentheses.

[Richard Williams]

Are you sure it is not working when there are less than 12 months? The 12/ _N would seem to take care of that. E.g. If there are only 6 months, you should wind up squaring, which is what I think you want.

[Richard Williams]

I don't think it matters if, say, there is no record for March 1997. It does matter if there is a record for March 1997 that has missing data for chg. does the latter happen? _N will tell you how many months that you had records for.

[Richard Williams]

Anyway, it is something like bysort idcode year: egen numvalid = total(!missing(chg))

[LydiaSmit]

I don't think it matters if, say, there is no record for March 1997. It does matter if there is a record for March 1997 that has missing data for chg. does the latter happen? _N will tell you how many months that you had records for.

The latter happens. I'm trying to solve it. By the way, there's something weird going on with receiving your posts on this forum and sending my posts. It takes at least several minutes each time.

[LydiaSmit]

By the way, for years with less than 12 months of data I get completely different annualized returns.
Based on your code (using numvalid instead of _N) I get e.g. 1.0180 and with the original code below I get 0.9413

Which method is the correct one?

Original

gen chgdec = chg+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

Below is your code, which I use at the moment

bysort idcode year: egen numvalid = total(!missing(chg))
bysort idcode year: egen  sumchg = total(chg)
gen expsumchg = exp(sumchg)
bysort idcode year: gen annual = exp(sumchg)^(12/numvalid)-1

[DavidHarmsen]

Nevermind

[LydiaSmit]

Date      riskfree rate
31jan2006    .    .0533
28feb2006        -.0031
31mar2006       .0356
28apr2006       -.0124
31may2006     -.0304
30jun2006       -.0054
31jul2006        -.0399
31aug2006       .0083
29sep2006      -.0123
31oct2006        .0164
30nov2006      .0077
29dec2006    -.0089

Annualizing the above 12 months of data should lead to 0.00690. However, that doesn't work with the annualizing codes of this topic
Therefore, I also need a code to annualize without compounding the monthly data.
I would really appreciate it if someone could give me that code.

[Richard Williams]

Please show the exact code you are using. It seems to keep changing a bit, e.g. at first you were dividing chg by 100 and in the last code you presented you did not divide by 100. Also indicate what results you are getting instead. Also try to explain how you know the correct result is .00690.

Your data listing is hard to read. Does this correctly reproduce your data? You have this stray dot in the first line of data.

clear all
input str20 date chg
31jan2006     .0533
28feb2006    -.0031
31mar2006     .0356
28apr2006    -.0124
31may2006    -.0304
30jun2006    -.0054
31jul2006    -.0399
31aug2006     .0083
29sep2006    -.0123
31oct2006     .0164
30nov2006     .0077
29dec2006    -.0089
end

gen date2 = date(date, "DMY")
format date2 %td
gen year = year(date2)
gen idcode = 1
sort idcode date2
list

. list

     +------------------------------------------------+
     |      date      chg       date2   year   idcode |
     |------------------------------------------------|
  1. | 31jan2006    .0533   31jan2006   2006        1 |
  2. | 28feb2006   -.0031   28feb2006   2006        1 |
  3. | 31mar2006    .0356   31mar2006   2006        1 |
  4. | 28apr2006   -.0124   28apr2006   2006        1 |
  5. | 31may2006   -.0304   31may2006   2006        1 |
     |------------------------------------------------|
  6. | 30jun2006   -.0054   30jun2006   2006        1 |
  7. | 31jul2006   -.0399   31jul2006   2006        1 |
  8. | 31aug2006    .0083   31aug2006   2006        1 |
  9. | 29sep2006   -.0123   29sep2006   2006        1 |
 10. | 31oct2006    .0164   31oct2006   2006        1 |
     |------------------------------------------------|
 11. | 30nov2006    .0077   30nov2006   2006        1 |
 12. | 29dec2006   -.0089   29dec2006   2006        1 |
     +------------------------------------------------+

[Richard Williams]

Therefore, I also need a code to annualize without compounding the monthly data.
I would really appreciate it if someone could give me that code.

So, are you saying that the formulas you have been using are wrong? If so, what is the correct formula? Without knowing the formula it is hard to know how to code it. If you showed how you came up with .00690 that could help a lot.

[LydiaSmit]

By the way, for years with less than 12 months of data I get completely different annualized returns.
Based on your code (using numvalid instead of _N) I get e.g. 1.0180 and with the original code below I get 0.9413

Which method is the correct one?

Original

gen chgdec = chg+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

Below is your code, which I use at the moment

bysort idcode year: egen numvalid = total(!missing(chg))
bysort idcode year: egen sumchg = total(chg)
gen expsumchg = exp(sumchg)
bysort idcode year: gen annual = exp(sumchg)^(12/numvalid)-1

chg=logged(monthly returns) in my case. I used the correct formulas for the standard monthly returns with your help.

I also have other monthly data. However, most of this data is the result of certain calculations, like the return of the best 30% performers - the return of the worst 30% performers. Using the same annualizing codes with compounding doesn't seem to give accurate results. So I'm actually asking for 2 different solutions based on 2 different problems. However, both problems have a lot in common so therefore I didn't make another topic.


You used the correct code in your second last post for the standard monthly returns, which worked great, however, I'm not sure about the following. For years with less than 12 months of data I get completely different annualized returns.
Based on your code (using numvalid instead of _N) I get e.g. 1.0180 and with the original code below I get 0.9413

A colleague had a small sample dataset with the .00690 as the annualized return from his college time. He didn't have the calculations for the outcomes anymore but the returns for the annualized compounded returns were correct. The dataset only contains annualized compounded returns if there were 12 months of data. The small dataset only has a few years of data, while I have 50 years of data

[Richard Williams]

I tweaked your data dropping the first two months. Both my code and your code produce identical results. Whether either is correct, I don't know.

clear all
input str20 date chg
31jan2006     .0533
28feb2006    -.0031
31mar2006     .0356
28apr2006    -.0124
31may2006    -.0304
30jun2006    -.0054
31jul2006    -.0399
31aug2006     .0083
29sep2006    -.0123
31oct2006     .0164
30nov2006     .0077
29dec2006    -.0089
end

gen date2 = date(date, "DMY")
format date2 %td
gen year = year(date2)
gen idcode = 1
sort idcode date2
list
drop if _n <= 2

set type double

*Lydia code
gen chgdec1 = chg+1
//Take log
gen logchgdec1 = log(chgdec1)
//Sum them up
gen sumchg1 = sum(logchgdec1)
//Exp
gen expsumchg1 = exp(sumchg1)
//Annualize
gen annual1 = expsumchg1^(12/_n)-1

* RW Code
bysort idcode year: egen numvalid = total(!missing(chg))
bysort idcode year: egen sumchg = total(log(chg + 1))
bysort idcode year: gen annual = exp(sumchg) ^ (12/numvalid) - 1
list date2 chg numvalid annual1 annual

. list date2 chg numvalid annual1 annual

     +---------------------------------------------------------+
     |     date2      chg   numvalid      annual1       annual |
     |---------------------------------------------------------|
  1. | 31mar2006    .0356         10    .52161402   -.05099033 |
  2. | 28apr2006   -.0124         10    .14456046   -.05099033 |
  3. | 31may2006   -.0304         10   -.03291885   -.05099033 |
  4. | 30jun2006   -.0054         10   -.04050536   -.05099033 |
  5. | 31jul2006   -.0399         10    -.1226082   -.05099033 |
     |---------------------------------------------------------|
  6. | 31aug2006    .0083         10   -.08832333   -.05099033 |
  7. | 29sep2006   -.0123         10   -.09559351   -.05099033 |
  8. | 31oct2006    .0164         10   -.06154123   -.05099033 |
  9. | 30nov2006    .0077         10   -.04517924   -.05099033 |
 10. | 29dec2006   -.0089         10   -.05099033   -.05099033 |
     +---------------------------------------------------------+

[Richard Williams]

You used the correct code in your second last post for the standard monthly returns, which worked great, however, I'm not sure about the following. For years with less than 12 months of data I get completely different annualized returns.
Based on your code (using numvalid instead of _N) I get e.g. 1.0180 and with the original code below I get 0.9413

You really need to show exact code and output. Otherwise it is too hard to figure out where any problems may be.

[LydiaSmit]

Not a clear picture, however, I just wanted to show my output when I don't drop the first 2 months. The rows with the missing data also contains other info which I'd like to keep.
First method has -.04267618 as annualized return and the second method has -.05099033 as a return.

So 1 of the 2 methods is clearly wrong if I want to keep the rows with the missing values.

[Richard Williams]

You are not showing your code. With my code from a few posts ago both your method and mine - or at least what I thought was your method - both results are the same. So check your coding against what I did a few posts ago.