I want to show the example of calling the Minimum Correlation Algorithm from Excel. I will use RExcel to connect R and Excel and will create a small VBA cell array function to communicate between Excel and R.

I have previously discussed the concept of connecting R and Excel in the “Calling Systematic Investor Toolbox from Excel using RExcel & VBA” post. Please read this post for the instructions to setup RExcel.

Following is a screen shot of the complete workbook:

You can download the MinimumCorrelation.xls workbook and experiment with it while you keep reading.

I created the “MinimumCorrelation” cell array function in VBA. Do not forget to use CTRL+SHIFT+ENTER to enter “MinimumCorrelation” function into your workbooks. The “MinimumCorrelation” function will send historical price information from Excel to the R environment. It will next execute the R script to construct weights using the Minimum Correlation Algorithm, and finally it will collect R calculations of portfolio weights and transfer them back to Excel.

Here is the R script that calls min.corr.portfolio() function. I created a VBA function “create_rcode” to create this file automatically for this example.

###############################################################################
# Load Systematic Investor Toolbox (SIT)                                       
# http://systematicinvestor.wordpress.com/systematic-investor-toolbox/         
###############################################################################
if(!exists('min.corr.portfolio')) {                                            
   setInternet2(TRUE)                                                          
   con = gzcon(url('http://www.systematicportfolio.com/sit.gz', 'rb'))         
       source(con)                                                             
   close(con)                                                                  
}                                                                              

   #*****************************************************************          
   # Setup                                                                     
   #*****************************************************************          
   n = ncol(hist_prices)                                                       
   hist = na.omit(hist_prices / mlag(hist_prices) - 1)                         
                                                                               
   # create historical input assumptions                                       
   ia = list()                                                                 
       ia$n = n                                                                
       ia$risk = apply(hist, 2, sd)                                            
       ia$correlation = cor(hist, use='complete.obs', method='pearson')        
       ia$cov = ia$correlation * (ia$risk %*% t(ia$risk))                      

   # portfolio allocation                                                      
   weights = min.corr.portfolio(ia, null)                                      
   dim(weights)=c(1,n)                                                         

Next, the “MinimumCorrelation” cell array function in VBA that calls min.corr.portfolio() function in R:

'Minimum Correlation Algorithm
Public Function MinimumCorrelation(ByRef r_values As Range) As Variant
    ' Start R connection
    RInterface.StartRServer

    ' Write R code to file
    create_rcode

    ' Put Historical Asset Prices into R
    RInterface.PutArray "hist_prices", r_values
    
    ' Executes the commands in filename
    RInterface.RunRFile r_filename
         
    ' Get Portfolio Allocation determined by the Minimum Correlation Algorithm into Excel
    MinimumCorrelation = RInterface.GetArrayToVBA("weights")
End Function

The complete working copy of the MinimumCorrelation.xls workbook.

Please do not forget to use CTRL+SHIFT+ENTER to enter “MinimumCorrelation” function into your workbooks.