1

I have a tibble (see below) that includes the twelve month returns for numerous stocks (SPY, GLD, etc), and also the return in the row next to the twelve month. I want to take the maximum value between the different twelve months from row (t-1), and then for the stock with that value, post it's return in a separate column named "Strat return" in row (t), and then post the name of the stock in another column as well named "Strat ticker" in row (t) as well.

I've tried running if_else and case_when arguments. When I tried doing those, I had trouble finding the maximum value the tibble ended up empty. Also, I do not know any way to make it scalable to include numerous different stocks if I want to add some. I use the lag(SPY_twelve_mon_ret) as well to find the rows (t-1) and that works well.

# Input

# A tibble: 6 x 8
# Groups:   mom_asset [1]
mom_asset date       spy_return       spy_twelve_mon      tbill_twelve_mon GLD_return    GLD_twelve_mon bond_return
<chr>     <date>          <dbl>              <dbl>                <dbl>      <dbl>              <dbl>       <dbl>
  1 SPY       2005-11-30    0.0440              0.0819               0.0826     0.0582             0.0882    0.00374 
2 SPY       2005-12-31   -0.00190             0.0483               0.0861     0.0505             0.178     0.0114  
3 SPY       2006-01-31    0.0240              0.0981               0.0308     0.0993             0.343    -0.000596
4 SPY       2006-02-28    0.00573             0.0817               0.0580    -0.0111             0.288     0.00161 
5 SPY       2006-03-31    0.0165              0.120                0.0140     0.0362             0.357    -0.00868 
6 SPY       2006-04-30    0.0126              0.156               -0.0507     0.120              0.501    -0.00149 

#  Output

# A tibble: 6 x 8
# Groups:   mom_asset [1]
mom_asset date       spy_return spy_twelve_mon_ret tbill_twelve_mon_ret GLD_return GLD_twelve_mon_ret bond_return     strat_returns   strat_label
<chr>     <date>          <dbl>              <dbl>                <dbl>      <dbl>              <dbl>       <dbl>          <dbl>         <chr>
2 SPY       2005-12-31   -0.00190             0.0483               0.0861     0.0505             0.178     0.0114          0.0114        BOND
3 SPY       2006-01-31    0.0240              0.0981               0.0308     0.0993             0.343    -0.000596       -0.000596      BOND
4 SPY       2006-02-28    0.00573             0.0817               0.0580    -0.0111             0.288     0.00161         0.00161       BOND 
5 SPY       2006-03-31    0.0165              0.120                0.0140     0.0362             0.357    -0.00868        -0.00868       BOND
6 SPY       2006-04-30    0.0126              0.156               -0.0507     0.120              0.501    -0.00149        -0.00149       BOND
moh
  • 11
  • 2
  • 3
    Please provide a [reproducible minimal example](https://stackoverflow.com/q/5963269/8107362). Especially, provide some sample data adn your desired output, e.g. with `dput()`. – mnist Nov 06 '19 at 15:23
  • I'm a bit confused. Are you trying to find the highest value among GLD_return, AEM_return,ABX_return, NEM_return and bond_return for each line in a new column? – Sæmundur Rögnvaldsson Nov 06 '19 at 15:31
  • First, the row 1 would get eliminated because it's the "lag". Then, in the row 2, a in a new column named "strat_return", the algorithm would go in row 1, look at the highest value between the different columns where the header includes "twelve", and then post the return from row 2 of the associated stock, and in the other new column named "strat_label", the first 3 characters of the stock label. – moh Nov 06 '19 at 15:53
  • For example, say in row 1 the largest twelve_mon_ret is for GLD, in row 2 in the strat_return column, GLD_return of row 2 would get posted, and in strat_label, "GLD". – moh Nov 06 '19 at 15:56

0 Answers0