Как спрогнозировать курс валют в excel

Создание прогноза в Excel для Windows

Если у вас есть статистические данные с зависимостью от времени, вы можете создать прогноз на их основе. При этом в Excel создается новый лист с таблицей, содержащей статистические и предсказанные значения, и диаграммой, на которой они отражены. С помощью прогноза вы можете предсказывать такие показатели, как будущий объем продаж, потребность в складских запасах или потребительские тенденции.

Часть электронной таблицы, содержащая таблицу прогнозируемых чисел и диаграмму прогноза

Создание прогноза

На листе введите два ряда данных, которые соответствуют друг другу:

ряд значений даты или времени для временной шкалы;

ряд соответствующих значений показателя.

Эти значения будут предсказаны для дат в будущем.

Примечание: Для временной шкалы требуются одинаковые интервалы между точками данных. Например, это могут быть месячные интервалы со значениями на первое число каждого месяца, годичные или числовые интервалы. Если на временной шкале не хватает до 30 % точек данных или есть несколько чисел с одной и той же меткой времени, это нормально. Прогноз все равно будет точным. Но для повышения точности прогноза желательно перед его созданием обобщить данные.

Выделите оба ряда данных.

Совет: Если выделить ячейку в одном из рядов, Excel автоматически выделит остальные данные.

На вкладке Данные в группе Прогноз нажмите кнопку Лист прогноза.

Кнопка

В окне Создание прогноза выберите график или гограмму для визуального представления прогноза.

Снимок диалогового окна

В поле Завершение прогноза выберите дату окончания, а затем нажмите кнопку Создать.

В Excel будет создан новый лист с таблицей, содержащей статистические и предсказанные значения, и диаграммой, на которой они отражены.

Этот лист будет находиться слева от листа, на котором вы ввели ряды данных (то есть перед ним).

Настройка прогноза

Если вы хотите изменить дополнительные параметры прогноза, нажмите кнопку Параметры.

Сведения о каждом из вариантов можно найти в таблице ниже.

Выберите дату, с которой должен начинаться прогноз. При выборе даты начала, которая наступает раньше, чем заканчиваются статистические данные, для построения прогноза используются только данные, предшествующие ей (это называется "ретроспективным прогнозированием").

Если вы начинаете прогноз перед последней точкой, вы сможете получить оценку точности прогноза, так как сможете сравнить прогнозируемый ряд с фактическими данными. Но если начать прогнозирование со слишком ранней даты, построенный прогноз может отличаться от созданного на основе всех статистических данных. При использовании всех статистических данных прогноз будет более точным.

Если в ваших данных прослеживаются сезонные тенденции, то рекомендуется начинать прогнозирование с даты, предшествующей последней точке статистических данных.

Установите или снимите флажок Доверительный интервал, чтобы показать или скрыть его. Доверительный интервал — это диапазон вокруг каждого предсказанного значения, в который в соответствии с прогнозом (при нормальном распределении) предположительно должны попасть 95 % точек, относящихся к будущему. Доверительный интервал помогает определить точность прогноза. Чем он меньше, тем выше достоверность прогноза для данной точки. Доверительный интервал по умолчанию определяется для 95 % точек, но это значение можно изменить с помощью стрелок вверх или вниз.

Сезонность — это число для длины (количества точек) сезонного шаблона и автоматически обнаруживается. Например, в ежегодном цикле продаж, каждый из которых представляет месяц, сезонность составляет 12. Автоматическое обнаружение можно переопрепредидить, выбрав Установить вручную, а затем выбрав число.

Примечание: Если вы хотите задать сезонность вручную, не используйте значения, которые меньше двух циклов статистических данных. При таких значениях этого параметра приложению Excel не удастся определить сезонные компоненты. Если же сезонные колебания недостаточно велики и алгоритму не удается их выявить, прогноз примет вид линейного тренда.

Диапазон временной шкалы

Здесь можно изменить диапазон, используемый для временной шкалы. Этот диапазон должен соответствовать параметру Диапазон значений.

Здесь можно изменить диапазон, используемый для рядов значений. Этот диапазон должен совпадать со значением параметра Диапазон временной шкалы.

Заполнить отсутствующие точки с помощью

Для обработки отсутствующих точек в Excel используется интерполяция, то есть отсутствующие точки будут заполнены в качестве взвешенного среднего значения соседних точек, если отсутствует менее 30 % точек. Чтобы использовать отсутствующие точки как нули, выберите в списке пункт Нули.

Использование агрегатных дубликатов

Если данные содержат несколько значений с одной меткой времени, Excel находит их среднее. Чтобы использовать другой метод вычисления, например Медиана или Количество,выберите нужный способ вычисления из списка.

Включить статистические данные прогноза

Установите этот флажок, если хотите поместить на новом листе дополнительную статистическую информацию о прогнозе. При этом добавляется таблица статистики, созданная с помощью прогноза. Ets. Функция СТАТ и показатели, такие как коэффициенты сглаживания ("Альфа", "Бета", "Гамма") и метрики ошибок (MASE, SMAPE, MAE, RMSE).

Формулы, используемые при прогнозировании

При использовании формулы для создания прогноза возвращаются таблица со статистическими и предсказанными данными и диаграмма. Прогноз предсказывает будущие значения на основе имеющихся данных, зависящих от времени, и алгоритма экспоненциального сглаживания (ETS) версии AAA.

Таблицы могут содержать следующие столбцы, три из которых являются вычисляемыми:

столбец статистических значений времени (ваш ряд данных, содержащий значения времени);

столбец статистических значений (ряд данных, содержащий соответствующие значения);

столбец прогнозируемых значений (вычисленных с помощью функции ПРЕДСКАЗ.ЕTS);

два столбца, представляющие доверительный интервал (вычисленные с помощью функции ПРЕДСКАЗ.ЕTS.ДОВИНТЕРВАЛ). Эти столбцы отображаются только при проверке доверительный интервал в разделе Параметры.

Скачивание образца книги

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

Как предсказать курс доллара. Эффективные методы прогнозирования с использованием Excel и EViews
Владимир Георгиевич Брюков, 2017

Каким будет курс американского доллара завтра, послезавтра, через неделю или через месяц? Сколько будут стоить в ближайшем будущем евро, британский фунт, швейцарский франк и другие валюты? Каких рекомендуемых курсов покупки или продажи при этом следует придерживаться? Это второе дополненное издание книги для тех, кто не только хочет зарабатывать на валютном рынке, но и свести к минимуму свои потери от торговли на валютном рынке. А для этого трейдер должен научиться прогнозировать.

Оглавление

  • Предисловие ко второму дополненному изданию
  • Предисловие к первому изданию
  • Об авторе
  • 1.1. Краткая характеристика стационарных и нестационарных случайных процессов
  • 1.2. Распознавание стационарности временного ряда с помощью построения его графика
  • Контрольные вопросы и задания к главе 1
  • 2.1. Характеристика метода наименьших квадратов и его применение при прогнозировании курса доллара
  • 2.2. Решение уравнения регрессии в Excel с учетом фактора времени. Интерпретация и оценка значимости полученных параметров

Приведённый ознакомительный фрагмент книги Как предсказать курс доллара. Эффективные методы прогнозирования с использованием Excel и EViews предоставлен нашим книжным партнёром — компанией ЛитРес.

2.2. Решение уравнения регрессии в Excel с учетом фактора времени. Интерпретация и оценка значимости полученных параметров

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

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

Второй этап состоит из собственно решения уравнения регрессии и нахождения его параметров.

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

Как известно, одним из наиболее распространенных способов определения тренда в динамике курса валюты является построение его зависимости от фактора времени T. Так, если в качестве зависимой переменной Y мы возьмем ежемесячный курс доллара, а в качестве независимой переменной T — время (в данном случае порядковые номера месяцев, начиная с июня 1992 г.=1), то у нас получится следующее уравнение парной линейной регрессии:

Y расч. =a + bT (2.2);

где a — свободный член уравнения регрессии; b — линейной коэффициент регрессии, показывающий, как изменение величины независимой переменной (фактора) T в среднем способствует изменению зависимой переменной (результативного признака) Y; Y расч. — расчетное значение результативного признака, вычисляемое по формуле (2.2).

Минимизируем сумму квадратов отклонений (остатков) Y факт. от Y расч.,то есть от фактических значений курса доллара от его расчетных значений. В результате формулу МНК (2.1.1) для линейной регрессии можно в данном случае представить в виде формулы (2.3):

Уравнение (2.3) в принципе можно решить самостоятельно, если найти его параметры согласно формулам (2.1.4) и (2.1.5), но в целях ускорения этого процесса мы будем его решать с помощью Пакета анализа Excel. Кстати, желающие лучше усвоить суть МНК могут сначала самостоятельно в «ручном режиме» решить данное уравнение регрессии, а затем сверить свои результаты с теми, что мы получим в Excel.

Для того чтобы подготовить исходные данные к решению данного уравнения регрессии разместим в Excel два столбца исходных данных. В первом столбце, который озаглавим Time, поместим порядковые номера месяцев, начиная с июня 1992 г. (с номером =1) и кончая апрелем 2010 г. (с номером =215). Во втором столбце, который озаглавим USDOLLAR, поместим данные по курсу доллара на конец месяца, начиная с июня 1992 г. и заканчивая апрелем 2010 г. (последние данные, имевшиеся на тот момент, когда писались эти строки). Таким образом столбец Time представляет собой независимую переменную, которая в формуле (5) обозначена символом T, а столбец USDOLLAR является зависимой переменной Yфакт. Далее переходим к решению уравнения регрессии в Пакете анализа Excel, о том, как это делается, можно прочитать ниже — в алгоритме действий № 3.

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

Шаг 1. Ввод в уравнение исходных данных

Делается это следующим образом: сначала в Microsoft Excel 2007 г. в верхней панели инструментов выбирается опция Данные (в Microsoft Excel 1997-2003 гг. нужно выбрать опцию Сервис), потом в появившемся окне Анализ данных — опция Регрессия. После чего появляется новое окно — Регрессия (см. рис. 2.1), в котором в графе Входной интервал y выделяем (с помощью мышки) столбец данных USDOLLAR (ячейки $C$1:$C$216). Здесь же в графе Входной интервал Х» выделяем столбец данных Time(ячейки $B$1:$B$216), то есть независимую переменную T из нашего уравнения регрессии (5).

Шаг 2. Дополнительные опции

Если бы мы хотели получить уравнение регрессии без свободного члена, который в формуле (2.2) обозначен символом a, то тогда нам следовало бы выбрать еще и опцию КОНСТАНТА-НОЛЬ. Однако в данном случае в использовании этой опции нет необходимости.

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

Остатки = Yрасч. — Yфакт. (7); где Yрасч. — расчетные, Yфакт. — фактические значения результативного признака.

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

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

Шаг 3. Вывод итогов

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

Рис. 2.1. Диалоговое окно РЕГРЕССИЯ для вывода итогов при решении в Excel уравнения регрессии

Результаты решения уравнения регрессии, которые в программе Excel выдаются в виде единой таблицы под заголовком ВЫВОД ИТОГОВ, у нас представлены в виде трех блоков (см. табл. 2.2-2.4). Так, в табл. 2.2 сгенерированы результаты по регрессионной статистике, в табл. 2.3 дается дисперсионный анализ, а в табл. 2.4 оценивается статистическая значимость коэффициентов регрессии.

Параметры, представленные в табл. 2.2, оценивают уровень аппроксимации фактических данных, полученный с помощью данного уравнения регрессии. Так, параметр Множественный R обозначает множественный коэффициент корреляции R, который характеризует тесноту связи между результативным признаком Y и факторами переменными X1, X2…Xn. Данный коэффициент изменяется в пределах от 0 до 1, причем, чем ближе к 1, тем теснее корреляционная связь между переменными, включенными в уравнение регрессии. Множественный коэффициент корреляции равен квадратному корню, извлеченному из коэффициента детерминации R2, который у нас также приводится в регрессионной статистике. Множественный коэффициент R также находят по формуле (2.5):

где Y факт. — фактическое, а Y расч. — расчетное (предсказанное по уравнению регрессии) значение результативного признака.

Зная величину коэффициента корреляции R, можно дать качественную оценку силы связи между зависимой и независимыми переменными, включенными в данное уравнение. С целью классификации силы связи обычно используют шкалу Чеддока (см. табл. 2.1).

Таблица 2.1. Шкала Чеддока для классификации силы связи

В случае между переменными существует функциональная связь, то R=1, а если корреляционная связь отсутствует, то R=0. Поскольку в таблице 2.2 множественный коэффициент корреляции R равен 0,8456, то согласно таблице Чеддока, связь между переменными, включенными в уравнение регрессии можно считать высокой. Следует также заметить, что если коэффициент множественной корреляции меньше 0,7, то это означает, что величина коэффициента детерминации R2 (о нем мы расскажем ниже) будет меньше 50%, а потому регрессионные модели с таким коэффициентом детерминации не имеют большого практического значения.

Однако самым важным является другой параметр регрессионной статистики — R-квадрат (его мы выделили жирным шрифтом), обозначающий коэффициент детерминации R2. Коэффициент детерминации R2 характеризует долю дисперсии результативного признака Y, объясняемую уравнением регрессии, в общей дисперсии результативного признака. Коэффициент детерминации R2 находится по формуле (2.6):

Ссылка на основную публикацию