Compute Portfolio Variance in R with covariance matrix in stacked format in dataframe

Question

I have my cov matrix and weights in 2 separate dataframes and I would like to compute portfolio variance (for each portfolio at each date, my example only has 1 portfolio and 2 dates for simplicity):

weights <- data.frame(Portfolio = c("P1", "P1", "P1", "P1", "P1", "P1"),
    Date = c("2008-03-31", "2008-03-31", "2008-03-31", "2008-06-30", "2008-06-30", "2008-06-30"),
    ID = c("Asset1", "Asset2", "Asset3", "Asset1", "Asset2", "Asset3"),
    Wgt = c(0.1, 0.2, 0.3, 0.3, 0.2, 0.1))


covar <- data.frame(
    Date = c("2008-03-31", "2008-03-31", "2008-03-31", "2008-03-31", "2008-03-31", "2008-03-31", "2008-03-31", "2008-03-31", "2008-03-31",
        "2008-06-30", "2008-06-30", "2008-06-30", "2008-06-30", "2008-06-30", "2008-06-30", "2008-06-30", "2008-06-30", "2008-06-30"),
    ID1 = c("Asset1", "Asset2", "Asset3", "Asset1", "Asset2", "Asset3", "Asset1", "Asset2", "Asset3",
        "Asset1", "Asset2", "Asset3", "Asset1", "Asset2", "Asset3", "Asset1", "Asset2", "Asset3"),
    ID2 = c("Asset1", "Asset1", "Asset1", "Asset2", "Asset2", "Asset2", "Asset3", "Asset3", "Asset3",
        "Asset1", "Asset1", "Asset1", "Asset2", "Asset2", "Asset2", "Asset3", "Asset3", "Asset3"),
    cov = c(0.011,      0.012,   0.013,    0.012,   0.022,     0.032,     0.013,     0.032,     0.033,
             0.0011,      0.0012,   0.0013,    0.0012,   0.0022,     0.0032,     0.0013,     0.0032,     0.0033)
)

Answer 1

If I understand your question correctly, you would need to group by Portfolio and Date and create a new column that calculates the variance of the Wgt column.

library(dplyr)

P_Variance <- weights %>% 
  group_by(Portfolio, Date) %>% 
  mutate(Var = var(Wgt))

> P_Variance
# A tibble: 3 × 5
# Groups:   Portfolio, Date [1]
  Portfolio Date       ID       Wgt   Var
  <chr>     <chr>      <chr>  <dbl> <dbl>
1 P1        2008-03-31 Asset1   0.1  0.01
2 P1        2008-03-31 Asset2   0.2  0.01
3 P1        2008-03-31 Asset3   0.3  0.01

I hope this helps!

Answer 2

I still have not figured this out but I was able to get it work for 1 single date and 1 single portfolio. Maybe if someone knows how to do this for all the dates and all portfolios? That is, I somehow need to find a way to do combine the summarize command with the groupby so I can run it for one date and one portfolio at a time.

# Merge data frames
merged_data <- merge(weights, covar, by.x = c("Date", "ID"), by.y = c("Date", "ID1"))

cov_matrix <- merged_data %>% filter(Portfolio=='P1',Date=="2008-03-31") %>% select( "Date", "ID",  "ID2" , "cov") %>%  tidyr::spread(key = ID2, value = cov) %>%
    select(-ID, -Date) %>%
    as.matrix()

weights_vector <- merged_data %>% filter(Portfolio=='P1',Date=="2008-03-31", ID==ID2)  %>% select( "Wgt") %>%    as.matrix()

# Calculate portfolio variance using matrix multiplication
portfolio_variances <- merged_data %>%
    group_by(Portfolio, Date)    %>% summarise(portfolio_variance = t(weights_vector)  %*% cov_matrix %*% weights_vector)

Answer 3

I'd suggest to reshape the data into a form that is more natural (and more efficient) for the computations; you can do this with ?tapply:

## <your data>
weights <- data.frame(Portfolio = c("P1", "P1", "P1"),
                      Date = c("2008-03-31", "2008-03-31", "2008-03-31"),
                      ID = c("Asset1", "Asset2", "Asset3"),
                      Wgt = c(0.1, 0.2, 0.3))


covar <- data.frame(
    Date = c("2008-03-31", "2008-03-31", "2008-03-31", "2008-03-31", "2008-03-31", "2008-03-31", "2008-03-31", "2008-03-31", "2008-03-31"),
    ID1 = c("Asset1", "Asset2", "Asset3", "Asset1", "Asset2", "Asset3", "Asset1", "Asset2", "Asset3"),
    ID2 = c("Asset1", "Asset1", "Asset1", "Asset2", "Asset2", "Asset2", "Asset3", "Asset3", "Asset3"),
    cov = c(0.011,      0.012,   0.013,    0.012,   0.022,     0.032,     0.013,     0.032,     0.033)
)

Create covariance matrices for all dates:

C <- covm <- tapply(covar$cov, covar[, c("ID1", "ID2", "Date")], c)
## , , Date = 2008-03-31
## 
##         ID2
## ID1      Asset1 Asset2 Asset3
##   Asset1  0.011  0.012  0.013
##   Asset2  0.012  0.022  0.032
##   Asset3  0.013  0.032  0.033

Create portfolios for all dates:

W <- tapply(weights$Wgt, list(weights$ID, weights$Portfolio, weights$Date), c,
            default = 0)
## , , 2008-03-31
## 
##         P1
## Asset1 0.1
## Asset2 0.2
## Asset3 0.3

Now you could loop over the dates and compute what you'd need. For variance and a specific date:

W[,, "2008-03-31"] %*% C[,, "2008-03-31"] %*% W[,, "2008-03-31"]

##         [,1]
## [1,] 0.00906

An equivalent computation is this one (taken from Vectorised objective functions):

colSums(C[,, "2008-03-31"] %*% W[,, "2008-03-31"] * W[,, "2008-03-31"])
## [1] 0.00906

This second formulation is useful when you have more than one portfolio for a given date. For instance:

weights <- data.frame(Portfolio = c("P1", "P1", "P1", "P2", "P2", "P2"),
                      Date = c("2008-03-31", "2008-03-31", "2008-03-31",
                               "2008-03-31", "2008-03-31", "2008-03-31"),
                      ID = c("Asset1", "Asset2", "Asset3",
                             "Asset1", "Asset2", "Asset3"),
                      Wgt = c(0.1, 0.2, 0.3, 0.5, 0.4, 0.1))
##   Portfolio       Date     ID Wgt
## 1        P1 2008-03-31 Asset1 0.1
## 2        P1 2008-03-31 Asset2 0.2
## 3        P1 2008-03-31 Asset3 0.3
## 4        P2 2008-03-31 Asset1 0.5
## 5        P2 2008-03-31 Asset2 0.4
## 6        P2 2008-03-31 Asset3 0.1

## (same code as above)
W <- tapply(weights$Wgt, list(weights$ID, weights$Portfolio, weights$Date), c,
            default = 0)

Now the portfolios are columns:

## , , 2008-03-31
## 
##         P1  P2
## Asset1 0.1 0.5
## Asset2 0.2 0.4
## Asset3 0.3 0.1


colSums(C[,, "2008-03-31"] %*% W[,, "2008-03-31"] * W[,, "2008-03-31"])
##      P1      P2 
## 0.00906 0.01526 

There are many details you'll need to check: do all portfolios hold the same assets, are there data for every calendar date, missing values, ...