Average share (opening) price

[JackDanIce]

Hi,

I have the following data, where a negative number represents a sell of shares and a positive number a purchase and column C shows at what price the transaction happened.

	A	B	C	D	E	F
Ticker	Quantity	Price		Ticker	Average Price
TSCO LN Equity		VOD LN Equity	?
TSCO LN Equity		TSCO LN Equity	?
TSCO LN Equity
TSCO LN Equity
TSCO LN Equity
TSCO LN Equity
VOD LN Equity
TSCO LN Equity
TSCO LN Equity
VOD LN Equity
VOD LN Equity
VOD LN Equity
VOD LN Equity
VOD LN Equity

<COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 104px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 104px"><COL style="WIDTH: 98px"></COLGROUP><TBODY>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]50 [/TD]
[TD="align: right"]323[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]-30 [/TD]
[TD="align: right"]330[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]60 [/TD]
[TD="align: right"]300[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: right"]300 [/TD]
[TD="align: right"]325[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: right"]-5 [/TD]
[TD="align: right"]360[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="align: right"]200 [/TD]
[TD="align: right"]310[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="align: right"]-50 [/TD]
[TD="align: right"]190[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="align: right"]10 [/TD]
[TD="align: right"]350[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="align: right"]-40 [/TD]
[TD="align: right"]320[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]

[TD="align: right"]25 [/TD]
[TD="align: right"]150[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]

[TD="align: right"]25 [/TD]
[TD="align: right"]155[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]

[TD="align: right"]-150 [/TD]
[TD="align: right"]180[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]

[TD="align: right"]-200 [/TD]
[TD="align: right"]170[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]

[TD="align: right"]40 [/TD]
[TD="align: right"]130[/TD]

</TBODY>

And I've been asked to calculate the average price per ticker. For now it doesn't matter whether it's based on a first in first out principle or first in last out principle.

Has anyone got a formula to calculate this?

Thanks in advance,
Jack

 

[drsarao]

Wouldn't SUMIF()/COUNTIF() (Or AVERAGEIF() IN Excel 2007+) work?

Is there a catch in BUY/SELL averaging?

 

[JackDanIce]

Not when I tried (hence asking on here!). No catches, just the data as it is

 

[drsarao]

Does this work for you?

Excel Workbook
	A	B	C	D	E	F
1	Ticker	Quantity	Price		Ticker	Average Price
2	TSCO LN Equity	50	323		VOD LN Equity	162.5
3	TSCO LN Equity	-30	330		TSCO LN Equity	327.25
4	TSCO LN Equity	60	300
5	TSCO LN Equity	300	325
6	TSCO LN Equity	-5	360
7	TSCO LN Equity	200	310
8	VOD LN Equity	-50	190
9	TSCO LN Equity	10	350
10	TSCO LN Equity	-40	320
11	VOD LN Equity	25	150
12	VOD LN Equity	25	155
13	VOD LN Equity	-150	180
14	VOD LN Equity	-200	170
15	VOD LN Equity	40	130
Sheet2

Excel 2003

Cell Formulas
Range	Formula
F2	=SUMIF(A:A,E2,C:C)/COUNTIF(A:A,E2)
F3	=SUMIF(A:A,E3,C:C)/COUNTIF(A:A,E3)

 

[JackDanIce]

I think it does! I'll test it out and don't know why it wasn't working for me, but thank you very much

 

[JackDanIce]

No it doesn't, remember why now. That formula doesn't take into account the different weightings due to the different quantities bought and sold at different prices. Your formula averages the price based on the number of trades only and doesn't include the quantities. Thank you for trying and replying though

 

[drsarao]

Well, that was the catch!
Can you define "weighting" in this particular scenario?
If you can calculate the weighted average by hand, then it usually can be implemented in Excel.
We just need to know / decide on a logic.
Give an example of how you'd do it by hand.

 

[JackDanIce]

For security x
Example 1: You buy 10 shares at price 10.00, another 10 at price 20.00, you now hold 20 shares at an average price of 15.00
Example 2: You buy 20 shares at price 10.00, another 10 at price 20.00, you now hold 30 shares at an average price of 13.33

These are simple examples and don't take into account if you sell some and then subsequently buy some more, e.g.

Example 3: You buy 20 shares at price 10.00, another 10 at price 20.00, sell 5 at price 18.00, you now hold 25 shares at an average price of:
(i) 13.33, based on the average price prior to the sell
(ii) 14.00, based on a first-in first-out principle (i.e. your original purchase of 20 shares has now been reduced to 15 and you are taking the post sale average price)
(iii) 12.00, based on last-in first-out principle (i.e. the last most recent purchase of 10 shares has now been reduced to 5 and you are taking the post sale average price)

For now, I don't care which of the 3 prices the formula calculates

 

[JackDanIce]

Actually if I can get a formula that calculates all 3 average prices, that would be even better...

 

[drsarao]

I am not very good with finance! So I don't really get FIFO/LIFO effect.
How far does this take you?

Excel Workbook
	A	B	C	D	E	F	G
1	Ticker	Quantity	Price	QuantityAbs	Cost	Ticker	Average Price
2	TSCO LN Equity	50	323	50	16150	VOD LN Equity	170.05
3	TSCO LN Equity	-30	330	30	9900	TSCO LN Equity	318.92
4	TSCO LN Equity	60	300	60	18000
5	TSCO LN Equity	300	325	300	97500
6	TSCO LN Equity	-5	360	5	1800
7	TSCO LN Equity	200	310	200	62000
8	VOD LN Equity	-50	190	50	9500
9	TSCO LN Equity	10	350	10	3500
10	TSCO LN Equity	-40	320	40	12800
11	VOD LN Equity	25	150	25	3750
12	VOD LN Equity	25	155	25	3875
13	VOD LN Equity	-150	180	150	27000
14	VOD LN Equity	-200	170	200	34000
15	VOD LN Equity	40	130	40	5200
Sheet6

Excel 2003

Cell Formulas
Range	Formula
D2	=ABS(B2)
E2	=IF(B2<0,-B2*C2,B2*C2)
G2	=SUMIF(A:A,F2,E:E)/SUMIF(A:A,F2,D:D)