Query to return a moving average

[q]

Hi, I have a stock data database. I am trying to write a query to return a
recordset containing the stock symbol and 14 day moving average (based on
last 14 days of data) for each stock in my database. I have successfully
written a query which returns 14 days of data for a single symbol. I have
successfully written a query which returns a moving average of THAT STOCK'S
14 days worth of data.

BUT, when I try to write a query to compute the moving average for each
symbol, it does not work. Could someone show me what I am doing wrong?

Here is the query to return 14 days of data for a single stock (This query
is named "qMostRecent14DayDataForSymbol"):

SELECT TOP 14 Symbols.Ticker, HistData.QDate, HistData.OpenP,
HistData.HighP, HistData.LowP, HistData.CloseP, HistData.Vol, HistData.AdjP
FROM Symbols INNER JOIN HistData ON Symbols.IDSym=HistData.IDSym
WHERE Symbols.Ticker = [Enter Symbol]
ORDER BY HistData.QDate DESC;


Here is the query which uses the above query to return a moving average for
that one stock:

SELECT qMostRecent14DayDataForSymbol.Ticker,
Avg(qMostRecent14DayDataForSymbol.CloseP) AS MVA8
FROM qMostRecent14DayDataForSymbol
GROUP BY qMostRecent14DayDataForSymbol.Ticker
ORDER BY qMostRecent14DayDataForSymbol.Ticker;


I thought that if I simply removed the WHERE clause in the first query, that
the 2nd query would loop through each stock in the database and compute the
moving average for the last 14 days of each stock and then group the results
by Symbol. I do get a table of values, BUT the value reported for the
moving average is simply the most recent day's closing price, not the
average of the most recent 14 days of prices.

Any help is appreciated.

Thanks.

 

[Guest]

Try this --
SELECT Symbols.Ticker, Avg(HistData.CloseP) AS [Average 14-day Closing],
Count(HistData.QDate) AS CountOfQDate
FROM Symbols INNER JOIN HistData ON Symbols.IDSym = HistData.IDSym
WHERE (((HistData.QDate) Between Date()-19 And Date()))
GROUP BY Symbols.Ticker
HAVING (((Symbols.Ticker)=[Enter Symbol]) AND ((Count(HistData.QDate))<=14));

 

[q]

You are a genius. Thank you very much.

RAB

Try this --
SELECT Symbols.Ticker, Avg(HistData.CloseP) AS [Average 14-day Closing],
Count(HistData.QDate) AS CountOfQDate
FROM Symbols INNER JOIN HistData ON Symbols.IDSym = HistData.IDSym
WHERE (((HistData.QDate) Between Date()-19 And Date()))
GROUP BY Symbols.Ticker
HAVING (((Symbols.Ticker)=[Enter Symbol]) AND
((Count(HistData.QDate))<=14));

Hi, I have a stock data database. I am trying to write a query to return
a
recordset containing the stock symbol and 14 day moving average (based on
last 14 days of data) for each stock in my database. I have successfully
written a query which returns 14 days of data for a single symbol. I
have
successfully written a query which returns a moving average of THAT
STOCK'S
14 days worth of data.

BUT, when I try to write a query to compute the moving average for each
symbol, it does not work. Could someone show me what I am doing wrong?

Here is the query to return 14 days of data for a single stock (This
query
is named "qMostRecent14DayDataForSymbol"):

SELECT TOP 14 Symbols.Ticker, HistData.QDate, HistData.OpenP,
HistData.HighP, HistData.LowP, HistData.CloseP, HistData.Vol,
HistData.AdjP
FROM Symbols INNER JOIN HistData ON Symbols.IDSym=HistData.IDSym
WHERE Symbols.Ticker = [Enter Symbol]
ORDER BY HistData.QDate DESC;


Here is the query which uses the above query to return a moving average
for
that one stock:

SELECT qMostRecent14DayDataForSymbol.Ticker,
Avg(qMostRecent14DayDataForSymbol.CloseP) AS MVA8
FROM qMostRecent14DayDataForSymbol
GROUP BY qMostRecent14DayDataForSymbol.Ticker
ORDER BY qMostRecent14DayDataForSymbol.Ticker;


I thought that if I simply removed the WHERE clause in the first query,
that
the 2nd query would loop through each stock in the database and compute
the
moving average for the last 14 days of each stock and then group the
results
by Symbol. I do get a table of values, BUT the value reported for the
moving average is simply the most recent day's closing price, not the
average of the most recent 14 days of prices.

Any help is appreciated.

Thanks.

 

[q]

You are a genius. Thank you very much.

RAB

Try this --
SELECT Symbols.Ticker, Avg(HistData.CloseP) AS [Average 14-day Closing],
Count(HistData.QDate) AS CountOfQDate
FROM Symbols INNER JOIN HistData ON Symbols.IDSym = HistData.IDSym
WHERE (((HistData.QDate) Between Date()-19 And Date()))
GROUP BY Symbols.Ticker
HAVING (((Symbols.Ticker)=[Enter Symbol]) AND
((Count(HistData.QDate))<=14));

Hi, I have a stock data database. I am trying to write a query to return
a
recordset containing the stock symbol and 14 day moving average (based on
last 14 days of data) for each stock in my database. I have successfully
written a query which returns 14 days of data for a single symbol. I
have
successfully written a query which returns a moving average of THAT
STOCK'S
14 days worth of data.

BUT, when I try to write a query to compute the moving average for each
symbol, it does not work. Could someone show me what I am doing wrong?

Here is the query to return 14 days of data for a single stock (This
query
is named "qMostRecent14DayDataForSymbol"):

SELECT TOP 14 Symbols.Ticker, HistData.QDate, HistData.OpenP,
HistData.HighP, HistData.LowP, HistData.CloseP, HistData.Vol,
HistData.AdjP
FROM Symbols INNER JOIN HistData ON Symbols.IDSym=HistData.IDSym
WHERE Symbols.Ticker = [Enter Symbol]
ORDER BY HistData.QDate DESC;


Here is the query which uses the above query to return a moving average
for
that one stock:

SELECT qMostRecent14DayDataForSymbol.Ticker,
Avg(qMostRecent14DayDataForSymbol.CloseP) AS MVA8
FROM qMostRecent14DayDataForSymbol
GROUP BY qMostRecent14DayDataForSymbol.Ticker
ORDER BY qMostRecent14DayDataForSymbol.Ticker;


I thought that if I simply removed the WHERE clause in the first query,
that
the 2nd query would loop through each stock in the database and compute
the
moving average for the last 14 days of each stock and then group the
results
by Symbol. I do get a table of values, BUT the value reported for the
moving average is simply the most recent day's closing price, not the
average of the most recent 14 days of prices.

Any help is appreciated.

Thanks.