Calculate Average Returns Based on Dates?

[ryguy7272]

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--

 

[Allen Browne]

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 here:
http://allenbrowne.com/subquery-01.html#YTD
It includes an example for getting year-to-date figures.

 

[ryguy7272]

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''.

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 here:
http://allenbrowne.com/subquery-01.html#YTD
It includes an example for getting year-to-date figures.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

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--

.

 

[Allen Browne]

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

Reply to group, rather than allenbrowne at mvps dot org.

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''.

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 here:
http://allenbrowne.com/subquery-01.html#YTD
It includes an example for getting year-to-date figures.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

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--

.

 

[ryguy7272]

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''.

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

Reply to group, rather than allenbrowne at mvps dot org.

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''.

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 here:
http://allenbrowne.com/subquery-01.html#YTD
It includes an example for getting year-to-date figures.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


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''.

.

.

 

[ryguy7272]

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''.

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''.

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

Reply to group, rather than allenbrowne at mvps dot org.

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''.


:

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 here:
http://allenbrowne.com/subquery-01.html#YTD
It includes an example for getting year-to-date figures.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


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''.

.

.

 

[Allen Browne]

You shouldn't have to use DateSerial(). Instead declare the parameter so JET
understands it correctly. Open the Parameters dialog (Parameters on
ribbon/toolbar) and enter 2 rows like this:
[Forms]![frmMstr]![cboFrom] Date/Time
[Forms]![frmMstr]![cboTo] Date/Time

You should then be able to use:
WHERE SharePrices.DateTime Between [Forms]![frmMstr]![cboFrom] And
[Forms]![frmMstr]![cboTo]

You may need to use DateAdd() to get exactly what you want, e.g. the
previous calendar year starts:
DateAdd("yyyy", -1, [Forms]![frmMstr]![cboFrom])

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

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''.

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

Reply to group, rather than allenbrowne at mvps dot org.

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''.


:

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 here:
http://allenbrowne.com/subquery-01.html#YTD
It includes an example for getting year-to-date figures.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


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''.

.

.

 

[Allen Browne]

If you GROUP BY the dates, you will get a different result for every date,
so I guess that makes sense.

Not sure of your goal, but perhaps you need to use a subquery to get the
total. The subquery will use a WHERE clause that limits it to the parameters
in the main query, but it won't GROUP BY anything (just return a Sum() or
Avg().)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

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''.

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''.

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

Reply to group, rather than allenbrowne at mvps dot org.


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''.


:

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 here:
http://allenbrowne.com/subquery-01.html#YTD
It includes an example for getting year-to-date figures.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


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''.

.

.

 

[ryguy7272]

Yeap, I probably need a subquery; don’t know how to set this up though.
Let’s say I am trying to return mutual fund symbols that meet certain
criteria such as Security Name, Company, Group, Class, and a fee others (this
part of the query is done; fine there). I’m trying to design a query that
returns these results, but only for between certain dates and averages the
results. So, if I enter a date range of 125 days, I don’t want to see TRBCX
show up 125 times (one for each date); I want to see an average return of
this fund during the 125 days. How do I set up a subquery to do this?

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

If you GROUP BY the dates, you will get a different result for every date,
so I guess that makes sense.

Not sure of your goal, but perhaps you need to use a subquery to get the
total. The subquery will use a WHERE clause that limits it to the parameters
in the main query, but it won't GROUP BY anything (just return a Sum() or
Avg().)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

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''.

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''.


:

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

Reply to group, rather than allenbrowne at mvps dot org.


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''.


:

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 here:
http://allenbrowne.com/subquery-01.html#YTD
It includes an example for getting year-to-date figures.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


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''.

.

.

.

 

[ryguy7272]

Ok, after wrestling with this for a bit more, I think I finally figured this
thing out! I just need one single date, i.e. [cboEnd]. This is still a
parameter, but I don't think I need 2-dates in my parameters clause.

All I need to do is prompt the user for a date, let’s say 2/10/2010, and
have Access find the average of fund returns between 2/10/2010 and
2/10/2010-21 days (trading days, not calendar days, but this should work out
fine because only trading days are in my table). So, I’m playing around with
it, but just getting all records showing, not an average of the 21 days back
from 2/10/2010. I think I’m going to need to employ a subquery, because
nothing that I’ve tried seems to work. Unfortunately I can’t get those
subqueries working wither.

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?

Thanks!
Ryan--


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

You shouldn't have to use DateSerial(). Instead declare the parameter so JET
understands it correctly. Open the Parameters dialog (Parameters on
ribbon/toolbar) and enter 2 rows like this:
[Forms]![frmMstr]![cboFrom] Date/Time
[Forms]![frmMstr]![cboTo] Date/Time

You should then be able to use:
WHERE SharePrices.DateTime Between [Forms]![frmMstr]![cboFrom] And
[Forms]![frmMstr]![cboTo]

You may need to use DateAdd() to get exactly what you want, e.g. the
previous calendar year starts:
DateAdd("yyyy", -1, [Forms]![frmMstr]![cboFrom])

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

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''.

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

Reply to group, rather than allenbrowne at mvps dot org.


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''.


:

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 here:
http://allenbrowne.com/subquery-01.html#YTD
It includes an example for getting year-to-date figures.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


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''.

.

.

.

 

[John W. Vinson]

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.

 

[Allen Browne]

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

Reply to group, rather than allenbrowne at mvps dot org.

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''.

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''.

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

Reply to group, rather than allenbrowne at mvps dot org.


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''.


:

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 here:
http://allenbrowne.com/subquery-01.html#YTD
It includes an example for getting year-to-date figures.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


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''.

.

.

 

[ryguy7272]

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''.

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.