Aggregation to Data by Period

[oldandlostnow]

BatchGetSymbols is very useful code. Thank you for writing it.
I have only looked at monthly data produced by BatchGetSymbols. It appears that the closing price and adjusted closing price are computed from the data for the first day of the period. Is that intended?

I believe the current code snippet is

df.tickers <- df.tickers %>%
  group_by(time.groups, ticker) %>%
  summarise(ref.date = min(ref.date),
            volume = sum(volume, na.rm = TRUE),
            price.open = first(price.open),
            price.high = max(price.close),
            price.low = min(price.close),
            price.close = first(price.close),
            price.adjusted = first(price.adjusted)) %>%
  #select(-time.groups) %>%
  arrange(ticker, ref.date)

Should this code be (changing the formulas for price.close and price.adjusted)

df.tickers <- df.tickers %>%
  group_by(time.groups, ticker) %>%
  summarise(ref.date = min(ref.date),
            volume = sum(volume, na.rm = TRUE),
            price.open = first(price.open),
            price.high = max(price.close),
            price.low = min(price.close),
            price.close = last(price.close),
            price.adjusted = last(price.adjusted)) %>%
  #select(-time.groups) %>%
  arrange(ticker, ref.date)

I would appreciate your thoughts. Thank you in advance for your help.

[msperlin]

hi oldandlostnow,

Yes, it was intended. The code uses the price at the first day available at that month (see ref.date = min(ref.date)).

I do agree however, that one could prefer using the last price (as u did in your code). I'll keep this issue open and fix it by adding an option to the function.

Thanks.

[pverspeelt]

Hi @msperlin,

If you run BatchGetSymbols and set the freq.data to weekly the weekly data returned is not using the datas from Monday to Friday and instead of the last day of the week it is showing the Wednesdays. Looks like the week.vec is not selecting the weeks correctly this year. Also I'm not sure which values are selected, but for some of them it is only the value of the Wednesday and not the highest / lowest of the period. (And I'm not sure which period it is taking exactly.)

Also the column values for price.high are not correct. you are using max(price.close) instead of max(price.high).

When I have some more time I will dive into this a bit more and compare this on how xts is doing this.

example:

library(BatchGetSymbols)
start_date <- Sys.Date()-30
end_date <- Sys.Date()

stocks <- BatchGetSymbols(c("FB", "AAPL"), 
                          first.date = start_date, 
                          last.date = end_date, 
                          freq.data = "weekly")

$df.tickers
# A tibble: 10 x 10
   ticker ref.date      volume price.open price.high price.low price.close price.adjusted ret.adjusted.prices ret.closing.prices
   <chr>  <date>         <dbl>      <dbl>      <dbl>     <dbl>       <dbl>          <dbl>               <dbl>              <dbl>
 1 AAPL   2020-10-19 245063000       120.       118.      116.        116.           116.            NA                 NA      
 2 AAPL   2020-10-21 478634100       117.       117.      115.        117.           117.             0.00767            0.00767
 3 AAPL   2020-10-28 710830900       115.       115.      109.        111.           111.            -0.0485            -0.0485 
 4 AAPL   2020-11-04 671619200       114.       119.      115.        115.           115.             0.0337             0.0337 
 5 AAPL   2020-11-11 387955300       117.       120.      119.        119.           119.             0.0413             0.0395 
 6 FB     2020-10-19  32350200       266.       268.      261.        261.           261.            NA                 NA      
 7 FB     2020-10-21 100863900       280.       285.      277.        279.           279.             0.0663             0.0663 
 8 FB     2020-10-28 147865300       279.       281.      261.        268.           268.            -0.0397            -0.0397 
 9 FB     2020-11-04 127264400       281        295.      272.        287.           287.             0.0736             0.0736 
10 FB     2020-11-11  51204400       273.       279.      275.        276.           276.            -0.0379            -0.0379 

[msperlin]

Hi pverspeelt and oldandlostnow,

I fixed the first issue by adding input "how.to.aggregate", which can be "first" or "last".

As for the weekday problem, I also fixed it, making sure all first dates are monday. Give it a try using the github version.
Let me know how it goes.

Best,

[pverspeelt]

Hi @msperlin,

Just tested the github version and the weekly aggregates work as expected. Only quible I have is that I would expect the closing price to be the last by default, not first. Maybe an example in a vignette or documentation to fully explain the difference between first and last for how.to.aggregate will keep the questions to a minimum?

But I appreciate the quick fix 👍 .

Best

[msperlin]

Great. Glad it worked out.

best,

[msperlin]

To improve the description, added an example at manual.