Google Sheet Function updated for Excel

[dmcmaste]

I am trying to replicated this Google Sheet in Excel. I can't get the array/filter functionality in Column I, L working correctly in Excel. Can anyone please help replicate/troubleshoot.

Portfolio - Troubleshooting

TRANS⇝Rollover IRA Date,Type,Stock,Transacted Units,Transacted Price (per unit),Avg. Price,Fees,Stock Split Ratio,Previous Units,Cumulative Units,Transacted Value,Previous Cost,Cost of Transaction,Cost of Transaction (per unit),Cumulative Cost,Gains/Losses from Sale,Realised Gains/Losses %,Cumul...

docs.google.com

For Column I - if it is the first time buying the stock previous units should be 0 otherwise added Transacted Units (Column D) to the previously bought Cumulative Unites (Column J).
Fol Column L - If it is the first time buying the stock it should be 0 otherwise it should be the previous Cumulative Cost (Column O) and added to the new transacted value (Column K)


Thanks!

 

[dmcmaste]

Would you be able to manually calculate the results for your example then enter them into the sheet so that I have something to aim at?
To someone who deals with stock investment all day is is probably quite obvious, to me not so much.

I put something simple together to show you some numbers but it would need to look up the STOCK like how we did in I2/J2 above.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Date	Type	Stock	Transacted Units	Transacted Price (per unit)	Avg. Price	Fees	Stock Split Ratio	Previous Units	Cumulative Units	Transacted Value	Previous Cost	Cost of Transaction	Cost of Transaction (per unit)	Cumulative Cost	Gains/Losses from Sale	Realised Gains/Losses %	Cumulative Cost by Ticker	Gains/Losses from Sale by Ticker	Realised Gains/Losses % by Ticker
2	12/10/2018	Deposit	CASH	1	159,570.56	-	0	1							Error	0			0.00	#DIV/0!
3	12/31/2018	Interest	CASH	1	66.84	-	0	1							Error	0			0.00	#DIV/0!
4	1/15/2019	Buy	AXP	50	90	90	0	1	0	50	4500	0	-	-	4500	0			0.00	#DIV/0!
5	1/16/2019	Buy	AAPL	35	150	150	0	1	0	35	5250	0	-	-	5250	0			0.00	#DIV/0!
6	1/17/2019	Buy	AXP	100	30	50	0	1	50	150	3000	4500	-	-	7500	0			0.00	#DIV/0!
7	2/14/2019	Sell	AAPL	35	170	-	0	1	35	0	5950	5250	5250	150	0	700	13%	5250.00	700.00	13%
8	2/19/2019	Buy	AAPL	10	100	100	0	1	0	10	1000	0	-	-	1000	0			0.00	#DIV/0!
9	2/20/2019	Buy	AAPL	20	90	93.33	0	1	10	30	1800	1000	-	-	2800	0			0.00	#DIV/0!
10	3/2/2020	Sell	AAPL	30	230	-	0	1	30	0	6900	2800	2800	93.33333333	0	4100	146%	8050	4800.00	60%
Sheet2

Cell Formulas
Range	Formula
I2:I10	I2	=IF((AND(B2<>"Deposit", B2<>"Interest", B2<>"DIV")), IFERROR(LOOKUP(2,1/(C$1:C1=C2),J$1:J1),0),"")
J2:J10	J2	=IF((AND(B2<>"Deposit", B2<>"Interest", B2<>"DIV")),IF(B2="Buy",I2+D2,IF(B2="Sell",I2-D2,IF(OR(B2="Div",B2="Fee"),I2,IF(B2="Split",I2*H2,0)))),"")
K2:K10	K2	=IF((AND(B2<>"Deposit", B2<>"Interest", B2<>"DIV")), IF(B2="Buy",E2*D2+G2,IF(B2="Sell",E2*D2-G2,E2*D2-G2)), "")
L2:L10	L2	=IF((AND(B2<>"Deposit", B2<>"Interest", B2<>"DIV")),IFERROR(LOOKUP(2,1/(C$1:C1=C2),O$1:O1),0),"")
M2:M10	M2	=IF((AND(B2<>"Deposit", B2<>"Interest", B2<>"DIV")),IF(B2="Sell",IF(I2=0,0,D2/I2*L2),"-"),"")
N2:N10	N2	=IF((AND(B2<>"Deposit", B2<>"Interest", B2<>"DIV")),IF(B2="Sell",IF(I2=0,0,L2/I2),"-"),"")
O2:O10	O2	=IF(B2="Buy",L2+K2,IF(OR(B2="Div",B2="Fee"),L2,IF(B2="Sell",IF(L2<=0,"Error.No Previous units.",L2-M2),IF(B2="Split",L2,"Error"))))
P2:P10	P2	=IF(B2="Sell",K2-M2,IF(OR(B2="Div",B2="Fee"),K2,0))
Q2:Q10	Q2	=IF(B2="Sell",(K2-M2)/M2,"")
S2:S9	S2	=P2
T2:T10	T2	=S2/R2
R7	R7	=M7
R10	R10	=M10+M7
S10	S10	=P10+P7

 

[jasonb75]

That's all I needed, it was just to clarify what was needed rather than me making a number of wrong guesses, think this should be somewhere close.

R2 =IF(B2="Sell",SUMIFS(M$2:M2,B$2:B2,"Sell",C$2:C2,C2),"")
S2 =IF(B2="Sell",SUMIFS(P$2:P2,B$2:B2,"Sell",C$2:C2,C2),"")
T2 =IF(S2="","",IFERROR(S2/R2,0))

 

[dmcmaste]

That's all I needed, it was just to clarify what was needed rather than me making a number of wrong guesses, think this should be somewhere close.

R2 =IF(B2="Sell",SUMIFS(M$2:M2,B$2:B2,"Sell",C$2:C2,C2),"")
S2 =IF(B2="Sell",SUMIFS(P$2:P2,B$2:B2,"Sell",C$2:C2,C2),"")
T2 =IF(S2="","",IFERROR(S2/R2,0))

This looks like it is working. I will check it out. Thanks for all the help!

 

[dmcmaste]

Hi I am trying to fill in column G2. Basically I want to look up Ticker BA (Cell B2) and reference column K & L. It should look up BA in column L and look at all Buys (Column K) and return max Cumulative Cost (Co9umn X).

In this example BA should return $7,17.00 (Cell X18)

Robinhood Portfolio.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	COMPANY	TICKER	LAST	CHANGE	CHANGE %	POSITION	COST BASIS	MARKET VALUE		Date	Type	Stock	Transacted Units	Transacted Price (per unit)	Avg. Price	Fees	Stock Split Ratio	Previous Units	Cumulative Units	Transacted Value	Previous Cost	Cost of Transaction	Cost of Transaction (per unit)	Cumulative Cost
2	Boeing Co (XNYS:BA)	BA	#####	6.47	4.36	20.000		3094.62		#######	Buy	AAPL	35.000	154.70	154.70	0.00	1.00	0	35.00	5,414.50	0	-	-	5,414.50
3	Blackstone Group Inc (XNYS:BX)	BX	#####	1.55	3.33	102.682		4939.00		#######	Buy	VGR	500.000	10.80	10.80	0.00	1.00	0	500.00	5,400.00	0	-	-	5,400.00
4	Canopy Growth Corp (XNYS:CGC)	CGC	#####	0.53	3.69	400.000		5952.00		#######	Buy	AAPL	0.150	170.20	154.77	0.00	1.00	35	35.15	25.53	5414.5	-	-	5,440.03
5	Delta Air Lines Inc (XNYS:DAL)	DAL	#####	1.65	7.37	100.000		2396.50		#######	Buy	BA	20.000	380.85	380.85	0.00	1.00	0	20.00	7,617.00	0	-	-	7,617.00
6	First Trust Dow Jones Internet Index Fund (ARCX:FDN)	FDN	#####	2.53	1.99	25.000		3238.75		#######	Div	VGR	500.000	0.40	-	0.00	1.00	500	500.00	200.00	5400			5,400.00
7	First Trust US Equity Opportunities ETF (ARCX:FPX)	FPX	#####	1.18	1.84	50.000		3265.05		#######	Buy	AAPL	0.144	187.83	154.90	0.00	1.00	35.15	35.29	27.05	5440.03	-	-	5,467.08
8	First Trust International Equity Opportunities ETF (XNAS:FPXI)	FPXI	#####	0.32	0.86	100.000		3734.00		#######	Div	BA	1.000	41.10	-	0.00	1.00	20	20.00	41.10	7617			7,617.00
9	General Electric Co (XNYS:GE)	GE	$ 7.34	0.12	1.66	900.000		6606.00		#######	Div	VGR	1.000	200.00	-	0.00	1.00	500	500.00	200.00	5400			5,400.00
10	iShares Russell 2000 ETF (ARCX:IWM)	IWM	#####	4.00	3.55	25.000		2915.25		#######	Buy	AAPL	0.130	208.34	155.10	0.00	1.00	35.294	35.42	27.08	5467.08	-	-	5,494.16
11	Southwest Airlines Co (XNYS:LUV)	LUV	#####	3.67	11.93	50.000		1719.25		#######	Div	BA	1.000	41.10	-	0.00	1.00	20	20.00	41.10	7617			7,617.00
12	Legg Mason Low Volatility High Dividend ETF (XNAS:LVHD)	LVHD	#####	0.85	3.26	150.000		4039.59		#######	Div	VGR	1.000	25.00	-	0.00	1.00	500	500.00	25.00	5400			5,400.00
13										#######	Div	VGR	1.000	200.00	-	0.00	1.00	500	500.00	200.00	5400			5,400.00
14										#######	Buy	AAPL	0.104	261.90	155.41	0.00	1.00	35.424	35.53	27.24	5494.16	-	-	5,521.40
15										#######	Div	BA	1.000	41.10	-	0.00	1.00	20	20.00	41.10	7617			7,617.00
16										#######	Div	VGR	1.000	210.00	-	0.00	1.00	500	500.00	210.00	5400			5,400.00
17										#######	Buy	AAPL	0.085	322.12	155.81	0.00	1.00	35.528	35.61	27.38	5521.4	-	-	5,548.78
18										#######	Div	BA	1.000	41.10	-	0.00	1.00	20	20.00	41.10	7617			7,617.00
19										#######	Sell	AAPL	35.000	254.00	-	0.00	1.00	35.613	0.61	8,890.00	5548.78	5,453.27	155.81	95.51
20										#######	Div	VGR	1.000	105.00	-	0.00	1.00	500	500.00	105.00	5400			5,400.00
21										#######	Sell	AAPL	0.613	254.00	-	0.00	1.00	0.613	0.00	155.70	95.5101	95.51	155.81	0.00
Sheet2

Cell Formulas
Range	Formula
B2:B12	B2	=A2.[Ticker symbol]
C2:C12	C2	=A2.Price
D2:D12	D2	=A2.Change
E2:E12	E2	=A2.[Change (%)]*100
F2:F12	F2	=MAX(IF('TRANS⇝Rollover IRA'!$C$2:$C$1000=$B2,'TRANS⇝Rollover IRA'!$J$2:$J$1000))
H2:H12	H2	=F2*C2
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[dmcmaste]

Would you be able to manually calculate the results for your example then enter them into the sheet so that I have something to aim at?
To someone who deals with stock investment all day is is probably quite obvious, to me not so much.

Apologies. I referenced the cells of the values and made the example easier to read.

Robinhood Portfolio.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	TICKER	LAST	CHANGE	CHANGE %	POSITION	COST BASIS	MARKET VALUE		Date	Type	Stock	Transacted Units	Transacted Price (per unit)	Avg. Price	Fees	Stock Split Ratio	Previous Units	Cumulative Units	Transacted Value	Previous Cost	Cost of Transaction	Cost of Transaction (per unit)	Cumulative Cost
2	BA	$ 142.68	-5.58	-3.77	20.000	7617.00	2853.50		2019-01-16	Buy	AAPL	35.000	154.70	154.70	0.00	1.00	0	35.00	5,414.50	0	-	-	5,414.50
3	VGR	$ 9.90	-0.51	-1.10	500.000	5400.00	4950.00		2019-02-11	Buy	VGR	500.000	10.80	10.80	0.00	1.00	0	500.00	5,400.00	0	-	-	5,400.00
4									2019-02-14	Buy	AAPL	0.150	170.20	154.77	0.00	1.00	35	35.15	25.53	5414.5	-	-	5,440.03
5									2019-03-15	Buy	BA	20.000	380.85	380.85	0.00	1.00	0	20.00	7,617.00	0	-	-	7,617.00
6									2019-03-28	Div	VGR	500.000	0.40	-	0.00	1.00	500	500.00	200.00	5400			5,400.00
7									2019-05-16	Buy	AAPL	0.144	187.83	154.90	0.00	1.00	35.15	35.29	27.05	5440.0306	-	-	5,467.08
8									2019-06-07	Div	BA	1.000	41.10	-	0.00	1.00	20	20.00	41.10	7617			7,617.00
9									2019-06-27	Div	VGR	1.000	200.00	-	0.00	1.00	500	500.00	200.00	5400			5,400.00
10									2019-08-15	Buy	AAPL	0.130	208.34	155.10	0.00	1.00	35.294	35.42	27.08	5467.0779	-	-	5,494.16
11									2019-09-06	Div	BA	1.000	41.10	-	0.00	1.00	20	20.00	41.10	7617			7,617.00
12									2019-09-27	Div	VGR	1.000	25.00	-	0.00	1.00	500	500.00	25.00	5400			5,400.00
13									2019-09-27	Div	VGR	1.000	200.00	-	0.00	1.00	500	500.00	200.00	5400			5,400.00
14									2019-11-14	Buy	AAPL	0.104	261.90	155.41	0.00	1.00	35.424	35.53	27.24	5494.1624	-	-	5,521.40
15									2019-12-06	Div	BA	1.000	41.10	-	0.00	1.00	20	20.00	41.10	7617			7,617.00
16									2019-12-30	Div	VGR	1.000	210.00	-	0.00	1.00	500	500.00	210.00	5400			5,400.00
17									2020-02-13	Buy	AAPL	0.085	322.12	155.81	0.00	1.00	35.528	35.61	27.38	5521.4001	-	-	5,548.78
18									2020-03-06	Div	BA	1.000	41.10	-	0.00	1.00	20	20.00	41.10	7617			7,617.00
19									2020-03-30	Sell	AAPL	35.000	254.00	-	0.00	1.00	35.613	0.61	8,890.00	5548.7806	5,453.27	155.81	95.51
20									2020-03-30	Div	VGR	1.000	105.00	-	0.00	1.00	500	500.00	105.00	5400			5,400.00
21									2020-04-01	Sell	AAPL	0.613	254.00	-	0.00	1.00	0.613	0.00	155.70	95.510137	95.51	155.81	0.00
Sheet2

Cell Formulas
Range	Formula
B2	B2	=A2.[Ticker symbol]
C2	C2	=A2.Price
D2:D3	D2	=A2.Change
E2:E3	E2	=A2.[Change (%)]*100
F2:F3	F2	=MAX(IF('TRANS⇝Rollover IRA'!$C$2:$C$1000=$B2,'TRANS⇝Rollover IRA'!$J$2:$J$1000))
G2	G2	=X18
H2:H3	H2	=F2*C2
G3	G3	=X20
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[jasonb75]

For office 365 =MAXIFS(X:X,L:L,B2)

Not sure where column K and buy come into the equation as X18 doesn't meet that requirement, if it should be X5 then =MAXIFS(X:X,L:L,B2,K:K,"Sell")

 

[dmcmaste]

For office 365 =MAXIFS(X:X,L:L,B2)

Not sure where column K and buy come into the equation as X18 doesn't meet that requirement, if it should be X5 then =MAXIFS(X:X,L:L,B2,K:K,"Sell")

Really Appreciate your help!

 

[jasonb75]

You're welcome

Just noticed your other reply above as well, think you responded to an old post from last week (which you replied to in post 11)

Apologies. I referenced the cells of the values and made the example easier to read.

the same thing has caught me out a few times, when you click the thread link from your list of watched threads, it only takes you to the end if there are new replies, otherwise it just takes you to the first page.

 

[dmcmaste]

Hi have a new question around the same Sheet.

I am looking to get the latest open (Buy) position (Cell T5) in a Stock and latest cost basis (Cell U5) in my sheet. So for this example below, I want to display AAPL = 25 units not 75 which is what the function in cell T5 is in and Cell U5 should be 1,250. Currently it is just getting the Max value in the range not the latest open Buy position. Thanks for the help!

My Portfolio (4-9-20).xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Date	Type	Stock	Transacted Units	Transacted Price (per unit)	Avg. Price	Fees	Stock Split Ratio	Previous Units	Cumulative Units	Transacted Value	Previous Cost	Cost of Transaction	Cost of Transaction (per unit)	Cumulative Cost		Gains/Losses from Sale	Realised Gains/Losses %
2	2020-01-01	Deposit	TR 401k Rollover	1.000	50,000.00	-	0.00	1.00							Error		0.00
3	2020-04-01	Buy	AAPL	75.000	100.00	100.00	0.00	1.00	0	75.00	7,500.00	0	-	-	7,500.00		0.00
4	2020-04-07	Buy	WMT	10.000	25.00	25.00	0.00	1.00	0	10.00	250.00	0	-	-	250.00		0.00		Ticker	MAX Position	Cost Basis
5	2020-04-10	Sell	AAPL	75.000	125.00	-	0.00	1.00	75	0.00	9,375.00	7500	7,500.00	100.00	0.00		1875.00	25.00%	AAPL	75	7500
6	2020-05-01	Buy	BBY	25.000	10.00	10.00	0.00	1.00	0	25.00	250.00	0	-	-	250.00		0.00
7	2020-05-05	Buy	AAPL	25.000	50.00	50.00	0.00	1.00	0	25.00	1,250.00	0	-	-	1,250.00		0.00
Trans.Roll (2)

Cell Formulas
Range	Formula
I2:I7	I2	=IF((AND(B2<>"Deposit", B2<>"Interest")), IFERROR(LOOKUP(2,1/(C$1:C1=C2),J$1:J1),0),"")
J2:J7	J2	=IF(B2<>"Deposit",IF(B2="Buy",I2+D2,IF(B2="Sell",I2-D2,IF(OR(B2="Div",B2="Fee"),I2,IF(B2="Split",I2*H2,0)))),"")
K2:K7	K2	=IF((AND(B2<>"Deposit", B2<>"Interest")), IF(B2="Buy",E2*D2+G2,IF(B2="Sell",E2*D2-G2,E2*D2-G2)), "")
L2:L7	L2	=IF((AND(B2<>"Deposit", B2<>"Interest")), IFERROR(LOOKUP(2,1/(C$1:C1=C2),O$1:O1),0),"")
M2:M7	M2	=IF((AND(B2<>"Deposit", B2<>"Interest", B2<>"DIV")),IF(B2="Sell",IF(I2=0,0,D2/I2*L2),"-"),"")
N2:N7	N2	=IF((AND(B2<>"Deposit", B2<>"Interest", B2<>"DIV")),IF(B2="Sell",IF(I2=0,0,L2/I2),"-"),"")
O2:O7	O2	=IF(B2="Buy",L2+K2,IF(OR(B2="Div",B2="Fee"),L2,IF(B2="Sell",IF(L2<=0,"Error.No Previous units.",L2-M2),IF(B2="Split",L2,"Error"))))
Q2:Q7	Q2	=IF(B2="Sell",K2-M2,IF(OR(B2="Div",B2="Fee"),K2,0))
R2:R7	R2	=IF(B2="Sell",(K2-M2)/M2,"")
T5	T5	=MAX(IF($C$2:$C$1000=$S5,$J$2:$J$1000))
U5	U5	=MAXIFS($O$2:$O$1001,$C$2:$C$1001,S5)
F2:F7	F2	=IF(B2="BUY", O2/J2, "-")
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
R1:R7,R41:R1048576	Cell Value	<0	text	NO
B:B	Cell Value	contains "Buy"	text	NO
B:B	Cell Value	contains "Sell"	text	NO

 

[jasonb75]

Try

=IFERROR(LOOKUP(2,1/($C$2:$C$1000=$S5)/($B$2:$B$1000="buy"),$J$2:$J$1000),"")

=IFERROR(LOOKUP(2,1/($C$2:$C$1000=$S5)/($B$2:$B$1000="buy"),$O$2:$O$1000),"")