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

[Cedrick Johnson]

Thanks Josh/Brian, this gave me a couple ideas - I will pick up the 
troubleshooting in the morning (the pub is calling me)... I'll share 
with the group any solution I come up with...

i have a couple ideas for getting the sum of the monthly PL, perhaps 
involving some fancy xts footwork i.e. (x['2008-05'])and adding to a 
matrix where I just have pure returns so I can use some features in 
performanceanalytics...

I'll also try the other approaches...

many thanks again,
c

Joshua Ulrich wrote:
> 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
>