Спарклайны для калькулятора валют рынка Forex в Excel

Если у вас есть Excel в версии с 2010 года, то вы часто использовали график курса во времени в ячейках листа, известный в разговорной речи как Sparkline. Спарклайны особенно полезны при построении панелей мониторинга или графически расширенных отчетов. Рассмотрим еще один интересный метод использования спарклайнов, который будет обогащен сигналом положительной/отрицательной динамики изменения показателей. Такое решение позволит не только отслеживать тенденции данных, но и быстро находить области, требующие срочных дополнительных изменений.

Подготовка данных для спарклайнов калькулятора форекс-стратегий

Для примера работы со спарклайнами составим простейший калькулятор анализа торговой стратегии на рынке Forex одновременно по 16-ти валютным парам на протяжении целых 6-ти лет. Калькулятор позволить пользователю быстро визуально определить наиболее подходящие валютные пары и вместе с тем же исключить убыточные валюты для данной стратегии.

В качестве источника данных для построения графиков в ячейках мы будем использовать результаты теста стратегии, заранее предоставленные в форме отчета о балансе средств на торговом счете по состоянию на текущий год с разбивкой по отдельным валютным парам. Созданные нами спарклайны графики позволят вам быстро определить тенденцию успешности торговли и определить те валютные пары, для которых баланс средств стал ниже, чем в 2014 году. Следующая таблица подготовлена на втором листе под названием «Данные»:

Как видно из данных отчета условием тестирования было определено, что в 2014-ом году был открыт торговый счет на, который был внесен депозит на сумму 10 000. Далее баланс депозита изменялся то в плюс, то в минус на протяжении 6-ти лет в зависимости от того по какой валюте совершались торги с использованием одной и той же торговой стратегии.

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

Создание шаблона интерактивной аналитической таблицы валют в Excel

Сам калькулятор – очень прост и будет обладать только лишь одним элементом управления. Для этого перейдите на первый лист «Калькулятор» и в ячейке D5 создайте выпадающий список со ссылкой на диапазон =Данные!$C$1:$H$1. Выберите инструмент: «ДАННЫЕ»-«Работа с данными»-«Проверка данных». В появившемся окне «Проверка вводимых значений» на вкладке «Параметры» из группы опций «Тип данных:» выберите опцию «Список». А в поле ввода «Источник» введите ссылку на используемые значения из ячеек второго листа как показано ниже на рисунке:

Также заполните заголовки таблицы. А затем заполняем табличную часть формулами:

Ссылаясь на ячейки первого столбца таблицы на листе «Данные» получаем все наименования валютных пар заполняя таким образом формулой со ссылкой =Данные!A2 диапазон ячеек B6:B21 на первом листе «Калькулятор»:

Диапазон ячеек D6:D21 на первом листе заполняем формулой с функцией =ГПР() для выборки значений со второго листа:

Диапазон E6:E21 на первом листе заполняем формулой для вычисления уровня разницы изменения в процентном соотношении между начальным сальдо торгового счета в 2014-ом году – 10 000 и текущим по состоянию на указанный год пользователем:

Не спешите устанавливать процентный формат, а вместо этого зададим свой пользовательский более информативный формат ячеек для диапазона E6:E21 вызвав диалоговое окно клавишами CTRL+1 где необходимо указать параметры как на рисунке:

Условным форматированием экспонируем красным цветом отрицательные изменения в процентах для удобочитаемости анализа. Для этого предварительно выделите диапазон E6:E21 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило». В появившемся окне «Диспетчер правил условного форматирования» нажмите на кнопку «Создать правило» после чего отмечаем опцию «Форматировать только ячейки, которые содержат» и указываем параметры: 1-значение ячейки; 2-меньше; 3-=0. Так же не забудем указать формат для ячеек, которые соответствуют данному условию:

Диспетчер правил условного форматирования.

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

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

Мы будем использовать особенные, умные спарклайны, которые должны иметь возможность динамически изменятся в зависимости от выбранного года пользователем в калькуляторе. Также все спарклайны в столбце E6:E21 должны автоматически изменять свой цвет в зависимости от положительной тенденции (синий) или отрицательной (красный).

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

Добавляем к таблице еще один столбец с формулой в ячейках Q2:Q17, которая буде определять убыточные валютные пары по состоянию на выбранный год в калькуляторе:

И в итоговой строке K18:P18 данной таблицы будем использовать формулу для главного спарклайна отображающего динамику общего баланса одновременно по всем валютным парам:

Теперь, когда все данные подготовлены переходим на лист «Калькулятор» и создаем спарклайны. Для этого выделите диапазон G6:G21 и выберите инструмент: «ВСТАВКА»-«Спарклайны»-«График»:

В окне «Создание спарклайнов» укажите в поле «Диапазон данных:» внешнюю ссылку Данные!J2:P17 на ранее созданную дополнительную таблицу на втором листе и нажмите ОК.

Изменение цвета спарклайнов по условию

Чтобы заставить спарклайны автоматически изменять свой цвет в зависимости от убыточности анализируемых валют придется использовать макрос. Чтобы создать такой макрос откройте редактор макросов выбрав инструмент: «РАЗРАБОТЧИК»-«Код»-«VisualBasic» или нажмите комбинацию клавиш ALT+F11:

Далее создайте новый модуль выбрав инструмент: «Insert»-«Module» и в созданный Module1 вставьте код:

Sub controlcolor()

Dim spkline As SparklineGroup

Dim lRow As Long

lRow = Cells(Rows.Count, "B").End(xlUp).Row

Range("G6:G" & lRow).SparklineGroups.Ungroup

For Each spkline In Range("G6:G" & lRow).SparklineGroups

With Application.Range(spkline.Item(1).SourceData)

If .Offset(, .Columns.Count).Resize(1, 1).Value = 1 Then

   spkline.SeriesColor.Color = 255

Else

   spkline.SeriesColor.ThemeColor = 5

End If

End With

Next

End Sub

Описание действий программы в коде макроса:

Указать номер последней непустой строки в столбце B для автоматического определения количества спарклайнов.
Определить адрес диапазона ячеек со спарклайнами вместе с тем получить доступ к их группе и разгруппировать.
Методом перебора получить доступ к адресу внешней ссылки на диапазон данных для каждого спарклайна и проверить, имеет ли соседняя ячейка значение 1 (то есть в столбце loss на листе «Данные»).
Если соседняя ячейка по отношению к адресу ссылки данных для спарклайна содержит число 1, тогда линия на графике окрашивается в красный цвет, в противном случае - в синий.

А теперь сделайте двойной щелчок по Лист1 (Калькулятор) в редакторе VBA-макросов VisualBasic и вставьте в него код для вызова макроса controlcolor из модуля 1:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range: Set rng = [D5] 'диапазон изменяемых ячеек

If Not Intersect(rng, Target) Is Nothing Then controlcolor

End Sub

В этом коде вызова все просто: при каждом изменении значений в ячейке D5 выполняется макрос controlcolor на текущем листе 1 (Калькулятор).

Теперь в ячейку G4 добавим главный спарклайн для отображения динамики общего баланса:

В главном спарклайне использем ссылку на ячейки в итоговой строке дополнительной таблице второго листа: Данные!J18:P18.

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

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

количество убыточных валютных в процентах.

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

Скачать калькулятор валют для форекс в Excel

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