CODEWITHSUNDEEP
rdataframedplyr
Mandallaz
Mandallaz

Reputation: 55

Prices returns calculation in a df with many tickers with dplyr

I have a dataframe with 3 columns : Dates, Tickers (i.e. financial instruments) and Prices. I just want to calculate the returns for each ticker.

Some data to play with:

AsofDate = as.Date(c("2018-01-01","2018-01-02","2018-01-03","2018-01-04","2018-01-05",
                     "2018-01-01","2018-01-02","2018-01-03","2018-01-04","2018-01-05",
                     "2018-01-01","2018-01-02","2018-01-03","2018-01-04","2018-01-05"))
Tickers = c("Ticker1", "Ticker1", "Ticker1", "Ticker1", "Ticker1",
            "Ticker2", "Ticker2", "Ticker2", "Ticker2", "Ticker2",
            "Ticker3", "Ticker3", "Ticker3", "Ticker3", "Ticker3")

Prices =c(1,2,7,4,2,
          6,5,7,9,12,
          11,11,16,14,15)

df = data.frame(AsofDate, Tickers, Prices)

My first Idea was just to order the Prices by (Tickers Prices), then calculate for all the vector and set at NA the first day...

TTR::ROC(x=Prices)

It works in Excel but I want something more pretty

So I tried something like this: 

require(dplyr)
ret = df %>% 
  select(Tickers,Prices) %>% 
  group_by(Tickers) %>% 
  do(data.frame(LogReturns=TTR::ROC(x=Prices)))

df$LogReturns = ret$LogReturns

But Here I get too much values, it seems that the calculation is not done by Tickers.

Can you give me a hint ?

Thanks !!

Upvotes: 2

Views: 912

Answers (2)

akrun
akrun

Reputation: 887541

We can use data.table

library(data.table)
setDT(df)[, returns := (Prices - shift(Prices))/Prices, by = Tickers]

Upvotes: 0

Ronak Shah
Ronak Shah

Reputation: 389155

In dplyr, we can use lag to get previous Prices

library(dplyr)
df %>%
  group_by(Tickers) %>%
  mutate(returns = (Prices - lag(Prices))/Prices)

# AsofDate   Tickers Prices returns
#   <date>     <fct>    <dbl>   <dbl>
# 1 2018-01-01 Ticker1      1 NA     
# 2 2018-01-02 Ticker1      2  0.5   
# 3 2018-01-03 Ticker1      7  0.714 
# 4 2018-01-04 Ticker1      4 -0.75  
# 5 2018-01-05 Ticker1      2 -1     
# 6 2018-01-01 Ticker2      6 NA     
# 7 2018-01-02 Ticker2      5 -0.2   
# 8 2018-01-03 Ticker2      7  0.286 
# 9 2018-01-04 Ticker2      9  0.222 
#10 2018-01-05 Ticker2     12  0.25  
#11 2018-01-01 Ticker3     11 NA     
#12 2018-01-02 Ticker3     11  0     
#13 2018-01-03 Ticker3     16  0.312 
#14 2018-01-04 Ticker3     14 -0.143 
#15 2018-01-05 Ticker3     15  0.0667

In base R, we can use ave with diff

df$returns <- with(df, ave(Prices, Tickers,FUN = function(x) c(NA,diff(x)))/Prices)

Upvotes: 2

Related Questions