Показатели. Формулы

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

Скопируем столбец исходной выборки и вставим его на новый лист. При вставке из буфера выбираем вариант

Values

Значения

Это позволит работать с числами и не использовать формулы, которые ссылаются на данные другого листа.

Вставка значений из буфера

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

Вычисление суммы можно вызвать, выделив столбец чисел и нажав кнопку экспресс-анализа, как мы уже проделали в предыдущих разделах.

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

ABS

Используем найденные суммы и определяем значения показателей.

Вычисление показателей

Для вычисления относительных коэффициентов вариации и осцилляции можно не умножать не 100%, а установить формат вывода в процентах. В этом случае на экране появится знак процента. Это будет более наглядно.

Format Cells — Number — Percentage

Формат ячеек — Число — Процентный

Сравним значения показателей, полученные разными способами. Для этого скопируем их в буфер обмена и вставим в общую таблицу — как значения.

Сравнение значений показателей

Реальные данные

Мы разобрались с основными приёмами работы на смоделированных, идеальных данных. Теперь попробуем взять в работу реальные данные.

Мы будем использовать данные о котировках — ценах на акции на фондовом рынке. Основные торги на сегодняшний день по большей части происходят на Московской бирже. Адрес в интернет:

MOEX.COM

Есть разные варианты загрузки биржевых данных. Мы будем использовать сайт компании «Финам». Здесь всё достаточно удобно и понятно.

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

Настройка экспорта котировок

Полученный файл имеет расширение названия *.TXT. Содержимое файла можно изучить в приложении

Notepad

Блокнот

Файл с котировками

Загрузим полученный файл в Excel. Выбираем в верхнем меню:

File — Open — Browse

Файл —Открыть — Обзор

В диалоговом окне

Open

Открытие документа

выбираем

Text Files

Текстовые файлы

Открытие текстового файла

В окне Мастера импорта текстовых файлов

Text Import Wizard — Step 1 of 3

Мастер текстов (импорт) — шаг 1 из 3

устанавливаем

My data has headers

Мои данные содержат заголовки

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

Next

Далее

Импорт файла с заголовками

На следующем экране

Step 2 of 4

шаг 2 из 3

убеждаемся, что наши данные разделены на столбцы. При необходимости настраиваем поле

Delimeters

Символом-разделителем является

Уже просматривается будущая таблица с данными. Нажимаем кнопку

Next

Далее

Предварительный просмотр таблицы

На третьем шаги импорта настраиваем формат представления даты. В разделе предварительного просмотра

Data preview

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

щёлкаем по первому столбцу.

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

Column Data Format — Date — YMD

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

В нашем случае формат даты

YMD (Year — Month — Day)

ГМД (Год — Месяц — День)

Формат даты

При необходимости можно установить дополнительную настройку, нажав кнопку

Advanced

Подробнее

В окне

Advanced Text Import Settings

Дополнительная настройка импорта текста

можно выбрать разделитель целой и дробной частей

Decimal separator

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

В нашем случае в роли разделителя выступает точка. После настройки нажимаем ОК и

Finish

Готово

Десятичный разделитель

После импорта данных убедимся в том, что загрузка прошла без ошибок.

Загруженные данные

На что обращаем внимание? Дата в первом столбце распознана и выводится на экран в формате даты. В нашем примере это запись даты в американском стиле MM/DD/YYYY.

Числа распознаны как числа и выводятся на экран как числа. Они прижаты к правому краю ячейки. Десятичный разделитель соответствует настройкам компьютера. В нашем случае разделитель — ТОЧКА, потому что используется англоязычная версия Excel. Для русскоязычной версии разделитель при выводе на экран — ЗАПЯТАЯ.

На рисунке ниже приводится пример выравнивания текста и чисел в англоязычной версии пакета. По умолчанию текст прижат влево, число — вправо. Текстовые заголовки в нашем примере тоже по умолчанию прижаты влево. Число с запятой распознано как текст, число с точкой — как число.

Выравнивание

Мы загрузили данные. Теперь обсудим, что же в этих данных содержится.

DATE — Дата торгов

TIME — Время торгов (начало торгового периода)

OPEN — Цена открытия (на начало периода)

HIGH — Максимальная цена за период

LOW — Минимальная цена за период

CLOSE — Цена закрытия (на конец периода)

VOLUME — Объём торгов (количество ценных бумаг, сменивших владельца за период)

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

В качестве варианта задания будем использовать список десяти 10 акций крупнейших российских компаний Индекса ММВБ10. Новое название: Индекс МосБиржи 10, MOEX10. Выберите акцию, расположенную в списке индекса на месте с номером, соответствующим номеру вашего варианта.

Список акций для расчёта индекса называется Базой расчёта. С последним вариантом списка можно ознакомиться на сайте Мосбиржи.

База расчёта MOEX10

Загрузите дневные котировки выбранной акции за последний год. Проведите анализ распределения и определите основные статистические показатели — как описано выше.

В отчёте опишите использованные данные, чтобы это было понятно для читателя. Используйте три ключевых вопроса — как в названии популярной телепередачи «Что? Где? Когда?». Применительно к нашей работе это означает ответы на следующие вопросы:

— Что это за данные?

— Что это за события или объекты?

— Где эти данные были взяты, с какого адреса?

— Где происходили сами события?

— Когда эти данные были получены?

— Когда происходили сами события?

Как видим, эти три вопроса касаются как реальных данных, так и самих событий, которые эти данные описывают. И то, и другое важно.

Чтобы оценить «масштаб трагедии», представьте себе сообщение про «курс валюты» без указания места событий. Читателю останется только догадываться, где и у кого наблюдался этот самый курс валюты:

— Центральный Банк

— Валютная секция Московской Биржи

— Усть-Колымский кооперативный банк

— ФОРЕКС-кухня без лицензии ЦБ

— Знакомый спекулянт на колхозном рынке

— На каком-то сайте в интернете, точно не помню

К тому же, автор работы через месяц даже сам не вспомнит, ЧТО, ГДЕ и КОГДА он скачивал в два часа ночи между дискотекой и утренними занятиями. Ну а если работу делали втроём-впятером, то и отвечать за работу будет некому.