Generating Monthly Returns from a ton of daily data

On Thu, Jun 4, 2009 at 8:46 PM, Brian G. Peterson <brian at braverock.com> wrote:
  

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

Here's some code that does what Brian suggested (using Gabor's style):

library(xts)

Lines <- "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"

z <- read.zoo(textConnection(Lines),sep=",")
(m <- apply(z,2,function(x) apply.monthly(x,sum)))

HTH,
Josh
--
http://www.fosstrading.com