Stock Cost Average

[rizmomin]

Please find attached sheet...


I need to know the cost basis of the shares i bought and hold.
i can computarize when same stock is bought and sold only 1 time.
If particluar stock is bought, sold and bought again..i need help how to get
the cost basis.

Brokerage Account sheet have all transactions downloaded from Quicken 2008.
There are entries when i bought shares and sold them and bought shares again.
Example is General Electric :

10/2/2008 Bought 1000 Shares GE $22322.95
10/3/2008 Bought 1000 Shares GE $22442.95
10/8/2008 SOLD 2000 Shares GE $ -954.10..This is _RlzdGain ,...loss of $-954.10

10/30/2008 Bought 1000 Shares GE $ 19151.95

$19151.95 should appear in Sheet1!B14.
Currently it is showing $63151.95 which is sum of 22322.95+22442.95+19151.95


Please help me accomplish this task..and let me know if you have any questions.


Thank you for your help.

Riz Momin

[Kieran]

Riz momin,

The formula in sheet1 c14 should be =SUMPRODUCT(--('Brokerage Account'!$D$1:$D$196=Sheet1!C$2),(--('Brokerage Account'!$F$1:$F$196=Sheet1!$A14)),('Brokerage Account'!$J$1:$J$196)).
[Changed sum column to J from K]

This will return the total value of the sale, not the realised gain.

You will then need to add another column ot your table to show the net value of purchases - that is B14 -C14.

Does this do what you want.

[rizmomin]

i had manually entered teh formula in Brokerage!J11...

Need a way to know that 2000 shares been bought and sold and then some how add the purchase price + Gain in column J...

Please help

Riz

[sglife]

I think this is what you need

Please Login or Register  to view this content.

*Press Ctrl + Shift + Enter.

[rizmomin]

Thank you so much for helping..i need you will be able to resolvemy issue.

This is giving wrong result.
Let me explain you.

What i need is the current cost basis of the stock i bought.
In the brokerage account sheet you will the transcations.

10/2/2008 Bought 1000 Shares GE at $ 22322.95
10/3/2008 Bought 1000 Shares GE at $ 22442.95
10/8/2008 SOLD 2000 Shares GE with -$954.10 realized gain.


Since total # of shares bought = Total # of shares sold then we would need
to go and find if any more GE stocks bouught.
If so
add total shares bought and enter that number in Column D (for GE at D14).
add total cost of shares and enter that number in Column B (for GE at B14).

You can see that on
10/30/2008 Bought 1000 Shares GE at $ 19151.95 (Row 34)

Did not sell these stocks ,
hence i need on sheet1 B14=19151.95 and D14=1000.

Once i have this, i will reaarange my data the way i need...

Please help accomplish this..


Thank you and please let me know if you have any further questions.


Riz Momin