How to fix NA error on the LAG LOG aggregated returns in R for FRED data-CodePudding

I am having difficulty understanding why the apply.quarterly() function is not really giving me the quarterly returns and returning NA and why my diff(log()) function is also not working with FRED data. Perhaps I am doing something wrong but I cannot really understand what. Could someone help me understand or fix this issue?

The code I use:

library("quantmod")
#Getting FRED data exchange rate
setDefaults(getSymbols,src='FRED')
getSymbols('DEXNOUS')
#DEXNOUS <- as.data.frame(DEXNOUS)

#Getting SP500
getSymbols("^GSPC",src="yahoo")
GSPC <- as.data.frame(GSPC)

#Getting Brent blend oil
setDefaults(getSymbols,src='FRED')
getSymbols("POILBREUSDM",src = "FRED")
#POILBREUSDM <- as.data.frame(POILBREUSDM)

#Aggregating the data
GSPC_AVG_QUARTER<- apply.quarterly(GSPC,mean)

POILBREUSDM_AVG_QUARTER <- apply.quarterly(POILBREUSDM,mean)

DEXNOUS_AVG_QUARTER <- apply.quarterly(DEXNOUS,mean) #for some reason returning NA

#Calculating the log returns

n <- length(GSPC_AVG_QUARTER)
log_returns_GSPC_AVG_QUARTER <- diff(log(GSPC_AVG_QUARTER$GSPC.Adjusted))

ns <- length(POILBREUSDM_AVG_QUARTER)
log_returns_POILBREUSDM_AVG_QUARTER <- diff(log(POILBREUSDM_AVG_QUARTER)) 

nd <- length(DEXNOUS_AVG_QUARTER)
log_returns_DEXNOUS_AVG_QUARTER <- diff(log(DEXNOUS_AVG_QUARTER)) #na

Snipet of the outcome for aggregated data:

Snipet of the outcome for diff Log returns:

I would really appreciate it if someone could please help me out on this.

Note: I edited the code on 14/02/2022 22:28 CET on this post but I still get the error for the FRED data on the exchange rates.

CodePudding user response：

Make these changes:

do not convert the xts objects to data frames
suggest not using setDefaults
n, ns and nd are not used and those lines are dropped.
add na.rm = TRUE to mean
use Ad(...) to extract the adjusted column
have cleaned up formatting

This gives

library(quantmod)

getSymbols('DEXNOUS', src = 'FRED')
getSymbols("^GSPC", src = "yahoo")
getSymbols("POILBREUSDM", src = "FRED")

GSPC_AVG_QUARTER <- apply.quarterly(GSPC, mean, na.rm = TRUE)
POILBREUSDM_AVG_QUARTER <- apply.quarterly(POILBREUSDM, mean, na.rm = TRUE)
DEXNOUS_AVG_QUARTER <- apply.quarterly(DEXNOUS, mean, na.rm = TRUE)

log_returns_GSPC_AVG_QUARTER <- diff(log(Ad(GSPC_AVG_QUARTER)))
log_returns_POILBREUSDM_AVG_QUARTER <- diff(log(POILBREUSDM_AVG_QUARTER)) 
log_returns_DEXNOUS_AVG_QUARTER <- diff(log(DEXNOUS_AVG_QUARTER))

#######

head(log_returns_GSPC_AVG_QUARTER, 3)
##            GSPC.Adjusted
## 2007-03-30            NA
## 2007-06-29   0.049843241
## 2007-09-28  -0.005164808

head(log_returns_POILBREUSDM_AVG_QUARTER, 3)
##            POILBREUSDM
## 1990-03-01          NA
## 1990-06-01  -0.2181184
## 1990-09-01   0.5069333

head(log_returns_DEXNOUS_AVG_QUARTER, 3)
##                 DEXNOUS
## 1971-03-31           NA
## 1971-06-30 -0.003084078
## 1971-09-30 -0.015590386