Smoothed Average runs slow

[A Little Help Here]

This code works but is very slow. The datechange() functions just changes
the date as needed based on holidays. Tested seperately they run fine. The
primary key is for the price data table is the date and symbol fields. The
price data table has roughly 5000 different symbols each with 5 years of
daily prices. Symbol, date, close price. The function will give a smoothed
average based on the symbol, date, and number of days for the average. For
"smoothing" I add 3x's the number of days requested. I assume if I can
shrink the record set before the calculations it will run faster? I am happy
with the output of the function.

Function SMMA(currdate As Date, symbol1, days) As Double
'this is a SMOOTHED EMA WITH ALPHA 1/N'
Dim dbsStock_Data_08 As DAO.Database
Dim rs As DAO.Recordset
Dim pastdate As Date
pastdate = DATECHANGE(currdate, days + days * 3)
Dim counter As Double
counter = 1
Dim TempAvg As Double
Dim DayClose As Field

Set dbsStock_Data_08 = OpenDatabase("c:\stock\Price Data.accdb")
Set rs = dbsStock_Data_08.OpenRecordset("PriceData")
Set DayClose = rs.Fields("PriceDataClose")

rs.Index = "PrimaryKey"
rs.Seek "=", pastdate, symbol1

If rs.NoMatch Then
GoTo endmacro:
Else

TempAvg = (rs!PriceDataClose)

Do While counter < days
pastdate = DATECHANGEadd(pastdate, 1)
rs.Index = "PrimaryKey"
rs.Seek "=", pastdate, symbol1

If rs.NoMatch Then
GoTo endmacro:
Else
TempAvg = TempAvg + (rs!PriceDataClose)
End If
counter = counter + 1
Loop
'LOOP2
Do While counter < days + days * 3
pastdate = DATECHANGEadd(pastdate, 1)
rs.Index = "PrimaryKey"
rs.Seek "=", pastdate, symbol1
If rs.NoMatch Then
GoTo endmacro:
Else
TempAvg = TempAvg - (TempAvg / (days)) + (rs!PriceDataClose)
End If
counter = counter + 1
Loop
pastdate = DATECHANGEadd(pastdate, 1)
rs.Index = "PrimaryKey"
rs.Seek "=", pastdate, symbol1
If rs.NoMatch Then
GoTo endmacro:
Else
TempAvg = TempAvg - (TempAvg / (days)) + (rs!PriceDataClose)
End If
End If
endmacro:
SMMA = TempAvg / days
rs.Close
'clean up
Set rs = Nothing
Set dbsStock_Data_08 = Nothing
Set DayAvg = Nothing
TempAvg = 0
End Function

 

[David H]

From what I can tell you have a nested loop in the code and I'm guessing that
the function itself is called from within a loop in another piece of code?

Are you just averaging stock prices across a specific period of time or
across the entire history in the database?

Do you have indexes on the underlying tables set up on the non-primary key
fields that your searching on?

'The primary key is for the price data table is the date and symbol fields.
' Do you have two specific fields in the databse - one for symbol and one for
date?

Have you tried using the AVG() in a SELECT query using BETWEEN [x] and [x]
in the WHERE statement? You can use the result of a function in a WHERE
statement as in WHERE dteDate BETWEEN #10/31/2009# and #11/30/2009# AND ID =
getRecordIdFromSymbol([symbol])

Also the use of GOTO is typically accepted as only being a best practice in
Error trapping.

If rs.NoMatch Then
GoTo endmacro:
Else
End If

Is better written as

[If Not rs.NoMatch] Then Or [If rs.NoMatch = False]

end if

Yes you're effectively saying 'If I did not not find no records'