Excel Statistical Mutation Functions
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)
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)
x |
A vector. Most functions are designed for numeric data. |
n |
Values to offset. Used in functions like |
fill_na |
Fill missing ( |
Mutation functions return a mutated / transformed version of the vector
Mutation Functions - Transforms a vector
Lags & Change (Offsetting Functions): CHANGE(), PCT_CHANGE(), LAG(), LEAD()
Cumulative Totals: CUMULATIVE_SUM(), CUMULATIVE_PRODUCT()
# 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
)Please choose more modern alternatives, such as Google Chrome or Mozilla Firefox.