Y
yogi_ajay
Guest
Hi All,
Looking for help from Access/SQL gurus!! My problem is as follows (sorry, too much of text but I just cannot find a better way to get it across..) :
1. I have a table containing day trading data with fields/data as follows:
Rec Date Price .. Shares_Sold
1 25 Aug 10 10
2 25 Aug 5 -5
3 26 Aug 20 10
4 28 Aug 15 -15
5 28 Aug 15 5
6. 29 Aug 10 -5
Now I want to basically come up with a table containing info about 'Squared-off' trades (that is number of outstanding shares equal to zero.. )
For example, from the above data, I want to come up with something like:
Lot_# From To #Shares Avg_Sell Avg_Buy
1 25Aug 28Aug 20 15 12.5
2 28Aug 29Aug 5 15 10
Basically, as soon as outstanding shares (sum of shares sold) becomes zero, it becomes a lot. Please note that -ve quantity in shares sold field indicates that shares were bought back!!
I am wondering if at all it is possible to come up with table II using query/queries... I think it should hopefully be possible somehow!! I dont wanna write macros/do programming (cause I dont know how to!!)
Help will be highly appreciated!!
Regards,
Ajay
Just to clarify the issue further...I know it is a bit complicated and I am not be the best of communicators.
Basically, we are dealing with the same stock (ticker). Idea is that of day trading in a particular stock. For eg. If I have 500 stocks of say Microsoft, I will sell (at high price) and buy back (at lower price) and make money on 'fluctuations' in a particular script in market. This ain't long term investment!!
Idea of a 'Lot' is that it is a group of transactions with which you buy back whatever you sold or vice versa (number of shares that you have after the lot is over is same as number of shares you have before the lot started!).
As far as average price is concerned, the idea is to come up with average selling and average buying price per share for a lot. For example Recs 1 through 4 consist of lot 1. Now within this, Recs 1 and 3 involve selling (10 shares at 10 and another 10 at 20, giving average selling price of 15 per share). Recs 2 and recs 4 imply a buy back (negative selling) of 5 shares at price of 5 and 15 shares at price of 15 giving an average buy back price of 12.5 per share (thus resulting in a profit of (15-12.5) per share * 20 shares = 30).
[This message has been edited by yogi_ajay (edited 08-31-2001).]
Looking for help from Access/SQL gurus!! My problem is as follows (sorry, too much of text but I just cannot find a better way to get it across..) :
1. I have a table containing day trading data with fields/data as follows:
Rec Date Price .. Shares_Sold
1 25 Aug 10 10
2 25 Aug 5 -5
3 26 Aug 20 10
4 28 Aug 15 -15
5 28 Aug 15 5
6. 29 Aug 10 -5
Now I want to basically come up with a table containing info about 'Squared-off' trades (that is number of outstanding shares equal to zero.. )
For example, from the above data, I want to come up with something like:
Lot_# From To #Shares Avg_Sell Avg_Buy
1 25Aug 28Aug 20 15 12.5
2 28Aug 29Aug 5 15 10
Basically, as soon as outstanding shares (sum of shares sold) becomes zero, it becomes a lot. Please note that -ve quantity in shares sold field indicates that shares were bought back!!
I am wondering if at all it is possible to come up with table II using query/queries... I think it should hopefully be possible somehow!! I dont wanna write macros/do programming (cause I dont know how to!!)
Help will be highly appreciated!!
Regards,
Ajay
Just to clarify the issue further...I know it is a bit complicated and I am not be the best of communicators.
Basically, we are dealing with the same stock (ticker). Idea is that of day trading in a particular stock. For eg. If I have 500 stocks of say Microsoft, I will sell (at high price) and buy back (at lower price) and make money on 'fluctuations' in a particular script in market. This ain't long term investment!!
Idea of a 'Lot' is that it is a group of transactions with which you buy back whatever you sold or vice versa (number of shares that you have after the lot is over is same as number of shares you have before the lot started!).
As far as average price is concerned, the idea is to come up with average selling and average buying price per share for a lot. For example Recs 1 through 4 consist of lot 1. Now within this, Recs 1 and 3 involve selling (10 shares at 10 and another 10 at 20, giving average selling price of 15 per share). Recs 2 and recs 4 imply a buy back (negative selling) of 5 shares at price of 5 and 15 shares at price of 15 giving an average buy back price of 12.5 per share (thus resulting in a profit of (15-12.5) per share * 20 shares = 30).
[This message has been edited by yogi_ajay (edited 08-31-2001).]
