excel_stat_mutation_functions: Excel Statistical Mutation Functions

In tidyquant: Tidy Quantitative Financial Analysis

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)

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.

Value

• Mutation functions return a mutated / transformed version of the vector

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

# 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
    )

tidyquant documentation built on Sept. 11, 2024, 7:29 p.m.