2
$\begingroup$

For performance presentation a multi period (or multi horizon) table is needed. What I mean is a table showing the trailing month, quarter, YTD, and other sub periods up to since inception.

So I want to have an easy way to calculate this with returns input of arbitrary length and frequency. In other words, input can be a daily series of 2 years of data, a monthly series of 10 years of data, etc.

This is a straightforward task to do in Excel, although it can be prone to formula typos and other known drawbacks.

I am looking for an R implementation. Have looked at PerformanceAnalytics, which is able to calculate annualized since inception or a calendar year table with months. However, these functions are limited as the calendar table requires monthly data and there is no flexibility for calculating sub-periods other than since inception.

I recognize I can write a custom function for this; however, this seems to be a very common way to show data in the industry that I would imagine there is some implementation already.

$\endgroup$
0
$\begingroup$

Computing total returns for a fixed period is fairly simple (last price divided by first price minus 1), so the frequency of observations (hourly, daily, weekly, ...) does not matter. But you will need a tool to limit your time-series to the window of interest, such as the previous two years.

For this, I'd suggest to learn about a time-series class. An example, for which I use zoo. It turns out that zoo comes with a method window that allows you to extract a specified time window.

Download data for the DAX (a German stock market index) from Yahoo:

library("tseries")
P <- drop(get.hist.quote("^GDAXI", quote = "Close", retclass = "zoo"))
plot(P)

For computing returns, I will use the package PMwR (which I maintain).

library("PMwR")
returns(P, period = "mtd")
## -1.8%  [31 Oct 2018 -- 21 Nov 2018] 
returns(P, period = "ytd")
## -13.0%  [29 Dec 2017 -- 21 Nov 2018]

## returns over the previous 2 years
returns(window(P, start = Sys.Date()-365*2), period = "itd")
## 4.9%  [22 Nov 2016 -- 21 Nov 2018] 

## returns over the previous 3 years
returns(window(P, start = Sys.Date()-365*3), period = "itd")
## 1.4%  [23 Nov 2015 -- 21 Nov 2018] 

To select other periods, simply set the start and end arguments to window.

If you want a quick overview, you may also say

summary(as.NAVseries(P), na.rm = TRUE)
## ---------------------------------------------------------
## 02 Jan 1991 ==> 21 Nov 2018   (7189 data points, 133 NAs)
##     1359.43         11244.2
## ---------------------------------------------------------
## High               13559.60  (23 Jan 2018)
## Low                 1317.17  (15 Jan 1991)
## ---------------------------------------------------------
## Return (%)              7.9  (annualised)
## ---------------------------------------------------------
## Max. drawdown (%)      72.7
## _ peak              8064.97  (07 Mar 2000)
## _ trough            2202.96  (12 Mar 2003)
## _ recovery                   (20 Jun 2007)
## _ underwater now (%)   17.1
## ---------------------------------------------------------
## Volatility (%)         20.2  (annualised)
## _ upside               15.0
## _ downside             13.7
## ---------------------------------------------------------
## 
## Monthly returns  ▁▁▂▇█▁▁ 
## 
##        Jan   Feb  Mar  Apr  May  Jun   Jul   Aug   Sep   Oct   Nov   Dec   YTD
## 1991   4.5  10.1 -3.1  6.0  6.3 -4.9  -0.3   1.9  -2.7  -1.6  -0.6   0.4  16.0
## 1992   6.9   3.6 -1.6  0.6  4.5 -2.8  -8.3  -4.9  -3.9   1.3   3.7  -0.4  -2.5
## [ ...... ]
## 2015   9.1   6.6  5.0 -4.3 -0.4 -4.1   3.3  -9.3  -5.8  12.3   4.9  -5.6   9.6
## 2016  -8.8  -3.1  5.0  0.7  2.2 -5.7   6.8   2.5  -0.8   1.5  -0.2   7.9   6.9
## 2017   0.5   2.6  4.0  1.0  1.4 -2.3  -1.7  -0.5   6.4   3.1  -1.6  -0.8  12.5
## 2018   2.1  -5.7 -2.7  4.3 -0.1 -2.4   4.1  -3.4  -0.9  -6.5  -1.8       -13.0
$\endgroup$
  • $\begingroup$ Thanks. This addresses my original question. However, the return calculations assumes one is working with prices, is there a way to use this if instead we have a returns series? Of course, I can convert the returns to some index/price series, but my data output is in returns for so would be great to skip one step. $\endgroup$ – Juan Mier Nov 26 '18 at 15:43
  • $\begingroup$ The function only works with prices. But you may easily convert returns to prices with cumprod(1+c(0, r)) for a numeric vector r. This also works for zoo series, but you may want to add an appropriate initial timestamp. $\endgroup$ – Enrico Schumann Nov 28 '18 at 19:24
0
$\begingroup$

When it is required to treat a lot of data, I expect that using numpy and pandas in a python prototyping environement will be the best solution.

  • Pre-process you data with numpy and pandas according to your needs

    after that

  • generate your excel file.

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.