1
$\begingroup$

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. Thanks

$\endgroup$
  • 1
    $\begingroup$ Post some sample data and desired output and someone will probably post a solution. For these kind of jobs in general I have found between dplyr and RcppRoll you can do any kind of lead/lag. $\endgroup$ – JoshK May 25 '16 at 15:05
  • $\begingroup$ @JoshK kindly have a look at this: stackoverflow.com/questions/37961458/… $\endgroup$ – Polar Bear Jun 23 '16 at 7:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.