Calculate Percent Return for Stocks

[ryguy7272]

I am working with this SQL:
SELECT SharePrices.StockSymbol, SharePrices.StockPrice AS StartPrice,
SharePrices_2.StockPrice AS EndPrice,
DateAdd("d",-21*(365/250),[Forms]![frmMstr]![cboEnd]) AS Date30Start,
DateAdd("d",0,[Forms]![frmMstr]![cboEnd]) AS Date30End,
(SharePrices_2.StockPrice-SharePrices.StockPrice)/SharePrices.StockPrice AS
Pct_Return
FROM (tblStocksGroup INNER JOIN SharePrices ON
tblStocksGroup.Ticker=SharePrices.StockSymbol) INNER JOIN SharePrices AS
SharePrices_2 ON tblStocksGroup.Ticker=SharePrices_2.StockSymbol
GROUP BY SharePrices.StockSymbol, SharePrices.StockPrice,
SharePrices_2.StockPrice;

Darly S helped me out this, big time. It’s pretty close to what I was
looking for, but it definitely needs a tweak or two. Let me describe what
I’m trying to do. I have a table of stock symbols, historical stock prices,
and date. I have another table with stock symbols and all sorts of other
criteria. There is a join line connecting the stock symbols field in each
table. Daryl gave me the idea of using a table for SharePrices and another
table for SharePrices2. When the query runs, I see the following:

Stock Symbol, StartPrice, EndPrice, StartDate, EndDate, and PercentReturn

This is what I want, except the query is creating a Cartesian Product. I
think I need a GroupBy clause to prevent the Cartesian Product from
occurring. Then, when it runs, I think Access will return the correct
StartDate and the correct EndDate; right now it disaplay ALL DATES.
Furthermore, it displays every possible combination of StartPrice and
EndPrice, thus the Cartesian Product.
Just to give a little more information, I have a form, named frmMstr. The
form has 20 checkboxes and one combobox, from where I choose a date. I have
a summary query that uses these 20 checkboxes; that works fine. I’m trying
to feed the results of this query, as well as three others (the three others
will work fine once I figure out this one). I’m trying to set this up so a
user can pick a date from the combobox and the query will automatically take
that date (the EndDate) and subtract 30 days from it (the StartDate). Then,
the query will calculate the PercentReturn as
((EndDate-StartDate)/StartDate). How can I modify my SQL (above) to do that?

Thanks so much!
Ryan---

 

[ryguy7272]

Thanks Ken! I think you figured out a key point that I couldn't figure out.
If the date sequence is not completely continuous then you'd need to use
subqueries to find the nearest matching dates per stock symbol to the
parameters. The sequence of dates is market trade date plus 21 days, 62
days, 123 days, 250 days); 5 out of 7 days of the week. If I pick a certain
date (where the date + 21, + 62, plus 123, and +250 are all in the date
list), the query works, but if I pick a certain incorrect date (where the
results are NOT in the date list), these queries DO NOT WORK. Wow! This is
turning out to be one heck of a project. I though this was going to take 1/2
hour or so, but I've already put about 20 hours into this, and I'm still not
finished. Is there an easy way to test for the existence of a date, and if
it's not in the range, go to the next date in the range? I can't tell for
sure if the next date is 1 days away, 2 days away, 3 days away (for a long
holiday weekend), or perhaps more days -- this past year the market was open
12/31/2009 and then trading didn't resume until 1/4/2010. I don't think I
necessarily want to round up the days or use an IIF. Is there a function
that can test for the next date if the selected date + 21 days is NOT in the
date range?

Thanks!
Ryan---

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

I think you need to restrict the query by means of a WHERE clause. Grouping
is unnecessary as you are not aggregating any values:

PARAMETERS [Forms]![frmMstr]![cboEnd] DATETIME;
SELECT SharePrices.StockSymbol, SharePrices.StockPrice AS StartPrice,
SharePrices_2.StockPrice AS EndPrice,
SharePrices_2.PriceDate AS Date30Start,
SharePrices.PriceDate AS Date30End,
(SharePrices_2.StockPrice-SharePrices.StockPrice)/SharePrices.StockPrice AS
Pct_Return
FROM SharePrices INNER JOIN SharePrices AS SharePrices_2
ON SharePrices.StockSymbol = SharePrices_2.StockSymbol
WHERE SharePrices_2.PriceDate = [Forms]![frmMstr]![cboEnd]
AND SharePrices.PriceDate = DateAdd("d",-30,[Forms]![frmMstr]![cboEnd]);

I've assumed a column PriceDate in the SharePrices table.

I see no need for the tblStocksGroup table in the query as none of its
columns are being returned or used in any expression.

Note that it’s a good idea to declare parameters of date/time data type as
such to avoid any possible misinterpretation of the value as an arithmetical
expression.

Also the expression 21*(365/250) serves no purpose as it will be rounded to
30 due to the DateAdd function expecting an integer as its second argument.

This assumes no more or less that one row in the table for every
StockSymbol/PriceDate of course. If the date sequence is not completely
continuous then you'd need to use subqueries to find the nearest matching
dates per stock symbol to the parameters.

Ken Sheridan
Stafford, England

I am working with this SQL:
SELECT SharePrices.StockSymbol, SharePrices.StockPrice AS StartPrice,
SharePrices_2.StockPrice AS EndPrice,
DateAdd("d",-21*(365/250),[Forms]![frmMstr]![cboEnd]) AS Date30Start,
DateAdd("d",0,[Forms]![frmMstr]![cboEnd]) AS Date30End,
(SharePrices_2.StockPrice-SharePrices.StockPrice)/SharePrices.StockPrice AS
Pct_Return
FROM (tblStocksGroup INNER JOIN SharePrices ON
tblStocksGroup.Ticker=SharePrices.StockSymbol) INNER JOIN SharePrices AS
SharePrices_2 ON tblStocksGroup.Ticker=SharePrices_2.StockSymbol
GROUP BY SharePrices.StockSymbol, SharePrices.StockPrice,
SharePrices_2.StockPrice;

Darly S helped me out this, big time. It’s pretty close to what I was
looking for, but it definitely needs a tweak or two. Let me describe what
I’m trying to do. I have a table of stock symbols, historical stock prices,
and date. I have another table with stock symbols and all sorts of other
criteria. There is a join line connecting the stock symbols field in each
table. Daryl gave me the idea of using a table for SharePrices and another
table for SharePrices2. When the query runs, I see the following:

Stock Symbol, StartPrice, EndPrice, StartDate, EndDate, and PercentReturn

This is what I want, except the query is creating a Cartesian Product. I
think I need a GroupBy clause to prevent the Cartesian Product from
occurring. Then, when it runs, I think Access will return the correct
StartDate and the correct EndDate; right now it disaplay ALL DATES.
Furthermore, it displays every possible combination of StartPrice and
EndPrice, thus the Cartesian Product.
Just to give a little more information, I have a form, named frmMstr. The
form has 20 checkboxes and one combobox, from where I choose a date. I have
a summary query that uses these 20 checkboxes; that works fine. I’m trying
to feed the results of this query, as well as three others (the three others
will work fine once I figure out this one). I’m trying to set this up so a
user can pick a date from the combobox and the query will automatically take
that date (the EndDate) and subtract 30 days from it (the StartDate). Then,
the query will calculate the PercentReturn as
((EndDate-StartDate)/StartDate). How can I modify my SQL (above) to do that?

Thanks so much!
Ryan---

--
Message posted via AccessMonster.com


.

 

[ryguy7272]

Thanks again Ken! Did a bit of research and found this link:
http://www.techonthenet.com/access/functions/domain/dmin.php

Of course, I looked at your reply too. Unfortunately, I'm still not getting
it...
This is what I'm working with now:
SELECT Min(SharePrices.DateTime) AS MinOfDateTime, SharePrices.StockSymbol,
SharePrices.StockPrice
FROM SharePrices
WHERE ((([Forms]![frmMstr]![cboEnd])=(SELECT MIN([Forms]![frmMstr]![cboEnd])
FROM SharePrices
WHERE [Forms]![frmMstr]![cboEnd] >=
DateAdd("d",250,[Forms]![frmMstr]![cboEnd]))))
GROUP BY SharePrices.StockSymbol, SharePrices.StockPrice;

Again, with the wrong date (where the results are NOT in the date list), the
queries DO NOT WORK.

I would surmise that it is an easy fix from here, but I'm still at a loss as
to what to do. Can you please give me a push to get over this final hurdle?


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

As regards a function you could call the DMin function to return the
earliest date >= the parameter date+21 where the stock symbol value = the
current stock symbol value, e.g.

WHERE TradeDate = DMin("TradeDate", "SharePrices", "StockSymbol = """ &
[StockSymbol] & """ And TradeDate >= #" & Format(DateAdd("d",21,[Enter Date:])
,"yyyy-mm-dd") & "#")

I've assumed StockSymbol is a text data type in the above.

Or if for every date in the table all stocks are represented, you can omit
StockSymbol from the criteria:

WHERE TradeDate = DMin("TradeDate", "SharePrices", "TradeDate >= #" & Format
(DateAdd("d",21,[Enter Date:]),"yyyy-mm-dd") & "#")

Or, more usually, you can use a subquery in the same way:

WHERE TradeDate =
(SELECT MIN(TradeDate)
FROM SharePrices AS SP1
WHERE SP1.StockSymbol = SharePrices.StockSymbol
AND TradeDate >= DATEADD("d",21,[Enter Date:]))

In this case the data type of StockSymbol is immaterial and there is no need
to format the date to the ISO standard as when building the criteria
expression for the DMin function call.

Again, if the correlation on StockSymbol can be omitted:

WHERE TradeDate =
(SELECT MIN(TradeDate)
FROM SharePrices
WHERE TradeDate >= DATEADD("d",21,[Enter Date:]))

which would perform far better in view of the absence of correlation with the
outer query. Note that you don't need to alias the second instance of the
table in this case.

Ken Sheridan
Stafford, England

Thanks Ken! I think you figured out a key point that I couldn't figure out.
If the date sequence is not completely continuous then you'd need to use
subqueries to find the nearest matching dates per stock symbol to the
parameters. The sequence of dates is market trade date plus 21 days, 62
days, 123 days, 250 days); 5 out of 7 days of the week. If I pick a certain
date (where the date + 21, + 62, plus 123, and +250 are all in the date
list), the query works, but if I pick a certain incorrect date (where the
results are NOT in the date list), these queries DO NOT WORK. Wow! This is
turning out to be one heck of a project. I though this was going to take 1/2
hour or so, but I've already put about 20 hours into this, and I'm still not
finished. Is there an easy way to test for the existence of a date, and if
it's not in the range, go to the next date in the range? I can't tell for
sure if the next date is 1 days away, 2 days away, 3 days away (for a long
holiday weekend), or perhaps more days -- this past year the market was open
12/31/2009 and then trading didn't resume until 1/4/2010. I don't think I
necessarily want to round up the days or use an IIF. Is there a function
that can test for the next date if the selected date + 21 days is NOT in the
date range?

Thanks!
Ryan---

I think you need to restrict the query by means of a WHERE clause. Grouping
is unnecessary as you are not aggregating any values:

[quoted text clipped - 72 lines]

Thanks so much!
Ryan---

--
Message posted via AccessMonster.com


.

 

[ryguy7272]

Ok, finally got this working! Here's the final SQL:
SELECT Min(SharePrices.DateTime) AS MinOfDateTime, SharePrices.StockSymbol,
SharePrices.StockPrice
FROM SharePrices
WHERE DateTime=DMin("DateTime","SharePrices","DateTime >= #" &
Format(DateAdd("d",21,Forms!frmMstr!cboEnd),"yyyy-mm-dd") & "#")
GROUP BY SharePrices.StockSymbol, SharePrices.StockPrice;

Thanks for the help Ken!! Couldn't have done it without you!!

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

Thanks again Ken! Did a bit of research and found this link:
http://www.techonthenet.com/access/functions/domain/dmin.php

Of course, I looked at your reply too. Unfortunately, I'm still not getting
it...
This is what I'm working with now:
SELECT Min(SharePrices.DateTime) AS MinOfDateTime, SharePrices.StockSymbol,
SharePrices.StockPrice
FROM SharePrices
WHERE ((([Forms]![frmMstr]![cboEnd])=(SELECT MIN([Forms]![frmMstr]![cboEnd])
FROM SharePrices
WHERE [Forms]![frmMstr]![cboEnd] >=
DateAdd("d",250,[Forms]![frmMstr]![cboEnd]))))
GROUP BY SharePrices.StockSymbol, SharePrices.StockPrice;

Again, with the wrong date (where the results are NOT in the date list), the
queries DO NOT WORK.

I would surmise that it is an easy fix from here, but I'm still at a loss as
to what to do. Can you please give me a push to get over this final hurdle?


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

As regards a function you could call the DMin function to return the
earliest date >= the parameter date+21 where the stock symbol value = the
current stock symbol value, e.g.

WHERE TradeDate = DMin("TradeDate", "SharePrices", "StockSymbol = """ &
[StockSymbol] & """ And TradeDate >= #" & Format(DateAdd("d",21,[Enter Date:])
,"yyyy-mm-dd") & "#")

I've assumed StockSymbol is a text data type in the above.

Or if for every date in the table all stocks are represented, you can omit
StockSymbol from the criteria:

WHERE TradeDate = DMin("TradeDate", "SharePrices", "TradeDate >= #" & Format
(DateAdd("d",21,[Enter Date:]),"yyyy-mm-dd") & "#")

Or, more usually, you can use a subquery in the same way:

WHERE TradeDate =
(SELECT MIN(TradeDate)
FROM SharePrices AS SP1
WHERE SP1.StockSymbol = SharePrices.StockSymbol
AND TradeDate >= DATEADD("d",21,[Enter Date:]))

In this case the data type of StockSymbol is immaterial and there is no need
to format the date to the ISO standard as when building the criteria
expression for the DMin function call.

Again, if the correlation on StockSymbol can be omitted:

WHERE TradeDate =
(SELECT MIN(TradeDate)
FROM SharePrices
WHERE TradeDate >= DATEADD("d",21,[Enter Date:]))

which would perform far better in view of the absence of correlation with the
outer query. Note that you don't need to alias the second instance of the
table in this case.

Ken Sheridan
Stafford, England

Thanks Ken! I think you figured out a key point that I couldn't figure out.
If the date sequence is not completely continuous then you'd need to use
subqueries to find the nearest matching dates per stock symbol to the
parameters. The sequence of dates is market trade date plus 21 days, 62
days, 123 days, 250 days); 5 out of 7 days of the week. If I pick a certain
date (where the date + 21, + 62, plus 123, and +250 are all in the date
list), the query works, but if I pick a certain incorrect date (where the
results are NOT in the date list), these queries DO NOT WORK. Wow! This is
turning out to be one heck of a project. I though this was going to take 1/2
hour or so, but I've already put about 20 hours into this, and I'm still not
finished. Is there an easy way to test for the existence of a date, and if
it's not in the range, go to the next date in the range? I can't tell for
sure if the next date is 1 days away, 2 days away, 3 days away (for a long
holiday weekend), or perhaps more days -- this past year the market was open
12/31/2009 and then trading didn't resume until 1/4/2010. I don't think I
necessarily want to round up the days or use an IIF. Is there a function
that can test for the next date if the selected date + 21 days is NOT in the
date range?

Thanks!
Ryan---

I think you need to restrict the query by means of a WHERE clause. Grouping
is unnecessary as you are not aggregating any values:
[quoted text clipped - 72 lines]
Thanks so much!
Ryan---

--
Message posted via AccessMonster.com


.