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

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

Выбираем в верхнем меню

Insert — Charts — Insert Waterfall, Funnels, Stock, Surface, or Radar Chart — Stock — Open-High-Low-Close

Вставка — Вставить каскадную, воронкообразную, биржевую, поверхностную или лепестковую диаграмму — Биржевая — Биржевая (курс открытия, самый высокий курс, самый низкий курс, курс закрытия).

Построение свечного графика

Отключаем легенду. Вводим заголовок диаграммы.

Свечной график

Можно рассмотреть поведение графика за текущий год. Установим масштаб по горизонтальной и вертикальной осям, как показано на рисунке.

Масштаб

На графике видны регулярные перерывы. Возможная причина — отсутствие торгов по выходным дням. Но этот момент мы разберём чуть позже.

График за 2019 год

Затем получим котировки обыкновенных акций Сбербанка. Выбираем категорию МосБиржа акции или Мосбиржа топ. Затем выбираем инструмент Сбербанк.

Экспорт котировок Сбербанка

Для дальнейшей работы скопируем оба массива данных на новый лист — Индекс Мосбиржи и обыкновенные акции Сбербанка.

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

Создаём новый столбец:

Шаг

 — число дней между соседними строками.

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

Наши расчёты основаны на том, что Excel хранит дату как ПОРЯДКОВЫЙ НОМЕР ДНЯ, начиная с 1 января 1900 года. При выводе на экран применяется выбранный способ отображения даты, в нашем случае это ГГГГ-ММ-ДД. Мы можем изменить формат вывода, но содержимое ячейки при этом не меняется. Это по-прежнему порядковый номер дня. Когда мы находим разность соседних дат, мы фактически вычитаем одно целое число из другого. Результат — число дней между датами.

Посмотрим на календарь. Вызываем приложение 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 года, а в котировках акций Сбербанка эта строка отсутствует. Можно предположить, что в этот праздничный день торгов, скорее всего, не было. Получается, что в наших данных разница на один день. На этот сдвиг наложилась разница соседних дат.

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

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

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