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!

 

[MARK858]

I have tried multiple times but can not get 365 to allow me to let Xl2bb macros work

Have you checked under the general tab on the XL2BB file properties to make sure you haven't got an Unblock checkbox?

 

[LakeDog]

Have you checked under the general tab on the XL2BB file properties to make sure you haven't got an Unblock checkbox?

Appreciate the idea, but no such luck. While I am sure it's something stupid I'm missing, I continue to miss the answer to XL2BB. I've disabled any and all blockers in Excel and Kaspersky. The program shows up on my workbook, but won't let me highlight nor capture any part of my sheet. "MiniSheet" and "Table Only" are grayed out. Thanks for the suggestion.

 

[MARK858]

As for your question try using the formulas below to determine the first/last days of the year. The N3 and N4 are only there for when Jan 1st isn't a weekend and really only applies to the first formula.

Book1.xlsb
	B	C	D									M	N
3	1st day	02/01/2023											01/01/2023
4	Last day	29/12/2023											01/01/2024
Sheet1

Cell Formulas
Range	Formula
C3	C3	=WORKDAY(DATE(YEAR(TODAY()),1,1)-1,1,N3:N4)
C4	C4	=WORKDAY("1JAN"&(YEAR(TODAY())+1),-1,N3:N4)

 

[LakeDog]

As for your question try using the formulas below to determine the first/last days of the year. The N3 and N4 are only there for when Jan 1st isn't a weekend and really only applies to the first formula.

Book1.xlsb
	B	C	D									M	N
3	1st day	02/01/2023											01/01/2023
4	Last day	29/12/2023											01/01/2024
Sheet1

Cell Formulas
Range	Formula
C3	C3	=WORKDAY(DATE(YEAR(TODAY()),1,1)-1,1,N3:N4)
C4	C4	=WORKDAY("1JAN"&(YEAR(TODAY())+1),-1,N3:N4)

Thanks for the suggestion. Worked with it overnight and am unable to get it to work. Trying a few more things......

 

[MARK858]

Can you post your data using the boards XL2BB add-in so I can copy it and I'll have a look when I get in tonight

 

[LakeDog]

Still can't get XL2BB to work on my desktop. I'll try my laptop in a bit to see if I can use it on it. Plus, have to thin out the database significantly by hand to make it work. Most of these databases are 9500 rows by 6 columns and the areas of interest are every 300 or so rows apart. I'll work on it.

Thank you for your efforts. Appreciated

 

[MARK858]

I don't need the full range, 20 rows will do as the layout is more important. Just needs enough to illustrate the issue

 

[LakeDog]

Understood but the data points of interest are separated by 200+ rows so it works better to thin it out. Otherwise, there's one result at best!

 

[LakeDog]

Here, finally

Sample File Filtering Dates.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1		Open	High	Low	Close	Adj Close	Volume
2	12/26/1986	0.171441	0.172743	0.171007	0.171007	0.106691	3715200		1/0/1900	Close
3	12/29/1986	0.171007	0.172743	0.164063	0.164063	0.102359	41702400		1/2/1987	0.165799		0.165799
4	12/30/1986	0.164063	0.166667	0.162326	0.165799	0.103442	25401600		1/4/1988	0.388889
5	12/31/1986	0.165799	0.170139	0.165799	0.167535	0.104525	23356800		1/3/1989	0.372396	**
6	1/2/1987	0.167535	0.169271	0.164931	0.165799	0.103442	12643200		1/4/1989	0.376736
7	1/5/1987	0.165799	0.177083	0.164931	0.175347	0.109399	48499200		1/0/1900	0
8	1/6/1987	0.175347	0.178819	0.174479	0.177951	0.111023	40032000		1/0/1900	0
9	1/7/1987	0.177951	0.1875	0.177951	0.1875	0.116981	60998400		1/0/1900	0
10	1/8/1987	0.188368	0.195313	0.188368	0.193576	0.120772	61488000		1/0/1900	0
11	1/9/1987	0.193576	0.202257	0.192708	0.201389	0.125646	63705600		1/0/1900	0
12	1/12/1987	0.201389	0.216146	0.199653	0.21441	0.13377	1.29E+08		1/0/1900	0
13	12/24/1987	0.369792	0.373264	0.368056	0.369792	0.230713	12672000		1/0/1900	0
14	12/28/1987	0.368056	0.369792	0.347222	0.355903	0.222047	50716800		1/0/1900	0
15	12/29/1987	0.355903	0.381944	0.350694	0.378472	0.236128	79459200		1/0/1900	0
16	12/30/1987	0.378472	0.388889	0.376736	0.387153	0.241544	70704000		1/0/1900	0
17	12/31/1987	0.383681	0.387153	0.373264	0.376736	0.235045	61545600		1/0/1900	0
18	1/4/1988	0.380208	0.392361	0.378472	0.388889	0.242627	1.1E+08		1/0/1900	0
19	1/5/1988	0.397569	0.402778	0.392361	0.395833	0.24696	1.56E+08		1/0/1900	0
20	1/6/1988	0.397569	0.414931	0.395833	0.40625	0.253459	1.22E+08		1/0/1900	0
21	1/7/1988	0.402778	0.421007	0.399306	0.420139	0.262124	90676800		1/0/1900	0
22	1/8/1988	0.418403	0.428819	0.386285	0.388889	0.242627	1.45E+08		1/0/1900	0
23	1/11/1988	0.387153	0.402778	0.380208	0.40191	0.250751	1.44E+08		1/0/1900	0
24	12/22/1988	0.364583	0.368056	0.359375	0.361111	0.225297	74592000		1/0/1900	0
25	12/23/1988	0.359375	0.361111	0.357639	0.361111	0.225297	17121600		1/0/1900	0
26	12/27/1988	0.361111	0.364583	0.359375	0.359375	0.224213	30772800		1/0/1900	0
27	12/28/1988	0.361111	0.362847	0.359375	0.362847	0.22638	21268800		1/0/1900	0
28	12/29/1988	0.361111	0.371528	0.361111	0.369792	0.230713	38275200		1/0/1900	0
29	12/30/1988	0.371528	0.376736	0.369792	0.369792	0.230713	63820800		1/0/1900	0
30	1/3/1989	0.371528	0.373264	0.366319	0.372396	0.232337	51825600		1/0/1900	0
31	1/4/1989	0.371528	0.381076	0.371528	0.376736	0.235045	53899200		1/0/1900	0
32									1/0/1900	0
Sheet1

Cell Formulas
Range	Formula
I2:I1048551	I2	=FILTER(A:A,(MONTH(A:A)=1)*(DAY(A:A)<=4),"")
J2:J1048551	J2	=FILTER(E:E,(MONTH(A:A)=1)*(DAY(A:A)<=4))
L3	L3	=FILTER(E:E,(MONTH(A:A)=1)*(DAY(A:A)=2),(DAY(A:A)=3))
Dynamic array formulas.