Calculate returns

[sunniva]

Hi!

I have a dataframe, like this:

        isin                     Year Month       MV       P      RI        VA   MNEM

1 BMG0539N1020 2011 12 804.00 20.10 92.80 2888.1 N:AOD
2 BMG0539N1020 2012 12 1148.00 28.70 132.50 13.5 N:AOD
3 BMG067231032 2014 12 3598.35 100.91 90.40 406557.6 N:AVAN
.
.
With 4691 observations of annual stock price data (545 companies/isin, with various length of each observation). The observations start and end at different time intervals, some companies have annual data from 1980-2020, and some might have data for 2010-2015 etc.
My problem is: I need to calculate the returns for each company/isin from RI. -> create a column/vector of returns calculated from RI ((RIt/RIt-1)-1)).

I will then need the return column to be shifted, so the returns are represented as rt+1 compared to the other data, meaning for the year 2012 I will represent the 2013 returns.

        isin                     Year Month       MV       P      RI      VA                          RETURN

1 BMG0539N1020 2011 12 804.00 20.10 92.80 return for year+1 (2011+1=2012)
2 BMG0539N1020 2012 12 1148.00 28.70 132.50 return for year+1 (2012+1=2013)
3 BMG067231032 2014 12 3598.35 100.91 90.40 return for year+1 (2014+1=2015)

Any advice?

Thanks!

[pieterjanvc]

Hi,

Try to create a reprex next time where we can easily access you data you copy pasted (not working now). A reprex consists of the minimal code and data needed to recreate the issue/question you're having. You can find instructions how to build and share one here:

FAQ: How to do a minimal reproducible example ( reprex ) for beginners

A minimal reproducible example consists of the following items: A minimal dataset, necessary to reproduce the issue The minimal runnable code necessary to reproduce the issue, which can be run on the given dataset, and including the necessary information on the used packages. Let's quickly go over each one of these with examples: Minimal Dataset (Sample Data) You need to provide a data frame that is small enough to be (reasonably) pasted on a post, but big enough to reproduce your issue. Let's say, as an example, that you are working with the iris data frame head(iris) #> Sepal.Length Sepal.Width Petal.Length Petal.Width Species #> 1 5.1 3.5 1.4 0.…

Here is an example on how you could shift the column using the lead function from the dplyr package (Tidyverse)

library(tidyverse)

#Dummy data
myData = data.frame(
  id = rep(1:3, each = 5),
  year = rep(2010:2014, 3),
  value = runif(15)
)

#Use lead() to shift all values up per id
myData %>% arrange(id, year) %>% 
  group_by(id) %>% 
  mutate(new = lead(value)) %>% 
  ungroup()
#> # A tibble: 15 x 4
#>       id  year  value     new
#>    <int> <int>  <dbl>   <dbl>
#>  1     1  2010 0.784   0.549 
#>  2     1  2011 0.549   0.0142
#>  3     1  2012 0.0142  0.928 
#>  4     1  2013 0.928   0.0629
#>  5     1  2014 0.0629 NA     
#>  6     2  2010 0.701   0.710 
#>  7     2  2011 0.710   0.115 
#>  8     2  2012 0.115   0.311 
#>  9     2  2013 0.311   0.726 
#> 10     2  2014 0.726  NA     
#> 11     3  2010 0.909   0.425 
#> 12     3  2011 0.425   0.706 
#> 13     3  2012 0.706   0.483 
#> 14     3  2013 0.483   0.526 
#> 15     3  2014 0.526  NA

^{Created on 2022-03-04 by the reprex package (v2.0.1)}

Note that all values are shifted one up (assuming the years are consecutive and none are skipped). The last year for each id does not have a new value as it is the last

Hope this helps,
PJ

[brad_schrage]

@sunniva

        isin                     Year Month       MV       P      RI        VA   MNEM

1 BMG0539N1020 2011 12 804.00 20.10 92.80 2888.1 N:AOD
2 BMG0539N1020 2012 12 1148.00 28.70 132.50 13.5 N:AOD
3 BMG067231032 2014 12 3598.35 100.91 90.40 406557.6 N:AVAN

I'd convert this into a tibble and work in the Tidyverse. The easiest way is to take your dataframe and convert it to a tibble like I do below on the mtcars dataset.

library(tidyverse)
library(lubridate)

mtcars <- mtcars %>% tibble()
mtcars %>% head()

In your example, i had to convert it into a tibble since I didn't have the dataframe provided. Depending on your data, I'd say your best bet is to group by isin and then make sure you're in order.

tribble(
    ~isin,        ~Year, ~Month, ~MV,    ~P,    ~RI,    ~VA,       ~MNEM,
    "BMG0539N1020", 2011, 12,    804.00,  20.10, 92.80, 2888.1,   "N:AOD",
    "BMG0539N1020", 2012, 12,    1148.00, 28.70, 132.50,13.5,     "N:AOD",
    "BMG067231032", 2014, 12,    3598.35, 100.91,90.40, 406557.6, "N:AVAN"
) %>% 
    mutate(across(c(Year, Month), ~as.integer(.))) %>% 
    # make sure you get end of year data to make these annual returns.
    filter(Month == 12) %>% 
    mutate(report_date = make_date(year = Year, month = Month, day = 1) %>% ceiling_date(unit = 'month'),
           year_end    = as_date(report_date - 1),
           report_year = year(report_date)) %>% 
    group_by(isin) %>% 
    arrange(report_date, .by_group = TRUE) %>% 
    mutate(ret = RI / lag(RI) - 1) %>% 
    ungroup()