moving average on excel

I have an automated self-updating moving average, which basically is the average of the prices of the last x minutes, polled once per minute.

So this means the macros print price once every minute.

Someone else had written these macros for me years ago, and until now, being an amateur VBA programmer, I still haven't fully understood it.

This would not be a problem, because they work but the reason I want to understand them is to simplify them, because I started off with just one moving average and now I will need about 13 of them, on different markets.

Here's the macros:

1) Everything starts from this macro, which gets started by "ThisWorkbook":

Sub ma_timer()
For i = 1 To 500
ma.Cells(i, 1) = tickers.Cells(12, 20)
Next
TimerOn 60000
End Sub

And this is clear but I don't know what happens next

2) Then we get on another sheet, where I have all these macros:

Private Declare Function SetTimer Lib "User32" _
(ByVal hWnd As Long, ByVal nIDEvent As Long, _
ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long

Private Declare Function KillTimer Lib "User32" _
(ByVal hWnd As Long, ByVal nIDEvent As Long) As Long

Dim averageprice(2) As Double
Dim minutes As Integer
Dim TimerID As Long


Public Sub TimerOn(Interval As Long)
On Error Resume Next
TimerID = SetTimer(0, 0, Interval_eur, AddressOf myroutine)
End Sub

Private Sub myroutine()
On Error Resume Next
minutes = minutes + 1
averageprice(0) = averageprice(0) + tickers.Cells(12, 20)

If minutes = 1 Then
For i = 1 To 499

ma.Cells(i, 1) = ma.Cells(i + 1, 1)

Next
meu.Cells(500, 1) = averageprice(0) / minuti


Erase averageprice
minutes = 0

End If

End Sub



Please let me know if you can help me. It works but I don't know why, and I would like to understand it in order to simplify it and not have these macros multiplied by 13, because it would be too heavy for my CPU.

 

Hi,
For under $100 XLQ provides functions to pull stock/ETF data from a variety of sources (Yahoo, MSN, Google, IB, etc.) as well as providing functions for various moving averages. That's what I use, it's available from qmatix.com

 

Thanks for the information, but this is not what I wanted to do. I didn't get to this point, to just quit using my excel sheet and start something totally new.

 

Sub ma_timer()
For i = 1 To 500
ma.Cells(i, 1) = tickers.Cells(12, 20)
Next
TimerOn 60000
End Sub

Your module streams in 1 ticker at Cells(12,20) and updates then spits out the ma at Cells(i,1).

I won't do all of the work for you , but all you basically need to do is
read different tickers into different cells..
i.e. tickers.Cells(12,20) is streaming 1
tickers2.Cells(13,20) is streaming 2
...
tickersN.Cells(N+12,20) is streaming N

Spit out ma.Cells(i,1)=tickers.Cells(12,20)
ma1.Cells(i,2)=tickers2.Cells(13,20)
..
maN.Cells(i,N)=tickersN.Cells(N+12,20)


You also need to find where the streaming interface tells to pull into
Cells(x,y), and modify it to stream into the aforementioned cells.

You have to be careful about timing, because doing one at a time in loops may have interval lags.

You could look into running the same idea as an array; you'd need to find out how you interface to your provider and how to pull in arrays of data, rather than single quotes.

Hope that helps you. Please don't PM on this one, as I don't have a lot of time to work on this at the moment. Easier to reply on the thread, as everyone learns.

 

I neglected to mention that XLQ provides functions available in Excel, so you wouldn't have to stop using your excel sheet. But I understand. I initially looked into to pulling the data myself but just didn't want to put in the time.

 

I'm in a somewhat similar situation although I am using software, Qlink, to feed an array of data into a sheet, then pulling that array into VBA on with a timer loop.

Unfortunately for me the Qlink module is locked, but I am curious how your data feed interfaces with excel.

 

Thanks to dtrader98 and to dwpeters, for the thoughtful advice and feedback.

To bellman and to them I say this. I will now spend a few hours until I figure it all out, and then come back and post an explanation of what my macros do, and which ones I can get rid of and simplify.

So far this is the excel workbook I am working on (see attachment below). It's a simplified version of my systems, which will help me to work specifically on just the moving average.

I am using an excel workbook that uses time instead of price, because it's the weekend so there's no tickers moving. It won't make much difference.

I've identified 4 steps (the 4 macros). Most likely I'll be able to get rid of half of this stuff (one line at a time while verifying that everything still works properly), which will then enable me to simplify the macros and apply them to 13 moving averages, instead of the way it is now where I have these 4 macros multiplied by 13 times (the 13 futures).

This code works perfectly, but as i said the problem is that it's still too complex.

 

It works!

Yes!!!

I figured it out, little stupid me...

Here it is, simplified as much as I could:

Private Declare Function SetTimer Lib "User32" (ByVal hWnd As Long, ByVal nIDEvent As Long, ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long

Sub time_ma()
For i = 1 To 500
ma.Cells(i, 1) = time
Next
TimerID = SetTimer(0, 0, 15000, AddressOf myroutine)
End Sub

Sub myroutine()
On Error Resume Next
For i = 1 To 499
ma.Cells(i, 1) = ma.Cells(i + 1, 1)
Next
ma.Cells(500, 1) = time
End Sub

This link has helped me a lot:
http://vmd.myxomop.com/apires/ref/s/settimer.html

Also, I learned by trial and error.

Now I'll finally be able to implement 13 columns like this one on just one worksheet, and the whole thing will be triggered by just 2 functions instead of 26 functions and 13 sheets.

Here's the final file. I had it run every 15 seconds so I could test its efficacy and debug it a little faster.

 

Ok, done. I've copied the whole thing on the paper trading excel workbook, and tomorrow I'll see if it all works out perfectly.

Here's the final code for 9 moving averages.

To explain it out loud to myself, here's what happens.

Private Declare Function...
It uses a function unknown to excel so that it has to call it up from somewhere within windows (excuse my ignorance, I just learned it's called "Windows API"). See here regarding this function:
http://vmd.myxomop.com/apires/ref/s/settimer.html
http://en.wikipedia.org/wiki/Windows_API
http://en.wikipedia.org/wiki/Subroutine

Once the SetTimer function is called (via "private declare function..."), it is used immediately in the first subroutine, "mav", which is actually triggered by a Workbook_Open() subroutine, which I didn't list here because it is simple and it is contained by ThisWorkbook, whereas all the code below is within a module inside the "modules" section (vba menu within excel).


Sub mav()...
In Sub "mav" I do 2 things:

1) I set all cells in my future x-minute price prints to the present price, because in case I need that average sooner than it will be ready, at least it will only be affected by the price at which it was started and not by values of zero or values from previous days

2) I start the timer, telling it to run every x milliseconds (I will need to set it to 5 minutes, I'll do that next)


Sub myroutine()...
Finally here at the last subroutine. This one gets executed every x seconds/minutes as decided by the previous subroutine. Here I do two things:

1) I copy each value of the column to the row above, so everything moves higher by one row, and I keep a record of the previously recorded prices

2) I print the present price (at the time the subroutine is executed) on the last and 500th row of the column. It will be later copied one cell higher and that cell will be available again.


Private Declare Function SetTimer Lib "User32" _
(ByVal hWnd As Long, ByVal nIDEvent As Long, ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long


Sub mav()
For i = 1 To 500
ma.Cells(i, 1) = Sheet1.Cells(12, 20)
ma.Cells(i, 2) = Sheet1.Cells(13, 20)
ma.Cells(i, 3) = Sheet1.Cells(14, 20)
ma.Cells(i, 4) = Sheet1.Cells(15, 20)
ma.Cells(i, 5) = Sheet1.Cells(16, 20)
ma.Cells(i, 6) = Sheet1.Cells(17, 20)
ma.Cells(i, 7) = Sheet1.Cells(18, 20)
ma.Cells(i, 8) = Sheet1.Cells(19, 20)
ma.Cells(i, 9) = Sheet1.Cells(20, 20)
Next
TimerID = SetTimer(0, 0, 15000, AddressOf myroutine)
End Sub


Sub myroutine()
On Error Resume Next
For i = 1 To 499
ma.Cells(i, 1) = ma.Cells(i + 1, 1)
ma.Cells(i, 2) = ma.Cells(i + 1, 2)
ma.Cells(i, 3) = ma.Cells(i + 1, 3)
ma.Cells(i, 4) = ma.Cells(i + 1, 4)
ma.Cells(i, 5) = ma.Cells(i + 1, 5)
ma.Cells(i, 6) = ma.Cells(i + 1, 6)
ma.Cells(i, 7) = ma.Cells(i + 1, 7)
ma.Cells(i, 8) = ma.Cells(i + 1, 8)
ma.Cells(i, 9) = ma.Cells(i + 1, 9)
Next
ma.Cells(500, 1) = Sheet1.Cells(12, 20)
ma.Cells(500, 2) = Sheet1.Cells(13, 20)
ma.Cells(500, 3) = Sheet1.Cells(14, 20)
ma.Cells(500, 4) = Sheet1.Cells(15, 20)
ma.Cells(500, 5) = Sheet1.Cells(16, 20)
ma.Cells(500, 6) = Sheet1.Cells(17, 20)
ma.Cells(500, 7) = Sheet1.Cells(18, 20)
ma.Cells(500, 8) = Sheet1.Cells(19, 20)
ma.Cells(500, 9) = Sheet1.Cells(20, 20)
End Sub

 

There's a lot of scope for re-factoring the code. You could start by removing the repetitive code and replacing it with some loops:

Code:

Sub mav()
    For i = 1 To 500
		For j = 1 To NoOfSymbols
			ma.Cells(i, j) = Sheet1.Cells(11 + j, 20)
        Next
    Next
    TimerID = SetTimer(0, 0, 15000, AddressOf myroutine)
End Sub


Sub myroutine()
    On Error Resume Next
	
    For i = 1 To 499
		For j = 1 To NoOfSymbols
			ma.Cells(i, j) = ma.Cells(i + 1, j)
		Next
    Next
	
	For j = 1 To NoOfSymbols
		ma.Cells(500, j) = Sheet1.Cells(11 + j, 20)
	Next
End Sub

Obviously you'll have to declare NoOfSymbols somewhere and set it to 9 but now you can change the number of symbols just by changing that declaration. Perhaps you can even read the value from a cell on your spreadsheet so all you have to do is change the cell.

"myroutine" isn't a very good name for a routine. Perhaps rename it to something more meaningful - what is that routine doing exactly?

What is the 500? Is that the number of periods? That should also be factored out as a variable or constant declared somewhere.

There is still some repetition of code in the version I posted so there's still room for improvement.