[R-SIG-Finance] Generating Monthly Returns from a ton of daily data
Brian G. Peterson
brian at braverock.com
Fri Jun 5 03:46:55 CEST 2009
Cedrick Johnson wrote:
> I have a large blob timeSeries object within R full of theoretical pl
> values. The Data is in daily format and i need to somehow get daily to
> monthly and calculate the return (First and Last Day)..
> Here's a sample of my dataset (is.timeSeries = TRUE):
> PL1 PL2 PL3
> 2008-05-01 12:00:00 -533 15467 -623
> 2008-05-02 12:00:00 -346 -5577 2363
> 2008-05-30 12:00:00 57 27168 -7850
> 2008-06-02 12:00:00 1308 -7750 548
> 2008-06-03 12:00:00 291 20498 -435
> 2008-06-30 12:00:00 1132 24990 -1405.5
> ...... this goes on until 5/27/09
> So basically what I'm looking to do is calculate each month's returns
> using CalculateReturns() or returns(). In order to do that, I realized
> that i needed to take the time series and convert the daily PL returns
> to monthly, which i did by issuing the following:
> Manager3.mnth = to.monthly(Managers[,3], OHLC=FALSE)
> I wanted to get PL3's daily returns and then aggregate it into a
> monthly return by running it through returns()and then continue on
> further by doing table.CalendarReturns, etc..
> Here's where I am stumped: When I do the to.monthly(), and i set
> OHLC=false, I get the following:
> > Manager3.mnth
> Managers[, 3].Open
> Managers[, 3].High Managers[, 3].Low Managers[, 3].Close
> 2008-05-30 13:00:00 17961.0
> 27879.0 16564.5 27879.0
> 2008-06-30 13:00:00 22683.5
> 50482.5 22683.5 49906.5
> I get a OHLC data set back.
> Am I approaching this problem the wrong way? For now, I can manually
> get around this by manipulating the data in Excel to achieve monthly
> info, but I envision these datasets becoming large enough that will
> become a huge PITA...
So, I think the issue here is that you don't have a price series, which
to.monthly would help you with, but a p&l series, where each day is
gains or losses for that day.
What you'll need to do instead is to sum() all the days in each month
for each column, which will give you
the monthly P&L for each strategy. Once you have monthly P&L, this can
be converted to returns by setting a starting wealth value (probably
your capital amount or notional value of the portfolio or strategy or
something similar, as I described in another thread a couple weeks
ago). You could, of course, also get daily simple returns in a similar
fashion, then compound them to get a monthly return.
I don't know immediately why setting OHLC=false isn't working correctly,
but that's a different issue. I think you need to correct the logic
issue first, as described above.
I can see general utility from what you're trying to do, so please share
the solution you come up with. I'll add it to my (long) list of things
to do as well, but I don't know when I'd get to it. Maybe Jeff or Josh
or Gabor (xts/quantmod/zoo) will have a quick way of doing the date
subsetting in xts to get the sum() of all the days in each month.
Brian G. Peterson
More information about the R-SIG-Finance