excel_ref_functions: Excel Reference Functions

In tidyquant: Tidy Quantitative Financial Analysis

Excel Reference Functions

Description

Excel reference functions are used to efficiently lookup values from a data source. The most popular lookup function is "VLOOKUP", which has been implemented in R.

These functions are designed to help users coming from an Excel background. Most functions replicate the behavior of Excel:

• Names are similar to Excel function names

• Functionality replicates Excel

Usage

VLOOKUP(.lookup_values, .data, .lookup_column, .return_column)

Arguments

.lookup_values	One or more lookup values.
.data	A data.frame or tibble that contains values to evaluate and return
.lookup_column	The column in .data containing exact matching values of the .lookup_values
.return_column	The column in .data containing the values to return if a match is found

Details

VLOOKUP() Details

• Performs exact matching only. Fuzzy matching is not implemented.

• Can only return values from one column only. Use dplyr::left_join() to perform table joining.

Value

Returns a vector the length of the input lookup values

Examples

library(dplyr)

lookup_table <- tibble(
    stock   = c("META", "AMZN", "NFLX", "GOOG"),
    company = c("Facebook", "Amazon", "Netflix", "Google")
)

# --- Basic Usage ---

VLOOKUP("NFLX",
        .data = lookup_table,
        .lookup_column = stock,
        .return_column = company)

# --- Usage with tidyverse ---

# Add company names to the stock data
FANG %>%
    mutate(company = VLOOKUP(symbol, lookup_table, stock, company))

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