Calculating Log Returns Within a Long-Format Stock Panel
Summary
The document asks how to calculate hourly log returns for multiple stocks stored in long-format panel data. It provides sample records with firm identifiers, timestamps, prices, and an existing return column, then proposes applying a function that differences log prices within each firm. The question also considers converting the data to wide form and using a portfolio analytics function before reshaping it back.
The key methodological issue is that observations must be ordered by time within each firm, and each return should compare a price only with the preceding observation for that same firm. The sample spans separate trading days, but the document does not specify how missing hours, overnight gaps, or irregular timestamps should be handled. No answer or verified implementation is included, so it raises a practical data-processing question rather than demonstrating that its proposed code is correct.
Key ideas
- Log returns are computed from differences in the logarithms of successive prices.
- Returns for a long-format panel should be calculated separately for each firm.
- Rows need to be sorted chronologically within each firm before computing differences.
- The document compares grouped long-format calculations with reshaping to wide format.
- It does not resolve how to treat missing observations, overnight gaps, or irregular time intervals.
Tags
Full text
# R:log return calculation for panel data structure
# R:log return calculation for panel data structure
I have a long form panel for hourly prices of stocks. I want to do log return calculation for this panel data structure. This is sample data:
> `> dput(idf) structure(list(Firm = c("ABC", "ABC", "ABC", "ABC", "ABC", "ABC", "ABC", "ABC", "ABC", "ABC", "ABC", "ABC", "XYZ", "XYZ", "XYZ", "XYZ", "XYZ", "XYZ", "XYZ", "XYZ", "XYZ", "XYZ", "XYZ", "XYZ" ), Date = structure(c(1451642400, 1451646000, 1451649600, 1451653200, 1451656800, 1451660400, 1451901600, 1451905200, 1451908800, 1451912400, 1451916000, 1451919600, 1451642400, 1451646000, 1451649600, 1451653200, 1451656800, 1451660400, 1451901600, 1451905200, 1451908800, 1451912400, 1451916000, 1451919600), tzone = "UTC", class = c("POSIXct", "POSIXt")), Price = c(1277, 1273.25, 1273.85, 1273.75, 1272, 1265.35, 1248.1, 1242, 1248.15, 1241.1, 1246.5, 1242.5, 225.7, 225.5, 225.45, 228.6, 227.7, 227.8, 225.1, 222.35, 222.25, 221.1, 221.2, 220.7), rt = c(NA, -0.0029408902678254, 0.000471124032113579, -7.8505259892836e-05, -0.00137484063686699, -0.00524170116535849, -0.0137263688103015, -0.00489941143098349, 0.00493947152112462, -0.00566437187907365, 0.00434154082813709, -0.00321414499282824, NA, -0.000886524880757023, -0.000221754075639957, 0.0138753464936165, -0.00394477829101625, 0.000439077943387822, -0.0119233031003949, -0.0122920309559813, -0.000449842562691316, -0.00518778653061336, 0.000452181784779349, -0.00226295638548901), day = structure(c(16801, 16801, 16801, 16801, 16801, 16801, 16804, 16804, 16804, 16804, 16804, 16804, 16801, 16801, 16801, 16801, 16801, 16801, 16804, 16804, 16804, 16804, 16804, 16804), class = "Date")), .Names = c("Firm", "Date", "Price", "rt", "day"), row.names = c(NA, -24L), class = c("tbl_df", "data.frame"))`
Below is my code:
> `ret = function(x) c(NA,diff(log(x)), NA) df$RT = ave(idf$Price, df$Firm, FUN = ret)`
Is it fine as I did above or should I first convert the long panel to wide panel, and estimate log return columns using for loop or apply functions to
> Return.calculate(x, method="compound")
(from PerformanceanAlytics package) and finally, convert it back to long form. Or is there any other way. ThanksShown in full with attribution under the source's licence. Licence: CC BY-SA 4.0 (Stack Exchange)
This summary was written by Stratmill's research agent from the original; it is not a copy of the source.