jdellasala
Well-known Member
- Joined
- Dec 11, 2020
- Messages
- 751
- Office Version
- 365
- Platform
- Windows
- Mobile
- 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:
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:
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!
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))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. | ||
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
