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

Delimiters — Semicolon

Символом-разделителем является — точка с запятой.

Разделитель — точка с запятой

Убеждаемся, что в разделе предварительного просмотра появились столбцы.

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

Next

Далее.

Выбранный формат данных для каждого столбца выводится в нижней части окна

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