[R-SIG-Finance] Generating Monthly Returns from a ton of daily data
cedrick at cedrickjohnson.com
Fri Jun 5 04:19:47 CEST 2009
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
I'll also try the other approaches...
many thanks again,
Joshua Ulrich wrote:
> On Thu, Jun 4, 2009 at 8:46 PM, Brian G. Peterson <brian at braverock.com> wrote:
>> 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:
>>> 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
>> 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
>> Brian G. Peterson
>> Ph: 773-459-4973
>> IM: bgpbraverock
>> R-SIG-Finance at stat.math.ethz.ch mailing list
>> -- Subscriber-posting only.
>> -- If you want to post, subscribe first.
> Here's some code that does what Brian suggested (using Gabor's style):
> 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)))
More information about the R-SIG-Finance