Trades for current portfolio holdings only

[JapanFreak]

Dear All,

please find attached a small sample database which maybe better helps to explain my problem. I guess the solution is quite simple but I just don’t get it…

I have a database with a table called tblTrades in which I collect data on stock market transactions:

[B][I][FONT=Arial]tblTrades[/FONT][/I][/B]
[FONT=Arial]TradeID – dtDate – sTicker – iQuantity – dPrice[/FONT]
[FONT=Arial]1 – 01/03/2011 – A – 10 – 50.00[/FONT]
[FONT=Arial]2 – 01/03/2011 – B – 10 – 25.00[/FONT]
[FONT=Arial]…[/FONT]

Note:
If iQuantity > 0 it is a Buy, if iQuantity < 0 it is a Sell.

I can use the following query to get the list of stocks in my portfolio as per today or, if I add a WHERE clause (e.g. WHERE dtDate<=#01/04/2011#), for a certain point in time in the past:

[FONT=Arial]SELECT T.sTicker, sum(T.iQuantity) AS iPosition[/FONT]
[FONT=Arial]FROM tblTrades AS T[/FONT]
[FONT=Arial]GROUP BY T.sTicker[/FONT]
[FONT=Arial]HAVING sum(T.iQuantity)>0;[/FONT]

Note:
If sum(T.iQuantity) > 0 the trade is still open, i.e. the stock is part of the portfolio
If sum(T.iQuantity) = 0 the trade has been closed, i.e. the stock is no longer part of the portfolio

However, I struggle to formulate a query which gives me only the trades for the stocks which are in the portfolio currently (and have been part of the portfolio at a certain point in time in the past, respectively). That is, based on my sample data I would like to have the following result as per today:

[FONT=Arial]dtDate – sTicker – iQuantity – dPrice [/FONT]
[FONT=Arial]01/03/2011 – A – 10 – 50.00[/FONT]
[FONT=Arial]01/05/2011 – A – 10 – 55.00[/FONT]
[FONT=Arial]01/03/2011 – B – 10 – 25.00[/FONT]
[FONT=Arial]01/06/2011 – B – 10 – 20.00[/FONT]
[FONT=Arial]01/10/2011 – D – 50 – 5.00[/FONT]
[FONT=Arial]01/13/2011 – D – 25 – 6.00[/FONT]

…and for a certain date in the past (e.g. 01/04/2011):

[FONT=Arial]dtDate – sTicker – iQuantity – dPrice [/FONT]
[FONT=Arial]01/03/2011 – A – 10 – 50.00[/FONT]
[FONT=Arial]01/03/2011 – B – 10 – 25.00[/FONT]
[FONT=Arial]01/03/2011 – C – 15 – 5.00[/FONT]

Any ideas?

Thanks for your help –

JapanFreak

 

[gemma-the-husky]

you need two queries

first have a query to determine the current stocks (or those stocks you had at a given point) - which you already have, i think [active stocks query]

then you have your normal movements query


then have a third query that joins these two queries - and now you will only get data for the stocks in the [active stocks query]

 

[JapanFreak]

Hi gemma-the-husky,

thanks for your reply, that helped. The following query works:

SELECT Q.sTicker, T.dtDate, T.iQuantity, T.dPrice
FROM qryPortfolioHoldings AS Q LEFT JOIN tblTrades AS T ON Q.sTicker=T.sTicker
ORDER BY Q.sTicker, T.dtDate;

Thanks
JapanFreak