Search code examples
rcalculationstock

Calculating the daily return for different stocks in R

I am trying to calculate the log returns of a dataset in R using the usual log differencing method for several stocks.

My current list of daily closing prices looks like this:

enter image description here

In total, I have a dataset of 4,000 stocks that cover the period of one year.

This is the trial data:

Date <- c(01.11.2019, 04.11.2019, 05.11.2019, 06.11.2019, 07.11.2019, 08.11.2019)

ACCR.PK <- c(0.0035, 0.003, 0.0035, 0.0057, 0.0032, 0.0032)

SWGI.PK <- c(0.51, 0.51, 0.51, 0.51, 0.51, 0.51)

HURC.OQ <- c(35.53, 35.62, 35.76, 35.52, 35.6, 36,07)

I would like to calculate this in R.

Typically the formula is used to calculate the returns.

Return = (New Price - Old Price) / Old Price [in percentage ] with "new price = t" and "old price = t-1"

I tried the following code:

# First upload the financial data in R
library(readxl)
Closing_Prices_2020 <- read_excel("Closing_Prices_2020.xlsx")

I then tried the two options:

First try:

Returns_2020 <- Return.calculate(Daily_Returns_2020, method="log")

Second try:

CalculateReturns(Closing_Prices_2020$ACCR.PK, method = c("discrete", "log"))

Neither of them works for me. Does somebody help in calculating the daily returns? Thanks!

Solution

  • Here is how you can do it with tidyquant.

    Load data:

    library(tidyverse)
library(tidyquant)

df <- tibble(
  Date = c(
    '01.11.2019',
    '04.11.2019',
    '05.11.2019',
    '06.11.2019',
    '07.11.2019',
    '08.11.2019'
  ),
  ACCR.PK = c(0.0035, 0.003, 0.0035, 0.0057, 0.0032, 0.0032),
  SWGI.PK = c(0.51, 0.51, 0.51, 0.51, 0.51, 0.51),
  HURC.OQ = c(35.53, 35.62, 35.76, 35.52, 35.6, 36)
) %>% 
  mutate(Date = Date %>% 
           as.Date(format = "%d.%m.%Y"))

    Calculate the daily return on each security

    df %>% 
  pivot_longer(-Date, names_to = "ticker", values_to = "price") %>% 
  group_by(ticker) %>% 
  tq_mutate(select = price, 
            mutate_fun = periodReturn, 
            period = "daily", 
            col_rename = "daily_return") 

# A tibble: 18 × 4
# Groups:   ticker [3]
   ticker  Date         price daily_return
   <chr>   <date>       <dbl>        <dbl>
 1 ACCR.PK 2019-11-01  0.0035      0      
 2 ACCR.PK 2019-11-04  0.003      -0.143  
 3 ACCR.PK 2019-11-05  0.0035      0.167  
 4 ACCR.PK 2019-11-06  0.0057      0.629  
 5 ACCR.PK 2019-11-07  0.0032     -0.439  
 6 ACCR.PK 2019-11-08  0.0032      0      
 7 SWGI.PK 2019-11-01  0.51        0      
 8 SWGI.PK 2019-11-04  0.51        0      
 9 SWGI.PK 2019-11-05  0.51        0      
10 SWGI.PK 2019-11-06  0.51        0      
11 SWGI.PK 2019-11-07  0.51        0      
12 SWGI.PK 2019-11-08  0.51        0      
13 HURC.OQ 2019-11-01 35.5         0      
14 HURC.OQ 2019-11-04 35.6         0.00253
15 HURC.OQ 2019-11-05 35.8         0.00393
16 HURC.OQ 2019-11-06 35.5        -0.00671
17 HURC.OQ 2019-11-07 35.6         0.00225
18 HURC.OQ 2019-11-08 36           0.0112