Moving Average Calculation problem for Week Day data

[Richard101a]

thread705-1609974

Hi,

I wonder if anyone could help.

I also used the VBA Snippet that pd2004 references in their post which I have referenced above.

Lets say I use the following data:

CurrencyType TransactionDate Rate
--------------------------------------------
Yen 8/01/14 $0.0079
Yen 8/04/14 $0.0082
Yen 8/05/14 $0.0085
Yen 8/06/14 $0.0088
Yen 8/07/14 $0.0091
Yen 8/11/14 $0.0079
Yen 8/12/14 $0.0082
Yen 8/13/14 $0.0085
Yen 8/14/14 $0.0088
Yen 8/15/14 $0.0091

This data spans Friday 1st August to Friday 15th August, with 10 entries and excludes weekends.

The problem I have encountered is that I would like to calculate a moving average on daily data where the data occurs on weekdays.

Using the VBA routine, I have set the start date parameter to If i <> x Then StartDate = StartDate - 1, for daily data, then in my query I set Expr1 to MAvgs(10,[TransactionDate],[CurrencyType]) to try to calculate a 10 period moving average.

The result of Expr 1, however for the moving average is incorrect because the VBA routine will calculate 10 days previous to 15th August, which is 5th August, it will thus only use 8 entries from 15th August (because week end data is excluded) and divide by 10 which is the moving average denominator.

I cannot set the MAvgs to a hard number because depending on the weekday, 10 days previous on a day count will sometimes include 2 weekend dates and on others 4 weekend dates.

How can I set the routine to count the actual number of entries instead of using a StartDate minus number of days?

 

[dhookom]

First, welcome to Tek-Tips. Please use the Pre TGML tag to format your data view and include a column of expected/desired values. It really helps us help you. More records is also recommended since your moving average is over 10 records and you only provided 10.

[pre]
CurrencyType TransactionDate Rate
--------------------------------------------
Yen 8/01/14 $0.0079
Yen 8/04/14 $0.0082
Yen 8/05/14 $0.0085
Yen 8/06/14 $0.0088
Yen 8/07/14 $0.0091
Yen 8/11/14 $0.0079
Yen 8/12/14 $0.0082
Yen 8/13/14 $0.0085
Yen 8/14/14 $0.0088
Yen 8/15/14 $0.0091
[/pre]
Assuming you have your table set up correctly with the primary key index on the appropriate fields, consider changing the code to:

Function MAvgs(Periods As Integer, StartDate As Date, TypeName As String, strTableName As String) As Double
    Dim MyDB As DAO.Database, MyRST As DAO.Recordset, MySum As Double
    Dim i As Integer, x As Integer
    Set MyDB = CurrentDb()
    Set MyRST = MyDB.OpenRecordset(strTableName)

    On Error Resume Next

    MyRST.Index = "PrimaryKey"
    
    MySum = 0
    With MyRST
        .MoveFirst
        .Seek "=", TypeName, StartDate
        If Not .NoMatch Then
            For i = 1 To Periods
                MySum = MySum + MyRST![Rate]
                If Not .BOF Then
                    x = x + 1
                    .MovePrevious
                End If
            Next
        End If
    .Close
    End With
    MAvgs = MySum / x
End Function

Call the function with an expression like (assuming your table name is tblRichard101a):
[pre]MoveAvg: MAvgs(10,[TransactionDate],[CurrencyType],"tblRichard101a")[/pre]
You will probably need to play with the periods or other code.

Duane
Hook'D on Access
MS Access MVP