moving average on excel

Discussion in 'Automated Trading' started by travis, Apr 23, 2010.

  1. travis

    travis

    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.
     
  2. 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
     
  3. travis

    travis

    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.
     
  4. 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.
     
  5. 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.
     
  6. bellman

    bellman

    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.
     
  7. travis

    travis

    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.
     
    • ma.xls
      File size:
      49 KB
      Views:
      152
  8. travis

    travis

    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.
     
    • ma.xls
      File size:
      44.5 KB
      Views:
      57
  9. travis

    travis

    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
     
  10. byteme

    byteme

    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.
     
    #10     Apr 25, 2010