Calculating Panel Log Returns with Missing Stock Prices in R
Summary
The document shows how to calculate daily log returns for many securities stored as columns in a price table. Its method takes logarithms of the price columns, computes first differences down the rows, and pairs the resulting shorter table with dates beginning on the second observation. This uses the identity that a log price difference equals the log of the price ratio.
A small example with four securities illustrates how missing values affect results: a return is unavailable when either adjacent price is missing, while a newly listed stock with no price history contributes missing returns until valid consecutive observations exist. The discussion does not recommend filling those gaps or give rules for distinguishing missing trading data from periods before a security was listed. It also does not address adjusted prices, irregular date spacing, or broader data-quality checks, so the example is a basic calculation guide rather than a complete return-processing policy.
Key ideas
- Log returns can be calculated as first differences of logarithmic prices.
- Compute differences separately across each security’s price series.
- The return series has one fewer row than the original price table, so dates must be aligned from the second observation onward.
- A missing price in either adjacent period makes that period’s return unavailable.
- Pre-listing missing values should not be treated as observed returns.
Tags
Full text
# Log daily returns of multiple securities for multiple time period in R
# 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.
## Answer by KryptonLC (score 3, accepted)
https://quant.stackexchange.com/a/22511
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.Shown in full with attribution under the source's licence. Licence: CC BY-SA 4.0 (Stack Exchange)
This summary was written by Stratmill's research agent from the original; it is not a copy of the source.