СОВМЕСТНОЕ ИСПОЛЬЗОВАНИЕ EXCEL И MATLAB 

ДЛЯ РЕШЕНИЯ ФИНАНСОВЫХ ЗАДАЧ

 

Юрий ВАСИЛЮК, Александр ОСТАНИН

Политехникa Бeлocтoцкaя, Институт Информатики

e-mail: aleks@ii.pb.bialystok.pl

 

АННОТАЦИЯ

 

Статья посвящена проблеме обмена данными между средой электронных таблиц MS Excel и ресурсами языка высокого уровня Matlab фирмы MathWorks. Описан интерфейс связи Excel Link, который соединяет рабочие пространства MS Excel и Matlab и позволяет пользователю обращаться к дополнительным библиотекам Matlab Toolboxes при решении сложных финансовых задач, к графическому интерфейсу, а также вызывать программы на языках С, Java, C++ и др. непосредственно из среды  MS Excel.

 

Слова ключевые: интерфейс связи, обмен данными, ячейки электронных таблиц

1. Введение

   Электронные таблицы были вначале предназначены для бухгалтеров, работников банков, бизнесменов. Однако при решении сложных экономических задач таких как: прогнозирование временных трендов, динамичная классификация, анализ и нахождение аномалий в поведении экономических объектов во времени и т. д., возможности Excel оказываются ограниченными. В такой ситуаци пользователи электронных таблиц все более обращают внимание на применение новых, более продвинутых математических пакетов, таких как: Mathematica, МatCAD, Маple, Маtlab, Нейронные сети и т. д. При этом для пользователя важно сохранение входных данных и результатов исчислений в электронных таблицах в традиционном виде. В связи с вышеуказанным, возникает потребность в интерфейсе, который поможет обеспечить доступ к ресурсам среды MATLAB из среды электронных таблиц MS Excel. Такой интерфейс связи (Excel Link) разработан фирмой MathWorks и входят в состав программных продуктов среды MATLAB. Он обеспечивает возможность обмена данными между рабочими областями рассматриваемых систем и позволяет реализовывать команды MATLAB, не покидая среды MS Excel. На рисунке 1 показана схема взаимодействия MS Excel и системы MATLAB.

 

 

 

 

 

 

 

 

 


Рис. 1.  Схема взаимодействия MS Excel и системы MATLAB

    2. Запуск интерфейса связи Excel Link

Данная процедура (рис. 2) осуществляется только один раз:

1.         Вызвать MS Excel.

2.         В меню сервиса (Tools) выбрать подменю настроек (Add-Ins...) и опцию обзора (Browse...).

3.         В MATLAB\Toolbox\Exlink найти дорогу к файлу excllink.xla и выбрать его.

4.         Вернуться в подменю настроек (Add-Ins...) и убедиться, что в списке ресурсов появилась новая добавка „Excel Link 2.3 for use with MATLAB”.

5.                 На панели MS Windows должна появиться кнопка MATLAB Command Window.

 

 

Рис.  2.  Окно подменю настроек

 

На панели электронных таблиц должен появиться новый поясок инструментов startmatlab, putmatrix, getmatrix и evalstring.  После установки Excel Link этот интерфейс будет запускаться автоматически при вызове електронных таблиц MS Excel.

 

 

 

 

 

 


Рис. 3.  Новый поясок инструментов

   Если мы этого не хотим, нужно в любую ячейку рабочего листа вписать поручение =MLAutoStart("no"), что исключит автоматический запуск программы MATLAB при вызове программы MS Excel. Для ручного запуска Excel Link и MATLAB следует выбрать подменю макрос (Macro) и выполнить опцию макросов (Macros...). В поле имени макроса (Macro Name) ввести команду matlabinit (рис. 4) и нажать кнопку выполнить (Run), после чего на панели активных окон MS Window должна появиться кнопка MATLAB Command Window.

 

 

 

Рис. 4.  Окно подменю макроса

 

    Чтобы прервать связь программы MATLAB с MS Excel и завершить работу системы MATLAB, оставаясь в окне MS Excel, необходимо в ячейку рабочего листа MS Excel вписать =MLClose(). Произвести загрузку MS Excel и восстановить связь с MATLAB можно с помощью функции  =MLOpen или команды matlabinit.

 

 

 

3.  Обмен данными между MATLAB и Excel

 

   Осуществление взаимосвязи между MS Excel и MATLAB требует использования всех трёх функций с пояска инструментов: putmatrix (вставь в матрицу), getmatrix (получи из матрицы) и evalstring (вычисли). Использование этих функций представим на примере простой системы линейных уравнений с тремя неизвестными (1):

 

                                                                                                                    (1)

 

что соответствует таблице (рис.5 ) с данными: 

 


 


Рис. 5.  Таблица с данными                                  

                      

   Задачу решения системы линейных уравнений можно представить при помощи матричной записи: имея данные матрицы А и B, нужно найти такую матрицу Y, чтобы А*Y=B или Y*А=B.  MATLAB располагает двумя операторами матричного деления, используемые  в двух указанных случаях:

-          оператор \ с таким свойством, что Y=А\ B решает уравнение А*Y=B,

-          оператор / с таким свойством, что Y=А/ B решает уравнение Y*А=B.

    Условием выполнения деления Y=А\B является наличие равного числа строк в матрицах А и B. Число столбцов результата деления Y равняется числу столбцов матрицы B, а число строк - числу столбцов матрицы А. В случае деления Y=А/B требуется согласование числа столбцов матриц А и B; число строк матрицы Y равна числу строк матрицы B, число же столбцов - числу строк матрицы А. При этом матрица А необязательно должна быть матрицей  квадратной.

    Решение нашего примера разберём на основании следующего алгоритма.

 

 

    4.  Решение линейных задач в MATLAB

 

1. Образовать матрицу  А.

-    для этого  образуем в Excel таблицу с данными как показано на рис. 5.

-  выделим в таблице ячейки с А3 по C5 и нажмем кнопку putmatrix. Это вызовет открытие диалогового окна, показанного на рисунке 6, где нужно вписать имя создаваемой в MATLAB матрицы A. В результате выполнения функции MLPutMatrix в рабочем пространстве программы MATLAB появится матрица A, которая содержит те же данные, что и  электронная таблица (смотри рис.10).

 

 

 

Рис. 6.   Диалоговое окно MS Excel с матрицей A

 

2. Аналогичным способом образyем вторую матрицу ( B ):

- выделить ячейки со свободными членами (D3:D5) и нажать кнопку putmatrix. Это вызовет открытие диалогового окна, показанного на рисунке 7, где нужно вписать имя создаваемой в MATLAB матрицы B. В результате выполнения функции MLPutMatrix в рабочем пространстве программы MATLAB появится матрица B, которая содержит те же данные, что и  электронная таблица (рис.10).

 

 

Рис. 7.  Диалоговое окно MS Excel с матрицей B

 

 

3.      Выполнить вычисления, нажав кнопку evalstring. При этом в открывающемся диалоговым окне (рис.8) нужно вписать функцию матричного деления Y= A\B, что вызовёт исполнение вычислений и образование в MATLAB вектора результата Y.

 

 

 

Рис. 8.  Диалоговое окно MS Excel с матрицей B

 

4.      Осталось нам только переслать результат в Excel и разместить его в соответствующих ячейках. С этой целью следует выделить первую ячейку вектора результатов (F3) и активизировать кнопку getmatrix, что вызовет появление диалогового окна, показанного на рисунке 9, в которое нужно вписать название результата исчислений Y.

 

 

Рис. 9.  Диалоговое окно MS Excel с матрицей Y

 

После выполнения функции MLGetMatrix результат вычислений помещается в область F3:F5 (рис.10):

 

 

Рис. 10.  Содержание рабочего листа Excel с решением, полученным в Matlab

 

Чтобы проверить образованные матрицы, в окне Matlab Command Windows нужно выбрать Workspace Browser, что вызовет открытие диалогового окна, показанного на рисунке 11. Элементы каждой из образованных матриц можно просмотреть, производя двукратное нажатие на ее названии.

 

 

Рис. 11.  Диалоговое окно Workspace в Matlab

 

5.  Обмен текстовой информацией

 

Наряду с массивами, объектами, подлежащими обмену между MATLAB и Excel, могут быть текстовые данные. 

 

1.         Образовать матрицу, например, mounth (месяц), введя её в ячейки электронной таблицы A1:C4 (рис. 12):


 

 


Рис. 12.  Ввод в ячейки текстовой информации

 

2.         Выделить ячейки с A1 по C4 и экспортировать данные в переменную mounth рабочей среды MATLAB при помощи кнопки putmatrix. Это вызовет открытие  диалогового окна, показанного на рисунке 13, где нужно вписать название создаваемой в MATLAB матрицы mounth. В результате выполнения функции MLPutMatrix в рабочей области программы MATLAB появится матрица с именем mounth, которая содержит текстовые данные, аналогично как и в ячейках рабочего листа MS Excel электронной таблицы.

 

 

Рис. 13.  Диалоговое окно MS Excel с матрицей  mounth

 

 

3.  Далее выясним  в MATLAB тип переменной mounth, используя команду  whos:

 

»  whos mounth

Name      Size   Bytes   Class

mounth    4x3    890     cell array

Grand total is 97 elements using 890 bytes

    Последняя строка показывает, что общая сумма состоит из  97 элементов, используя 890 байтов.

    Таким образом, текстовая информация из ячеек MS Excel записывается в массив ячеек (cell array) MATLAB. Экспорт в MATLAB текста только одной ячейки рабочего листа MS Excel приводит к помещению ее содержимого в символьный массив типа char array. Импорт массива ячеек в MS Excel приводит к заполнению прямоугольной области на рабочем листе, размеры которой совпадают с размерами импортируемого массива.

    Обмен данными между приложениями может быть реализован не только при помощи кнопок панели инструментов MS Excel Link, но и с использованием функций, определенных в надстройке MS Excel Link [1,5,17,23].

 

6.  Примеры решения типовых задач

 

6.1.  Задача ценообразования опциона

 

 

Этот режим работы связан с использованием всего трех функций putmatrix, getmatrix и evalstring, отображаемых в виде кнопок интерфейса Ехсel Link на панели электронных таблиц MS Excel. Поясним порядок работы, используя пример Sheet4 из демонстрационного файла MATLAB ExliSamp.xls.

Данный пример связан с задачей ценообразования опциона и решается на ocнове биномиального закона распределения стоимости базисного актива опциона. Использование этого распределения обусловлено тем, что цена актива в течение определенного короткого промежутка времени может изменяться на некоторое малое значение в сторону увеличения или уменьшения. Процесс последовательного вычисления во времени такого случайного изменения цены связан с построением так называемого биномиального дерева.

Данный пример иллюстрирует, как можно, находясь на рабочем листе системы MS Excel, выполнить в диалоговом режиме считывание данных из ячеек электронной таблицы в массив системы МАTLAB, затем вычислить цены и возвратить результаты в виде двух массивов в определенные ячейки электронной таблицы MS Excel [5].

Содержимое рабочего листа Sheet4 показано на рис. 14 и содержит три поименованных диапазона ячеек: B4:B10 с именем bindata, B15 с именем asset_tree и B23 с именем value_tree .

 

 

 

Рис. 14. Содержимое рабочего листа Sheet 4

 

 

    Необходимо произвести считывание данных из ячеек B4:B10 и переслать их в рабочую область МАTLAB как массив b. Для этого следует выделить эти ячейки и нажать кнопку putmatrix на панели MS Ехсеl (рис. 14), что вызовет появление диалогового окна, в котором запрашивается имя переменной в системе МАTLAB. После введения массива b и нажатия кнопки OK выполняется функция MLPutMatrix и в рабочей обасти МАTLAB будет сформирован массив класса double array с именем b, который содержит те же данные, что и ячейки электронной таблицы.

    Далее вычислим функцию binprice, обращение к которой имеет вид:

 

[p, o] = binprice(b(1), b(2), b(3), b(4), b(5), b(6), b(7))                                                              (2)

    С этой целью следует активизировать кнопку evalstring, что вызовет появление диалогового окна, где нужно вписать функцию binprice в виде (2) и нажать кнопку ОК.   В результате выполнения функции MLEvalString в рабочей области среды MATLAB будут сформированы два массива с именами о и p,  которые содержат результаты вычислений.

    Остались нам только переслать эти данные в систему MS Ехсеl и разместить в соответствующих ячейках. Для этого следует назначить начальную ячейку и активизировать кнопку getmatrix, что вызовет появление диалогового окна, в котором запрашивается имя массива. После введения имени p и нажатия кнопки ОК выполняется функция MLGetMatrix и этот массив будет размещен в ячейках B15:G20.

    Повторяя аналогичные действия с массивом о, мы получим следующую итоговую электронную таблицу (рис. 15):

 

Рис. 15.  Содержимое итоговой электронной таблицы

    

      6.2.  Отображение показателей чувствительности портфеля опционов

 

    В даном примере изображается показатель гамма как функция цены и времени для портфеля из 10 опционов, оцениваемых в соответствии с моделью Блэка-Сколса [5]. Рисунок должен изображать трехмерную поверхность. Для каждой точки на этой поверхности высота (значение z) представляет сумму показателей гамма для каждого опциона в портфеле, взвешенном количеством каждого опциона. По оси х откладывается изменяющаяся стоимость, по оси у - время. К рисунку добавляется четвертое измерение путем введения дельты как цвета поверхности. Этот пример включен в М-файл ftgехЗ.m.

   Возьмем сначала портфель с произвольными данными. Пусть текущие цены изменяются от $20 до $90 для каждого опциона. Установим соответствующие цены исполнения для каждого опциона:

 

Range = 20:90;

PLen = length(Range);

ExPrice = [75  70  50  55  75  50  40  75  60  35];

 

    Пусть все  безрисковые  процентные  ставки  равны  10%  и пусть интервалы  до срока исполнения измеряются днями. Установим значения для всех волатильностей равными 0.35. Зададим для каждого инструмента, входящего в портфель, число опционов и выделим пространство для массивов:

 

Rate = 0.1*ones(10,1);

Time = [36  36  36  27  18  18  18  9  9  9];

Sigma = 0.35*ones(10,1);

NumOpt = 1000*[4  8  3  5  5.5  2  4.8  3  4.8  2.5];

ZVal = zeros(36, PLen);

Color = zeros(36, Plen);

 

    Для каждого инструмента мы создадим матрицу цен (размера time*plen) для каждого периода:

 

for i = 1:10

Pad = ones(Time(i),PLen);

NewR = Range(ones(Time(i),1),:);

 

    Создадим вектор периодов времени от 1 до time и массив временных интервалов, по одному столбцу для каждой цены:

T = (1:Time(i))';

NewT = T(:,ones(PLen,1));

 

    Для вычисления гаммы и дельты вызовем соответствующие функции чувствительности пакета gamma и delta

ZVal(36-Time(i)+1:36,:) = ZVal(36-Time(i)+1:36,:) ...

+ NumOpt(i) * blsgamma(NewR, ExPrice(i)*Pad, ...

Rate(i)*Pad, NewT/36, Sigma(i)*Pad);

Color(36-Time(i)+1:36,:) = Color(36-Time(i)+1:36,:) ...

+ NumOpt(i) * blsdelta(NewR, ExPrice(i)*Pad, ...

Rate(i)*Pad, NewT/36, Sigma(i)*Pad);

end

 

    Далее нарисуем поверхность как сетчатую, зададим точку наблюдения и реверсируем ось х относительно точки наблюдения. Интервалы по осям изменяются соответственно от 20 до 90, от О до 36 и от - ∞ до + ∞.

 

 

mesh(Range, 1:36, ZVal, Color);

view(60,60);

     set(gca, 'xdir','reverse');

     axis([20  90  0  36  -inf  inf]);

 

    Добавим заголовки, обозначения осей и нарисуем 'box', 'on'. Аннотируем цвета штрихами, а метки - цветными штрихами. Цветной монитор показывает все детали, а данная черно-белая репродукция является лишь некоторым приближением. Полученное изображение представлено на рис. 16

 

title('Показатели чувствительности опциона '”call”');

xlabel('Цена акций ($)');

ylabel('Время (месяцы)');

zlabel('Гамма');

set(gca, 'box', 'on');

colorbar('horiz');

a = findobj(gcf, 'type', 'axes');

set(get(a(2), 'xlabel'), 'string', 'Delta');

 

 

 

 


 


Рис. 16.  Показатель чувствительности гамма как функция цены и времени

 

 

 

 

 

 

 

Литература

 

1.      Ануфриев И.Е., Смирнов А.Б., Смирнова Е.Н.: MATLAB 7., БХВ, Петербург, 2005

2.      Васильев А.Н.: Научные вычисления в Microsoft Excel. М., Изд. Вильямс, 2004

3.      Елисеева И.И.: Эконометрика. М., Финансы и статистика, 2001

4.      Курицкий Б.: Поиск оптимальных решений средствами Excel 7.0., BHV, Петербург, 1997

5.      Лавров К.Н., Цыплякова Т.П.: Финансовая аналитика. М., Диалог, МИФИ, 2001

6.      Мур Д., Уэдерфорд Л.Р. и др.: Экономическое моделирование в Microsoft Excel, 6-е изд., М., Изд. Вильямс, 2004

7.      Рей К.И.: Рынок облигаций. Торговля и управление рисками. М., Дело, 1999

8.      Хазанова Л.Э.: Математическое моделирование в экономике. М., БЕК, 1998

9.      Цисарь И., Нейман В.: Компьютерное моделирование экономики., М., Диалог, МИФИ, 2002

10.  Шведов А.С.: Теория эффективных портфелей ценных бумаг. М., ГУВШЭ, 1999

11.  Bodie Zvi, Kane A., Markus A.J.: Investments, 2nd ed. Burr Ridge III., Irwin., 1993

12.  Kolberg M.: Excel w firmie. Przykłady zastosowań, Wydawnictwo Read Me, Łódź, 2001

13.  Liengme B.V.: Guide to Microsoft Excel for Scientists and Engineers, Elsevier Sci. Ltd., 1999

14.  Markowitz H.M.: The Optimization of the Quadratic Function Subject to Linear Constaints. // Naval Reseach Logistic Quarterly. V. 3, 1956

15.  Marshall J.F., Vipul K.B.: Financial Engineering: A Complete Guide to Financial Innovation, New York Institute of Finance, N.Y., 1992

16.  Middleton M.R.: Microsoft Excel w analizie danych, Read Me, Warszawa, 2003

17.  Ostanin A.: Optymalizacja liniowa i nieliniowa, Wydawnictwo Politechniki Białostockiej, Białystok, 2005

18.  Penny J., Lindfield G.: Numerical Methods using Matlab, Prentice Hall, N.J., 1999

19.  Sharpe W.F., Gordon J.A.: Investments, 4th ed., Englewood Cliffs, N.J., Prentice Hall, 1990

20.  Sharpe W.F.: Macro-Investment Analysis. An “electronic work-on-progress” published on the World Wide Web, 1995,  at http://www.stanford.edu/~wfsharpe/mia/mia.htm

21.  Wisniewski M., Dacre T.: Mathematical Programming. Optimization Models for Business and Management Decision Making, McGraw-Hill Book Company, London, 1990

22.  http://www.mathworks.com – адрес фирмы  MATLAB

23.  USER’S GUIDE Excel LINK for Use with MATLAB - MathWorks, 2004

24.  Optimization Toolbox For Use with MATLAB, User's Guide, MathWorks, 2004

25.  Financial Toolbox User’s Guide. The Math Works, Inc. 2000-2008

26.  GARCH Toolbox User’s Guide. The Math Works, Inc. 1999-2008