excel_stat_summary_functions: Excel Statistical Summary Functions In tidyquant: Tidy Quantitative Financial Analysis

 excel_stat_summary_functions R Documentation

Excel Statistical Summary Functions

Description

15+ common statistical functions familiar to users of Excel (e.g. `SUM()`, `AVERAGE()`). These functions return a single value (i.e. a vector of length 1).

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

``````SUM(x)

AVERAGE(x)

MEDIAN(x)

MIN(x)

MAX(x)

COUNT(x)

COUNT_UNIQUE(x)

STDEV(x)

VAR(x)

COR(x, y)

COV(x, y)

FIRST(x)

LAST(x)

NTH(x, n = 1)

CHANGE_FIRSTLAST(x)

PCT_CHANGE_FIRSTLAST(x)
``````

Arguments

 `x` A vector. Most functions are designed for numeric data. Some functions like `COUNT()` handle multiple data types. `y` A vector. Used in functions requiring 2 inputs. `n` A single value used in `NTH()` to select a specific element location to return.

Details

Summary Functions

• All functions remove missing values (`NA`). This is the same behavior as in Excel and most commonly what is desired.

Value

• Summary functions return a single value

Useful functions

Summary Functions - Return a single value from a vector

• Sum: `SUM()`

• Center: `AVERAGE()`, `MEDIAN()`

• Spread: `STDEV()`, `VAR()`

• Range: `MIN()`, `MAX()`

• Count: `COUNT()`, `COUNT_UNIQUE()`

• Position: `FIRST()`, `LAST()`, `NTH()`

• Change (Summary): `CHANGE_FIRSTLAST()`, `PCT_CHANGE_FIRSTLAST()`

• Correlation: `COR()`, `COV()`

Examples

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

# --- Basic Usage ----

SUM(1:10)

PCT_CHANGE_FIRSTLAST(c(21, 24, 22, 25))

# --- 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",