Calculate Average Returns Based on Dates?

On Mon, 15 Feb 2010 20:28:01 -0800, ryguy7272
wrote:

I’m working with the SQL below, but it doesn’t roll up everything into one
line of average returns, over 21 days, per fund:
SELECT Avg(SharePrices.StockPrice) AS AvgOfStockPrice,
SharePrices.StockSymbol, SharePrices.DateTime
FROM tblStocksGroup INNER JOIN SharePrices ON tblStocksGroup.Ticker =
SharePrices.StockSymbol
GROUP BY SharePrices.StockSymbol, SharePrices.DateTime
HAVING (((SharePrices.DateTime) Between
DateSerial(Year([Forms]![frmMstr]![cboEnd]),Month([Forms]![frmMstr]![cboEnd]),Day([Forms]![frmMstr]![cboEnd]))
And
DateSerial(Year([Forms]![frmMstr]![cboEnd]),Month([Forms]![frmMstr]![cboEnd]),Day([Forms]![frmMstr]![cboEnd]-21))));

I think I’m really close now. How can I do this?

Use a WHERE clause (applied *before* the totalling) rather than a HAVING
clause (applied *after*), and don't group by the date. Best to also explicitly
define the parameter type:

PARAMETERS [Forms]![frmMstr]![cboEnd] DateTime;
SELECT Avg(SharePrices.StockPrice) AS AvgOfStockPrice,
SharePrices.StockSymbol, SharePrices.DateTime
FROM tblStocksGroup INNER JOIN SharePrices ON tblStocksGroup.Ticker =
SharePrices.StockSymbol
GROUP BY SharePrices.StockSymbol
WHERE (((SharePrices.DateTime) Between
DateSerial(Year([Forms]![frmMstr]![cboEnd]),Month([Forms]![frmMstr]![cboEnd]),Day([Forms]![frmMstr]![cboEnd]))
And
DateSerial(Year([Forms]![frmMstr]![cboEnd]),Month([Forms]![frmMstr]![cboEnd]),Day([Forms]![frmMstr]![cboEnd]-21))));

Slightly simpler, use the DateAdd function in the criteria:

PARAMETERS [Forms]![frmMstr]![cboEnd] DateTime;
SELECT Avg(SharePrices.StockPrice) AS AvgOfStockPrice,
SharePrices.StockSymbol, SharePrices.DateTime
FROM tblStocksGroup INNER JOIN SharePrices ON tblStocksGroup.Ticker =
SharePrices.StockSymbol
GROUP BY SharePrices.StockSymbol
WHERE SharePrices.DateTime = DateAdd("d", -21, [Forms]![frmMstr]![cboEnd])
AND SharePrices.DateTime = DateAdd("d", 1, [Forms]![frmMstr]![cboEnd]);

The dateadd at the end lets the user select (say) 2/15/2010 into cboEnd and
still return all records from midnight at the start of that day through
11:59:59.99999999 pm that day - otherwise you'll lose the last day's data.
--

John W. Vinson [MVP]

Did you look at the link on subqueries?

This example asumes you have a tblshare (one record for each type of share),
and it shows how to use a subquery to get the previous 3-month and 12-month
average for each share price from tblSharePrice:

SELECT ShareID,
(SELECT Avg(SharePrice) AS AvgPrice
FROM SharePrices
WHERE SharePrices.ShareID = tblShare.ShareID
AND SharePrices.[DateTime] Between
DateAdd("yyyy", -3, [Forms]![frmMstr]![cboTo])
And [Forms]![frmMstr]![cboTo]) AS ThreeMoAvg,
(SELECT Avg(SharePrice) AS AvgPrice
FROM SharePrices
WHERE SharePrices.ShareID = tblShare.ShareID
AND SharePrices.[DateTime] Between
DateAdd("yyyy", -1, [Forms]![frmMstr]![cboTo])
And [Forms]![frmMstr]![cboTo]) AS YearAvg
FROM tblShare;

You could certainly use an INNER JOIN for one of those, but you need to use
subqueries like that for the other periods.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"ryguy7272" wrote in message
...
Whoops, just noticed one more thing, with the dates in there, I can't seem
to
roll up everything to an average per mutual fund. For instance, this
returns
180 results:
SELECT SharePrices.StockSymbol, Avg(SharePrices.[StockPrice]) AS AvgPrice
FROM SharePrices INNER JOIN tblStocksGroup ON SharePrices.StockSymbol =
tblStocksGroup.Ticker
GROUP BY SharePrices.DateTime, SharePrices.StockSymbol
HAVING (((SharePrices.DateTime) Between [Forms]![frmMstr]![cboFrom] And
[Forms]![frmMstr]![cboTo]));

This returns 9 results:
SELECT SharePrices.StockSymbol, Avg(SharePrices.[StockPrice]) AS AvgPrice
FROM SharePrices INNER JOIN tblStocksGroup ON SharePrices.StockSymbol =
tblStocksGroup.Ticker
GROUP BY SharePrices.StockSymbol;

The 9 results is right, and the average is right, but the dates are
removed,
so everything rolls up...but I need the dates in there to be able to do
these
calculations between a range of dates.

How do I make this work????????

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"ryguy7272" wrote:

Thanks again Allen! Your ideas were great and I think I’m almost done.
I’m
working with this now:
SELECT Avg(SharePrices.StockPrice) AS YTD
FROM tblStocksGroup INNER JOIN SharePrices ON tblStocksGroup.Ticker =
SharePrices.StockSymbol
WHERE
(((SharePrices.DateTime)=DateSerial(Year([Forms]![frmMstr]![cboFrom]),Month([Forms]![frmMstr]![cboFrom]),Day([Forms]![frmMstr]![cboFrom]))
And
(SharePrices.DateTime)=DateSerial(Year([Forms]![frmMstr]![cboTo]),Month([Forms]![frmMstr]![cboTo]),Day([Forms]![frmMstr]![cboTo]))));

I’m passing a From (date) and To (date) from a Form to a Query. That
gives
me an average return between whatever dates I select. I tested the
results
in Excel; this is right on now! I’m just trying to figure out a way to
do
this for up to a year. So, if I choose a range, like From (2/12/2009) To
(2/12/2010), I would be able to calculate an average return for 1-month
(21
days), 3-months (63-days), a 6-months (125-days), and
a 1-year (250-days) return.

Finally, I tested a parameter in the Query; it didn’t seem to work but
maybe
I set it up wrong. Do you think I need to prompt the user for an input
via a
parameter-prompt?

Thanks again!
Ryan---


--
Ryan---
If this information was helpful, please indicate this by clicking
''Yes''.


"Allen Browne" wrote:

Presumably you have a parameter in your query: when you run it, it pops
up
the parameter box and asks for the end date.

If this is named EndDate, you could ask for:
Between [EndDate] - 90 And [EndDate]
to specify the previous 90 days.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"ryguy7272" wrote in message
...
Wow! Very cool. I modified the SQL a bit and came up with this:
SELECT avg(SharePrices.StockPrice) AS YTD
FROM tblStocksGroup INNER JOIN SharePrices ON tblStocksGroup.Ticker =
SharePrices.StockSymbol
WHERE SharePrices.DateTime =
DateSerial(Year([SharePrices].[DateTime]),1,1)
AND SharePrices.DateTime DateSerial(Year([SharePrices].[DateTime]),
Month([SharePrices].[DateTime]) + 1, 1)

I run it and get a result of: YTD = $16.98

I'm not really sure what this is though. I know this is the average
return,
but I don't know what the time frame is. YTD, how is this
calculated,
since
Jan 1st, or 2nd (assuming market is closed for New Year's Day). I
may
have
to put my data in Excel and analyze it there. How can I modify this
to
prompt a user for an input, like in a TextBox, and pass this to the
Query
to
get a 1-month return (21 days), 3-month (63-days), 6-month
(125-days), and
1-year (250-days)?

Thanks everyone!!
Ryan--

--
Ryan---
If this information was helpful, please indicate this by clicking
''Yes''.


"Allen Browne" wrote:

Use subqueries to sum the amounts for the different periods.

The records for a month are different from the records for a year,
so you
need a different SELECT statement to get them. Therefore sub-SELECTs
will
be
the way to go.

There's an introduction to subqueries he
http://allenbrowne.com/subquery-01.html#YTD
It includes an example for getting year-to-date figures.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"ryguy7272" wrote in message
...
I’m using the below SQL, which works perfectly well. I’m trying
to
figure
out a way to add in a little calculation for mutual fund returns
for
1-month,
3-months, 6-months, and 12-months. I am thinking I would need to
prompt
the
user for a date, and then do the calculations based on that. It
wouldn’t
necessarily be today’s date, but a date the user chooses, minus
21-days
(assuming 21 days in a trading month), 63-days, 125-days, and
250-days
(assuming there are 260-days in a trading year, minus 9 holidays).

How can I incorporate this logic into my current query? I
originally
proposed this question a couple weeks ago, but haven’t been able
to
devote
any time to I until just now. When I first posted this question,
I
received
a response that said I may need to create 4 separate queries, and
then
pull
all results into one final query.

Here is the SQL that I’m working with right now:
SELECT SharePrices.DateTime, SharePrices.StockSymbol,
SharePrices.StockPrice, tblStocksGroup.Company,
tblStocksGroup.HDVest50k,
tblStocksGroup.HDVest100k, tblStocksGroup.ETF,
tblStocksGroup.NetJets,
tblStocksGroup.JetBlue
FROM SharePrices INNER JOIN tblStocksGroup ON
SharePrices.StockSymbol =
tblStocksGroup.Ticker
WHERE (((SharePrices.DateTime) Between [Forms]![frmMstr]![cboFrom]
And
[Forms]![frmMstr]![cboTo]) AND
((tblStocksGroup.HDVest50k)=[Forms]![frmMstr]![chkHDVest50k]) AND
((tblStocksGroup.HDVest100k)=[Forms]![frmMstr]![chkHDVest100k])
AND
((tblStocksGroup.ETF)=[Forms]![frmMstr]![chkETF]) AND
((tblStocksGroup.NetJets)=[Forms]![frmMstr]![chkNetJets]) AND
((tblStocksGroup.JetBlue)=[Forms]![frmMstr]![chkJetBlue]))
ORDER BY SharePrices.DateTime;

TIA!!!
Ryan--


--
Ryan---
If this information was helpful, please indicate this by clicking
''Yes''.

.

.

Yeap! That was it! I was up late last night, and spent some significant
time on that query, but just couldn't get it working. Now, however, it
works!! Thanks so much. As it turns out, I had to make a small modification
because it actually takes an average of 21 days, but as it turns out, I need
the average of 21 records to get an average of 30-days, so I just added this:
-21*(365/250)

Thanks for helping me to get this straightened out John!!!

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"John W. Vinson" wrote:

On Mon, 15 Feb 2010 20:28:01 -0800, ryguy7272
wrote:

I’m working with the SQL below, but it doesn’t roll up everything into one
line of average returns, over 21 days, per fund:
SELECT Avg(SharePrices.StockPrice) AS AvgOfStockPrice,
SharePrices.StockSymbol, SharePrices.DateTime
FROM tblStocksGroup INNER JOIN SharePrices ON tblStocksGroup.Ticker =
SharePrices.StockSymbol
GROUP BY SharePrices.StockSymbol, SharePrices.DateTime
HAVING (((SharePrices.DateTime) Between
DateSerial(Year([Forms]![frmMstr]![cboEnd]),Month([Forms]![frmMstr]![cboEnd]),Day([Forms]![frmMstr]![cboEnd]))
And
DateSerial(Year([Forms]![frmMstr]![cboEnd]),Month([Forms]![frmMstr]![cboEnd]),Day([Forms]![frmMstr]![cboEnd]-21))));

I think I’m really close now. How can I do this?

Use a WHERE clause (applied *before* the totalling) rather than a HAVING
clause (applied *after*), and don't group by the date. Best to also explicitly
define the parameter type:

PARAMETERS [Forms]![frmMstr]![cboEnd] DateTime;
SELECT Avg(SharePrices.StockPrice) AS AvgOfStockPrice,
SharePrices.StockSymbol, SharePrices.DateTime
FROM tblStocksGroup INNER JOIN SharePrices ON tblStocksGroup.Ticker =
SharePrices.StockSymbol
GROUP BY SharePrices.StockSymbol
WHERE (((SharePrices.DateTime) Between
DateSerial(Year([Forms]![frmMstr]![cboEnd]),Month([Forms]![frmMstr]![cboEnd]),Day([Forms]![frmMstr]![cboEnd]))
And
DateSerial(Year([Forms]![frmMstr]![cboEnd]),Month([Forms]![frmMstr]![cboEnd]),Day([Forms]![frmMstr]![cboEnd]-21))));

Slightly simpler, use the DateAdd function in the criteria:

PARAMETERS [Forms]![frmMstr]![cboEnd] DateTime;
SELECT Avg(SharePrices.StockPrice) AS AvgOfStockPrice,
SharePrices.StockSymbol, SharePrices.DateTime
FROM tblStocksGroup INNER JOIN SharePrices ON tblStocksGroup.Ticker =
SharePrices.StockSymbol
GROUP BY SharePrices.StockSymbol
WHERE SharePrices.DateTime = DateAdd("d", -21, [Forms]![frmMstr]![cboEnd])
AND SharePrices.DateTime = DateAdd("d", 1, [Forms]![frmMstr]![cboEnd]);

The dateadd at the end lets the user select (say) 2/15/2010 into cboEnd and
still return all records from midnight at the start of that day through
11:59:59.99999999 pm that day - otherwise you'll lose the last day's data.
--

John W. Vinson [MVP]
.