Валентин Арьков - Статистический анализ взаимосвязи в Excel

Добиваемся нулевого расхождения по датам в обоих наборах данных — см. рисунок.

Нулевое расхождение дат

Данные «очищены», то есть приведены в некоторый порядок. Переходим к анализу связи между курсом акций и значениями индекса.

Копируем лист с биржевыми данными в отчёт. Для этого щёлкаем правой кнопкой мыши по вкладке листа и выбираем в контекстном меню Move or Copy.

Копирование листа

Вставим лист из текущей рабочей книги в другую книгу — а наш отчёт, последним листом. Переименуем лист для единообразия.

Копирование листа в другую книгу

Копируем даты и цену закрытия для обоих инструментов на новый лист.

Построим совмещённый график изменения во времени.

Совмещённый график

Настроим раздельный масштаб, чтобы лучше рассмотреть оба графика.

Изменение типа графика

Открывается диалоговое окно

Change Chart Type

Изменить тип диаграммы.

Выбираем комбинированный тип графика:

Combo

Комбинированная.

Устанавливаем для обоих графиков

Scatter with Straight Lines

Точечная с прямыми отрезками.

Включаем вторую вертикальную ось:

Secondary Axis

Вспомогательная ось.

Комбинированный график

Настраиваем диапазон значений по каждой оси. Устанавливаем заголовки по осям. Получаем более-менее информативную диаграмму — см. рисунок.

Комбинированная диаграмма

Можно видеть, что в целом направление изменения совпадает, хотя иногда котировки акций отклоняются от движения индекса.

Построим диаграмму разброса. Настраиваем заголовки, маркер, линию. Включаем построение линейной регрессии и вывод уравнения на экран.

Диаграмма разброса

Подобный анализ проводится при построении инвестиционного портфеля. Прочитайте статью Бета-коэффициентв Википедии.

Вызываем функцию CORRELи находим значение коэффициента линейной корреляции: r = 0.924. Это значение говорит о тесной положительной линейной связи между котировками акций и значением индекса.

Автор выражает глубокую признательность студентам, участвовавшим в подготовке данных методических указаний. Следующие студенты активно помогали в постановке лабораторных работ и составлении чернового варианта методических указаний:

— Корнеева Мария

— Кострюкова Анна

— Токарева Татьяна

— Муслимов Роберт

В тестировании учебных материалов, выявлении упущенных моментов и оценке трудоёмкости работ участвовали следующие студенты:

— Яковлева Виктория

— Исхаков Радмир

— Халиков Ильшат

— Мурадян Гарик

— Мухаметьянова Ольга

— Хайретдинова Ирина

— Макаров Владислав

— Макрушин Константин

— Портнов Владислав

В рамках проекта тестировщики общались с составителями, и после каждого очередного исправления текст становился всё более понятным.

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

Кстати, в процессе работы попутно выяснилось, насколько большой поток информации требуется обрабатывать для текущей координации проекта. Так что в следующий раз нам дополнительно потребуется куратор, который освоит управление проектом.

В данном выпуске мы познакомились с анализов взаимосвязи методами корреляционного и регрессионного анализа. Это второй выпуск в серии «Бизнес-аналитика и статистика в Excel». В нашей серии учебных пособий имеются следующие выпуски:

1. Анализ распределения. Сводка и группировка данных. Оценка формы распределения. Вычисление статистических показателей. Соответствующие разделы предмета: «Введение», «Распределение», «Статистические показатели», «Сводка и группировка», «Статистические графики». В конечном счёте мы построим графики (гистограмму и кумуляту), а также оценим значения показателей — таких как «сигма».

2. Корреляционный и регрессионный анализ. Статистическое изучение взаимосвязи. Соответствующий раздел предмета: «Взаимосвязь явлений», «Фондовые индексы». Мы узнаем, что прямую линию можно провести через любое количество точек, если делать это «в среднем». А ещё нам предстоит выяснить, как связаны между собой различные отрасли экономики — если посмотреть на них глазами Московской биржи.

3. Анализ динамики. Компоненты рядов динамики. Скользящие средние. Уравнение и линия тренда. Соответствующий раздел предмета: «Динамика». Здесь мы рассмотрим составные части динамики (изменения во времени) и биржевые графики, на которых наносят общую тенденцию (тренд).