Читать «Как предсказать курс доллара. Расчеты в Excel для снижения риска проигрыша» онлайн - страница 12

Посмотрим, как можно найти линейную форму зависимости переменной Y от независимой переменной X, которую можно найти, решив однофакторное линейное уравнение регрессии.

Алгоритм № 6. «Как решить уравнение регрессии в Excel»

Шаг 1. В файле Excel в ячейках $A$1:$C$110 размещается таблица. Во-первых, с данными по курсу доллара к рублю (в ячейках $ C$1:$C$110) за период 27.07.2014 г. по 28.11.2014 г. В этот период, как мы выяснили в главе 1, наблюдался тренд с высоким коэффициентом детерминации. Во-вторых, с датами торгов (в ячейках $A$1:$A$110) и порядковыми номерами торговых дней (в ячейках $B$1:$B$110), начиная с 27 июня 2014 года = 1 – см. таблицу 2.1. Причем, даты торгов представлены в таблице 2.1 только для справки, чтобы мы могли их соотнести с порядковыми номерами торговых дней.

Таблица 2.1 Исходные данные по курсу доллара к рублю и порядковыми номерами торговых дней

Источник: расчеты автора и данные Банка России

Шаг 2. Сначала в Microsoft Excel 2007 года в верхней панели инструментов выбирается опция Данные (в Microsoft Excel 1997-2003 года нужно выбрать опцию Сервис), потом в появившемся диалоговом окне Анализ данных ‑ опцию Регрессия. После этого появляется новое диалоговое окно ‑ Регрессия (См. ‑ рис. 2.1), в котором в графе Входной интервал y выделяем с помощью мышки столбец данных «Курс доллара к рублю» (ячейки $C$1:$C$110).

Здесь же в графе Входной интервал Х» выделяем столбец данных «Порядковый номер торгового дня» (ячейки $B$1:$B$110). Таким образом курс доллара к рублю используется в этом уравнении регрессии как зависимая переменная y, а порядковые номера торговых дней ‑ как независимая переменная Х.

Шаг 3. Если бы мы хотели получить уравнение регрессии без свободного члена (то есть приравняли бы его нулю), который в формуле линейного тренда: Y=AX+С обозначен символом С и обозначает в уравнении исходный уровень тренда или точку его пересечения с осью Y на графике, то тогда нам следовало бы выбрать еще и опцию КОНСТАНТА-НОЛЬ. В этом случае начальной точкой пересечения графика линейного тренда с осью Y будет ноль. Однако необходимость в использовании этой опции обычно возникает в том случае, когда после решения уравнения регрессии со свободным членом выясняется, что исходный уровень тренда С является статистически незначимым.

Замечу также, что при решении уравнения регрессии количество наблюдений всегда должно быть в 6-7 раз больше числа включенных в него независимых переменных. В данном случае у нас в уравнении регрессии независимая переменная X только одна. Следовательно, как минимум нужны данные по торгам за 6-7 торговых дней, но по факту у нас учтены данные по 109 наблюдениям, то есть это требование в этом случае с избытком «перевыполнено».