0

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!

Li4991
  • 59
  • 5
  • Could you edit your question to make this reproducible? What's is the variable for old price? What is the variable for new price? – jpsmith Jan 10 '23 at 23:04
  • Hi @jpsmith , I edited the question. I hope this is getting clearer now. Thanks. – Li4991 Jan 10 '23 at 23:17
  • 1
    Can you include your data using `dput()` so your question is [reproducible](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example)? – jrcalabrese Jan 10 '23 at 23:37

1 Answers1

1

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
Chamkrai
  • 5,912
  • 1
  • 4
  • 14
  • Thanks, Tom! I receive the following Error Message when applying your code: "Error in merge_two_tibbles(tib1 = data, tib2 = ret, mutate_fun) : Could not join. Incompatible structures." I am trying to work on it, but do you have any idea what could cause that issue? – Li4991 Jan 11 '23 at 11:18
  • @Li4991 Not really. Is your current widely different from the sample data you have posted here? – Chamkrai Jan 11 '23 at 11:40
  • I just found the mistake: There were some cells empty (due to not existing data). I replaced the empty cells with a 0 and now the code works perfectly fine. Thank you so much - you saved my day! – Li4991 Jan 11 '23 at 11:46