r: analyse series of historical positions as portfolio using 'standard' tools

Question

I have a series of historical trading positions in the form

Symbol OpenPrice OpenDate InvestmentInDollars CloseDate ReturnInDollars

I need to evaluate the performance of this series over time, preferably using standard packages in R such as quantmod and PerformanceAnalytics, so I can calculate drawdowns, cumulative return/P&L and other standard measures.

This list of trades already exists: I'm not looking to create a trading strategy.

I can find plenty of examples for evaluating a 'named bundle of stocks over time' portfolio, but not for one where there are a series of overlapping trades in different securities.

Is there are simple way to format/munge this data to make its performance easily evaluated, end to end or over specific periods (say month by month?), and to produce the standard measures above?

Answer 1

Since you want to evaluate the results over time, you will need to value open positions between trades. Essentially, create an equity curve for each single-instrument trade; then sum these equity curves at every timestamp.

Here is example code how the equity curve could be computed with the PMwR package, which I maintain. See the PMwRmanual for more details.

In the example, I assume you traded two stocks, A and B. For simplicity, I use numeric timestamps (but you could use Date, POSIXct, etc as well). I then compute the cumulative profit/loss for timestamps 1 to 10.

I start by putting the trades into journals.

library("PMwR")
A <- journal(instrument = "A",
             timestamp = c(3.2, 5.1),
             amount = c(1, -1),
             price = c(99, 102))
##    instrument  timestamp  amount  price
## 1           A        3.2       1     99
## 2           A        5.1      -1    102
## 
## 2 transactions  

B <- journal(instrument = "B",
             timestamp = c(1.1, 4.1),
             amount = c(1,-1),
             price = c(12,12))

##    instrument  timestamp  amount  price
## 1           B        1.1       1     12
## 2           B        4.1      -1     12
## 
## 2 transactions  

The prices to be used for valuation are stored in a matrix prices, with column names matching the stocks' names.

prices <- cbind(A = 101:110,
                B = 11:20)
##        A  B
## [1,] 101 11
## [2,] 102 12
## [3,] 103 13
## [4,] 104 14
## [5,] 105 15
## [6,] 106 16
## [7,] 107 17
## [8,] 108 18
## [9,] 109 19
##[10,] 110 20

I can then look at aggregated profit/loss...

pl(c(A,B))

## 
## A 
##   P/L total       3
##   average buy    99
##   average sell  102
##   cum. volume     2
## 
## B 
##   P/L total      0
##   average buy   12
##   average sell  12
##   cum. volume    2
## 
## 'P/L total' is in units of instrument;
## 'volume' is sum of /absolute/ amounts.

... and also at profit/loss over time.

profit_loss <- pl(c(A,B),
                  along.timestamp = 1:10,
                  vprice = prices)

rowSums(sapply(profit_loss, `[[`, "pl"))

## 1  2  3  4  5  6  7  8  9 10 
## 0  0  1  7  6  3  3  3  3  3 

Answer 2

Yes assuming this is a strategy where you are holding for multiple days you need to combine the data to get your entire portfolios return series:

1. Get the daily price history for each asset you traded - I use the free AlphaVantage API, get an API key and use quantmod with src = 'av' such as getSymbols("SPY", src = 'av', adjusted = TRUE, output.size = "full", api.key = "YOUR_KEY")
2. Calculate the returns using Return.calculate() on the Adjusted Close columns and xts.merge or cbind them into an xts matrix.

3. Create an xts matrix of how many shares you held each day for each asset. Looks like you might need to create a new column to help with this shares = InvestmentInDollars/OpenPrice. So for each row in the matrix, you need to specify how many shares you held in each asset (from OpenDate to ClosedDate).

4. Multiply (scalar) the price matrix by the shares matrix to come up with the daily dollar values for your positions.

5. Multiply the matrix from step four of dollar values by the matrix of asset returns. This gives you the daily P&L for each asset.
6. Sum up these rows rowSums() and set the first row in the column to your starting account value, you now have a series of P&L for your entire history.
7. Take the cumsum() of the daily P&L to get your equity curve.
8. From here, use performanceAnalytics Return.calculate() again to calculate your portfolio returns. From here you can calculate any risk or performance measures.

For you, it looks like the hardest part will be creating a matrix of either how many shares you held or the asset weights (could use return.Portfolio()) unfortunately, there's not much of a way around this.