0

I have a very long dataset of numerous stocks for many years, similar to this one:

one_ticker = tq_get("AAPL", from = "2021-06-01")
one_ticker <- one_ticker %>%
                mutate(day = day(date),
                month = month(date),
                year = year(date))


 symbol date        open  high   low close   volume adjusted   day month  year
   <chr>  <date>     <dbl> <dbl> <dbl> <dbl>    <dbl>    <dbl> <int> <dbl> <dbl>
 1 AAPL   2021-06-01  125.  125.  124.  124. 67637100     124.     1     6  2021
 2 AAPL   2021-06-02  124.  125.  124.  125. 59278900     125.     2     6  2021
 3 AAPL   2021-06-03  125.  125.  123.  124. 76229200     123.     3     6  2021
 4 AAPL   2021-06-04  124.  126.  124.  126. 75169300     126.     4     6  2021
 5 AAPL   2021-06-07  126.  126.  125.  126. 71057600     126.     7     6  2021
 6 AAPL   2021-06-08  127.  128.  126.  127. 74403800     126.     8     6  2021
 7 AAPL   2021-06-09  127.  128.  127.  127. 56877900     127.     9     6  2021
 8 AAPL   2021-06-10  127.  128.  126.  126. 71186400     126.    10     6  2021
 9 AAPL   2021-06-11  127.  127.  126.  127. 53522400     127.    11     6  2021
10 AAPL   2021-06-14  128.  131.  127.  130. 96906500     130.    14     6  2021

  1. I want first to calculate the biWeekly adjusted price return within each month:
    -first biWeekly interval: days 1-15 -second biWeekly interval: days 16-30

  2. Calculate the adjusted returns standard deviation within each quarter.

Here are the results (for Apple last 6 months):

    1. Adjusted_biWeekly_Returns
    
[1]  0.043128324
[2]  0.052324355
[3]  0.081663817
[4] -0.003620508
[5]  0.026136504
[6]  0.004698278
[7] -0.022818187
[8] -0.048995111
[9]  0.0153523
[10] 0.022176775

Explanations:

[1] 129.257401/123.913231-1 = 0.043128324 (15/06/2021 adjusted price// 01/06/2021 adjusted price)
[5] 148.882721/145.090561-1 = 0.026136504 (13/08/2021 & 02/08/2021) - because there was no trading on the 15th and the 1st.

     2. Quarterly Standard Deviation: 

1/06/2021 - 1/09/2021 0.028944365 ([1]-[6] standard deviation) 
1/09/2021 - 1/01/2022 Not available yet.

How can I calculate it in R? *there is the tq_transmute function which is very useful for weekly but not biWeekly calculations

YefR
  • 369
  • 3
  • 12

1 Answers1

0

You could do each step separately and make use of the to.weekly and apply.quarterly functions as done in the code below:

library(tidyverse)
library(tidyquant)
library(xts)
library(PerformanceAnalytics)
library(TTR)

one_ticker = tq_get("AAPL", from = "2021-06-01")
one_ticker <- one_ticker %>%
  mutate(day = day(date),
         month = month(date),
         year = year(date))

aapl_adj <- xts(x = one_ticker$adjusted,
                order.by = one_ticker$date)

aapl_adj_weekly <- to.weekly(aapl_adj, OHLC = F) # convert to weekly

idx <- seq_along(aapl_adj_weekly) %% 2 > 0 # create index for bi-weekly dates
 
aapl_adj_biweekly <- aapl_adj_weekly[idx, ] # extract bi-weekly values

aapl_adj_biweekly_returns <- TTR::ROC(aapl_adj_biweekly, type = "discrete", na.pad = F)

aapl_adj_biweekly_returns

# compute quarterly standard deviation
xts::apply.quarterly(aapl_adj_biweekly_returns, sd)
                   e1
2021-06-18         NA
2021-09-24 0.03159961
2021-11-16 0.02900001

If you don't need to downsample to a biweekly frequency, you could just run this in one go for each ticker. This has the advantage of better estimates of the return standard deviation too, since you use all the available data points instead of only biweekly data:

# fast version without downsampling and annualized standard deviation
aapl_adj |> TTR::ROC(type = "discrete", na.pad = F) |> xts::apply.quarterly(FUN = PerformanceAnalytics::sd.annualized)

                  e1
2021-06-30 0.1551537
2021-09-30 0.2063587
2021-11-16 0.1701798
tester
  • 1,662
  • 1
  • 10
  • 16