library(data.table)
dt <- fread("prices.csv") # simulated daily closes for four tickersHow do I compute returns per ticker with data.table?
Compute a daily return inside each ticker with a single data.table assignment, check the ticker boundary, and see what := does to the table it is called on.
data.table computes a return inside each ticker with := and shift(), in one line. Lesson 19 did the same job with four dplyr verbs: group_by(), arrange(), mutate() and ungroup().
In this lesson I compute per ticker returns on the price file with data.table, check the ticker boundary the way Lesson 19 did, show what := does to the table it is called on, and finish with grouped summaries.
Step 1. One line for the returns
The file is the one Lesson 30 read: simulated daily closes for four tickers, 2020 to 2025.
The rows arrive stacked: every ticker for one date, then every ticker for the next.
Now the returns. A data.table call is dt[i, j, by]: i chooses rows, j is what to compute, and by says which groups to compute it in.
dt[order(Ticker, Date), ret := Close / shift(Close) - 1, by = Ticker]
print(ncol(dt)) # -> [1] 5 <- ret is now part of dt[1] 5Four pieces, left to right. order(Ticker, Date) sits in i and sorts the rows the calculation walks through, all AAA days in date order, then BBB, and so on. shift(Close) is data.table’s lag: it returns the previous value and NA at the start. by = Ticker restarts the shift inside each ticker. := writes ret into dt itself rather than returning a new table, which is why the line has no <- in front of it.
print(head(dt[Ticker == "AAA"], 4)) Date Ticker Close Volume ret
<IDat> <char> <num> <int> <num>
1: 2020-01-01 AAA 75.08 25594073 NA
2: 2020-01-02 AAA 76.94 23185470 0.024773575
3: 2020-01-03 AAA 77.34 13469734 0.005198856
4: 2020-01-06 AAA 78.32 20680555 0.012671321# -> Date Ticker Close Volume ret
# -> <IDat> <char> <num> <int> <num>
# -> 1: 2020-01-01 AAA 75.08 25594073 NA
# -> 2: 2020-01-02 AAA 76.94 23185470 0.024773575
# -> 3: 2020-01-03 AAA 77.34 13469734 0.005198856
# -> 4: 2020-01-06 AAA 78.32 20680555 0.012671321The first AAA day is NA. It has no earlier AAA day to divide into, exactly as in Lesson 19.
Step 2. Check the boundary
Lesson 19 checked the row where one ticker ends and the next begins. The same check here. Sorting a copy by ticker then date puts the four blocks in order, and shift() on the ticker column finds the rows where the name changes.
srt <- dt[order(Ticker, Date)] # a sorted copy, four blocks of 1566
b <- which(srt$Ticker != shift(srt$Ticker)) # rows where the ticker name changes
print(b) # -> [1] 1567 3133 4699[1] 1567 3133 4699print(srt[1565:1568, .(Date, Ticker, Close, ret)]) Date Ticker Close ret
<IDat> <char> <num> <num>
1: 2025-12-30 AAA 196.56 -0.001929522
2: 2025-12-31 AAA 199.30 0.013939764
3: 2020-01-01 BBB 144.61 NA
4: 2020-01-02 BBB 144.85 0.001659636# -> Date Ticker Close ret
# -> <IDat> <char> <num> <num>
# -> 1: 2025-12-30 AAA 196.56 -0.001929522
# -> 2: 2025-12-31 AAA 199.30 0.013939764
# -> 3: 2020-01-01 BBB 144.61 NA
# -> 4: 2020-01-02 BBB 144.85 0.001659636Row 3 is BBB’s first day and it is NA. Three boundaries between four tickers, three NAs there, plus the very first row of the file, so four in all.
Drop the by and the same expression reaches across the join.
srt[, ret_all := Close / shift(Close) - 1] # no by, so one shift down the whole table
print(srt[1566:1568, .(Ticker, Close, ret, ret_all)]) Ticker Close ret ret_all
<char> <num> <num> <num>
1: AAA 199.30 0.013939764 0.013939764
2: BBB 144.61 NA -0.274410437
3: BBB 144.85 0.001659636 0.001659636# -> Ticker Close ret ret_all
# -> <char> <num> <num> <num>
# -> 1: AAA 199.30 0.013939764 0.013939764
# -> 2: BBB 144.61 NA -0.274410437
# -> 3: BBB 144.85 0.001659636 0.001659636
print(sum(is.na(srt$ret))) # -> [1] 4 <- one per ticker[1] 4print(sum(is.na(srt$ret_all))) # -> [1] 1 <- only the first row[1] 1The -0.274410437 is BBB’s 144.61 over AAA’s 199.30, minus one. Two different companies, six years apart, one number. Every other row of ret_all matches ret; only the three boundary rows differ. Counting the NAs is the check: four means the group restarted, one means it did not.
Step 3. := writes into the table you called it on
<- on a data.table gives the same table a second name. := then changes what both names see.
dt2 <- dt # a second name for the same table
dt2[, flag := 1L] # column added through dt2
print("flag" %in% names(dt)) # -> [1] TRUE[1] TRUEdt3 <- copy(dt) # copy() builds a separate table
dt3[, note := 1L] # column added through dt3
print("note" %in% names(dt)) # -> [1] FALSE[1] FALSEflag appeared in dt. note did not. Call copy() when you want a table the next := cannot reach. Setting a column to NULL with := removes it, again in place.
dt[, flag := NULL] # drop the column again
print(names(dt)) # -> [1] "Date" "Ticker" "Close" "Volume" "ret"[1] "Date" "Ticker" "Close" "Volume" "ret" Step 4. Grouped summaries on the returns
j can return several named values at once inside .(), and .N is the number of rows in the current group. i filters first, so !is.na(ret) drops each ticker’s first day before anything is computed.
stats <- dt[!is.na(ret), # drop the four NA rows
.(days = .N, # returns in this ticker
mean_d = round(mean(ret), 6), # mean daily return
sd_d = round(sd(ret), 6), # daily standard deviation
ann_vol = round(100 * sd(ret) * sqrt(252), 2)), # scaled by sqrt(252), as in Lesson 27
by = Ticker]
print(stats) Ticker days mean_d sd_d ann_vol
<char> <int> <num> <num> <num>
1: AAA 1565 0.000734 0.014805 23.50
2: BBB 1565 0.000506 0.009106 14.45
3: CCC 1565 0.001136 0.013111 20.81
4: DDD 1565 0.001133 0.024220 38.45# -> Ticker days mean_d sd_d ann_vol
# -> <char> <int> <num> <num> <num>
# -> 1: AAA 1565 0.000734 0.014805 23.50
# -> 2: BBB 1565 0.000506 0.009106 14.45
# -> 3: CCC 1565 0.001136 0.013111 20.81
# -> 4: DDD 1565 0.001133 0.024220 38.451566 days each, 1565 returns each. The annualised volatilities are the ones Lesson 27 printed from the dplyr version: 23.50% for AAA, 20.81% for CCC, 38.45% for DDD, 14.45% for BBB. A standard deviation scales by sqrt(252) and a mean by 252. I annualise only the spread here; mean_d is left daily.
Your turn
From dt, print the number of returns, the total return, the best day and the worst day for each ticker. Compound the returns with prod(1 + ret) - 1; simple returns do not add up across days.
TipShow answer
print(dt[, .(days = sum(!is.na(ret)), # returns, not rows
total = round(100 * (prod(1 + ret, na.rm = TRUE) - 1), 2),
best = round(max(ret, na.rm = TRUE), 4), # best single day
worst = round(min(ret, na.rm = TRUE), 4)), # worst single day
by = Ticker])
# -> Ticker days total best worst
# -> <char> <int> <num> <num> <num>
# -> 1: AAA 1565 165.45 0.0489 -0.0469
# -> 2: BBB 1565 107.03 0.0311 -0.0307
# -> 3: CCC 1565 416.98 0.0423 -0.0451
# -> 4: DDD 1565 272.55 0.0911 -0.0813na.rm = TRUE is needed on all three, since one NA in the input makes prod(), max() and min() return NA. These are buy and hold figures on simulated prices over one sample.
dplyr and data.table both compute this, and they give the same numbers. Knowing the long form from Lesson 8, where the return was a loop over a vector, is what lets you read either one.