Multiple Select Query - Stock Growth Over Time Periods

[craigb]

Hi,



I have created a MS Access Database with a mysql backend for managing stock portfolio (approx 40 stocks). What I am wanting to do is create a query to show gain / loss for each stock in a 1 week, 1 month, 3 month, 6 month, 12 month, 18 month time periods. I know how I could potentially do this using 7 queries (making one for each time period and then another one to join it all but hoping to do it in a better way.

My Table Structure is as Follows

tblStocksList - This table lists the individual stocks and various information about them but the key fields are listed below
- StockID (Primary Key)
- FundName
- Ticker

tblHistory - This table has data stored daily for each stock detailing various details about that stock at the close of each day. Key fields are
- HistoryID (Primary Key - But not utilised for query)
- StockID
- rDate - Has the date the data was recorded
- ClosePrice - This is the value of the stock at the close of each day

The Desired output is as follows
StockID | FundName | Ticker | 1WkClosePrice | 1MthClosePrice | 3MthClosePrice ... ... ... etc.

Where 1WkClosePrice is the ClosePrice a week ago (from today) or as close to as possible (ie if a weekend or non trading day), 1MthClosePrice is the ClosePrice from 1 Month ago for that stock or as close to as possible and so on. Note: Purchase date is not relevant. Pulling data from these periods will allow overview of how a stock has performed over the short, medium and longer term.

I have tried to set the query up with multiple versions of the tblHistory table which I have managed to make work for 2 periods but when it gets to adding the 3rd it just freezes up when I run it and even 10 minutes later still trying to run it. Also tried utilising Dlookups in the query and just utilising criteria but couldn't get that to work but think that is likely because need the multiple criteria and could only get date to work (but stockID wasn't linking)

Would like some advise if doing the 7 queries or whatever is the best solution or is it better to do this another way

The other option I am contemplating is generating a table via vba but can't see that would be the logical choice. Any advice how to best handle this from a method, cleanliness and speed aspects would be appreciated.

[CJ_London]

not sure what the problem is as you have described the symptom but not how it is generated - suggest post the sql you your query(s). And clarify what is the 1wk/mth etc close price? 1wk/mth etc from what? the purchase date? 1st Jan? Perhaps provide some example data to cover all eventualities and the required outcome

But on the limited information provided I would have thought a crosstab query would be the way to go.

[craigb]

Thanks Ajax for your reply,

I have edited my original post to make it a little clearer and also part of the formatting screwed up originally which may have contributed to some confusion.

Looking at a crosstab while that gives me the data I don't know how to tell it to just show select dates (ie. 1 week ago, 1 month ago, 3 months ago and so on without all the other dates inbetween). Also keeping in mind that my dataset currently has over a years work of data (368 dates so far) so crosstab doesn't appear to allow me to place dates across the top unless I can filter just the dates I want somehow and instead I have the StockID across the top. This is probably ideal solution if I can transpose the rows / columns data, set dates for the date paramater across the top to just select dates and provide a heading for each of these columns.

TRANSFORM Sum(tblHistory.ClosePrice) AS SumOfClosePriceSELECT tblHistory.rDate
FROM tblHistory
GROUP BY tblHistory.rDate
PIVOT tblHistory.StockID;

The starting phase of A select query is as follows but can't get it beyond this without it crashing

SELECT TOP 1 tblHistory.StockID, Last(tblHistory.ClosePrice) AS CurrentPrice, tblHistory_1.ClosePrice AS 1WkClosePrice, tblHistory_1.rDate AS rDate01wFROM tblHistory LEFT JOIN tblHistory AS tblHistory_1 ON tblHistory.StockID = tblHistory_1.StockID
GROUP BY tblHistory.StockID, tblHistory_1.ClosePrice, tblHistory_1.rDate
HAVING (((tblHistory_1.rDate)<DateAdd("ww","-1",Date())))
ORDER BY tblHistory_1.rDate DESC;

Note I have got this solution for a similar function that is utilising heaps of queries and does work but it isn't clean and makes the queries list I think very messy to have so many queries for a single element of my reports but if thats what I need then I will go down this similar route but just want to confirm that in theory it is the best / only way to go. In this scenario there is basically the StockID, TimePeriodDate (Calculated with DateAdd function) and the corresponding ClosePrice on that day in each query. Then have a master query that merges it all together with the common link being StockID

SELECT qryStockOverview01Month.StockID, qryStockOverviewNow.CurrentPrice, qryStockOverviewNow.rDateCurrent, qryStockOverview01Month.[1Month], qryStockOverview01Month.rDate01, Round(([CurrentPrice]/[1Month]*100)-100,2) AS Calc1, qryStockOverview03Month.[3Month], qryStockOverview03Month.rDate03, qryStockOverview06Month.[6Month], qryStockOverview06Month.rDate06, qryStockOverview12Month.[12Month], qryStockOverview12Month.rDate12, qryStockOverview18Month.[18Month], qryStockOverview18Month.rDate18, Round(([CurrentPrice]/[3Month]*100)-100,2) AS Calc3, Round(([CurrentPrice]/[6Month]*100)-100,2) AS Calc6, tblStocksList.Ticker, tblStocksList.FundNameFROM ((qryStockOverviewVolatile1Month RIGHT JOIN (qryStockOverviewVolatile3Month RIGHT JOIN (qryStockOverviewVolatile6Month RIGHT JOIN (((((qryStockOverview01Month LEFT JOIN qryStockOverview03Month ON qryStockOverview01Month.StockID = qryStockOverview03Month.StockID) LEFT JOIN qryStockOverview06Month ON qryStockOverview01Month.StockID = qryStockOverview06Month.StockID) LEFT JOIN qryStockOverview12Month ON qryStockOverview01Month.StockID = qryStockOverview12Month.StockID) LEFT JOIN qryStockOverview18Month ON qryStockOverview01Month.StockID = qryStockOverview18Month.StockID) LEFT JOIN qryStockOverviewNow ON qryStockOverview01Month.StockID = qryStockOverviewNow.StockID) ON qryStockOverviewVolatile6Month.StockID = qryStockOverview01Month.StockID) ON qryStockOverviewVolatile3Month.StockID = qryStockOverview01Month.StockID) ON qryStockOverviewVolatile1Month.StockID = qryStockOverview01Month.StockID) LEFT JOIN qry52WeekBar ON qryStockOverview01Month.StockID = qry52WeekBar.StockID) LEFT JOIN tblStocksList ON qryStockOverview01Month.StockID = tblStocksList.StockID
GROUP BY qryStockOverview01Month.StockID, qryStockOverviewNow.CurrentPrice, qryStockOverviewNow.rDateCurrent, qryStockOverview01Month.[1Month], qryStockOverview01Month.rDate01, Round(([CurrentPrice]/[1Month]*100)-100,2), qryStockOverview03Month.[3Month], qryStockOverview03Month.rDate03, qryStockOverview06Month.[6Month], qryStockOverview06Month.rDate06, qryStockOverview12Month.[12Month], qryStockOverview12Month.rDate12, qryStockOverview18Month.[18Month], qryStockOverview18Month.rDate18, Round(([CurrentPrice]/[3Month]*100)-100,2), Round(([CurrentPrice]/[6Month]*100)-100,2), tblStocksList.Ticker, tblStocksList.FundName;

[CJ_London]

the filtering should be straightforward - something like this for weeks

Where rdate>=dateadd("ww",-[Enter Number of Weeks],date())

tho 'nearest' is more of a problem - the above won't capture earlier dates. If you really want that, it is more complicated.

But if you wanted from 'start of a week' then that would be something like this for Monday

Where rdate>=dateadd("ww",-[Enter Number of Weeks],date())-weekday(dateadd("ww",-[Enter Number of Weeks],date()),2)+1

replace [Enter Number of Weeks] with a reference to a form control if required

if you want to select week/month etc perhaps something like

Where rdate>=dateadd([Enter ww for weeks, m for months,q for quarters, yyyy for years],-[Enter Number of Weeks],date())

again change to reference a form if required.

Link to the dateadd parameter functions
https://support.microsoft.com/en-us/...4-61e8c57afc19


if you just want the record for the start date then perhaps something like this as an initial query

SELECT StockID, min(rDate) as sDate
FROM tblHistory
WHERE rDate>=dateadd("ww",-[Enter Number of Weeks],date())

which you can then join to tblHistory on stockID and sDate to find the price on that day

[craigb]

Hi Ajax,

Nearest was probably a poor choice of word. Currently in other functions I am going back "x" period and then finding the most recent record before that date. Generally that date or perhaps up to 2 days before if the calculated date is a Sunday for example.

As for the other parts of your response. The issue I am having is making the joins between the different periods so they all form one query result (ie. 1 Week Close Price, 1 Month Close Price, 3 Month Close Price etc. all in the same output.

To achieve this should I just have the 7 odd separate queries and then one to merge them all. I can achieve it this way but is this the "best way". If not then how do I manage the join / filtering and which type of query would be recommended.

[CJ_London]

Currently in other functions I am going back "x" period and then finding the most recent record before that date.

in that case use

SELECT StockID, max(rDate) as sDate
FROM tblHistory
WHERE rDate<=dateadd("ww",-[Enter Number of Weeks],date())

all form one query result

you could use a union query based on the above

SELECT StockID, max(rDate) as sDate
FROM tblHistory
WHERE rDate<=dateadd("ww",-1,date())
GROUP BY StockID
UNION ALL SELECT StockID, max(rDate) as sDate
FROM tblHistory
WHERE rDate<=dateadd("m",-1,date())
GROUP BY StockID
etc

or a separate table for your periods

tblPeriods
pInt..pNum
ww......1
m........1
m........3
q.........1
etc

and a cartesian query

SELECT StockID, pIint & num as Period, max(rDate) as sDate
FROM tblHistory, tblPeriods
WHERE rDate<=dateadd(pInt,-pNum,date())
GROUP BY StockID, pIint & num

[craigb]

Thanks Ajax,

The Cartesian query was exactly what I needed. I have expanded on the Periods table to provide a meaningful name and then run a crosstab query off that and the output is pretty close to exactly what I am after. Just need to tweak it some more but should be pretty right.

I knew there had to be a better way than either 7+ queries or creating a table through VBA but often I go the hard or wrong solutions up front when there is a far simpler solution.

As a result of this will also plan to go back and condense the other similar large messy queries based on similar periods with this method.

Thanks again for your assistance on this one been a big help not just on this one but the overall database design.

[CJ_London]

happy to help