Home > Portfolio Construction, R > Calling Systematic Investor Toolbox from Excel using RExcel & VBA

## Calling Systematic Investor Toolbox from Excel using RExcel & VBA

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 of the 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 the following guide to help with my installation http://homepage.univie.ac.at/erich.neuwirth/php/rcomwiki/doku.php?id=wiki:how_to_install

Next, please check that RExcel is working properly by trying some examples from
http://homepage.univie.ac.at/erich.neuwirth/php/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 an Efficient Frontier. Following is a screen shot of the complete interface: Let’s put our Input Assumptions into Excel: Return, Risk, and Correlation matrix in rows 1:12. Let’s make a button at row 14 to construct an Efficient Frontier and associate it with our “create_efficient_frontier” VBA macro.

The “create_efficient_frontier” VBA macro will collect our Input Assumptions from Excel and send them to the R environment. It will next execute the R script to construct the Efficient Frontier, and finally it will collect R calculations of Risk, Return, and Weights of portfolios lying on the Efficient Frontier and transfer them 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 be a static file containing all the logic for your algorithm.

```###############################################################################
# Load Systematic Investor Toolbox (SIT)
# https://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 an Efficient Frontier with user specified Input Assumptions. This workbook can be used by non-R users to explore the power of the R language, as long as RExcel is installed.

Please make sure that you add the RExcel library to the references. First, click on the Tool->References (from the VBA IDE menu) Next select RExcelVBAlib and press Ok Categories: Portfolio Construction, R
1. April 10, 2012 at 3:06 am

great post. Just wanted to note that when I opened the file directly from the Internet into Excel, it seemed the R server creation tried to automatically setwd (set my working directory) to the http site from download which of course does not work. When I saved a local copy, Excel created the R server instance fine.

Also, naming the reference in VBA with Tools->References might be tricky to some. I remember way back in the good old days not understanding that this was even an option, and I probably burned a couple days fumbling to figure it out. I also know it is extremely difficult to programatically add the reference (thisworkbook.VBProject.References.AddFromFile), so users will be on their own. Could you possibly add a screenshot of that menu?

I have had trouble (as they warn) with some RExcel functionality in 64-bit Office, but all your code runs great.

Extremely well done as always.

2. April 11, 2012 at 9:36 am

Thanks, this post safed me two working days figuring out how to call a script with a button (a must have in the eye of my boss).

3. May 16, 2013 at 8:47 pm

Everyone loves what you guys tend to be up too. Such clever work
and coverage! Keep up the good works guys I’ve added you guys to my blogroll.

1. April 22, 2012 at 12:23 am
2. September 27, 2012 at 3:36 am