This project will analyze the daily foreign exchange dataset created for three items: the Canadian dollar, Mexican peso, and Chinese yuan relative to the U.S. dollar. The dataset was generated with assistance from an LLM, which collected and organized publicly available exchange-rate observations from the Federal Reserve Economic Data (FRED) database into an analysis-ready workbook. The objective is to use window calculations to add two measures to each observation: the year-to-date average exchange rate and the six-day moving average. These measures will make it possible to compare the broader direction of each exchange-rate series with its more recent short-term movement.
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.
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 or nonnumeric values must remain excluded instead of being converted to zero, since zero would not be a valid exchange rate and would distort the averages. 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.