excel_date_functions {tidyquant}

Excel Date and Time Functions

Description

50+ date and time functions familiar to users coming from an Excel Background. The main benefits are:

1. Integration of the amazing lubridate package for handling dates and times

2. Integration of Holidays from timeDate and Business Calendars

3. New Date Math and Date Sequence Functions that factor in Business Calendars (e.g. EOMONTH(), NET_WORKDAYS())

These functions are designed to help users coming from an Excel background. Most functions replicate the behavior of Excel:

• Names in most cases match Excel function names

• Functionality replicates Excel

• By default, missing values are ignored (same as in Excel)

Usage

AS_DATE(x, ...)

AS_DATETIME(x, ...)

DATE(year, month, day)

DATEVALUE(x, ...)

YMD(x, ...)

MDY(x, ...)

DMY(x, ...)

YMD_HMS(x, ...)

MDY_HMS(x, ...)

DMY_HMS(x, ...)

YMD_HM(x, ...)

MDY_HM(x, ...)

DMY_HM(x, ...)

YMD_H(x, ...)

MDY_H(x, ...)

DMY_H(x, ...)

WEEKDAY(x, ..., label = FALSE, abbr = TRUE)

WDAY(x, ..., label = FALSE, abbr = TRUE)

DOW(x, ..., label = FALSE, abbr = TRUE)

MONTHDAY(x, ...)

MDAY(x, ...)

DOM(x, ...)

QUARTERDAY(x, ...)

QDAY(x, ...)

DAY(x, ...)

WEEKNUM(x, ...)

WEEK(x, ...)

WEEKNUM_ISO(x, ...)

MONTH(x, ..., label = FALSE, abbr = TRUE)

QUARTER(x, ..., include_year = FALSE, fiscal_start = 1)

YEAR(x, ...)

YEAR_ISO(x, ...)

DATE_TO_NUMERIC(x, ...)

DATE_TO_DECIMAL(x, ...)

SECOND(x, ...)

MINUTE(x, ...)

HOUR(x, ...)

NOW(...)

TODAY(...)

EOMONTH(start_date, months = 0)

EDATE(start_date, months = 0)

NET_WORKDAYS(start_date, end_date, remove_weekends = TRUE, holidays = NULL)

COUNT_DAYS(start_date, end_date)

YEARFRAC(start_date, end_date)

DATE_SEQUENCE(start_date, end_date, by = "day")

WORKDAY_SEQUENCE(start_date, end_date, remove_weekends = TRUE, holidays = NULL)

HOLIDAY_SEQUENCE(
  start_date,
  end_date,
  calendar = c("NYSE", "LONDON", "NERC", "TSX", "ZURICH")
)

HOLIDAY_TABLE(years, pattern = ".")

FLOOR_DATE(x, ..., by = "day")

FLOOR_DAY(x, ...)

FLOOR_WEEK(x, ...)

FLOOR_MONTH(x, ...)

FLOOR_QUARTER(x, ...)

FLOOR_YEAR(x, ...)

CEILING_DATE(x, ..., by = "day")

CEILING_DAY(x, ...)

CEILING_WEEK(x, ...)

CEILING_MONTH(x, ...)

CEILING_QUARTER(x, ...)

CEILING_YEAR(x, ...)

ROUND_DATE(x, ..., by = "day")

ROUND_DAY(x, ...)

ROUND_WEEK(x, ...)

ROUND_MONTH(x, ...)

ROUND_QUARTER(x, ...)

ROUND_YEAR(x, ...)

Arguments

x

A vector of date or date-time objects

...

Parameters passed to underlying lubridate functions.

year

Used in DATE()

month

Used in DATE()

day

Used in DATE()

label

A logical used for MONTH() and WEEKDAY() Date Extractors to decide whether or not to return names (as ordered factors) or numeric values.

abbr

A logical used for MONTH() and WEEKDAY(). If label = TRUE, used to determine if full names (e.g. Wednesday) or abbreviated names (e.g. Wed) should be returned.

include_year

A logicial value used in QUARTER(). Determines whether or not to return 2020 Q3 as 3 or 2020.3.

fiscal_start

A numeric value used in QUARTER(). Determines the fiscal-year starting quarter.

start_date

Used in Date Math and Date Sequence operations. The starting date in the calculation.

months

Used to offset months in EOMONTH() AND EDATE() Date Math calculations

end_date

Used in Date Math and Date Sequence operations. The ending date in the calculation.

remove_weekends

A logical value used in Date Sequence and Date Math calculations. Indicates whether or not weekends should be removed from the calculation.

holidays

A vector of dates corresponding to holidays that should be removed from the calculation.

by

Used to determine the gap in Date Sequence calculations and value to round to in Date Collapsing operations. Acceptable values are: A character string, containing one of "day", "week", "month", "quarter" or "year".

calendar

The calendar to be used in Date Sequence calculations for Holidays from the timeDate package. Acceptable values are: "NYSE", "LONDON", "NERC", "TSX", "ZURICH"

years

A numeric vector of years to return Holidays for in HOLIDAY_TABLE()

pattern

Used to filter Holidays (e.g. pattern = "Easter"). A "regular expression" filtering pattern.

Details

Converters - Make date and date-time from text (character data)

• General String-to-Date Conversion: AS_DATE(), AS_DATETIME()

• Format-Specific String-to-Date Conversion: YMD() (YYYY-MM-DD), MDY() (MM-DD-YYYY), DMY() (DD-MM-YYYY)

• Hour-Minute-Second Conversion: YMD_HMS(), YMD_HM(), and friends.

Extractors - Returns information from a time-stamp.

• Extractors: SECOND(), MINUTE(), HOUR(), DAY(), WEEK(), MONTH(), QUARTER(), YEAR()

Current Time - Returns the current date/date-time based on your locale.

• NOW(), TODAY()

Date Math - Perform popular Excel date calculations

• EOMONTH() - End of Month

• NET_WORKDAYS(), COUNT_DAYS() - Return number of days between 2 dates factoring in working days and holidays

• YEARFRAC() - Return the fractional period of the year that has been completed between 2 dates.

Date Sequences - Return a vector of dates or a Holiday Table (tibble).

• DATE_SEQUENCE(), WORKDAY_SEQUENCE(), HOLIDAY_SEQUENCE - Return a sequence of dates between 2 dates that factor in workdays and timeDate holiday calendars for popular business calendars including NYSE and London stock exchange.

Date Collapsers - Collapse a date sequence (useful in dplyr::group_by() and pivot_table())

• FLOOR_DATE(), FLOOR_DAY(), FLOOR_WEEK(), FLOOR_MONTH(), FLOOR_QUARTER(), FLOOR_YEAR()

• Similar functions exist for CEILING and ROUND. These are wrappers for lubridate functions.

Value

• Converters - Date or date-time object the length of x

• Extractors - Returns information from a time-stamp.

• Current Time - Returns the current date/date-time based on your locale.

• Date Math - Numeric values or Date Values depending on the calculation.

• Date Sequences - Return a vector of dates or a Holiday Table (tibble).

• Date Collapsers - Date or date-time object the length of x

Examples

# Libraries
library(tidyquant)
library(tidyverse)
library(lubridate)

# --- Basic Usage ----

# Converters ---
AS_DATE("2011 Jan-01") # General
YMD("2011 Jan-01")     # Year, Month-Day Format
MDY("01-02-20")        # Month-Day, Year Format (January 2nd, 2020)
DMY("01-02-20")        # Day-Month, Year Format (February 1st, 2020)

# Extractors ---
WEEKDAY("2020-01-01")                                  # Labelled Day
WEEKDAY("2020-01-01", label = FALSE)                   # Numeric Day
WEEKDAY("2020-01-01", label = FALSE, week_start = 1)   # Start at 1 (Monday) vs 7 (Sunday)
MONTH("2020-01-01")
QUARTER("2020-01-01")
YEAR("2020-01-01")

# Current Date-Time ---
NOW()
TODAY()

# Date Math ---
EOMONTH("2020-01-01")
EOMONTH("2020-01-01", months = 1)
NET_WORKDAYS("2020-01-01", "2020-07-01") # 131 Skipping Weekends
NET_WORKDAYS("2020-01-01", "2020-07-01",
             holidays = HOLIDAY_SEQUENCE("2020-01-01", "2020-07-01",
                                         calendar = "NYSE")) # 126 Skipping 5 NYSE Holidays

# Date Sequences ---
DATE_SEQUENCE("2020-01-01", "2020-07-01")
WORKDAY_SEQUENCE("2020-01-01", "2020-07-01")
HOLIDAY_SEQUENCE("2020-01-01", "2020-07-01", calendar = "NYSE")
WORKDAY_SEQUENCE("2020-01-01", "2020-07-01",
                 holidays = HOLIDAY_SEQUENCE("2020-01-01", "2020-07-01",
                                             calendar = "NYSE"))

# Date Collapsers ---
FLOOR_DATE(AS_DATE("2020-01-15"), by = "month")
CEILING_DATE(AS_DATE("2020-01-15"), by = "month")
CEILING_DATE(AS_DATE("2020-01-15"), by = "month") - ddays(1) # EOMONTH using lubridate

# --- Usage with tidyverse ---

# Calculate returns by symbol/year/quarter
FANG %>%
    pivot_table(
        .rows       = c(symbol, ~ QUARTER(date)),
        .columns    = ~ YEAR(date),
        .values     = ~ PCT_CHANGE_FIRSTLAST(adjusted)
    )