Как предсказать курс доллара. Расчеты в Excel для снижения риска проигрыша

Посмотрим, как можно найти линейную форму зависимости переменной 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 наблюдениям, то есть это требование в этом случае с избытком «перевыполнено».

Шаг 4. Далее выбираем опцию Остатки, поскольку она нам нужна, чтобы в выходных данных содержалась информация об отклонении расчетных (вычисленных по уравнению регрессии) значений курса доллара Yрасчет от его фактического курса y. При этом остаток для каждого наблюдения (торгового дня) находится путем вычитания из фактического курса доллара его расчетного значения на этот торговый день.

Шаг 5. Опцию МЕТКИ применяют для того, чтобы переменные, включенные в уравнение регрессии, в выводе итогов были обозначены в виде заголовков соответствующих столбцов.

Шаг 6. По умолчанию оценка в Excel параметров уравнения регрессии делается с 95% уровнем надежности. Но в случае необходимости в опции Уровень надежности можно поставить цифру 99, что означает задание для программы оценить коэффициенты регрессии с 99% уровнем надежности. В результате в выводе итогов мы получим данные, характеризующие как в целом уравнение регрессии, так и верхние и нижние интервальные оценки коэффициентов данного уравнения с 95% и 99 % уровнями надежности. При 95% уровне надежности существует риск, что в 5 % случаях оценки коэффициентов уравнения регрессии могут оказаться неточными, а при 99% уровне надежности этот риск равен 1%.

Шаг 7. Вывод итогов. На заключительном этапе выбираем в параметрах вывода (окно РЕГРЕСССИЯ) опцию выходной интервал, в которой указываем соответствующую ячейку Excel ($H$1), далее щелкаем по надписи ОК и получаем ВЫВОД ИТОГОВ (см. рис 2.1, где можно увидеть все заданные нами параметры уравнения регрессии). В случае необходимости вывод итогов можно получить на отдельном листе (см. опцию НОВЫЙ РАБОЧИЙ ЛИСТ) или в новой книге Excel (см. опцию НОВАЯ РАБОЧАЯ КНИГА).

В окончательном виде заполненное нами диалоговое окно РЕГРЕСССИЯ приобретет следующий вид – см. рис. 2.1. После чего щелкаем левой кнопкой мышкой по надписи ОК в этом окне и получаем вывод данных.

Рис. 2.1

2.2. Вывод итогов и оценка параметров уравнения регрессии