**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*.

**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...