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!

 

[jasonb75]

Try removing 'arrayformula' and the corresponding parenthesis from the formula, then array confirm it in excel with Ctrl Shift Enter.

 

[dmcmaste]

Try removing 'arrayformula' and the corresponding parenthesis from the formula, then array confirm it in excel with Ctrl Shift Enter.

Thanks for your quick reply. I did that and then I got filter highlight and the error: "That function isn't valid" popped up. I think I need to replace that part since INDEX() doesn't like Filter(). Any further suggestions?

 

[jasonb75]

If you're seeing that then it means that the FILTER function isn't currently supported in your version of excel.

It appears that not all users of office 365 have received the update with the new functions, I'll have look at an alternative method.

 

[MARK858]

It appears that not all users of office 365 have received the update with the new functions

Those on the semi-annual channel haven't received them yet.

 

[jasonb75]

It took me a while to figure out what the formula was doing, think this should do what you need.

In I2 and fill down, =IF(B2<>"Deposit", IFERROR(LOOKUP(2,1/(C$1:C1=C2),J$1:J1),0),"")
In L2 and fill down, =IF(B2<>"Deposit", IFERROR(LOOKUP(2,1/(C$1:C1=C2),O$1:O1),0),"")

 

[dmcmaste]

It took me a while to figure out what the formula was doing, think this should do what you need.

In I2 and fill down, =IF(B2<>"Deposit", IFERROR(LOOKUP(2,1/(C$1:C1=C2),J$1:J1),0),"")
In L2 and fill down, =IF(B2<>"Deposit", IFERROR(LOOKUP(2,1/(C$1:C1=C2),O$1:O1),0),"")

Thanks you so much! Appreciate the help. I think we are in business.

 

[dmcmaste]

Those on the semi-annual channel haven't received them yet.

Thanks for the additional information!

 

[dmcmaste]

Thanks you so much! Appreciate the help. I think we are in business.

I have one more request. Could you help me add R2, S2 and T2 formulas? Basically I want to do what I'm doing in O2, P2, Q2 to get cumulative Gain/Loss grouped by company instead of since buy and sell period. In this example I want to see AAPL's overall gains/loss between the 2 sell periods.

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
3	12/31/2018	Interest	CASH	1	66.84	-	0	1							Error	0
4	1/15/2019	Buy	AXP	50	90	90	0	1	0	50	4500	0	-	-	4500	0
5	1/16/2019	Buy	AAPL	35	150	150	0	1	0	35	5250	0	-	-	5250	0
6	1/17/2019	Buy	AXP	100	30	50	0	1	50	150	3000	4500	-	-	7500	0
7	2/14/2019	Sell	AAPL	35	170	-	0	1	35	0	5950	5250	5250	150	0	700	13%
8	2/19/2019	Buy	AAPL	10	100	100	0	1	0	10	1000	0	-	-	1000	0
9	2/20/2019	Buy	AAPL	20	90	93.33	0	1	10	30	1800	1000	-	-	2800	0
10	3/2/2020	Sell	AAPL	30	230	-	0	1	30	0	6900	2800	2800	93.33333333	0	4100	146%
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,"")

 

[jasonb75]

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.