# excel_stat_mutation_functions: Excel Statistical Mutation Functions In tidyquant: Tidy Quantitative Financial Analysis

 excel_stat_mutation_functions R Documentation

## 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(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",
) %>%

# 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 April 3, 2023, 5:13 p.m.