# Calling Systematic Investor Toolbox from Excel using RExcel & VBA

April 9, 2012
By

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

RExcel is a great tool to connect R and Microsoft Excel. With a press of a button, I can easily execute my R scripts and present output interactively in Excel. This easy integration allows non-R users to explore the power R language. As an example of this approach, I want to show how to create an Efficient Frontier using Systematic Investor Toolbox and display it in Excel.

First, you need to install RExcel from http://rcom.univie.ac.at/download.html I used following guide to help with my installation http://learnserver2.csd.univie.ac.at/rcomwiki/doku.php?id=wiki:how_to_install

Next, please check that RExcel is working properly by trying some examples from
http://learnserver2.csd.univie.ac.at/rcomwiki/doku.php?id=wiki:excel_worksheet_functions_using_r

Now, we are ready to design the workbook to run mean-variance optimization to create Efficient Frontier. Following is a screen shot of the complete interface:

Let’s put Input Assumptions: Return, Risk, and Correlation matrix in rows 1:12. Let’s make a button to construct Efficient Frontier at row 14, and associate it with “create_efficient_frontier” VBA macro.

The “create_efficient_frontier” VBA macro will collect Input Assumptions and send them to R environment, it will next execute the R script to construct the Efficient Frontier, and finally it will collect the Risk, Return, and Weights of portfolios lying on the Efficient Frontier and transfer it back to Excel.

Here is the R script to construct the Efficient Frontier. I created a VBA function “create_rcode” to create this file automatically for this example. In practice this can a static file containing all the logic for your algorithm.

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

#--------------------------------------------------------------------------
# Create Efficient Frontier
#--------------------------------------------------------------------------
ia = list()
ia\$symbols = ia.name
ia\$n = len(ia\$symbols)
ia\$expected.return = ia.return
ia\$risk = ia.risk
ia\$correlation = ia.correlation
n = ia\$n

# 0 <= x.i <= 1
constraints = new.constraints(n, lb = 0, ub = 1)

# SUM x.i = 1
constraints = add.constraints(rep(1, n), 1, type = '=', constraints)

# create efficient frontier
ef.risk = portopt(ia, constraints, 50)
```

The “create_efficient_frontier” VBA macro will collect Input Assumptions and send them to R environment, it will next execute the R script to construct the Efficient Frontier, and finally it will collect the Risk, Return, and Weights of portfolios lying on the Efficient Frontier and transfer it back to Excel.

Here is the “create_efficient_frontier” VBA macro that automates all the functionality:

```' Create Efficient Frontier
Sub create_efficient_frontier()
' Start R connection
RInterface.StartRServer

' Write R code to file
create_rcode

' Clean Output Area
Sheets("AssetAllocation").Range("A17:IV10000").ClearContents

' Put Input Assumptions into R
RInterface.PutArray "ia.name", Range("AssetAllocation!A4:A12")
RInterface.PutArray "ia.return", Range("AssetAllocation!B4:B12")
RInterface.PutArray "ia.risk", Range("AssetAllocation!C4:C12")
RInterface.PutArray "ia.correlation", Range("AssetAllocation!F4:N12")

' Executes the commands in R script
RInterface.RunRFile r_filename

' Get Efficient Frontier into Excel
RInterface.GetArray "ef.risk\$return", Range("AssetAllocation!B17")
RInterface.GetArray "ef.risk\$risk", Range("AssetAllocation!C17")
RInterface.GetArray "ef.risk\$weight", Range("AssetAllocation!E17")
End Sub
```

Here is a complete AssetAllocation.xls workbook that creates Efficient Frontier with user specified Input Assumptions. This workbook can be used by non-R users to explore the power R language, as long as RExcel is installed.

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

Tags: ,