I am looking for a way to calculate the daily change in a stock market position between a entry and exit date. For instance if I enter at 02/06/08 at price 951.84, and exit at 02/19/08 at price 967.42 what is the daily change in price based on Daily market price
I would like to merge the two data frames merge(df1,df2,all=T) but at the same time consider buy and sell date and calculate the change in between.
>DailyMarketPrice
date price
01/21/08 917.75
01/22/08 955.93
01/23/08 921.28
01/24/08 958.55
01/25/08 961.69
01/28/08 949.08
01/29/08 956.97
01/30/08 951.14
01/31/08 949.04
02/01/08 976.43
02/04/08 976.46
02/05/08 945.63
02/06/08 951.84
02/07/08 925.87
02/08/08 920.76
02/11/08 911.39
02/12/08 945.41
02/13/08 949.05
02/14/08 950.84
02/15/08 938.79
02/18/08 962.13
02/19/08 967.42
>signals
date Cond
01/21/08 Buy
01/22/08 Sell
02/06/08 Buy
02/19/08 Sell
Unfortunately, I'm not familiar with enough packages to come up with something that'll handle something like this for you. However, since there's only one asset being traded here, it seems simple enough to code up something on your own. Is this the kind of output you're looking for?
date price inv.change inventory pnl.change pnl
01/21/08 917.75 1 0 0.00 0.00
01/22/08 955.93 -1 1 38.18 38.18
01/23/08 921.28 0 0 0.00 38.18
01/24/08 958.55 0 0 0.00 38.18
01/25/08 961.69 0 0 0.00 38.18
01/28/08 949.08 0 0 0.00 38.18
01/29/08 956.97 0 0 0.00 38.18
01/30/08 951.14 0 0 0.00 38.18
01/31/08 949.04 0 0 0.00 38.18
02/01/08 976.43 0 0 0.00 38.18
02/04/08 976.46 0 0 0.00 38.18
02/05/08 945.63 0 0 0.00 38.18
02/06/08 951.84 1 0 0.00 38.18
02/07/08 925.87 0 1 -25.97 12.21
02/08/08 920.76 0 1 -5.11 7.10
02/11/08 911.39 0 1 -9.37 -2.27
02/12/08 945.41 0 1 34.02 31.75
02/13/08 949.05 0 1 3.64 35.39
02/14/08 950.84 0 1 1.79 37.18
02/15/08 938.79 0 1 -12.05 25.13
02/18/08 962.13 0 1 23.34 48.47
02/19/08 967.42 -1 1 5.29 53.76
If so, here's the code I used to merge DailyMarketPrice and signals:
DailyMarketPrice <- do.call(rbind, list(
data.frame(date = "01/21/08", price = 917.75),
data.frame(date = "01/22/08", price = 955.93),
data.frame(date = "01/23/08", price = 921.28),
data.frame(date = "01/24/08", price = 958.55),
data.frame(date = "01/25/08", price = 961.69),
data.frame(date = "01/28/08", price = 949.08),
data.frame(date = "01/29/08", price = 956.97),
data.frame(date = "01/30/08", price = 951.14),
data.frame(date = "01/31/08", price = 949.04),
data.frame(date = "02/01/08", price = 976.43),
data.frame(date = "02/04/08", price = 976.46),
data.frame(date = "02/05/08", price = 945.63),
data.frame(date = "02/06/08", price = 951.84),
data.frame(date = "02/07/08", price = 925.87),
data.frame(date = "02/08/08", price = 920.76),
data.frame(date = "02/11/08", price = 911.39),
data.frame(date = "02/12/08", price = 945.41),
data.frame(date = "02/13/08", price = 949.05),
data.frame(date = "02/14/08", price = 950.84),
data.frame(date = "02/15/08", price = 938.79),
data.frame(date = "02/18/08", price = 962.13),
data.frame(date = "02/19/08", price = 967.42)
))
signals <- setNames(as.data.frame(matrix(c(
"01/21/08", "Buy",
"01/22/08", "Sell",
"02/06/08", "Buy",
"02/19/08", "Sell"
), ncol = 2, byrow = TRUE)), c("date", "Cond"))
# Set `inv.change` to `+1` on "Buy" days, `-1` on "Sell" days and `0` on every
# day without a transaction.
merged <- cbind(DailyMarketPrice, inv.change = c(+1, -1, 0)[match(signals$Cond[match(DailyMarketPrice$date, signals$date)], c("Buy", "Sell", NA))])
# Set `inventory` to 0 on "Buy" days, 1 on "Sell" days, and 1 on every day
# between "Buy" and "Sell" days.
merged$inventory <- head(diffinv(merged$inv.change), -1)
# Mark-to-market the inventory positions by calculating change in prices during
# holding periods.
merged$pnl.change <- merged$inventory * c(0, diff(merged$price))
# Record the cumulative P&L.
merged$pnl <- tail(diffinv(merged$pnl.change), -1)
print(merged)