Return a Prior Month's Value from within the Same Table?

[chasfh]

What I am actually trying to do is create a formula that will capture a stock's gain or loss by referring to the number of shares in a prior month within the same table, and then multiplying that by the price difference between this month and the prior month. I have included what the answer should be in the sample XL2BB table below.

I need to do this on the prior month's shares total because when I receive a dividend, it gets reinvested in more shares, and I don't want to do so on the current month's higher share total because I would be double-counting against a separate income spreadsheet we are keeping. That's why the should-be answer for DUHP is not simply this month's total minus last month's total.

Since I am trying to return a column's value based on lookups of other columns, I tried doing this with XLOOKUP, but since the lookup values and the lookup array are all the exact same columns, I get the #SPILL error. So, if this can be done, I assume it will entail use of a different formula.

Seems self-explanatory, but let me know whether I need to clarify. Thanks.

Book1
	A	B	C	D	E	F	G	H	I	J
1	Period	Ticker	Shares	Price	Total	Month	Year	M/M G/L		Answer should be
2	Aug-24	DRIV	200	22.71	$ 4,542.00	8	2024
3	Sep-24	DRIV	200	23.33	$ 4,666.00	9	2024			$ 124.00
4	Aug-24	DUHP	317.9095	33.56	$ 10,669.04	8	2024
5	Sep-24	DUHP	318.8494	33.91	$ 10,812.18	9	2024			$ 111.27
6	Aug-24	EMXC	73.21556	60.66	$ 4,441.26	8	2024
7	Sep-24	EMXC	73.21556	60.71	$ 4,444.92	9	2024			$ 3.66
Sheet1

Cell Formulas
Range	Formula
F2:F7	F2	=MONTH(A2)
G2:G7	G2	=YEAR(A2)
J3,J7,J5	J3	=C2*(D3-D2)

 

[Peter_SSs]

.. and I think still produces the correct results with any sorting (this one sorted by Total)

chasfh.xlsm
	A	B	C	D	E	F	G	H
3	Mo. End	Account	Ticker	Shares	Price	Total	M/M G/L	Test
4	Sep-24	Main	SCYX	180	1.43	$257.40		-5.4
5	Aug-24	Main	SCYX	180	1.46	$262.80	5.4	-120.6
6	Jul-24	Main	SCYX	180	2.13	$383.40	120.6
7	Aug-24	Main	BCAB	401	1.77	$709.77
8	Aug-24	Main	SWVXX	1065.37	1	$1,065.37
9	Jul-24	Main	SB	233	5.06	$1,178.98
10	Sep-24	Main	BCAB	607	2.03	$1,232.21	104.26	104.26
11	Jul-24	Main	ACIC	129	12.21	$1,575.09
12	Aug-24	Main	OLPX	911	2.09	$1,903.99
13	Sep-24	Main	OLPX	911	2.34	$2,131.74	227.75	227.75
14	Sep-24	Main	ACIC	281	10.93	$3,071.33	-165.12	-84.3
15	Jul-24	Main	REAL	841	3.74	$3,145.34
16	Aug-24	Main	ACIC	281	11.23	$3,155.63	84.3	-126.42
17	Jul-24	Main	SBLK	153	22.54	$3,448.62
18	Jul-24	Main	FSLR	17	215.99	$3,671.83
19	Aug-24	Main	REAL	1420	2.64	$3,748.80	-925.1	-925.1
20	Jul-24	Main	VTLE	89	43.61	$3,881.29
21	Sep-24	Main	REAL	1420	2.99	$4,245.80	497	497
22	Jul-24	Main	SQ	70	61.88	$4,331.60
23	Aug-24	Main	HPP	911	5.15	$4,691.65
24	Jul-24	Main	PHYS	271	18.98	$5,143.58
25	Jul-24	Main	C	82	64.88	$5,320.16
26	Sep-24	Main	HPP	1259	4.55	$5,728.45	-546.6	-546.6
27	Jul-24	Main	HCA	17	363.05	$6,171.85
28	Sep-24	Main	MOH	25	331.8	$8,295.00		-449.75
29	Sep-24	Main	OS	264	32.24	$8,511.36
30	Aug-24	Main	MOH	25	349.79	$8,744.75	449.75
31	Sep-24	Main	ALAB	178	50.54	$8,996.12
32	Aug-24	Main	FOUR	110	83.1	$9,141.00
33	Sep-24	Main	ACMR	484	19.11	$9,249.24
34	Sep-24	Main	MU	94	99.85	$9,385.90
35	Sep-24	Main	FOUR	110	88.9	$9,779.00	638	638
36	Jul-24	Main	VYM	91.0073	124.33	$11,314.94
37	Aug-24	Main	VYM	91.0073	127.36	$11,590.69	275.752119	275.752119
38	Sep-24	Main	VYM	91.6129	128	$11,726.45	58.244672	58.244672
39	Aug-24	Main	SHOP	200	74.07	$14,814.00
40	Aug-24	Main	AMZN	83	178.5	$14,815.50		-703.84
41	Sep-24	Main	AMZN	83	184.76	$15,335.08	519.58	519.58
42	Jul-24	Main	AMZN	83	186.98	$15,519.34	184.26
43	Sep-24	Main	SHOP	200	79.14	$15,828.00	1014	1014
44	Sep-24	Main	SWVXX	0	1	$ -	0	0
Sheet2

Cell Formulas
Range	Formula
G4:G44	G4	=LET(arrLookup,Table2[[#Headers],[Account]]:[@Account]&Table2[[#Headers],[Ticker]]:[@Ticker],arrReturn,Table2[[#Headers],[Shares]]:[@Price],arrPrevSharesPrice,XLOOKUP([@Account]&[@Ticker],DROP(arrLookup,-1),DROP(arrReturn,-1),"",0,-1),PrevShares,CHOOSECOLS(arrPrevSharesPrice,1),PrevPrice,CHOOSECOLS(arrPrevSharesPrice,2),CurrPrice,[@Price],IFERROR(PrevShares*(CurrPrice-PrevPrice),""))
H4:H44	H4	=LET(s,TAKE(SORT(FILTER(Table2[[Mo. End]:[Price]],([Mo. End]<=[@[Mo. End]])*([Account]=[@Account])*([Ticker]=[@Ticker])),1),-2),IFERROR(INDEX(s,1,4)*(INDEX(s,2,5)-INDEX(s,1,5)),""))

 

[chasfh]

Man, I just don't know how you guys can figure this stuff out, but it's pretty amazing!

I apologize, @Alex Blakenburg, but I had to mark @Peter_SSs's new formula as the solution, since I can now sort by any column and the calculation does not break.

Peter, for the life of me, I cannot deconstruct this formula and figure out exactly how it works. Would you be so kind as to break it down and explain exactly why this does what we are asking it to do? I think I get the LET part, but the INDEX parts at the end are especially confusing at the moment. Thanks!

 

[chasfh]

Ok, so, @Peter_SSs, apologies for this, but I'm running into another issue, and it's my fault for not sharing this with you because I did not think of this needing to happen.

Typically we want to do month-over-month calculation based on prior month's shares because additional shares are added, and we can't include that in the gain/loss total. that would happen 95% of the time.

But sometimes shares are sold off, not added, so in those cases we want to base the calculation on the current month's shares, not the previous month's.

In that case we need some flavor of IF test where, if the value under Buy/Sell? column equals "Sell", we need to make that calculation based on same month's shares.

I tried to see if I could fit a revision into your formula, but since I can't deconstruct it to understand it, I don't know how I can add a reference to the additional column anits alternate formula.

I've posted the XL2BB below, showing the example for two issues we have. Off on the right are the differneces based on the calculation you did, and then what they should be with a new calcuation. Let me know if there's something more you need? I appreciate your continuing help on this (and I hope it's fun for you to figure it out!). Thanks again!

_Net Worth - Terrie and Chuck.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
2	Mo. End	Account	Ticker	Shares	Price	Total	M/M G/L	Month	Year	Buy/Sell?	Shares2	Total2	Realized
3	May-24	Terrie	NICHX	388.5556	27.59	$ 10,720.25		5	2024						Calculation:		Should be:
4	Jun-24	Terrie	NICHX	388.5556	27.16	$ 10,553.17	$ (167.08)	6	2024						$ (167.08)		$ (167.08)
5	Jul-24	Terrie	NICHX	388.5556	27.41	$ 10,650.31	$ 97.14	7	2024						$ 97.14		$ 97.14
6	Aug-24	Terrie	NICHX	388.5556	27.57	$ 10,712.48	$ 62.17	8	2024						$ 62.17		$ 62.17
7	Sep-24	Terrie	NICHX	282.8209	27.12	$ 7,670.10	$ (174.85)	9	2024	Sell	-1057.35	29252.36			$ (174.85)		$ (127.27)
8	May-24	CP-Managed	NPSRX	395.131	15.25	$ 6,025.75		5	2024
9	Jun-24	CP-Managed	NPSRX	397.0937	15.30	$ 6,075.53	$ 19.76	6	2024						$ 19.76		$ 19.76
10	Jul-24	CP-Managed	NPSRX	399.0489	15.44	$ 6,161.32	$ 55.59	7	2024						$ 55.59		$ 55.59
11	Aug-24	CP-Managed	NPSRX	400.9902	15.62	$ 6,263.47	$ 71.83	8	2024						$ 71.83		$ 71.83
12	Sep-24	CP-Managed	NPSRX	383.9396	15.81	$ 6,070.09	$ 76.19	9	2024	Sell	-189.633	3000			$ 76.19		$ 72.95
R-IY Worksheet (2)

Cell Formulas
Range	Formula
H3:H9,H11:H12	H3	=MONTH(A3)
I3:I9,I11:I12	I3	=YEAR(A3)
J3:J12	J3	=IFNA(IF(XLOOKUP([@Account]&[@Ticker]&[@Month]&[@Year],History[Account]&History[Symbol]&History[Month]&History[Year],History[Action])="buy","Buy",IF(XLOOKUP([@Account]&[@Ticker]&[@Month]&[@Year],History[Account]&History[Symbol]&History[Month]&History[Year],History[Action])="sell","Sell","")),"")
K3:K12	K3	=IF([@[Buy/Sell?]]="Buy",(XLOOKUP([@Account]&[@Ticker]&[@Month]&[@Year],History[Account]&History[Symbol]&History[Month]&History[Year],History[Quantity])),IF([@[Buy/Sell?]]="Sell",-(XLOOKUP([@Account]&[@Ticker]&[@Month]&[@Year],History[Account]&History[Symbol]&History[Month]&History[Year],History[Quantity])),""))
L3:L12	L3	=IF([@[Buy/Sell?]]="Buy",(XLOOKUP([@Account]&[@Ticker]&[@Month]&[@Year],History[Account]&History[Symbol]&History[Month]&History[Year],History[Amount])),IF([@[Buy/Sell?]]="Sell",(XLOOKUP([@Account]&[@Ticker]&[@Month]&[@Year],History[Account]&History[Symbol]&History[Month]&History[Year],History[Amount])),""))
Q4:Q6,Q9:Q11	Q4	=O4
O4:O7,O9:O12	O4	=D3*(Table24[@Price]-E3)
G3:G12	G3	=LET(s,TAKE(SORT(FILTER(Table24[[Mo. End]:[Price]],([Mo. End]<=[@[Mo. End]])*([Account]=[@Account])*([Ticker]=[@Ticker])),1),-2),IFERROR(INDEX(s,1,4)*(INDEX(s,2,5)-INDEX(s,1,5)),""))

 

[Alex Blakenburg]

As you previously deduced my original formula was based on the table being sorted, so if that is the case then Peter's formula is definitely the way to go.
If you just want to use the current Qty if the previous Qty > the current row Qty, this modification to Peter's formula should work.
Note: It mentions Table3 in one spot change that to your table name

=LET(s,TAKE(SORT(
              FILTER(Table3[[Mo. End]:[Price]],
                     ([Mo. End]<=[@[Mo. End]])*([Account]=[@Account])*([Ticker]=[@Ticker])),1),-2),
     SharesQtyToUse, IF(INDEX(s,1,4)<=INDEX(s,2,4),INDEX(s,1,4),INDEX(s,1,5)),
     IFERROR(SharesQtyToUse*(INDEX(s,2,5)-INDEX(s,1,5)),""))

 

[Peter_SSs]

I don't follow the examples. Formulas shown seem to refer to tables called 'Table24' and 'History'. I don't know what either of those are in relation to the sample data shown.

But sometimes shares are sold off, not added, so in those cases we want to base the calculation on the current month's shares, not the previous month's.

In that case we need some flavor of IF test where, if the value under Buy/Sell? column equals "Sell", we need to make that calculation based on same month's shares.

For what you are asking, do you really need a Buy/Sell? column? Wouldn't it be simply that if the number of shares is less that what it was in the previous month then there must have been a sale?

I suspect there will be a simpler formula but until I can understand the data and requirement better .....

Back to the sample data:
NICHX was 282.8209 shares @ 27.12 in Sep-24. In Aug-24 it was 388.5556 shares @ 27.57. What are the steps in the calculation you want for NICHX for the row Sep-24?

 

[chasfh]

I don't follow the examples. Formulas shown seem to refer to tables called 'Table24' and 'History'. I don't know what either of those are in relation to the sample data shown.


For what you are asking, do you really need a Buy/Sell? column? Wouldn't it be simply that if the number of shares is less that what it was in the previous month then there must have been a sale?

I suspect there will be a simpler formula but until I can understand the data and requirement better .....

Back to the sample data:
NICHX was 282.8209 shares @ 27.12 in Sep-24. In Aug-24 it was 388.5556 shares @ 27.57. What are the steps in the calculation you want for NICHX for the row Sep-24?

'Table24' is Table 2, I had copied the tab so I could manipulate that one instead of the original so I could avoid inadvertently screwing up that original. 'History' is a table on another tab this is pulling the words "Buy" or "Sell" into the cell, if there was a purchase or a sale.

I do need a buy/sell column, for two reasons: (1) if there was a "buy", I need to subtract the bought shares under Shares2 column from the same month's shares under Shares column so I can calculate the M/M G/L of this month's price minus last month's price based only on those shares carried over; and (2) if there was a "sell", I need to calculate M/M G/L based only on remaining shares this month after the sell, not on prior month's shares carried over—but, additionally, I need to calculate a realized gain under Realized column, which you don't have to worry about for the purposes of helping me here.

Am I making sense?

 

[Peter_SSs]

Am I making sense?

Yes, in relation to my first question.


No, in relation to my second question which doesn't seem to me to have been addressed.

Back to the sample data:
NICHX was 282.8209 shares @ 27.12 in Sep-24. In Aug-24 it was 388.5556 shares @ 27.57. What are the steps in the calculation you want for NICHX for the row Sep-24?

 

[chasfh]

Back to the sample data:
NICHX was 282.8209 shares @ 27.12 in Sep-24. In Aug-24 it was 388.5556 shares @ 27.57. What are the steps in the calculation you want for NICHX for the row Sep-24?

I hope I understand what you're looking for here ...

The logic will go:

• Check to see whether Buy/Sell? column (cell J7) contains "Sell";
• If yes, return same month's shares (D7) minus (same month's price (E7) minus previous month's price (E6))
• If no, return previous month's shares (D6) minus (same month's price (E7) minus previous month's price (E6))—that is, same process that you have in there now.

If this isn't what you're looking for, please advise. Thanks!

 

[Peter_SSs]

If yes, return same month's shares (D7) minus (same month's price (E7) minus previous month's price (E6))

So according to that
D7 minus (E7 -E6)
282.8209 - (27.12 - 27.57)
282.8209 - (-0.45)
283.2709

You said post 13 showed what the result should be but I don't see this figure anywhere in that post.

I did wonder if you actually meant

If yes, return same month's shares (D7) times (same month's price (E7) minus previous month's price (E6))

. . but applying that
D7 * (E7 - E6)
282.8209 * (-0.45)
-127.27
Again I do not see that figure anywhere in post 13.

So I am still not sure what calculation you do actually want.

 

[chasfh]

So according to that
D7 minus (E7 -E6)
282.8209 - (27.12 - 27.57)
282.8209 - (-0.45)
283.2709

You said post 13 showed what the result should be but I don't see this figure anywhere in that post.

I did wonder if you actually meant

. . but applying that
D7 * (E7 - E6)
282.8209 * (-0.45)
-127.27
Again I do not see that figure anywhere in post 13.

So I am still not sure what calculation you do actually want.

The $-127.27 figure is in cell Q7 of Post 13, in red.