Time-Series Window Functions
Chanice McKenzie
Introduction
The dplyr article Window
functions explains how window functions perform calculations
across related rows while preserving one output for each original
observation. This analysis applies cumulative and offset window
functions to daily Canadian dollar, Mexican peso, and Chinese yuan
exchange rates relative to the U.S. dollar. The business question is:
How does each current exchange rate compare with its broader
year-to-date level and its more recent six-observation
trend?
Analysis
Data source and workflow
The attached workbook was generated with assistance from an LLM, which organized three publicly available Federal Reserve Economic Data (FRED) series into a single long-format dataset. To make the analysis reproducible in another environment, the code rebuilds the dataset directly from the public FRED URLs rather than reading a local file. The date range is fixed from January 1, 2022 through September 11, 2026 to match the submitted workbook.
The business question is translated into the following data question: For each currency and observation date, what are the average exchange rate since the beginning of that calendar year and the average of the six most recent reported observations?
Planned Approach
I plan to complete the analysis in R with a dplyr-based
workflow. I will first import the dataset and confirm that the
Date field is stored as a date, the Value
field is numeric, and each observation has a valid item and unit. I will
then arrange the observations chronologically within each item and
create a year variable from the date.
The year-to-date average will be calculated separately for each item and year. For every date, the calculation will include all available observations from the beginning of that calendar year through the current observation. Grouping by both item and year is important because the cumulative average must restart each January rather than continue across the full dataset.
The six-day moving average will be calculated separately for each item after the observations are ordered by date. Each result will use the current value and the five preceding available daily observations. For the first five observations in each series, I will retain missing values until a complete six-observation window is available. This will prevent partial windows from being presented as comparable six-day averages.
After creating both measures, I will verify that the calculations do not cross between currencies, that the year-to-date average resets at the start of every year, and that each moving-average window contains six observations. The final dataset will retain the original fields and add the year-to-date average and six-day moving average as new columns.
Anticipated Data Challenges
The largest challenge is that the dataset contains business-day observations rather than a value for every calendar day. Weekends, holidays, and unavailable observations create gaps between dates. Therefore, a six-row window represents the six most recent reported observations, which may cover more than six calendar days. I will describe this interpretation in the final analysis. If the assignment requires six consecutive calendar days instead, the data would first need to be expanded to a complete daily calendar and a decision would be needed about how to handle missing dates.
I will also check for duplicate item-date combinations. Missing exchange-rate observations will be removed rather than converted to zero because zero is not a valid exchange rate and would distort the window calculations. In addition, the three items have different numerical ranges, so their raw values should not be interpreted as directly comparable magnitudes even though they share the general format of foreign currency units per U.S. dollar.
The year-to-date average will initially be based on only a small number of observations, while the moving average will not be available until six valid observations have accumulated. These early values will be reviewed before the results are summarized or visualized.
# Packages used for data manipulation and table formatting.
library(dplyr)
library(knitr)
library(ggplot2)
library(tidyr)
# Information needed to download and label the three FRED series.
series_information <- data.frame(
Series = c("DEXCAUS", "DEXMXUS", "DEXCHUS"),
Item = c(
"Canadian dollar per U.S. dollar",
"Mexican peso per U.S. dollar",
"Chinese yuan per U.S. dollar"
),
Unit = c("CAD per USD", "MXN per USD", "CNY per USD"),
stringsAsFactors = FALSE
)
# This function downloads one series and gives it consistent column names.
download_fx_series <- function(series_code, item_name, unit_name) {
data_url <- paste0(
"https://fred.stlouisfed.org/graph/fredgraph.csv?id=",
series_code,
"&cosd=2022-01-01&coed=2026-09-11"
)
one_series <- read.csv(data_url, na.strings = ".")
names(one_series) <- c("Date", "Value")
one_series %>%
mutate(
Date = as.Date(Date),
Value = as.numeric(Value),
Item = item_name,
Unit = unit_name
) %>%
filter(!is.na(Value)) %>%
select(Date, Item, Value, Unit)
}
# Download each currency and combine the results into one data frame.
fx_list <- lapply(seq_len(nrow(series_information)), function(i) {
download_fx_series(
series_information$Series[i],
series_information$Item[i],
series_information$Unit[i]
)
})
fx_data <- bind_rows(fx_list)Check and summarize the data
Before calculating the windows, I confirm that every row has a valid date, item, unit, and positive numeric value. I also check that an item does not have more than one observation on the same date. Missing source observations are excluded rather than converted to zero because zero is not a meaningful exchange rate.
# Stop the analysis if required values are missing or invalid.
if (any(is.na(fx_data$Date)) ||
any(is.na(fx_data$Item)) ||
any(is.na(fx_data$Unit)) ||
any(is.na(fx_data$Value)) ||
any(fx_data$Value <= 0)) {
stop("The dataset contains a missing or invalid required value.")
}
# Check for repeated item-date combinations.
duplicate_rows <- fx_data %>%
count(Item, Date) %>%
filter(n > 1)
if (nrow(duplicate_rows) > 0) {
stop("The dataset contains duplicate item-date combinations.")
}
# Summarize the number and range of observations for each currency.
data_summary <- fx_data %>%
group_by(Item, Unit) %>%
summarise(
Observations = n(),
First_Date = min(Date),
Last_Date = max(Date),
.groups = "drop"
)
kable(data_summary, caption = "Coverage of the foreign exchange dataset")| Item | Unit | Observations | First_Date | Last_Date |
|---|---|---|---|---|
| Canadian dollar per U.S. dollar | CAD per USD | 1175 | 2022-01-03 | 2026-09-11 |
| Chinese yuan per U.S. dollar | CNY per USD | 1175 | 2022-01-03 | 2026-09-11 |
| Mexican peso per U.S. dollar | MXN per USD | 1175 | 2022-01-03 | 2026-09-11 |
The dataset contains three separate currency series. Weekends, holidays, and unavailable observations are omitted, so the six-day calculation below represents six reported business-day observations rather than six consecutive calendar days.
Calculate the window functions
The year-to-date average uses cummean(), a cumulative
window function. Grouping by both Item and
Year ensures that the calculation restarts with the first
available observation each January.
The six-day moving average uses lag(), an offset window
function. For each currency, the calculation averages the current value
and the five preceding reported values. The first five rows for each
currency remain missing because a complete six-observation window is not
yet available.
fx_windowed <- fx_data %>%
# Window calculations require the observations to be in time order.
arrange(Item, Date) %>%
mutate(Year = as.integer(format(Date, "%Y"))) %>%
# The YTD average restarts for every item at the beginning of each year.
group_by(Item, Year) %>%
mutate(
YTD_Average = cummean(Value)
) %>%
ungroup() %>%
# The moving average stays within each currency series.
group_by(Item) %>%
mutate(
Six_Day_Moving_Average = (
Value +
lag(Value, 1) +
lag(Value, 2) +
lag(Value, 3) +
lag(Value, 4) +
lag(Value, 5)
) / 6
) %>%
ungroup()Verify the window calculations
Two checks confirm that the windows behave as intended. First, the first year-to-date average for every item and year must equal that first observation’s value. Second, the first five moving-average values for each currency should be missing, and the sixth observation should contain the first complete six-observation moving average.
# Check that the YTD calculation restarts correctly each January.
ytd_check <- fx_windowed %>%
group_by(Item, Year) %>%
slice_head(n = 1) %>%
mutate(Check = abs(Value - YTD_Average) < 0.0000001) %>%
ungroup()
stopifnot(all(ytd_check$Check))
# Verify the six-observation moving window
moving_window_check <- fx_windowed %>%
group_by(Item) %>%
arrange(Date, .by_group = TRUE) %>%
mutate(Row_Number = row_number()) %>%
summarise(
First_Five_Are_NA =
all(is.na(Six_Day_Moving_Average[Row_Number <= 5])),
Sixth_Is_Available =
!is.na(Six_Day_Moving_Average[Row_Number == 6]),
.groups = "drop"
)
moving_window_check## # A tibble: 3 × 3
## Item First_Five_Are_NA Sixth_Is_Available
## <chr> <lgl> <lgl>
## 1 Canadian dollar per U.S. dollar TRUE TRUE
## 2 Chinese yuan per U.S. dollar TRUE TRUE
## 3 Mexican peso per U.S. dollar TRUE TRUEstopifnot(
all(moving_window_check$First_Five_Are_NA),
all(moving_window_check$Sixth_Is_Available)
)
kable(
ytd_check %>% select(Item, Year, Date, Value, YTD_Average),
digits = 4,
caption = "Verification that the YTD average resets each year"
)| Item | Year | Date | Value | YTD_Average |
|---|---|---|---|---|
| Canadian dollar per U.S. dollar | 2022 | 2022-01-03 | 1.2757 | 1.2757 |
| Canadian dollar per U.S. dollar | 2023 | 2023-01-03 | 1.3664 | 1.3664 |
| Canadian dollar per U.S. dollar | 2024 | 2024-01-02 | 1.3310 | 1.3310 |
| Canadian dollar per U.S. dollar | 2025 | 2025-01-02 | 1.4422 | 1.4422 |
| Canadian dollar per U.S. dollar | 2026 | 2026-01-02 | 1.3738 | 1.3738 |
| Chinese yuan per U.S. dollar | 2022 | 2022-01-03 | 6.3550 | 6.3550 |
| Chinese yuan per U.S. dollar | 2023 | 2023-01-03 | 6.9135 | 6.9135 |
| Chinese yuan per U.S. dollar | 2024 | 2024-01-02 | 7.1426 | 7.1426 |
| Chinese yuan per U.S. dollar | 2025 | 2025-01-02 | 7.2994 | 7.2994 |
| Chinese yuan per U.S. dollar | 2026 | 2026-01-02 | 6.9877 | 6.9877 |
| Mexican peso per U.S. dollar | 2022 | 2022-01-03 | 20.5700 | 20.5700 |
| Mexican peso per U.S. dollar | 2023 | 2023-01-03 | 19.4210 | 19.4210 |
| Mexican peso per U.S. dollar | 2024 | 2024-01-02 | 17.0140 | 17.0140 |
| Mexican peso per U.S. dollar | 2025 | 2025-01-02 | 20.6250 | 20.6250 |
| Mexican peso per U.S. dollar | 2026 | 2026-01-02 | 17.8763 | 17.8763 |
kable(
moving_window_check,
caption = "Verification of the six-day moving window"
)| Item | First_Five_Are_NA | Sixth_Is_Available |
|---|---|---|
| Canadian dollar per U.S. dollar | TRUE | TRUE |
| Chinese yuan per U.S. dollar | TRUE | TRUE |
| Mexican peso per U.S. dollar | TRUE | TRUE |
Visualization of Window Functions
The following visualization compares the original exchange rate with the year-to-date average and six-observation moving average for each currency.
plot_data <- fx_windowed %>%
select(
Date,
Item,
`Original Value` = Value,
`YTD Average` = YTD_Average,
`Six-Day Moving Average` = Six_Day_Moving_Average
) %>%
pivot_longer(
cols = c(
`Original Value`,
`YTD Average`,
`Six-Day Moving Average`
),
names_to = "Measure",
values_to = "Rate"
)
ggplot(
plot_data,
aes(x = Date, y = Rate, linetype = Measure)
) +
geom_line() +
facet_wrap(~ Item, scales = "free_y") +
labs(
title = "Exchange Rates and Window Averages",
x = "Date",
y = "Foreign Currency Units per U.S. Dollar",
linetype = "Measure"
) +
theme_minimal()## Warning: Removed 15 rows containing missing values or values outside the scale range
## (`geom_line()`).
The original exchange-rate values show greater short-term variation than
either window average. The six-observation moving average responds more
quickly to recent changes, while the year-to-date average is smoother
because it incorporates all reported observations since the beginning of
the calendar year.
Review sample and latest results
The first table displays the beginning of each currency series. It shows that the six-day moving average is unavailable for observations one through five and appears for the first time on observation six. The second table presents the latest observation, year-to-date average, and six-day moving average for each currency.
# Display the first six observations within each currency.
sample_results <- fx_windowed %>%
group_by(Item) %>%
slice_head(n = 6) %>%
ungroup() %>%
select(Date, Item, Value, YTD_Average, Six_Day_Moving_Average)
# Keep the most recent observation for each currency.
latest_results <- fx_windowed %>%
group_by(Item) %>%
slice_max(Date, n = 1, with_ties = FALSE) %>%
ungroup() %>%
select(Date, Item, Unit, Value, YTD_Average, Six_Day_Moving_Average)
kable(
sample_results,
digits = 4,
caption = "First six observations and calculated windows"
)| Date | Item | Value | YTD_Average | Six_Day_Moving_Average |
|---|---|---|---|---|
| 2022-01-03 | Canadian dollar per U.S. dollar | 1.2757 | 1.2757 | NA |
| 2022-01-04 | Canadian dollar per U.S. dollar | 1.2697 | 1.2727 | NA |
| 2022-01-05 | Canadian dollar per U.S. dollar | 1.2700 | 1.2718 | NA |
| 2022-01-06 | Canadian dollar per U.S. dollar | 1.2725 | 1.2720 | NA |
| 2022-01-07 | Canadian dollar per U.S. dollar | 1.2643 | 1.2704 | NA |
| 2022-01-10 | Canadian dollar per U.S. dollar | 1.2679 | 1.2700 | 1.2700 |
| 2022-01-03 | Chinese yuan per U.S. dollar | 6.3550 | 6.3550 | NA |
| 2022-01-04 | Chinese yuan per U.S. dollar | 6.3721 | 6.3636 | NA |
| 2022-01-05 | Chinese yuan per U.S. dollar | 6.3640 | 6.3637 | NA |
| 2022-01-06 | Chinese yuan per U.S. dollar | 6.3822 | 6.3683 | NA |
| 2022-01-07 | Chinese yuan per U.S. dollar | 6.3769 | 6.3700 | NA |
| 2022-01-10 | Chinese yuan per U.S. dollar | 6.3756 | 6.3710 | 6.3710 |
| 2022-01-03 | Mexican peso per U.S. dollar | 20.5700 | 20.5700 | NA |
| 2022-01-04 | Mexican peso per U.S. dollar | 20.5010 | 20.5355 | NA |
| 2022-01-05 | Mexican peso per U.S. dollar | 20.4390 | 20.5033 | NA |
| 2022-01-06 | Mexican peso per U.S. dollar | 20.4350 | 20.4862 | NA |
| 2022-01-07 | Mexican peso per U.S. dollar | 20.3970 | 20.4684 | NA |
| 2022-01-10 | Mexican peso per U.S. dollar | 20.4550 | 20.4662 | 20.4662 |
kable(
latest_results,
digits = 4,
caption = "Latest exchange rates and window averages"
)| Date | Item | Unit | Value | YTD_Average | Six_Day_Moving_Average |
|---|---|---|---|---|---|
| 2026-09-11 | Canadian dollar per U.S. dollar | CAD per USD | 1.3864 | 1.3835 | 1.3812 |
| 2026-09-11 | Chinese yuan per U.S. dollar | CNY per USD | 6.7080 | 6.8295 | 6.7099 |
| 2026-09-11 | Mexican peso per U.S. dollar | MXN per USD | 16.9704 | 17.3951 | 16.9291 |
Conclusions
The window calculations provide two different perspectives on each exchange-rate series. On September 11, 2026, the Canadian dollar rate was 1.3864, compared with a year-to-date average of 1.3835 and a six-day average of 1.3812. The Mexican peso rate of 16.9704 and Chinese yuan rate of 6.708 were both below their respective year-to-date averages of 17.3951 and 6.8295.
Because each value represents foreign currency units per one U.S. dollar, the three currencies have different scales and should not be compared by their raw magnitudes. The six-day averages describe the six most recent reported observations, not six consecutive calendar days. Future work could refresh the end date, investigate percentage changes so the currencies can be compared on a common scale, or expand the calendar to evaluate a true six-calendar-day window with an explicitly chosen missing-value method.
Citations
OpenAI. (2026). ChatGPT (Version 5.6) [Large language model]. https://chat.openai.com. Accessed September 20, 2026.