Moving Average Calculation 1

[pd2004]

Hello,

I am trying to calculate a moving average for a series of data. I want to generate the moving average for each point within the data, in order to show in a graph. Anyway, below is an example from MS Support. I have followed to the letter, but mine doesn't give a moving average. It repeats the same data point over and over (the first data point). So, I don't believe the function is finding the startdate in the MyRST.Seek line, therefore just returning the first data point.

Finally (maybe will make this really easy) I am confused about how the indexes work. I thought you could only have one primary key, but apparently you can create multiple field constraints. I have tried to do this with the following data definition query:

ALTER TABLE Table1 ADD CONSTRAINT NoDupes UNIQUE (CurrencyType, TransactionDate)

Sorry about the length of this post. I appreciate your help.

Pat




The following sample function computes moving averages based on a table with a multiple-field primary key. The weekly values of foreign currencies are used for this example. To create the sample function, follow these steps:


Create the following table and save it as Table1:
Table: Table1
-----------------------------------------
Field Name: CurrencyType [Primary Key]
Data Type: Text
Field Size: 25

Field Name: TransactionDate [Primary Key]
Data Type: Date/Time
Format: Short Date

Field Name: Rate
Data Type: Currency
Decimal Places: 4


View the table in Datasheet view and enter the following values:
CurrencyType TransactionDate Rate
--------------------------------------------
Yen 8/6/93 $0.0079
Yen 8/13/93 $0.0082
Yen 8/20/93 $0.0085
Yen 8/27/93 $0.0088
Yen 9/3/93 $0.0091
Mark 8/6/93 $0.5600
Mark 8/13/93 $0.5700
Mark 8/20/93 $0.5800
Mark 8/27/93 $0.5900
Mark 9/3/93 $0.6000


Open a new module and type the following functions:

' '*************************************************************
'Declarations section of the module.
'*************************************************************

Option Explicit

'===============================================================
' The following function MAvgs computes moving averages based on
' a table with a multiple-field primary key.
'===============================================================

Function MAvgs(Periods As Integer, StartDate, TypeName)
Dim MyDB As DATABASE, MyRST As Recordset, MySum As Double
Dim i, x
Set MyDB = CurrentDb()
Set MyRST = MyDB.OpenRecordset("Table1")

On Error Resume Next

MyRST.Index = "PrimaryKey"
x = Periods - 1
ReDim Store(x)
MySum = 0

For i = 0 To x
MyRST.MoveFirst
MyRST.Seek "=", TypeName, StartDate

' These two variables should be in the same order as the
' primary key fields in your table.
Store(i) = MyRST![Rate]

If i <> x Then StartDate = StartDate - 7
' The 7 here assumes weekly data; 1 for daily data.

If StartDate < #8/6/93# Then MAvgs = Null: Exit Function
' #8/6/93# is replaced with the earliest date of the data
' in your table.
MySum = Store(i) + MySum
Next i

MAvgs = MySum / Periods
MyRST.Close
End Function


Create the following query based on the Table1 table:
Query: Query1
-------------------------------------------------------
Field: CurrencyType
Field: TransactionDate
Field: Rate
Field: Expr1: MAvgs(3,[TransactionDate],[CurrencyType])


NOTE: This query will generate a three-week moving average of the Rate data. To compute a longer or shorter moving average, change the number 3 in the query's Expr1 column to the value you want to compute.
Run the query. Note that you see the following three-week moving average for each currency. A Null value indicates that there were not enough earlier values to compute that week's average.
CurrencyType TransactionDate Rate Expr1
Mark 08/06/93 $0.5600
Mark 08/13/93 $0.5700
Mark 08/20/93 $0.5800 0.57
Mark 08/27/93 $0.5900 0.58
Mark 09/03/93 $0.6000 0.59
Yen 08/06/93 $0.0079
Yen 08/13/93 $0.0082
Yen 08/20/93 $0.0085 0.0082
Yen 08/27/93 $0.0088 0.0085
Yen 09/03/93 $0.0091 0.0088

 

[dhookom]

How old is that code? It doesn't explicitly use DAO and doesn't mention that this won't work with linked tables.

Dim MyDB as DAO.Database, MyRST as DAO.Recordset,...

I would use a subquery rather than a recordset. It might look something like:

SELECT CurrencyType, TransactionDate, Rate, 
(SELECT Avg(Rate)
 FROM Table1 B
 WHERE A.CurrencyType = B.CurrencyType AND
 A.TransactionDate BETWEEN B.TransactionDate - 14 AND B.TransactionDate)
FROM Table1 A;

Duane
Hook'D on Access
MS Access MVP

 

[pd2004]

Hi Duane,

That is really perfect. I really appreciate your help. However, the code you gave is calculating the forward 14 day moving average ( placing the moving average in the record for day 1 of the average, where I wanted it to be a backward looking average, placed in record 14). I changed just slightly to the following and it appears to be working!

SELECT A.CurrencyType, A.TransactionDate, A.Rate, (SELECT Avg(Rate)
FROM Table1 B
WHERE A.CurrencyType = B.CurrencyType AND
B.TransactionDate BETWEEN A.TransactionDate - 14 AND A.TransactionDate) AS Expr1
FROM Table1 AS A;


You can see, all I did was exchange "A" for "B" in the where clause.

This is a really big help to me and I really appreciate it. I have not seen coding like this before, and honestly, I don't really understand it. I don't know how the SQL understands what B and A are. I am assuming that they are creating some kind of alternate reference to Table1. If you can give any guidance I would really appreciate it. Also, maybe some reference to material that I could look at? I am always very excited to learn something new about VBA/SQL, and I really appreciate your help!

Pat

 

[PHV]

understands what B and A are
they are aliases

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[pd2004]

Thank you, PHV. It is making better sense already!

Pat

 

[joshery420]

Wow, never looked at SQL view before. Supremely helpful.
I'm trying to get this code to function in my own dataset and I'm stuck on one particular issue.
pd2004, not sure if the new subquery code worked the same as your old VBA code or not, but with my data it still shows the "rolling average" even if there aren't enough days to create that length of an average.
e.g. If I'm performing a 7 day rolling average, day 1 shows the same data in the 7DayAvg column as it does in the daily data column. Day 2 would show the average of Days 1 and 2, etc.
Do either of you guys know how to fix this by chance?
Also, thanks for the great code tip PHV!!

 

[joshery420]

Woops, meant to thanks dhookom for the code tip, not PHV.
But hey, thanks to both of you. XD

 

[pd2004]

Hi Joshery420,

I will leave the best solutions to the professionals here, but you can see in my original post how the Microsoft Help example tries to handle that. Here is the code:

If StartDate < #8/6/93# Then MAvgs = Null: Exit Function
' #8/6/93# is replaced with the earliest date of the data
' in your table.

They are just exiting the function if the date doesn't fit the criteria. I don't know if you could incorporate something like that into the aliases code provided by dhookem. I don't like their way of handling this, and I suspect that dhookem will provide a much more elegant solution. For my purposes the issue you are describing is not a concern, but I will be interested in seeing any solutions.

Pat

 

[dhookom]

You could try using IIf() to test for a count of the number of records.
[red]Caution: untested notepad code follows:[/red]

SELECT A.CurrencyType, A.TransactionDate, A.Rate, IIF((SELECT Count(Rate)
 FROM Table1 C
 WHERE A.CurrencyType = C.CurrencyType AND
 C.TransactionDate BETWEEN A.TransactionDate - 14 AND A.TransactionDate)>=7, (SELECT Avg(Rate)
 FROM Table1 B
 WHERE A.CurrencyType = B.CurrencyType AND
 B.TransactionDate BETWEEN A.TransactionDate - 14 AND A.TransactionDate),Null) AS Expr1
FROM Table1 AS A;

Duane
Hook'D on Access
MS Access MVP

 

[pd2004]

Very interesting. Thank you, Duane. I appreciate your help.

Pat