MKTDAYS(StrtDt, Hols, [LstDt])
StrtDt
Starting Date. Can be provided as a quoted string or a date value.
Hols
A column of Holiday (Market Closed) dates to be omitted from the list of dates.
[LstDt]
OPTIONAL Last date to be listed. If omitted, TODAY()-1 is used.

Provides a list of dates that omits Fridays, Saturdays, and the dates provided by the Hols list of dates. Intended for use with the STOCKHISTORY function.

jdellasala

Well-known Member
Joined
Dec 11, 2020
Messages
751
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
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:
Excel Formula:
=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
ABCD
1NewYear's Day01/02/202301/03/2023
2Martin Luther King Day01/16/202301/04/2023
3President's Day02/20/202301/05/2023
4Memorial Day05/29/202301/06/2023
5Fourth of July07/04/202301/09/2023
6Labor Day09/04/202301/10/2023
7Columbus Day10/09/202301/11/2023
8Veterans Day11/10/202301/12/2023
9Thanksgiving Day11/23/202301/13/2023
10Christmas Day12/25/202301/17/2023
1101/18/2023
1201/19/2023
1301/20/2023
1401/23/2023
1501/24/2023
1601/25/2023
1701/26/2023
1801/27/2023
1901/30/2023
2001/31/2023
2102/01/2023
2202/02/2023
2302/03/2023
2402/06/2023
2502/07/2023
2602/08/2023
2702/09/2023
2802/10/2023
2902/13/2023
3002/14/2023
3102/15/2023
3202/16/2023
3302/17/2023
3402/21/2023
3502/22/2023
3602/23/2023
3702/24/2023
3802/27/2023
3902/28/2023
4003/01/2023
4103/02/2023
4203/03/2023
4303/06/2023
4403/07/2023
4503/08/2023
4603/09/2023
4703/10/2023
Sheet2
Cell Formulas
RangeFormula
A1:B10A1=MARKETCAL()
D1:D47D1=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!
 
Upvote 0
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.
 
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!
 
Just Don. I am not a doctor, although that's what the ladies believe on Friday nights.
 
@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: 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.
 
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!
 
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
ABC
1Sun 01-Jan-2367
2Tue 03-Jan-23Tue 03-Jan-23
3Wed 04-Jan-23Wed 04-Jan-23
4Thu 05-Jan-23Thu 05-Jan-23
5Fri 06-Jan-23Fri 06-Jan-23
6Mon 09-Jan-23Mon 09-Jan-23
2b
Cell Formulas
RangeFormula
C1C1=NETWORKDAYS.INTL(A1,TODAY(),1,Holidays)
A2:A68A2=WORKDAY.INTL(A1,SEQUENCE(67,,1,1),1,Holidays)
B2:B68B2=WORKDAY.INTL(A1,SEQUENCE(NETWORKDAYS.INTL(A1,TODAY(),1,Holidays),,1,1),1,Holidays)
Dynamic array formulas.
Named Ranges
NameRefers ToCells
Holidays='2b'!$M$2:$M$4C1, A2:B2
 

Forum statistics

Threads
1,216,503
Messages
6,131,022
Members
449,616
Latest member
PsychoCube

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top