Shares Trading

[sajad570]

Hi,
I need to manipulate some data related to shares trading.
info i have
1. Opening Stock
2. daily buying and selling report

What i need is
1. to be able to view the profit all transaction by selecting a date range.
2. maintain a stock in hand report as of each day.



Sheet 1

Stock Report
As of 31-Dec-2013
Company	Shares	Avg Cost	Amount
Company 1	30,000.00	28.38	851,370.19
Company 2	10,000.00	30.16	301,631.15

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

Sheet 2
Daily Trading Report

01-Jan-2014				Opening Balance					1,853,637.79	1,853,637.79
07-Jan-2014	Buy	4003	United Electronics Company	(4,000)	96.500	167.91	Ref:	(386,167.91)	-	1,467,469.88
07-Jan-2014	Sell	1211	Saudi Arabian Mining Co.	30,000	33.304	-434.71	Ref:	-	998,680.19	2,466,150.07
07-Jan-2014	Buy	2050	Savola Group	(1,202)	62.750	32.81	Ref:	(75,458.31)	-	2,390,691.76
09-Jan-2014	Buy	1010	Riyad Bank	(30,000)	30.700	400.64	Ref:	(921,400.64)	-	1,469,291.12
09-Jan-2014	Sell	1040	Saudi Hollandi Bank	10,000	42.000	-182.70	Ref:	-	419,817.30	1,889,108.42

<colgroup><col><col><col><col><col><col><col><col span="3"><col><col></colgroup><tbody>
</tbody>

Sheet 3

Portfolio Activity
From	1-Jan-2014.
To	12-Jan-14
Date	Description	Sales		Purchase / Cost of Sales		GP	GP%
Qty	@	SAR		Qty	@	SAR
07-Jan-2014	Company 1	30,000	33.29	998,680.19		30,000	28.38	851,370		147,310.00	14.75%
09-Jan-2014	Company 2	10,000	41.98	419,817.30		10,000	30.16	301,631		118,186.15	28.15%
	Total	40,000		1,418,497.49		40,000		1,153,001		265,496	18.72%
07-Jan-2014	United Electronics Company					(4,000)	96.54	(386,167.91)
07-Jan-2014	Savola Group					(1,202)	62.78	(75,458.31)
09-Jan-2014	Riyad Bank					(30,000)	30.71	(921,400.64)
	Total					(35,202)		(1,383,026.86)

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>