n period moving average in queries

[rolex]

I'm trying to figure out if it's possible to calculate moving average in query. Searching through previous posts, all I could find is:

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=31806

There are good ideas there. Apparently, this works fine, but what I actually need is different - moving average using certain period. Let me try to explain.

The table is a very simple one, contains one numeric field and one auto number field, sorted by auto number. I need to generate a moving average of the last n periods, for example n=5. Moving average would be an average of "Price" values for the 5 records immediately preceding current record.

Sample:


AutoNum Price MovingAvg

1 100.00 100.00
2 120.00 110.00
3 110.00 110.00
4 105.00 108.75
5 95.00 106.00 (avg of #1-5)
6 70.00 100.00 (avg of #2-6)
7 80.00 92.00 (avg of #3-7)
8 85.00 87.00 (avg of #4-8)
9 87.00 83.40 etc.
10 70.00 78.40

I know, as a last resort I can perform this task using VB code, parsing all the data and generating another table, but with large data sets this could turn to be a headache.

Is there any way to perform this in query?

 

[Tanis]

Try this link.

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q143237

 

[Jon K]

Try these two queries (replacing with the correct table name in the first query):

qryRankRecords:-
SELECT AutoNum, Price, (Select count(*) from TableName where AutoNum<=a.AutoNum) AS Rank
FROM TableName AS a;

qryMovingAvg:-
SELECT AutoNum, Price, (Select sum(Price) from qryRankRecords where Rank between b.Rank and b.Rank-4)/ iif(Rank>5,5,Rank) AS MovingAvg
FROM qryRankRecords AS b;

Run the second query.

(Note. Rank in the first query happens to be the same as the AutoNum in your data. However, it is used to ensure that the second query works even if some records are deleted from the table.)

 

[rolex]

Thanks, folks

 

[rolex]

Try these two queries (replacing with the correct table name in the first query):

qryRankRecords:-
qryMovingAvg:-
Run the second query.

(Note. Rank in the first query happens to be the same as the AutoNum in your data. However, it is used to ensure that the second query works even if some records are deleted from the table.)

Jon,

Thanks, that works. There's only one problem - on a larger data base (a few thousand records) it takes forever to get the second query finish. Any ideas how this could be optimized?

 

[Jon K]

Because of the number of calculations involved, the two queries are good only on a relatively small table. For instance, if there are 100 records in the table, qryRankRecords alone needs to count the records 100*100=10,000 times to arrive at the Ranks.

For a table with more than 100 or 150 records, you will find that you can do it much faster in Excel. To arrive at the Ranks in Excel, you can use Excel's fill feature with lightning speed, and you can get the MovingAvg in thousands of cells by selecting the cells, enter a formula and press Ctrl+Enter.


Edit:
The last part of the sentence in the first paragraph should have been: "... to count the table 100 times to arrive at the Ranks."

 

[rolex]

Because of the number of calculations involved, the two queries are good only on a relatively small table. For instance, if there are 100 records in the table, qryRankRecords alone needs to count the records 100*100=10,000 times to arrive at the Ranks.

For a table with more than 100 or 150 records, you will find that you can do it much faster in Excel. To arrive at the Ranks in Excel, you can use Excel's fill feature with lightning speed, and you can get the MovingAvg in thousands of cells by selecting the cells, enter a formula and press Ctrl+Enter.

You're right, I'm better off doing all with VB. Not sure I even need Excel - will try MS Access VB code first. Thanks, anyway.

 

[EricRex]

Follow up question to this answer:
If my table already has an autonumber ID field that is properly ordered starting from 1 to N, can't I skip the qryRankRecords and just do the second query directly from the table?

I tried it and had troubles with this idea.

Cheers,
Eric

 

[DHookom]

I wouldn't rely strictly on an autonumber field since records might have been deleted.

If you tried something and "had troubles", you should be more specific, possibly with some sample data and SQL statement(s).

 

[CJ_London]

Rather than hijacking this is a 22 year old thread, might be better to start a new one?

Might be better to use a non standard join rather than a sub query but without some example data …..

 

[EricRex]

I wouldn't rely strictly on an autonumber field since records might have been deleted.

If you tried something and "had troubles", you should be more specific, possibly with some sample data and SQL statement(s).

Hey DHookom,

I get your point about deleted records. In this case, I just imported this stock data so the Rank and the ID fields are identical.

My query on the table

SELECT ID, close, (SELECT(sum(close) from VIX_DailyHist where ID BETWEEN b.ID and b.ID-4)/ iif(ID > 5, 5, ID) AS VIXSMA5
FROM VIX_DailyHist AS b;

This throws the error, "Property not found."

A snippet of my table:
ID BarDate open high low close
1 11/4/2022 25.63 25.71 24 24.55
2 11/7/2022 25.67 25.67 24.34 24.35
3 11/8/2022 24.71 26.16 24.24 25.54
4 11/9/2022 25.34 26.35 25.02 26.09
5 11/10/2022 26.51 26.59 22.84 23.53
6 11/11/2022 23.89 23.91 22.37 22.52

​

 

[The_Doc_Man]

OK, I'll toss in. You could do this with VBA and a little bit of chicanery. You want the floating average of five adjacent records. So have VBA open a sorted recordset that shows you the records in the desired order. Whatever fields you want to have a floating average, you must repeat this algorithm for each field - but it works one RECORD at a time so shouldn't be that bad..

For the first five records, just add in and average the field in question by forming the sum and dividing by the number of records. No sweat, right? But let's add the complication that you have a five-number array for each thing you are floating, plus a separate summation variable. For each record you read, store the value in the array and count records encountered so far.

For the sixth and subsequent records until you reach the end of the dataset, before you put the next record in the array, compute where it goes. If you were counting the records then recordcount MOD 5 will give you 0 to 4. So if your array was dimensioned as (0 to 4) rather than (1 to 5) you know which slot is next. Before you store the new value, subtract the value already in that slot from the sum variable, THEN store the new value and add it to the sum variable. Then divide by 5. Store these sums however you intended. This is a one-pass algorithm that will give you the running sum correlated with the date to which it belongs.

 

[DHookom]

Hey DHookom,

I get your point about deleted records. In this case, I just imported this stock data so the Rank and the ID fields are identical.

My query on the table

SELECT ID, close, (SELECT(sum(close) from VIX_DailyHist where ID BETWEEN b.ID and b.ID-4)/ iif(ID > 5, 5, ID) AS VIXSMA5
FROM VIX_DailyHist AS b;

This throws the error, "Property not found."

A snippet of my table:
ID BarDate open high low close
1 11/4/2022 25.63 25.71 24 24.55
2 11/7/2022 25.67 25.67 24.34 24.35
3 11/8/2022 24.71 26.16 24.24 25.54
4 11/9/2022 25.34 26.35 25.02 26.09
5 11/10/2022 26.51 26.59 22.84 23.53
6 11/11/2022 23.89 23.91 22.37 22.52

​

I'm not sure if this resolves your issue but you have an extra "(" after the second SELECT. Try:

SELECT ID, close,
(SELECT sum(close) from VIX_DailyHist
  where ID BETWEEN b.ID and b.ID-4)/ iif(ID > 5, 5, ID) AS VIXSMA5
FROM VIX_DailyHist AS b;

 

[EricRex]

I'm not sure if this resolves your issue but you have an extra "(" after the second SELECT. Try:

SELECT ID, close,
(SELECT sum(close) from VIX_DailyHist
  where ID BETWEEN b.ID and b.ID-4)/ iif(ID > 5, 5, ID) AS VIXSMA5
FROM VIX_DailyHist AS b;

Thank you for your second set of eyes! I was super careful about the field names but missed that. It works!