Moving Average

  • Thread starter Thread starter Evan
  • Start date Start date
E

Evan

Using A97

Anyone got any custom functions or tips on how to measure/return moving
averages in Access queries?

TIA

Evan
 
Hi Evan

This information can be found in article Q143237 and reads as follows:

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:

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

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

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

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

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

NOTE: If you want to compute a moving average for a table with a single
primary key, use the primary key both as an argument to be passed to
the function and as the key value for the Seek method.
 
As always, there's more than one way to do this.

I have a stock table, with 3 fields. Symbol, StockDate,
and Close.

I want to do 30 Day moving averages.

I create a query, and add tblStock to the query. Then, I
add the table again, and it comes in as tblStock_1.

I create a join on the symbol.

I add tblStock.Symbol, tblStock.Stockdate, tblStock.Close,
tblStock_1.StockDate, tblStock_1.Close.

I set criteria on tblStock_1.StockDate that says:

Between tblStock.StockDate-30 and tblStock.StockDate

Now, make it a group by query, and don't show
tblStock_1.StockDate.

Finally, make tblStock_1.Close a AVG field.

Run the query and you're done.


Chris

-----Original Message-----
Hi Evan

This information can be found in article Q143237 and reads as follows:

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:

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

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

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

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

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

NOTE: If you want to compute a moving average for a table with a single
primary key, use the primary key both as an argument to be passed to
the function and as the key value for the Seek method.

Using A97

Anyone got any custom functions or tips on how to measure/return moving
averages in Access queries?

TIA
Click to expand...


.
Click to expand...
 
Easy enough to query the average between the current date and X days ago for
each value in the data table ...
 
Back
Top