R data.table Return calculation and set()

Question

I am sure I am missing something simple, but how do I calculate daily returns using data.tables in R?

Let's say I have a data.table like:

DT <- data.table(ticker=rep(letters,each=5), priceA=runif(5*26^2), priceB=runif(5*26^2))

How to I form a new column with the respective returns of price for each ticker?

By returns I mean the normal percentage returns. That is, the second value of priceA for ticker a minus the previous one for the same ticker and this divided by the previous one.

Given the example with the columns ticker, priceA and priceB I should get the column returnsA as in:

      ticker     priceA     priceB  returnsA
   1:      a 0.63519775 0.04784728 
   2:      a 0.01530738 0.34917328  -0.97590
   3:      a 0.28601406 0.12307475  17.68472 
   4:      a 0.77851212 0.47829863  1.721937
   5:      a 0.84078779 0.23491432  0.079993

Also, how do I use set() instead of := to make sth like

DT[, newprice := priceA * priceB]

?

Thank you! :)

For `set`, maybe `DT[,newprice:=NA]; set(DT,j=4L,value=DT[['priceA']]*DT[['priceB']])` You haven't defined "returns" yet. — Frank, Feb 20 '14 at 19:18
Thank you for your answer, this is exactly what I wanted for set(). I have updated my question and added an example. — Konstantinos, Feb 20 '14 at 19:47

score 3 · Accepted Answer · edited May 23 '17 at 12:03

3

Okay, this works:

set.seed(42)
DT <- data.table(
  ticker=rep(letters,each=5),
  priceA=runif(5*26^2),
  priceB=runif(5*26^2))

DT[,paste('returns',LETTERS[1:2],sep=''):={
  lapply(.SD,function(x){
    old <- head(x,-1)
    new <- tail(x,-1)
    c(NA,(new-old)/old)
  })
},by=ticker,.SDcols=grep('^price',names(DT))]

The result (for this seed value) is

      ticker    priceA    priceB    returnsA   returnsB
   1:      a 0.9148060 0.7956245          NA         NA
   2:      a 0.9370754 0.9314941  0.02434327  0.1707710
   3:      a 0.2861395 0.6269996 -0.69464620 -0.3268883
   4:      a 0.8304476 0.1666758  1.90224707 -0.7341691
   5:      a 0.6417455 0.6483800 -0.22722939  2.8900659
  ---                                                  
3376:      z 0.2887293 0.3473923 -0.54132570 -0.3514041
3377:      z 0.9013438 0.1788842  2.12176058 -0.4850656
3378:      z 0.3126429 0.7648157 -0.65313686  3.2754788
3379:      z 0.8791381 0.1300418  1.81195584 -0.8299698
3380:      z 0.8160158 0.8159330 -0.07180019  5.2743905

To use set in your example:

DT[,newprice:=NA]
set(DT,j=ncol(DT),value=DT[['priceA']]*DT[['priceB']])

Also, there are packages designed for dealing with returns and such, as seen here: Calculating %changes with the By()

edited May 23 '17 at 12:03

Community

1
1

answered Feb 20 '14 at 19:57

Frank

66,179
8
96
180

So, it wasn't so simple. :) Thank you very much. For anybody interested, http://stackoverflow.com/questions/14937165/using-dynamic-column-names-in-data-table and http://stackoverflow.com/questions/8508482/what-does-sd-stand-for-in-data-table-in-r are helpful for understanding .SDcols – Konstantinos Feb 20 '14 at 20:35
1

You're welcome. Yeah, actually `.SDcols` is not strictly needed if your DT looks exactly like this (since `.SD`'s columns would default to every column except "ticker", because it is in the `by`). I used it here because (1) if you ran the operation twice in a row without it, you'd find weird results; and... well (2) it's useful to know about, along with `grep`, `head`, `[[`, etc. if they're new to you or someone else coming across this question :) Anyways, those are good references you've found there. – Frank Feb 20 '14 at 20:45
I am trying to understand your working solution and I am trying to modify it. Suppose I want to use Delt() from library(quantmod) that calculates the returns instead of your `function(x)`. Could I ask how should I do this? – Konstantinos Feb 20 '14 at 23:17
1

@user2383408 Despite linking to it, I don't know the quantmod package. Maybe you should post another question...? – Frank Feb 21 '14 at 00:11
1

Asked it here: http://stackoverflow.com/questions/21923028/r-using-quantmods-delt-in-a-data-table Thank you very much, Frank, you saved my day! :) – Konstantinos Feb 21 '14 at 00:56

R data.table Return calculation and set()

1 Answers1

Linked