Financial Data Access with SQL, Excel & VBA

(1)

Computational Finance and Risk Management

Financial Data Access

with SQL, Excel & VBA

Guy Yollin

Instructor, Applied Mathematics University of Washington

(2)

Outline

1 What is RExcel?

2 Installation and configuration

3 Getting help with RExcel

4 Getting started with RExcel

5 The RCommander

6 Basic data transfer

7 Scratchpad mode

(3)

Lecture references

T. Baier, E. Neuwirth

Using R from within Excel. 2007.

Creating and Deploying an Application with (R)Excel and R. The R Journal Vol. 3/2, December 2011.

(4)

Computational Finance and Risk Management

Let’s not kid ourselves: The most widely used

piece of software for statistics is Excel.

(5)

Outline

1 What is RExcel?

5 The RCommander

7 Scratchpad mode

8 Scratchpad example

(6)

RExcel - an interface between R and Excel

RExcel is an interface program that uses R as an add-in to Excel

Transfers data between R and Excel, using Excel as a data editor for data to be analyzed with R

Uses Excel as a container and simple code editor for R commands, and running R commands from Excel

Runs R commands from Excel VBA macros allowing users to create Excel based applications and Excel add-ins using R

Uses R functions in Excel worksheets functions, integrating R functionality into the automatic recalculation engine of Excel

(7)

RExcel modes of operation

RExcel supports three modes of operation:

Scratchpad and data transfer mode

Menus control data transfer from R to Excel and back; commands can be executed immediately, either from Excel cells or from R command line

Macro mode

Macros, invisible to the user, control data transfer and R command execution

Spreadsheet mode

Formulas in Excel cells control data transfer and command execution, automatic recalculation is controlled by Excel

(8)

RExcel components

statconnDCOM A DCOM† server which allows to seamlessly integrate R or Scilab into other applications.

rcom An R package allowing to have R and another program running at the same time and let them communicate.

rscproxy An R package which is required for rcom and for the statconn (D)COM Server.

RExcel An Excel addin using statconn (D)COM or rcom to allow Excel to call R from within Excel.

RAndFriends An installation wrapper containing installation programs for R (including many packages), statconnDCOM, RExcel, and the example files for the book R through Excel.

(9)

Outline

1 What is RExcel?

5 The RCommander

7 Scratchpad mode

(10)

RAndFriends

Download the latest version of RAndFriends from the statconn website. Download includes:

Latest version of R (2.15.1) Package rscproxy 2.0-5 Package rcom 2.2-5 To install:

Run the executable and accept default installation options

(11)

Excel add-ins and developer tabs

Add-Ins Tab

Contains RExcel add-in menu Developer Tab

Contains access to macro creation and VBA development If the Add-Ins and/or Developer tab is not visible:

Go toFile →Options → Customize Ribbon. Activate theAdd-Insand Developer

checkbox under "Main Tabs".

(12)

Establishing a reference to

RExcelVBAlib

To use RExcel VBA procedures, a reference must be established to the

RExcelVBAlib object library

From the VBE in Excel: Select the "Tools" tab Select "References"

Find the object library labeled

RExcelVBAliband activate the checkbox

Click "OK" and exit the VBA editor

(13)

Outline

1 What is RExcel?

5 The RCommander

7 Scratchpad mode

(14)

Getting help

To access the RExcel help click the RExcel add-in menu and select "RExcel Help"

This help option opens "Using R from within Excel" in a web browser

Contents

"Using R from within Excel" is a quick reference guide

For additional information, see "R Through Excel"

(15)

RExcel tutorial video

"Combining R and Excel" tutorial video

Provides RExcel walkthrough commentary

Walkthrough material is available through the RExcel add-in menu

http://rcom.univie.ac.at/RExcelDemo/

It is highly recommended that students view this material before getting started with RExcel.

(16)

Walkthroughs

RExcel comes with walkthrough demo worksheets.

To start working on the demo material click the RExcel add-in and select "Demo Worksheets"

Demo worksheets include: Data transfer

Writing macros Worksheet functions Graphics with sliders Interactive graphics

(17)

Outline

1 What is RExcel?

5 The RCommander

7 Scratchpad mode

(18)

Starting RExcel

To start RExcel:

Navigate to the add-ins tab Click on the RExcel add-in to bring up the RExcel add-in menu

Click "Start R"

The RExcel splash screen should appear briefly

(19)

The RExcel add-in menu

Once the R Server is started, the RExcel add-in menu will appear as follows†:

†

If foreground server is started, option will read Disconnect R rather then Close R

(20)

RExcel add-in menu commands

RExcel add-in commands:

Start R Starting RExcel will begin a new R session.

Close R Closing RExcel will end your R session.

Run Code Executes valid R code from a cell or a range of cells.

Get R Value Puts the value of an R expression into the active cell or range of cells.

Put R Var Puts a valid selection of cells into an array or dataframe object.

(21)

RExcel add-in menu commands cont.

Set R working dir Allows the user to set a new working directory.

Load R file Loads an .R file into the current R session from the working directory.

Copy Code Copies selected R code and inputs the selection into a VBA procedure.

Debug R Displays the debug window in which all calls to R are displayed before execution.

Error Log Displays an additional window with error messages.

Options Displays an options menu.

Set R server Allows the user to switch between a foreground server or background server.

(22)

RExcel add-in menu commands cont.

RExcel Help Opens "Using R from within Excel" in a browser window.

R Help Opens the R help documentation.

RCommander Opens the RCommander with Excel or separate menu options.

Demo Worksheets Opens the Demo walkthroughs in a new Excel spreadsheet.

RthroughExcel Worksheets Opens RExcel projects with advance graphics and analysis.

Mark Calc cells Marks cells containing calculated results.

(23)

The RExcel RMB menu

Once the R Server is started, there will be RExcel specific right-mouse-button options:

(24)

The RExcel RMB menu cont.

The RExcel RMB menu adds the following options:

Insert Current R Plot Inserts the current R Plot into a given range.

Name Range Create a named range for easier selection.

(25)

Setting the R Server type

Setting the R Server

Before starting RExcel, the "Set R server" option is available. There are two methods by which to run RExcel:

Background server Foreground server

Starting RExcel initiates a number of different processes at startup. These processes are explained at greater length in the Startup section of "Using R from within Excel".

(26)

Setting the R Server type

Background Server

The background server is totally hidden from the user, all interaction with R has to be done in Excel.

Foreground Server

The foreground server allows direct access to the R GUI command line while working in Excel.

(27)

Background server

To initiate the background server: Open the RExcel add-in menu Select Set R server

Under server type, click "Background"

Note

No visible R GUI

All R commands must be sent from Excel

(28)

Foreground server

To initiate the foreground server: Open the RExcel add-in menu Select Set R server

Under server type, click "Foreground"

Note

R GUI is visible

R commands can be typed directly into the command line or sent from Excel

(29)

Background vs. foreground

Background advantages

R operates in the background, invisible to front-end users Background disadvantages

R operates in the background, invisible to front-end users Foreground advantages

For those familiar with R, a foreground server provides a more convenient method by which to pass R commands

A foreground server is simply supplemental, no RExcel functionality is lost by running a foreground server

Foreground disadvantages

Do not close the R GUI before disconnecting from the DCOM server. This may cause system instability.

If R is upgraded but RExcel is not reinstalled, RExcel will not work with the foreground server

(30)

Outline

1 What is RExcel?

5 The RCommander

7 Scratchpad mode

(31)

RCommander

R Commander is a GUI for the R† Developed by John Fox at McMaster University Licensed under GNU GPL Along with plug-ins, arguably the most fully featured GUI for R along the lines of S-PLUS

†

http://socserv.mcmaster.ca/jfox/Misc/Rcmdr/

(32)

The RCommander Window

The RCommander windows can be thought of as a re-skinned version of the R GUI.

RCommander contains three distinct sub-windows:

Script Window Type or open R scripts

Output Window Displays output from submitted R commands

Messages Displays error messages and general information

(33)

The RCommander functions and methods

RCommander functionality:

File Save and/or open scripts, set a new working directory

Edit Copy, paste, cut, undo, redo, etc.

Data Import data from various sources and manage datasets

Statistics Get statistics regarding the active dataset

Graph Plot the active dataset

Models Create statistical models based upon the active dataset

Distributions Plot distributions and compute relevant statistical values

Tools Load packages/plug-ins and access RCommander options

Help Access R and RCommander Help

Depending upon the nature of the active dataset, some options may not be available.

(34)

Starting the RCommmander

RExcel provides menu options, via the RCommander, for some data transfer, analysis and visualization. To start the RCommander:

Start RExcel

Open the RExcel add-in menu Click "RCommander"

Select either "with Excel menus" or "with separate menus"

(35)

Activating a dataset

In order create models and

distributions using the RCommander, a dataset must be defined:

To activate a dataset click "<No active dataset>" located below the tab bar

Notice that the user must define at least one dataset in the current R session to activate a dataset (to be explained in the following sections)

Select a dataset from the dropdown menu and click "OK"

(36)

Outline

1 What is RExcel?

5 The RCommander

7 Scratchpad mode

(37)

Arrays and dataframes

RExcel can handle two classes of data:

Array

RExcel uses the term array to mean R arrays, matrices, and vectors. Basic R data types where all elements are of the same class.

Dataframe

A dataframe is a two-dimensional matrix-like structure where each column can be of different classes.

(38)

Using RExcel to create a data.frame in R

There are various methods by which RExcel can create a data.frame in R.

RExcel add-in menu options RMB menu options

RCommander menu options R commands typed directly into the R GUI or the RCommander script window

RExcel worksheet functions RExcel VBA procedures

(39)

Sending a data.frame from Excel via RExcel add-in menu

For beginning users, the most convenient

method is to use the RMB menu options. Start RExcel

Highlight a selection of data Right-click the data selection Choose "Put R DataFrame"

Create a dataframe name or accept default name

Note

RExcel assumes that the data selection contains column headers

By default, RExcel names the

dataframe after the spreadsheet title. This can be changed in the RExcel add-in menu under "Options".

(40)

Importing data via RCommander

A dataframe can also be created through the RCommander menu options:

Start RExcel and open the RCommander

Open the "Data" tab Import data

Note

The RCommander accepts various data forms:

.txt, .dat, .csv, .xls, .xlsx, etc. The RCommander can also load data from any attached R package

To attach additional packages go to the "Tools" tab in the RCommander and select "Load package(s)"

(41)

Importing data via RCommander cont.

RCommander will create R code based upon user input from the data import wizard.

(42)

Retrieving a data.frame from R

There are a few methods by which to retrieve a dataframe from R.

RExcel add-in menu options RMB menu options

(43)

Retrieving a data.frame from R via RExcel RMB

RMB menu options

Insure RExcel is running

Right click the cell in which you want the dataframe to begin

Choose "Get R DataFrame" Click "Get from R"

Select a dataframe from the dropdown Note

If you ended the connection to the DCOM server, previously created dataframes will have been deleted Sequential numbering will be included unless the "with rownames" checkbox is off

(44)

Outline

1 What is RExcel?

5 The RCommander

7 Scratchpad mode

(45)

RExcel as code container and editor

RExcel can run valid R code from a cell or selection of cells. Sending and receiving information between R and Excel is accessible through the RExcel add-in menu and the RMB menu.

(46)

The "Run code" option

By interfacing R and Excel through RExcel, R code can be typed directly into a cell or a range of cells and be sent to R:

There is no need to enclose the R expression in quotations Code can be run by accessing the RExcel add-in menu or the RMB menu

If there is a coding issue, an pop-up will relay error messages directly from R

(47)

Special RExcel commands

RExcel implements a few special commands that allow the automation of data exchanage without VBA. They can be run through the "Run Code" option.

Command Description

#!rputvariable range store the value (contents) of a range in an R variable #!rputdataframevariable range store the value of a range in an R data frame #!rputpivottablevariable range store the value of a range in an R variable #!rgetr-expression range store the value of the R expression in the range #!rgetdataframer-expression range store the data frame of the R expression in the range #!insertcurrentrplotcell-address insert the active plot into the worksheet

(48)

Outline

1 What is RExcel?

5 The RCommander

7 Scratchpad mode

(49)

The quantmod package

The quantmodpackage for R is designed to assist the quantitative trader in the development, testing, and deployment of statistically based trading models.

Key functions:

getSymbols load or download price data

Yahoo Finance / Google Finance FRED

Oanda csv, RData MySQL, SQLite

chartSeries charting tool to create standard financial charts Author:

Jeffrey Ryan

(50)

The

getSymbols

function

The getSymbolsfunction loads (downloads) historic price data R Code: ThegetSymbolsfunction

> library(quantmod) > args(getSymbols)

function (Symbols = NULL, env = .GlobalEnv, reload.Symbols = FALSE, verbose = FALSE, warnings = TRUE, src = "yahoo", symbol.lookup = TRUE, auto.assign = TRUE, ...)

NULL

Main arguments:

Symbols symbols to be loaded

src source of the data (Yahoo, Google, FRED, etc.) Return value:

(51)

Federal reserve economic data

The function getSymbolscan also be used to access data from the Federal Reserve Economic Data (FRED) database

http://research.stlouisfed.org/fred2/

(52)

(53)

Ready to Run code

(54)

(55)

Data and plot retrieved from R

(56)

(57)

Sending data.frame to R via special command

(58)

(59)

Computational Finance and Risk Management

http://depts.washington.edu/compfin

Guy Yollin

†http://en.wikipedia.org/wiki/Component_Object_Model

†http://socserv.mcmaster.ca/jfox/Misc/Rcmdr/

http://research.stlouisfed.org/fred2/

http://depts.washington.edu/compfin