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)

 

[rowebca]

You can do something simple like this:

Function gainSTCK(a As Range) As Double
If a = a.Offset(-1, 0) Then
gainSTCK = a.Offset(0, 1) * (a.Offset(0, 2) - a.Offset(-1, 2))
Else
gainSTCK = 0
End If
End Function

So practically we are checking the column B to see if values are equal.

an example will be:

 In any cell = gainSTCK(B3) ' one example

You can use this function in a loop to get all values from the table.


Regards,
GB

 

[Peter_SSs]

I'm not sure if each stock will always appear each month or what should happen if it doesn't, but see if this helps.

chasfh.xlsm
	A	B	C	D	E			H
1	Period	Ticker	Shares	Price	Total			M/M G/L
2	Aug-24	DRIV	200	22.71	$4,542.00
3	Sep-24	DRIV	200	23.33	$4,666.00			124.00
4	Aug-24	DUHP	317.9095	33.56	$10,669.04
5	Sep-24	DUHP	318.8494	33.91	$10,812.18			111.27
6	Aug-24	EMXC	73.21556	60.66	$4,441.26
7	Sep-24	EMXC	73.21556	60.71	$4,444.92			3.66
Sheet1

Cell Formulas
Range	Formula
H2:H7	H2	=LET(f,TAKE(FILTER(C$2:D2,(A$2:A2<=A2)*(B$2:B2=B2)),-2),IFERROR(INDEX(f,1,1)*(INDEX(f,2,2)-INDEX(f,1,2)),""))

 

[Alex Blakenburg]

This is a fair bit longer but uses table structured referencing.
I didn't have the table name so I have used Table1.

=LET(
arrLookup,Table1[[#Headers],[Ticker]]:[@Ticker],
arrReturn,Table1[[#Headers],[Shares]]:[@Price],
arrPrevSharesPrice, XLOOKUP([@Ticker],
                            DROP(arrLookup,-1),
                            DROP(arrReturn,-1),
                            "",0,-1),
PrevShares,CHOOSECOLS(arrPrevSharesPrice,1),
PrevPrice,CHOOSECOLS(arrPrevSharesPrice,2),
CurrPrice,[@Price],
IFERROR(PrevShares*(CurrPrice-PrevPrice),""))

 

[chasfh]

I gotta say, @Peter_SSs and @Alex Blakenburg, you guys are absolute wizards. There was no way I was going to arrive at these solutions on my own, and I really appreciate you guys taking a good hack at this.

I'm going to have to crown Alex the winner on this one. What I showed you was a simplified table; there are actually multiple accounts and the same stock shows up in more than one account, and Alex's formula was the one that was able to separate the accounts out and calculate correctly.

Of course I had to add another column, the Account column, to the #Headers and XLOOKUP portion of the formula, so that it looks more like this:

=LET(
arrLookup,Table1[[#Headers],[Account]]:[@Account]&Table1[[#Headers],[Ticker]]:[@Ticker],
arrReturn,Table1[[#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),""))

And of course, remove the spaces for the formula to work in the worksheet in the first place, and change the table name, so this is the actual formula from the top of the table:

=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),""))

Which looks like alphanumeric vomit but, as you guys with overseas accents like to say, it "works a treat!"

Thanks a bunch!

 

[Alex Blakenburg]

Thanks for the comprehensive feedback. Glad we could help.

 

[Peter_SSs]

Glad you have it working how you want.
Any chance you could post a slightly larger set of realistic sample data with the correct columns and your final formula in it so I can take a look at it in action?
Before creating an XL2BB sample, you can hide any of the columns that the formula does not use (to keep the XL2BB sample more manageable).

 

[chasfh]

Sure, @Peter_SSs, here you go. M/M G/L means month over month gain or loss, based on previous months' shares totals, since additional shares purchased or added by dividend reinvestment would constitute buys or income, and not gains or losses.

_Net Worth - Terrie and Chuck.xlsx
	A	B	C	D	E	F	G
1	Sort by
2	Ascending
3	Mo. End	Account	Ticker	Shares	Price	Total	M/M G/L
4	Jul-24	Main	ACIC	129	12.21	$ 1,575.09
5	Aug-24	Main	ACIC	281	11.23	$ 3,155.63	$ (126.42)
6	Sep-24	Main	ACIC	281	10.93	$ 3,071.33	$ (84.30)
7	Sep-24	Main	ACMR	484	19.11	$ 9,249.24
8	Sep-24	Main	ALAB	178	50.54	$ 8,996.12
9	Jul-24	Main	AMZN	83	186.98	$ 15,519.34
10	Aug-24	Main	AMZN	83	178.50	$ 14,815.50	$ (703.84)
11	Sep-24	Main	AMZN	83	184.76	$ 15,335.08	$ 519.58
12	Aug-24	Main	BCAB	401	1.77	$ 709.77
13	Sep-24	Main	BCAB	607	2.03	$ 1,232.21	$ 104.26
14	Jul-24	Main	C	82	64.88	$ 5,320.16
15	Aug-24	Main	FOUR	110	83.10	$ 9,141.00
16	Sep-24	Main	FOUR	110	88.90	$ 9,779.00	$ 638.00
17	Jul-24	Main	FSLR	17	215.99	$ 3,671.83
18	Jul-24	Main	HCA	17	363.05	$ 6,171.85
19	Aug-24	Main	HPP	911	5.15	$ 4,691.65
20	Sep-24	Main	HPP	1259	4.55	$ 5,728.45	$ (546.60)
21	Aug-24	Main	MOH	25	349.79	$ 8,744.75
22	Sep-24	Main	MOH	25	331.80	$ 8,295.00	$ (449.75)
23	Sep-24	Main	MU	94	99.85	$ 9,385.90
24	Aug-24	Main	OLPX	911	2.09	$ 1,903.99
25	Sep-24	Main	OLPX	911	2.34	$ 2,131.74	$ 227.75
26	Sep-24	Main	OS	264	32.24	$ 8,511.36
27	Jul-24	Main	PHYS	271	18.98	$ 5,143.58
28	Jul-24	Main	REAL	841	3.74	$ 3,145.34
29	Aug-24	Main	REAL	1420	2.64	$ 3,748.80	$ (925.10)
30	Sep-24	Main	REAL	1420	2.99	$ 4,245.80	$ 497.00
31	Jul-24	Main	SB	233	5.06	$ 1,178.98
32	Jul-24	Main	SBLK	153	22.54	$ 3,448.62
33	Jul-24	Main	SCYX	180	2.13	$ 383.40
34	Aug-24	Main	SCYX	180	1.46	$ 262.80	$ (120.60)
35	Sep-24	Main	SCYX	180	1.43	$ 257.40	$ (5.40)
36	Aug-24	Main	SHOP	200	74.07	$ 14,814.00
37	Sep-24	Main	SHOP	200	79.14	$ 15,828.00	$ 1,014.00
38	Jul-24	Main	SQ	70	61.88	$ 4,331.60
39	Aug-24	Main	SWVXX	1065.37	1.00	$ 1,065.37
40	Sep-24	Main	SWVXX	0	1.00	$ -	$ -
41	Jul-24	Main	VTLE	89	43.61	$ 3,881.29
42	Jul-24	Main	VYM	91.0073	124.33	$ 11,314.94
43	Aug-24	Main	VYM	91.0073	127.36	$ 11,590.69	$ 275.75
44	Sep-24	Main	VYM	91.6129	128.00	$ 11,726.45	$ 58.24
R-IY Worksheet

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),""))

 

[chasfh]

Thanks for the comprehensive feedback. Glad we could help.

Sure thing, and I do have a question: it seems the formula won't really work unless the table is actively sorted by month ascending, whether primarily or secondarily. If I sort it by any other column and there is no secondary sort by month ascending, the calculations from the formula breaks. Is that a requirement that's built into the formula? Is there any way to tweak the formula so that it will calculate properly no matter how I sort the table?

 

[Peter_SSs]

So try this. I have the previous suggestion formula in col G and a new formula in col H.
This much shorter formula produces the same results for this layout ..

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