MKTDAYS

[jdellasala]

MKTDAYS is the second of two posts intended to alleviate the problem with the STOCKHISTORY function where it returns an error when attempting to provide information for an invalid date as reported by @DRSteele .

The first post was MARKETCAL which can be used to provide the required array of dates the market is closed.

Initially I was going to make the optional LstDt (Last Date) parameter forced to be equal to TODAY()-1, however it occurred to me that someone might want to set a Last Date in the future and use IFERROR to prevent STOCKHISTORY from displaying an error. However, it's worth noting that since the parameter is optional and defaults to TODAY()-1, the function is fully dynamic and will provide the needed array of dates up until yesterday which are all the valid dates since STOCKHISTORY barfs when TODAY() is used!

So here's the code:

=LAMBDA( StrtDt, Hols,[LstDt], LET(
EndDt, IF(ISOMITTED(LstDt), TODAY() - 1, LstDt), 
CalFul, SEQUENCE(DATE(YEAR(EndDt), MONTH(EndDt), DAY(EndDt)) - DATE(YEAR(StrtDt), MONTH(StrtDt), DAY(StrtDt)) + 1, , DATE(YEAR(StrtDt), MONTH(StrtDt), DAY(StrtDt))), 
WkCal, FILTER(CalFul, WEEKDAY(CalFul, 2) < 6), 
HolFlg, IF(ISERROR(MATCH(WkCal, Hols, 0)), 1, 0), 
MktCal, FILTER(WkCal, HolFlg), MktCal))

The foundation of the function is the SEQUENCE of dates from the Start Date to the Last Date with the number of dates determined by Last Date - Start Date + 1.

That array is then filtered to omit Saturdays and Sundays using WEEKDAY with the 2 option for Saturdays to equal 6 and Sundays to make it easier to FILTER the full list of dates.

A new array is then built to create a list of 0's for dates to be omitted and 1's for dates to be included from the no weekends list of dates.

That array is then used to FILTER the No Weekends list to get the final list of valid Market Days.

Generating that list for the current year results in this:

MarketCalendar.xlsx
	A	B	C	D
1	NewYear's Day	01/02/2023		01/03/2023
2	Martin Luther King Day	01/16/2023		01/04/2023
3	President's Day	02/20/2023		01/05/2023
4	Memorial Day	05/29/2023		01/06/2023
5	Fourth of July	07/04/2023		01/09/2023
6	Labor Day	09/04/2023		01/10/2023
7	Columbus Day	10/09/2023		01/11/2023
8	Veterans Day	11/10/2023		01/12/2023
9	Thanksgiving Day	11/23/2023		01/13/2023
10	Christmas Day	12/25/2023		01/17/2023
11				01/18/2023
12				01/19/2023
13				01/20/2023
14				01/23/2023
15				01/24/2023
16				01/25/2023
17				01/26/2023
18				01/27/2023
19				01/30/2023
20				01/31/2023
21				02/01/2023
22				02/02/2023
23				02/03/2023
24				02/06/2023
25				02/07/2023
26				02/08/2023
27				02/09/2023
28				02/10/2023
29				02/13/2023
30				02/14/2023
31				02/15/2023
32				02/16/2023
33				02/17/2023
34				02/21/2023
35				02/22/2023
36				02/23/2023
37				02/24/2023
38				02/27/2023
39				02/28/2023
40				03/01/2023
41				03/02/2023
42				03/03/2023
43				03/06/2023
44				03/07/2023
45				03/08/2023
46				03/09/2023
47				03/10/2023
Sheet2

Cell Formulas
Range	Formula
A1:B10	A1	=MARKETCAL()
D1:D47	D1	=MKTDAYS("1/1/2023",B1:B10)
Dynamic array formulas.

The last date in the array won't change until next Tuesday since Monday - 1 would provide a Sunday which would be filtered out. Of course that would be overridden if a last date in the future is provided, but STOCKDATA will kick back an error for any valid date before yesterday.

Note that the Start Date is provided as a quoted string above, but can also be a function that returns a date like DATE, or a cell that contains a Date value.

At the Excel Community Feedback portal which Microsoft uses to get enhancement ideas, I posted a thread over a year ago asking that STOCKDATA not return an error when only dates are requested which it still does. If the function allowed requesting data on any stock for any valid dates, these two LAMBDA functions wouldn't be necessary. Sadly it's only gotten one vote - MINE! Feel free to visit the option and vote for it if you think that would be a LOT easier than needing two LAMBDA functions!

Otherwise, hope some of you find this useful!

 

[DRSteele]

These are well conceived ideas here, Jerry. Thanks for mentioning my Article.

One problem with the holiday technique, as you may imagine, is that there are other countries that have different holidays. This especially affects Canadians, who either own cross-border assets in USA or surely use reference data in the form of commodities or indexes centered in USA. So then the dates still won't line up across a long list of securities in the columns. The only solution therefore is to have all the calendar dates and fill in the holes with the prior data. I hope Microsoft remedies the situation.

The Excel Community thread is full of good ideas nobody votes on, and they languish. I too am such a victim.

 

[jdellasala]

These are well conceived ideas here, Jerry. Thanks for mentioning my Article.

One problem with the holiday technique, as you may imagine, is that there are other countries that have different holidays. This especially affects Canadians, who either own cross-border assets in USA or surely use reference data in the form of commodities or indexes centered in USA. So then the dates still won't line up across a long list of securities in the columns. The only solution therefore is to have all the calendar dates and fill in the holes with the prior data. I hope Microsoft remedies the situation.

The Excel Community thread is full of good ideas nobody votes on, and they languish. I too am such a victim.

Thanks Dr. That said, the MARKETCAL function can be easily reworked for other dates. If not, the MKTDAYS function just needs a list of Holidays, just like the .INTL functions and then can be used for whatever market!

 

[DRSteele]

Just Don. I am not a doctor, although that's what the ladies believe on Friday nights.

 

[smozgur]

@jdellasala: I updated two recent Lambda function threads that you posted to remove the description from the title. Please use only the function name in the title in your future Lambda functions.

 

[jdellasala]

@jdellasala: I updated two recent Lambda function threads that you posted to remove the description from the title. Please use only the function name in the title in your future Lambda functions.

OK. Sorry about that.

 

[smozgur]

Sorry about that.

No problem.

 

[jdellasala]

NOTE: I realized that MARKETCAL had a LOT of problems. My apologies for posting before checking it out thoroughly! I asked to have the post removed, and the link in the original post no longer works.
I am working on a replacement which I'll post as MARKETCAL2 as soon as I check it out fully!

 

[Dave Patton]

N.B. I have not used StockHistory and I did not analyze the requirements or your suggestion.

Would nested functions not provide the list of relevant days?

T202304a.xlsm
	A	B	C
1	Sun 01-Jan-23		67
2	Tue 03-Jan-23	Tue 03-Jan-23
3	Wed 04-Jan-23	Wed 04-Jan-23
4	Thu 05-Jan-23	Thu 05-Jan-23
5	Fri 06-Jan-23	Fri 06-Jan-23
6	Mon 09-Jan-23	Mon 09-Jan-23
2b

Cell Formulas
Range	Formula
C1	C1	=NETWORKDAYS.INTL(A1,TODAY(),1,Holidays)
A2:A68	A2	=WORKDAY.INTL(A1,SEQUENCE(67,,1,1),1,Holidays)
B2:B68	B2	=WORKDAY.INTL(A1,SEQUENCE(NETWORKDAYS.INTL(A1,TODAY(),1,Holidays),,1,1),1,Holidays)
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
Holidays	='2b'!$M$2:$M$4	C1, A2:B2