I am trying to apply some basic math to daily stock values based on a corresponding yearly value.
(daily prices)
library(tidyquant)
data(FANG)
# daily prices
FANG %>%
select(c(date, symbol, adjusted)) %>%
group_by(symbol)
# A tibble: 4,032 x 3
# Groups: symbol [4]
date symbol adjusted
<date> <chr> <dbl>
1 2013-01-02 FB 28
2 2013-01-03 FB 27.8
3 2013-01-04 FB 28.8
4 2013-01-07 FB 29.4
5 2013-01-08 FB 29.1
6 2013-01-09 FB 30.6
7 2013-01-10 FB 31.3
8 2013-01-11 FB 31.7
9 2013-01-14 FB 31.0
10 2013-01-15 FB 30.1
# ... with 4,022 more rows
(max price per year)
FANG_yearly_high <-
FANG %>%
group_by(symbol) %>%
summarise_by_time(
.date_var = date,
.by = "year",
price = AVERAGE(adjusted))
# Groups: symbol [4]
symbol date price
<chr> <date> <dbl>
1 AMZN 2013-01-01 404.
2 AMZN 2014-01-01 407.
3 AMZN 2015-01-01 694.
4 AMZN 2016-01-01 844.
5 FB 2013-01-01 58.0
6 FB 2014-01-01 81.4
7 FB 2015-01-01 109.
8 FB 2016-01-01 133.
9 GOOG 2013-01-01 560.
10 GOOG 2014-01-01 609.
11 GOOG 2015-01-01 777.
12 GOOG 2016-01-01 813.
13 NFLX 2013-01-01 54.4
14 NFLX 2014-01-01 69.2
15 NFLX 2015-01-01 131.
16 NFLX 2016-01-01 128.
I would like to divide each daily price by the corresponding max price for the year.
I tried:
FANG %>%
group_by(symbol) %>%
summarise_by_time(
.date_var = date,
.by = "year",
price = AVERAGE(adjusted) / YEAR(date(MAX(adjusted)))
)
and the get this error:
Error in as.POSIXlt.numeric(x, tz = tz(x)) : 'origin' must be supplied
Any sensible way to accomplish this? Thank you
summarise_by_time is good if you just want to summarise. But you want to divide a daily price by a max of a period. So you need to use mutate. Below are 2 examples. The first one for daily prices, the second for weekly. You can adjust the weekly version easily to monthly.
library(tidyquant)
library(dplyr)
data(FANG)
# daily prices
FANG %>%
select(c(date, symbol, adjusted)) %>%
group_by(symbol, year = year(date)) %>%
mutate(price_pct = adjusted / max(adjusted))
# A tibble: 4,032 x 5
# Groups: symbol, year [16]
date symbol adjusted year price_pct
<date> <chr> <dbl> <dbl> <dbl>
1 2013-01-02 FB 28 2013 0.483
2 2013-01-03 FB 27.8 2013 0.479
3 2013-01-04 FB 28.8 2013 0.496
4 2013-01-07 FB 29.4 2013 0.508
5 2013-01-08 FB 29.1 2013 0.501
6 2013-01-09 FB 30.6 2013 0.528
7 2013-01-10 FB 31.3 2013 0.540
8 2013-01-11 FB 31.7 2013 0.547
9 2013-01-14 FB 31.0 2013 0.534
10 2013-01-15 FB 30.1 2013 0.519
# ... with 4,022 more rows
Weekly / monthly:
# weekly
FANG %>%
select(c(date, symbol, adjusted)) %>%
group_by(symbol) %>%
tq_transmute(mutate_fun = to.period,
period = "weeks" # change weeks to months for monthly
) %>%
group_by(symbol, year = year(date)) %>%
mutate(price_pct = adjusted / max(adjusted))
# A tibble: 836 x 5
# Groups: symbol, year [16]
symbol date adjusted year price_pct
<chr> <date> <dbl> <dbl> <dbl>
1 FB 2013-01-04 28.8 2013 0.519
2 FB 2013-01-11 31.7 2013 0.572
3 FB 2013-01-18 29.7 2013 0.535
4 FB 2013-01-25 31.5 2013 0.569
5 FB 2013-02-01 29.7 2013 0.536
6 FB 2013-02-08 28.5 2013 0.515
7 FB 2013-02-15 28.3 2013 0.511
8 FB 2013-02-22 27.1 2013 0.489
9 FB 2013-03-01 27.8 2013 0.501
10 FB 2013-03-08 28.0 2013 0.504
# ... with 826 more rows