Calculating profit and loss and open positions by date

[rowbro]

Hi all,

I have a tradesheet from my equity portfolio that looks something like the below (but much much longer). I have this in a table in an Access database at the moment. I have two queries that I was hoping someone could assist with:

1. I would like to be able to look at the open positions (i.e. where the number of shares bought>number sold) on any given date (i.e. from inception to the given date).

2. The profit or loss on positions closed to date (i.e. the weighted average price of the "sells" of any given stock -e.g. Advtech Ltd, less the weighted average price of all the "buys" of any given stock).

Can anyone assist with ideas on queries that would achieve these?

Thanks in advance,

[TABLE="width: 631"]
<tbody>[TR]
[TD]TransactionID
[/TD]
[TD]TransactionDate
[/TD]
[TD]Instrument
[/TD]
[TD]InstrumentID
[/TD]
[TD]Buy/Sell
[/TD]
[TD]No
[/TD]
[TD]Price
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]3/11/2014
[/TD]
[TD]African Rainbow Minerals Ltd
[/TD]
[TD]2
[/TD]
[TD]Buy
[/TD]
[TD]15
[/TD]
[TD]21,150.00
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]6/4/2014
[/TD]
[TD]Advtech Ltd
[/TD]
[TD]1
[/TD]
[TD]Buy
[/TD]
[TD]200
[/TD]
[TD]890
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]7/14/2014
[/TD]
[TD]Advtech Ltd
[/TD]
[TD]1
[/TD]
[TD]Sell
[/TD]
[TD]163
[/TD]
[TD]802
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]7/15/2014
[/TD]
[TD]Afrimat Ltd
[/TD]
[TD]3
[/TD]
[TD]Buy
[/TD]
[TD]360
[/TD]
[TD]1,530.00
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]8/12/2014
[/TD]
[TD]Astral Foods Ltd
[/TD]
[TD]4
[/TD]
[TD]Buy
[/TD]
[TD]55
[/TD]
[TD]14,450.00
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]9/1/2014
[/TD]
[TD]Astral Foods Ltd
[/TD]
[TD]4
[/TD]
[TD]Buy
[/TD]
[TD]45
[/TD]
[TD]14,175.00
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]10/29/2014
[/TD]
[TD]Afrimat Ltd
[/TD]
[TD]3
[/TD]
[TD]Sell
[/TD]
[TD]360
[/TD]
[TD]1,630.00
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]11/13/2014
[/TD]
[TD]Astral Foods Ltd
[/TD]
[TD]4
[/TD]
[TD]Buy
[/TD]
[TD]100
[/TD]
[TD]15,500.00
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]4/23/2015
[/TD]
[TD]African Rainbow Minerals Ltd
[/TD]
[TD]2
[/TD]
[TD]Sell
[/TD]
[TD]15
[/TD]
[TD]20,504.00
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]6/18/2015
[/TD]
[TD]Advtech Ltd
[/TD]
[TD]1
[/TD]
[TD]Sell
[/TD]
[TD]37
[/TD]
[TD]870
[/TD]
[/TR]
</tbody>[/TABLE]

 

[ranman256]

Q1=
SELECT tTrades.[Buy/Sell], Count(tTrades.TransactionID) AS CountOfTransactionID, Sum(IIf([Buy/Sell]="Buy",-[Price],[Price])) AS Amt
FROM tTrades
WHERE (((tTrades.TransactionDate) Between #1/1/2014# And #6/1/2015#))
GROUP BY tTrades.[Buy/Sell];