0

I would like to check if the (ExDate-n) in the Dividend Table exist in the Date Column in the StockPrice Table where n is an incremental variable. If the (ExDate-n) exists in the StockPrice Table, I will assign Exminus1 as ExDate-n It will first start with n = 1 (i.e. yesterday) and finish once the (ExDate-n) exists in the StockPrice Table. I therefore created a for loop but it didn't work out.

I tried the for loop with if statement and %in% operator but it didn't work.

This is the original tables.

Stock table:

Stock Table

Dividend table:

Dividend Table

Final output:

Final output (Modified Dividend Table)

#Install necessary packages
install.packages('quantmod')
install.packages('plyr')
install.packages('dplyr')

library('quantmod')
library('plyr')
library('dplyr')

####################################   Input No.    ########################################
StockNo <- "6823.hk"
startDate <- "2001-01-01"
####################################   Input No.    ########################################

# Stock price Data
stockData <- new.env() #Make a new environment for quantmod to store data in
startDate = as.Date(StartDate) #Specify period of time we are interested in
endDate = Sys.Date()
tickers <- c(StockNo) #Define the tickers we are interested in
StockPrice <- getSymbols(tickers, env = stockData, src = "yahoo", from = startDate, to = endDate, auto.assign = FALSE)
StockPrice <- as.data.frame(StockPrice)
StockPrice <- cbind(Date=as.Date(rownames(StockPrice)),StockPrice)

#Dividend data
Dividend <- getDividends(tickers, env = stockData, src = "yahoo", from = startDate, to = endDate, auto.assign = FALSE)
Dividend <- as.data.frame(Dividend)
Dividend <- cbind(ExDate=as.Date(rownames(Dividend)),Dividend)

# Create Ex Date - 1
#!!!!!!!!!!!!!!!!!!!   Here is where I had issue with 

for (previous in (1:6))

{ 
  if ( (Dividend$ExDate - previous ) %in% StockPrice$Date) 

  { 
    Dividend$Exminus1 <- Dividend$ExDate-previous 
break;
    }
  previous <- previous+1

}

#New <- Dividend[,c(3,1,2)]

#Dividend

#Combine Stock Price & Dividend
CombineTable <- left_join (StockPrice, Dividend , by = c("Date"="Exminus1"))

#Calculate Yield
CombineTable$Yield <- CombineTable[,9]/CombineTable[,5]

#Preapre Month / Year for more segments
CombineTable$Month <- format(as.Date(CombineTable$ExDate, format="%d/%m/%Y"),"%m")
CombineTable$Year <- format(as.Date(CombineTable$ExDate, format="%d/%m/%Y"),"%Y")

tail(CombineTable)
## Get Data with Dividend Only
DivOnlyData <- CombineTable [ !is.na(CombineTable$ExDate) ,]
DivOnlyData

I tried the ifelse method but still no luck. The new column Dividend$Exminus1 always returns Dividend$ExDate - 5 even though the nested ifelse statement is correct when I only ran it.

ifelse ( (Dividend$ExDate - 1) %in% StockPrice$Date ,Dividend$Exminus1 <- Dividend$ExDate-1 , 
         ifelse ( (Dividend$ExDate - 2) %in% StockPrice$Date , Dividend$Exminus1 <- Dividend$ExDate-2, 
                  ifelse ( (Dividend$ExDate - 3) %in% StockPrice$Date , Dividend$Exminus1 <- Dividend$ExDate-3  ,
                           ifelse ( (Dividend$ExDate - 4) %in% StockPrice$Date , Dividend$Exminus1 <- Dividend$ExDate-4  ,
                                  ifelse ( (Dividend$ExDate - 5) %in% StockPrice$Date , Dividend$Exminus1 <- Dividend$ExDate-5 ,NA)))))
Tat
  • 1
  • 1
  • 1
    Please add a reproducible example along with expected output. Also if you google with the warning message `the condition has length > 1 and only the first element will be used` there are lot of posts which would help you understand what might be the issue. – Ronak Shah May 15 '19 at 11:19
  • I have added the final product I am hoping to see. – Tat May 15 '19 at 11:47
  • 2
    It's unnecessary to increment `previous` in a for-loop where it is also your iterator variable. Also, with "add a reproducible example" Ronak Shah means you should give us a way to reconstruct your data so we can test your code. A screenshot isn't enough. –  May 15 '19 at 12:16
  • You added images, not a reproducible example. See [How to make a great R reproducible example?](https://stackoverflow.com/q/5963269/4996248). – John Coleman May 15 '19 at 12:18
  • I put the whole script in to the post. – Tat May 15 '19 at 12:46
  • `if` statements don't expect to operate over a vector—`ifelse` is the vectorized version. Without downloading all your data, I'm guessing you're giving a vector to the `if` condition. (This is where `dput` of a sample of the two data frames would be helpful, rather than us having `quantmod` installed and downloading 18 years of stock data.) There are several posts already about this: here's one https://stackoverflow.com/q/23316161/5325862 – camille May 15 '19 at 13:02
  • Possible duplicate of [Interpreting "condition has length > 1" warning from \`if\` function](https://stackoverflow.com/questions/14170778/interpreting-condition-has-length-1-warning-from-if-function) – camille May 15 '19 at 13:10
  • HI Camille, Just looked at the post you suggested. My follow up question is that post shows a condition that the coder knows exactly what values are in the dataset to do the match. However, Since the DividendDate will change if we input a different stock ticket, how should I tackle it with the for loop? Is %in% a good to use here? – Tat May 15 '19 at 13:13

0 Answers0