If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Avg. 1-month, 3-month, 6-month & 12-month Stock Returns
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--- If this information was helpful, please indicate this by clicking ''Yes''. |
#2
|
|||
|
|||
Avg. 1-month, 3-month, 6-month & 12-month Stock Returns
ryguy7272 wrote:
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 http://www.accessmonster.com/Uwe/For...eries/201002/1 |
#3
|
|||
|
|||
Avg. 1-month, 3-month, 6-month & 12-month Stock Returns
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 wrote: 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-- |
#4
|
|||
|
|||
Avg. 1-month, 3-month, 6-month & 12-month Stock Returns
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''. "PieterLinden via AccessMonster.com" wrote: ryguy7272 wrote: 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 http://www.accessmonster.com/Uwe/For...eries/201002/1 . |
Thread Tools | |
Display Modes | |
|
|