Search code examples
rdataframeplyrstock

Creating a stock index etc. from a large data frame in R

I have a data frame namned stockdata, which includes the close prices of several stocks over several years. The style of the data frame is presented below:

      date close  ticker  stock.name
2001-09-06  3.06  LAGR   Lagercrantz
2001-09-07  2.89  LAGR   Lagercrantz
2001-09-09  2.67  LAGR   Lagercrantz
2001-09-10  2.67  LAGR   Lagercrantz
2001-09-11  2.56  LAGR   Lagercrantz
2001-09-12  2.24  LAGR   Lagercrantz
2001-09-13  2.44  LAGR   Lagercrantz
2001-09-06 20.70  MEAB   Malmbergs Elektriska
2001-09-07 20.60  MEAB   Malmbergs Elektriska
2001-09-09 20.40  MEAB   Malmbergs Elektriska
2001-09-10 20.50  MEAB   Malmbergs Elektriska
2001-09-11 20.50  MEAB   Malmbergs Elektriska
2001-09-12 20.70  MEAB   Malmbergs Elektriska
2001-09-13 20.60  MEAB   Malmbergs Elektriska
2011-07-06 1.8018 HTRO   Hexatronic
2011-07-07 1.8018 HTRO   Hexatronic
2011-07-08 1.8318 HTRO   Hexatronic
2011-07-11 1.8394 HTRO   Hexatronic
2011-07-12 1.8394 HTRO   Hexatronic
2011-07-13 1.8769 HTRO   Hexatronic

From this I would like to:

  1. Add a column namned percentage, which should contain the stocks' performance in percentage based on the first listing date of each stock.

  2. Create a stock index based all the stocks' close price in the data frame. Since the amount of stocks vary by time (different introduction dates, delistings etc.), this needs to be considered when calculating the percentage and price for the new stock index.

Which would be the easiest way to perform these things? Is there any way without having to loop through all the data?

Solution

  • Data

    df <- read.table(text = "
date close  ticker  stock.name
2001-09-06  3.06  LAGR   Lagercrantz
2001-09-07  2.89  LAGR   Lagercrantz
2001-09-09  2.67  LAGR   Lagercrantz
2001-09-10  2.67  LAGR   Lagercrantz
2001-09-11  2.56  LAGR   Lagercrantz
2001-09-12  2.24  LAGR   Lagercrantz
2001-09-13  2.44  LAGR   Lagercrantz
2001-09-06 20.70  MEAB   'Malmbergs Elektriska'
2001-09-07 20.60  MEAB   'Malmbergs Elektriska'
2001-09-09 20.40  MEAB   'Malmbergs Elektriska'
2001-09-10 20.50  MEAB   'Malmbergs Elektriska'
2001-09-11 20.50  MEAB   'Malmbergs Elektriska'
2001-09-12 20.70  MEAB   'Malmbergs Elektriska'
2001-09-13 20.60  MEAB   'Malmbergs Elektriska'
2011-07-06 1.8018 HTRO   Hexatronic
2011-07-07 1.8018 HTRO   Hexatronic
2011-07-08 1.8318 HTRO   Hexatronic
2011-07-11 1.8394 HTRO   Hexatronic
2011-07-12 1.8394 HTRO   Hexatronic
2011-07-13 1.8769 HTRO   Hexatronic
  ",
  header = TRUE)

    1. and 2.

    library(tidyverse)

df %>%
  group_by(ticker) %>%
  mutate(
    percentage = close / close[date == min(date)],
    average = mean(percentage))