Calculating Year-to-Date Returns from Irregular Price Data
Article Quant Q&A · Author: hb.klein
Summary
The document explains how to calculate a running year-to-date return when a price series has trading dates rather than observations for every calendar day. Its central method is to group rows by calendar year and divide each observation by the first available price in its year, then subtract one. This resets the return baseline on the first recorded trading date of each year and works with irregularly spaced observations.
The examples also show how to select the first or last observation in each annual group, and note that the same grouping idea can be used for other time intervals. An alternate answer points out that some years may have no observations and demonstrates handling empty groups. The examples rely on older pandas APIs and historical data-access packages, so their exact syntax may need updating in current environments. The underlying grouping and within-group transformation remain the key reusable ideas; the document does not discuss dividend adjustments or other return conventions beyond its adjusted-price example.
Key ideas
- Group a date-indexed price series by year to establish annual return periods.
- Use the first available observation in each year as the baseline for that year’s running return.
- A within-group transformation can produce a year-to-date series aligned with the original rows.
- Selecting the final observation in each group provides annual endpoint values.
- Handle years with no observations explicitly, and adapt older library syntax as needed.
Tags
Full text
# calculate YTD return / find first available datapoint of a year in python
# calculate YTD return / find first available datapoint of a year in python
I need to calculate the year-to-date relative return of a given dataset. I usually caculate the cumulative relative return with this simple function:
```
def RelPerf(price):
RelPerf = (price/price[0])
return RelPerf
```
The problem ist that I need to set instead of "price[0]" the price by the start of each year (first available datapoint of the year). Since the dataset does not contain data for each day of the year I can't simply use sth like +365. So the question is how do I get dynamically the location of the first available datapoint into the formula?
This is a short example of the dataframe used:
```
CLOSE_SPX Close_iBoxx A_Returns B_Returns A_Vola B_Vola
2014-05-15 1870.85 234.3017 -0.009362 0.003412 0.170535 0.075468
2014-05-16 1877.86 234.0216 0.003747 -0.001195 0.170153 0.075378
2014-05-19 1885.08 233.7717 0.003845 -0.001068 0.170059 0.075384
2014-05-20 1872.83 234.2596 -0.006498 0.002087 0.170135 0.075410
2014-05-21 1888.03 233.9101 0.008116 -0.001492 0.169560 0.075326
2014-05-22 1892.49 233.5429 0.002362 -0.001570 0.169370 0.075341
2014-05-23 1900.53 233.8605 0.004248 0.001360 0.168716 0.075333
2014-05-27 1911.91 234.0368 0.005988 0.000754 0.168797 0.075294
2014-05-28 1909.78 235.4454 -0.001114 0.006019 0.168805 0.075474
2014-05-29 1920.03 235.1813 0.005367 -0.001122 0.168866 0.075451
2014-05-30 1923.57 235.2161 0.001844 0.000148 0.168844 0.075430
2014-06-02 1924.97 233.8868 0.000728 -0.005651 0.168528 0.075641
2014-06-03 1924.24 232.9049 -0.000379 -0.004198 0.167852 0.075267
```
## Answer by MarkD (score 5, accepted)
https://quant.stackexchange.com/a/18088
You need to use a groupby, timegrouper, and its annual option, and take the first value in each group.
For example, if I grab yahoo data:
```
import pandas as pd
import pandas.io.data as web
# Grab S&P 500 data going back to beginning of 2011
SPY_Dat = web.DataReader('SPY', 'yahoo', datetime.date(2011,1,1), end)
# Convert to annual data:
SPY_Ann_Dat = SPY_Dat.groupby(pd.TimeGrouper('A')).nth(0)
```
In this case we are using the Annual time grouper ('A'). We could also use monthly (M), quarterly (Q), or weekly (W).
Interestingly, nth can also use negative indexing, so if we wanted to get the last day of each year, we could change the last line to:
```
SPY_Last_Ann_Dat = SPY_Dat.groupby(pd.TimeGrouper('A')).nth(-1)
```
So, now if we wanted to, as in your example, calculate the YTD return, we can tweak things a bit, to not just grab the first date, but apply a transformation using the data from the first date in each group:
```
import pandas as pd
import pandas.io.data as web
# Grab S&P 500 data going back to beginning of 2011
SPY_Dat = web.DataReader('SPY', 'yahoo', datetime.date(2011,1,1))
# Group the data with the same TimeGrouper to get things grouped by year
SPY_GroupedDat = SPY_Dat.groupby(pd.TimeGrouper('A'))
# Create a new column with YTD data of adjusted close, using a transformation lambda function applied to our group data.
SPY_Dat["YTD"] = SPY_GroupedDat['Adj Close'].transform(lambda x: x/x.iloc[0]-1.0)
```
This will yield a column in SPY_Dat which is a running YTD return, reset on the first trading date of each year.
## Answer by Martien Lubberink (score 1)
https://quant.stackexchange.com/a/41018
The code looks a bit dated, as `grouper` now requires `level` and `frequency`.
Then, pandas-datareader gets choked on Yahoo and Google.
The script below works, using quandl as source.
```
import pandas as pd
pd.core.common.is_list_like = pd.api.types.is_list_like # Solving a pandas-datareader v0.6.0 problem
import pandas_datareader.data as web
from datetime import datetime
start = datetime(2011, 1, 1)
end = datetime(2018, 1, 1)
```
Grab S&P 500 data going back to beginning of 2011 SPY_Dat = web.DataReader('AAPL.US', 'quandl', start, end)
Convert to annual data:
```
SPY_Ann_Dat = SPY_Dat.groupby(pd.Grouper(level='Date', freq='A')).nth(0)
```
Alternatively:
```
SPY_Last_Ann_Dat = SPY_Dat.groupby(pd.Grouper(level='Date', freq='A')).nth(-1)
```
Group the data with the same TimeGrouper to get things grouped by year
```
SPY_GroupedDat = SPY_Dat.groupby(pd.Grouper(level='Date', freq='A'))
```
Create a new column with YTD data of adjusted close, using a transformation lambda function applied to our group data.
```
SPY_Dat["YTD"] = SPY_GroupedDat['AdjClose'].transform(lambda x: x/x.iloc[0]-1.0)
```
## Answer by Vagee (score 0)
https://quant.stackexchange.com/a/36402
In case any years data is missing, it gives error.
It can be corrected as below :
```
df["YTD"] = df1_GroupedDat['Adj Close'].transform(lambda x: 0 if x.empty else x/x.iloc[0]-1.0)
```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.