Trouble Creating a "Moving Average" Field in Query

  • Thread starter Thread starter Steve in Wisconsin
  • Start date Start date
S

Steve in Wisconsin

Hi,

I have a table with stock prices in it. One "Close" price
per day for approximately 1000 days (hence 1000 records).
I have created a query in which I want to display each
days "Close" as well as a calculated value for the average
of the last 20 prior days from each records closing date.

It seems simple enough in concept and I have tried using
the DAVG() function but failed because I couldn't find a
way to reference only the 20 records prior to the current
record.

Any suggestions would be truly appreciated.

Thanks!
 
Hi,

You need to refer to the table twice.

SELECT a.TheDate, AVB(b.amount)
FROM myTable As a INNER JOIN myTable As b
ON (b.TheDate BETWEEN a.TheDate AND a.TheDate-20)
GROUP BY a.TheDate
ORDER BY a.TheDate


If the average is "by stockID", then, it is better trying:


SELECT a.TheDate, a.StockID, AVB(b.amount)
FROM myTable As a INNER JOIN myTable As b
ON (a.StockID=b.StockID)
AND
(b.TheDate BETWEEN a.TheDate-20 AND a.TheDate)
GROUP BY a.TheDate, a.StockID
ORDER BY a.TheDate, a.StockID



Hoping it may help,
Vanderghast, Access MVP
 
Hi,


You would need a TOP and a subquery, something like:


SELECT a.theDate, a.StockID, AVG(b.Amount)

FROM myTable As a INNER JOIN myTable As b
ON a.StockID=b.StockID

WHERE b.TheDate IN(
SELECT TOP 10 c.TheDate
FROM mytable As c
WHERE c.StockID=a.StockID AND
c.TheDate <= a.TheDate
ORDER BY c.TheDate DESC )

GROUP BY a.theDate, a.StockID
ORDER BY a.theDate, a.StockID




Hoping it may help,
Vanderghast, Access MVP


Steve in Wisconsin said:
I appreciate your response. I have considered using the
date field but I run into problems when some stocks will
(for numerous reasons) not trade for a few random days. So
using any date function really is something I have to be
careful to stay away from.

Do you hav eany sggestions as to how I can reference the
previous 20 records from any point in my list?

Can I create a calculated field in a query design grid
that every time the query is run it generates a
recordnumber that can be referenced in the same query?

i.e. DAVG("[ClosePrice]","myQuery", "[RecID] >= ([RecID]-
19)")

Thanks.
-----Original Message-----
Hi,

You need to refer to the table twice.

SELECT a.TheDate, AVB(b.amount)
FROM myTable As a INNER JOIN myTable As b
ON (b.TheDate BETWEEN a.TheDate AND a.TheDate- 20)
GROUP BY a.TheDate
ORDER BY a.TheDate


If the average is "by stockID", then, it is better trying:


SELECT a.TheDate, a.StockID, AVB(b.amount)
FROM myTable As a INNER JOIN myTable As b
ON (a.StockID=b.StockID)
AND
(b.TheDate BETWEEN a.TheDate-20 AND a.TheDate)
GROUP BY a.TheDate, a.StockID
ORDER BY a.TheDate, a.StockID



Hoping it may help,
Vanderghast, Access MVP



"Steve in Wisconsin"


.
Click to expand...
Click to expand...
 
Hi,


Well, it is TOP 20 in your case, not TOP 10 ... silly me.


Vanderghast, Access MVP
 
Thanks Michel, I'll give it a try!
-----Original Message-----
Hi,


Well, it is TOP 20 in your case, not TOP 10 ... silly me.


Vanderghast, Access MVP





.
Click to expand...
 
Back
Top