PQ Function fxMakeCalendar - Generate calendar of days Market is Closed

[jdellasala]

I found this site - https://www.mypivots.com/market-holidays/united-states/ where you can select any year and get a list of days the market is closed, and wrote a function I call fxMakeCalendar to retrieve those dates for any year:

( YearToGet as text ) =>
let
    Source = Web.Page(Web.Contents( "https://www.mypivots.com/market-holidays/united-states/" & YearToGet )),
    HolCal = Source{0}[Data],
    ChangedType = Table.TransformColumnTypes( HolCal,{{"Date", type date}, {"Holiday", type text}})
in
    ChangedType

The easy way to make this function available to any Workbook is to right click on it in the Data -> Queries & Connections list and select Export Connection File... You will be prompted to save the file in the My Data Sources folder under whatever your system has designated as your Documents folder. The file as presented here will save with the file name Query - fxMakeCalendar.odc. After it's saved, it can be brought into any Workbook by using Data -> Existing Connections, and it will be listed under Connection files on this computer.
Using this, it's easy enough to generate a list of Market Holidays for multiple years:

let
    Source = {1900..2100},
    ConvertedToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    ChangedType = Table.TransformColumnTypes(ConvertedToTable,{{"Column1", type text}}),
    InvokedFxMakeCalendar = Table.AddColumn(ChangedType, "Calendars", each fxMakeCalendar([Column1])),
    RemovedColumns = Table.RemoveColumns(InvokedFxMakeCalendar,{"Column1"}),
    ExpandedCalendars = Table.ExpandTableColumn(RemovedColumns, "Calendars", {"Date", "Holiday"}, {"Date", "Holiday"}),
    ChangedType1 = Table.TransformColumnTypes(ExpandedCalendars,{{"Date", type date}, {"Holiday", type text}}),
    InsertedDayName = Table.AddColumn(ChangedType1, "Day Name", each Date.DayOfWeekName([Date]), type text),
    InsertedDay = Table.AddColumn(InsertedDayName, "Day", each Date.Day([Date]), Int64.Type),
    InsertedYear = Table.AddColumn(InsertedDay, "Year", each Date.Year([Date]), Int64.Type),
    SortedRows = Table.Sort(InsertedYear,{{"Date", Order.Ascending}}),
    ReorderedColumns = Table.ReorderColumns(SortedRows,{"Holiday", "Date", "Day Name", "Day", "Year"})
in
    ReorderedColumns

This can be helpful when using the .INTL versions of WORKDAY and NETWORKDAYS!
There is also the LAMBDA function T_US_HOLIDAY_CALC by Xlambda, but it doesn't include Good Friday which is a Market Holiday.

 

[jdellasala]

Last week I posted a Power Query function that lists all of the Market Closed dates for a given year. However I have since found through both historical data as well as the STOCKHISTORY function that the Markets were NOT closed for MLK day until 1998 in spite of it having been signed into law as a Federal Holiday in 1986. The following compensates for the web stie's error. I also updated the parameter to allow any value to be accepted rather than just text. No error checking is done to confirm the date requested is earlier than the site allows, or that Excel allows (1/1/1900), or some non-whole number regardless of type.

( YearToGet as any ) =>
let
    Source = Web.Page(Web.Contents( "https://www.mypivots.com/market-holidays/united-states/" & Text.From( YearToGet ) )),
    HolCal = Source{0}[Data],
    ChangedType = Table.TransformColumnTypes( HolCal,{{"Date", type date}, {"Holiday", type text}}),
//  Historic Data says the Market was open on MLK day until 1998, hence the fix:
    CalFix = if Date.Year( ChangedType{1}[Date] ) < 1998 and ChangedType{1}[Holiday] = "Martin Luther King Day" then
       Table.SelectRows(ChangedType, each ([Holiday] <> "Martin Luther King Day")) else ChangedType
in
    CalFix