Статистический анализ взаимосвязи в Excel

Здесь отсутствует цена закрытия Close, зато есть цена под названием Price, то есть Цена. Судя по всему, это и есть цена закрытия. Дело в том, что при анализе котировок самой важной считается именно цена закрытия. В некоторых случаях остальные значения практически игнорируют.

Переименуем заголовок Price и введём информативное название Close. Расставим столбцы в правильном порядке для построения графиков:

Date — Open — High — Low — Close.

Теперь проверим работу русской версии сайта. Для переключения языка щёлкнем по изображению флага в правом верхнем углу страницы. Выбираем в списке языков:

Русский.

Выбор языка

Обращем внимание на адрес страницы в браузере:

https://ru.investing.com/indices/mcx-historical-data.

В начале адреса появились буквы RU вместо WWW. В остальном всё без изменений.

Загружаем те же данные. Рассмотрим содержимое файла

Прошлые данные — Индекс МосБиржи. csv.

Все поля в кавычках. Десятичный разделитель — запятая. Разделитель полей — точка. Закрываем Блокнот.

Формат файла на русском

Открываем файл *.CSV в английской версии Excel. Судя по выравниванию содержимого ячеек, данные загружены как текст, а не как даты и числа. Текст выровнен по левой стороне. Даты тоже распознаны как текст.

Загрузка файла на русском

Проверьте, как загружаются в русскую версию Excel файлы, полученные через английскую и русскую версии сайта.

Переключаемся на английский язык и загружаем данные по котировкам акций Сбербанка — код бумаги: SBER. Выбираем тот же диапазон дат.

Проведём анализ шага по времени, как в предыдущих примерах.

Шаг по времени

Здесь тоже можно обнаружить расхождения по датам. Для анализа связи придётся пожертвовать частью данных. Удаляем строки, которые выпадают из общей картины.

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

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

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

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

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

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

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

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

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

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

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

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

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

Change Chart Type

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

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

Combo

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

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

Scatter with Straight Lines

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

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

Secondary Axis

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

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

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

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

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

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

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

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

Вызываем функцию CORREL и находим значение коэффициента линейной корреляции: r

= 0.924. Это значение говорит о тесной положительной линейной связи между котировками акций и значением индекса.

Благодарности

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Послесловие