# Log daily returns of multiple securities for multiple time period in R

I have dataset containing daily closing prices of 5413 companies from 2000 to 2014. I want to calculate daily log returns for the stocks as according to dates as log(Price today/Price yesterday). I illustrate the dataset as follows:

Date       A G L    ABA    ABB ABBEY
2000-1-3    NA      NA      NA  NA
2000-1-4    79.5    325     NA  961
2000-1-5    79.5    322.5   NA  945
2000-1-6    79.5    327.5   NA  952
2000-1-7    NA      327.5   NA  941
2000-1-10   79.5    327.5   NA  946
2000-1-11   79.5    327.5   NA  888


How could calculate the the daily log returns and additionally tackle the NA. My sample period is from 2000 to 2014 so there are some companies who were listed in year 2001,so, for the whole year 2000 they have NA, how should this be handled. Your help is highly appreciated.

## 1 Answer

Well, it wasn't easy because you didn't mentioned how your data is formatted. I create my own data.frame() basing on data you provided. You can skip this part if your data.frame is ready. Here's code I used to create a dataframe:

> #given dates
> dates=c("2000-1-3","2000-1-4","2000-1-5","2000-1-6","2000-1-7","2000-1-10","2000-1-11")
> #formating from string to dates
> newdates=as.Date(dates,"%Y-%m-%d")
> #given stock prices
> stock1prices=c("NA","79.5","79.5","79.5","NA","79.5", "79.5")
> stock2prices=c("NA", "325", "322.5", "327.5", "327.5", "327.5", "327.5")
> stock3prices=c("NA","NA","NA","NA","NA","NA","NA")
> stock4prices=c("NA","961","945","952","941","946","888")
> #NewStockPrces - converting from string to numbers
> nsp1=as.numeric(stock1prices)
> nsp2=as.numeric(stock2prices)
> nsp3=as.numeric(stock3prices)
> nsp4=as.numeric(stock4prices)
> #creating dataframe of prices
> prices=data.frame(newdates,nsp1,nsp2,nsp3,nsp4)
> prices
newdates nsp1  nsp2 nsp3 nsp4
1 2000-01-03   NA    NA   NA   NA
2 2000-01-04 79.5 325.0   NA  961
3 2000-01-05 79.5 322.5   NA  945
4 2000-01-06 79.5 327.5   NA  952
5 2000-01-07   NA 327.5   NA  941
6 2000-01-10 79.5 327.5   NA  946
7 2000-01-11 79.5 327.5   NA  888


There was just 4 stocks, so I used very simple method to create my dataframe prices. Here's what is more important for you.

logs=data.frame(
+   cbind.data.frame(
+     newdates[-1],
+     diff(as.matrix(log(prices[,-1])))
+     )
+   )
> logs
newdates..1. nsp1         nsp2 nsp3         nsp4
1   2000-01-04   NA           NA   NA           NA
2   2000-01-05    0 -0.007722046   NA -0.016789481
3   2000-01-06    0  0.015384919   NA  0.007380107
4   2000-01-07   NA  0.000000000   NA -0.011621895
5   2000-01-10   NA  0.000000000   NA  0.005299429
6   2000-01-11    0  0.000000000   NA -0.063270826


To clarify what is going on in this code lets analyze it from the inside out:

Step 1: Calculating log-returns

• You know that log(a/b) = log(a)-log(b), so we can calculate differences of logarithms. Funcition diff(x,lag=1) calculates differences with given lag. Here it is lag=1 so it gives first differences.
• Our x are prices in dataframe. Do pick from a data.frame every columns without the first (there are dates) we use prices[,-1].
• We need logarithms, so log(prices[,-1])
• Function diff() works with vector or matrix, so we need to treat calculated logarithms as matrix, thus as.matrix(log(prices[,-1]))
• Now we can use diff() with lag=1, so diff(as.matrix(log(prices[,-1])))

Step 2: Creating dataframe of log-returns and dates

• We can't use just cbind(). Firstly, because lengths are different (returns are shorter by 1 record). We need to remove first date, so newdates[-1]

• Secondly, using cbind() dates will be transformed into numeric values such 160027 or other.
Here we have to use cbind.data.frame(x,y), as seen above.

• Now data is ready and we can create use a data.frame() and name it as logs so logs=data.frame(...) as above.

Once again I don't know how your database looks like, so I can't give you exact solution. Most important thing is to use diff(log(x))` to easily calculate log-returns.

• Thank you again. Just added a minor detail and it works logs=data.frame(cbind.data.frame(Date\$Price[-1],diff(as.matrix(log(Price[,-1]))))) Dec 29, 2015 at 22:14