Подготовка данных и предварительные вычисления

Исходные данные, используемые в настоящей задаче, являются открытыми, и их можно получить на любой фондовой торговой площадке или агрегаторе. В данном случае в качестве такового будет использована finance.yahoo.com. Компании должны выбираться из отраслевых списков, доступ к которым имеется с головной страницы в разделе Industries (см. рис. 17).

Рис. 17. Сайт finance.yahoo.com, список компаний в разделе Industries и группе Industrial Goods.

В качестве примера выбраны 5 компаний (в скобках указывается биржевой тикер): Apple (AAPL), Microsoft (MSFT), ExxonMobil (XOM), General Electric (GE), Amazon (AMZN). Данные о стоимости акций компании доступны в разделе «Historical data» профиля компании (см. рис. 18).

Рис. 18. В разделе Historical data профиля компании перед выгрузкой необходимо установить периодичность данных и длительность ряда.

Данные выгружаются в файл *.csv (специальный текстовый формат файла), который может быть открыт напрямую в Excel. Однако при этом все данные за один период загружаются склеенными в одной строке, поэтому перед использованием их следует разбить по ячейкам с помощью сервиса «Текст по столбцам». Предварительно следует заменить во всём массиве данных разделитель столбцов с «,» на «;», а десятичные «.» на «,». Из всего полученного массива нам потребуется только столбец «Close» цен закрытия, который и следует перенести в основную рабочую таблицу. Результат представлен на рис. 19 слева. В качестве примера использованы более короткие помесячные ряды.

Рис. 19. Динамика цен активов (слева) и темпов прироста (справа), фрагмент массива данных.

Однако для решения задачи требуются не цены активов, а их изменение, поэтому на основании этих данных требуется рассчитать темпы прироста  (рис. 19 справа). Средний темп прироста актива i будем использовать как оценку доходности r _i, а среднеквадратическое отклонение – как оценку риска σ_i. На рис. 20 показаны результаты расчёта, где для вычисления риска использована функция СРЗНАЧ, а риска – СТАНДОТКЛОН. Доли активов в портфеле изначально можно задать произвольно, они будут вычислены позже.

Рис. 20. Расчёт доходности и риска активов по компаниям и всего инвестиционного портфеля.

Для расчёта риска портфеля ещё требуется нам также потребуется корреляционная матрица, элементы которой можно вычислить с помощью функции КОРРЕЛ. Сложность заключается в том, что нам требуются все коэффициенты корреляции между темпами прироста цен активов всех компаний, т.е. между AAPL и MSFT, AAPL и XOM, MSFT и XOM и т.д. Задавать диапазоны для вычисления значения функции вручную каждый раз затруднительно, поэтому применим для этого следующий приём: используем для выбора диапазона функцию СМЕЩ[3]. Предварительно нам потребуется пронумеровать строки и столбцы, начиная с 0 (см. рис. 21). Каждый элемент матрицы вычисляется по формуле

=КОРРЕЛ(СМЕЩ($H$3:$H$25;0;H$27);СМЕЩ($H$3:$H$25;0;$G28))

Здесь диапазон $H$3:$H$25 ссылается на темпы приростов первой компании (в нашем случае – AAPL). Ссылка H$27, указывая на заданные ранее номера столбцов корреляционной матрицы, вычисляет, на сколько столбцов сместиться от неё. В данном случае это 0, но при копировании формулы вправо она в дальнейшем задаст смещение на 1, 2 и т.д. аналогично сработает и ссылка $G28, но уже для элементов-строк корреляционной матрицы.

Рис. 21. Результат расчёта корреляционной матрицы. Синим выделены результаты вычисления произведения матрицы на вектор рисков и весов в исходном (столбец) и транспонированном (строка) виде.

Теперь мы готовы к вычислению характеристик портфеля. Доходность вычисляется как средневзвешенная доходность компаний (скалярное произведение доходности компаний и их долей в портфеле , см. рис. 20, нижняя часть). Гораздо сложнее с риском в силу того, что средневзвешенный риск вычисляется по правилам сложения дисперсий. В матричном виде эта формула записывается как , а в развёрнутом:

где ρ_ij – элементы корреляционной матрицы V.

Во избежание дополнительных сложностей проделаем вычисления в два этапа:

· сначала вычислим  как скалярное произведение вектора риска, долей и каждой строки матрицы V, результат см. на рис. 21 в выделенном столбце справа;

· затем протранспанируем результат (рис. 21, выделенная строка внизу) и домножим на , см. рис. 20, нижняя часть.