Построение индикаторов в Excel
Два способа построения индикаторов по заданному временному ряду котировок в Excel. Для примера взят индикатор средней цены за заранее заданный временной отрезок (количество котировок), "MovingAverage".
В макросе "formulaMA_1" алгоритм для каждого бара ("Close" котировки) суммирует заданное число предыдущих котировок включая текущую и делит сумму на их число. При помощи цикла.
В макросе "formulaMA_2" алгоритм просто вставляет формулу индикатора в ячейку столбца смежного со столбцом данных и "протягивает" формулу до конца диапазона данных. Затем заменяет формулы в ячейках на их значения, для уменьшения "тяжести" файла. Достаточно универсальный способ для тех кто не желает слишком копаться в макросах VBA.
В общем виде формула среднего за период 10 котировок в макросе выглядит так: = "=ROUND(AVERAGE(R[-9]C[-1]:RC[-1]),5)" . Для того чтобы иметь возможность быстро менять период индикатора нужно ввести изменяемую переменную, тогда формула будет выглядеть так: = "=ROUND(AVERAGE(R[" & -(sizeAverage - 1) & "]C[-1]:RC[-1]),5)". Получить текст любой другой формулы для вставки в макрос "formulaMA_2" можно при помощи макро-рекордера Excel. Делается это так - набираем формулу в ячейке листа Excel, в ленте панели инструментов выбираем: "Вид > Макросы > Запись макроса". Переходим на ячейку введённой формулы и нажимаем Enter. Можно выключать макро-рекордер "Вид > Макросы > Остановить запись". Нажимаем правой кнопкой мыши на ярлычке любого листа Книги и выбираем "Исходный текст", откроется окно редактора, в левой части навигации появится Модуль ("Module1"), в котором и будет записан макрос с текстом формулы. Если в книге уже были другие модули, вновь записанный макро-рекордером макрос обычно появляется в в модуле под последним номером, нужно поискать.
Ссылка на скачивание файла "formulaMA.xlsm" в архиве: (formulaMA.rar) , 210 KB.
Для оценки скорости выполнения вариантов расчёта написал макрос "testTime_1" по мотивам обсуждения в ветке (www.planetaexcel.ru).
Макрос тестирования времени выполнения перезапускает макрос расчёта индикатора заданное число раз и заносит время выполнения в массив, затем вычисляет среднее время и выводит данные на лист "Bufer". Макросы "testTime_1" и "testTime_2" отличаются только запускаемым макросом "formulaMA_1" или "formulaMA_2", просто для удобства - чтобы не лезть в текст менять при тестах.
Как и предполагалось, вариант расчёта через формулу оказался быстрее чем месить данные циклом. Примерно в два раза.
На системе x64 в Excel 2010 x64 (база данных - 6000 котировок) у меня получились следующие цифры:
Период индикатора
"formulaMA_1" Время, msec
"formulaMA_2" Время, msec
5
173.886719
172.910156
173.691406
92.109375
92.5
92.1875
10
177.1875
175.195312
173.066406
90.058594
90.078125
90.527344
20
178.28125
182.304688
183.359375
90.46875
89.140625
89.257812
Макрос тестирования времени выполнения запускался по три раза для каждого варианта расчёта индикатора и каждого периода рассчитываемого индикатора. Среднее время на числе в 100 перезапусков макроса.
Вот так примерно выглядит массив из 100 перезапусков, с выбросами в зависимости от текущей загрузки Системы процессами:
.
[Spoiler (click to open)].
Sub formulaMA_1()
'макрос расчёта индикатора "MovingAverage"
'объявление переменных
Dim dataArray() As Double 'массив данных
Dim maArray() As Double 'массив индикатора
Dim CloseValue As Double 'значение котировки
Dim Sum As Double 'сумма котировок за период
Dim mAverage As Double 'средняя котировка за период
Dim irowStart As Long 'номер первой заполненной строки данных листа "DataFx" с которой будет произведён расчёт индикатора
Dim irowEnd As Long 'номер последней заполненной строки данных в столбце тикера листа "DataFx"
Dim nRow As Long 'количество строк данных
Dim iArrayRow As Long 'количество строк в массиве
Dim iArrayCol As Integer 'количество столбцов в массиве
Dim sizeAverage As Integer 'период индикатора MovingAverage
Dim startingRow As Long 'начальная строка на листе вывода массива
Dim startingColumn As Integer 'начальный столбец на листе вывода массива
Dim i, k As Long 'счётчики циклов
'On Error Resume Next'переход к следующему шагу при ошибке в коде
Application.Calculation = xlManual 'выключаем автоматический пересчёт в книге
Application.ScreenUpdating = False 'отключаем обновление экрана
Application.ThisWorkbook.Worksheets("Settings").Select 'переход на лист "Settings"
'irowStart = 2 'номер строки данных листа "DataFx" с которой будет начат расчёт индикатора
'sizeAverage = 10 'период индикатора "MovingAverage"
irowStart = Application.ThisWorkbook.Worksheets("Settings").Cells(4, 3).Value '
sizeAverage = Application.ThisWorkbook.Worksheets("Settings").Cells(5, 3).Value '
Application.ThisWorkbook.Worksheets("DataFx").Select 'переход на лист "DataFx"
'определение номера последней заполненной строки данных в столбце тикера листа "DataFx"
irowEnd = Application.ThisWorkbook.Worksheets("DataFx").Columns("A").Find(What:="*", _
LookIn:=xlValues, SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
'
nRow = (irowEnd - irowStart) 'количество строк данных
'задание размерности массивов
ReDim dataArray(nRow, 0) 'массив данных, строк/столбцов
ReDim maArray(nRow, 0) 'массив индикатора, строк/столбцов
iArrayRow = UBound(maArray, 1) 'размер, количество строк в массиве
iArrayCol = UBound(maArray, 2) 'размер, количество столбцов в массиве
'заполнение массива в памяти данными с листа
For i = 0 To iArrayRow Step 1
CloseValue = Application.ThisWorkbook.Worksheets("DataFx").Cells(irowStart + i, 2).Value 'котировка
dataArray(i, 0) = CloseValue 'занесение в массив
Next i
'вычисление значения индикатора
k = sizeAverage - 1 '
Do While k <= iArrayRow 'выполнять пока верно условие, т.е. до конца массива данных
Sum = 0 'начальное значение
mAverage = 0 'начальное значение
For i = sizeAverage - 1 To 0 Step -1 'цикл суммирования в соотв. с величиной периода индикатора
CloseValue = dataArray(k - i, 0) 'котировка
Sum = Sum + CloseValue 'сумма котировок
Next i
mAverage = Round(Sum / sizeAverage, 5) 'среднее значение котировки за период
If k >= sizeAverage - 1 Then maArray(k, 0) = mAverage 'занесение результата в массив, с проверкой периода
k = k + 1 'увеличение позиции счётчика
Loop
Application.ThisWorkbook.Worksheets("DataFx").Select 'переход на лист
startingRow = irowStart
startingColumn = 3
Call ClearDataSheets 'запуск макроса удаления результатов прежних расчётов с листа "DataFx"
'вывод ответа на лист "DataFx"
With ThisWorkbook.Worksheets("DataFx")
.Range(.Cells(startingRow, startingColumn), .Cells(startingRow + iArrayRow, _
startingColumn + iArrayCol)).Value = maArray()
End With
Erase dataArray() 'удаление массива из памяти
Erase maArray() 'удаление массива из памяти
Application.Calculation = xlAutomatic 'включаем автоматический пересчёт в книге
Application.ScreenUpdating = True 'включаем обновление экрана
End Sub
Sub formulaMA_2()
''макрос расчёта индикатора "MovingAverage", вариант 2
'объявление переменных
Dim irowStart As Long 'номер первой заполненной строки данных листа "DataFx" с которой будет произведён расчёт индикатора
Dim sizeAverage As Integer 'период индикатора MovingAverage
Dim irowEnd As Long 'номер последней заполненной строки данных в столбце тикера листа "DataFx"
Dim iRow As Long 'первая строка вставки формулы
Application.Calculation = xlManual 'выключаем автоматический пересчёт в книге
Application.ScreenUpdating = False 'отключаем обновление экрана
Application.ThisWorkbook.Worksheets("Settings").Select 'переход на лист "Settings"
'irowStart = 2 'номер строки данных листа "DataFx" с которой будет начат расчёт индикатора
'sizeAverage = 10 'период индикатора "MovingAverage"
irowStart = Application.ThisWorkbook.Worksheets("Settings").Cells(4, 3).Value '
sizeAverage = Application.ThisWorkbook.Worksheets("Settings").Cells(5, 3).Value '
Application.ThisWorkbook.Worksheets("DataFx").Select 'переход на лист "DataFx"
Call ClearDataSheets 'запуск макроса удаления результатов прежних расчётов с листа "DataFx"
'определение номера последней заполненной строки данных в столбце тикера листа "DataFx"
irowEnd = Application.ThisWorkbook.Worksheets("DataFx").Columns("A").Find(What:="*", _
LookIn:=xlValues, SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
'
iRow = (irowStart + sizeAverage - 1)
Application.ThisWorkbook.Worksheets("DataFx").Cells(iRow, 3).Select 'переход на лист "DataFx"
'вставка формулы в первую ячейку диапазона расчёта
'ActiveCell.FormulaR1C1 = "=ROUND(AVERAGE(R[-9]C[-1]:RC[-1]),5)"
ActiveCell.FormulaR1C1 = "=ROUND(AVERAGE(R[" & -(sizeAverage - 1) & "]C[-1]:RC[-1]),5)"
'протягивание формул до конца диапазона данных
Selection.AutoFill Destination:=Application.ThisWorkbook.Worksheets("DataFx").Range(Cells(iRow, 3), Cells(irowEnd, 3))
Application.Calculation = xlAutomatic 'включаем автоматический пересчёт в книге
'замена формул в ячейках на значения
With Application.ThisWorkbook.Worksheets("DataFx").Range(Cells(iRow, 3), Cells(irowEnd, 3))
.Value = .Value
End With
Application.ScreenUpdating = True 'включаем обновление экрана
End Sub
Sub testTime_1()
'http://www.planetaexcel.ru/forum/index.php?PAGE_NAME=read&FID=8&TID=8239
Dim myArray() As Double 'массив записи времени выполнения макроса
Dim nRow As Integer 'количество строк в массиве, число раз перезапуска макроса
Dim i As Integer 'счётчик цикла
Dim startingRow As Integer 'начальная строка на листе вывода массива
Dim startingColumn As Integer 'начальный столбец на листе вывода массива
Dim t As Variant '
Dim averageValue As Double 'среднее значение в массиве
Application.ThisWorkbook.Worksheets("Settings").Select 'переход на лист "Settings"
'nRow = 10
nRow = Application.ThisWorkbook.Worksheets("Settings").Cells(7, 3).Value '
ReDim myArray(nRow - 1, 0) 'массив индикатора, строк/столбцов
iArrayRow = UBound(myArray, 1) 'размер, количество строк в массиве
iArrayCol = UBound(myArray, 2) 'размер, количество столбцов в массиве
'перезапуск макроса nRow раз
For i = 0 To (nRow - 1)
t = Timer
Call formulaMA_1 'запуск макроса "formulaMA_1"
myArray(i, 0) = (Timer - t) * 1000 'занесение значения в массив
Next i
averageValue = Round(WorksheetFunction.Average(myArray), 6) 'среднее значение, с округлением "6"
startingRow = 5
startingColumn = 1
Application.ThisWorkbook.Worksheets("Bufer").Select 'переход на лист "Bufer"
'вывод ответа на лист "Bufer"
With ThisWorkbook.Worksheets("Bufer")
.Range(.Cells(startingRow, startingColumn), .Cells(startingRow + iArrayRow, _
startingColumn + iArrayCol)).Value = myArray()
End With
'вывод ответа на лист "Bufer"
Application.ThisWorkbook.Worksheets("Bufer").Cells(2, 1).Value = averageValue
Erase myArray() 'удаление массива из памяти
'MsgBox (Timer - t) * 1000 & "msec"
End Sub
.
.
В макросе "formulaMA_1" алгоритм для каждого бара ("Close" котировки) суммирует заданное число предыдущих котировок включая текущую и делит сумму на их число. При помощи цикла.
В макросе "formulaMA_2" алгоритм просто вставляет формулу индикатора в ячейку столбца смежного со столбцом данных и "протягивает" формулу до конца диапазона данных. Затем заменяет формулы в ячейках на их значения, для уменьшения "тяжести" файла. Достаточно универсальный способ для тех кто не желает слишком копаться в макросах VBA.
В общем виде формула среднего за период 10 котировок в макросе выглядит так: = "=ROUND(AVERAGE(R[-9]C[-1]:RC[-1]),5)" . Для того чтобы иметь возможность быстро менять период индикатора нужно ввести изменяемую переменную, тогда формула будет выглядеть так: = "=ROUND(AVERAGE(R[" & -(sizeAverage - 1) & "]C[-1]:RC[-1]),5)". Получить текст любой другой формулы для вставки в макрос "formulaMA_2" можно при помощи макро-рекордера Excel. Делается это так - набираем формулу в ячейке листа Excel, в ленте панели инструментов выбираем: "Вид > Макросы > Запись макроса". Переходим на ячейку введённой формулы и нажимаем Enter. Можно выключать макро-рекордер "Вид > Макросы > Остановить запись". Нажимаем правой кнопкой мыши на ярлычке любого листа Книги и выбираем "Исходный текст", откроется окно редактора, в левой части навигации появится Модуль ("Module1"), в котором и будет записан макрос с текстом формулы. Если в книге уже были другие модули, вновь записанный макро-рекордером макрос обычно появляется в в модуле под последним номером, нужно поискать.
Ссылка на скачивание файла "formulaMA.xlsm" в архиве: (formulaMA.rar) , 210 KB.
Для оценки скорости выполнения вариантов расчёта написал макрос "testTime_1" по мотивам обсуждения в ветке (www.planetaexcel.ru).
Макрос тестирования времени выполнения перезапускает макрос расчёта индикатора заданное число раз и заносит время выполнения в массив, затем вычисляет среднее время и выводит данные на лист "Bufer". Макросы "testTime_1" и "testTime_2" отличаются только запускаемым макросом "formulaMA_1" или "formulaMA_2", просто для удобства - чтобы не лезть в текст менять при тестах.
Как и предполагалось, вариант расчёта через формулу оказался быстрее чем месить данные циклом. Примерно в два раза.
На системе x64 в Excel 2010 x64 (база данных - 6000 котировок) у меня получились следующие цифры:
Период индикатора
"formulaMA_1" Время, msec
"formulaMA_2" Время, msec
5
173.886719
172.910156
173.691406
92.109375
92.5
92.1875
10
177.1875
175.195312
173.066406
90.058594
90.078125
90.527344
20
178.28125
182.304688
183.359375
90.46875
89.140625
89.257812
Макрос тестирования времени выполнения запускался по три раза для каждого варианта расчёта индикатора и каждого периода рассчитываемого индикатора. Среднее время на числе в 100 перезапусков макроса.
Вот так примерно выглядит массив из 100 перезапусков, с выбросами в зависимости от текущей загрузки Системы процессами:
.
[Spoiler (click to open)].
Sub formulaMA_1()
'макрос расчёта индикатора "MovingAverage"
'объявление переменных
Dim dataArray() As Double 'массив данных
Dim maArray() As Double 'массив индикатора
Dim CloseValue As Double 'значение котировки
Dim Sum As Double 'сумма котировок за период
Dim mAverage As Double 'средняя котировка за период
Dim irowStart As Long 'номер первой заполненной строки данных листа "DataFx" с которой будет произведён расчёт индикатора
Dim irowEnd As Long 'номер последней заполненной строки данных в столбце тикера листа "DataFx"
Dim nRow As Long 'количество строк данных
Dim iArrayRow As Long 'количество строк в массиве
Dim iArrayCol As Integer 'количество столбцов в массиве
Dim sizeAverage As Integer 'период индикатора MovingAverage
Dim startingRow As Long 'начальная строка на листе вывода массива
Dim startingColumn As Integer 'начальный столбец на листе вывода массива
Dim i, k As Long 'счётчики циклов
'On Error Resume Next'переход к следующему шагу при ошибке в коде
Application.Calculation = xlManual 'выключаем автоматический пересчёт в книге
Application.ScreenUpdating = False 'отключаем обновление экрана
Application.ThisWorkbook.Worksheets("Settings").Select 'переход на лист "Settings"
'irowStart = 2 'номер строки данных листа "DataFx" с которой будет начат расчёт индикатора
'sizeAverage = 10 'период индикатора "MovingAverage"
irowStart = Application.ThisWorkbook.Worksheets("Settings").Cells(4, 3).Value '
sizeAverage = Application.ThisWorkbook.Worksheets("Settings").Cells(5, 3).Value '
Application.ThisWorkbook.Worksheets("DataFx").Select 'переход на лист "DataFx"
'определение номера последней заполненной строки данных в столбце тикера листа "DataFx"
irowEnd = Application.ThisWorkbook.Worksheets("DataFx").Columns("A").Find(What:="*", _
LookIn:=xlValues, SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
'
nRow = (irowEnd - irowStart) 'количество строк данных
'задание размерности массивов
ReDim dataArray(nRow, 0) 'массив данных, строк/столбцов
ReDim maArray(nRow, 0) 'массив индикатора, строк/столбцов
iArrayRow = UBound(maArray, 1) 'размер, количество строк в массиве
iArrayCol = UBound(maArray, 2) 'размер, количество столбцов в массиве
'заполнение массива в памяти данными с листа
For i = 0 To iArrayRow Step 1
CloseValue = Application.ThisWorkbook.Worksheets("DataFx").Cells(irowStart + i, 2).Value 'котировка
dataArray(i, 0) = CloseValue 'занесение в массив
Next i
'вычисление значения индикатора
k = sizeAverage - 1 '
Do While k <= iArrayRow 'выполнять пока верно условие, т.е. до конца массива данных
Sum = 0 'начальное значение
mAverage = 0 'начальное значение
For i = sizeAverage - 1 To 0 Step -1 'цикл суммирования в соотв. с величиной периода индикатора
CloseValue = dataArray(k - i, 0) 'котировка
Sum = Sum + CloseValue 'сумма котировок
Next i
mAverage = Round(Sum / sizeAverage, 5) 'среднее значение котировки за период
If k >= sizeAverage - 1 Then maArray(k, 0) = mAverage 'занесение результата в массив, с проверкой периода
k = k + 1 'увеличение позиции счётчика
Loop
Application.ThisWorkbook.Worksheets("DataFx").Select 'переход на лист
startingRow = irowStart
startingColumn = 3
Call ClearDataSheets 'запуск макроса удаления результатов прежних расчётов с листа "DataFx"
'вывод ответа на лист "DataFx"
With ThisWorkbook.Worksheets("DataFx")
.Range(.Cells(startingRow, startingColumn), .Cells(startingRow + iArrayRow, _
startingColumn + iArrayCol)).Value = maArray()
End With
Erase dataArray() 'удаление массива из памяти
Erase maArray() 'удаление массива из памяти
Application.Calculation = xlAutomatic 'включаем автоматический пересчёт в книге
Application.ScreenUpdating = True 'включаем обновление экрана
End Sub
Sub formulaMA_2()
''макрос расчёта индикатора "MovingAverage", вариант 2
'объявление переменных
Dim irowStart As Long 'номер первой заполненной строки данных листа "DataFx" с которой будет произведён расчёт индикатора
Dim sizeAverage As Integer 'период индикатора MovingAverage
Dim irowEnd As Long 'номер последней заполненной строки данных в столбце тикера листа "DataFx"
Dim iRow As Long 'первая строка вставки формулы
Application.Calculation = xlManual 'выключаем автоматический пересчёт в книге
Application.ScreenUpdating = False 'отключаем обновление экрана
Application.ThisWorkbook.Worksheets("Settings").Select 'переход на лист "Settings"
'irowStart = 2 'номер строки данных листа "DataFx" с которой будет начат расчёт индикатора
'sizeAverage = 10 'период индикатора "MovingAverage"
irowStart = Application.ThisWorkbook.Worksheets("Settings").Cells(4, 3).Value '
sizeAverage = Application.ThisWorkbook.Worksheets("Settings").Cells(5, 3).Value '
Application.ThisWorkbook.Worksheets("DataFx").Select 'переход на лист "DataFx"
Call ClearDataSheets 'запуск макроса удаления результатов прежних расчётов с листа "DataFx"
'определение номера последней заполненной строки данных в столбце тикера листа "DataFx"
irowEnd = Application.ThisWorkbook.Worksheets("DataFx").Columns("A").Find(What:="*", _
LookIn:=xlValues, SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
'
iRow = (irowStart + sizeAverage - 1)
Application.ThisWorkbook.Worksheets("DataFx").Cells(iRow, 3).Select 'переход на лист "DataFx"
'вставка формулы в первую ячейку диапазона расчёта
'ActiveCell.FormulaR1C1 = "=ROUND(AVERAGE(R[-9]C[-1]:RC[-1]),5)"
ActiveCell.FormulaR1C1 = "=ROUND(AVERAGE(R[" & -(sizeAverage - 1) & "]C[-1]:RC[-1]),5)"
'протягивание формул до конца диапазона данных
Selection.AutoFill Destination:=Application.ThisWorkbook.Worksheets("DataFx").Range(Cells(iRow, 3), Cells(irowEnd, 3))
Application.Calculation = xlAutomatic 'включаем автоматический пересчёт в книге
'замена формул в ячейках на значения
With Application.ThisWorkbook.Worksheets("DataFx").Range(Cells(iRow, 3), Cells(irowEnd, 3))
.Value = .Value
End With
Application.ScreenUpdating = True 'включаем обновление экрана
End Sub
Sub testTime_1()
'http://www.planetaexcel.ru/forum/index.php?PAGE_NAME=read&FID=8&TID=8239
Dim myArray() As Double 'массив записи времени выполнения макроса
Dim nRow As Integer 'количество строк в массиве, число раз перезапуска макроса
Dim i As Integer 'счётчик цикла
Dim startingRow As Integer 'начальная строка на листе вывода массива
Dim startingColumn As Integer 'начальный столбец на листе вывода массива
Dim t As Variant '
Dim averageValue As Double 'среднее значение в массиве
Application.ThisWorkbook.Worksheets("Settings").Select 'переход на лист "Settings"
'nRow = 10
nRow = Application.ThisWorkbook.Worksheets("Settings").Cells(7, 3).Value '
ReDim myArray(nRow - 1, 0) 'массив индикатора, строк/столбцов
iArrayRow = UBound(myArray, 1) 'размер, количество строк в массиве
iArrayCol = UBound(myArray, 2) 'размер, количество столбцов в массиве
'перезапуск макроса nRow раз
For i = 0 To (nRow - 1)
t = Timer
Call formulaMA_1 'запуск макроса "formulaMA_1"
myArray(i, 0) = (Timer - t) * 1000 'занесение значения в массив
Next i
averageValue = Round(WorksheetFunction.Average(myArray), 6) 'среднее значение, с округлением "6"
startingRow = 5
startingColumn = 1
Application.ThisWorkbook.Worksheets("Bufer").Select 'переход на лист "Bufer"
'вывод ответа на лист "Bufer"
With ThisWorkbook.Worksheets("Bufer")
.Range(.Cells(startingRow, startingColumn), .Cells(startingRow + iArrayRow, _
startingColumn + iArrayCol)).Value = myArray()
End With
'вывод ответа на лист "Bufer"
Application.ThisWorkbook.Worksheets("Bufer").Cells(2, 1).Value = averageValue
Erase myArray() 'удаление массива из памяти
'MsgBox (Timer - t) * 1000 & "msec"
End Sub
.
.