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

Text Import Wizard — Step 3 of 3

Мастер импорта — шаг 3

в разделе

Data preview

Образец разбора данных.

По умолчанию для всех столбцов установлен общий формат

Column data format — General

Формат данных столбца — Общий.

Можно видеть, что столбец рассматривается как целое число. Мы догадываемся, что в этом столбце записана дата в формате «год-месяц-день». Нужно сообщить об этом мастеру импорта.

Общий формат

Настроим формат даты. Выбираем столбец и устанавливаем формат записи даты:

Column data format — Date — YMD

Формат данных столбца — дата — ГМД.

Формат даты

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

Advanced

Подробнее.

Убеждаемся, что в качестве разделителя целой и дробной частей

Decimal separator

Разделитель целой и дробной части

установлена точка — как и в формате импортируемого файла.

Нажимаем кнопки ОК и

Finish

Готово.

Разделитель — точка

Файл загружен в Excel. Убеждаемся, что импорт прошёл успешно. Дата распознана как дата — это видно в самих ячейках и в строке формул. Числа с дробной частью распознаны как числа. Напомним, что числа по умолчанию выравниваются по правому краю ячейки, а такст прижимается влево.

Результаты импорта

Копируем страницу значений индекса в отчёт. Для выделения заполненных ячеек используем комбинацию клавиш Ctrl + Shift + Upи Ctrl + Shift + Down. Стрелки вверх и вниз позволяют не только перемещаться на одну строку, но и перейти в начало и конец заполненныго диапазона ячеек. Удаляем неинформативные столбцы — тикер, период, время. Делаем заголовок.

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

Настроим формат вывода даты. Выделяем столбец дат. В контекстном меню выбираем

Format Cells — Number — Category — Date — Locale (location) — Russian

Формат ячеек — Число — Числовые форматы — Дата — Язык (местоположение) — русский.

Пример отображения даты в выбранном формате можно сразу же наблюдать в разделе

Sample

Образец.

Формат даты

Теперь даты выглядят более привычно: год-месяц-день. При этом в строке формул дата по-прежнему выводится в американском формате месяц/день/год — в английской версии пакета.

Формат даты

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

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

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

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

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