Learn R Programming

tidyquant (version 1.0.9)

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(timetk, exclude = "FANG")
library(dplyr)

# --- 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 = forcats::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
    )

Run the code above in your browser using DataLab