Weighted Averages in sub-query...

[JapanFreak]

Hi there,

once again I have a problem for which I am looking for some hints...

I still have one table, called tblTransactions, which contains security market transactions. For each buy and sell order, respectively it contains one data set with columns Date, Ticker (i.e. the unique identifier of each security), Quantity (positive for buy, negative for sell orders) and Price (at which the trade was executed).

The following code gives me all stocks, which are no longer part of the portfolio since they have been sold out completely for any arbitrarily chosen date (here 1/30/07) together with the date, on which the last position in a certain stock (identified by the ticker) were sold:

SELECT T.Ticker, max(T.Date) AS SellDate
FROM tblTransactions AS T
WHERE T.Date<=#1/30/2007#
GROUP BY T.Ticker
HAVING sum(T.Qty) =0
ORDER BY T.Ticker;

Now it becomes complicated: What I am looking for is a sub-query, which I want to add to the code above and which gives me the weighted average price at which the stocks were bought and sold, respectively if there have been more than one buy or sell transaction.

That is, for the following sample data of tblTransactions...

Date --- Ticker --- Quantity --- Price
01/01/07 --- AAA --- 50 --- $50
01/01/07 --- BBB --- 25 --- $75
01/10/07 --- BBB --- 75 --- $100
01/15/07 --- AAA --- 30 --- $60
01/20/07 --- BBB --- -100 --- $100
01/25/07 --- AAA --- -40 --- $120
01/26/07 --- AAA --- -40 --- $100

...the query I am looking for should give the following result as per 01/30/07:

Ticker --- SellDate --- WeightedAvgEntry --- WeightedAvgExit
AAA --- 01/26/07 --- $53.75 1) --- $110.00 3)
BBB --- 01/20/07 --- $93.75 2) --- $100.00

Notes / how to calculate the weighted averages:
1) (50*$50 + 30*$60) / 80 = $53.75
2) (25*$75 + 75*$100) / 100 = $93.75
3) (40*$120 + 40*$100) / 80 = $110.00

I do not have any clue at all how to solve that problem. I would be very happy for any hint that could lead to the right direction.

Best regards
JapanFreak

 

[petehilljnr]

Given the setup of your table shown ... how do you distinguish between what is the Entry (buy) price and the Exit (sell) price?

Is there a column missing from your sample data?

 

[JapanFreak]

Clarification

Hi,

thank you for your reply. No, there is nothing missing, it depends on whether Quantity is >0 or <0:

... Quantity (positive for buy, negative for sell orders)...

Best regards,
JapanFreak

 

[petehilljnr]

... Quantity (positive for buy, negative for sell orders)...

sweet - missed that!

In that case:

Use 3 queries - (you can get it down to 2, but easier to show you in 3)

qryTransactions: - Use this query to filter on the dates you want (change code in bold)

SELECT tblTransactions.*
FROM tblTransactions
WHERE (((tblTransactions.Date) [B]Between #Your Start Date# And #Your End Date#[/B]));

qryBuysAndSells: - Compiles all your buys and sells for the period defined in qryTransactions

SELECT qryTransactions.Ticket, "WeightedAvgExit" AS MyText, Max(qryTransactions.Date) AS MyDate, Sum(qryTransactions.Quantity*qryTransactions.Price)/Sum(qryTransactions.Quantity) AS MyValue
FROM qryTransactions
WHERE (((qryTransactions.Quantity)<0))
GROUP BY qryTransactions.Ticket, "WeightedAvgExit"
HAVING Sum(qryTransactions.Quantity) <> 0
UNION SELECT qryTransactions.Ticket, "WeightedAvgEntry" AS MyText, Max(qryTransactions.Date) AS MyDate, Sum([qryTransactions].[Quantity]*[qryTransactions].[Price])/Sum([qryTransactions].[Quantity]) AS MyValue
FROM qryTransactions
WHERE (((qryTransactions.Quantity)>0))
GROUP BY qryTransactions.Ticket, "WeightedAvgEntry"
HAVING Sum(qryTransactions.Quantity) <> 0;

qryResults:

TRANSFORM Avg(qryBuysAndSells.MyValue) AS AvgOfMyValue
SELECT qryBuysAndSells.Ticket, Max(qryBuysAndSells.MyDate) AS SellDate
FROM qryBuysAndSells
GROUP BY qryBuysAndSells.Ticket
PIVOT qryBuysAndSells.MyText;

The reason I used a cross tab query was to cover the scenario where in the period given, their was either no buys or no sells (they would appear as blanks - hence the condition HAVING Sum(qryTransactions.Quantity) <> 0)

Regards,
Pete

 

[petehilljnr]

Actually - on second thoughts, that's a crap way of doing it.

This would be better:

qryTransactions:

SELECT tblTransactions.*
FROM tblTransactions
WHERE (((tblTransactions.Date) Between #Your Start Date# And #Your End Date#));

qryBuys:

SELECT qryTransactions.Ticket, Max(qryTransactions.Date) AS BuyDate, Sum([qryTransactions].[Quantity]*[qryTransactions].[Price])/Sum([qryTransactions].[Quantity]) AS MyWeightAvgEntry
FROM qryTransactions
WHERE (((qryTransactions.Quantity)>0))
GROUP BY qryTransactions.Ticket
HAVING (((Sum(qryTransactions.Quantity))<>0));

qrySells:

SELECT qryTransactions.Ticket, Max(qryTransactions.Date) AS SellDate, Sum([qryTransactions].[Quantity]*[qryTransactions].[Price])/Sum([qryTransactions].[Quantity]) AS MyWeightAvgExit
FROM qryTransactions
WHERE (((qryTransactions.Quantity)<0))
GROUP BY qryTransactions.Ticket
HAVING (((Sum(qryTransactions.Quantity))<>0));

qryBuysAndSells:

SELECT qryTransactions.Ticket, qryBuys.BuyDate, qrySells.SellDate, qryBuys.MyWeightAvgEntry, qrySells.MyWeightAvgExit
FROM (qryTransactions LEFT JOIN qryBuys ON qryTransactions.Ticket = qryBuys.Ticket) LEFT JOIN qrySells ON qryTransactions.Ticket = qrySells.Ticket
GROUP BY qryTransactions.Ticket, qryBuys.SellDate, qrySells.SellDate, qryBuys.MyWeightAvgEntry, qrySells.MyWeightAvgExit;

 

[JapanFreak]

Problem solved.

Pete,

thank you so much for your help. Both alternatives work. Actually, it took some time until I fully understood what you are doing in the first one. It contains several things I did not know before. Thank you for helping me to learn that stuff.

Once again thank you very much.

Best regards,
JapanFreak