Databases
StackOverflow2013
Postsdbo
POUsing plyr (or *apply) to calc cumulative rtns
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POUsing plyr (or *apply) to calc cumulative rtns
    primarykey
    data
    text
    <p>I've struggled for hours on this problem that seems right for plyr or *apply. Can someone point me to a less clunky R solution than the one I've listed below?</p> <p>Bkdg: I looked at many R finance related packages, and I can't find a popular package that deals well with securities that disappear in the middle of a time series along with weights that vary programatically. I'm building my own solution for this particular issue, but I'd rather use an existing one.</p> <p>The crux of the problem is that I want to use plyr to "loop" over a list of securities within a date subset. Some of the securities disappear over the date range. (I'm using forward rtns from data tha is free of survivorship bias.) I want the output from each date range to be a data frame of cumulative returns of the selected securities. I can use this (along with initial weights) to combine with other date ranges to calculate various portfolio metrics.</p> <p>I start with a (toy) data frame of selected securities like this (date, ticker, net return):</p> <pre><code>d t r 1 2013-03-31 ibm 0.01 2 2013-03-31 appl 0.02 3 2013-03-31 loser 0.03 4 2013-04-30 ibm 0.04 5 2013-04-30 appl 0.05 6 2013-04-30 loser 0.06 7 2013-05-31 ibm 0.07 8 2013-05-31 appl 0.08 </code></pre> <p>Note that security "loser" is not present in the last month of the date range. (Securities will not reappear.) Here is some code that creates the toy data frame and the clunkly solution that seems to work. </p> <pre><code>library(plyr) #Create data frame for the example code dt &lt;- as.Date("20130331","%Y%m%d") mydf &lt;- data.frame(d=dt,t="ibm",r=0.01) mydf &lt;- rbind(mydf,data.frame(d=dt,t="appl",r=0.02)) mydf &lt;- rbind(mydf,data.frame(d=dt,t="loser",r=0.03)) dt &lt;- as.Date("20130430","%Y%m%d") mydf &lt;- rbind(mydf,data.frame(d=dt,t="ibm",r=0.04)) mydf &lt;- rbind(mydf,data.frame(d=dt,t="appl",r=0.05)) mydf &lt;- rbind(mydf,data.frame(d=dt,t="loser",r=0.06)) dt &lt;- as.Date("20130531","%Y%m%d") mydf &lt;- rbind(mydf,data.frame(d=dt,t="ibm",r=0.07)) mydf &lt;- rbind(mydf,data.frame(d=dt,t="appl",r=0.08)) #Note that there is no row for "loser" for 2013-05-31 #This plyr call crashes because "loser" doesn't have the same # num of rtns as the others #newdf &lt;- ddply(mydf,.(t),function(x) cumprod(x[,"r"]+1)-1) </code></pre> <p><strong>Error in list_to_dataframe(res, attr(.data, "split_labels")):</strong> <strong>Results do not have equal lengths</strong></p> <pre><code>#I work with intermediate lists as a workaround tmp.list &lt;- dlply(mydf,.(t),function(x) cumprod(x[,"r"]+1)-1) #Get the longest of any of the resulting lists (tmp = 3 in this example) tmp &lt;- max(as.numeric(lapply(tmp.list,length))) #Define function to extend cumulative rtn for missing values # By holding cumulative rtn constant, its as if # I hold cash when a security disappears extendit &lt;- function(x) if(length(x)&lt;tmp){ c(x,rep(x[length(x)],tmp-length(x))) } else {x} #Use plyr to make all lists the same length tmp2.list&lt;-llply(tmp.list,extendit) #Use plyr to create the data table I wanted cusipcumrtns.df &lt;- ldply(tmp2.list) #Must name key column since it got lost in the process colnames(cusipcumrtns.df)[1] &lt;- "t" </code></pre> <p>The code above yields the following data frame containing the cumulative returns for each security.</p> <pre><code>t V1 V2 V3 1 ibm 0.01 0.0504 0.123928 2 appl 0.02 0.0710 0.156680 3 loser 0.03 0.0918 0.091800 </code></pre> <p>Any pointers for a more refined solution are greatly appreciated. This seems to work, but I'm trying to learn to be a better with R.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USjoeyt
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POUsing plyr (or *apply) to calc cumulative rtns
      1. This table or related slice is empty.
      1. VTUpMod
    1. COYour end result is equivalent to saying that `loser` had a zero return (no win, no loss) on its missing period. Is this really what you want?
      singulars
      1. POUsing plyr (or *apply) to calc cumulative rtns
      1. USflodel
    2. CO@flodel: Yes, I think so. Since I'm using what I am told is a data set free of survivorship bias, I am assuming that the forward rtn from the last period contains any returns due to bankruptcy or acquisition or whatever. Rather than reinvest the proceeds in the other securities, I hold cash. I'm not sure if this is the best thing to do, but it is what I intended. I welcome your input.
      singulars
      1. POUsing plyr (or *apply) to calc cumulative rtns
      1. USjoeyt
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload