Add a daily return column to a price table with dplyr::lag(), and see what goes wrong when one table holds several tickers.
You compute a return in a table with mutate(ret = Close / lag(Close) - 1). dplyr::lag() reaches back one row and hands you yesterday’s close alongside today’s.
In this lesson I add a return column to one ticker, check it against the diff() version from Lesson 8, repeat it on a second ticker, and then run lag() on all three tickers at once to show what it gets wrong.
Step 1. One ticker, oldest row first
The file is simulated. It holds three tickers in one table, three rows per date, so before anything else I pull out one ticker and sort it.
lag() reaches back one row of the table, not one calendar day, so the row above has to be the day before.
library(dplyr)prices <-read.csv("prices.csv") # simulated closes, 120 rowsprices$Date <-as.Date(prices$Date) # read.csv gives Date as characteraapl <- prices |># prices itself is left untouchedfilter(Ticker =="AAA") |># one ticker onlyarrange(Date) # oldest row firstprint(nrow(aapl)) # -> [1] 40
[1] 40
print(head(aapl, 4)) # check the sort and the ticker filter
mutate() adds a column. lag(Close) slides the closes down one place and puts NA in the gap at the top, so each row divides today’s close by the one above it. Both Close and lag(Close) are 40 long, so the division runs element by element and the answer is 40 long too.
aapl <- aapl |># the result replaces aaplmutate(ret = Close /lag(Close) -1) # today over yesterday, minus oneprint(head(aapl[, c("Date", "Close", "ret")], 5)) # ret alongside its close
Date Close ret
1 2026-01-02 186.66 NA
2 2026-01-05 186.38 -0.001500054
3 2026-01-06 188.51 0.011428265
4 2026-01-07 187.24 -0.006737043
5 2026-01-08 189.27 0.010841700
Row 1 is NA. The first day has no day before it, so it has no return. That is the same fact Lesson 8 stated as “three prices give two returns”, written differently: diff() dropped the day, lag() keeps the row and marks it NA.
There is exactly one NA, and the rest compound to the move from the first close to the last. ret[-1] drops the first element, the NA.
Run the Lesson 8 line on the same closes. The numbers agree, but the lengths do not.
d <-diff(aapl$Close) /head(aapl$Close, -1) # lesson 8, on a bare vectorprint(length(d)) # -> [1] 39
[1] 39
print(length(aapl$ret)) # -> [1] 40
[1] 40
print(all.equal(d, aapl$ret[-1])) # -> [1] TRUE
[1] TRUE
diff() returns one element fewer than it was given. A table column has to be as long as the table, so d cannot go into a 40-row data frame without you first pasting an NA on the front. lag() returns the same length it was given, which is why it is the one that fits in mutate().
Step 4. The second ticker
Same three lines on CCC.
msft <- prices |>filter(Ticker =="CCC") |># second tickerarrange(Date) |>mutate(ret = Close /lag(Close) -1) # same calculationprint(head(msft[, c("Date", "Close", "ret")], 4))
Date Close ret
1 2026-01-02 416.98 NA
2 2026-01-05 423.19 0.014892801
3 2026-01-06 423.95 0.001795884
4 2026-01-07 435.34 0.026866376
Row 81 reads CCC’s 435.94 as DDD’s yesterday and reports a 71% fall. Two rows out of 120 are wrong. Average the stacked column, then average AAA’s own column from Step 2.
The stacked mean is positive. The AAA mean is negative.
The fix is to tell lag() where each ticker starts and stops, with group_by(Ticker). That is Lesson 19. Until then, filter to one ticker before you take a return.
Your turn
Pull DDD out of prices, sort it by date, and add a ret column with lag(). Count the NAs, compound the rest, and check the answer against the last close over the first.