How to get returns from a DF-CodePudding

So I'm using the quantmod library to calculate historical returns, but while I can get the past prices, how can I calculate the returns and add it on to the dataframe???

My code looks like this

tickers <- c('KO', 'AAPL')
getSymbols(tickers, from = '2020-07-01', to = '2021-07-01')
history <- cbind(KO$KO.Close,AAPL$AAPL.Close)

CodePudding user response：

First I did a way to better import and structure data

Import

library(quantmod)
library(tidyverse)

tickers <- c('KO', 'AAPL')

df <-
  map_df(
    .x = tickers,
    .f = function(x){
      getSymbols(x, from = '2020-07-01', to = '2021-07-01',auto.assign = FALSE) %>% 
        as_tibble() %>% 
        set_names(c("open","high","low","close","volume","adjusted")) %>% 
        mutate(symbol = x)
    }
  )

# A tibble: 504 x 7
    open  high   low close   volume adjusted symbol
   <dbl> <dbl> <dbl> <dbl>    <dbl>    <dbl> <chr> 
 1  44.9  45.5  44.7  44.8 14316500     43.1 KO    
 2  45.3  45.4  44.8  44.9 15111900     43.2 KO    
 3  45.1  45.3  44.6  45.2 15146000     43.5 KO    
 4  45    45.5  44.8  45.2 13043600     43.5 KO    
 5  45.1  45.2  44.5  45.1 13851200     43.3 KO    
 6  45.0  45.0  43.8  43.9 16087100     42.2 KO    
 7  43.9  45.2  43.9  45.2 15627800     43.4 KO    
 8  45.5  45.7  45.0  45.2 16705300     43.5 KO    
 9  44.9  45.9  44.7  45.9 17080100     44.1 KO    
10  46.3  47.2  46.2  46.4 23738000     44.6 KO

Return

I do not know if this is the right formula for return, but you can change later inside mutate

df %>% 
  group_by(symbol) %>% 
  mutate(return = 100*((open/lag(open))-1))

# A tibble: 504 x 8
# Groups:   symbol [2]
    open  high   low close   volume adjusted symbol return
   <dbl> <dbl> <dbl> <dbl>    <dbl>    <dbl> <chr>   <dbl>
 1  44.9  45.5  44.7  44.8 14316500     43.1 KO     NA    
 2  45.3  45.4  44.8  44.9 15111900     43.2 KO      0.801
 3  45.1  45.3  44.6  45.2 15146000     43.5 KO     -0.331
 4  45    45.5  44.8  45.2 13043600     43.5 KO     -0.310
 5  45.1  45.2  44.5  45.1 13851200     43.3 KO      0.311
 6  45.0  45.0  43.8  43.9 16087100     42.2 KO     -0.199
 7  43.9  45.2  43.9  45.2 15627800     43.4 KO     -2.60 
 8  45.5  45.7  45.0  45.2 16705300     43.5 KO      3.76 
 9  44.9  45.9  44.7  45.9 17080100     44.1 KO     -1.36 
10  46.3  47.2  46.2  46.4 23738000     44.6 KO      3.10 
# ... with 494 more rows

CodePudding user response：

Assuming the return you're looking for as today's value/yesterday's value, and using the tidyverse:

    library(tidyverse)
    library(timetk)
    
    tickers <- c('KO', 'AAPL')
    quantmod::getSymbols(tickers, from = '2020-07-01', to = '2021-07-01')
    
# Convert to a tibble to keep the dates    
equity1 <- tk_tbl(KO) %>% 
      select(date = index, 5)
      
    equity2 <- tk_tbl(AAPL) %>% 
      select(date = index, 5)
    
# Combine the series using a join, in case dates don't line up exactly.
    history <- full_join(equity1, equity2, by = "date")
    
# Make data long, group by equity, do the calculation, turn back into wide data:
    return <- history %>% 
      pivot_longer(-date) %>% 
      group_by(name) %>% 
      mutate(return = value/lag(value)-1) %>% 
      ungroup() %>% 
      pivot_wider(names_from = name, values_from = c(value, return))


    # A tibble: 252 x 5
   date       value_KO.Close value_AAPL.Close return_KO.Close return_AAPL.Close
   <date>              <dbl>            <dbl>           <dbl>             <dbl>
 1 2020-07-01           44.8             91.0       NA                 NA      
 2 2020-07-02           44.9             91.0        0.00134            0      
 3 2020-07-06           45.2             93.5        0.00780            0.0268 
 4 2020-07-07           45.2             93.2       -0.000442          -0.00310
 5 2020-07-08           45.1             95.3       -0.00310            0.0233 
 6 2020-07-09           43.9             95.8       -0.0257             0.00430
 7 2020-07-10           45.2             95.9        0.0282             0.00175
 8 2020-07-13           45.2             95.5        0.00221           -0.00461
 9 2020-07-14           45.9             97.1        0.0137             0.0165 
10 2020-07-15           46.4             97.7        0.0116             0.00688
# ... with 242 more rows