Building Portfolio Equity Curves from Overlapping Historical Trades

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


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












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 by Enrico Schumann (score 0, accepted)

https://quant.stackexchange.com/a/40929

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 by Jared M (score 1)

https://quant.stackexchange.com/a/40920

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:

- 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")`

- Calculate the returns using `Return.calculate()` on the Adjusted Close columns and xts.merge or cbind them into an xts matrix.

- 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).

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

- 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.

- 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.

- Take the `cumsum()` of the daily P&L to get your equity curve.

- 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.