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
|
|||
|
|||
Calculate Percent Return for Stocks
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--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
#2
|
|||
|
|||
Calculate Percent Return for Stocks
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 ryguy7272 wrote: 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 http://www.accessmonster.com/Uwe/For...eries/201002/1 |
#3
|
|||
|
|||
Calculate Percent Return for Stocks
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''. "KenSheridan via AccessMonster.com" wrote: 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 ryguy7272 wrote: 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 http://www.accessmonster.com/Uwe/For...eries/201002/1 . |
#4
|
|||
|
|||
Calculate Percent Return for Stocks
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 ryguy7272 wrote: 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 http://www.accessmonster.com/Uwe/For...eries/201002/1 |
#5
|
|||
|
|||
Calculate Percent Return for Stocks
Thanks again Ken! Did a bit of research and found this link:
http://www.techonthenet.com/access/f...omain/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''. "KenSheridan via AccessMonster.com" wrote: 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 ryguy7272 wrote: 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 http://www.accessmonster.com/Uwe/For...eries/201002/1 . |
#6
|
|||
|
|||
Calculate Percent Return for Stocks
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''. "ryguy7272" wrote: Thanks again Ken! Did a bit of research and found this link: http://www.techonthenet.com/access/f...omain/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''. "KenSheridan via AccessMonster.com" wrote: 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 ryguy7272 wrote: 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 http://www.accessmonster.com/Uwe/For...eries/201002/1 . |
Thread Tools | |
Display Modes | |
|
|