Automize portfolios volatilities computation

[snoozecj]

Hi All,

Thanks for reading my post. I have a series of portfolios created from the combination of several stocks. I should compute the volatility of those portfolios using the historical daily performances of each stock. Since I have all the combinations in one data frame (called combinations_of_certificates), and all stocks return in another data frame (called perf, where the columns are stocks and rows days) I don't know which will be the most efficient way to automize the process. Below you can find an extract:

> Combinations of certificates

        ISIN_1       ISIN_2       ISIN_3       ISIN_4
2 CH0595726594 CH1111679010 XS1994697115 CH0587331973
3 CH0595726594 CH1111679010 XS1994697115 XS2027888150
4 CH0595726594 CH1111679010 XS1994697115 XS2043119358
5 CH0595726594 CH1111679010 XS1994697115 XS2011503617
6 CH0595726594 CH1111679010 XS1994697115 CH1107638921
7 CH0595726594 CH1111679010 XS1994697115 XS2058783270
8 CH0595726594 CH1111679010 XS1994697115 JE00BGBBPB95

> perf

     CH0595726594  CH1111679010  XS1994697115  CH0587331973
626  0.0055616769 -0.0023656130  1.363791e-03  1.215922e-03
627  0.0086094443  0.0060037334  0.000000e+00  2.519220e-03
628  0.0053802380  0.0009027081  0.000000e+00  7.508635e-04
629 -0.0025213543 -0.0022046297  4.864050e-05  1.800720e-04
630  0.0192416817  0.0093401627 -6.079767e-03  3.800836e-03
631 -0.0101224820  0.0051741294  6.116956e-03 -1.345184e-03
632 -0.0013293793 -0.0100475153 -4.494163e-03 -1.746106e-03
633  0.0036350604  0.0012999350  3.801130e-03 -5.997121e-05
634  0.0030097434 -0.0011484496 -1.187614e-03 -2.069131e-03
635  0.0002034381  0.0030493901 -1.851762e-03 -3.806280e-04
636 -0.0035594427  0.0167455769 -2.148123e-04 -4.709560e-04
637  0.0007654623 -0.0051958237 -3.711191e-04  1.604010e-04
638  0.0107592678 -0.0016260163  4.298764e-04  3.397951e-03
639  0.0050953486 -0.0007403020  2.011738e-03  8.790770e-04
640  0.0008532851 -0.0071121648 -9.746114e-04  5.389598e-04
641 -0.0068204614  0.0133810874 -9.755622e-05 -1.346674e-03
642  0.0091395678  0.0102591793  1.717157e-03 -1.977785e-03
643  0.0027520640 -0.0157912638  1.256440e-03 -1.301119e-04
644 -0.0048902196  0.0039494471 -1.624514e-03 -3.373340e-03
645 -0.0116838833  0.0062450826  6.625549e-04  1.205255e-03
646  0.0004566442 -0.0018570102 -3.456636e-03  4.474138e-03
647  0.0041586368  0.0085679315  4.435933e-03  1.957455e-03
648  0.0007575758  0.0002912621  0.000000e+00  2.053306e-03
649  0.0046429473 -0.0138309230 -4.435798e-03  1.541798e-03
650  0.0049731250 -0.0488164953  4.181975e-03 -9.733133e-04
651  0.0008497451 -0.0033110870  2.724477e-04 -7.555498e-04
652  0.0004494831  0.0049831300 -8.657588e-04 -1.790813e-04
653 -0.0058905751  0.0020143588  8.178287e-04 -1.213991e-03
654  0.0000000000  0.0167525773  4.864050e-05  9.365068e-04
655  0.0010043186  0.0048162231  0.000000e+00 -2.110146e-03
656 -0.0024079462 -0.0100403633 -2.431907e-03 -9.176600e-04
657 -0.0095544604 -0.0193670047  0.000000e+00 -8.935435e-03
658  0.0008123477  0.0114339172  2.437835e-03  5.530483e-03
659  0.0022828734 -0.0015415446 -3.239300e-03  2.765060e-03
660  0.0049096523 -0.0001029283  3.199079e-02  2.327835e-03
661 -0.0027702226 -0.0357198003  9.456712e-04  3.189602e-04
662 -0.0008081216 -0.0139311449 -2.891020e-02 -1.295363e-03
663 -0.0033867462  0.0068745264 -2.529552e-03 -1.496588e-04
664 -0.0015216068 -0.0558572120 -3.023653e-03 -7.992975e-03
665  0.0052829422  0.0181072771  4.304652e-03 -3.319519e-03
666  0.0084386054  0.0448545861 -8.182748e-04  4.279284e-03
667 -0.0076664829 -0.0059415480 -2.047362e-04  6.059936e-03
668 -0.0062108665 -0.0039847073  7.313506e-04  5.993467e-04
669 -0.0053350948  0.0068119154 -1.042631e-02 -2.056524e-03
670 -0.0263588067  0.0245395479 -2.188962e-02 -6.732491e-03
671 -0.0021511018  0.0220649895  1.412435e-02  1.702085e-03
672  0.0205058100 -0.0007179119  3.057527e-03 -1.002423e-02
673  0.0096862280 -0.0194488633  1.207407e-03 -1.553899e-03
674  0.0007143951 -0.0068557672  6.227450e-03  1.790274e-03
675 -0.0021926470 -0.0051114507 -6.267498e-03 -1.035691e-03
676  0.0076655765 -0.0139300847  6.583825e-03  3.059472e-03
677 -0.0032457653  0.0180480206 -4.635495e-03  1.064002e-03
678  0.0036633764  0.0060676410 -2.762676e-04  5.364970e-04
679 -0.0008111122 -0.0013635410 -1.065898e-03  1.214059e-03
680  0.0050228311  0.0055141267  3.003507e-03  1.121643e-03
681 -0.0007067495  0.0147281558 -2.699002e-03 -1.514035e-04
682 -0.0024248548  0.0002573473 -2.113685e-03 -1.423409e-03
683 -0.0002025624  0.0138417207 -4.374895e-03  1.415328e-04
684 -0.0141822418 -0.0169517332 -3.578920e-03 -1.799234e-03
685 -0.0005651749 -0.0259693324 -5.926428e-03 -3.635333e-03
686  0.0004112688  0.0133043570 -1.545642e-03  1.981828e-03
687 -0.0150565262 -0.0107757493 -1.717916e-02 -1.328749e-02
688  0.0039129754 -0.0441013167 -8.376631e-03 -5.653841e-04
689  0.0019748467  0.0115063340 -2.835394e-02  7.868428e-03
690  0.0072614108  0.0358764014  3.586897e-02  7.960077e-03
691 -0.0003604531  0.0106119001  1.024769e-04 -2.733651e-04

What I should do is look for each portfolio (each row of final_output is a portfolio, i.e. 4 stocks portfolio) in perf and compute the volatility (standard deviation) of that portfolio using the stocks historical daily performances of the last three months. (Of course, here I have pasted only 4 stocks performances for simplicity, I have more than 200) Once done for the first, I should do the same for all the other rows (portfolios).

Below is the formula I used for computing the volatility:

#formula for computing the volatility
sqrt(t(weights) %*% covariance_matrix %*% weights)

#where covariance_matrix is
cov(portfolio_component_monthly_returns)

#All the portfolios are equiponderated
weights = [ 0.25 0.25 0.25 0.25 ]

What I'm trying to do since yesterday is to automize the process for all the rows, indeed I have more than 10'000 rows. I'm an RStudio naif, so even trying and surfing on the new I have no results and no ideas of how to automize it. Would someone have a clue how to do it?

Hope to have been clearer as possible, in case do not hesitate to ask me.

Many thanks

[HanOostdijk]

I think this could be done by using the dplyr::rowwise function.
For that you should write the function that does the calculation for one row only (for one combination of 4 stocks)

[snoozecj]

Thanks for the answer. I have tried both with dplyr::rowwise and a for cycle, but both haven't worked. dplyr::rowwise probably is my fault because I don't understand how to use it, i.e. how to define the i - row. Please find below the code I used for the for cycle:

#for cycle

for (i in 1:nrow(Combination_of_certificates)) {
  portfolio_ISIN <- matrix(data=0,nrow=nrow(perf),ncol=4,byrow=FALSE)
  portfolio_ISIN=data.frame(portfolio_ISIN)
  names(portfolio_ISIN)[1] <- Combination_of_certificates[i,1]
  names(portfolio_ISIN)[2] <- Combination_of_certificates[i,2]
  names(portfolio_ISIN)[3] <- Combination_of_certificates[i,3]
  names(portfolio_ISIN)[4] <- Combination_of_certificates[i,4]
  
  # how to find a column with the same name in another data frame
  
  portfolio_ISIN [,1] <- get(names(portfolio_ISIN) [1],perf)
  portfolio_ISIN [,2] <- get(names(portfolio_ISIN) [2],perf)
  portfolio_ISIN [,3] <- get(names(portfolio_ISIN) [3],perf)
  portfolio_ISIN [,4] <- get(names(portfolio_ISIN) [4],perf)
  
  portfolio_component_monthly_returns <- portfolio_ISIN [,1:4]
  
  covariance_matrix <- cov(portfolio_component_monthly_returns)
  
  volatility <- matrix(data=0,nrow=nrow(Combination_of_certificates),ncol=1,byrow=FALSE)
  
  volatility [i,1] <- sqrt(t(weights) %*% covariance_matrix %*% weights)
  
  volatility[i,1] <- volatility[i,1]*sqrt(250)

}

The code inside works, but I cannot understand how to automize it from a row to another.
Indeed, after having run the code, what I have is just a column of zero and the last value of the row. Would you know how to apply this code with dplyr::rowwise?

[HanOostdijk]

Hello @snoozecj ,

see the example below where I specify only two combinations of 4 stocks
and set the returns for the five stocks involved to randoms numbers.

I create the function calc_volatility that follows ( I assume but check!) your calculation for one combination.
And then I call that function for all combinations (here restricted to the two I specified) with

volatilities <- Combination_of_certificates %>%
  rowwise() %>%
  mutate(vol = calc_volatility(ISIN_1,ISIN_2,ISIN_3,ISIN_4))

I hope this helps. The full code you will find here:

library(dplyr)
#> 
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#> 
#>     filter, lag
#> The following objects are masked from 'package:base':
#> 
#>     intersect, setdiff, setequal, union
library(magrittr)
library(stringr)
library(purrr)
#> 
#> Attaching package: 'purrr'
#> The following object is masked from 'package:magrittr':
#> 
#>     set_names

set.seed(2021)

Combination_of_certificates <- data.frame(
  ISIN_1 = c("CH0595726594", "CH0595726594") ,
  ISIN_2 = c("CH1111679010", "CH1111679010") ,    
  ISIN_3 = c("XS1994697115", "XS1994697115") ,
  ISIN_4 = c("CH0587331973", "XS2027888150") 
)
head(Combination_of_certificates)
#>         ISIN_1       ISIN_2       ISIN_3       ISIN_4
#> 1 CH0595726594 CH1111679010 XS1994697115 CH0587331973
#> 2 CH0595726594 CH1111679010 XS1994697115 XS2027888150

perf <- data.frame (
  CH0595726594 = rnorm(250),
  CH1111679010 = rnorm(250),
  XS1994697115 = rnorm(250),
  CH0587331973 = rnorm(250),
  XS2027888150 = rnorm(250) 
)
head(perf)
#>   CH0595726594 CH1111679010 XS1994697115 CH0587331973 XS2027888150
#> 1   -0.1224600   0.26093831   0.21598179    1.6508943   0.80959540
#> 2    0.5524566  -0.43434214  -1.39253776    0.2017592   0.09941633
#> 3    0.3486495  -1.87300754  -0.07437819    0.8139997  -2.34302131
#> 4    0.3596322  -0.80304453  -1.13603889    1.0523580   0.67652598
#> 5    0.8980537   0.33232209  -0.42951107   -0.8471807  -3.61147374
#> 6   -1.9225695   0.01211264   0.47957570    0.4378037  -0.16416799

calc_volatility <- function(isin_1,isin_2,isin_3,isin_4) {
  # function to calculate volatility for one combination of stocks
  stocks  <- c(isin_1,isin_2,isin_3,isin_4)
  # column numbers in perf for this combination
  indices <- purrr::map_dbl(stocks, ~stringr::str_which(names(perf),.))
  portfolio_component_monthly_returns <- perf [,indices]
  covariance_matrix <- cov(portfolio_component_monthly_returns)
  weights <- c(0.25, 0.25, 0.25, 0.25) # weights not necessary now (?)
  sqrt(t(weights) %*% covariance_matrix %*% weights) * sqrt(250)
} 

volatilities <- Combination_of_certificates %>%
  rowwise() %>%
  mutate(vol = calc_volatility(ISIN_1,ISIN_2,ISIN_3,ISIN_4))

head(volatilities)
#> # A tibble: 2 x 5
#> # Rowwise: 
#>   ISIN_1       ISIN_2       ISIN_3       ISIN_4       vol[,1]
#>   <chr>        <chr>        <chr>        <chr>          <dbl>
#> 1 CH0595726594 CH1111679010 XS1994697115 CH0587331973    8.19
#> 2 CH0595726594 CH1111679010 XS1994697115 XS2027888150    8.04
Created on 2021-08-31 by the reprex package (v2.0.0)