Как потестить систему в Экселе. Пошагово) Часть 1

Для примера тестирования возьмём простую стратегию, описанную несколько лет назад Юрием Иванычем в его живом журнале (http://jc-trader.livejournal.com/tag/%D0%B1%D0%BE%D0%BB%D0%BB%D0%B8%D0%BD%D0%B4%D0%B6%D0%B5%D1%80).
В основе системы — Боллинджер со следующими параметрами: SMA 70, 2 стандартных девиации. Рабочий таймфрейм — часовики.
Условия для открытия/закрытия позиций:
Лонг. Если свеча закрывается выше верхней границы Боллинджера, на открытии следующей свечи открывается лонг. Если свеча закрывается ниже скользящей средней, на открытии следующей свечи лонг закрывается.
Шорт. Если свеча закрывается ниже нижней границы Боллинджера, на открытии следующей свечи открывается шорт. Если свеча закрывается выше скользящей средней, на открытии следующей свечи шорт закрывается.
Позиция открывается на весь депозит. Полное реинвестирование.

Шаг 1. Котировки для нашего тестирования мы возьмём на сайте Финама. Нам необходимы склеенные фьючерсы в текстовом формате на индекс РТС за период с октября 2005 года до августа 2015. Сайт http://finam.ru/profile, «Экспорт котировок». Выбираем период, таймфрейм (1 час), разделительный знак (запятая или точка с запятой), (время дня в этой системе не принципиально, но отметим галочкой, что в файле будет указано время открытия свечи). Получаем файл в формате .txt.
Мы разобьём наши котировки на 3 отдельных файла: 1.) в первом файле будут данные с 2005 по февраль 2011 (примерно за этот переод тестировал систему Юрий Иваныч), 2.) во втором файле будут котировки с марта 2011 по сентябрь 2012 года (когда шаг цены на фьючерсе был увеличен в два раза), 3.) и наконец в третьем файле будут котировки с октября 2012 до конца августа 2015 года.
Шаг 2. Экспортируем котировки в формат Экселя. Открываем в Экселе вкладку «Данные», выбираем в левой части панели инструментов «Из текста». Находим первый файл (2005-2011). Выполняем следующие три шага:
.) шаг первый пропускаем («далее»),

2.) шаг второй — выбираем разделитель (я использую запятые) (нажимаем «далее»),


3.) шаг третий — выбираем столбцы из файла, которые следует пропустить: в этой системе нам не понадобятся столбцы PER и VOL, их пропускаем, жмём «готово».

Теперь нам необходимо построить таблицу, которая будет иметь следующий вид: в первой её части будут сами котировки, будут заданы параметры Боллинджера, а также будут заданы условия открытия позиций;

во второй части будут определены цены открытия позиций, условия и цены закрытия позиций, объём открытых позиций в деньгах и по количеству контрактов, объём закрытых позиций в деньгах, а также возможные издержки (комиссионные+проскальзывание);




в третьей части таблицы будут показаны результаты системы, изменение депо и кроме того будут рассчитаны параметры, необходимые для оценки эффективности системы, такие как относительное количество прибыльных сделок, профит-фактор, максимальная просадка, плюс будут рассчитаны два  коэффициента для оценки волатильности кривой капитала (коэффициенты Шарпа и Сортино).

Собственно, для того, чтобы впоследствии расчитать коэффициенты Шарпа и Сортино, нам необходимо разделить столбец, где указаны даты формирования свечей, на три отдельных столбца (год, месяц и день) — это позволит нам без лишних проблем рассчитать доходность системы в каждый отдельный месяц. Делаем это так: выбираем вкладку «Данные», выбираем нужный столбец и нажимаем «Текст по столбцам», на первом шаге ставим галочку напротив «фиксированной ширины»,




на втором шаге вручную расставляем разделители,




на третьем шаге нажимаем «готово» — получаем три столбца.

А теперь основные функции.

1.) Рассчитываем Боллинджер.
Три столбца: первый столбец (J) — простая скользящая средняя с периодом 70. В ячейку J71 записываем функцию =СРЗНАЧ(I2:I71) — функция будет отсылать к столбцу I, где указаны цены закрытия свечей (close) и считать среднее значение цен закрытия за 70 свечей.
В столбце K — верхняя полоса Боллинджера. рассчитываем её как стандартное отклонение от цены закрытия, помноженное на 2 (2 стандартных девиации) и прибавленное к значению скользящей средней — в ячейку K71 пишем:
=J71+СТАНДОТКЛОН(I2:I71)*2
Похожую формулу пишем в ячейку L71 но только здесь мы вычитаем две девиации из значения скользящей:
=J71-СТАНДОТКЛОН(I2:I71)*2 -
получаем нижнюю полосу. Выделяем все три ячейки, правая кнопка мыши, выбираем «копировать». В поле «Имя», которое находится в левой верхней части экрана (слева от поля «функции»), вводим значение последней ячейки диапазона — в данном случае это ячейка L15283, зажимаем клавишу Shift и нажимаем Enter -все ячейки диапазона автоматический выделяются (от J71 до L15283). затем нажимаем «вставить» — и формулы из первых трёх ячеек копируются на весь диапазон. 

2.) Задаём условия для открытия позиций.
В ячейку M73 занесём формулу для открытия лонга.
Условие сформулировано так: если предыдущая свеча закрылась выше верхней полосы Боллинджера, на этой свече открывается лонг.
Используем функцию ЕСЛИ: В M73 пишем:
=ЕСЛИ(I72>K72;1;0)
(если цена закрытия (значение в ячейке I72) будет выше значения в ячейке K72, в столбце появится единица, которая будет сигналом на вход; в ином случае будет 0).


Аналогичную формулу пишем в столбец N73, но только значение I72 должно быть ниже значения в ячейке L72: =ЕСЛИ(I72<L72;1;0) (в данном случае единица будет сигналом на открытие шорта).

Копируем формулы ячеек на весь диапазон вышеописанным способом.

3.) Следующие восемь столбцов носят названия: «Цена открытия лонга» (столбец O), «Стоп-лосс Лонг» (P), «Тейк-проф. Лонг» (Q), «Цена открытия шорта» ®, «Стоп-лосс Шорт»(S), «Тейк-профит Шорт» (T), «Цена закрытия Лонг» (U), «Цена закрытия Шорт» (V).
Поскольку система не имеет фиксированных стоп-лоссов и тейк-профитов — соответствующие столбцы будут пустыми.
Для определения цены лонга используем формулу с вложенными функциями.
Нам необходимо сделать так, чтобы в столбце Цены открытия отображалось не только открытие позиции, но и её удержание до тех пор, пока позиция не будет закрыта по сигналу. В частности сигнал на закрытие лонга поступит из столбца U — «цена закрытие лонга», до тех пор, пока этого сигнала не будет, в каждой ячейки столбца O должна отображаться цена открытия, если позиция была открыта.
Для начала определим условие и цену закрытия лонга. Помним, что условие закрытия лонга — закрытие свечи ниже скользящей средней; позиция закрывается по цене открытия следующей свечи.
Формально позиция должна быть закрыта при соблюдении двух условий: прежде позиция была открыта + значение в последней ячейке в столбце I (цена закрытия) оказалось ниже значения в последней ячейке в столбце J (скользящая средняя). При соблюдении этих условий позиция закрывается по значению в текущей ячейке в столбце F (цена открытия). В столбец U22 пишем формулу:
=ЕСЛИ(И(O22>0;I21<J21);F22;0)
Здесь мы использовали функцию «И», которая позволяет сочетать несколько условий. 
Теперь при соблюдении условий в столбце отобразиться цена из ячейки F22, по которой будет закрыта позиция; в ином случае будет ноль. 
Теперь зададим условия, по которым будет отображаться цена открытия лонга в столбце O.
У нас есть два условия для открытия лонга: первое условие — это сигнал, получаемый из столбца M (сигнал на открытие лонга), второе условие — это отсутствие открытой позиции.
Зададим их при помощи той же функции «И»: И(M22=1;O21=0)
В текущей ячейке столбца M сигнал на лонг, и вместе с тем предыдущее значение «Цены открытой позиции» = нулю. Если соблюдены оба эти условия, должна быть открыта позиция по цене открытия текущей свечи. Опять же используем функцию «ЕСЛИ»:
=ЕСЛИ(И(M22=1;O21=0);F22
Теперь в ту же формулу необходимо задать условия, при которых позиция будет удерживаться.
Таких условий тоже два: позиция прежде была открыта + сигнала на закрытие позиции не было (ноль в столбце U). Если в предыдущей ячейке была открытая позиция + ноль в текущей ячейке U, значение текущей ячейки O будет равно значению предыдущей. Запишем это в виде функции:
ЕСЛИ(И(O21>0;U21=0);O21
И добавим эту функцию в имеющуюся формулу:
=ЕСЛИ(И(M22=1;O21=0);F22; ЕСЛИ(И(O21>0;U21=0);O21;0))
Формула читается так: Если открытой позиции нет (O21=0), но есть сигнал на открытие позиции (M22=1), позиция открывается на текущей свече (F22); в ином случае — если позиция была открыта (O21>0) и сигнала на её закрытие нет (U21=0), позиция удерживается (O21); и наконец если ни одно из условий не соответствует действительности (нет сигнала на лонг (M22=1) или поступил сигнал на закрытие лонга (U21>0)) — то значение в столбце O равно нулю — открытых позиций нет (вне рынка).

Аналогичным образом задаются условия на открытие, удержание и закрытие шорта в соответствующих столбцах (R и V).

Пока всё) Потом допишу. Сейчас нет времени. Да и многовато для одного поста)