1

I am trying to transform a table of stocks ranked by their return to a matrix of values that would be inputted to PerformanceAnalytics::ReturnPortfolio as the weights of the stocks in the portfolio.

What I am specially looking to do is to transform the ranking value of the 33% best stocks to 1, the 33% worst-performing stocks to -1 and the rest to 0.

This is the original code where I am just taking the best stocks and changing their ranked value to 1:

asset_ranking <- db_Momentum %>%
  dplyr::select(-date) %>%
  as.matrix() %>%
  rowRanks(ties.method = "min") %>%
  as.data.frame() %>%
  dplyr::mutate(date = db_Momentum$date) %>%
  dplyr::select(date, everything()) %>%
  purrr::set_names(c("date", tickets)) %>%
  as_tibble()

# Analyzing Sector Selection
asset_selection <- asset_ranking %>%
  mutate_if(is.numeric, ~ + ( . > (length(tickets) - n_assets_buy))) %>%
  dplyr::select(-date)

This is an example of what it is like now:

AAPL IBM KO T TLT SPY
1 2 3 4 6 5
2 1 3 5 4 6
1 4 2 5 3 6
6 4 5 2 1 3

And this is what I would like it to be:

AAPL IBM KO T TLT SPY
1 1 0 0 -1 -1
1 1 0 -1 0 -1
1 0 1 -1 0 -1
-1 0 -1 1 1 0
ChorMachine
  • 50
  • 5

1 Answers1

1

We could loop over the rows with apply and MARGIN = 1, get the quantile with probs as .33 and .67, pass that as breaks in the cut, convert to integer and use that index for replacing the values to 1, 0, -1

asset_selection[] <- t(apply(asset_selection, 1, function(x)
   c(1, 0, -1)[as.integer(cut(x, c(-Inf, quantile(x, c(.33, .67)), Inf)))]))

-output

asset_selection
#  AAPL IBM KO  T TLT SPY
#1    1   1  0  0  -1  -1
#2    1   1  0 -1   0  -1
#3    1   0  1 -1   0  -1
#4   -1   0 -1  1   1   0

data

asset_selection <- structure(list(AAPL = c(1, 2, 1, 6), 
      IBM = c(2, 1, 4, 4), KO = c(3, 
 3, 2, 5), T = c(4, 5, 5, 2), TLT = c(6, 4, 3, 1), SPY = c(5, 
6, 6, 3)), class = "data.frame", row.names = c(NA, -4L))
akrun
  • 874,273
  • 37
  • 540
  • 662
  • That would not work on a higher number of stocks. Or how would you do it for 9 stocks, for example? – ChorMachine Jan 19 '21 at 20:01
  • @ChorMachine If you have 9 stocks, what would be the value to be replaced. I guess based on the logic may be `8 - asset_ranking` ? – akrun Jan 19 '21 at 20:02
  • As I said in the question, the objective is to transform the 33% best performing stocks to 1, 33% worst to -1 and the rest 33% to 0. Which, in the case of a list of 12 stocks would be 4 per category. – ChorMachine Jan 19 '21 at 20:05
  • @ChorMachine Please update your input example with a more general example and its expected output. When there is a simple case, it is tempting to give a simple fix – akrun Jan 19 '21 at 20:06
  • @ChorMachine I didn't notice that you changed the data. I modified the code, please check – akrun Jan 19 '21 at 22:04