Exponential moving average using array in Excel VBA

S

Super P

New Member
Joined
May 22, 2021
Messages
28
Office Version
  1. 365
Platform
  1. Windows
The array VBA code below calculates 9 periods exponential moving average of the values in Column U (from row 31 to last row ~row 6983). The output will be in Column V (from row 39 to last row). he problem now the second output value is 39 rows below i.e. cell V78, this should be located in cell V40, appreciate any VBA expert help, thanks

VBA Code: 
Option Explicit

Sub EMA9()
   
    Dim valArray As Variant
    Dim runSum, EMA9() As Double
    Dim i, lastRow, lRow, firstRow, x1, x2, iPeriod, iCol As Long

    'Step1 - set last row and reference range to calculate
    With Worksheets("Sheet1")
      lastRow = .Cells(Rows.Count, 1).End(xlUp).Row
      valArray = .Range(.Cells(1, 21), .Cells(lastRow, 21)).Value2
    End With
   
    'Step2 - set lower and upper limit
    iPeriod = 9
    x1 = 2 / (iPeriod + 1)
    x2 = 1 - (2 / (iPeriod + 1))
    ReDim EMA9(LBound(valArray, 1) - 38 To UBound(valArray, 1), 1 To 1)
   
    'Step3 - calculate first row value, [sum (row 31 to row 39)]
    firstRow = 31
    runSum = 0
    For i = firstRow To (iPeriod + 30)
      runSum = runSum + valArray(i, 1)
    Next
   
    'Step4 - calculate first row average value, [average (row 31 to row 39)]
    EMA9(1, 1) = runSum / iPeriod
   
    'Step5 - calculate the 2nd row (row 32) value onwards, [current value of valArray * x1 + previous value of EMA9 * x2]
    For i = (iPeriod + firstRow) To UBound(valArray, 1)
        EMA9(i, 1) = valArray(i, 1) * x1 + EMA9(i - 1, 1) * x2
    Next
   
    'Step6 - write the values to worksheet
    iCol = 22
    With Worksheets("Sheet1")
        .Range(.Cells((iPeriod - 8), iCol), .Cells(lastRow, iCol)).Value2 = EMA9
    End With
   
    'Step7 - clear memory
    Erase valArray: Erase EMA9

End Sub

1648379455985.png
 
Last edited by a moderator:

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
i have no idea what you are calculating.
starting row 31, the average of this row+9 next
then something ...
Why don't you do it in the same array, append some additional columns to valarray for calculation, so that you don't have problems with pointers.

Afterwards, copying (part of) the 3rd column to your sheet is peanuts.
VBA Code: 
Sub EMA9()

     Dim valArray() As Variant
     Dim runSum, EMA9() As Double
     Dim i, lastRow, lRow, firstRow, x1, x2, iPeriod, iCol As Long, c, i1, i2

     'Step1 - set last row and reference range to calculate
     With Worksheets("Sheet1")
          lastRow = .Cells(Rows.Count, 1).End(xlUp).Row
          Set c = .Range(.Cells(1, 21), .Cells(lastRow, 21))    'your range
          valArray = c.Value2                                   'range to array
          ReDim Preserve valArray(1 To UBound(valArray), 1 To 3)     'add 2 additional columns to the array
     End With

     'Step2 - set lower and upper limit
     firstRow = 31
     iPeriod = 9
     x1 = 2 / (iPeriod + 1)
     x2 = 1 - (2 / (iPeriod + 1))

     'Step3 - calculate first row value, [sum (row 31 to row 39)]

     For i = firstRow To UBound(valArray) - iPeriod + 1
          For i1 = 0 To iPeriod - 1
               valArray(i, 2) = valArray(i, 2) + valArray(i, 1)     'step 3 = in the 2nd column  = sum of 9 elements
          Next
          valArray(i, 2) = valArray(i, 2) / iPeriod            'step 4 = average

          For i2 = i + 1 To UBound(valArray)
               valArray(i, 3) = valArray(i, 1) * x1 + valArray(i - 1, 2) * x2 'in the 3rd column, something
          Next
     Next

     c.Offset(, 3).Resize(UBound(valArray), 3).Value = valArray 'for testing write it 3 columns to the right
     
     'Step7 - clear memory
     Erase valArray
End Sub
 
Upvote 0
BSALV said:
i have no idea what you are calculating.
starting row 31, the average of this row+9 next
then something ...
Why don't you do it in the same array, append some additional columns to valarray for calculation, so that you don't have problems with pointers.

Afterwards, copying (part of) the 3rd column to your sheet is peanuts.
VBA Code: 
Sub EMA9()

     Dim valArray() As Variant
     Dim runSum, EMA9() As Double
     Dim i, lastRow, lRow, firstRow, x1, x2, iPeriod, iCol As Long, c, i1, i2

     'Step1 - set last row and reference range to calculate
     With Worksheets("Sheet1")
          lastRow = .Cells(Rows.Count, 1).End(xlUp).Row
          Set c = .Range(.Cells(1, 21), .Cells(lastRow, 21))    'your range
          valArray = c.Value2                                   'range to array
          ReDim Preserve valArray(1 To UBound(valArray), 1 To 3)     'add 2 additional columns to the array
     End With

     'Step2 - set lower and upper limit
     firstRow = 31
     iPeriod = 9
     x1 = 2 / (iPeriod + 1)
     x2 = 1 - (2 / (iPeriod + 1))

     'Step3 - calculate first row value, [sum (row 31 to row 39)]

     For i = firstRow To UBound(valArray) - iPeriod + 1
          For i1 = 0 To iPeriod - 1
               valArray(i, 2) = valArray(i, 2) + valArray(i, 1)     'step 3 = in the 2nd column  = sum of 9 elements
          Next
          valArray(i, 2) = valArray(i, 2) / iPeriod            'step 4 = average

          For i2 = i + 1 To UBound(valArray)
               valArray(i, 3) = valArray(i, 1) * x1 + valArray(i - 1, 2) * x2 'in the 3rd column, something
          Next
     Next

     c.Offset(, 3).Resize(UBound(valArray), 3).Value = valArray 'for testing write it 3 columns to the right
    
     'Step7 - clear memory
     Erase valArray
End Sub
Click to expand...
@BSALV many thanks!!, the reason it starts in row 31 is because there are moving averages calculation started in 7 period, then 12 period, etc. as screenshot1 below

screenshot1
1648408245942.png


I tried to run your code and have 3 comments as per screenshot2 below
1. The output stops in row 6975, the output should be up to lastrow i.e. in this instance up to row 6983
2. The first cell output should start in row 39, formula for first cell output (V39) is average of MACD range (U31:U39)
3. The second cell output should be in row 40, and so on... formula for second cell output (V40) is U40 * x1 + V39 * x2, where x1 and x2 defined in the code. succeeding cell output until last row will be similar formula as second cell output.

Is it possible that all of the above can be done in a single column output?

Thanks.

Screenshot2
1648408402492.png
 
Upvote 0
you hope this calculation is right, it's a guess.
The blue part is just to fill the range
Rich (BB code): 
Sub EMA9()

     Dim valArray(), i, lastRow, x1, x2, iPeriod, c

     'Step1 - set last row and reference range to calculate
     With Worksheets("Sheet1")

          With Range("U1:U6000")                                'just for demonstration, fill 6.000 cells with a random value
               .Formula = "=rand()"
               .Value = .Value
          End With
          .Range("A6000").Value = "x"                           ' to mark the last row, fill here something

          lastRow = .Cells(Rows.Count, 1).End(xlUp).Row
          Set c = .Range(.Cells(1, 21), .Cells(lastRow, 21))    'your range
          MsgBox c.Address
     End With

     valArray = c.Value2                                        'range to array
     ReDim Preserve valArray(1 To UBound(valArray), 1 To 3)     'add 2 additional columns to the array

     iPeriod = 9
     x1 = 2 / (iPeriod + 1)
     x2 = 1 - (2 / (iPeriod + 1))
     For i = 40 To UBound(valArray)
          valArray(i, 3) = valArray(i, 1) * x1 + valArray(i - 1, 3) * x2     'in the 3rd column, 80% of previous value + 20% of new value
     Next

     c.Offset(, -1).Resize(UBound(valArray), 1).Value = Application.Index(valArray, 0, 3)     'write in the T-column your 3rd column of valArray

     Erase valArray
End Sub
 
Upvote 0
your 1st question isn't fulfilled, i know, because i didn't understand that one.
How can you add 9 extra's if there is no data.
Can you modify my example how it should look like ?
Column X is the same with formulas, modify them so that i understand you.
example file
 
Upvote 0
BSALV said:
your 1st question isn't fulfilled, i know, because i didn't understand that one.
How can you add 9 extra's if there is no data.
Can you modify my example how it should look like ?
Column X is the same with formulas, modify them so that i understand you.
example file
Click to expand...
@BSALV thanks appreciate your time to help, apologies if there are any confusion, I think the blank cells should be the other way around i.e. that should be on the top of the range.

Reference to sreenshot1 below, the first 8 cells (V31:V38) should be blank, since the 9th period will only start in the 9th cell, therefore the first output should start in cell (V39), average of cells (V31:V39).

Reference to sreenshot1 below, to explain my request in a simple way, I have set aside the factors "x1" and "x2". Thus, the 2nd and succeeding output value should be current row value of the dataset minus previous row value of the output i.e. V40 = U40 + V39, V41 = U41 + V40,......... V6983 = U6983 + V6982

Reference to screenshot2 below, using my original code (first/my post), if I changed a code under step5 (changes in bold),

'Step5 - calculate the 2nd row (row 32) value onwards, [current value of valArray * x1 + previous value of EMA9 * x2]
For i = (iPeriod + firstRow) To UBound(valArray, 1)
EMA9(i, 1) = valArray(i, 1) * x1 + EMA9(i - 39, 1) * x2
Next

I am getting all the output values correctly. The problem is the position of the 2nd and subsequent outputs are incorrect. The 2nd output was placed in row 78 (V78), this should be in row 40 (V40). Can you be able to find out the mistake in my original code or if additional code is required e.g. resize, redim again, etc.. The expected position are in column W.

Many thanks!!

screenshot1
1648443469825.png


screenshot 2
1648445061948.png
 
Upvote 0
Super P said:
@BSALV thanks appreciate your time to help, apologies if there are any confusion, I think the blank cells should be the other way around i.e. that should be on the top of the range.

Reference to sreenshot1 below, the first 8 cells (V31:V38) should be blank, since the 9th period will only start in the 9th cell, therefore the first output should start in cell (V39), average of cells (V31:V39).

Reference to sreenshot1 below, to explain my request in a simple way, I have set aside the factors "x1" and "x2". Thus, the 2nd and succeeding output value should be current row value of the dataset minus previous row value of the output i.e. V40 = U40 + V39, V41 = U41 + V40,......... V6983 = U6983 + V6982

Reference to screenshot2 below, using my original code (first/my post), if I changed a code under step5 (changes in bold),

'Step5 - calculate the 2nd row (row 32) value onwards, [current value of valArray * x1 + previous value of EMA9 * x2]
For i = (iPeriod + firstRow) To UBound(valArray, 1)
EMA9(i, 1) = valArray(i, 1) * x1 + EMA9(i - 39, 1) * x2
Next

I am getting all the output values correctly. The problem is the position of the 2nd and subsequent outputs are incorrect. The 2nd output was placed in row 78 (V78), this should be in row 40 (V40). Can you be able to find out the mistake in my original code or if additional code is required e.g. resize, redim again, etc.. The expected position are in column W.

Many thanks!!

screenshot1
View attachment 61038

screenshot 2
View attachment 61039
Click to expand...
@BSALV I already solved the issue

I just need to minus 38 as below

'Step5 - calculate the 2nd row (row 32) value onwards, [current value of valArray * x1 + previous value of EMA9 * x2]
For i = (iPeriod + firstRow) To UBound(valArray, 1)
EMA9(i - 38, 1) = valArray(i, 1) * x1 + EMA9(i - 39, 1) * x2
Next

Thanks for the help!!!
 
Upvote 0
Super P said:
@BSALV I already solved the issue

I just need to minus 38 as below

'Step5 - calculate the 2nd row (row 32) value onwards, [current value of valArray * x1 + previous value of EMA9 * x2]
For i = (iPeriod + firstRow) To UBound(valArray, 1)
EMA9(i - 38, 1) = valArray(i, 1) * x1 + EMA9(i - 39, 1) * x2
Next

Thanks for the help!!!
Click to expand...
@BSALV apologies there are still issue, the output (zero/0) includes row 1 to row 38 (V1:V38), any idea what to amend/change in the code so that it will not write to those range? The option of letting it write then after delete is not preferred, I have a header in row 5 (V5).

screenshot3
1648458279047.png
 
Upvote 0
you make it yourself difficult, write your results in the same array, like i showed you yesterday.
Then, you can copy that entire 2nd column to the RHS or you can copy a part of that 2nd column but then 38 rows lower and ...
VBA Code: 
Sub fill_Data()

     inumber = 6000
     Worksheets("Sheet1").Range("U1").Resize(inumber) = WorksheetFunction.RandArray(inumber)     'just for demonstration, fill 6.000 cells with a random value

End Sub

Sub EMA_9()
   
     With Sheets("sheet1")
          With .Range(.Range("U1"), .Range("U" & Rows.Count).End(xlUp))     'just for demonstration, fill 6.000 cells with a random value
               valArray = .Value2                               'range to array
               ReDim Preserve valArray(1 To UBound(valArray), 1 To 2)     'add additional column to the array

     '1st part = calculation *******************************************************************************
               iRow1 = 39
               iPeriod = 9
               x1 = 2 / (iPeriod + 1)
               x2 = 1 - (2 / (iPeriod + 1))

               For i = iRow1 To UBound(valArray)                'calculate values in the 2nd column of that array starting with irow1 until end
                    If i = iRow1 Then                           '1st period is the average of this + 8 preceeding periods
                         For i1 = i - iPeriod + 1 To i
                              valArray(i, 2) = valArray(i, 2) + valArray(i, 1) / iPeriod
                         Next
                    Else
                         valArray(i, 2) = valArray(i, 1) * x1 + valArray(i - 1, 2) * x2     'next period = average preceeding period * x2 + actual value * x1
                    End If
               Next

     '2nd part : copying ************************************************************************************
     '1st method = copy the whole new column, 2 columns to the right = column W
               .Offset(, 2).Value = Application.Index(valArray, 0, 2)

     '2nd method = copy only that calculated part 39-6000 to a certain address in column Y
               MyRows = Evaluate("=row(a" & iRow1 & ":a" & UBound(valArray) & ")")     'make in an array an incrementing serie of integer numbers starting with row1 (=39) until ubound(valarray) (=6000)
               .Offset(, 4).ClearContents                       'make that range 4 columns to the right empty as precaution
               .Offset(iRow1 - 1, 4).Resize(UBound(MyRows)).Value = Application.Index(valArray, MyRows, 2)     ' copy that part of the 2nd column of the array to the cell 4 columns to the right and (row1-1) rows lower

          End With
     End With
     
     Erase valArray
End Sub
 
Upvote 0
Solution
BSALV said:
you make it yourself difficult, write your results in the same array, like i showed you yesterday.
Then, you can copy that entire 2nd column to the RHS or you can copy a part of that 2nd column but then 38 rows lower and ...
VBA Code: 
Sub fill_Data()

     inumber = 6000
     Worksheets("Sheet1").Range("U1").Resize(inumber) = WorksheetFunction.RandArray(inumber)     'just for demonstration, fill 6.000 cells with a random value

End Sub

Sub EMA_9()
  
     With Sheets("sheet1")
          With .Range(.Range("U1"), .Range("U" & Rows.Count).End(xlUp))     'just for demonstration, fill 6.000 cells with a random value
               valArray = .Value2                               'range to array
               ReDim Preserve valArray(1 To UBound(valArray), 1 To 2)     'add additional column to the array

     '1st part = calculation *******************************************************************************
               iRow1 = 39
               iPeriod = 9
               x1 = 2 / (iPeriod + 1)
               x2 = 1 - (2 / (iPeriod + 1))

               For i = iRow1 To UBound(valArray)                'calculate values in the 2nd column of that array starting with irow1 until end
                    If i = iRow1 Then                           '1st period is the average of this + 8 preceeding periods
                         For i1 = i - iPeriod + 1 To i
                              valArray(i, 2) = valArray(i, 2) + valArray(i, 1) / iPeriod
                         Next
                    Else
                         valArray(i, 2) = valArray(i, 1) * x1 + valArray(i - 1, 2) * x2     'next period = average preceeding period * x2 + actual value * x1
                    End If
               Next

     '2nd part : copying ************************************************************************************
     '1st method = copy the whole new column, 2 columns to the right = column W
               .Offset(, 2).Value = Application.Index(valArray, 0, 2)

     '2nd method = copy only that calculated part 39-6000 to a certain address in column Y
               MyRows = Evaluate("=row(a" & iRow1 & ":a" & UBound(valArray) & ")")     'make in an array an incrementing serie of integer numbers starting with row1 (=39) until ubound(valarray) (=6000)
               .Offset(, 4).ClearContents                       'make that range 4 columns to the right empty as precaution
               .Offset(iRow1 - 1, 4).Resize(UBound(MyRows)).Value = Application.Index(valArray, MyRows, 2)     ' copy that part of the 2nd column of the array to the cell 4 columns to the right and (row1-1) rows lower

          End With
     End With
    
     Erase valArray
End Sub
Click to expand...
@BSALV your a star, many thanks!!, This is beyond my grasp, I will need to study line by line to understand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

mehidy1437
  • Question Question
Requesting for experts comments suggestion on my vba code
Replies
5
Views
883
mehidy1437
mehidy1437
S
  • Question Question
Moving Average VBA
Replies
1
Views
2K
offthelip
O
I
  • Question Question
Is there an easy way to add formulaR1C1 into VBA arrays to speed up calculation time?
Replies
4
Views
1K
ianawwalker
I
A
  • Question Question
Weighted Moving Average Calculation in VBA
Replies
5
Views
1K
artz
A
O
  • Solved
MS Excel VBA Code - Issue with a Simple Array
Replies
2
Views
475
OilEconomist
O

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,636
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top