Calling Minimum Correlation Algorithm from Excel using RExcel & VBA

September 26, 2012
By

(This article was first published on Systematic Investor » R, and kindly contributed to R-bloggers)

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.


To leave a comment for the author, please follow the link and comment on his blog: Systematic Investor » R.

R-bloggers.com offers daily e-mail updates about R news and tutorials on topics such as: visualization (ggplot2, Boxplots, maps, animation), programming (RStudio, Sweave, LaTeX, SQL, Eclipse, git, hadoop, Web Scraping) statistics (regression, PCA, time series, trading) and more...



If you got this far, why not subscribe for updates from the site? Choose your flavor: e-mail, twitter, RSS, or facebook...

Tags:

Comments are closed.