Use R or mysql to calculate time period returns?
I'm trying to calculate various time period returns (monthly, quarterly, yearly etc.) for each unique member (identified by 'Code' in the example below) of a data set. The data set will contain monthly pricing information for a 20 year period for approximately 500 stocks. An example of the data is below:

             Date Code    Price Dividend
    1  2005-01-31  xyz  1000.00     20.0
    2  2005-01-31  abc     1.00      0.1
    3  2005-02-28  xyz  1030.00     20.0
    4  2005-02-28  abc     1.01      0.1
    5  2005-03-31  xyz  1071.20     20.0
    6  2005-03-31  abc     1.03      0.1
    7  2005-04-30  xyz  1124.76     20.0

I am fairly new to R, but thought that there would be a more efficient solution than looping through each 'Code' and then each 'Date' as shown here:

    uniqueDates <- unique(data$Date)
    uniqueCodes <- unique(data$Code
    
    for  (date in uniqueDates) {
      for (code in uniqueCodes) {
        nextDate <- seq.Date(from=stock_data$Date[i], by="3 months",length.out=2)[2]
        curPrice <- data$Price[data$Date == date]
        futPrice <- data$Price[data$Date == nextDate]
        data$ret[(data$Date == date) & (data$Code == code)] <- (futPrice/curPrice)-1
      }
    }
This method in itself has an issue in that seq.Date does not always return the final day in the month.

Unfortunately the data is not uniform (the number of companies/codes varies over time) so using a simple row offset won't work. The calculation must match the 'Code' and 'Date' with the desired date offset. 

I had initially tried selecting the future dates by using the seq.Date function

    data$ret = (data[(data$Date == (seq.Date(from = data$Date, by="3 month", length.out=2)[2])), "Price"] / data$Price) - 1

But this generated an error as seq.Date requires a single entry.

> Error in seq.Date(from = stock_data$Date, by = "3 month", length.out =
> 2) :    'from' must be of length 1

I thought that R would be well suited to this type of calculation but perhaps not. Since all the data is in a mysql database I am now thinking that it might be faster/easier to do this calc directly in the database.

Any suggestions would be greatly appreciated.


Use R or mysql to calculate time period returns?
===

I'm trying to calculate various time period returns (monthly, quarterly, yearly etc.) for each unique member (identified by 'Code' in the example below) of a data set. The data set will contain monthly pricing information for a 20 year period for approximately 500 stocks. An example of the data is below:

             Date Code    Price Dividend
    1  2005-01-31  xyz  1000.00     20.0
    2  2005-01-31  abc     1.00      0.1
    3  2005-02-28  xyz  1030.00     20.0
    4  2005-02-28  abc     1.01      0.1
    5  2005-03-31  xyz  1071.20     20.0
    6  2005-03-31  abc     1.03      0.1
    7  2005-04-30  xyz  1124.76     20.0

I am fairly new to R, but thought that there would be a more efficient solution than looping through each 'Code' and then each 'Date' as shown here:

    uniqueDates <- unique(data$Date)
    uniqueCodes <- unique(data$Code
    
    for  (date in uniqueDates) {
      for (code in uniqueCodes) {
        nextDate <- seq.Date(from=stock_data$Date[i], by="3 months",length.out=2)[2]
        curPrice <- data$Price[data$Date == date]
        futPrice <- data$Price[data$Date == nextDate]
        data$ret[(data$Date == date) & (data$Code == code)] <- (futPrice/curPrice)-1
      }
    }
This method in itself has an issue in that seq.Date does not always return the final day in the month.

Unfortunately the data is not uniform (the number of companies/codes varies over time) so using a simple row offset won't work. The calculation must match the 'Code' and 'Date' with the desired date offset. 

I had initially tried selecting the future dates by using the seq.Date function

    data$ret = (data[(data$Date == (seq.Date(from = data$Date, by="3 month", length.out=2)[2])), "Price"] / data$Price) - 1

But this generated an error as seq.Date requires a single entry.

> Error in seq.Date(from = stock_data$Date, by = "3 month", length.out =
> 2) :    'from' must be of length 1

I thought that R would be well suited to this type of calculation but perhaps not. Since all the data is in a mysql database I am now thinking that it might be faster/easier to do this calc directly in the database.

Any suggestions would be greatly appreciated.

