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

Цена диска т.р. = 4,272 +1,558 • Объём диска Гб.

По уравнению регрессии строим вспомогательную табличку из двух точек.

Попутно найдём ЛИНЕЙНЫЙ ПРОГНОЗ. Это наш прогноз значения Y для выбранного значения X по линейному уравнению регрессии. В исходных данных нет диска на 4 Гб. Судя по нашему уравнению, он должен стоить примерно 10,5 тыс. руб.

Вспомогательная таблица

Наносим линию регрессии на диаграмму разброса. Точки почти лежат на прямой линии.

Линия регрессии

Рассмотрим ещё один популярный момент — «Цена за гигабайт». Во сколько обходится хранение данных на дисках разного размера? Просто поделим рубли на гигабайты. С увеличением объёма диска стоимость хранения данных падает. Так что для большого сервера могут оказаться более экономичными большие диски.

Стоимость хранения данных

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

Для начала ознакомимся с заданием. Создадим новый лист в рабочей книге Excel и опишем свой вариант задания.

В интернете есть много ресурсов, где можно бесплатно загрузить биржевые котировки — так называемые ИСТОРИЧЕСКИЕ ДАННЫЕ. Адреса могут со временем изменяться. Работа сайтов может быть нестабильной. Могут появляться новые источники данных.

В качестве примера рассмотрим три источника данных:

MOEX.RU

FINAM.RU

INVESTING.COM

В данной работе нас будет интересовать качество полученных данных и удобство их загрузки в Excel для дальнейшей обработки.

Откроем сайт компании «Финам» www.finam.ruв браузере. Перейдём по ссылке Теханализ.

Сайт «Финам»

На открывшейся странице переходим по ссылке Экспорт котировок.

Ссылка на страницу экспорта

Выбираем Российские индексыв выпадающем списке.

Выбор индексов

В выпадающем списке инструментов выбираем IMOEX.

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

Установим следующие настройки экспорта данных:

— интервал: диапазон дат 10 лет

— периодичность: 1 день

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

— разделитель разрядов: нет

— формат записи: TICKER, PER, DATE, TIME, OPEN, HIGH, LOW, CLOSE, VOL

— добавить заголовок файла: да

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

Нажимаем кнопку Получить файл. Сохраняем файл на диске и обращаем внимание, куда именно его сохранили. Ведь этот файл нам предстоит загружать в Excel.

Для начала ознакомимся с форматом файла. Откроем загруженный файл в текстовом редакторе Notepad( Блокнот). В первой строке находятся заголовки столбцов. Поля разделены точками с запятой. Целая и дробная часть числа разделены точкой. Напомним, что в английской версии Excel в качестве разделителя используется ТОЧКА, в русской версии — ЗАПЯТАЯ. Закрываем окно редактора.

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

Переходим в окно Excel и выбираем в меню

File — Open — Browse — Text Files

Файл — Открыть — Обзор — Текстовые файлы.

Загрузка текстового файла

Выбираем файл и нажимаем кнопку

Open

Открыть.

В окне

Text Import Wizard — Step 1 of 3

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

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

My data has headers

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

Убеждаемся, что в разделе

Original data type

Формат исходных данных

указан формат данных

Delimited

( с разделителями).

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

Next

Далее.

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

Результат импорта можно контролировать в нижней части диалогового окна мастера импорта

Preview of file

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

На следующих шагах импорта эта часть окна будет называться

Data preview

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

По умолчанию в качестве разделителя полей установлен символ табуляции

Delimiters — Tab

Символом-разделителем является — табуляция.

На этом шаге программа не разделяет поля и рассматривает содержимое каждой строки как одну ячейку.

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

Установим точку с запятой в качестве разделителя: