Moving average and maximum calcuation query/queries

[Christina Wise via AccessMonster.com]

I have a table (MarketHistoryShort) containing a large amount of historical
price and volume information for a number of different stocks with the
following fields:

ID Ticker Company Name TradeDate High Low Close Volume
183150 AZ ABC Company 1/15/01 58 55.688 56.125 19704
183151 AZ ABC Company 1/16/01 58.25 55.313 58.188 18605
183152 AZ ABC Company 1/17/01 63.188 58.938 61.938 59965
183153 AZ ABC Company 1/18/01 68 60.375 67.563 49918
184296 DE DEF Company 1/15/01 32.313 30.625 31.813 40605
184297 DE DEF Company 1/16/01 33.563 31.313 33.563 27310
184298 DE DEF Company 1/17/01 34.188 32.813 33.563 33803
184299 DE DEF Company 1/18/01 34.813 33.313 34.438 31908
184300 DE DEF Company 1/19/01 34.063 32.375 32.688 26098

The “ID” field is the primary key that Access assigned to each record when
I imported the database.

I am trying to write a query/queries to pull entries for stocks that drop
beneath their 50-day moving average on the highest volume in three months.

I tried to break it into separate queries, one calculating a 50-day moving
average and another calculating a moving 3-month maximum volume for each
stock and date, but did not have much luck with either.

Per a previous inquiry, with help from the gurus here, I wrote a statement
to calculate the 3-month maximum, but I can’t get it to compute a
running/moving maximum for each stock/date:

SELECT Max([MarketHistoryShort].[Volume]) AS MaxOfVolume
FROM MarketHistoryShort
WHERE ((([MarketHistoryShort].[TradeDate]) Between Date() And DateAdd("m",-
3,Date())));


It can also be done this way:

SELECT MarketHistoryShort.Ticker, MarketHistoryShort.TradeDate,
MarketHistoryShort.Volume, MarketHistoryShort.Ticker
FROM MarketHistoryShort
WHERE (((MarketHistoryShort.Volume)=(SELECT Max([Volume]) FROM
[MarketHistoryShort] AS M WHERE M.[TradeDate] BETWEEN DateAdd("m", -3,
Date()) AND Date())));

I’m sorry if this post is too long or complicated, but I’ve been puzzling
over this for two days and am at a loss.

Christina.

 

[[MVP] S.Clark]

Access and Statistics aren't always friends, which is why we created Total
Access Statistics.
http://www.fmsinc.com/products/statistics/productguide.htm#DataAnalysisFunctions

To roll your own, I would do with several different queries. The first
would be to determine the 50 day MoveAvg for each stock and save to a table.
Then use that table to compare against the current prices to extract or flag
those that have fallen below it.

Another method may be to export the data to Excel and hit it with the
statistic features there.