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

[Joshua Ulrich]

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
>
> _______________________________________________
> R-SIG-Finance at stat.math.ethz.ch mailing list
> https://stat.ethz.ch/mailman/listinfo/r-sig-finance
> -- Subscriber-posting only.
> -- If you want to post, subscribe first.
>

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