Помоги проекту - поделись книгой:
Шаг 6. Для того чтобы построить новый, хотя и более короткий тренд, но с высоким коэффициентом детерминации необходимо выделить мышкой столбец с ежедневными данными за период с 27 июня по 28 ноября 2014 года. Столбец с датами при этом не выделяем.
Шаг 7. Воспользуемся шагами 1-3 алгоритма № 4 «Построение графическим способом линейного тренда в Excel» для построения графика курса доллара к рублю за период с 27 июня по 28 ноября 2014 года. При этом на оси Y установим минимальное значение по курсу доллара на уровне 33 рублей – см. 1-2 шаги алгоритма № 3 «Оптимизация масштаба графика в Excel с учетом анализируемых данных». В результате у нас получился следующий график ‑ см. рис. 1.15.
Источник: Банк России
Рис. 1.15.
Шаг 8. Для построения линейного тренда в Excel по графику курса доллара к рублю за период с 27 июня по 28 ноября 2014 года в зависимости от номера торгового дня (начиная 27.06.2014 г. =1 и до 28.11.2014. г.=109) нужно воспользоваться 4-5 шагами алгоритма № 4 «Построение графическим способом линейного тренда в Excel». В результате у нас появится следующий график с линейным трендом ‑ см. рис. 1.16.
Источник: Банк России
Рис. 1.16.
Судя по рис. 1.16, у этого тренда величина достоверности аппроксимации R2 =0,8933. Таким образом данный линейный тренд объясняет 89,33 % всех колебаний курса доллара к рублю, то есть это достаточно высокий уровень коэффициента детерминации. Следовательно, мы справились с задачей – найти линейный тренд, который лучше подходит для прогнозирования в биржевой торговле. Как я уже говорил, для целей прогнозирования, на наш взгляд, желательно использовать линейный тренд с коэффициентом детерминации не ниже R2=0,80.
Формула этого тренда Y = 0,1231X + 32,101 означает, что при увеличении номера торгового дня X на одну единицу (то есть с каждым последующим торговым днем) величина курса доллара Y в период с 27.06.2014 г. по 28.11. 2014 г. в среднем вырастала на 12,31 копейки при исходном уровне (то есть перед началом торгов 27.06.2014 г.), равном 32,101 рублей.
Таким образом в главе 1 мы научились в Excename = "note"
Строить график с данными по курсу валюты.
Оптимизации масштаба графика курса валюты с учетом значений анализируемых данных.
Построению графическим способом линейного тренда.
Поиску и построению графическим способом линейного тренда с высоким коэффициентом детерминации.
1.3. Работа над закреплением пройденного материала
С учетом полученной в этой главе информации предлагаю читателю проверить свои знания, решив следующие два задания. При этом нужно исходить, что выявление линейного тренда нам потребуется для участия в торгах 1 декабря 2014 года.
Задание 1.1
Шаг 1. Теперь нам нужны исходные данные по паре ‑ евро/рубль за период с 1 июля 1992 года по 1 декабря 2014 года. При этом с 1 июля 1992 года и до конца 1998 года будем использовать вместо евро его предшественницу ‑ ЭКЮ, которую в еврозоне в начале 1999 года обменяли к новой единой европейской валюте исходя из соотношения 1 евро=1 ЭКЮ. Данные по курсу евро и ЭКЮ нужно скачать на сайте Банка России в разделе «Динамика официального курса заданной валюты».
Шаг 2. Проведите первичную обработку данных для дальнейшей с ними работы, как мы ранее это делали по курсу доллара к рублю. То есть нужно сдвинуть дату итогов котировки валютной пары на один день назад. Кроме того, данные по курсу евро (а точнее говоря по курсу ЭКЮ) к рублю с 30 июня 1992 года по 31 декабря 1997 года необходимо разделить на 1000. Поскольку Банк России дает курс рубля за этот период в неденоминированном виде.
Шаг 3. Постройте график с данными по курсу евро к рублю.
Шаг 4. Проведите оптимизацию масштаба графика курсу евро к рублю с учетом значений анализируемых данных перед началом торгов 1 декабря 2014 года.
Шаг 5. Постройте графическим способом линейный тренд по курсу евро к рублю.
Шаг 6. Найдите и постройте графическим способом линейный тренд с высоким коэффициентом детерминации по курсу евро к рублю.
Задание 1.2
Шаг 1. Для выполнения этого задания нам нужны данные по паре ‑ евро/доллар за период с 1 июля 1992 года по 1 декабря 2014 года. Как мы уже говорили, с 1 июля 1992 года по конец 1998 года вместо евро нами будут использоваться данные по курсу ЭКЮ, исходя из соотношения 1 евро=1 ЭКЮ.
Шаг 2. На сайте Банка России нет данных по паре евро/доллар. Поэтому для того, чтобы получить курс евро к доллару необходимо взять за весь период данные по курсу евро к рублю (с 1 июля 1992 и до конца 1998 года нужно взять данные по курсу ЭКЮ к рублю) и по курсу доллара к рублю, а затем поделить первые на вторые. Например, на 29 ноября 2014 года курс евро к доллару находим таким образом: (EUR/RUB)/(USD/RUB)=EUR/USD=61,4108/49,3220= 1,2451 долл.
Аналогичные вычисления в Excel делаем по всем датам, относящимся к периоду с 1 июля 1992 года по 1 декабря 2014 года.
Шаг 3. Проведите первичную обработку данных для дальнейшей работы с ними. Правда, при этом потребуется только сдвинуть дату итогов котировки валютной пары на один день назад. А вот делить на 1000 полученные данные не нужно, поскольку в результате деления курса евро к рублю и курса доллара к рублю эффект деноминации российской валюты в полученных данных исчезает.
Шаг 4. Постройте график с данными по курсу евро к доллару.
Шаг 5. Проведите оптимизацию масштаба графика курсу евро к доллару с учетом значений анализируемых данных перед началом торгов 1 декабря 2014 года.
Шаг 6. Постройте графическим способом линейный тренд по курсу евро к доллару.
Шаг 7. Найдите и постройте графическим способом линейный тренд с высоким коэффициентом детерминации по курсу евро к доллару .
Глава 2. Как в Excel решить однофакторное уравнение регрессии для линейного тренда
2.1. Решаем уравнение регрессии
В первой главе мы научились графическим способом находить линейный тренд с высоким коэффициентом детерминации R2, величина которого говорит об уровне его устойчивости. Впрочем, надо иметь в виду: даже при самом высоком коэффициенте R2 тренд в силу различных причин может в любую минуту смениться. И к этому нужно быть всегда готовым – об этом мы поговорим в главе 4.
А в главе 2 остановимся на расчете формулы и других параметров линейного тренда с помощью однофакторного уравнения регрессии. Для справки замечу, что в теории вероятностей и математической статистике регрессия (лат. regressio – обратное движение, отход) представляет собой зависимость среднего значения какой-либо переменной Y от другой независимой переменной X или нескольких независимых переменных.
В отличие от чисто функциональной зависимости, когда каждому значению независимой переменной X соответствует одно определённое значение зависимой (результативной) величины Y, при регрессионной связи одному и тому же значению независимой X могут соответствовать – из-за воздействия случайной компоненты ‑ различные значения зависимой (результативной) переменной Y.
В качестве метода аппроксимации (подгонки) в уравнении регрессии используется метод наименьших квадратов (МНК), минимизирующий сумму квадратов отклонений курса валюты Y от его предсказываемых значений Yрасчет, рассчитанных по определенной формуле. Причем, используя в главе 1 графический способ поиска линейного тренда, мы также применяли МНК, но без вывода данных, которые программа Excel в этом случае не дает. В связи с этим графический способ не позволяет пользователю программы оценить статистическую значимость, как в целом уравнения регрессии, так и отдельных его членов.
По сравнению с графическим способом, преимуществом которого является его наглядность, решение уравнения регрессии в Excel позволяет не только найти уравнение тренда, но и с помощью тестов проверить не только его статистическую значимость в общем виде, но и отдельных включенных в уравнение переменных.
Как мы уже говорили, динамика курса валюты представляет собой упорядоченный во времени ряд, имеющий не только тренд, но и случайную компоненту. Поэтому в качестве метода оценки параметров тренда, как правило, используют уравнение регрессии. При этом задачей регрессионного анализа является определение математической формулы, которая аппроксимирует (подгоняет) динамику зависимой (результативной) переменной Y (курс доллара к рублю или другой валюты) от соответствующего изменения независимой переменной X (порядкового номера года, квартала, месяца, торгового дня и т.д.). При этом методом этой «подгонки» является метод наименьших квадратов.
Посмотрим, как можно найти линейную форму зависимости переменной 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. Вывод итогов и оценка параметров уравнения регрессии
Результаты решения уравнения регрессии, которые в программе Excel даются в виде единой таблицы под заголовком ВЫВОД ИТОГОВ, у нас представлены в виде четырех блоков (см. таблицы 2.2,2.3, 2.4 и 2.5). Так, в таблице 2.2 сгенерированы результаты по регрессионной статистике; в таблице 2.3 дается дисперсионный анализ; в таблице 2.4 оценивается статистическая значимость коэффициентов регрессии; а в таблице 2.5 даются найденные по данному уравнению регрессии расчетные значения курса доллара yрасчет и остатки.
Будем знакомиться с этими таблицами по раздельности, но при этом, чтобы не потонуть в потоке информации будем обращать внимание только на наиболее важные для прогнозирования параметры вывода данных (выделены жирным шрифтом). Более подробно о параметрах вывода данных по итогам решения уравнения регрессии можно прочитать в моей книге ‑ «Как предсказать курс доллара. Эффективные методы прогнозирования с использованием Excel и EViews», ‑ стр. 22-40.
В таблице 2.2. следует обратить внимание на параметры R-квадрат и Нормированного R-квадрата. R-квадрат или R2, о котором мы уже говорили в главе 1, называется коэффициентом детерминации. Как мы уже знаем, что он дается на диаграмме в случае построения тренда графическом способе. Нормированный R-квадрат в статистической литературе иногда называют еще и скорректированным коэффициентом детерминации.
Параметры, представленные в таблице 2.2, оценивают уровень аппроксимации фактических данных, полученный с помощью данного уравнения регрессии. Так, здесь R2=0,8933, а это означает, что линейный тренд объясняет 89,33 % всей динамики курса доллара к рублю, то есть в данном случае мы получили довольно высокий уровень коэффициента детерминации.
Можно также сказать несколько иначе: в данном уравнении тренда изменения независимой переменной «Порядковый номер торгового дня» на 89,33 % объясняет динамику зависимой переменной «Курс доллара к рублю». Кстати, при графическом способе решения уравнения тренда – по тем же рыночным данным и за тот же период времени – величина коэффициента детерминации R2 также оказалась равна 0,8933 – см. рис. 1.20.
Чем ближе коэффициент детерминации к 1, тем теснее связь между переменными, включенными в уравнение регрессии. Как я уже говорил, для целей торговли, нужно использовать линейный тренд с коэффициентом детерминации не ниже R2=0,80.
Когда максимальное значение коэффициента детерминации равно 1, то в этом случае можно сказать, что динамика зависимой переменной на 100% объясняется изменением независимой переменной. В этом случае также говорят, что между переменными существует функциональная связь. Это будет означать, что в динамике тренда нет случайной компоненты, но, вполне очевидно, что на практике этого в колебаниях курсов валют никогда не бывает.
Нормированный R2 имеет смысл использовать, когда нам придется сравнивать уравнения регрессии с различным количеством включенных в него независимых переменных. Дело в том, что при добавлении в уравнении регрессии независимых переменных величина коэффициента детерминации R2 соответственно растет. Поэтому для того чтобы сделать сравнения коэффициентов детерминации между уравнениями регрессии с разным числом факторов сопоставимыми, используется нормированный (скорректированный) R2, величина которого корректируется в сторону уменьшения при добавлении в уравнение дополнительных факторов. При прочих равных условиях предпочтение будем отдавать тому уравнению, в котором нормированный R2 будет выше.
Еще один параметр в таблице 2.2. Наблюдения=109 говорит о том, что в данное уравнение регрессии получено на основе данных по итогам торгов за 109 торговых дней.
Поделиться книгой: