Добрый день, уважаемые читатели.
Сегодня я хочу показать как можно связать возможности языка 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:
PutDataframeRRunInsertCurrentRPlot
Функция 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 новых функции:
RunRFileAsSimpleDFRunRCall
Первая в данном списке 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'е.
Автор: kuznetsovin
