Filtering for First and Last day of the Year or Month from Years of Workdate Data

[LakeDog]

Hi, looking at historical stock data downloaded from Yahoo. It is basically 6 columns: Date, Open, High, Low, Close, AdjClose, Volume.

I am trying to Filter by the first date of the year and the last date of the year to get the Close. The issue I am having is that all the data is on working days, so it's not typically the 1st of the month. Actuallly, January 1st is always a holiday so it's the 2nd, 3rd or 4th of the month. Same with the last day day of the year, not all are the 31st and may even the last working day is the 28th of the month. I have used a formula to reduce it down by Filtering (using 365) for the DAY equal to or less than 3 and that helps, but want to clean it up to just the first working day of the year. Attached is an image of an example worksheet, but that formula is:

=FILTER(E:E,(MONTH(A:A)=1)*(DAY(A:A)<=3))

=FILTER(E:E,(MONTH(A:A)=1)*(DAY(A:A)=1),(DAY(A:A)=2))

=FILTER(E:E,(MONTH(A:A)=1)*(DAY(A:A)=2),(DAY(A:A)=3))

I have altered it to filter to the 2nd as well as the 3rd, as well as I have attempted multiple combined nested IF's, AND's and OR's without success to generate a single formula. Looking for any suggestions to what is likely a straight forward solution that I can no longer see. I've put those other formulas on the attached image.

I have tried multiple times but can not get 365 to allow me to let Xl2bb macros work even though MSFT trust center allows. It's loaded on my sheet so sorry, best I can do is an image of the start of the sheet. Understand, I am looking at 9347 rows of data, not just the few in the image.

Thanks!

 

[LakeDog]

I'm filtering the "Close" data using the dates. Need the 1st value in A to be the first day in January shown for each year, obrtaining the corresponding Close value (Column E). Here is a manually generated table for the above data.

Sample File Filtering Dates.xlsx
	N	O	P	Q	R
15	What I'm after (Manually calculated results):
16
17	Date	ClosePrice		Date:	ClosePrice
18	"No data"			12/31/1986	0.1675
19	1/2/1987	0.1658		12/31/1987	0.3767
20	1/4/1988	0.3889		12/30/1988	0.3698
21	1/4/1989	0.3767		etc
22
23	**Note the 1/3/1989 in columns I and J.
Sheet1

 

[LakeDog]

And for the record MARK858, my error in XL2BB was apparently checking the "Properties" in the file XL2BB in Windows Explorer, not fully unzipped. When I saved it to another directory extracted, I found the needed checkbox.......just as you suggested. Thank you for all your help and efforts.

 

[MARK858]

Please note that my dates are in dd/mm/yyyy format, Column T is a list of Public holidays (also the 2nd of January 1989 was a Monday so if it is to be classed as a public holiday it needs adding to the list in column T but even then the first working day would be the 3rd [at least in the UK as we get 1 day in lieu of the 1st falling on a weekend] not the 4th)

Book1.xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1		Open	High	Low	Close	Adj Close	Volume
2	26/12/1986	0.171441	0.172743	0.171007	0.171007	0.106691	3715200													01/01/1986
3	29/12/1986	0.171007	0.172743	0.164063	0.164063	0.102359	41702400													01/01/1987
4	30/12/1986	0.164063	0.166667	0.162326	0.165799	0.103442	25401600													01/01/1988
5	31/12/1986	0.165799	0.170139	0.165799	0.167535	0.104525	23356800													01/01/1989
6	02/01/1987	0.167535	0.169271	0.164931	0.165799	0.103442	12643200													01/01/1990
7	05/01/1987	0.165799	0.177083	0.164931	0.175347	0.109399	48499200													01/01/1991
8	06/01/1987	0.175347	0.178819	0.174479	0.177951	0.111023	40032000													01/01/1992
9	07/01/1987	0.177951	0.1875	0.177951	0.1875	0.116981	60998400													01/01/1993
10	08/01/1987	0.188368	0.195313	0.188368	0.193576	0.120772	61488000													01/01/1994
11	09/01/1987	0.193576	0.202257	0.192708	0.201389	0.125646	63705600													01/01/1995
12	12/01/1987	0.201389	0.216146	0.199653	0.21441	0.13377	128793600													01/01/1996
13	24/12/1987	0.369792	0.373264	0.368056	0.369792	0.230713	12672000													01/01/1997
14	28/12/1987	0.368056	0.369792	0.347222	0.355903	0.222047	50716800													01/01/1998
15	29/12/1987	0.355903	0.381944	0.350694	0.378472	0.236128	79459200													01/01/1999
16	30/12/1987	0.378472	0.388889	0.376736	0.387153	0.241544	70704000													01/01/2000
17	31/12/1987	0.383681	0.387153	0.373264	0.376736	0.235045	61545600													01/01/2001
18	04/01/1988	0.380208	0.392361	0.378472	0.388889	0.242627	110347200								None			0.16754		01/01/2002
19	05/01/1988	0.397569	0.402778	0.392361	0.395833	0.24696	155520000								0.165799			0.37674		01/01/2003
20	06/01/1988	0.397569	0.414931	0.395833	0.40625	0.253459	121680000								0.388889			0.36979		01/01/2004
21	07/01/1988	0.402778	0.421007	0.399306	0.420139	0.262124	90676800								None		`	None		01/01/2005
22	08/01/1988	0.418403	0.428819	0.386285	0.388889	0.242627	145440000								None			None		01/01/2006
23	11/01/1988	0.387153	0.402778	0.380208	0.40191	0.250751	143568000													01/01/2007
24	22/12/1988	0.364583	0.368056	0.359375	0.361111	0.225297	74592000													01/01/2008
25	23/12/1988	0.359375	0.361111	0.357639	0.361111	0.225297	17121600													01/01/2009
26	27/12/1988	0.361111	0.364583	0.359375	0.359375	0.224213	30772800													01/01/2010
27	28/12/1988	0.361111	0.362847	0.359375	0.362847	0.22638	21268800													01/01/2011
28	29/12/1988	0.361111	0.371528	0.361111	0.369792	0.230713	38275200													01/01/2012
29	30/12/1988	0.371528	0.376736	0.369792	0.369792	0.230713	63820800													01/01/2013
30	03/01/1989	0.371528	0.373264	0.366319	0.372396	0.232337	51825600													01/01/2014
31	04/01/1989	0.371528	0.381076	0.371528	0.376736	0.235045	53899200													01/01/2015
32																				01/01/2016
33																				01/01/2017
34																				01/01/2018
35																				01/01/2019
36																				01/01/2020
37																				01/01/2021
38																				01/01/2022
39																				01/01/2023
40																				01/01/2024
41																				01/01/2025
42																				01/01/2026
43																				01/01/2027
44																				01/01/2028
45																				01/01/2029
46																				01/01/2030
47																				01/01/2031
48																				01/01/2032
Sheet1

Cell Formulas
Range	Formula
O18:O22	O18	=FILTER(E:E,A:A=WORKDAY(DATE(ROW(Z1986),1,1)-1,1,$T$2:$T$48),"None")
R18:R22	R18	=FILTER(E:E,A:A=WORKDAY("1JAN"&(ROW(Z1986)+1),-1),"None")

 

[LakeDog]

Major strides forward! Appreciate it. The formula for the last day of the year seems to work well. But I'm struggling with the first day of the year. It seems to work variably and I can't sort out why. I've attached the results I get using them in the entire database. Column O is the formula for the first day of the year and column R is the last day. Column N is the same first day formula filtering the date column. Similarly, Column Q is the last day formula filtering the date. The dates match the close prices. Tried several changes, but can't get the first day to work. Any thoughts?

MSFT Data work.xlsx
	N	O	P	Q	R	S	T
1
2	#CALC!	0.097222		12/31/1986	0.167535		1/1/1986
3	#CALC!	None		12/31/1987	0.376736		1/1/1987
4	#CALC!	None		12/30/1988	0.369792		1/1/1988
5	#CALC!	None		12/29/1989	0.604167		1/1/1989
6	#CALC!	None		12/31/1990	1.045139		1/1/1990
7	#CALC!	None		12/31/1991	2.317708		1/1/1991
8	#CALC!	None		12/31/1992	2.667969		1/1/1992
9	#CALC!	None		12/31/1993	2.519531		1/1/1993
10	1/3/1994	2.503906		12/30/1994	3.820313		1/1/1994
11	#CALC!	None		12/29/1995	5.484375		1/1/1995
12	#CALC!	None		12/31/1996	10.32813		1/1/1996
13	#CALC!	None		12/31/1997	16.15625		1/1/1997
14	#CALC!	None		12/31/1998	34.67188		1/1/1998
15	#CALC!	None		12/31/1999	58.375		1/1/1999
16	1/3/2000	58.28125		12/29/2000	21.6875		1/1/2000
17	#CALC!	None		12/31/2001	33.125		1/1/2001
18	#CALC!	None		12/31/2002	25.85		1/1/2002
19	#CALC!	None		12/31/2003	27.37		1/1/2003
20	#CALC!	None		12/31/2004	26.72		1/1/2004
21	1/3/2005	26.74		12/30/2005	26.15		1/1/2005
22	#CALC!	None		12/29/2006	29.86		1/1/2006
23	#CALC!	None		12/31/2007	35.6		1/1/2007
24	#CALC!	None		12/31/2008	19.44		1/1/2008
25	#CALC!	None		12/31/2009	30.48		1/1/2009
26	#CALC!	None		12/31/2010	27.91		1/1/2010
27	1/3/2011	27.98		12/30/2011	25.96		1/1/2011
28	#CALC!	None		12/31/2012	26.71		1/1/2012
29	#CALC!	None		12/31/2013	37.41		1/1/2013
30	#CALC!	None		12/31/2014	46.45		1/1/2014
31	#CALC!	None		12/31/2015	55.48		1/1/2015
32	#CALC!	None		12/30/2016	62.14		1/1/2016
33	#CALC!	None		12/29/2017	85.54		1/1/2017
34	#CALC!	None		12/31/2018	101.57		1/1/2018
35				12/31/2019	157.7		1/1/2019
36				12/31/2020	222.42		1/1/2020
37				12/31/2021	336.32		1/1/2021
38				12/30/2022	239.82		1/1/2022
39							1/1/2023
40							1/1/2024
41							1/1/2025
42
MSFT

Cell Formulas
Range	Formula
N2	N2	=FILTER(A:A,A:A=WORKDAY(DATE(ROW(X1986),1,1)-1,1,$T$2:$T$41))
O2:O34	O2	=FILTER(E:E,A:A=WORKDAY(DATE(ROW(X1986),1,1)-1,1,$S$2:$S$21),"None")
N3:N34	N3	=FILTER(A:A,A:A=WORKDAY(DATE(ROW(X1987),1,1)-1,1,$S$2:$S$21))
Q2:Q38	Q2	=FILTER(A:A,A:A=WORKDAY("1JAN"&(ROW(X1986)+1),-1),"None")
R2:R38	R2	=FILTER(E:E,A:A=WORKDAY("1JAN"&(ROW(X1986)+1),-1),"None")

 

[LakeDog]

Oops, found one simple error. Corrected and it helps. Still missing something.

MSFT Data work.xlsx
	N	O
1
2	#CALC!	None
3	1/2/1987	0.165799
4	1/4/1988	0.388889
5	#CALC!	None
6	1/2/1990	0.616319
7	1/2/1991	1.038194
8	1/2/1992	2.375
9	1/4/1993	2.660156
10	1/3/1994	2.503906
11	#CALC!	None
12	1/2/1996	5.609375
13	1/2/1997	10.20313
14	1/2/1998	16.39063
15	1/4/1999	35.25
16	1/3/2000	58.28125
17	1/2/2001	21.6875
18	1/2/2002	33.52
19	1/2/2003	26.86
20	1/2/2004	27.45
21	1/3/2005	26.74
22	#CALC!	None
23	#CALC!	None
24	1/2/2008	None
25	1/2/2009	None
26	1/4/2010	None
27	1/3/2011	27.98
28	#CALC!	None
29	1/2/2013	None
30	1/2/2014	None
31	1/2/2015	None
32	1/4/2016	None
33	#CALC!	None
34	1/2/2018	None
MSFT

Cell Formulas
Range	Formula
N2:N34	N2	=FILTER(A:A,A:A=WORKDAY(DATE(ROW(X1986),1,1)-1,1,$T$2:$T$41))
O2:O34	O2	=FILTER(E:E,A:A=WORKDAY(DATE(ROW(X1986),1,1)-1,1,$T$2:$T$21),"None")

 

[MARK858]

Not home but have you added any dates that are holidays other than the 1st January to column T?
You don't appear to on my phone.

 

[LakeDog]

No, I did check to make sure I had every year listed.

 

[MARK858]

No, I did check to make sure I had every year listed.

You need to add them (and adjust the range in the formula if necessary) as Excel has no way of knowing what is a public holiday in each country, as I put in post 13....

Column T is a list of Public holidays (also the 2nd of January 1989 was a Monday so if it is to be classed as a public holiday it needs adding to the list in column T but even then the first working day would be the 3rd [at least in the UK as we get 1 day in lieu of the 1st falling on a weekend] not the 4th)

 

[LakeDog]

You need to add them (and adjust the range in the formula if necessary) as Excel has no way of knowing what is a public holiday in each country, as I put in post 13....

Yep, that is likely the issue.

But have you ever worked for days on an issue and them realized your perspective is wrong?? I sincerely appreciate MARK858's efforts and insight. I've learned a lot, which is really why I do this. I'm retired, this is better than zigsaw puzzles. But it finally hit me. Using the solution, which is perfect from MARK858, I need a stable list of dates for the 1st of the month and holidays. There's actually 10 holidays a year (including the 1st of January). And they can change so that list of holidays for 40 years of data could be 400. Okay, probably more like 350-360 given New Years day is consistent. Regardless, that is a big list.

But then I wokeup. I am working with different ticker databases, but all in the same years. The dates are the most stable variable. At the maximum, 40 years of data or 80 consistent reference dates. A simple XLOOKUP would work. Indeed, a simple such formula would give me the data I need. I just needed to keep a relatively small data of the actual dates in a column to use as the array for a search. Below is just the January first day of such data.

I sincerely appreciate MARK858's efforts and I have learned a lot. Thank you!!!!!!!!!!!!!!!!!!!

MSFT Data work Trial A.xlsx
	I	J	K	L
3
4		1/2/1987	0.1658
5		1/4/1988	0.3889
6		1/3/1989	0.3724
7		1/2/1990	0.6163
8		1/2/1991	1.0382
9		1/2/1992	2.3750
10		1/4/1993	2.6602
11		1/3/1994	2.5039
12		1/3/1995	3.7891
13		1/2/1996	5.4297
14		1/2/1997	10.5781
15		1/2/1998	35.2500
16		1/4/1999	58.2813
17		1/3/2000	56.3125
18		1/2/2001	23.9688
19		1/2/2002	34.6150
20		1/2/2003	26.8950
21		1/2/2004	26.7400
22		1/3/2005	26.8400
23		1/3/2006	26.9700
24		1/3/2007	29.8100
25		1/2/2008	35.3700
26		1/2/2009	30.9500
27		1/4/2010	27.9800
28		1/3/2011	28.0900
29		1/3/2012	27.4000
30		1/2/2013	27.2500
31		1/2/2014	36.9100
32		1/2/2015	54.8000
33		1/4/2016	62.5800
34		1/3/2017	62.3000
35		1/2/2018	86.3500
36		1/2/2019	97.4000
37		1/2/2020	158.6200
38		1/4/2021	334.7500
39		1/3/2022	329.0100
40		1/3/2023	229.1000
MSFT

 

[MARK858]

You're welcome, happy you have a solution that works for you.