Avg. 1-month, 3-month, 6-month & 12-month Stock Returns

[ryguy7272]

I don’t know for sure if Access can handle this request, but I know Queries
are pretty powerful, so I’m thinking it is possible. As I alluded to in the
title of the post, I’m trying to find a way to query for the average of
1-month stock returns, average of 3-month stock returns, average of 6-month
stock returns & average of 12-month stock returns.

This is my SQL now:
SELECT SharePrices.StockSymbol, Avg(SharePrices.StockPrice) AS AveragePrice,
tblStocksGroup.Company, tblStocksGroup.Group, tblStocksGroup.Class
FROM SharePrices INNER JOIN tblStocksGroup ON SharePrices.StockSymbol =
tblStocksGroup.Ticker
GROUP BY SharePrices.StockSymbol, tblStocksGroup.Company,
tblStocksGroup.Group, tblStocksGroup.Class
HAVING (((tblStocksGroup.Group)=[Forms]![frmMaster]![cboGroup]) AND
((tblStocksGroup.Class)=[Forms]![frmMaster]![cboClass]));

That gives me an average of my stock prices, but it is just an average of
all the stock prices in a table named ‘SharePrice’. Is there a way to do
what I described above? If I assume 250 trading days in a year (52*5 = 260 –
9 holidays = 251), I think I would first have to calculate the average based
on the number of days to get 21 (for 1 month), 63 (for 3 months), 125 (for
6-months), and 250 days.

Thanks so much!
Ryan--

 

[John Spencer]

Do you have a Date field in there somewhere? IF so, that would be the way to
break this down into months, quarters, and years.

Also, can you define what you mean by 1 month, 3 month, 6 month, and 12 month
returns? For instance, does one month equate to
== what is the average price for a stock in January 2009
== what is the average price per month for the year 2009
== Or something else

Your query calculates the average Price of a stock and does not seem to have
much to do with a return on investment.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[ryguy7272]

I think that would work. So, the final query would be a select query and not
a union query? Can you show me how to do this for 21 days, for instance. I
will have just over 1-year of dates, and of course, stock prices. Then, how
would I construct the final query. Wouldn't Access try to produce some kind
of cartesian product if I set it up that way?

Thanks a lot!!
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

I don’t know for sure if Access can handle this request, but I know Queries
are pretty powerful, so I’m thinking it is possible. As I alluded to in the
title of the post, I’m trying to find a way to query for the average of
1-month stock returns, average of 3-month stock returns, average of 6-month
stock returns & average of 12-month stock returns.

This is my SQL now:
SELECT SharePrices.StockSymbol, Avg(SharePrices.StockPrice) AS AveragePrice,
tblStocksGroup.Company, tblStocksGroup.Group, tblStocksGroup.Class
FROM SharePrices INNER JOIN tblStocksGroup ON SharePrices.StockSymbol =
tblStocksGroup.Ticker
GROUP BY SharePrices.StockSymbol, tblStocksGroup.Company,
tblStocksGroup.Group, tblStocksGroup.Class
HAVING (((tblStocksGroup.Group)=[Forms]![frmMaster]![cboGroup]) AND
((tblStocksGroup.Class)=[Forms]![frmMaster]![cboClass]));

That gives me an average of my stock prices, but it is just an average of
all the stock prices in a table named ‘SharePrice’. Is there a way to do
what I described above? If I assume 250 trading days in a year (52*5 = 260 –
9 holidays = 251), I think I would first have to calculate the average based
on the number of days to get 21 (for 1 month), 63 (for 3 months), 125 (for
6-months), and 250 days.

Thanks so much!
Ryan--

Ryan,
just wondering... what if you create separate queries to do the 3, 6, 9, and
12 month returns and then use a select query to join them all together (just
join back to the original tblStocksGroup table)...

then you'd have all the 3n returns joined back to tblStocksGroup and you
could drop the different summary columns in the grid and away you go... right?


--
Message posted via AccessMonster.com


.