[R-SIG-Finance] Generating Monthly Returns from a ton of daily data

[Brian G. Peterson]

Cedrick Johnson wrote:
> Howdy-
>
> 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
> GMT
>                                                    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...
>
> Regards,
> Cedrick
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.

Regards,

  - Brian

-- 
Brian G. Peterson
http://braverock.com/brian/
Ph: 773-459-4973
IM: bgpbraverock