dataframe - Iteratively find the returns of a portfolio in R

Question

The problem is the following: I have a dataframe, let's call it DF, which looks like this:

    |Symbol |   Date    | volume |price |
    |------------------------------------
    |A      |2014-01-01 | 0      |   5  |
    |A      |2014-01-02 | 3      |   8  |
    |A      |2014-01-03 | 0      |   4  |
    |A      |2014-01-04 | 1      |  12  |
    |B      |2014-01-01 |45      |   9  |
    |B      |2014-01-02 |3       |   6  |
    |B      |2014-01-03 |34      |   7  |
    |B      |2014-01-04 |45      |  34  |
    |C      |2014-01-01 |4       |   5  | 
    |C      |2014-01-02 |9       |   7  |
    |C      |2014-01-03 |3       |   8  |
    |C      |2014-01-04 |0       |   3  | 

And I need to calculate the value-weighted portfolio return, for each one of the dates in the data frame, forming a daily series of returns.

With the following line of code, I extract from the dataframe the data corresponding to a single date:

DF_1 <- DF[date=="2014-01-01"]

Getting the next dataframe:

    |Symbol |   Date    | volume |price |
    |------------------------------------
    |A      |2014-01-01 | 0      |   5  |
    |B      |2014-01-01 | 45     |   9  |
    |C      |2014-01-01 | 4      |   5  |

Then, I add up all the values of the volume column:

(TOT_vol <- colSums(DF_1[ , 3, drop = FALSE]))

And I create two additional columns: weight and R_i:

DF_1$weight <- as.numeric(DF_1$volume)/TOT_vol
DF_1$R_i <- apply(DF_1[,c(4,5),drop=FALSE],1,prod)

Getting the next data frame:

    |Symbol |   Date    | volume |price | weight |  R_i |
    |----------------------------------------------------
    |A      |2014-01-01 | 0      |   5  |     0  |   0  |
    |B      |2014-01-01 | 45     |   9  |   0.91 |  8.19|
    |C      |2014-01-01 | 4      |   5  |   0.08 |   0.4|

Finally I obtain the value weighted return for that specific date, madding the values in column R_i:

(RM <- colSums(DF_1[,6,drop=FALSE]))

My problem is that I need to find that RM for all the dates of the data frame and create a series with all those daily returns; I am very new in R and I know that I must set up a for loop but, honestly, I don't know how.

Additionally, I would like to know if you can find a simpler way to do what I did to find the daily returns. That is, without the need to create two new columns in my original dataframe.

I look forward to any help. Thank you very much!

question from:https://stackoverflow.com/questions/65845666/iteratively-find-the-returns-of-a-portfolio-in-r

Answer 1

Are you looking for this?

library(dplyr)

df %>% group_by(Date) %>%
  mutate(weight = volume/sum(volume),
         R_i = weight*price)

# A tibble: 12 x 6
# Groups:   Date [4]
   Symbol Date       volume price weight    R_i
   <chr>  <chr>       <int> <int>  <dbl>  <dbl>
 1 A      01-01-2014      0     5 0       0    
 2 A      02-01-2014      3     8 0.2     1.6  
 3 A      03-01-2014      0     4 0       0    
 4 A      04-01-2014      1    12 0.0217  0.261
 5 B      01-01-2014     45     9 0.918   8.27 
 6 B      02-01-2014      3     6 0.2     1.2  
 7 B      03-01-2014     34     7 0.919   6.43 
 8 B      04-01-2014     45    34 0.978  33.3  
 9 C      01-01-2014      4     5 0.0816  0.408
10 C      02-01-2014      9     7 0.6     4.2  
11 C      03-01-2014      3     8 0.0811  0.649
12 C      04-01-2014      0     3 0       0 

OR perhaps this

df %>% group_by(Date) %>%
  mutate(weight = volume/sum(volume),
         R_i = weight*price) %>%
  summarise(RM = sum(R_i))

# A tibble: 4 x 2
  Date          RM
  <chr>      <dbl>
1 01-01-2014  8.67
2 02-01-2014  7   
3 03-01-2014  7.08
4 04-01-2014 33.5 

dput

> dput(df)
structure(list(Symbol = c("A", "A", "A", "A", "B", "B", "B", 
"B", "C", "C", "C", "C"), Date = c("01-01-2014", "02-01-2014", 
"03-01-2014", "04-01-2014", "01-01-2014", "02-01-2014", "03-01-2014", 
"04-01-2014", "01-01-2014", "02-01-2014", "03-01-2014", "04-01-2014"
), volume = c(0L, 3L, 0L, 1L, 45L, 3L, 34L, 45L, 4L, 9L, 3L, 
0L), price = c(5L, 8L, 4L, 12L, 9L, 6L, 7L, 34L, 5L, 7L, 8L, 
3L)), class = "data.frame", row.names = c(NA, -12L))