Search code examples
rxtszooquantitative-finance

Want to select a column based on a return (within that same column) below a threshold in R


I have a return data frame (xts, zoo object) of size 1379 x 843. It should be read as date x security.

This is an example of the input:

            BIIB.US.Equity JNJ.US.Equity BLUE.US.Equity BMRN.US.Equity AGN.US.Equity
2018-06-15   -0.5126407   0.001633853   -0.070558376   0.0846854857  -0.004426559
2018-06-18   -0.052158804  -0.310521165   -0.035226652  -0.0206967213  -0.008430535
2018-06-19    0.010099613   0.010303330    0.006510048   0.0004184976   0.007745167
2018-06-20    0.016504588  -0.004324060    0.029808774   0.0284459318   0.012366368
2018-06-21    0.001616924  -0.004834480    0.023211360   0.0009151922  -0.015411839
2018-06-22   -0.004136679   0.010374640   -0.065652522   0.0097023265   0.005322048

Now I would like to return a different list:

             BIIB.US.Equity JNJ.US.Equity
2018-06-15   -0.5126407     0.001633853
2018-06-18   -0.052158804  -0.30521165
2018-06-19    0.010099613   0.010303330
2018-06-20    0.016504588  -0.004324060
2018-06-21    0.001616924  -0.004834480
2018-06-22   -0.004136679   0.010374640

As you can see the second list only contains 2 columns because there is a dip in 51% on the first security at time 2018-06-15, and a dip in 30% on the second security in the second at time 2018-06-18. Both of which exceed the threshold of 30%

What I want is to get a new data frame from my current which selects securities where there is an instance of a 30% drop in security return or greater.

Currently I have tried:

df1 <- returns < -.3
returns[df1]

but this returns the error:

Error in `[.xts`(returns, df1) : 'i' or 'j' out of range

I have also tried this:

cls <- sapply(returns, function(c) any(c < -.3))
a<- returns[, cls, with = FALSE]

Yet that returns a matrix of the same size, only with a lot of NA values. Is there something I am missing?

Basically what I'd expect to get out is a data frame "df" of size 1379 x (something less than 843) where df is all columns where there is an instance of a daily drop of -.3 or less.

EDIT:
To those that have tried to help, thank you, but the output returns as such (I assigned the call to a):

    > a
     BIIB.US.Equity JNJ.US.Equity BLUE.US.Equity BMRN.US.Equity AGN.US.Equity PFE.US.Equity NBIX.US.Equity
    > summary(a)
         Index   
     Min.   :NA  
     1st Qu.:NA  
     Median :NA  
     Mean   :NA  
     3rd Qu.:NA  
     Max.   :NA  
    > str(a)
    An 'xts' object of zero-width

Solution

  • This should work:

    df[, sapply(df, function(x) min(x, na.rm = TRUE) <= -0.3)]