-4

I am using Stata and I have 6 years of daily returns for stocks that individuals hold in their portfolios. I would like to aggregate the daily returns to monthly portfolio returns. In some instances, the individual may hold more than one stock in the portfolio. I am struggling with writing the code to do this.

For a visual, my data looks like this:

Data

I would like the results to look like this:

Results

Where individual 2's portfolio return for the month of December 1996 is calculated as: 0.3 * 0.0031 + 0.7 * 0.0076 = 0.00625.

I have tried the collapse command such as

collapse Return, by (ID Year Month)

but this does not provide the same return that I calculated out in Excel.

I am able to make a weighted portfolio return for all the days using

bysort ID year month: egen wt_return = stock_weight * monthly_return

But this gives me daily returns. My trouble is then aggregating them into one return for the corresponding month.

As for the specifics, I would like to calculate the monthly portfolio return as the product of 1 + the weighted daily returns. As a last resort, the mean return for the month could work.

Roberto Ferrer
  • 11,024
  • 1
  • 21
  • 23
user3196098
  • 21
  • 1
  • 5
  • 1
    Please show us what you've tried. See http://stackoverflow.com/help/asking – Roberto Ferrer Mar 20 '14 at 21:56
  • 1
    Poor question. No code; no improvement when prompted; assumes that Stata users here all know the rules about financial data used in this problem. For example, is the monthly return just the mean of the daily returns, or something else? Look at `egen`; it is a good workhorse here. – Nick Cox Mar 21 '14 at 10:10
  • You still do not say how you compute `Monthly Return`. – Roberto Ferrer Mar 21 '14 at 15:30
  • Your `egen` code segment couldn't possibly run, as there is no `egen` function call. Perhaps `egen` is a typo for `generate`, but note that in such a case, the `by` framework makes no difference to the outcome. – Nick Cox Mar 21 '14 at 17:56

1 Answers1

1

You don't show monthly portfolio return for person 2 in 1991. Your initial example data doesn't show stock weights but the desired example data does. Your variable Monthly Return is not reproducible. You should take time to verify your question is clear when posting. It's supposed be clear to the public who will read it, not only to you.

I didn't bother checking if your computations are correct but below is what I understand you want. The procedure is simply to compute a weighted return and then add them up by person year month groups. (I assume the stock weights apply to stocks on a daily basis, which is what your example data implies.)

clear all
set more off

input ///
perid year month day str3 stockid return stockw
1 1991 1 1 "ABC" .01 1
1 1991 1 2 "ABC" .02 1
1 1991 1 3 "ABC" -.01 1
1 1991 1 31 "ABC" .004 1
1 1996 12 31 "ABC" .002 1
2 1991 1 1 "ABC" .01 .3
2 1991 1 2 "ABC" .02 .3
2 1996 12 31 "ABC" .004 .3
2 1991 1 1 "XYZ" .001 .7
2 1991 1 2 "XYZ" .004 .7
2 1996 12 31 "XYZ" .021 .7
end

* create weighted return
gen returnw = return * stockw

sort perid year month day
list, sepby(perid year month day)

* sum weighted returns by person, year, month
collapse (sum) returnw, by (perid year month)

list, sepby(perid)

If you want collapse to sum, then you must indicate it with the (sum) (although I'm not clear if this is what you want). By default, it computes the mean. Read help collapse thouroughly.

Roberto Ferrer
  • 11,024
  • 1
  • 21
  • 23