Calculating Multi-Horizon Returns from Arbitrary-Frequency Data
Summary
The document describes how to calculate trailing and calendar-period returns from price series with different observation frequencies. It recommends using a time-series object to select the desired date window, then computing the total return over that window. Because total return depends on the starting and ending prices, the input can be daily, weekly, or another frequency, provided the selected window is handled appropriately.
An example uses a German equity index and the R packages zoo and PMwR to calculate month-to-date, year-to-date, and returns over prior multi-year windows. It also shows a summary function that reports annualized performance, risk statistics, and a calendar table of monthly returns. The approach offers flexible date selection, while the example does not supply a general-purpose ready-made table for every requested subperiod. The alternative response briefly suggests Python data tools followed by Excel output, but gives no concrete method. The examples illustrate implementation rather than establishing performance or validating return conventions.
Key ideas
- Total return over a fixed window can be computed from the first and last prices, regardless of observation frequency.
- A time-series window operation can select observations for custom trailing periods.
- Month-to-date and year-to-date returns can be calculated alongside returns over user-defined date ranges.
- Summary tools may provide annualized statistics and monthly calendar tables, but their supported formats can be limited.
Tags
Full text
# Multi Period Return Table
# Multi Period Return Table
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.
## Answer by Enrico Schumann (score 0, accepted)
https://quant.stackexchange.com/a/42728
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
```
## Answer by A. STEFANI (score 0)
https://quant.stackexchange.com/a/42723
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.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.