Расширяем возможности MS Excel 2010 c помощью R

Добрый день, уважаемые читатели.

Сегодня я хочу показать как можно связать возможности языка R и офисного пакета MS Excel 2010. Ниже я расскажу о том, как можно расширить функционал встроенного языка VBA с помощью функций R, а поможет мне в этом надстройка RExcel. Инструкцию по его установке можно без проблем найти в сети или на офф. сайте.

Постановка задачи и предварительные настройки

Для того, чтобы мы смогли использовать функции R из VBA необходимо в Excel открыть «редактор Visual Basic» (Alt + F11). После этого к проекту надо добавить модуль RExcelVBAlib, сделать это можно перейдя Tools->References и поставить галочку на нужном пункте.



Данный модуль содержит класс rinterface, по средством, которого и происходит взаимодействие составляющих нашей связки.

Для демонстрации я взял исходные данные по котировакам доллара с сайта «Финам» за период 16.12.2011 по 20.03.2014. Для примера на их основе средствами R построим график изменения ежемесячной цены открытия ('OPEN').

Основы работы с rinterface из RExcel

Для начала давайте напишем код на R, который будет выполнять поставленную задачу и сохраним его в файл, например agg_price.R(он нам понадобиться в дальнейшем).

library(zoo) 

agg_price_func <- function(x) {
  y <- zoo(x$OPEN, as.Date(as.character(x$DATE),"%Y%m%d"))
  new_y <- aggregate(y, as.yearmon, mean)
  plot(new_y)
  return (new_y)
  }

Разберемся что делает данный код.

Сначала загружаем библиотеку zoo, которая понадобиться нам для работы с временными рядами.

Затем создаем функцию, которая выполняет следующее:

• Преобразует наш набор данных во временной ряд. Индексами которого будут значения столбца 'Date', преобразованные в дату. Значения уровней данного рядя будут равны столобцу 'OPEN'.


• С помощью следующей строки мы агрегируем наши данные по месяцам с помощью функции aggregate. Данный шаг нужен потому, что исходные данные у нас содержат ежедневные данные, а нам надо перейти к месяцам.


• выводим график по месячным значениям


• возвращаем массив с месячными значения, на основе которых строился график.

Итак, код на R мы написали. Теперь посмотрим, как вызывать его из VBA.

Для этого есть несколько способов, которые будут показаны ниже.

Способ 1. Построчное выполнение команд функции.

Данный способ является самым простым для понимания и самым длинным по количеству строк кода. Код процедуры для него следующий:

Sub call_r_func()

RInterface.PutDataframe "open_price", Range("USD!A1:C535")
RInterface.RRun "library(zoo)"
RInterface.RRun "price <- zoo(open_price$OPEN, as.Date(as.character(open_price$DATE),""%Y%m%d""))"
RInterface.RRun "agg_price <- aggregate(price, as.yearmon, mean)"
RInterface.RRun "plot(agg_price)"
RInterface.InsertCurrentRPlot Range("OPEN_PRICE!A1"), widthrescale:=0.5, heightrescale:=0.5, closergraph:=True    

End Sub

Как можно заметить в данной процедуре и используются 3 функции из rinterface:

1. PutDataframe


2. RRun


3. InsertCurrentRPlot

Функция PutDataframe служит для загрузки данных в переменную языка R типа dataframe. В качестве первого параметра ей передается имя будущей переменной, которую будет использовать интерфейс. Вторым параметром будет диапазон значений, которые будет содержать переменная.

RRun служит для выполнения команда интерпретатора R. В качестве параметра ей передается строка, которая будет выполнена им.

Последняя в списке InsertCurrentRPlot выполняет вывод R графики на заданный лист MS Excel. В качестве первного парамерта ей передается ячека в которую будет выводиться график. Параметры widthrescale и heightrescale используются для масштабирования выводимого графика (в нашем случаем 50%). closergraph сигнализирует о том, что график, выведенный с помощью функции plot() нужно закрыть.

После описанных выше функций работа процедуры call_r_func() не должна вызывать вопросов. Вкратце данная процедура просто выполнила описанный выше R скрипт построчно.

Плюсом данного способа является, то что весь код сосредоточен в VBA макросе , что может быть удобно для небольших задач.

Недостатком может является неудобная отладка кода на R.

Способ 2. Использование внешней функции для выводом графика.

Процедура для этого способа выглядит следующим образом:

Sub call_r_impotr_func_without_print()

RInterface.RunRFile "D:/agg_price.R"
RInterface.RunRCall "agg_price_func", AsSimpleDF(Range("USD!A1:C535"))
RInterface.InsertCurrentRPlot Range("OPEN_PRICE!H1"), widthrescale:=0.5, heightrescale:=0.5, closergraph:=True

End Sub

В данном коде появились 3 новых функции:

1. RunRFile


2. AsSimpleDF


3. RunRCall

Первая в данном списке RunRFile позволяет выполнить код, находящийся в файле .r

AsSimpleDF преобразует выбранный в параметре диапазон в тип набора данных (dataframe).

Функция RunRCall выполняет вызов процедуры и результат ее не возвращает (return (...) игнорируется). В качестве первого параметра передается имя процедуры, либо ее код на R. Все последующие парамерты передают параметры определенные в процедуре (в нашем случае он один).

Из описания выше, становиться ясно, что наша процедура call_r_impotr_func_without_print() сначала выполняет внешний файл agg_price.R. Затем она вызывает функцию agg_price_func для набора данных из заданного диапазона. И в конце выводит график на заданный лист.

Плюсом у данного метода является простота отладки кода на R , т.к. он может быть написан в любом редакторе или IDE.

Из минусов надо отметить, что на выходе вместо одного файлика мы получим 2: xls и r.

Способ 3. Использование внешней функции с выводом данных и графиком.

Код процедуры:

Sub call_r_impotr_func_with_print()

RInterface.RunRFile "D:/agg_price.R"
RInterface.GetRApply "agg_price_func", Range("OPEN_PRICE!A19"), AsSimpleDF(Range("USD!A1:C535"))
RInterface.InsertCurrentRPlot Range("OPEN_PRICE!D19"), closergraph:=True

End Sub

У данной процедуры есть лишь одно небольшое отличие от предыдущей, а именно вместо

RunRCall вызывается GetRApply.

Отличие данной функции в том, что она может возвращать результат (return (...) не игнорируется), а не только выполнять како-то код. Хотя тем, кто знаком с определение процедуры и функции, данное отличие понятно.

Кроме того, у GetRApply после параметра с именем функции, также необходимо указать диапазон ячеек, куда будут выводиться значения, получившиеся при работе функции.

Плюсы и минусы данного подхода такие же, как и у предыдущего.

Заключение

После выполнения данных VBA процедур, наш тестовый лист выглядит так:



В статье я постарался показать наиболее полезные варианты исполнения кода R из VBA. Кроме того в сжатой форме были описаны достоинства и недостатки всех этих способ. Также надо сказать, что RExcel может работать и без VBA.

Надо отметить, что показаны далеко не все функции интерфейса rinterface, но данный материал даст возможность с минимальными затратами времени освоить и не описанные функции.

Файл с процедурами можно взять у меня на GitHub'е.

This entry passed through the Full-Text RSS service — if this is your content and you're reading it on someone else's site, please read the FAQ at http://ift.tt/jcXqJW.