# 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


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

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

• ok, thank you! But how do I get this into the def RelPerf() funtion, displayed in the question? Jun 1, 2015 at 0:21
• @hb.klein: Ok, added another snip of code that does what I believe you are looking for (in the context of data grabbed from Yahoo, and calculating the return as price_n/price_0 - 1.0, you can easily modify the lambda function to calculate return however suits you best. Jun 1, 2015 at 1:44

The code looks a bit dated, as grouper now requires level and frequency.

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


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)