I have a sheet of stock buys and sells, sorted by date, and I am trying to
track the average cost of my stock purchases, but I can't seem to get it to
work.

Here is an example:
stock date Transaction shares price commision total
avg cost/shr
ALS 4/02/07 BUY 1000 5.00 9.99 5009.99
5.01
ALS 4/30/07 SELL 500 6.00 9.99 3009.99
---
ALS 5/07/07 BUY 1000 4.00 9.99 4009.99
???

I have 2 buys and 1 sell. Since the sell changes the weight of the 4/02/07
purchase to 500 shares, then the weighted average of the 2 buys should come
to 4.35. My calculations keep coming up to an average of 4.50, which is
wrong, because I can't find a way to account for the drop of 500 shares on
the 4/30/07 transaction. I tried SUMPRODUCT, and it works fine as long as
there are only buy transactions. But the SELLS screw it up.

I want to be able to keep adding buys and sells to the sheet in any order.
I may buy 3 or 4 times in a row, then sell all at once or sell a bit at a
time. In each case, I want to know what my average buy cost is at the time.
Please help me write a formula for the average cost/share column.

thanks in advance,
Tim

I made a simplified example:

Beginning in Row1:

A -- number of shares
B -- +1 for a buy, -1 for a sell
C -- price
D -- average price

Cell D1=C1
D2:
=IF(B2=1,(D1*SUMPRODUCT(A$1:A1,B$1:B1)+C2*A2)/SUMPRODUCT(A$1:A2,B$1:B2),D1)

D3:
=IF(B3=1,(D2*SUMPRODUCT(A$1:A2,B$1:B2)+C3*A3)/SUMPRODUCT(A$1:A3,B$1:B3),D2)

and so on.

I hope this works

"Tim" wrote:

I have a sheet of stock buys and sells, sorted by date, and I am trying to
track the average cost of my stock purchases, but I can't seem to get it to
work.

Here is an example:
stock date Transaction shares price commision total
avg cost/shr
ALS 4/02/07 BUY 1000 5.00 9.99 5009.99
5.01
ALS 4/30/07 SELL 500 6.00 9.99 3009.99
---
ALS 5/07/07 BUY 1000 4.00 9.99 4009.99
???

I have 2 buys and 1 sell. Since the sell changes the weight of the 4/02/07
purchase to 500 shares, then the weighted average of the 2 buys should come
to 4.35. My calculations keep coming up to an average of 4.50, which is
wrong, because I can't find a way to account for the drop of 500 shares on
the 4/30/07 transaction. I tried SUMPRODUCT, and it works fine as long as
there are only buy transactions. But the SELLS screw it up.

I want to be able to keep adding buys and sells to the sheet in any order.
I may buy 3 or 4 times in a row, then sell all at once or sell a bit at a
time. In each case, I want to know what my average buy cost is at the time.
Please help me write a formula for the average cost/share column.

thanks in advance,
Tim

Art,
Thanks, it's a rudimentary start. Some key components are missing, such as
commissions, which have an effect on the average I'm trying to calculate. If
we include commissions into the formula, we probably need to include a total
(share*price)+commission.

I kinda of understand what you're doing. Is there a way to change the +1/-1
field to a character text "buy" and "sell"? Would that distort the
SUMPRODUCT formula? It just makes it alot easier to view with text fields.

thanks,
Tim

"Art" wrote:

I made a simplified example:

Beginning in Row1:

A -- number of shares
B -- +1 for a buy, -1 for a sell
C -- price
D -- average price

Cell D1=C1
D2:
=IF(B2=1,(D1*SUMPRODUCT(A$1:A1,B$1:B1)+C2*A2)/SUMPRODUCT(A$1:A2,B$1:B2),D1)

D3:
=IF(B3=1,(D2*SUMPRODUCT(A$1:A2,B$1:B2)+C3*A3)/SUMPRODUCT(A$1:A3,B$1:B3),D2)

and so on.

I hope this works

"Tim" wrote:

I have a sheet of stock buys and sells, sorted by date, and I am trying to
track the average cost of my stock purchases, but I can't seem to get it to
work.

Here is an example:
stock date Transaction shares price commision total
avg cost/shr
ALS 4/02/07 BUY 1000 5.00 9.99 5009.99
5.01
ALS 4/30/07 SELL 500 6.00 9.99 3009.99
---
ALS 5/07/07 BUY 1000 4.00 9.99 4009.99
???

I have 2 buys and 1 sell. Since the sell changes the weight of the 4/02/07
purchase to 500 shares, then the weighted average of the 2 buys should come
to 4.35. My calculations keep coming up to an average of 4.50, which is
wrong, because I can't find a way to account for the drop of 500 shares on
the 4/30/07 transaction. I tried SUMPRODUCT, and it works fine as long as
there are only buy transactions. But the SELLS screw it up.

I want to be able to keep adding buys and sells to the sheet in any order.
I may buy 3 or 4 times in a row, then sell all at once or sell a bit at a
time. In each case, I want to know what my average buy cost is at the time.
Please help me write a formula for the average cost/share column.

thanks in advance,
Tim

To use buy & sell instead of 1 and -1, try this:

D2:
=IF(B2="buy",(D1*SUMPRODUCT((A$1:A1)*(2*(B$1:B1="b uy")-1))+C2*A2)/SUMPRODUCT((A$1:A2)*(2*(B$1:B2="buy")-1)),D1)

D3:
=IF(B3="buy",(D2*SUMPRODUCT((A$1:A2)*(2*(B$1:B2="b uy")-1))+C3*A3)/SUMPRODUCT((A$1:A3)*(2*(B$1:B3="buy")-1)),D2)


"Tim" wrote:

Art,
Thanks, it's a rudimentary start. Some key components are missing, such as
commissions, which have an effect on the average I'm trying to calculate. If
we include commissions into the formula, we probably need to include a total
(share*price)+commission.

I kinda of understand what you're doing. Is there a way to change the +1/-1
field to a character text "buy" and "sell"? Would that distort the
SUMPRODUCT formula? It just makes it alot easier to view with text fields.

thanks,
Tim

"Art" wrote:

I made a simplified example:

Beginning in Row1:

A -- number of shares
B -- +1 for a buy, -1 for a sell
C -- price
D -- average price

Cell D1=C1
D2:
=IF(B2=1,(D1*SUMPRODUCT(A$1:A1,B$1:B1)+C2*A2)/SUMPRODUCT(A$1:A2,B$1:B2),D1)

D3:
=IF(B3=1,(D2*SUMPRODUCT(A$1:A2,B$1:B2)+C3*A3)/SUMPRODUCT(A$1:A3,B$1:B3),D2)

and so on.

I hope this works

"Tim" wrote:

I have a sheet of stock buys and sells, sorted by date, and I am trying to
track the average cost of my stock purchases, but I can't seem to get it to
work.

Here is an example:
stock date Transaction shares price commision total
avg cost/shr
ALS 4/02/07 BUY 1000 5.00 9.99 5009.99
5.01
ALS 4/30/07 SELL 500 6.00 9.99 3009.99
---
ALS 5/07/07 BUY 1000 4.00 9.99 4009.99
???

I have 2 buys and 1 sell. Since the sell changes the weight of the 4/02/07
purchase to 500 shares, then the weighted average of the 2 buys should come
to 4.35. My calculations keep coming up to an average of 4.50, which is
wrong, because I can't find a way to account for the drop of 500 shares on
the 4/30/07 transaction. I tried SUMPRODUCT, and it works fine as long as
there are only buy transactions. But the SELLS screw it up.

I want to be able to keep adding buys and sells to the sheet in any order.
I may buy 3 or 4 times in a row, then sell all at once or sell a bit at a
time. In each case, I want to know what my average buy cost is at the time.
Please help me write a formula for the average cost/share column.

thanks in advance,
Tim

Thanks Art, that worked for the buy & sell. Just the commission to add, and
I'll be totally happy.

Here's my stab at it - I added another column, so we now have
A -- number of shares
B -- buy or sell
C -- price
D -- commission
E -- average price

Then I added the commission column (D2 & D3) to the formula as follows:

E3:
=IF(B2="BUY",(E1*SUMPRODUCT((A$1:A1)*(2*(B$1:B1="B UY")-1))+C2*A2+D2)/SUMPRODUCT((A$1:A2)*(2*(B$1:B2="BUY")-1)),E1)

E4:
=IF(B3="BUY",(E2*SUMPRODUCT((A$1:A2)*(2*(B$1:B2="B UY")-1))+C3*A3+D3)/SUMPRODUCT((A$1:A3)*(2*(B$1:B3="BUY")-1)),E2)

Now, this appears to work for the "buy" transactions, but the "sell" doesn't
work since the ELSE part of the IF defaults to the previous row average. Is
there a way we can calculate the commision for the "sells" as part of the
ELSE?

thanks,
Tim

"Art" wrote:

To use buy & sell instead of 1 and -1, try this:

D2:
=IF(B2="buy",(D1*SUMPRODUCT((A$1:A1)*(2*(B$1:B1="b uy")-1))+C2*A2)/SUMPRODUCT((A$1:A2)*(2*(B$1:B2="buy")-1)),D1)

D3:
=IF(B3="buy",(D2*SUMPRODUCT((A$1:A2)*(2*(B$1:B2="b uy")-1))+C3*A3)/SUMPRODUCT((A$1:A3)*(2*(B$1:B3="buy")-1)),D2)


"Tim" wrote:

Art,
Thanks, it's a rudimentary start. Some key components are missing, such as
commissions, which have an effect on the average I'm trying to calculate. If
we include commissions into the formula, we probably need to include a total
(share*price)+commission.

I kinda of understand what you're doing. Is there a way to change the +1/-1
field to a character text "buy" and "sell"? Would that distort the
SUMPRODUCT formula? It just makes it alot easier to view with text fields.

thanks,
Tim

"Art" wrote:

I made a simplified example:

Beginning in Row1:

A -- number of shares
B -- +1 for a buy, -1 for a sell
C -- price
D -- average price

Cell D1=C1
D2:
=IF(B2=1,(D1*SUMPRODUCT(A$1:A1,B$1:B1)+C2*A2)/SUMPRODUCT(A$1:A2,B$1:B2),D1)

D3:
=IF(B3=1,(D2*SUMPRODUCT(A$1:A2,B$1:B2)+C3*A3)/SUMPRODUCT(A$1:A3,B$1:B3),D2)

and so on.

I hope this works

"Tim" wrote:

I have a sheet of stock buys and sells, sorted by date, and I am trying to
track the average cost of my stock purchases, but I can't seem to get it to
work.

Here is an example:
stock date Transaction shares price commision total
avg cost/shr
ALS 4/02/07 BUY 1000 5.00 9.99 5009.99
5.01
ALS 4/30/07 SELL 500 6.00 9.99 3009.99
---
ALS 5/07/07 BUY 1000 4.00 9.99 4009.99
???

I have 2 buys and 1 sell. Since the sell changes the weight of the 4/02/07
purchase to 500 shares, then the weighted average of the 2 buys should come
to 4.35. My calculations keep coming up to an average of 4.50, which is
wrong, because I can't find a way to account for the drop of 500 shares on
the 4/30/07 transaction. I tried SUMPRODUCT, and it works fine as long as
there are only buy transactions. But the SELLS screw it up.

I want to be able to keep adding buys and sells to the sheet in any order.
I may buy 3 or 4 times in a row, then sell all at once or sell a bit at a
time. In each case, I want to know what my average buy cost is at the time.
Please help me write a formula for the average cost/share column.

thanks in advance,
Tim

Tim,

Sorry I didn't have a chance to look at this until now. I'm trying to
remember how this worked -- as I recall I did not include any aspect of the
sell price in the average price. That is, a sell would sell at whatever the
average price was at that point. The sale price itself didn't affect
anything. Wouldn't this hold true for the commission on a sale as well?

"Tim" wrote:

Thanks Art, that worked for the buy & sell. Just the commission to add, and
I'll be totally happy.

Here's my stab at it - I added another column, so we now have
A -- number of shares
B -- buy or sell
C -- price
D -- commission
E -- average price

Then I added the commission column (D2 & D3) to the formula as follows:

E3:
=IF(B2="BUY",(E1*SUMPRODUCT((A$1:A1)*(2*(B$1:B1="B UY")-1))+C2*A2+D2)/SUMPRODUCT((A$1:A2)*(2*(B$1:B2="BUY")-1)),E1)

E4:
=IF(B3="BUY",(E2*SUMPRODUCT((A$1:A2)*(2*(B$1:B2="B UY")-1))+C3*A3+D3)/SUMPRODUCT((A$1:A3)*(2*(B$1:B3="BUY")-1)),E2)

Now, this appears to work for the "buy" transactions, but the "sell" doesn't
work since the ELSE part of the IF defaults to the previous row average. Is
there a way we can calculate the commision for the "sells" as part of the
ELSE?

thanks,
Tim

"Art" wrote:

To use buy & sell instead of 1 and -1, try this:

D2:
=IF(B2="buy",(D1*SUMPRODUCT((A$1:A1)*(2*(B$1:B1="b uy")-1))+C2*A2)/SUMPRODUCT((A$1:A2)*(2*(B$1:B2="buy")-1)),D1)

D3:
=IF(B3="buy",(D2*SUMPRODUCT((A$1:A2)*(2*(B$1:B2="b uy")-1))+C3*A3)/SUMPRODUCT((A$1:A3)*(2*(B$1:B3="buy")-1)),D2)


"Tim" wrote:

Art,
Thanks, it's a rudimentary start. Some key components are missing, such as
commissions, which have an effect on the average I'm trying to calculate. If
we include commissions into the formula, we probably need to include a total
(share*price)+commission.

I kinda of understand what you're doing. Is there a way to change the +1/-1
field to a character text "buy" and "sell"? Would that distort the
SUMPRODUCT formula? It just makes it alot easier to view with text fields.

thanks,
Tim

"Art" wrote:

I made a simplified example:

Beginning in Row1:

A -- number of shares
B -- +1 for a buy, -1 for a sell
C -- price
D -- average price

Cell D1=C1
D2:
=IF(B2=1,(D1*SUMPRODUCT(A$1:A1,B$1:B1)+C2*A2)/SUMPRODUCT(A$1:A2,B$1:B2),D1)

D3:
=IF(B3=1,(D2*SUMPRODUCT(A$1:A2,B$1:B2)+C3*A3)/SUMPRODUCT(A$1:A3,B$1:B3),D2)

and so on.

I hope this works

"Tim" wrote:

I have a sheet of stock buys and sells, sorted by date, and I am trying to
track the average cost of my stock purchases, but I can't seem to get it to
work.

Here is an example:
stock date Transaction shares price commision total
avg cost/shr
ALS 4/02/07 BUY 1000 5.00 9.99 5009.99
5.01
ALS 4/30/07 SELL 500 6.00 9.99 3009.99
---
ALS 5/07/07 BUY 1000 4.00 9.99 4009.99
???

I have 2 buys and 1 sell. Since the sell changes the weight of the 4/02/07
purchase to 500 shares, then the weighted average of the 2 buys should come
to 4.35. My calculations keep coming up to an average of 4.50, which is
wrong, because I can't find a way to account for the drop of 500 shares on
the 4/30/07 transaction. I tried SUMPRODUCT, and it works fine as long as
there are only buy transactions. But the SELLS screw it up.

I want to be able to keep adding buys and sells to the sheet in any order.
I may buy 3 or 4 times in a row, then sell all at once or sell a bit at a
time. In each case, I want to know what my average buy cost is at the time.
Please help me write a formula for the average cost/share column.

thanks in advance,
Tim