excel_stat_mutation_functions: Excel Statistical Mutation Functions

Description

15+ common statistical functions familiar to users of Excel (e.g. ABS(), SQRT()) that modify / transform a series of values (i.e. a vector of the same length of the input is returned).

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

ABS(x)
SQRT(x)
LOG(x)
EXP(x)
RETURN(x, n = 1, fill_na = NA)
PCT_CHANGE(x, n = 1, fill_na = NA)
CHANGE(x, n = 1, fill_na = NA)
LAG(x, n = 1, fill_na = NA)
LEAD(x, n = 1, fill_na = NA)
CUMULATIVE_SUM(x)
CUMULATIVE_PRODUCT(x)
CUMULATIVE_MAX(x)
CUMULATIVE_MIN(x)
CUMULATIVE_MEAN(x)
CUMULATIVE_MEDIAN(x)

Value

Mutation functions return a mutated / transformed version of the vector

Arguments

x: A vector. Most functions are designed for numeric data.
n: Values to offset. Used in functions like LAG(), LEAD(), and PCT_CHANGE()
fill_na: Fill missing (NA) values with a different value. Used in offsetting functions.

Useful functions

Mutation Functions - Transforms a vector

Transformation: ABS(), SQRT(), LOG(), EXP()
Lags & Change (Offsetting Functions): CHANGE(), PCT_CHANGE(), LAG(), LEAD()
Cumulative Totals: CUMULATIVE_SUM(), CUMULATIVE_PRODUCT()

Examples

Run this code

# Libraries
library(tidyquant)
library(timetk)
library(tidyverse)
library(forcats)

# --- Basic Usage ----

CUMULATIVE_SUM(1:10)

PCT_CHANGE(c(21, 24, 22, 25), fill_na = 0)

# --- Usage with tidyverse ---

# Go from daily to monthly periodicity,
# then calculate returns and growth of $1 USD
FANG %>%
    mutate(symbol = as_factor(symbol)) %>%
    group_by(symbol) %>%

    # Summarization - Collapse from daily to FIRST value by month
    summarise_by_time(
        .date_var  = date,
        .by        = "month",
        adjusted   = FIRST(adjusted)
    ) %>%

    # Mutation - Calculate monthly returns and cumulative growth of $1 USD
    group_by(symbol) %>%
    mutate(
        returns = PCT_CHANGE(adjusted, fill_na = 0),
        growth  = CUMULATIVE_SUM(returns) + 1
    )