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

Посмотрим на календарь. Вызываем приложение Calendar. Выбираем 2009 год. Видим, что 17 и 18 января 2009 года — это выходные дни. А по выходным на бирже торгов нет. Так что между соседними рабочими днями будет шаг 1 день, а пятницей и понедельником — 3 дня.

Выходные дни

Чтобы увидеть порядковый номер дня, выделим ячейку с датой и выберем в контекстном меню

Format Cells — Number — Category — General

Формат ячеек — Числовые форматы — Число — Общий.

При выборе формата Generalмы увидим исходное содержимое ячейки. Наше число показано в разделе

Sample

Образец

диалогового окна

Format Cells

Формат ячеек.

Выясняется, что дата 11 января 2009 годана самом деле хранится как число 39824. То есть это 39824-й день от начала отсчёта (1 января 1900 года). При этом в строке формул та же самая дата выводится как 1/11/2009. Это американский формат MM/DD/YYYY. Сначала месяц, потом день и в конце — год.

Порядковый номер дня

Построим диаграмму разброса «Шаг по времени в зависимости от номера записи». Большинство значений равны 1 и 3. Но есть и долгие перерывы.

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

Подводим курсор к самой высокой точке и видим, что это пара чисел (497, 12). На 497 строке нашего массива данных разность дат составила 12 дней. Это новогодние выходные с 31 декабря 2010 года по 10 января 2011 года — см. рисунок.

Новогодние выходные

Сравним данные по индексу и выбранной ценной бумаге. Найдём разность дат наших двух массивов. Построим график разностей. Разность в пять дней обнаруживается в апреле 2016 года. Сравним эти даты.

Расхождение дат

При внимательном рассмотрении таблицы выясняем, что в данных о биржевом индексе есть данные за 23 февраля 2016 года, а в котировках акций Сбербанка эта строка отсутствует. Можно предположить, что в этот праздничный день торгов, скорее всего, не было. Получается, что в наших данных разница на один день. На этот сдвиг наложилась разница соседних дат.

Расхождение дат

Загрузите данные в соответствии со своим вариантом и проведите анализ массива. Какой шаг по времени в каждом массиве по отдельности? Есть ли разница дат в двух массивах? Зафиксируйте выводы на текущей странице отчёта.

Для анализа связи нам нужны пары значений, относящиеся к одним моментам времени. Должна быть синхронность. Качество данных нужно проверять. Как гласит народная мудрость, ДОВЕРЯЙ, НО ПРОВЕРЯЙ.

Проверим другие источники информации.

На сайте Московской Биржи есть возможность ознакомиться с историческими данными. Далее можно сравнить информацию биржи с данными из других источников — для контроля качества.

Для просмотра значений индекса Мосбиржи перейдём по ссылкам:

www.moex.com — Котировки — Индексы — MOEX Russia Index — Архив значений.

Выбираем настройки

Архив значений по дням

с 20.02.2016 по 26.02.2016.

Нажимаем кнопку

Показать.

Устанавливаем сортировку даты по возрастанию. Можно видеть, что данные за 23 февраля 2016 года отсутствуют.

Значения индекса

Сравните значения индекса IMOEX за 22 и 24.02.2016 на сайтах Финам и Мосбиржи.

Установите диапазон дат с 01.01.2009 по 31.08.2019.

Нажмите ссылку

Скачать данные в формате — CSV.

Изучите формат полученного файла в программе

Notepad

Блокнот.

Обратите внимание на диапазон дат в файле.

Загрузите данные в Excel.

Для просмотра котировок обыкновенных акций Сбербанка перейдём по ссылкам:

www.moex.com — Котировки — Акции — Сбербанк —Итоги торгов — Инструмент — Сбербанк SBER Сбербанк России ПАО ао.

SBER — кодовое обзначение финансового инструмента;

ПАО — форма организации, сокращение от Публичное акционерное общество;

ао — акции обыкновенные.

Устанавливаем даты:

Началои Окончание.

Нажимаем кнопку

Обновить.

Проверим данные с 20 по 26 февраля 2016 года. Есть данные за 22 и 24 февраля. Между ними праздничный день 23 февраля.

Курс акций Сбербанка

Сравним данные, полученные на сайте компании «Финам» и на сайте Московской биржи — см. рисунок. Зелёным выделим совпадающие значения индекса, красным — отличающиеся. Появилась лишняя строка за 23 февраля 2016 года, заполненная значением закрытия предыдущего дня. Возможно, на момент эксперимента с экспортом котировок произошёл сбой на сервере. Делаем вывод на будущее: при получении данных из интернет следует контролировать качество и выборочно сверяться с оригиналом, то есть с сайтом Мосбиржи.