I want to bind daily and weekly data continously (without NA) I am using this code for that purpose but I have two problems.
library(quantmod)
aapl=getSymbols("AAPL",from="2015-01-01",auto.assign=F)
d_aapl=Cl(aapl)/Op(aapl)
head(d_aapl)
w_aapl=to.weekly(d_aapl)
head(w_aapl)
res=cbind(d_aapl,Cl(w_aapl))
head(res,8)
the first problem is with w_aapl=to.weekly(d_aapl) logically it should only perform to.weekly function only to d_aapl variable but its performing it to all the columns except volume and adjusted close. what is happening here?
d_aapl.Open d_aapl.High d_aapl.Low d_aapl.Close
2015-01-02 0.9815064 0.9815064 0.9815064 0.9815064
2015-01-09 0.9811617 1.0243522 0.9811617 0.9941422
2015-01-16 0.9702487 1.0069699 0.9702487 0.9902831
2015-01-23 1.0081603 1.0194087 1.0055072 1.0060552
2015-01-30 0.9943731 1.0221802 0.9708237 0.9895270
2015-02-06 1.0049131 1.0089451 0.9909182 0.9909182
The second problem is with the output res. After column binding I expected some of the days will be populated with NA but instead of NA I want to use previous data
curren result
AAPL.Close d_aapl.Close
2015-01-02 0.9815064 0.9815064
2015-01-05 0.9811617 NA
2015-01-06 0.9973719 NA
2015-01-07 1.0051306 NA
2015-01-08 1.0243522 NA
2015-01-09 0.9941422 0.9941422
2015-01-12 0.9702487 NA
2015-01-13 0.9891412 NA
wanted
AAPL.Close d_aapl.Close
2015-01-02 0.9815064 0.9815064
2015-01-05 0.9811617 0.9815064
2015-01-06 0.9973719 0.9815064
2015-01-07 1.0051306 0.9815064
2015-01-08 1.0243522 0.9815064
2015-01-09 0.9941422 0.9941422
2015-01-12 0.9702487 0.9941422
2015-01-13 0.9891412 0.9941422
How to do it?
The actual answer to your first problem, in which you are asking what is happening: w_aapl=to.weekly(d_aapl) is simply returning the OHLC of your daily d_aapl time series (which is a univariate series) on a weekly scale, although the first few rows (and last few) of the weekly data might not make that so clear. (It has nothing to with adjusted prices or volumes in the context of your question.)
The timestamps in w_aapl correspond to the last available day in each weekly aggregation by default (read about ?to.period's indexAt argument if you wish to change the timestamp to the start of the week, but this is a side point to answering your question), which runs from Monday to Sunday each week (The data for Monday being the first day of the next week).
Since you have no data on the weekends, your timestamp will be the last Friday in each week (but it would be Sunday if Sunday data was included). Maybe this will help in seeing what is going on:
class(coredata(d_aapl)) <- "character"
v <- xts(order.by = index(d_aapl), x = weekdays(index(d_aapl)), dimnames = list(NULL, "Weekday"))
head(merge(d_aapl, v), 20)
# AAPL.Close Weekday
# 2015-01-02 "0.981506445654964" "Friday"
# 2015-01-05 "0.981161686386908" "Monday"
# 2015-01-06 "0.997371888517253" "Tuesday"
# 2015-01-07 "1.00513062514358" "Wednesday"
# 2015-01-08 "1.02435224688221" "Thursday"
# 2015-01-09 "0.994142220540378" "Friday"
# 2015-01-12 "0.970248685084346" "Monday"
# 2015-01-13 "0.989141173831105" "Tuesday"
# 2015-01-14 "1.00696993757364" "Wednesday"
# 2015-01-15 "0.971090909090909" "Thursday"
# 2015-01-16 "0.990283088762806" "Friday"
# 2015-01-20 "1.00816028405639" "Tuesday"
# 2015-01-21 "1.00550716857753" "Wednesday"
# 2015-01-22 "1.01940867006333" "Thursday"
# 2015-01-23 "1.00605520909915" "Friday"
# 2015-01-26 "0.994373131604943" "Monday"
# 2015-01-27 "0.97082370522725" "Tuesday"
# 2015-01-28 "0.980277148183554" "Wednesday"
# 2015-01-29 "1.02218020976616" "Thursday"
# 2015-01-30 "0.989527044095827" "Friday"
Specifically for your data, each row will take the data from Monday to Friday, and assign the Friday timestamp:
2015-01-02 is a special case which takes the values between 2014-12-29 (A Monday) to 2015-01-04 (A Sunday), which is just one value on Friday at 2015-01-02 (which is why OHLC = C for the first weekly bar).2015-01-09 row will taken from d_aapl between 2015-01-05 to 2015-01-11. You can see the high was clearly from 2015-01-08, the low on 2015-01-05, the open was the first value in the range which is 2015-01-05, the close the value for 2015-01-09.2015-01-16 will be taken from d_aapl between 2015-01-12 to 2015-01-18, etc....Regarding your second question, other answers already provided work well, but you might also consider seeing ?merge.xts and its fill argument, which is more concise in certain situations: res=merge(d_aapl,Cl(w_aapl), fill = na.locf)